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

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

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