Skip to Main Content
  • Questions
  • TKPROF analysis: parses and executions

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, HARI PRASAD.

Asked: February 21, 2017 - 7:34 am UTC

Last updated: February 21, 2017 - 11:06 am UTC

Version: 11G

Viewed 1000+ times

You Asked

Hi Tom,

Below is the execution plan for one of the sql. When i verify the query execution plan it is using index range scan and index unique scan.

By seeing the below output how we will start the analysis.
and can you advise on why is parse count = 1 and execute count is 75437.

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 75437 99.38 98.53 0 0 0 0
Fetch 75437 34.42 34.02 0 451618 0 75437
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 150875 133.81 132.56 0 451618 0 75437

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS

and Chris said...

It means that Oracle Database (hard) parsed the query once and executed it 75,437 times. i.e. of the 75k times your session ran the statement, only on the first run was the statement NOT in the shared pool.

So it only had to go through the full process of checking the statement is valid and producing the execution plan once.

http://docs.oracle.com/database/122/CNCPT/sql.htm#CNCPT1741
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2588723819082

For example, this block runs "select * from t" 100 times:

create table t (
  x int 
);

insert into t values (1);
commit;

alter session set tracefile_identifier = chris;
exec dbms_monitor.session_trace_enable ( );
declare
  v t%rowtype;
begin
  for i in 1 .. 100 loop
    select * into v from t;
  end loop;
end;
/
exec dbms_monitor.session_trace_disable; 


But it only had to load the statement once. So TKPROF for this statement shows:

SELECT *
FROM
 T


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute    100      0.00       0.00          0          0          0           0
Fetch      100      0.00       0.00          0        300          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      201      0.00       0.00          0        301          0         100


Parse = 1, Executions and Fetches both = 100.

In general this is a good thing. Hard parsing is an expensive process. So you want few parses to lots of executions. Which is what you have.

But this doesn't necessarily mean your code is good!

For example, in your output, the query processed 75,437 rows from 75,437 executions. i.e. an average of one row/run. This suggests you may have a loop with 75,437 iterations, fetching one row each time. If this is the case, ask yourself: can you get rid of the loop and rewrite your code to fetch all the rows in one go?

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.