固化执行计划
1、根据sql_id,查看下相关的执行计划,
首先通过在SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('01x1j0z641qm1')); 发现找不到相关的内容了;
去awr中查找一下:
SELECT dbms_xplan.display_awr('01x1j0z641qm1') FROM dual ;
SQL_ID 01x1j0z641qm1
--------------------
SQL语句省略。。。。。。
Plan hash value: 1967474240
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| | | |
| 1 | FILTER | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 126 | 10 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 2 | 126 | 10 (0)| 00:00:01 | | |
| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_VCH_OPRT | 1 | 52 | 6 (0)| 00:00:01 | ROWID | ROWID |
| 5 | INDEX RANGE SCAN | IX_T_VCH_OPRT_T_OPRT_TM | 2 | | 4 (0)| 00:00:01 | | |
| 6 | INDEX RANGE SCAN | P_VCH_OPRTSUB | 2 | | 3 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS BY INDEX ROWID | T_VCH_OPRT_SUB | 2 | 148 | 4 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID 01x1j0z641qm1
--------------------
SQL语句省略。。。。。。
Plan hash value: 2056954843
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21457 (100)| | | |
| 1 | SORT ORDER BY | | 5551 | 683K| 21457 (1)| 00:04:18 | | |
| 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | 5551 | 683K| 21456 (1)| 00:04:18 | | |
| 4 | NESTED LOOPS | | 5553 | 683K| 21456 (1)| 00:04:18 | | |
| 5 | TABLE ACCESS BY INDEX ROWID | T_VCH_OPRT_SUB | 5553 | 401K| 4791 (1)| 00:00:58 | | |
| 6 | INDEX RANGE SCAN | IX_T_VCH_OPRT_SUB_VCH_STR_END | 5553 | | 59 (0)| 00:00:01 | | |
| 7 | INDEX UNIQUE SCAN | PK_T_VCH_OPRT | 1 | | 2 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_VCH_OPRT | 1 | 52 | 3 (0)| 00:00:01 | ROWID | ROWID |
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 01x1j0z641qm1
--------------------
SQL语句省略。。。。。。
Plan hash value: 3207148589
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 289K(100)| | | |
| 1 | SORT ORDER BY | | 36153 | 4448K| 5176K| 289K (1)| 00:57:56 | | |
| 2 | FILTER | | | | | | | | |
| 3 | NESTED LOOPS | | 36153 | 4448K| | 288K (1)| 00:57:44 | | |
| 4 | NESTED LOOPS | | 45086 | 4448K| | 288K (1)| 00:57:44 | | |
| 5 | PARTITION RANGE ALL | | 22543 | 1144K| | 199K (1)| 00:39:58 | 1 | 50 |
| 6 | TABLE ACCESS FULL | T_VCH_OPRT | 22543 | 1144K| | 199K (1)| 00:39:58 | 1 | 50 |
| 7 | INDEX RANGE SCAN | P_VCH_OPRTSUB | 2 | | | 3 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY INDEX ROWID| T_VCH_OPRT_SUB | 2 | 148 | | 4 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------
2、可以看到,有三个执行计划,去DBA_SQL_PLAN_BASELINES查看下,发现,正好有三条记录:
SELECT * FROM DBA_SQL_PLAN_BASELINES WHERE sql_text LIKE '%Your SQL Text%'';
SIGNATURE SQL_HANDLE SQL_TEXT PLAN_NAME CREATOR ORIGIN PARSING_SCHEMA_NAME DESCRIPTION ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE OPTIMIZER_COST
1 12617946963699100000 SQL_af1bf3e92601a8e0 <CLOB> SQL_PLAN_ay6zmx4m03a705a2b640d DZCDE AUTO-CAPTURE DZCDE YES YES NO YES YES 95898
2 12617946963699100000 SQL_af1bf3e92601a8e0 <CLOB> SQL_PLAN_ay6zmx4m03a706b875dab DBMGR MANUAL-LOAD DZCDE YES YES NO YES YES 21457
3 12617946963699100000 SQL_af1bf3e92601a8e0 <CLOB> SQL_PLAN_ay6zmx4m03a707ff84314 DBMGR MANUAL-LOAD DBMGR YES YES YES YES YES 10
3、查看基线的详细信息:
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_af1bf3e92601a8e0',plan_name=>'SQL_PLAN_ay6zmx4m03a705a2b640d'));
--------------------------------------------------------------------------------
SQL handle: SQL_af1bf3e92601a8e0
SQL text: 省略。。。。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ay6zmx4m03a705a2b640d Plan id: 1512793101
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 3987913300
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1339 | 166K| 54089 (1)| 00:10:50 | | |
| 1 | SORT ORDER BY | | 1339 | 166K| 54089 (1)| 00:10:50 | | |
| 2 | CONCATENATION | | | | | | | |
|* 3 | FILTER | | | | | | | |
| 4 | NESTED LOOPS | | 386 | 49022 | 16213 (1)| 00:03:15 | | |
| 5 | NESTED LOOPS | | 386 | 49022 | 16213 (1)| 00:03:15 | | |
|* 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_VCH_OPRT | 380 | 19760 | 14692 (1)| 00:02:57 | ROWID | ROWID |
|* 7 | INDEX RANGE SCAN | IX_T_VCH_OPRT_DPTTYP_STATUS | 27358 | | 1671 (1)| 00:00:21 | | |
|* 8 | INDEX RANGE SCAN | P_VCH_OPRTSUB | 1 | | 3 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | T_VCH_OPRT_SUB | 1 | 75 | 4 (0)| 00:00:01 | | |
|* 10 | FILTER | | | | | | | |
| 11 | NESTED LOOPS | | 953 | 118K| 37875 (1)| 00:07:35 | | |
| 12 | NESTED LOOPS | | 953 | 118K| 37875 (1)| 00:07:35 | | |
|* 13 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_VCH_OPRT | 939 | 48828 | 34118 (1)| 00:06:50 | ROWID | ROWID |
|* 14 | INDEX RANGE SCAN | IX_T_VCH_OPRT_SRCDP_OLDSTA | 67846 | | 1699 (1)| 00:00:21 | | |
|* 15 | INDEX RANGE SCAN | P_VCH_OPRTSUB | 1 | | 3 (0)| 00:00:01 | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | T_VCH_OPRT_SUB | 1 | 75 | 4 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------
4、这个不是,目前使用的。我们来实验一下,强行固化上(假设最近运行的sql语句,走这个执行计划是最优的):
declare
pl pls_integer;
begin
pl := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_af1bf3e92601a8e0',
plan_name => 'SQL_PLAN_ay6zmx4m03a705a2b640d',
attribute_name => 'FIXED',
attribute_value => 'YES');
/*pl:=dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_8340a22efd9935e8',plan_name => 'SQL_PLAN_86h525vytkdg8b166c4ef');*/
/*pl:=dbms_spm.load_plans_from_cursor_cache('ckxzagwrv7v2t');*/
/* pl:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'5ta4pdrftsngr',plan_hash_value=>'3023965358',sql_handle=>'SQL_6b79d8957099a102');*/
end;
/
5、查看,是否固化成功
SELECT fixed FROM DBA_SQL_PLAN_BASELINES WHERE sql_handle='SQL_af1bf3e92601a8e0' AND plan_name='SQL_PLAN_ay6zmx4m03a705a2b640d';
FIXED
-----
YES
6、在sql developer中,F5一下SQL,发现已经走的是这个执行计划了。
不过,通过如下语句查询,显示两个执行计划都是fixed的了。
select * from table(dbms_xplan.display_sql_plan_baseline('SQL_6b79d8957099a102'));
是否,最后固化上的才会生效呢?
明天继续看,先下班咯。。。。
1、根据sql_id,查看下相关的执行计划,
首先通过在SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('01x1j0z641qm1')); 发现找不到相关的内容了;
去awr中查找一下:
SELECT dbms_xplan.display_awr('01x1j0z641qm1') FROM dual ;
SQL_ID 01x1j0z641qm1
--------------------
SQL语句省略。。。。。。
Plan hash value: 1967474240
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| | | |
| 1 | FILTER | | | | | | | |
| 2 | NESTED LOOPS | | 1 | 126 | 10 (0)| 00:00:01 | | |
| 3 | NESTED LOOPS | | 2 | 126 | 10 (0)| 00:00:01 | | |
| 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_VCH_OPRT | 1 | 52 | 6 (0)| 00:00:01 | ROWID | ROWID |
| 5 | INDEX RANGE SCAN | IX_T_VCH_OPRT_T_OPRT_TM | 2 | | 4 (0)| 00:00:01 | | |
| 6 | INDEX RANGE SCAN | P_VCH_OPRTSUB | 2 | | 3 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS BY INDEX ROWID | T_VCH_OPRT_SUB | 2 | 148 | 4 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------
SQL_ID 01x1j0z641qm1
--------------------
SQL语句省略。。。。。。
Plan hash value: 2056954843
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 21457 (100)| | | |
| 1 | SORT ORDER BY | | 5551 | 683K| 21457 (1)| 00:04:18 | | |
| 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | 5551 | 683K| 21456 (1)| 00:04:18 | | |
| 4 | NESTED LOOPS | | 5553 | 683K| 21456 (1)| 00:04:18 | | |
| 5 | TABLE ACCESS BY INDEX ROWID | T_VCH_OPRT_SUB | 5553 | 401K| 4791 (1)| 00:00:58 | | |
| 6 | INDEX RANGE SCAN | IX_T_VCH_OPRT_SUB_VCH_STR_END | 5553 | | 59 (0)| 00:00:01 | | |
| 7 | INDEX UNIQUE SCAN | PK_T_VCH_OPRT | 1 | | 2 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_VCH_OPRT | 1 | 52 | 3 (0)| 00:00:01 | ROWID | ROWID |
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 01x1j0z641qm1
--------------------
SQL语句省略。。。。。。
Plan hash value: 3207148589
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 289K(100)| | | |
| 1 | SORT ORDER BY | | 36153 | 4448K| 5176K| 289K (1)| 00:57:56 | | |
| 2 | FILTER | | | | | | | | |
| 3 | NESTED LOOPS | | 36153 | 4448K| | 288K (1)| 00:57:44 | | |
| 4 | NESTED LOOPS | | 45086 | 4448K| | 288K (1)| 00:57:44 | | |
| 5 | PARTITION RANGE ALL | | 22543 | 1144K| | 199K (1)| 00:39:58 | 1 | 50 |
| 6 | TABLE ACCESS FULL | T_VCH_OPRT | 22543 | 1144K| | 199K (1)| 00:39:58 | 1 | 50 |
| 7 | INDEX RANGE SCAN | P_VCH_OPRTSUB | 2 | | | 3 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY INDEX ROWID| T_VCH_OPRT_SUB | 2 | 148 | | 4 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------
2、可以看到,有三个执行计划,去DBA_SQL_PLAN_BASELINES查看下,发现,正好有三条记录:
SELECT * FROM DBA_SQL_PLAN_BASELINES WHERE sql_text LIKE '%Your SQL Text%'';
SIGNATURE SQL_HANDLE SQL_TEXT PLAN_NAME CREATOR ORIGIN PARSING_SCHEMA_NAME DESCRIPTION ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE OPTIMIZER_COST
1 12617946963699100000 SQL_af1bf3e92601a8e0 <CLOB> SQL_PLAN_ay6zmx4m03a705a2b640d DZCDE AUTO-CAPTURE DZCDE YES YES NO YES YES 95898
2 12617946963699100000 SQL_af1bf3e92601a8e0 <CLOB> SQL_PLAN_ay6zmx4m03a706b875dab DBMGR MANUAL-LOAD DZCDE YES YES NO YES YES 21457
3 12617946963699100000 SQL_af1bf3e92601a8e0 <CLOB> SQL_PLAN_ay6zmx4m03a707ff84314 DBMGR MANUAL-LOAD DBMGR YES YES YES YES YES 10
3、查看基线的详细信息:
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_af1bf3e92601a8e0',plan_name=>'SQL_PLAN_ay6zmx4m03a705a2b640d'));
--------------------------------------------------------------------------------
SQL handle: SQL_af1bf3e92601a8e0
SQL text: 省略。。。。
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_ay6zmx4m03a705a2b640d Plan id: 1512793101
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 3987913300
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1339 | 166K| 54089 (1)| 00:10:50 | | |
| 1 | SORT ORDER BY | | 1339 | 166K| 54089 (1)| 00:10:50 | | |
| 2 | CONCATENATION | | | | | | | |
|* 3 | FILTER | | | | | | | |
| 4 | NESTED LOOPS | | 386 | 49022 | 16213 (1)| 00:03:15 | | |
| 5 | NESTED LOOPS | | 386 | 49022 | 16213 (1)| 00:03:15 | | |
|* 6 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_VCH_OPRT | 380 | 19760 | 14692 (1)| 00:02:57 | ROWID | ROWID |
|* 7 | INDEX RANGE SCAN | IX_T_VCH_OPRT_DPTTYP_STATUS | 27358 | | 1671 (1)| 00:00:21 | | |
|* 8 | INDEX RANGE SCAN | P_VCH_OPRTSUB | 1 | | 3 (0)| 00:00:01 | | |
|* 9 | TABLE ACCESS BY INDEX ROWID | T_VCH_OPRT_SUB | 1 | 75 | 4 (0)| 00:00:01 | | |
|* 10 | FILTER | | | | | | | |
| 11 | NESTED LOOPS | | 953 | 118K| 37875 (1)| 00:07:35 | | |
| 12 | NESTED LOOPS | | 953 | 118K| 37875 (1)| 00:07:35 | | |
|* 13 | TABLE ACCESS BY GLOBAL INDEX ROWID| T_VCH_OPRT | 939 | 48828 | 34118 (1)| 00:06:50 | ROWID | ROWID |
|* 14 | INDEX RANGE SCAN | IX_T_VCH_OPRT_SRCDP_OLDSTA | 67846 | | 1699 (1)| 00:00:21 | | |
|* 15 | INDEX RANGE SCAN | P_VCH_OPRTSUB | 1 | | 3 (0)| 00:00:01 | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | T_VCH_OPRT_SUB | 1 | 75 | 4 (0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------
4、这个不是,目前使用的。我们来实验一下,强行固化上(假设最近运行的sql语句,走这个执行计划是最优的):
declare
pl pls_integer;
begin
pl := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_af1bf3e92601a8e0',
plan_name => 'SQL_PLAN_ay6zmx4m03a705a2b640d',
attribute_name => 'FIXED',
attribute_value => 'YES');
/*pl:=dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_8340a22efd9935e8',plan_name => 'SQL_PLAN_86h525vytkdg8b166c4ef');*/
/*pl:=dbms_spm.load_plans_from_cursor_cache('ckxzagwrv7v2t');*/
/* pl:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'5ta4pdrftsngr',plan_hash_value=>'3023965358',sql_handle=>'SQL_6b79d8957099a102');*/
end;
/
5、查看,是否固化成功
SELECT fixed FROM DBA_SQL_PLAN_BASELINES WHERE sql_handle='SQL_af1bf3e92601a8e0' AND plan_name='SQL_PLAN_ay6zmx4m03a705a2b640d';
FIXED
-----
YES
6、在sql developer中,F5一下SQL,发现已经走的是这个执行计划了。
不过,通过如下语句查询,显示两个执行计划都是fixed的了。
select * from table(dbms_xplan.display_sql_plan_baseline('SQL_6b79d8957099a102'));
是否,最后固化上的才会生效呢?
明天继续看,先下班咯。。。。