[20180724]Flashback query和子游標共享.txt

[20180724]Flashback query和子游標共享.txt

--//看了連結:
--//沒有想到as of scn的Flashback query會產生子游標,我自己重複演示看看.

1.環境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table deptx as select * from dept;
Table created.

SCOTT@test01p> select current_scn from v$database;
CURRENT_SCN
-----------
   27958721

SCOTT@test01p> update deptx set dname=lower(dname);
4 rows updated.

SCOTT@test01p> commit ;
Commit complete.

2.測試:
select * from deptx as of scn 27958721;
/
/

--//執行3次!!並且顯示dname的資訊是大寫.

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2vsddq1rb1unp, child number 2
-------------------------------------
select * from deptx as of scn 27958721

Plan hash value: 428208148

----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |       |     5 (100)|          |
|   1 |  TABLE ACCESS FULL| DEPTX |      4 |    80 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPTX@SEL$1
--//可以發現child number 2,生成許多籽游標.
--//看看為什麼不能共享:
SCOTT@test01p> @ share  2vsddq1rb1unp
SQL_TEXT                       = select * from deptx as of scn 27958721
SQL_ID                         = 2vsddq1rb1unp
ADDRESS                        = 000007FF22E2AD08
CHILD_ADDRESS                  = 000007FF22E2D9A0
CHILD_NUMBER                   = 0
REASON                         = <ChildNode><ChildNumber>0</ChildNumber><ID>21</ID><reason>Flashback
cursor(1)</reason><size>3x4</size><Flashback_cursor>0</Flashback_cursor><As_of_cursor>1</As_of_cursor><Flashback_session>0</Flashback_session></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select * from deptx as of scn 27958721
SQL_ID                         = 2vsddq1rb1unp
ADDRESS                        = 000007FF22E2AD08
CHILD_ADDRESS                  = 000007FF24B30148
CHILD_NUMBER                   = 1
FLASHBACK_CURSOR               = Y
REASON                         = <ChildNode><ChildNumber>1</ChildNumber><ID>21</ID><reason>Flashback
cursor(1)</reason><size>3x4</size><Flashback_cursor>0</Flashback_cursor><As_of_cursor>1</As_of_cursor><Flashback_session>0</Flashback_session></ChildNode>
--------------------------------------------------
SQL_TEXT                       = select * from deptx as of scn 27958721
SQL_ID                         = 2vsddq1rb1unp
ADDRESS                        = 000007FF22E2AD08
CHILD_ADDRESS                  = 000007FF22FEC3B8
CHILD_NUMBER                   = 2
FLASHBACK_CURSOR               = Y
REASON                         =
--------------------------------------------------
PL/SQL procedure successfully completed.

--//不能共享的原因是FLASHBACK_CURSOR.

3.如果寫成這樣:
SCOTT@test01p> alter system flush shared_pool;
System altered.

SCOTT@test01p> exec dbms_flashback.enable_at_system_change_number(27958721);
PL/SQL procedure successfully completed.

select * from deptx ;
/
/

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  58ckg85gng2n7, child number 0
-------------------------------------
select * from deptx

Plan hash value: 428208148

----------------------------------------------------------------------------
| Id  | Operation         | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |        |       |     5 (100)|          |
|   1 |  TABLE ACCESS FULL| DEPTX |      4 |    80 |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / DEPTX@SEL$1

--//可以發現這樣不會生成子游標.
--//收尾:
SCOTT@test01p> exec dbms_flashback.disable;
PL/SQL procedure successfully completed.

select child_number, parse_calls, executions, reason "DBMS_FLASHBACK, increasing SCN"
from v$sql
left join V$SQL_SHARED_CURSOR using(sql_id, child_number)
where sql_id='58ckg85gng2n7'

CHILD_NUMBER PARSE_CALLS EXECUTIONS DBMS_FLASHBACK, increasing SCN
------------ ----------- ---------- --------------------------------
           0           3          3