sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 开发DBA >

【学习笔记】Oracle数据库outlines测试笔记与使用案例

时间:2016-11-13 19:44   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Outlines的使用笔记,outlines 特性在oracle 8i就引入,此文章详细介绍该功能使用案例。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 关于outline的一点测试和总结

我们知道 outlines 特性在oracle 8i就引入了,如下是做的简单测试。
首先来看看2个跟outline相关的参数:

create_stored_outlines — 控制是否自动创建outline
use_stored_outlines — 控制是否启用outline


使用outline的方式有很多种,列出如下几种方式:

1. 针对sql语句或sqlid

SQL> conn roger/roger
Connected.

SQL> ALTER SESSION SET create_stored_outlines = TRUE;

SESSION altered.

SQL> SELECT * FROM v$version WHERE rownum <2;

BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - Prod

SQL> CREATE OUTLINE test_id FOR CATEGORY test_outlines ON
  2  SELECT owner,object_id
  3  FROM test_ht
  4  WHERE object_id=:p;

Outline created.

SQL> SELECT name,category,sql_text FROM user_outlines WHERE category=UPPER('test_outlines');

NAME      CATEGORY          SQL_TEXT                                             
--------- ----------------- -------------------------------------------------------
TEST_ID   TEST_OUTLINES     SELECT owner,object_id FROM test_ht WHERE object_id=:p

SQL> SELECT * FROM user_outline_hints WHERE name=UPPER('test_id');

NAME    NODE  STAGE JOIN_POS HINT                                                          
------- ---- ------ -------- ----------------------------------------------------------------
TEST_ID    1      1        1 INDEX_RS_ASC(@"SEL$1" "TEST_HT"@"SEL$1" ("TEST_HT"."OBJECT_ID"))
TEST_ID    1      1        0 OUTLINE_LEAF(@"SEL$1")
TEST_ID    1      1        0 ALL_ROWS
TEST_ID    1      1        0 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
TEST_ID    1      1        0 IGNORE_OPTIM_EMBEDDED_HINTS

SQL> SELECT name,category,used FROM user_outlines WHERE category=UPPER('test_outlines');

NAME            CATEGORY                       USED
--------------- ------------------------------ ------
TEST_ID         TEST_OUTLINES                  UNUSED

-- 该outline未使用

SQL> var p NUMBER;
SQL> EXEC :p :=1000;

PL/SQL PROCEDURE successfully completed.

SQL> SELECT name,category,used FROM user_outlines WHERE category=UPPER('test_outlines');

NAME            CATEGORY                       USED
--------------- ------------------------------ ------
TEST_ID         TEST_OUTLINES                  UNUSED

-- 设置参数use_stored_outlines

SQL> var p NUMBER;
SQL> EXEC :p :=10000;

PL/SQLOracleо PROCEDURE successfully completed.

SQL> SELECT owner,object_id FROM test_ht WHERE object_id=:p;

OWNER                           OBJECT_ID
------------------------------ ----------
WMSYS                               10000

SQL>  SELECT name,category,used FROM user_outlines WHERE category=UPPER('test_outlines');

NAME            CATEGORY                       USED
--------------- ------------------------------ ------
TEST_ID         TEST_OUTLINES                  UNUSED

SQL> ALTER SESSION SET use_stored_outlines=TEST_OUTLINES;

SESSION altered.

SQL>  var p NUMBER;
SQL> EXEC :p :=20000;

PL/SQL PROCEDURE successfully completed.

SQL> SELECT owner,object_id FROM test_ht WHERE object_id=:p;

OWNER                           OBJECT_ID
------------------------------ ----------
SYS                                 20000

SQL> SELECT name,category,used FROM user_outlines WHERE category=UPPER('test_outlines');

NAME            CATEGORY                       USED
--------------- ------------------------------ ------
TEST_ID         TEST_OUTLINES                  USED

SQL> SELECT sql_id,sql_text FROM v$sqlarea WHERE sql_text LIKE '%select owner,object_id%';

SQL_ID        SQL_TEXT
------------- ---------------------------------------------------------------------------
3v6z2kwca0ttm SELECT sql_id,sql_text FROM v$sqlarea WHERE sql_text LIKE '%select owner,ob
              ject_id%'

45s1gxyr1y5k3 SELECT owner,object_id FROM test_ht WHERE object_id=:p
6cc34dzmkg686 CREATE TABLE test_ht AS SELECT owner,object_id,object_name FROM dba_objects

SQL> SELECT * FROM TABLE(dbms_xplan.DISPLAY_CURSOR('&sql_id',NULL));

Enter VALUE FOR sql_id: 45s1gxyr1y5k3
OLD   1: SELECT * FROM TABLE(dbms_xplan.DISPLAY_CURSOR('&sql_id',NULL))
NEW   1: SELECT * FROM TABLE(dbms_xplan.DISPLAY_CURSOR('45s1gxyr1y5k3',NULL))

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
SQL_ID  45s1gxyr1y5k3, child NUMBER 0
-------------------------------------
SELECT owner,object_id FROM test_ht WHERE object_id=:p

Plan hash VALUE: 793292976

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | ROWS  | Bytes | Cost (%CPU)| TIME     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HT |     1 |     9 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | IDX_ID  |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=:P)

SQL_ID  45s1gxyr1y5k3, child NUMBER 1
-------------------------------------
SELECT owner,object_id FROM test_ht WHERE object_id=:p

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

