把两个SQL的Outline进行交换，用效率高的执行计划替换效率差的执行计划

最新推荐文章于 2019-07-29 01:00:00 发布

conglan2816

最新推荐文章于 2019-07-29 01:00:00 发布

阅读量156

点赞数

How to Edit a Stored Outline to Use the Plan from Another Stored Outline [ID 730062.1]

根据ORACLE文档自己做了个OUTLINE，测试了一天，基本上算是有戏吧

早上在WINDOWS oracle 10.2.0.4上搞了半天，执行计划的NOTE是提示已经使用了OUTLINE了，但

从实际的执行计划看还是按原来老的计划在执行。在METALINK中查了，发现是个BUG，没法下载补丁测试，转到10.2。0.3上继续，这次还算是有点戏，但执行计划的NOTE并不提示已经使用了OUTLINE，实际上已经使用了，从SQL执行速度上以及统计信息中可以看出来。

Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>sqlplus dbo/www@nbtos

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 6月 12 16:48:10 2010

ERROR:
ORA-12154: TNS: 无法解析指定的连接标识符

C:\Documents and Settings\Administrator>
C:\Documents and Settings\Administrator>
C:\Documents and Settings\Administrator>sqlplus dbo/www@nbtostest

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 6月 12 16:48:24 2010

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

----------------没创建OUTLINE前

SQL> var eirno varchar2(20);
SQL> exec :eirno:='232342523'

PL/SQL 过程已成功完成。

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

SQL> select /*+ full(a) */* from eirhis a where a.eirno=:eirno;

未选定行

SQL> set autot traceonly
SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

------------------------------------------------------------------------------
| Id | Operation                   | Name      | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |   283 |     3   (0)|
|   1 | TABLE ACCESS BY INDEX ROWID| EIRHIS    |     1 |   283 |     3   (0)|
|* 2 |   INDEX UNIQUE SCAN         | PK_EIRHIS |     1 |       |     2   (0)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          3 consistent gets
          0 physical reads
          0 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> select /*+ full(a) */* from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

-----------------------------------------------------------------
| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |        |     1 |   283 | 17773   (1)|
|* 1 | TABLE ACCESS FULL| EIRHIS |     1 |   283 | 17773   (1)|
-----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      84863 consistent gets
      82885 physical reads
          0 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

----从上面统计数据看全表扫描与索引扫描的差别还是很大的

SQL> alter session set create_stored_outline=true;
alter session set create_stored_outline=true
*
第 1 行出现错误:
ORA-02248: 无效的 ALTER SESSION 选项

SQL> alter session set create_stored_outlines=true;

会话已更改。

-- 从v$sql中找到上面执行的两个SQL对应的hash_value用于创建下面的OUTLINE

SQL> exec dbms_outln.create_outline(2167656650,0);

PL/SQL 过程已成功完成。

SQL> exec dbms_outln.create_outline(3939794144,0);

PL/SQL 过程已成功完成。

SQL> alter session set create_stored_outlines=false
2 ;

会话已更改。

SQL> alter outline SYS_OUTLINE_06021823590426502 rename to ol_inx;

大纲已变更。

SQL> alter outline SYS_OUTLINE_06021823592745304 rename to ol_full;

大纲已变更。

SQL> alter outline ol_inx change category to mycat;

大纲已变更。

SQL> alter outline ol_full change category to mycat;

大纲已变更。

SQL> create private outline p_ol_inx from ol_inx;

大纲已创建。

SQL> create private outline p_ol_full from ol_full;

大纲已创建。

SQL> select ol_name from ol$;

执行计划
----------------------------------------------------------

---------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     2 |    34 |     2   (0)|
|   1 | TABLE ACCESS FULL| OL$ |     2 |    34 |     2   (0)|
---------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
          5 recursive calls
          0 db block gets
         10 consistent gets
          0 physical reads
          0 redo size
        463 bytes sent via SQL*Net to client
        385 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          2 rows processed

SQL> set autot off
SQL> select ol_name from ol$;

OL_NAME
------------------------------
P_OL_INX
P_OL_FULL

---删除全表扫描的这个，其实这个是我最后想用的，我就是想用全表扫描而不用EIR表的那个索引

--再加一步

update ol$ set hintcount=(select hintcount from ol$ where ol_name='P_OL_FULL') WHERE OL_NAME='P_OL_INX';

--然后再执行下面的删除，因为有时两个OUTLINE的HINTCOUNT并不一定相同。

