Re: Parse
Date: Sun, 16 Aug 2015 01:36:08 -0400
Message-ID: <CAFZPrvXBFsy6N3F_5TeYd6y72TxhR=eBYEEU2_M-z7CGX1zJzg_at_mail.gmail.com>
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 _at_mystats script by Adrian Billington
SQL> _at_mystats start
--1st run (Hard parse; parse count (hard) displayed)
SQL> select 1 from dba_users where user_id = uid;
1
1
SQL> _at_mystats stop "l=parse count"
MyStats report : 16-AUG-2015 01:21:44
- Summary Timings
Type Statistic Name Value
------ ----------------------------------------------------------------
TIMER snapshot interval (seconds) 9.67 TIMER CPU time used (seconds) 0.05
- Statistics Report
Type Statistic Name Value
------ ----------------------------------------------------------------
STAT parse count (hard) 4 STAT parse count (total) 18
- 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> _at_mystats start
--2nd run (Soft Parse; parse count (hard) not displayed)
SQL> select 1 from dba_users where user_id = uid; 1
1 SQL> _at_mystats stop "l=parse count"
MyStats report : 16-AUG-2015 01:22:17
- Statistics Report
Type Statistic Name Value
------ ----------------------------------------------------------------
STAT parse count (total) 13
- 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>
Luis Figueroa.
On Sat, Aug 15, 2015 at 10:32 PM, Michael Calisi <oracle455_at_gmail.com> wrote:
> Is there a way to check whether a query is being hard or soft parsed? >
--
http://www.freelists.org/webpage/oracle-l
Received on Sun Aug 16 2015 - 07:36:08 CEST