Thanks for the question, Sujit.

Asked: March 22, 2017 - 11:52 am UTC

Last updated: March 25, 2017 - 3:30 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

I had query regarding "SQL ordered by Parse Calls" in AWR report. Is this hard+soft parse or only soft parse queries. Also, I have one query where Parse calls - 370,526
Executions - 576,426

If it a soft parse, execution count and parse count should be same. Am I missing something in my understanding?
Is it due to "session cached cursor" concept?

and Connor said...

A parse call is *any* parse call.

"If it a soft parse, execution count and parse count should be same"

Not necessarily and yes, most likely due to session cached cursors, eg

SQL> create or replace
  2  procedure my_proc is
  3    x varchar2(100);
  4  begin
  5    for i in 1 .. 50 loop
  6      select 'HELLO' into x from dual;
  7    end loop;
  8  end;
  9  /

Procedure created.

SQL>
SQL> alter session set sql_trace = true;

Session altered.

SQL>
SQL> exec my_proc;

PL/SQL procedure successfully completed.

SELECT 'HELLO' 
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     50      0.00       0.00          0          0          0           0
Fetch       50      0.00       0.00          0          0          0          50
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      101      0.00       0.00          0          0          0          50

--
--
-- now repeated in a new session
--
SQL> alter session set session_cached_cursors = 0 ;

Session altered.

SQL> alter session set sql_trace = true;

Session altered.

SQL>
SQL> exec my_proc;

PL/SQL procedure successfully completed.

SELECT 'HELLO' 
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       50      0.00       0.00          0          0          0           0
Execute     50      0.00       0.00          0          0          0           0
Fetch       50      0.00       0.00          0          0          0          50
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      150      0.00       0.00          0          0          0          50

Rating

(2 ratings)

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

Comments

Thanks Connor

Sujit Pandey, March 24, 2017 - 5:52 am UTC

Thanks Connor for the clear explainantion..

March 25, 2017 - 3:30 am UTC

glad we could help

Statistics

A reader, March 24, 2017 - 6:42 am UTC

Hi Connor,

From where you get the below Statistics?

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 50 0.00 0.00 0 0 0 0
Fetch 50 0.00 0.00 0 0 0 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.00 0.00 0 0 0 50

March 25, 2017 - 3:29 am UTC

tkprof [mytracefile]

If you want to see how to locate the tracefile for a session, here's a video on it

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks Connor

Statistics

More to Explore

PL/SQL demos

PL/SQL docs

Regarding AWR Report's - "SQL ordered by Parse Calls"

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Thanks Connor

Statistics

More to Explore

PL/SQL demos

PL/SQL docs