dbms_spm包的使用（二）

最新推荐文章于 2023-06-25 19:38:20 发布

cuili9760

最新推荐文章于 2023-06-25 19:38:20 发布

阅读量145

点赞数

dbms_spm包的使用

（二）将批量的sql执行计划都固定下来

在对emp表进行几次查询操作后，执行以下语句：

SQL> select sql_text,sql_id from v$sql where sql_text like '% emp %';

SQL_TEXT SQL_ID

-------------------------------------------------------------------------------- -------------

select * from emp where mgr=7839 2xt4ug5mcccdn

select * from emp where ename='ALLEN' 4y6nkmx4dsr3q

select sql_text,sql_id from v$sql where sql_text like '% emp %' 8x34a6v9dygmm

SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE '% emp %' 20215c0gn2wfw

select sql_id from v$sql where sql_text like '% emp %' 1q2ahwwm5m1xm

如果我们希望将以上所有emp表相关的语句计划固定下来，则可以执行：

SQL> declare

2 my_plans pls_integer;

3 begin

4 for i in (select sql_id from v$sql where sql_text like '% emp %') loop

5 my_plans:=dbms_spm.load_plans_from_cursor_cache(sql_id=>i.sql_id);

6 end loop;

7 end;

8 /

PL/SQL procedure successfully completed

查看在dba_sql_plan_baselines表中的记录：

SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines;

SQL_HANDLE SQL_TEXT PLAN_NAME

------------------------------ -------------------------------------------------------------------------------- ------------------------------

SYS_SQL_1f5c56101761f5ee SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE '% emp %' SQL_PLAN_1yr2q20bq3xgfb73cade2

SYS_SQL_2a9b14f5ac58c943 select * from emp where ename='ALLEN' SQL_PLAN_2p6snyqq5jka3d8a279cc

SYS_SQL_b4fcbe753d70a915 select * from emp where mgr=7839 SQL_PLAN_b9z5yfnyr1a8pd8a279cc

SYS_SQL_d8c5743fa3353c9e select * from v$sql where sql_text like '% emp %' SQL_PLAN_djjbn7yjmag4yb73cade2

类似地，如果希望将某个快照的sql全部固定下来，可以将上面for循环中的语句改为：

select sql_id from sys.wrh$_sqltext where snap_id=xxxx;

那么如何知道哪条语句的执行计划发生了修改呢？

先看以下两种场景：

（1）如果我们在emp表的mgr字段创建了索引，会出现以下结果：

SQL> create index idx_emp_mgr on emp(mgr);

Index created

SQL> select * from emp where mgr=7839;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----------- ---------- --------- ----- ----------- --------- --------- ------

7566.00 JONES MANAGER 7839 1981/4/2 2975.00 20

7698.00 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782.00 CLARK MANAGER 7839 1981/6/9 2450.00 10

SQL>

SQL> select sql_handle,sql_text,plan_name

2 from dba_sql_plan_baselines t;

SQL_HANDLE SQL_TEXT PLAN_NAME

------------------------------ -------------------------------------------------------------------------------- ------------------------------

SYS_SQL_1f5c56101761f5ee SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE '% emp %' SQL_PLAN_1yr2q20bq3xgfb73cade2

SYS_SQL_2a9b14f5ac58c943 select * from emp where ename='ALLEN' SQL_PLAN_2p6snyqq5jka3d8a279cc

SYS_SQL_b4fcbe753d70a915 select * from emp where mgr=7839 SQL_PLAN_b9z5yfnyr1a8p2237e0d1

SYS_SQL_b4fcbe753d70a915 select * from emp where mgr=7839 SQL_PLAN_b9z5yfnyr1a8pd8a279cc

SYS_SQL_d8c5743fa3353c9e select * from v$sql where sql_text like '% emp %' SQL_PLAN_djjbn7yjmag4yb73cade2

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_b4fcbe753d70a915',format=>'basic'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL handle: SYS_SQL_b4fcbe753d70a915

SQL text: select * from emp where mgr=7839

--------------------------------------------------------------------------------

Plan name: SQL_PLAN_b9z5yfnyr1a8p2237e0d1 Plan id: 574087377

Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE

--------------------------------------------------------------------------------

Plan hash value: 2059184959

---------------------------------------------------

| Id | Operation | Name |

---------------------------------------------------

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP |

| 2 | INDEX RANGE SCAN | IDX_EMP_MGR |

---------------------------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan name: SQL_PLAN_b9z5yfnyr1a8pd8a279cc Plan id: 3634526668

Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD

--------------------------------------------------------------------------------

Plan hash value: 3956160932

----------------------------------

| Id | Operation | Name |

----------------------------------

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS FULL| EMP |

----------------------------------

34 rows selected

（2）如果表t上执行select object_id from t where object_id<10时原本存在索引。

创建基线后，删除索引。会有如下结果：

SQL> select sql_id,sql_text from v$sql where sql_text like 'select object_id from t where object_id<10%';

SQL_ID SQL_TEXT

------------- --------------------------------------------------------------------------------

67dqwpb1nhyuk select object_id from t where object_id<10

SQL> declare

2 my_plans pls_integer;

3 begin

4 my_plans:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'67dqwpb1nhyuk');

5 end;

6 /

PL/SQL procedure successfully completed

SQL> select object_id from t where object_id<10;

8 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2498590897

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8 | 40 | 2 (0)| 00:00:01 |

