Parse statistic values don't seem to add up [message #685105] |
Sat, 23 October 2021 12:23  |
Andrey_R
Messages: 393 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
|
|
|
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: 68226 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
|
|
|