Database Reference
In-Depth Information
14:35:15 SQL> exec dbms_lock.sleep(20);
PL/SQL procedure successfully completed.
14:35:35 SQL>
14:35:35 SQL> -- End of invalidation window. A new cursor will be created.
14:35:35 SQL>
14:35:35 SQL> select to_char(cast(sys_extract_utc(systimestamp) as date),'dd.mm.yyyy
hh24:mi:ss') sysdate_utc from dual;
SYSDATE_UTC
-------------------
10.06.2013 13:35:35
14:35:35 SQL> select count(*) from t1 where a=1;
COUNT(*)
----------
1
14:35:35 SQL> select plan_table_output from table ( sys.dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
------------------
SQL_ID 5p7s1hq62rf59, child number 1
-------------------------------------
select count(*) from t1 where a=1
Plan hash value: 3547404373
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| I_T1 | 1 | 4 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------
L.: „
Im Test-Case werden noch 2 Ausführungen der SQL-Anweisung gemacht. Den kom-
pletten Ablauf kannst Du selber verfolgen, wenn Du das Skript test_case_delayed_inva-
lidation_of_cursors.sql ausführst. Zum Schluss werden die jeweiligen Daten aus der View
V$SQL_SHARED_CURSOR ermittelt.
“
14:36:26 SQL> select child_number, roll_invalid_mismatch, reason from v$sql_shared_cursor
where sql_id='5p7s1hq62rf59';
CHILD_NUMBER R
------------ -
REASON
----------------------------------------------------------------------------------------------
------------------------------------------------------------
0 N
<ChildNode><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window
Exceeded(2)</reason><size>0x0</size><details>already_processed</details></ChildNode><ChildNode
><ChildNumber>0</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window
Exceeded(3)</reason><size>2x4</size><invalidation_window>1370871331</invalidation_window><ksug
ctm>1370871334</ksugctm></ChildNode>
1 Y
<ChildNode><ChildNumber>1</ChildNumber><ID>34</ID><reason>Rolling Invalidate Window
Exceeded(3)</reason><size>2x4</size><invalidation_window>1370871372</invalidation_window><ksug
ctm>1370871385</ksugctm></ChildNode>
2 Y
