Oracle plan_baseline is ignored

Question

I have query with bind variables which comming from outer application.

The optimizer use the the unwanted index and I want to force it use another plan.

So I generate the good plan using index hint and then created the baseline with the plans

and connect the wanted plan to the query sql_id, and change the fixed attribute to 'YES'.

I executed the DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE function

and the output shows that the wanted plan marked as fixed=yes.

So why when I'm running the query it still with the bad plan??

The code:

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

what are the values of bind variables ? the query will not read proper index with empty variables, actually do you know how an index works ? if you have an index on colmun gender that has 70% male and 30% female , if you want to select on female then the optimizer will use the index — Moudiz, Feb 13 '18 at 15:30
Could you append definitions of `PIPIPWORKSPACEO_3` and `PIPIPWORKSPACEO_2` indexes to the question ? Basically this methd cannot work because you are generating plans for **different SQL statements with distinct [SQL signatures](https://docs.oracle.com/database/121/TGSQL/glossary.htm#GUID-160EB64E-536F-4313-B7B0-191C2719CF1F)**. A text of the first statement begins with `SELECT DISTINCT .....` while the other with `select /*+ index ......`, Oracle treats them as completely different SQL commands, and the plan generated for one can not be applied to the other. — krokodilko, Feb 14 '18 at 04:32
Notice that when I send empty values, just for the explain plan, I get the wanted plan and the plan does change (from no hint query to the query with the hint) even the values empty. So it's works. In the last check, when I actually want to check if the fixed baseline works, I'm using real values in the execute immediate statement. Also, It's not realy about the way index works. The purpose of the baseline is to force the plan, despite the optimizer choose to not use that index. — user2671057, Feb 14 '18 at 04:42
@krokodilko, I will paste the definition. But I think that it should work. I don't ask from the optimizer to treat them like they are same, but the can use the same plan. As I know that is the way to use the base line.. bind the new plan with the source sql — user2671057, Feb 14 '18 at 04:45
@krokodilko, this is what explained here: https://blogs.oracle.com/optimizer/how-do-i-deal-with-a-third-party-application-that-has-embedded-hints-that-result-in-a-sub-optimal-execution-plan-in-my-environment — user2671057, Feb 14 '18 at 04:50

score 0 · Answer 1 · answered Oct 10 '19 at 02:00

From a read through of your test case, I suspect the problem is that you're interpreting the FIXED attribute incorrectly. If you list all the plans for your baseline, you will probably find the original and the loaded cursor plan are both ENABLED and ACCEPTED at the moment. I think what you need to do (based on my own usage of these calls) is use the ENABLED attribute. Set ENABLED to NO for the unwanted plan. Try:

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

Oracle plan_baseline is ignored

1 Answers1