2

When I get the TKPROF output, I can see the parse, execute, fetch, disk, etc.

call   count      cpu    elapsed     disk    query current    rows
---- -------  -------  --------- -------- -------- -------  ------
Parse      1     0.16      0.29         3       13       0       0
Execute    1     0.00      0.00         0        0       0       0
Fetch      1     0.03      0.26         2        2       4      14 
Misses in library cache during parse: 1

But getting the TKPROF is not as fast as getting the autotrace from the SQL Developer.

So, are there any equivalent columns corresponding to these columns in TKPROF output? It can be the execution plan output our in the V$STATNAME area below. If yes, which ones are they? Because when I check, I see a couple different parses. And I don't see anything like fetch in the v$statname.

And if there are the equivalents, will the values of TKPROF and AUTOTRACE be equal or different?

Thanks in advance.

oramas
  • 881
  • 7
  • 12

1 Answers1

2

there are many different ways to get execution plans and plan performance information in Oracle - and though they use similar information from the wait interface and the internal instrumentation it is not always easy to get an exact match for the numbers from the different tools. Usually this is not a big issue, since all the results provide a similar picture. Just to clarify some points:

  • tkprof is a tool to render a trace file generated by a SQL trace (which can be created in different ways): so you have to create the trace file and then get the rendering; and this may be more complicated than the use of other build-in strategies. On the plus-side SQL trace provides resource and timing information for all the detail steps in the execution.
    • autotrace uses internal statistics from the wait interface, but you have to consider the effects of fetch size and data transport to get the same information, your application access would create. With autotrace you get only an information about the reosurce usage and timing for the complete operation.
    • if you have the necessary licence, you can use the SQL Monitor to get very detailed information about the execution steps and their impact on the performance of the complete operation.
    • and finally you can create an execution plan with rowsource statistics by the use of a hint (gather_plan_statistics) or a corresponding session parameter (statistics_level). To gather this kind of plan you have to call dbms_xplan.display_cursor with a fitting format option.

Chris Saxon gives a useful overview for these options in https://blogs.oracle.com/sql/how-to-create-an-execution-plan.

Martin Preiss
  • 396
  • 3
  • 9