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
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

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



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;


SQL> select :a from dual;


SQL> select :a from dual;


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


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 |

          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

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

What am I missing ?


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: 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

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

Current Time: Mon Jul 04 17:15:56 CDT 2022