6.使用SPM替换SQL的执行计划

最新推荐文章于 2024-10-02 00:27:17 发布

戒掉贪嗔痴(薛双奇)

最新推荐文章于 2024-10-02 00:27:17 发布

阅读量374

点赞数 7

分类专栏：数据库运维-Oracle-性能调优SQL 文章标签：数据库

本文链接：https://blog.csdn.net/weixin_43346403/article/details/135756738

版权

数据库运维-Oracle-性能调优SQL 专栏收录该内容

28 篇文章 1 订阅

订阅专栏

本文详细介绍了如何在Oracle数据库中启用执行计划基线，通过执行旧SQL和新SQL来更新计划，管理SQL计划的固定状态，以及调整optimizer_capture_sql_plan_baselines参数以优化性能。

摘要由CSDN通过智能技术生成

(1)启用执行计划基线

SQL> alter system set optimizer_capture_sql_plan_baselines=true;

System altered.

--declare 
-- rs pls_integer;
-- begin
-- rs:=dbms_spm.load_plans_from_cursor_cache('4s1dw29xzc2rs');
-- end;
-- /
--PL/SQL procedure successfully completed.

(2)执行旧SQL和新SQL,让执行计划加载到计划基线中。

select  id  from test01 where id>800000;
select	/*+full(test01)*/id	from test01 where id>800000;

--找执行过的SQL对应的SQL_ID;
SQL> col SQL_TEXT for a100
SQL> set lin 200
SQL> select SQL_TEXT,SQL_ID from  v$sqlarea  where SQL_TEXT like '%test01%' and upper(SQL_TEXT) not like 'EXPLAIN%'
and  SQL_TEXT like '%where id>800000';
SQL_TEXT											     SQL_ID
---------------------------------------------------------------------------------------------------- -------------
select	id  from test01 where id>800000 				 a05acgcuwzfwm
select /*+full(test01)*/id from test01 where id>800000	 0wg3z9uhv7umc



SQL> col SQL_TEXT for a50
SQL> col CREATED for a40
SQL> set lin 200
SQL> select sql_handle, plan_name,enabled accepted, fixed,created,SQL_TEXT from dba_sql_plan_baselines;
SQL_HANDLE		       PLAN_NAME		      ACC FIX CREATED				       SQL_TEXT
------------------------------ ------------------------------ --- --- ---------------------------------------- --------------------------------------------------
SQL> select sql_handle, plan_name,enabled, accepted, fixed,created,SQL_TEXT from dba_sql_plan_baselines;

SQL_HANDLE		       PLAN_NAME		              ENA ACC FIX CREATED				       SQL_TEXT
----------------------------------------------------- --- --- ---------------------------------------- --------------------------------------------------
SQL_2112181b20c8066e   SQL_PLAN_224hs3chch1mff98b55bb YES YES NO  23-JAN-24 06.10.57.000000 PM	       select  id  from test01 where id>800000
SQL_344ebb56d048d47e   SQL_PLAN_38mpvav84jp3y04c70b35 YES YES NO  23-JAN-24 05.58.47.000000 PM	       select	/*+full(test01)*/id	from test01 where id>800000
SQL_c12964b9d3f12200   SQL_PLAN_c2ab4r79z28h0f98b55bb YES YES NO  23-JAN-24 05.56.04.000000 PM	       select  id  from test01 where id>800000

(3)如果要删除固定的执行计划。

--我们暂时不做删除。
--declare 
--xx PLS_INTEGER; 
--BEGIN 
--xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_344ebb56d048d47e',plan_name=>'SQL_PLAN_38mpvav84jp3y04c70b35'); 
--END; 
--/

(4)停用原基线

--select  id  from test01 where id>800000
--停用旧SQL的执行计划;
declare 
 rs pls_integer;
 begin
   rs:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
   plan_name=>'SQL_PLAN_c2ab4r79z28h0f98b55bb',
   attribute_name=>'ENABLED',
   attribute_value=>'NO');
end;
/

PL/SQL procedure successfully completed.
--这里虽然停用了,ACCEPT=YES,没有变。
--ENABLED=NO;  停用了。


