ORACLE sql调整（二）

Stored Outlines

stored outline实际上是ORACLE存储的比较稳定的执行计划。
第一步：
Creating Outlines
修改系统/会话参数

-- Switch on automatic creation of stored outlines.
ALTER SYSTEM SET create_stored_outlines=TRUE;
ALTER SESSION SET create_stored_outlines=TRUE;

-- Switch on automatic creation of stored outlines.
ALTER SYSTEM SET create_stored_outlines=FALSE;
ALTER SESSION SET create_stored_outlines=FALSE;

赋予用户权限

-- Grant the necessary privileges.
CONN sys/password AS SYSDBA
GRANT CREATE ANY OUTLINE TO SCOTT;
GRANT EXECUTE_CATALOG_ROLE TO SCOTT;

CONN scott/tiger

-- 为特定的sql创建outline.
CREATE OUTLINE emp_dept FOR CATEGORY scott_outlines
ON SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;

--检查outline是否正确创建.
COLUMN name FORMAT A30
SELECT name, category, sql_text FROM user_outlines WHERE category = 'SCOTT_OUTLINES';

NAME                           CATEGORY
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
EMP_DEPT                       SCOTT_OUTLINES
SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno


1 row selected.

-- 列出所有的hint列表.
COLUMN hint FORMAT A50
SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = 'EMP_DEPT';

     NODE      STAGE   JOIN_POS HINT
---------- ---------- ---------- --------------------------------------------------
        1          1          0 NO_EXPAND(@"SEL$1" )
        1          1          0 PQ_DISTRIBUTE(@"SEL$1" "E"@"SEL$1" NONE NONE)
        1          1          0 USE_MERGE(@"SEL$1" "E"@"SEL$1")
        1          1          0 LEADING(@"SEL$1"  "D"@"SEL$1" "E"@"SEL$1")
        1          1          0 NO_STAR_TRANSFORMATION(@"SEL$1" )
        1          1          0 NO_FACT(@"SEL$1" "E"@"SEL$1")
        1          1          0 NO_FACT(@"SEL$1" "D"@"SEL$1")
        1          1          2 FULL(@"SEL$1" "E"@"SEL$1")
        1          1          1 INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
        1          1          0 NO_REWRITE(@"SEL$1" )
        1          1          0 NO_REWRITE(@"SEL$1" )

11 rows selected.

下面的方式使用DBMS_OUTLN.CREATE_OUTLINE的方式建立outline

-- 运行语句
SELECT e.empno, e.ename, d.dname, e.job FROM emp e, dept d WHERE e.deptno = d.deptno AND d.dname = 'SALES';

    EMPNO ENAME      DNAME          JOB
---------- ---------- -------------- ---------
     7499 ALLEN      SALES          SALESMAN
     7698 BLAKE      SALES          MANAGER
     7654 MARTIN     SALES          SALESMAN
     7900 JAMES      SALES          CLERK
     7844 TURNER     SALES          SALESMAN
     7521 WARD       SALES          SALESMAN

6 rows selected.

-- 获取hash_value
SELECT hash_value, child_number, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT e.empno, e.ename, d.dname, e.job%';

HASH_VALUE CHILD_NUMBER
---------- ------------
SQL_TEXT
----------------------------------------------------------------------------------------------------
3909283366            0
SELECT e.empno, e.ename, d.dname, e.job FROM emp e, dept d WHERE e.deptno = d.deptno AND d.dname = '
SALES'


1 row selected.

-- Create outline .
BEGIN
 DBMS_OUTLN.create_outline(
   hash_value    => 3909283366,
   child_number  => 0,
   category      => 'SCOTT_OUTLINES');
END;
/

-- 检查.
COLUMN name FORMAT A30
SELECT name, category, sql_text FROM user_outlines WHERE category = 'SCOTT_OUTLINES';

NAME                           CATEGORY
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SYS_OUTLINE_05092314510581419  SCOTT_OUTLINES
SELECT e.empno, e.ename, d.dname, e.job FROM emp e, dept d WHERE e.deptno = d.de

EMP_DEPT                       SCOTT_OUTLINES
SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno


2 rows selected.

-- list
COLUMN hint FORMAT A50
SELECT node, stage, join_pos, hint  FROM user_outline_hints WHERE name = 'SYS_OUTLINE_05092314510581419';


     NODE      STAGE   JOIN_POS HINT
---------- ---------- ---------- --------------------------------------------------
        1          1          0 NO_EXPAND(@"SEL$1" )
        1          1          0 PQ_DISTRIBUTE(@"SEL$1" "E"@"SEL$1" NONE NONE)
        1          1          0 USE_MERGE(@"SEL$1" "E"@"SEL$1")
        1          1          0 LEADING(@"SEL$1"  "D"@"SEL$1" "E"@"SEL$1")
        1          1          0 NO_STAR_TRANSFORMATION(@"SEL$1" )
        1          1          0 NO_FACT(@"SEL$1" "E"@"SEL$1")
        1          1          0 NO_FACT(@"SEL$1" "D"@"SEL$1")
        1          1          2 FULL(@"SEL$1" "E"@"SEL$1")
        1          1          1 INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
        1          1          0 NO_REWRITE(@"SEL$1" )

10 rows selected.

Using Outlines

--检查outline是否已经被使用
SELECT name, category, used FROM user_outlines;

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
SYS_OUTLINE_05092314510581419  SCOTT_OUTLINES                 UNUSED
EMP_DEPT                       SCOTT_OUTLINES                 UNUSED

2 rows selected.

-- 对下面的语句建大纲
SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
SELECT e.empno, e.ename, d.dname, e.job FROM emp e, dept d WHERE e.deptno = d.deptno AND d.dname = 'SALES';

-- 检查outline的使用情况
SELECT name, category, used FROM user_outlines;

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
SYS_OUTLINE_05092314510581419  SCOTT_OUTLINES                 UNUSED
EMP_DEPT                       SCOTT_OUTLINES                 UNUSED

2 rows selected.

-- 启用outline
ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET use_stored_outlines=SCOTT_OUTLINES;

-- 运行语句
SELECT e.empno, e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;
SELECT e.empno, e.ename, d.dname, e.job FROM emp e, dept d WHERE e.deptno = d.deptno AND d.dname = 'SALES';

-- 检查使用情况
SELECT name, category, used FROM user_outlines;

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
SYS_OUTLINE_05092314510581419  SCOTT_OUTLINES                 USED
EMP_DEPT                       SCOTT_OUTLINES                 USED

2 rows selected.

Dropping Outlines

BEGIN
 DBMS_OUTLN.drop_by_cat (cat => 'SCOTT_OUTLINES');
END;
/