Plan hash VALUE: 793292976

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | ROWS  | Bytes | Cost (%CPU)| TIME     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HT |     1 |     9 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ID  |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=:P)

Note
-----
   - outline "TEST_ID" used FOR this statement

42 ROWS selected.

-- 对于存在的cursor创建outline

SQL> SELECT owner,object_id,object_name FROM test_ht WHERE object_id=3000;

OWNER           OBJECT_ID  OBJECT_NAME
-------------- ----------  --------------------------
SYS                  3000  EXU8SYNU


SQL> SELECT sql_id,sql_text FROM v$sqlarea WHERE sql_text LIKE '%select owner,object_id%';

SQL_ID        SQL_TEXT
------------- ---------------------------------------------------------------------------
3v6z2kwca0ttm SELECT sql_id,sql_text FROM v$sqlarea WHERE sql_text LIKE '%select owner,ob
              ject_id%'

8gz444rhg594f SELECT owner,object_id,object_name FROM test_ht WHERE object_id=3000

SQL> SELECT hash_value, child_number, sql_text FROM v$sql WHERE sql_text LIKE '%select owner,object_id%';

HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------ ---------------------------------------------------------------------------
413165363            0 SELECT sql_id,sql_text FROM v$sqlarea WHERE sql_text LIKE '%select owner,ob
                        ject_id%'

  47485093            0 SELECT hash_value, child_number, sql_text FROM v$sql WHERE sql_text LIKE '%
                        select owner,object_id%'

3773998222            0 SELECT owner,object_id,object_name FROM test_ht WHERE object_id=3000

SQL> EXEC dbms_outln.create_outline(3773998222,0);

PL/SQL PROCEDURE successfully completed.

SQL> SELECT owner,object_id,object_name FROM test_ht WHERE object_id=3000;

OWNER                           OBJECT_ID OBJECT_NAME
------------------------------ ---------- --------------------
SYS                                  3000 EXU8SYNU

SQL> SELECT hash_value, child_number, sql_text
  2  FROM v$sql
  3  WHERE sql_text LIKE '%select owner,object_id%';

HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------ ---------------------------------------------------------------------------
413165363            0 SELECT sql_id,sql_text FROM v$sqlarea WHERE sql_text LIKE '%select owner,ob
                        ject_id%'

  47485093            0 SELECT hash_value, child_number, sql_text FROM v$sql WHERE sql_text LIKE '%
                        select owner,object_id%'

3773998222            0 SELECT owner,object_id,object_name FROM test_ht WHERE object_id=3000

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('8gz444rhg594f'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  8gz444rhg594f, child NUMBER 0
-------------------------------------
SELECT owner,object_id,object_name FROM test_ht WHERE object_id=3000

Plan hash VALUE: 793292976

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | ROWS  | Bytes | Cost (%CPU)| TIME     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HT |     1 |    33 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
|*  2 |   INDEX RANGE SCAN          | IDX_ID  |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=3000)

Note
-----
   - outline "SYS_OUTLINE_11071210544304523" used FOR this statement

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------

23 ROWS selected.

SQL> SELECT name,CATEGORY,USED,SQL_TEXT FROM user_outlines
  2  WHERE name='SYS_OUTLINE_11071210544304523';

NAME                            CATEGORY    USED   SQL_TEXT
------------------------------- ----------- ------ ---------------------------------------------------------------------------
SYS_OUTLINE_11071210544304523   DEFAULT     USED   SELECT owner,object_id,object_name FROM test_ht WHERE object_id=3000

这里需要说明一下的是alter session set create_stored_outlines = true;这是由于bug5454975的缘故(10204已经修复)
虽然我这里是10204,不过我还是设置了一下,列出来说明,以提醒大家。详见metalink ID 445126.1

最后再补充一下,如果不用outline,那么可以将其删除,可以通过如下的几种方式:

EXECUTE DBMS_OUTLN.drop_by_cat('TEST_OUTLINES');

EXECUTE DBMS_OUTLN.CLEAR_USED('TEST_OUTLINES');

EXECUTE DBMS_OUTLN.drop_unused; -- 这是删除所有状态为unused的outline,要慎重。

SQL> SHOW parameter outline

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_outline_bitmap_tree                 BOOLEAN     TRUE
_plan_outline_data                   BOOLEAN     TRUE
create_stored_outlines               string

2.关于outlines的使用,有几个需要注意的地方:

1. 参数cursor_sharing设置为force时,outlines将无效;
2. literial sql的共享程度不高的情况下,使用outline会生产很多个执行计划,可能会有一些问题;
3. 一般情况我们在使用outline的时候,也是发现某个sql的执行计划不稳定的时候,由于执行计划是基于
统计信息的,那么由于生产系统中统计信息可能是在不断的变化,那么使用outline固定的执行计划不见得
一定就是最好的,这一点需要考虑。
4. 由于outlines信息的存放在用户outln下,那么该用户就显得尤为重要,维护的时候需要注意,不能随便给drop了。
5. outline创建以后,不是说就一层不变了,可以进行编辑,至于什么时候编辑,怎么编辑,大家可以参考
metalink文档 730062.1 How to Edit a Stored Outline to Use the Plan from Another Stored Outline。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库outlines测试笔记与使用案例

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1252.html

Oracle研究中心

关键词:

oracle outlines

create_stored_outlines

use_stored_outlines