--可以看到,ENABLED=NO,停用了。
select sql_handle, plan_name,enabled,accepted, fixed,created,SQL_TEXT from dba_sql_plan_baselines;
SQL_HANDLE		       PLAN_NAME		           ENA ACC FIX CREATED				   SQL_TEXT
--------------------------------------------------- --- --- --- ---------------------------------------- --------------------------------------------------
SQL_c12964b9d3f12200 SQL_PLAN_c2ab4r79z28h004c70b35 YES YES YES 23-JAN-24 06.23.47.000000 PM		   select  id  from test01 where id>800000
SQL_c12964b9d3f12200 SQL_PLAN_c2ab4r79z28h0f98b55bb NO  YES NO  23-JAN-24 05.56.04.000000 PM		   select  id  from test01 where id>800000

--(5)变更前执行计划。

SQL> select * from table(dbms_xplan.display_cursor('a05acgcuwzfwm',null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	a05acgcuwzfwm, child number 0
-------------------------------------
select	id  from test01 where id>800000
Plan hash value: 578627003
---------------------------------------------------------------------------
| Id  | Operation	 | Name   | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	  |	  |	  |   490 (100)|	  |
|*  1 |  INDEX RANGE SCAN| IDX_ID |   191K|  2432K|   490   (1)| 00:00:06 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID">800000)
Note
-----
   - dynamic sampling used for this statement (level=2)
22 rows selected.

(6)变更执行计划

--变更执行计划。
--连接新基线
   declare 
      ln_ps pls_integer;
   begin
      ln_ps:=dbms_spm.load_plans_from_cursor_cache
      (sql_id=>'0wg3z9uhv7umc',            --新SQL的sql_id
      plan_hash_value=>262542483,          --新SQL的plan_hash_value
      sql_handle=>'SQL_c12964b9d3f12200'   --旧sql_handle
      );
   end;
   /


--查看执行计划,执行计划已经变更。
SQL> select * from table(dbms_xplan.display_cursor('a05acgcuwzfwm',null,null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID	a05acgcuwzfwm, child number 0
-------------------------------------
select	id  from test01 where id>800000
Plan hash value: 578627003
---------------------------------------------------------------------------
| Id  | Operation	 | Name   | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |	  |	  |	  |   490 (100)|	  |
|*  1 |  INDEX RANGE SCAN| IDX_ID |   191K|  2432K|   490   (1)| 00:00:06 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID">800000)
Note
-----
   - dynamic sampling used for this statement (level=2)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID	a05acgcuwzfwm, child number 2
-------------------------------------
select	id  from test01 where id>800000
Plan hash value: 262542483
----------------------------------------------------------------------------
| Id  | Operation	  | Name   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	   		|	   |	   |	 2 (100)|	   |
|*  1 |  TABLE ACCESS FULL| TEST01 	|	 4 |	52 |	 2   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID">800000)
Note
-----
   - SQL plan baseline SQL_PLAN_c2ab4r79z28h004c70b35 used for this statement
44 rows selected.
--虽然有两个执行计划,但是这里显然使用了新的执行计划:SQL_PLAN_c2ab4r79z28h004c70b35。

(7)ACCEPTED字段说明

--一个SQL语句对应的基线，我将它们归纳为三种状态
1.accepted（可接受），只有这种状态的基线，优化器才会考虑此基线中的执行计划
2.no-accepted（不可接受），这种状态的基线，优化器在SQL语句解析期间不会考虑。这种状态的基线必须通过演化和验证通过后，转变为accepted状态后，才会被优化器考虑使用
3.fixed为yes（固定），这种状态的基线固有最高优先级！比其他两类基线都要优先考

(8)固定执行计划

--1)固定前检查。
col SQL_TEXT for a50
col CREATED for a40
set lin 200
select sql_handle, plan_name, accepted, fixed,created,SQL_TEXT from dba_sql_plan_baselines;
SQL_HANDLE		       PLAN_NAME		              ACC FIX CREATED				       SQL_TEXT
----------------------- ------------------------------ --- --- ---------------------------------------- --------------------------------------------------
SQL_344ebb56d048d47e   SQL_PLAN_38mpvav84jp3y04c70b35 YES NO  23-JAN-24 05.58.47.000000 PM	       select	/*+full(test01)*/id	from test01 where id>800000
SQL_c12964b9d3f12200   SQL_PLAN_c2ab4r79z28h004c70b35 YES NO  23-JAN-24 06.23.47.000000 PM	       select  id  from test01 where id>800000
SQL_c12964b9d3f12200   SQL_PLAN_c2ab4r79z28h0f98b55bb YES NO  23-JAN-24 05.56.04.000000 PM	       select  id  from test01 where id>800000

--2)查看执行计划,执行计划已经变更。
--由原来的索引范围扫描变更为,全表扫描。
select  id  from test01 where id>800000;
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	a05acgcuwzfwm, child number 2
-------------------------------------
select	id  from test01 where id>800000
Plan hash value: 262542483

----------------------------------------------------------------------------
| Id  | Operation	  | Name   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	   		|	   |	   |	 2 (100)|	   	|
|*  1 |  TABLE ACCESS FULL| TEST01 	|	 4 |	52 |	 2   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID">800000)
Note
-----
   - SQL plan baseline SQL_PLAN_c2ab4r79z28h004c70b35 used for this statement
22 rows selected.


--SQL_c12964b9d3f12200	       SQL_PLAN_c2ab4r79z28h004c70b35
--3)固定这个执行计划。
DECLARE
 i NATURAL;
 BEGIN
 i := dbms_spm.alter_sql_plan_baseline(
 'SQL_c12964b9d3f12200',
 'SQL_PLAN_c2ab4r79z28h004c70b35',
 attribute_name => 'FIXED',
 attribute_value => 'YES');
 dbms_output.put_line(i);
 END;
/

--(9)固定完执行计划检查

col SQL_TEXT for a50
col CREATED for a40
set lin 200
select sql_handle, plan_name, accepted, fixed,created,SQL_TEXT from dba_sql_plan_baselines;
SQL_HANDLE		       PLAN_NAME		                      ACC FIX CREATED				       SQL_TEXT
----------------------- ------------------------------ --- --- ---------------------------------------- --------------------------------------------------
SQL_c12964b9d3f12200   SQL_PLAN_c2ab4r79z28h004c70b35 YES YES 23-JAN-24 06.23.47.000000 PM	       select  id  from test01 where id>800000
SQL_c12964b9d3f12200   SQL_PLAN_c2ab4r79z28h0f98b55bb YES NO  23-JAN-24 05.56.04.000000 PM	       select  id  from test01 where id>800000

--可以看到:SQL_PLAN_c2ab4r79z28h004c70b35,已经固定到SQL上。

(10)再次查看执行计划

select  id  from test01 where id>800000;
select * from table(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	a05acgcuwzfwm, child number 2
-------------------------------------
select	id  from test01 where id>800000
Plan hash value: 262542483
----------------------------------------------------------------------------
| Id  | Operation	  | Name   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	   |	   |	   |	 2 (100)|	   |
|*  1 |  TABLE ACCESS FULL| TEST01 |	 4 |	52 |	 2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID">800000)
Note
-----
   - SQL plan baseline SQL_PLAN_c2ab4r79z28h004c70b35 used for this statement
22 rows selected.
我们的执行计划绑定成功。

(11)总结

执行计划绑定使用SQL_PROFILE绑定时,总是绑定失败,还是使用原来的执行计划。
SPM可以绑定执行计划:
1)optimizer_capture_sql_plan_baselines=true;
2)执行替换前SQL,替换后SQL,此时会缓存到计划基线中;
3)停用SQL的旧的执行计划,绑定新的执行计划到旧SQL;
4)验证SQL的执行计划已经变更。
5)optimizer_capture_sql_plan_baselines参数的取值有以下有三种：
	1、FALSE：此参数将关闭sql计划基线功能，不捕获计划基线；
	2、TRUE：此参数将启用sql计划基线功能，捕获计划基线；
	3、FORCE：此参数将强制oracle数据库总是将当前执行的sql语句的计划捕获为基线，
	而不管sql语句是否已经捕获过计划基线。
6)正确设置optmizer_capture_sql_plan_baselines的参数，需要考虑以上三种取值中的特性，
并根据oracle数据库的实际性能需求进行选择：

--TRUE;
如果oracle数据库的服务质量要求较高，但数据库的性能不允许增加消耗，
我们可以将优化器参数optimizer_capture_sql_plan_baselines设置为TRUE，
以利用oracle数据库自动优化sql计划基线的功能。

--FORCE;
如果对于数据库性能有一定要求，可以将优化器参数optimizer_capture_sql_plan_baselines设置为FORCE，
使oracle数据库系统总是将当前执行的sql语句的计划捕获为基线，以确保语句持续获得最佳性能。

--FALSE;
如果不需要oracle数据库自动优化sql计划基线的功能，我们可以将优化器参数
optimizer_capture_sql_plan_baselines设置为FALSE，此时oracle数据库系统会忽略sql计划基线功能，
从而提高性能。