忽略Oracle plan_baseline

Question

我使用来自外部应用程序的绑定变量进行查询。

优化器使用了不需要的索引，我想强制它使用另一个计划。

因此，我使用index hint生成了良好的计划，然后使用这些计划创建了基线

并将想要的计划连接到查询sql_id，并将固定属性更改为'YES‘。

我执行了DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE函数

输出显示标记为fixed=yes的所需计划。

那么，为什么当我运行查询时，它仍然带有糟糕的计划呢？

代码：

-- Query
SELECT  DISTINCT t_01.puid 
FROM PWORKSPACEOBJECT t_01 , PPOM_APPLICATION_OBJECT t_02 
WHERE ( (  UPPER(t_01.pobject_type)  IN  ( UPPER( :1 ) ,  UPPER( :2 )  ) 
AND ( t_02.pcreation_date >= :3 ) ) AND ( t_01.puid = t_02.puid ) )


-- get the text
select sql_fulltext
from v$sqlarea
where sql_id = '21pts328r2nb7' and rownum = 1;

-- prepare the explain plan
explain plan for
SELECT  DISTINCT t_01.puid 
FROM PWORKSPACEOBJECT t_01 , PPOM_APPLICATION_OBJECT t_02 
WHERE ( (  UPPER(t_01.pobject_type)  IN  ( UPPER( :1 ) ,  UPPER( :2 )  ) 
AND ( t_02.pcreation_date >= :3 ) ) AND ( t_01.puid = t_02.puid ) ) ;

-- we can see that there is no use of index - PIPIPWORKSPACEO_2
select * from table(dbms_xplan.display);

------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   | 10382 |   517K| 61553 |
|   1 |  HASH UNIQUE                   |                   | 10382 |   517K| 61553 |
|   2 |   HASH JOIN                    |                   |   158K|  7885K| 61549 |
|   3 |    INLIST ITERATOR             |                   |       |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| PWORKSPACEOBJECT  |   158K|  4329K| 52689 |
|   5 |      INDEX RANGE SCAN          | PIPIPWORKSPACEO_3 |   158K|       |   534 |
|   6 |    INDEX RANGE SCAN            | DBTAO_IX1_PPOM    |  3402K|    74M|  2911 |
------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

-- generate plan with the wanted index
explain plan for
select /*+ index(t_01 PIPIPWORKSPACEO_2)*/  distinct t_01.puid 
from pworkspaceobject t_01 , ppom_application_object t_02 
where ( (  upper(t_01.pobject_type)  in  ( upper( :1 ) ,  upper( :2 )  ) 
and ( t_02.pcreation_date >= :3 ) ) and ( t_01.puid = t_02.puid ) ) ;

-- the index working - the index used
select * from table(dbms_xplan.display);


-----------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   | 10382 |   517K|   223K|
|   1 |  HASH UNIQUE                  |                   | 10382 |   517K|   223K|
|   2 |   HASH JOIN                   |                   |   158K|  7885K|   223K|
|   3 |    TABLE ACCESS BY INDEX ROWID| PWORKSPACEOBJECT  |   158K|  4329K|   214K|
|   4 |     INDEX FULL SCAN           | PIPIPWORKSPACEO_2 |   158K|       |   162K|
|   5 |    INDEX RANGE SCAN           | DBTAO_IX1_PPOM    |  3402K|    74M|  2911 |
-----------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

-- get the sql_id of the query with the good index
-- 7t72qvghr0zqh
select sql_id from v$sqlarea where sql_text like 'select /*+ index(t_01 PIPIPWORKSPACEO_2)%';

-- get the plan hash value of the good plan by the sql_id
--4040955653
select plan_hash_value from v$sql_plan where sql_id = '7t72qvghr0zqh';

-- get the plan hash value of the bad plan by the sql_id
--1044780890
select plan_hash_value from v$sql_plan where sql_id = '21pts328r2nb7';

-- load the source plan
begin
        dbms_output.put_line(
        dbms_spm.load_plans_from_cursor_cache
           ( sql_id => '21pts328r2nb7' )
        );
END;

-- the new base line created with the bad plan
select * from dba_sql_plan_baselines;

-- load the good plan of the second sql_id (with the wanted index)
-- and bind it to the sql_handle of the source query
begin
dbms_output.put_line(
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
( sql_id => '7t72qvghr0zqh',
plan_hash_value => 4040955653,
sql_handle => 'SQL_4afac4211aa3317d' )
);
end;

-- new there are 2 plans bind to the same sql_handle and sql_text
select * from dba_sql_plan_baselines;

