ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 11g sql plan baseline(4)使用baseline覆盖hint

oracle 11g sql plan baseline(4)使用baseline覆盖hint

Linux操作系统 作者:宕机熟手 时间:2013-11-05 17:07:48 0 删除 编辑

看trouble shooting preformance介绍 使用baseline的一个好方法

覆盖hints
当应用的sql text中使用hints
强制了访问路径之类已经不允许调整,但此时你发现了更好的执行计划就用下面的方法


SQL> drop table t;

Table dropped.

SQL>  select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production


建立测试表
SQL>  create table t (a int, b int);

Table created.

SQL>    declare
  2              begin
  3              for i in 1..1000 loop
  4              insert into t values(i,i+1);
  5              end loop;
  6              commit;
  7              end;
  8         /

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats('XH','T');


PL/SQL procedure successfully completed.

SQL> SQL> SELECT /*+ full(t) */ count(*) FROM t WHERE a=2;

  COUNT(*)
----------
         1
强制走fts

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4u1qj7t4xp6g0, child number 1
-------------------------------------
SELECT /*+ full(t) */ count(*) FROM t WHERE a=2

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| T    |     1 |     4 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"=2)


19 rows selected.

强制走fts


建立index
SQL> CREATE INDEX t_ind ON t (a);

Index created.


SQL>
SQL> SELECT /*+index(t) */ count(*) FROM t WHERE a=2;

  COUNT(*)
----------
         1

SQL> SELECT * FROM table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0x4t5g90683np, child number 0
-------------------------------------
SELECT /*+index(t) */ count(*) FROM t WHERE a=2

Plan hash value: 468740019

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     4 |            |          |

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

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

   2 - access("A"=2)


当发现这个强制走fts的语句走index更好时,但有不能修改使用hint走fts的sql语句,按下面的步骤


首先为这个用hints走fts的sql语句建立 sql plan baseline推荐使用从library cache中加载建立

测试用的是 自动捕捉建立,原理看(1)
SQL> SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;

Session altered.

SQL> SELECT /*+ full(t) */ count(*) FROM t WHERE a=2;

  COUNT(*)
----------
         1

SQL> SELECT /*+ full(t) */ count(*) FROM t WHERE a=2;

  COUNT(*)
----------
         1

SQL> ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;

Session altered.

 

SQL> SQL> SELECT /*+ full(t) */ count(*) FROM t WHERE a=2;

  COUNT(*)
----------
         1

SQL> SELECT * FROM table(dbms_xplan.display_cursor);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4u1qj7t4xp6g0, child number 1
-------------------------------------
SELECT /*+ full(t) */ count(*) FROM t WHERE a=2

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS FULL| T    |     1 |     4 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("A"=2)

Note
-----
   - SQL plan baseline SYS_SQL_PLAN_b805d5513fdbb376 used for this statement

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


23 rows selected.


可以看到sql plan baseline建立好了 而且使用了

SQL> SQL>  SELECT sql_handle, plan_name
  2      FROM dba_sql_plan_baselines
  3      WHERE creator = user
  4     AND created > systimestamp - to_dsinterval('0 00:15:00');

SQL_HANDLE                     PLAN_NAME
------------------------------ ------------------------------
SYS_SQL_c0ba6edab805d551       SYS_SQL_PLAN_b805d5513fdbb376~~~~~~~~~用hints有fts的sql plan baseline


下面修改这个sql plan baseline
方法
1.用library中加载走index的 plan 到 用hints走fts 的sql plan  baseline中(通过sql_handle确定sql plan baseline等于加载到这个sql_handle中)
2.此时这个用hints走fts的sql plan baseline中 就有2个plan 一个走 index,一个走fts,删除走fts的(用plan_name 确认走hints走fts的)

SQL> DECLARE
  2    ret PLS_INTEGER;
  3  BEGIN
  4    ret := dbms_spm.load_plans_from_cursor_cache(
  5             sql_id          => '&sql_id_index_scan',
  6             plan_hash_value => '&plan_hash_value_index_scan',
  7             sql_handle      => '&sql_handle'
  8           );
  9    dbms_output.put_line(ret || ' SQL plan baseline(s) created');
 10    ret := dbms_spm.drop_sql_plan_baseline(
 11             sql_handle => '&sql_handle',
 12             plan_name  => '&plan_name'
 13           );
 14    dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');
 15  END;
 16  /
Enter value for sql_id_index_scan: 0x4t5g90683np
old   5:            sql_id          => '&sql_id_index_scan',
new   5:            sql_id          => '0x4t5g90683np',
Enter value for plan_hash_value_index_scan: 468740019
old   6:            plan_hash_value => '&plan_hash_value_index_scan',
new   6:            plan_hash_value => '468740019',
Enter value for sql_handle: SYS_SQL_c0ba6edab805d551
old   7:            sql_handle      => '&sql_handle'
new   7:            sql_handle      => 'SYS_SQL_c0ba6edab805d551'
Enter value for sql_handle: SYS_SQL_c0ba6edab805d551
old  11:            sql_handle => '&sql_handle',
new  11:            sql_handle => 'SYS_SQL_c0ba6edab805d551',
Enter value for plan_name: SYS_SQL_PLAN_b805d5513fdbb376
old  12:            plan_name  => '&plan_name'
new  12:            plan_name  => 'SYS_SQL_PLAN_b805d5513fdbb376'

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT sql_handle, sql_text, enabled, accepted
  2  FROM dba_sql_plan_baselines
  3  WHERE creator = user
  4  AND created > systimestamp - to_dsinterval('0 00:15:00');

SQL_HANDLE
------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
ENA ACC
--- ---
SYS_SQL_c0ba6edab805d551
SELECT /*+ full(t) */ count(*) FROM t WHERE a=2                     ~~~此时这个sql plan baseline中就有了走index的plan,但sql text没变 还是加hint full
YES YES

 

 

 

SQL> SELECT /*+ full(t) */ count(*) FROM t WHERE a=2;

  COUNT(*)
----------
         1

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4u1qj7t4xp6g0, child number 2
-------------------------------------
SELECT /*+ full(t) */ count(*) FROM t WHERE a=2

Plan hash value: 468740019

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     4 |            |          |

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

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

   2 - access("A"=2)

Note
-----
   - SQL plan baseline SYS_SQL_PLAN_b805d55126cd6d8d used for this statement

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


23 rows selected.


此时 走index覆盖了hint走fts

 

 

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

请登录后发表评论 登录
全部评论

注册时间:2013-11-05

  • 博文量
    5
  • 访问量
    8993