hard/soft parse

Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention [ID 62143.1]
Hard Parse
If a new SQL statement is issued which does not exist in the shared pool then this has to be parsed fully. Eg: Oracle has to allocate memory for the statement from the shared pool, check the statement syntactically and semantically etc... This is referred to as a hard parse and is very expensive in both terms of CPU used and in the number of latch gets performed.
Soft Parse
If a session issues a SQL statement which is already in the shared pool AND it can use an existing version of that statement then this is known as a 'soft parse'. As far as the application is concerned it has asked to parse the statement.

 

----------------------------------------

SQL>  select statistic#, name,value from v$sysstat where NAME like 'parse count%';

STATISTIC# NAME                                VALUE
---------- ------------------------------ ----------
       583 parse count (total)               1572755
       584 parse count (hard)                  65429　　★
       585 parse count (failures)               3857
       586 parse count (describe)                396

 

 

http://docs.oracle.com/cd/E11882_01/server.112/e25513/stats002.htm#i375475
--------------------------------------------
Oracle® Database Reference 11g Release 2 (11.2)
Statistics Descriptions
parse count (hard)
Total number of parse calls (real parses). A hard parse is a very expensive operation in terms of memory use, because it requires Oracle to allocate a workheap and other memory structures and then build a parse tree.
parse count (describe)
Total number of parse calls on a describe cursor. This operation is a less expensive than a hard parse and more expensive than a soft parse.
parse count (total)
Total number of parse calls (hard, soft, and describe). A soft parse is a check on an object already in the shared pool, to verify that the permissions on the underlying object have not changed.
 

-------------------------------------------------------

How to Identify Resource Intensive SQL to Tune [ID 232443.1]

Finding SQL Statements With High CPU Parse Time in Statspack

If "CPU Parse time" is a significant component of Response Time, it can be because cursors are repeatedly opened and closed every time they are executed instead of being opened once, kept open for multiple executions and only closed when they are no longer required.

The "SQL ordered by Parse Calls" can help find such cursors, here is an example:

SQL ordered by Parse Calls for DB: DWI1 Instance: DWI1 Snaps: 1 -4
-> End Parse Calls Threshold: 1000

% Total
Parse Calls Executions Parses Hash Value
------------ ------------ -------- ----------
13,632,745 13,632,745 98.90 3980186470
SELECT distinct TS002.C_JOB_DEP, TS002.C_JOB FROM TS002_JO
B_DEP TS002, TS001_JOB TS001 WHERE TS001.C_JOB = TS002.C_JO
B_DEP AND TS002.C_JOB = :b1 AND TS001.C_TIP_JOB !=

11,701 27,255,840 0.08 3615375148
COMMIT

The first SQL statement (hash value 3980186470) has had the most parses issued against it (98.90% of all parses in the instance). It is parsed every time it is executed (Parse Calls = Executions). Due to its frequency it is a prime candidate for reducing parse calls as described above.

Note: in excessive parsing situations, it is likely that there will be symptoms such as latch free waits on the Library Cache latches and possibly the Shared Pool latch, in addition to CPU Parse time.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24756186/viewspace-755397/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24756186/viewspace-755397/