Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sai Pradyumn.

Asked: January 20, 2017 - 8:16 am UTC

Last updated: October 09, 2023 - 4:54 am UTC

Version: Oracle 11

Viewed 10K+ times! This question is

You Asked

Hi

when can be parse calls total are higher than the execution totals for a query in DBA_HIST_SQLSTAT
Please provide me possible scenarios

Thanks
Sai

and Connor said...

There are lots of way you can parse a query without actually running it, eg

SQL> variable rc refcursor
SQL> exec open :rc for SELECT * FROM DUAL D99;

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2    l_theCursor     integer default dbms_sql.open_cursor;
  3  begin
  4    dbms_sql.parse(  l_theCursor,  'SELECT * FROM DUAL D99', dbms_sql.native );
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL> select sql_text, parse_calls, executions
  2  from v$sql
  3  where sql_text like 'SEL%D99%';

SQL_TEXT                                                         PARSE_CALLS EXECUTIONS
---------------------------------------------------------------- ----------- ----------
SELECT * FROM DUAL D99                                                     2          1



A common cause is client tools which parse a query to ensure it is valid before going ahead an executing it.

Rating

  (2 ratings)

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

Comments

_ve execute to parse ratio

Sai Pradyumn, January 21, 2017 - 11:31 am UTC

Hi McDonald
Thanks for your reply.I had come across with this scenario.
But in my production environment there are some queries
where the execute to parse ratio is negative.

Following are categories of those queries.

1:Some of them are from anonymous blocks.
2:Some of them are from application with bind variables
with Prepared statement of JDBC.
3:Some of them are part of Materialized view Fast refresh

Now i am getting why these queries are having the highest parse call totals.

If the parse calls totals if highest than the execution totals , will to go hard parsing always ?

Is there are relation between parsing type and Execute to parse ration


Link is gone

Alain L., October 06, 2023 - 6:44 am UTC

Hi Connor,

the last link you provided in the comment is now leading to a plumbing company :-)
I don't think this is on purpose.
Connor McDonald
October 09, 2023 - 4:54 am UTC

Well there you go :-)

I've updated it to a new link.

Thanks for letting us know

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library