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

最新推荐文章于 2024-10-03 20:57:12 发布

cuiye1492

最新推荐文章于 2024-10-03 20:57:12 发布

阅读量134

点赞数

文章标签：数据库 5g

看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 hash value: 2966233522

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 |            |          |

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/12020513/viewspace-628961/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-628961/