SQL> delete ol$ where ol_name='P_OL_FULL';

已删除 1 行。

--将原来走索引的那个OUTLINE改名为走全表扫描的OUTLINE，这样走索引的那个SQL对应的OL$HINTS信息则会变为走全表扫描的那个OUTLINE的HINTS信息。

SQL> UPDATE OL$ SET OL_NAME='P_OL_FULL' WHERE OL_NAME='P_OL_INX";
ERROR:
ORA-01756: 引号内的字符串没有正确结束

SQL> UPDATE OL$ SET OL_NAME='P_OL_FULL' WHERE OL_NAME='P_OL_INX';

已更新 1 行。

SQL> COMMIT;

提交完成。

----OUTLINE创建完成，实验。。。。。。

SQL> ALTER SESSION SET USE_PRIVATE_OUTLINES=TRUE;

会话已更改。

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

SQL> SET AUTOT TRACEONLY;
SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      84863 consistent gets----很大
      82884 physical reads
          0 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL>

会话已更改。

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      84863 consistent gets---很大
      82884 physical reads
          0 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

---这样看来，上面的结果看上去还是根据想要的来执行的

SQL> CREATE OR REPLACE OUTLINE OL_INX FROM PRIVATE P_OL_FULL FOR CATEGOREY MYCAT;
CREATE OR REPLACE OUTLINE OL_INX FROM PRIVATE P_OL_FULL FOR CATEGOREY MYCAT
*
第 1 行出现错误:
ORA-18010: 命令丢失必须的 CATEGORY 关键字

SQL> CREATE OR REPLACE OUTLINE OL_INX FROM PRIVATE P_OL_FULL FOR CATEGORY MYCAT;

大纲已创建。

SQL> ALTER SESSION SET USE_PRIVATE_OUTLINES=FALSE;

会话已更改。

--使用公共的OUTLINE，以前用的都是PRIVATE的OUTLINE

SQL> ALTER SESSION SET USE_STORED_OUTLINES=MYCAT;

会话已更改。

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
        159 recursive calls
        123 db block gets
      84920 consistent gets---很大
      82883 physical reads
        568 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      84863 consistent gets
      82882 physical reads
          0 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

系统已更改。

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
       6386 recursive calls
        120 db block gets
      86376 consistent gets
      82914 physical reads
          0 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
        158 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      84863 consistent gets
      82883 physical reads
          0 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> select /*+ INDEX(A) */* from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
        127 recursive calls
          0 db block gets
         40 consistent gets---很小
          0 physical reads
          0 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

SQL>

---从上面看，虽然没有出现NOTE信息以证明，执行用的是全表扫描，但统计数据上看，明显是用的全表扫描。

---------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
--换到另一个窗口，可是我还没有设置use_stored_outline呢，怎么就也用上了？？？

C:\Documents and Settings\Administrator>SQLPLUS dbo/www@nbtostest

SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 6月 12 17:21:56 2010

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set autot traceonly
SQL> var eirno varchar2(20)
SQL> exec :eirno:='2324235'

PL/SQL 过程已成功完成。

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      84863 consistent gets
      82884 physical reads
          0 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> alter system set use_stored_outlines=false;

系统已更改。

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

---上面的这个测试比较怪异，计划上显示是表索引，但统计信息表时是全表扫描的数据呀。。。。

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      84863 consistent gets
      82880 physical reads
          0 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> alter system set use_stored_outlines=mycat
2 ;

系统已更改。

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
          0 recursive calls
          0 db block gets
      84863 consistent gets
      82879 physical reads
          0 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> drop outline ol_inx;

大纲已删除。

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

统计信息
----------------------------------------------------------
        133 recursive calls
          0 db block gets
         42 consistent gets
          0 physical reads
          0 redo size
       6514 bytes sent via SQL*Net to client
        374 bytes received via SQL*Net from client
          1 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          0 rows processed

SQL> select * from eirhis a where a.eirno=:eirno;

未选定行

执行计划
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."EIRNO"=:EIRNO)

Note
-----
- 'PLAN_TABLE' is old version

SQL>

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

转载于:http://blog.itpub.net/280958/viewspace-665066/

conglan2816

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
把两个SQL的Outline进行交换，用效率高的执行计划替换效率差的执行计划

How to Edit a Stored Outline to Use the Plan from Another Stored Outline [ID 730062.1]根据ORACLE文档自己做...
复制链接

扫一扫