Home » RDBMS Server » Server Administration » Parse statistic values don't seem to add up (Oracle 18c Express, Windows 10 )
Parse statistic values don't seem to add up [message #685105] Sat, 23 October 2021 12:23 Go to next message
Andrey_R
Messages: 380
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 Go to previous message
Michel Cadot
Messages: 68050
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

Previous Topic: log_archive_dest truncate
Next Topic: Truncate SYS.AUD$ Using dbms_audit_mgmt
Goto Forum:
  


Current Time: Mon Dec 06 21:57:01 CST 2021