-- alter the good one to be fixed
begin
dbms_output.put_line(
dbms_spm.alter_sql_plan_baseline
    ( sql_handle => 
        'SQL_4afac4211aa3317d',
        PLAN_NAME => 'SQL_PLAN_4pyq444da6cbxf7c97cc7',
     ATTRIBUTE_NAME => 'fixed',
    ATTRIBUTE_VALUE => 'YES' 
   )) ;
end;

-- check the good plan - fixed = yes
select * from table(
dbms_xplan.display_sql_plan_baseline (
   sql_handle      => 'SQL_4afac4211aa3317d',
   plan_name       => 'SQL_PLAN_4pyq444da6cbxf7c97cc7',
   format          => 'ALL'));


--------------------------------------------------------------------------------
SQL handle: SQL_4afac4211aa3317d
SQL text: SELECT  DISTINCT t_01.puid FROM PWORKSPACEOBJECT t_01 , 
          PPOM_APPLICATION_OBJECT t_02 WHERE ( (  UPPER(t_01.pobject_type)  IN  ( 
          UPPER( :1 ) ,  UPPER( :2 )  ) AND ( t_02.pcreation_date >= :3 ) ) AND ( 
          t_01.puid = t_02.puid ) ) 
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_4pyq444da6cbxf7c97cc7         Plan id: 4157177031
Enabled: YES     Fixed: YES     Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 4040955653

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   | 10382 |   517K|       |   223K  (1)| 00:44:37 |
|   1 |  HASH UNIQUE                  |                   | 10382 |   517K|       |   223K  (1)| 00:44:37 |
|*  2 |   HASH JOIN                   |                   |   158K|  7885K|  6192K|   223K  (1)| 00:44:37 |
|   3 |    TABLE ACCESS BY INDEX ROWID| PWORKSPACEOBJECT  |   158K|  4329K|       |   214K  (1)| 00:42:50 |
|*  4 |     INDEX FULL SCAN           | PIPIPWORKSPACEO_2 |   158K|       |       |   162K  (1)| 00:32:25 |
|*  5 |    INDEX RANGE SCAN           | DBTAO_IX1_PPOM    |  3402K|    74M|       |  2911   (1)| 00:00:35 |
-----------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / T_01@SEL$1
   4 - SEL$1 / T_01@SEL$1
   5 - SEL$1 / T_02@SEL$1

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

   2 - access("T_01"."PUID"="T_02"."PUID")
   4 - filter(UPPER("POBJECT_TYPE")=UPPER(:1) OR UPPER("POBJECT_TYPE")=UPPER(:2))
   5 - access("T_02"."PCREATION_DATE">=:3)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=1) "T_01"."PUID"[VARCHAR2,15]
   2 - (#keys=1) "T_01"."PUID"[VARCHAR2,15]
   3 - "T_01"."PUID"[VARCHAR2,15]
   4 - "T_01".ROWID[ROWID,10]
   5 - "T_02"."PUID"[VARCHAR2,15]

Note
-----
   - 'PLAN_TABLE' is old version

-- run explain plan for the query
-- need to use the new plan
declare
v_string clob;
begin
select sql_fulltext
into v_string
from v$sqlarea
where sql_id = '21pts328r2nb7' and rownum = 1;

execute immediate 'explain plan for ' || v_string using '1','1',sysdate;

end;

-- check the plan - still the unwanted index and plan
select * from table(dbms_xplan.display);


------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   | 10382 |   517K| 61553 |
|   1 |  HASH UNIQUE                   |                   | 10382 |   517K| 61553 |
|   2 |   HASH JOIN                    |                   |   158K|  7885K| 61549 |
|   3 |    INLIST ITERATOR             |                   |       |       |       |
|   4 |     TABLE ACCESS BY INDEX ROWID| PWORKSPACEOBJECT  |   158K|  4329K| 52689 |
|   5 |      INDEX RANGE SCAN          | PIPIPWORKSPACEO_3 |   158K|       |   534 |
|   6 |    INDEX RANGE SCAN            | DBTAO_IX1_PPOM    |  3402K|    74M|  2911 |
------------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version

Answer 1

根据对测试用例的通读，我怀疑问题在于您错误地解释了FIXED属性。如果列出基线的所有计划，您可能会发现原始计划和加载的游标计划目前都已启用并接受。我认为您需要做的(基于我自己对这些调用的使用)是使用ENABLED属性。对于不需要的计划，将ENABLED设置为NO。尝试：

exec dbms_spm.alter_sql_plan_baseline(
sql_handle=>'SQL_...' -- baseline to update
,plan_name=>'SQL_PLAN_...'  -- unwanted plan signature to disable
,attribute_name=>'ENABLED',attribute_value=>'NO')