|* 1 | INDEX RANGE SCAN| IDX_T_OBJECT_ID | 8 | 40 | 2 (0)| 00:00:01 |

------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("OBJECT_ID"<10)

Note

-----

- SQL plan baseline "SQL_PLAN_5td0qnhnkk433f7fa6824" used for this statement

Statistics

----------------------------------------------------------

14 recursive calls

13 db block gets

12 consistent gets

0 physical reads

5064 redo size

509 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

8 rows processed

SQL> set autot off

SQL> drop index idx_t_object_id;

Index dropped.

SQL> set autot trace

SQL> select object_id from t where object_id<10;

8 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8 | 40 | 288 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 8 | 40 | 288 (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"<10)

Statistics

----------------------------------------------------------

209 recursive calls

35 db block gets

1067 consistent gets

1035 physical reads

14224 redo size

509 bytes sent via SQL*Net to client

419 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

8 rows processed

SQL> select sql_handle,sql_text,plan_name

2 from dba_sql_plan_baselines t;

SQL_HANDLE SQL_TEXT PLAN_NAME

------------------------------ -------------------------------------------------------------------------------- ------------------------------

SYS_SQL_1f5c56101761f5ee SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE '% emp %' SQL_PLAN_1yr2q20bq3xgfb73cade2

SYS_SQL_2a9b14f5ac58c943 select * from emp where ename='ALLEN' SQL_PLAN_2p6snyqq5jka3d8a279cc

SYS_SQL_5cb416a429291063 select object_id from t where object_id<10 SQL_PLAN_5td0qnhnkk43394ecae5c

SYS_SQL_5cb416a429291063 select object_id from t where object_id<10 SQL_PLAN_5td0qnhnkk433f7fa6824

SYS_SQL_b4fcbe753d70a915 select * from emp where mgr=7839 SQL_PLAN_b9z5yfnyr1a8p2237e0d1

SYS_SQL_b4fcbe753d70a915 select * from emp where mgr=7839 SQL_PLAN_b9z5yfnyr1a8pd8a279cc

SYS_SQL_d8c5743fa3353c9e select * from v$sql where sql_text like '% emp %' SQL_PLAN_djjbn7yjmag4yb73cade2

7 rows selected

SQL>

SQL> SET SERVEROUTPUT ON

SQL> SET LONG 10000

SQL> DECLARE

2 report clob;

3 BEGIN

4 report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(

5 sql_handle => 'SYS_SQL_5cb416a429291063');

6 DBMS_OUTPUT.PUT_LINE(report);

7 END;

8 /

-------------------------------------------------------------------------------

Evolve SQL Plan Baseline Report

-------------------------------------------------------------------------------

Inputs:

-------

SQL_HANDLE = SYS_SQL_5cb416a429291063

PLAN_NAME =

TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

VERIFY = YES

COMMIT = YES

Plan: SQL_PLAN_5td0qnhnkk43394ecae5c

------------------------------------

Plan was not verified.

Plan was found to be same as the baseline plan.

Plan was changed to an accepted plan.

由于索引删除，原有的使用索引的执行计划（基线）不能用了，只能使用新的执行计划

-------------------------------------------------------------------------------

Report Summary

-------------------------------------------------------------------------------

Number of plans verified: 0

Number of plans accepted: 1

PL/SQL procedure successfully completed

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SYS_SQL_5cb416a429291063',format=>'basic'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL handle: SYS_SQL_5cb416a429291063

SQL text: select object_id from t where object_id<10

--------------------------------------------------------------------------------

Plan name: SQL_PLAN_5td0qnhnkk43394ecae5c Plan id: 2498539100

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

--------------------------------------------------------------------------------

Plan hash value: 1601196873

----------------------------------

| Id | Operation | Name |

----------------------------------

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS FULL| T |

----------------------------------

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan name: SQL_PLAN_5td0qnhnkk433f7fa6824 Plan id: 4160383012

Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD

--------------------------------------------------------------------------------

Plan hash value: 1601196873

----------------------------------

| Id | Operation | Name |

----------------------------------

| 0 | SELECT STATEMENT | |

| 1 | TABLE ACCESS FULL| T |

----------------------------------

与前面的SQL_PLAN_5td0qnhnkk433f7fa6824 实际执行计划相比可以看出，实际执行的是索引扫描，这里由于索引删除，不能捕获原执行计划了。

33 rows selected

通过前两个例子说明：在dba_sql_plan_baselines中根据sql_handle分组，有多条记录（多余一条记录）时，就说明语句的执行计划发生了修改或存在更优的计划。

============================================================

再次说明一下常用的语句：

（1）从缓存导入基线：

（3）declare
  my_plans pls_integer;
begin
  my_plans:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'xxxxxxxxxx');
  end;
/
（2）根据plan_name或sql_handle查看执行计划：

select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'xxxxxxxxxxxxxx',format=>'basic'));

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'xxxxxxxxx',format=>'basic'));

（3）查看基线：

select sql_handle,sql_text,plan_name
from dba_sql_plan_baselines t;
（4）更新基线：

SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
  report clob;
BEGIN
  report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
  sql_handle => 'xxxxxxxxxx');
  DBMS_OUTPUT.PUT_LINE(report);
END;
/
（5）删除基线：

declare

n pls_integer;

begin

n:=dbms_spm.drop_sql_plan_baseline(plan_name=>'SQL_PLAN_djqgumq9ng3tada556d36');

dbms_output.put_line(n);

end;

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

转载于:http://blog.itpub.net/26451536/viewspace-1175715/