Oracle Outline实用总结

SQL和数据库技术 2022-09-05

1190

编者按：即使很多东西不再使用，但是也许某一天突然会因此给你一些提示。

【免责声明】本公众号文章仅代表个人观点，与任何公司无关，仅供参考。

编辑|SQL和数据库技术(ID:SQLplusDB)

## 概述

Oracle Outline主要用于固定SQL的执行计划，是早期Oracle（10g之前）控制SQL执行计划的一种机制。

虽然11g以后Oracle推荐使用SPM、SQL Profile等手段控制执行计划，但是在某些旧系统中可能依然被使用。在这里进行一些实用方法的总结和介绍。

## 创建 Outline

### 方法1：create_stored_outlines

可以使用通过create_stored_outlines参数捕捉SQL执行时候的执行计划，并创建相应的Outline。

SQL> --开启Outline创建
SQL> alter session set create_stored_outlines = <CATEGORY名>;
或者
SQL> alter session set create_stored_outlines = TRUE;




<执行对象SQL>




SQL>--关闭Outline创建
SQL> alter session set create_stored_outlines = false;

例：

SQL> alter session set create_stored_outlines =test_outline;




会话已更改。




SQL> select count(*) from T_COUNT;




  COUNT(*)
----------
    100000




SQL> set linesize 200
SQL> set pagesize 1000
SQL> set long 30
SQL> col name format a30
SQL> col owner format a10
SQL> col category format a15
SQL> SELECT name,owner,category,used,enabled,sql_text
  2  FROM dba_outlines;




NAME                           OWNER      CATEGORY        USED         ENABLED          SQL_TEXT
------------------------------ ---------- --------------- ------------ ---------------- ------------------------------
SYS_OUTLINE_22090207473993401  SCOTT      TEST_OUTLINE    UNUSED       ENABLED          select count(*) from T_COUNT




SQL>  alter session set create_stored_outlines = false;




会话已更改。




SQL>

### 方法2：CREATE OUTLINE语句

可以通过CREATE OUTLINE语句对某个SQL创建Outline。

CREATE OR REPLACE  OUTLINE on <SQL 语句>;
或者
CREATE OR REPLACE PUBLIC OUTLINE on <SQL 语句>；
或者
CREATE OR REPLACE OUTLINE  <Outline名>  FOR CATEGORY <CATEGORY名>   on  <SQL 语句 >；

例：

SQL> CREATE OR REPLACE OUTLINE  ouline_name2  FOR CATEGORY ouline_category2   on  select count(*) from T_COUNT;




大纲已创建。




SQL> set linesize 200
SQL> set pagesize 1000
SQL> set long 30
SQL> col name format a30
SQL> col owner format a10
SQL> col category format a20
SQL> SELECT name,owner,category,used,enabled,sql_text
  2    FROM dba_outlines;




NAME                           OWNER      CATEGORY             USED         ENABLED          SQL_TEXT
------------------------------ ---------- -------------------- ------------ ---------------- ------------------------------
SYS_OUTLINE_22090207473993401  SCOTT      TEST_OUTLINE         UNUSED       ENABLED          select count(*) from T_COUNT
OULINE_NAME2                   SCOTT      OULINE_CATEGORY2     UNUSED       ENABLED          select count(*) from T_COUNT




SQL>
SQL> CREATE OR REPLACE  OUTLINE on select count(*) from T_COUNT;




大纲已创建。




SQL> SELECT name,owner,category,used,enabled,sql_text
  2    FROM dba_outlines;




NAME                           OWNER      CATEGORY             USED         ENABLED          SQL_TEXT
------------------------------ ---------- -------------------- ------------ ---------------- ------------------------------
SYS_OUTLINE_22090207473993401  SCOTT      TEST_OUTLINE         UNUSED       ENABLED          select count(*) from T_COUNT
OULINE_NAME2                   SCOTT      OULINE_CATEGORY2     UNUSED       ENABLED          select count(*) from T_COUNT
SYS_OUTLINE_22090208301749802  SCOTT      DEFAULT              UNUSED       ENABLED          select count(*) from T_COUNT

### 方法3：创建共享池中SQL语句的outline（dbms_outln.create_outline）

可以通过dbms_outln.create_outline程序包针对共享池中SQL语句创建outline。

