Hi Michael,
"v$sqlarea.loads" is incremented for every hard parse:
SQL> set serveroutput off
--1st run (hard Parse; load query cursor for first time)
SQL> select 0 from dba_users where user_id = uid;
0
SQL> select a.sql_id,
2 a.address,
3 a.hash_value,
4 a.version_count,
5 a.executions,
6 a.parse_calls,
7 a.invalidations,
8 a.loads
9 from v$sqlarea a,
10 v$session b
11 where a.sql_id = b.prev_sql_id
12 and b.user# = uid
13 and b.sid = sys_context('userenv','sid');
SQL_ID ADDRESS HASH_VALUE VERSION_COUNT EXECUTIONS
PARSE_CALLS INVALIDATIONS LOADS
------------- ---------------- ---------- ------------- ----------
----------- ------------- ----------
4x49sa3ncwfxd 00000000AE611F68 3905829805 1 1
1 0 1
--2nd run (Soft Parse; loads remains unchanged)
SQL> select 0 from dba_users where user_id = uid;
0
SQL> select a.sql_id,
2 a.address,
3 a.hash_value,
4 a.version_count,
5 a.executions,
6 a.parse_calls,
7 a.invalidations,
8 a.loads
9 from v$sqlarea a,
10 v$session b
11 where a.sql_id = b.prev_sql_id
12 and b.user# = uid
13 and b.sid = sys_context('userenv','sid');
SQL_ID ADDRESS HASH_VALUE VERSION_COUNT EXECUTIONS
PARSE_CALLS INVALIDATIONS LOADS
------------- ---------------- ---------- ------------- ----------
----------- ------------- ----------
4x49sa3ncwfxd 00000000AE611F68 3905829805 1 2
2 0 1
--Purge query cursor for testing purposes
SQL> exec DBMS_SHARED_POOL.PURGE ('00000000AE611F68, 3905829805', 'C');
PL/SQL procedure successfully completed.
--3rd run (Hard Parse; loads metric is incremented)
SQL> select 0 from dba_users where user_id = uid;
0
----------
0
SQL> select a.sql_id,
2 a.address,
3 a.hash_value,
4 a.version_count,
5 a.executions,
6 a.parse_calls,
7 a.invalidations,
8 a.loads
9 from v$sqlarea a,
10 v$session b
11 where a.sql_id = b.prev_sql_id
12 and b.user# = uid
13 and b.sid = sys_context('userenv','sid');
SQL_ID ADDRESS HASH_VALUE VERSION_COUNT EXECUTIONS
PARSE_CALLS INVALIDATIONS LOADS
------------- ---------------- ---------- ------------- ----------
----------- ------------- ----------
4x49sa3ncwfxd 00000000AE611F68 3905829805 1 1
1 1 2
We also can verify this by querying the session statistics:
--I will use @mystats script by Adrian Billington
SQL> @mystats start
--1st run (Hard parse; parse count (hard) displayed)
SQL> select 1 from dba_users where user_id = uid;
1
----------
1
SQL> @mystats stop "l=parse count"
==========================================================================================
MyStats report : 16-AUG-2015 01:21:44
==========================================================================================
------------------------------------------------------------------------------------------
1. Summary Timings
------------------------------------------------------------------------------------------
Type Statistic Name
Value
------ ----------------------------------------------------------------
----------------
TIMER snapshot interval (seconds)
9.67
TIMER CPU time used (seconds)
0.05
------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------
Type Statistic Name
Value
------ ----------------------------------------------------------------
----------------
STAT parse count (hard)
4
STAT parse count (total)
18
-----------------------------------------------------------------------------------------
3. About
------------------------------------------------------------------------------------------
- MyStats v2.01 by Adrian Billington (http://www.oracle-developer.net)
- Based on the SNAP_MY_STATS utility by Jonathan Lewis
==========================================================================================
End of report
==========================================================================================
SQL> @mystats start
--2nd run (Soft Parse; parse count (hard) not displayed)
SQL> select 1 from dba_users where user_id = uid;
1
----------
1
SQL> @mystats stop "l=parse count"
==========================================================================================
MyStats report : 16-AUG-2015 01:22:17
==========================================================================================
------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------
Type Statistic Name
Value
------ ----------------------------------------------------------------
----------------
STAT parse count (total)
13
------------------------------------------------------------------------------------------
3. About
------------------------------------------------------------------------------------------
- MyStats v2.01 by Adrian Billington (http://www.oracle-developer.net)
- Based on the SNAP_MY_STATS utility by Jonathan Lewis
==========================================================================================
End of report
==========================================================================================
SQL>
Thanks and regards,
Luis Figueroa.
On Sat, Aug 15, 2015 at 10:32 PM, Michael Calisi <oracle455@xxxxxxxxx>
wrote:
Is there a way to check whether a query is being hard or soft parsed?
