当前位置：移动技术网 > IT编程>数据库>Oracle > Oracle高级优化——侵入存储提纲（用PRIVATEOUTLINE）

Oracle高级优化——侵入存储提纲（用PRIVATEOUTLINE）

2018年03月24日 | 移动技术网IT编程 | 我要评论

重庆招生,bbpiano,山村小中医

Oracle高级优化——侵入存储提纲（用PRIVATEOUTLINE）

如果某语句当前的执行计划并不令人满意，而且这条语句又不能加hint（因为在应用中不能编辑）。那么可以用这种方法，使执行计划稳定在一个与当前执行计划不同的执行计划上。
SYS@ prod> conn hr/hr
Connected. 
为所要调整的语句用当前的执行计划（并不是想要的）创建一个outline。
HR@ prod> create outline pub_otln for category c1 on
  2  select last_name from employees1 where employee_id = 100 ;

Outline created.

创建一个与上面相同的私有OUTLINE。
HR@ prod> create private outline otln1 from pub_otln ;

Outline created.

通过向原来的语句中加入hints得到想要的执行计划，如下，使执行计划使用复合索引test_idx2。
HR@ prod> set autotrace on
HR@ prod> select /*+ index(employees1 test_idx2) */ last_name from employees1 where employee_id = 100 ;

LAST_NAME
-------------------------
King


Execution Plan
----------------------------------------------------------
Plan hash value: 2093088777

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    11 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES1 |     1 |    11 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX2  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("EMPLOYEE_ID"=100)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

HR@ prod> set autotrace off

为这条功能与原语句相同，但是加了hints的语句创建私有outline。
HR@ prod> create private outline otln2 on 
  2  select /*+ index(employees1 test_idx2) */ last_name from employees1 where employee_id = 100 ;

Outline created.

修改ol$hints表（临时表），使两个outline的ol_name字段对调。
HR@ prod> col ol_name for a30
HR@ prod> col hint_text for a100
HR@ prod> set linesize 150
HR@ prod> select ol_name , hint_text from ol$hints ;

OL_NAME                        HINT_TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
OTLN1                          OUTLINE_LEAF(@"SEL$1")
OTLN1                          IGNORE_OPTIM_EMBEDDED_HINTS
OTLN1                          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
OTLN1                          DB_VERSION('11.2.0.1')
OTLN1                          ALL_ROWS
OTLN1                          INDEX_RS_ASC(@"SEL$1" "EMPLOYEES1"@"SEL$1" ("EMPLOYEES1"."EMPLOYEE_ID"))
OTLN2                          INDEX_RS_ASC(@"SEL$1" "EMPLOYEES1"@"SEL$1" ("EMPLOYEES1"."EMPLOYEE_ID" "EMPLOYEES1"."FIRST_NAME"))
OTLN2                          OUTLINE_LEAF(@"SEL$1")
OTLN2                          ALL_ROWS
OTLN2                          DB_VERSION('11.2.0.1')
OTLN2                          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
OTLN2                          IGNORE_OPTIM_EMBEDDED_HINTS

12 rows selected.

HR@ prod> update ol$hints set ol_name = 'T' where ol_name = 'OTLN1' ;

6 rows updated.

HR@ prod> update ol$hints set ol_name = 'OTLN1' where ol_name = 'OTLN2' ;

6 rows updated.

HR@ prod> update ol$hints set ol_name = 'OTLN2' where ol_name = 'T' ;

6 rows updated.

结果如下：
HR@ prod> select ol_name , hint_text from ol$hints ;

OL_NAME                        HINT_TEXT
------------------------------ ----------------------------------------------------------------------------------------------------
OTLN2                          OUTLINE_LEAF(@"SEL$1")
OTLN2                          IGNORE_OPTIM_EMBEDDED_HINTS
OTLN2                          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
OTLN2                          DB_VERSION('11.2.0.1')
OTLN2                          ALL_ROWS
OTLN2                          INDEX_RS_ASC(@"SEL$1" "EMPLOYEES1"@"SEL$1" ("EMPLOYEES1"."EMPLOYEE_ID"))
OTLN1                          INDEX_RS_ASC(@"SEL$1" "EMPLOYEES1"@"SEL$1" ("EMPLOYEES1"."EMPLOYEE_ID" "EMPLOYEES1"."FIRST_NAME"))
OTLN1                          OUTLINE_LEAF(@"SEL$1")
OTLN1                          ALL_ROWS
OTLN1                          DB_VERSION('11.2.0.1')
OTLN1                          OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
OTLN1                          IGNORE_OPTIM_EMBEDDED_HINTS

