ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20120221]CTAS与shared pool.txt

[20120221]CTAS与shared pool.txt

原创 Linux操作系统 作者:lfree 时间:2012-02-21 16:05:14 0 删除 编辑
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。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-716874/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2318
  • 访问量
    6046262