[20120221]CTAS与shared pool.txt

1.在10g下测试:

SQL> select * from v$version ;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

PL/SQL Release 10.2.0.3.0 - Production

CORE    10.2.0.3.0      Production

TNS for Linux: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

SQL> create table t1 as select * from emp ;

Table created.

SQL> @dpc

PLAN_TABLE_OUTPUT

---------------------------------------

SQL_ID  g9djabjtgrnft, child number 0

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: g9djabjtgrnft, CHILD_NUMBER: 0

      Please verify value of SQL_ID and CHILD_NUMBER;

      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

8 rows selected.

SQL> select * from v$sql where sql_id='g9djabjtgrnft';

no rows selected

--可以发现V$sql视图并没有记录这个执行语句。

--另外如果你drop表，在重复执行上面的ctas语句，可以发现child number一直是等于0.

查询基表：

SQL> column kglnaobj format a60

SQL> SELECT addr, indx, inst_id, kglhdpar, kglnaobj  from  x$kglcursor_child WHERE kglobt03 = 'g9djabjtgrnft';

no rows selected

SQL> SELECT addr, indx, inst_id, kglhdpar, kglnaobj FROM SYS.x$kglob WHERE kglobt03 = 'g9djabjtgrnft';

ADDR                   INDX    INST_ID KGLHDPAR         KGLNAOBJ

---------------- ---------- ---------- ---------------- ------------------------------------------------------------

0000002A972F47D0          0          1 00000000D3BFEBD0 create table t1 as select * from emp

0000002A972F5B00          1          1 00000000D3BFEBD0 create table t1 as select * from emp

2.在11g下测试:

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t1 as select * from emp ;

Table created.

SQL> @dpc

PLAN_TABLE_OUTPUT

-------------------------------------

SQL_ID  g9djabjtgrnft, child number 2

-------------------------------------

create table t1 as select * from emp

Plan hash value: 2748781111

----------------------------------------------------------------------------------------

| Id  | Operation              | Name | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |

----------------------------------------------------------------------------------------

|   0 | CREATE TABLE STATEMENT |      |        |     4 (100)|       |       |          |

|   1 |  LOAD AS SELECT        |      |        |            |   269K|   269K|  269K (0)|

|   2 |   TABLE ACCESS FULL    | EMP  |     14 |     3   (0)|       |       |          |

----------------------------------------------------------------------------------------

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

20 rows selected.

--注意我已经执行了两次！这次是第3次！child number=2.

SQL> column sql_text format a60

SQL> select  SQL_TEXT , SQL_ID ,child_number from v$sql where sql_id='g9djabjtgrnft';

SQL_TEXT                                                     SQL_ID        CHILD_NUMBER

------------------------------------------------------------ ------------- ------------

create table t1 as select * from emp                         g9djabjtgrnft            0

create table t1 as select * from emp                         g9djabjtgrnft            1

create table t1 as select * from emp                         g9djabjtgrnft            2

SQL> column kglnaobj format a60

SQL> SELECT addr, indx, inst_id, kglhdpar, kglnaobj  from  x$kglcursor_child WHERE kglobt03 = 'g9djabjtgrnft';

ADDR                   INDX    INST_ID KGLHDPAR         KGLNAOBJ

---------------- ---------- ---------- ---------------- ------------------------------------------------------------

0000002A971C4FD0          0          1 0000000095CBB208 create table t1 as select * from emp

0000002A971C6078          1          1 0000000095CBB208 create table t1 as select * from emp

0000002A971C7138          2          1 0000000095CBB208 create table t1 as select * from emp

SQL> SELECT addr, indx, inst_id, kglhdpar, kglnaobj FROM SYS.x$kglob WHERE kglobt03 = 'g9djabjtgrnft';

ADDR                   INDX    INST_ID KGLHDPAR         KGLNAOBJ

---------------- ---------- ---------- ---------------- ------------------------------------------------------------

0000002A97277D58          0          1 0000000095CBB208 create table t1 as select * from emp

0000002A97278E00          1          1 0000000095CBB208 create table t1 as select * from emp

0000002A97279EA8          2          1 0000000095CBB208 create table t1 as select * from emp

0000002A9727AF68          3          1 0000000095CBB208 create table t1 as select * from emp

--可以发现11g下ctas的操作也记录在v$sql中，而在10g执行完成后，就退出了shared pool。