[20180724]Flashback query和子光标共享.txt

[20180724]Flashback query和子光标共享.txt --//看了链接:https://stewashton.wordpress.com/2018/07/23/optimistic-locking-8-double-checking-with-flashback/ --//没有想到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