12 rows selected.

修改ol$表，使hintcount字段对调。
HR@ prod> select ol_name , hintcount from ol$ ;

OL_NAME                         HINTCOUNT
------------------------------ ----------
OTLN1                                   6
OTLN2                                   6
这里两个相同，不用更改了。

启用私有outline，查看效果是否达到了。
HR@ prod> alter session set use_private_outlines = true ;

Session altered.

HR@ prod> set autotrace on
HR@ prod> select last_name from employees1 where employee_id = 100 ;

LAST_NAME
-------------------------
King


Execution Plan
----------------------------------------------------------
Plan hash value: 2093088777

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    11 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES1 |     1 |    11 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX2  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("EMPLOYEE_ID"=100)

Note
-----
   - outline "OTLN1" used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
可知，原语句已经使用了加了hints后的执行计划。

将结果固化（私有outline是临时的，放在临时表中，重连会话后就没有了）
用正确的private outline替换掉原来的outline（为了不至于同一语句出现两个outline）
HR@ prod> create or replace outline pub_otln from private otln1 for category c1 ;

Outline created.
HR@ prod> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dbsvr1 dbhome_1]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 8 08:58:49 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

重连，在会话启用outline，查看结果是否有效。
SYS@ prod> conn hr/hr
Connected. 

HR@ prod> alter session set use_stored_outlines=c1 ;

Session altered.
HR@ prod> set autotrace on
HR@ prod> select last_name from employees1 where employee_id = 100 ;

LAST_NAME
-------------------------
King


Execution Plan
----------------------------------------------------------
Plan hash value: 2093088777

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    11 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES1 |     1 |    11 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX2  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - access("EMPLOYEE_ID"=100)

Note
-----
   - outline "PUB_OTLN" used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
2rows processed
目标达成。

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！点击进行留言回复

Oracle TNS 协议分析：方法论及基础知识入门

本系列重点分析TNS 314下的客户端与服务端之间的通讯，通过抓包分析，查看在不同客户端，不同服务端情况下传输方式的不同，尝试还原其协议细节，实现对协议... [阅读全文]
快速向表插入大量数据

当需要对一个非常大的表INSERT的时候，会消耗非常多的资源，因为update表的时候，oracle需要生成 redo log和undo log;此时最... [阅读全文]
Oracle（PLSQL）入门学习八（完结篇）

学习视频：https://www.bilibili.com/video/BV1tJ411r7EC?p=75 游标cursor：用于存放多条数据的容器。需... [阅读全文]
Oracle（PLSQL）入门学习七

学习视频：https://www.bilibili.com/video/BV1tJ411r7EC?p=74 设计数据库步骤：良好的数据库设计：节省空间... [阅读全文]
Oracle入门学习五

学习视频： https://www.bilibili.com/video/BV1tJ411r7EC?p=55 数据的完整性：保证插入表格的数据必须正确。... [阅读全文]
Oracle入门学习六

事务：把一组操作看做一个工作单元，要么都执行，要么都不执行。dml操作才有事务，查询没有事务。开始事务：从上一次的事务结束之后，从第一次dml操作，就... [阅读全文]
Oracle字符串函数length、upper、lower、initcap、 concat、instr、replace

Oracle入门学习字符串函数：length、upper、lower、initcap、 concat、instr、replace。 -- dual 常量... [阅读全文]
Oracle表连接的内连接、外连接

Oracle表连接：内连接、外连接。外连接分左连接、右连接。多表查询时，如果表之间没有条件关联，则会把所有匹配的结果查找出来，例如A表6条数据，B表7... [阅读全文]
Oracle入门学习一

oracle的安装，用户授权，表格操作，数据类型，ddl表格，dml数据。下一篇：Oracle入门学习二学习视频：https://www.bilib... [阅读全文]
Oracle入门学习二

上一篇：Oracle入门学习一学习视频：https://www.bilibili.com/video/BV1tJ411r7EC?p=15 算术运算符：... [阅读全文]

网友评论


验证码：

Oracle高级优化——侵入存储提纲（用PRIVATEOUTLINE）

2018年03月24日 | 移动技术网IT编程 | 我要评论

Oracle高级优化——侵入存储提纲（用PRIVATEOUTLINE）

您可能感兴趣的文章:

相关文章:

网友评论