OraFAQ Forum: Server Administration » Parse statistic values don't seem to add up

Home » RDBMS Server » Server Administration » Parse statistic values don't seem to add up (Oracle 18c Express, Windows 10 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Parse statistic values don't seem to add up [message #685105]

Sat, 23 October 2021 12:23

Andrey_R
Messages: 409
Registered: January 2012
Location: Israel

Senior Member

Hi all,
I am trying to review parse statistics for a database.
When I check V$SYSSTAT with the following query, I get numbers that don't add up:

SQL> select  name,value from v$sysstat where lower(name) like 'parse count%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse count (total)                                                   44852
parse count (hard)                                                     9321
parse count (failures)                                                   34
parse count (describe)                                                   48

SQL>

I tried to look up Oracle documentation but whatever I found couldn't explain the gap.

https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/V-SYSSTAT.html#GUID-250136E5-E07E-4A78-9F67-28C0D3C6E922

Questions
-------

1. How can the gap between sum of hard/failures/describe be explained ?
2. I tried to re-execute some query to make sure it's soft parsed:

SQL> var a number
SQL> exec :a := 1;

PL/SQL procedure successfully completed.

SQL> select :a from dual;

        :A
----------
         1

SQL> select :a from dual;

        :A
----------
         1

SQL> select :a from dual;

        :A
----------
         1

SQL> set autot on
SQL> select :a from dual;

        :A
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

I still don't see any soft parses in my V$SYSSTAT.

What am I missing ?

Thanks
Andrey

Report message to a moderator

Re: Parse statistic values don't seem to add up [message #685107 is a reply to message #685105]

Sat, 23 October 2021 12:38

Michel Cadot
Messages: 68315
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Because you also have "softer soft parses" as Tom Kyte used to name them, these are statements that are retained in your PGA/UGA thanks to the "session_cached_cursors" parameter and don't need any parse through the SGA and so are not added to these statistics.
You can get them through "session cursor cache hits" statistic.

Have a look at these 2 topics from AskTom:
High Soft Parse Rate
Softer Soft Parse

Report message to a moderator

Re: Parse statistic values don't seem to add up [message #686310 is a reply to message #685107]

Sat, 23 July 2022 07:43

Andrey_R
Messages: 409
Registered: January 2012
Location: Israel

Senior Member

Michel Cadot wrote on Sat, 23 October 2021 20:38

Because you also have "softer soft parses" as Tom Kyte used to name them, these are statements that are retained in your PGA/UGA thanks to the "session_cached_cursors" parameter and don't need any parse through the SGA and so are not added to these statistics.
You can get them through "session cursor cache hits" statistic.

Have a look at these 2 topics from AskTom:
High Soft Parse Rate
Softer Soft Parse

Thank you very much Michel. And apologies for providing feedback with a bit of a delay , but I did eventually get there Smile

I've visited the links and read through them. The softer soft parse means session_cached_cursors, for which there is a metric in V$SYSSTAT,
however it cumulates to more than the total parses alltogether:

SQL> set lines 900 pages 20000
SQL> col name for a75
SQL> select  name,value from v$sysstat where lower(name) like 'parse count%'
  2  or name ='session cursor cache hits';

NAME                                                                             VALUE
--------------------------------------------------------------------------- ----------
session cursor cache hits                                                       170212
parse count (total)                                                              33977
parse count (hard)                                                                7437
parse count (failures)                                                              64
parse count (describe)                                                              12

I've tried to read on some documentation for 18c Statistics Description https://docs.oracle.com/en/database/oracle/oracle-database/18/refrn/statistics-descriptions-2.html#GUID-2FBC1B7E-9123-41DD-8178-96176260A639

And it says that session_cursor_cache_hits is "Number of hits in the session cursor cache. A hit means that the SQL (including recursive SQL) or PL/SQL statement did not have to be reparsed. Subtract this statistic from "parse count (total)" to determine the real number of parses that occurred."

So I'm still missing something in understanding the context of things.. Sad

Can I get another hint please ?

Thanks
Andrey

Report message to a moderator

Re: Parse statistic values don't seem to add up [message #686312 is a reply to message #686310]

Sat, 23 July 2022 10:17

Michel Cadot
Messages: 68315
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is a very old discussion about what is in and what is not in both these statistics which change with the versions and even patches.

See the following threads to have a quick look:
Jonathan Lewis' blog: Parse Calls
Christian Antognini's blog: The Broken Statistics: "parse count (total)" and "session cursor cache hits"
Oracle community: about "session cursor cache hits" and "parse count(total)" statistics

Report message to a moderator

Re: Parse statistic values don't seem to add up [message #686313 is a reply to message #686312]

Sat, 23 July 2022 15:35

Andrey_R
Messages: 409
Registered: January 2012
Location: Israel

Senior Member

Michel Cadot wrote on Sat, 23 July 2022 18:17

This is a very old discussion about what is in and what is not in both these statistics which change with the versions and even patches.

See the following threads to have a quick look:
Jonathan Lewis' blog: Parse Calls
Christian Antognini's blog: The Broken Statistics: "parse count (total)" and "session cursor cache hits"
Oracle community: about "session cursor cache hits" and "parse count(total)" statistics

Thanks Michel. Will do.

But I would like to just clarify..
Tom Kyte said:

Quote:

there are three types of parses (well, maybe four) in Oracle...

there is the dreaded hard parse - they are VERY VERY VERY bad.
there is the hurtful soft parse - they are VERY VERY very bad.
there is the hated softer soft parse you might be able to achieve with session cached cursors - they are VERY very very bad.

then there is the absence of a parse, no parse, silence. This is golden, this is perfect, this is the goal.

So From what I understand:

Hard Parse - when you have nothin for the executed SQL in the cache library and you invest tons of work to produce an exec plan and write it down into the library.
Soft Parse - when you have an SQL executing and you go look for the plan in the library in the Shared Pool and you get it back to your session.
Softer soft Parse - when you have already Soft/Hard Parsed a query in your session so it's in your sessions's cache and you re-use the plan from there very easily

What the hack is No Parse then Shocked

Is that some kind of PL/SQL static SQL execution that is somehow even more efficient than the "regular" Softer soft parse via SQL engine ?
How is it different than the regular SQL ? what happens when it "Not parsin" versus the regular SQL ? and Why can't the SQL do the same..

Thanks
Andrey

Edit: I think I found the answer here https://asktom.oracle.com/pls/apex/asktom.search?tag=what-is-an-absence-of-parse and here https://docs.oracle.com/cd/B10500_01/java.920/a96654/stmtcach.htm
I'll follow up in case I have further questions. Many many thanks again.

[Updated on: Sat, 23 July 2022 15:41]

Report message to a moderator

Previous Topic:	JavaVM invalid
Next Topic:	SYS_NC columns

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 06 12:27:27 CST 2022