-- 通过v$sql视图获取查询sql语句的hash_value和child_number
select sql_id,hash_value,child_number,sql_text from v$sql
where sql_text like '<SQL语句>%';
-- 通过dbms_outln.create_outline程序包创建outline
exec DBMS_OUTLN.create_outline(hash_value=><hash_value>,child_number => <child_number>,category=>'<自定义名>);

例：

SQL> col sql_text format a30
SQL> select sql_id,hash_value,child_number,sql_text from v$sql
  2  where sql_text like 'select count(*) from T_COUNT%';




SQL_ID                     HASH_VALUE CHILD_NUMBER SQL_TEXT
-------------------------- ---------- ------------ ------------------------------
7rcxfv4rk3zrd               790757101            0 select count(*) from T_COUNT




SQL> exec DBMS_OUTLN.create_outline(hash_value=>790757101,child_number => 0,category=>'cursor_test');




PL/SQL 过程已成功完成。




SQL> SELECT name,owner,category,used,enabled,sql_text
  2     FROM dba_outlines;




NAME                           OWNER      CATEGORY             USED         ENABLED          SQL_TEXT
------------------------------ ---------- -------------------- ------------ ---------------- ------------------------------
SYS_OUTLINE_22090207473993401  SCOTT      TEST_OUTLINE         UNUSED       ENABLED          select count(*) from T_COUNT
OULINE_NAME2                   SCOTT      OULINE_CATEGORY2     UNUSED       ENABLED          select count(*) from T_COUNT
SYS_OUTLINE_22090208301749802  SCOTT      DEFAULT              UNUSED       ENABLED          select count(*) from T_COUNT
SYS_OUTLINE_22090309385593401  SCOTT      cursor_test          UNUSED       ENABLED          select count(*) from T_COUNT


SQL>

参考：

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_OUTLN.html
>CREATE_OUTLINE Procedure
DBMS_OUTLN.CREATE_OUTLINE (
   hash_value    IN NUMBER,
   child_number  IN NUMBER,
   category      IN VARCHAR2 DEFAULT 'DEFAULT');`

## 使用outline

可以通过use_stored_outlines参数使用相应的Outline。

alter session set use_stored_outlines=TRUE/FALSE/<category名>;

例：

SQL> alter session set use_stored_outlines=TRUE;




会话已更改。




SQL> set autotrace on
SQL> select count(*) from T_COUNT;




  COUNT(*)
----------
    100000








执行计划
----------------------------------------------------------
Plan hash value: 3211753073




-----------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |   104   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_C1 |   100K|   104   (1)| 00:00:01 |
-----------------------------------------------------------------------




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








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




SQL>

设置use_stored_outlines为TRUE时，使用了默认outline "SYS_OUTLINE_22090208301749802"。

## 删除Outline

可以通过drop outline删除指定名的outline、dbms_outln某个类别下的outline。

drop outline <NAME名>；
exec dbms_outln.drop_by_cat(cat=>'<category名>');

例：

SQL> set linesize 200
SQL> set pagesize 1000
SQL> set long 30
SQL> col name format a30
SQL> col owner format a10
SQL> col category format a20
SQL> SELECT name,owner,category,used,enabled,sql_text
  2     FROM dba_outlines;




NAME                           OWNER      CATEGORY             USED         ENABLED          SQL_TEXT
------------------------------ ---------- -------------------- ------------ ---------------- ------------------------------
SYS_OUTLINE_22090207473993401  SCOTT      TEST_OUTLINE         UNUSED       ENABLED          select count(*) from T_COUNT
OULINE_NAME2                   SCOTT      OULINE_CATEGORY2     UNUSED       ENABLED          select count(*) from T_COUNT
SYS_OUTLINE_22090208301749802  SCOTT      DEFAULT              USED         ENABLED          select count(*) from T_COUNT
SYS_OUTLINE_22090309385593401  SCOTT      cursor_test          UNUSED       ENABLED          select count(*) from T_COUNT




SQL> drop outline OULINE_NAME2;




大纲已删除。




SQL> SELECT name,owner,category,used,enabled,sql_text
  2     FROM dba_outlines;




NAME                           OWNER      CATEGORY             USED         ENABLED          SQL_TEXT
------------------------------ ---------- -------------------- ------------ ---------------- ------------------------------
SYS_OUTLINE_22090207473993401  SCOTT      TEST_OUTLINE         UNUSED       ENABLED          select count(*) from T_COUNT
SYS_OUTLINE_22090208301749802  SCOTT      DEFAULT              USED         ENABLED          select count(*) from T_COUNT
SYS_OUTLINE_22090309385593401  SCOTT      cursor_test          UNUSED       ENABLED          select count(*) from T_COUNT




SQL> exec dbms_outln.drop_by_cat(cat=>'cursor_test');




PL/SQL 过程已成功完成。




SQL> SELECT name,owner,category,used,enabled,sql_text
  2     FROM dba_outlines;




NAME                           OWNER      CATEGORY             USED         ENABLED          SQL_TEXT
------------------------------ ---------- -------------------- ------------ ---------------- ------------------------------
SYS_OUTLINE_22090207473993401  SCOTT      TEST_OUTLINE         UNUSED       ENABLED          select count(*) from T_COUNT
SYS_OUTLINE_22090208301749802  SCOTT      DEFAULT              USED         ENABLED          select count(*) from T_COUNT




SQL>

## Outline迁移到SPM

对于版本升级等场景可以通过dbms_spm.migrate_stored_outline将现有stored outline迁移为SQL执行计划基线（SPM baseline）。





DBMS_SPM.MIGRATE_STORED_OUTLINE (
   attribute_name     IN  VARCHAR2,
   attribute_value    IN  CLOB,
   fixed              IN  VARCHAR2 := 'NO')
 RETURN CLOB;




DBMS_SPM.MIGRATE_STORED_OUTLINE (
   outln_list         IN  DBMS_SPM.NAME_LIST,
   fixed              IN  VARCHAR2 := 'NO')
 RETURN CLOB;

参考：

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SPM.html#GUID-9BB5FE07-DBB7-4EB2-851C-9E80251EB676

例：





SQL> SELECT name,owner,category,used,enabled,sql_text
  2     FROM dba_outlines;




NAME                           OWNER      CATEGORY             USED         ENABLED          SQL_TEXT
------------------------------ ---------- -------------------- ------------ ---------------- ------------------------------
SYS_OUTLINE_22090207473993401  SCOTT      TEST_OUTLINE         UNUSED       ENABLED          select count(*) from T_COUNT
SYS_OUTLINE_22090208301749802  SCOTT      DEFAULT              USED         ENABLED          select count(*) from T_COUNT




SQL> DECLARE
  2    my_report CLOB;
  3  BEGIN
  4    my_report := DBMS_SPM.MIGRATE_STORED_OUTLINE(
  5    attribute_name => 'CATEGORY',
  6    attribute_value => 'TEST_OUTLINE'
  7    );
  8  END;
  9  /




PL/SQL 过程已成功完成。




SQL> col ORIGIN format a15
SQL> set linesize 200
SQL> col PLAN_NAME format a20
SQL> col SQL_HANDLE format a30
SQL> col MODULE format a20
SQL> col ORIGIN format a15
SQL>
SQL> SELECT SQL_HANDLE, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, MODULE,ORIGIN,sql_text
  2       FROM DBA_SQL_PLAN_BASELINES
  3       where ORIGIN ='STORED-OUTLINE';




SQL_HANDLE                     PLAN_NAME            ORIGIN          ENABLE ACCEPT FIXED  MODULE               ORIGIN          SQL_TEXT
------------------------------ -------------------- --------------- ------ ------ ------ -------------------- --------------- ------------------------------
SQL_27d9fb2586cd731d           SYS_OUTLINE_22090207 STORED-OUTLINE  YES    YES    NO     TEST_OUTLINE         STORED-OUTLINE  select count(*) from T_COUNT
                               473993401

## outline相关的视图

outline相关的视图包括如下：

基本视图：dba_outlines,dba_outline_hints
内部表：ol$、ol$hints、ol$nodes

例：

SQL>  select name,hint from dba_outline_hints;




NAME                           HINT
------------------------------ ------------------------------
SYS_OUTLINE_22090207473993401  INDEX_FFS(@"SEL$1" "T_COUNT"@"
SYS_OUTLINE_22090207473993401  OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_22090207473993401  ALL_ROWS
SYS_OUTLINE_22090207473993401  DB_VERSION('19.1.0')
SYS_OUTLINE_22090207473993401  OPTIMIZER_FEATURES_ENABLE('19.
SYS_OUTLINE_22090207473993401  IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_22090208301749802  INDEX_FFS(@"SEL$1" "T_COUNT"@"
SYS_OUTLINE_22090208301749802  OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_22090208301749802  ALL_ROWS
SYS_OUTLINE_22090208301749802  DB_VERSION('19.1.0')
SYS_OUTLINE_22090208301749802  OPTIMIZER_FEATURES_ENABLE('19.
SYS_OUTLINE_22090208301749802  IGNORE_OPTIM_EMBEDDED_HINTS




已选择 12 行。

网罗收集10046的各种Case，方便trace信息的收集 | Oracle官方博客转载

供收藏：Oracle固定SQL执行计划的方法总结

Oracle SQL 性能调优：使用Hint固定执行计划1(Hash Join)

Oracle SQL 性能调优：使用Hint固定执行计划2(Nested Loop Join)

Oracle SQL 性能调优：使用SqlPatch固定执行计划

Oracle SQL 性能调优：使用SqlPatch固定执行计划（二）19c

文章转载自SQL和数据库技术，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle Outline实用总结

评论