EBS database upgrade - concurrent requests query performance

[satish]

Hi erman,

We are upgrading ebs database 11.1.0.7 to 11.2.0.4
Application version R12.1.3

Our concern was , as we have currently in our existing database many sqls using "sql plan baselines" and other using "sql profiles" , so basically we are currently using these to make oracle follow a fixed execution path for those sqls all the time and thus we have "optimizer_use_sql_plan_baselines" is set as TRUE(which is default one).

But in addition to this when we will set "optimizer_capture_sql_plan_baselines" as TRUE few days before upgrade , in first attempt Optimizer will create/capture new baselines for all the sqls. So my worry was that if this will override any existing baselines/sql profiles which are in use for the critical sqls. considering we dont have SYS_AUTO_SPM_EVOLVE_TASK turned ON), the new baseline has to pass through a manual plan evolution process before its gets in use by the existing query, so we are safe in that perspective as it wont automatically utilize new baselines without our notice. Please correct me if wrong.

Thanks,
Satiah

[ErmanArslansOracleBlog]

Facts:
-----------
*OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES enables or disables the automatic recognition of repeatable SQL statements, as well as the generation of SQL plan baselines for such statements.

*Automatic Plan Capture
The value of the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter, whose default value is FALSE, determines if the system should automatically capture SQL plan baselines. When set to TRUE, the system records a plan history for SQL statements. The first plan for a specific statement is automatically flagged as accepted. Alternative plans generated after this point are not used until it is verified they do not cause performance degradations. Plans with acceptable performance are added to the SQL plan baseline during the evolution phase.

So, if you don't have an automatic evolution, then yes you re safe from that perspective.

Also see -> Automatic SQL Plan Baselines (Doc ID 1930525.1)

Why did you enable optimizer_capture_sql_plan_baselines at this point?

[satish]

Thanks erman.

We enabled it to capture all sql run by concurrent requests,forms,etc.. After upgrade completed,if any sql performance is bad,then we can revert old plan using baseline.

after upgrade if we see any plan deviation with new optimizer version for any query (which was not having any old sql profile/sql plan baselines associated with it before). Then we can simple follow the dba_sql_plan_baselines, to scan through the captured baselines those created just before upgrade and can evolve those and use those to get the plan back in as it used to in old version. Please correct me if my understanding

You might have done multiple upgrades.Pls help us if there is any other alternative.

Thanks,
Satish

[ErmanArslansOracleBlog]

That 's ok then.
-Have a clone of the old system(before upgrade system) as a reference -- In case, you want to check the earlier conditions of a slow running query after the upgrade.
-Also you have the AWR schema to check the before-upgrade conditions after the upgrade..

[ErmanArslansOracleBlog]

Bytheway;

I tested that optimizer_capture_sql_plan_baselines parameter, and as I already mentioned, you re safe there.

->>

***INITIAL STATE:
----------------------------------------------------------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

CREATE AN EXAMPLE TABLE
------------------------
create table T as select * from dba_objects;

QUERY THAT TABLE
------------------------
select count(*) from T where data_object_id between 100 and 200;

Plan hash value: 2966233522
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   292   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   218 |  2834 |   292   (1)| 00:00:04 |
---------------------------------------------------------------------------


SET A BASELINE (ACCEPTED & ENABLED) FOR THAT QUERY AND CREATE AN INDEX
------------------------

Note
-----
   - SQL plan baseline "SQL_PLAN_93xyd2tw4zufv3fdbb376" used for this statement

create index test on T(data_object_id);


ENABLE THE OPTIMIZER CAPTURE PARAMETER
------------------------
alter system set optimizer_capture_sql_plan_baselines=TRUE;


REQUERY THAT TABLE at least 2 TIMES WITH THE SAME QUERY
------------------------

select count(*) from T where data_object_id between 100 and 200;

*** It still does FTS. It uses the existing SQL PLAN BASELINE*****
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   292   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   218 |  2834 |   292   (1)| 00:00:04 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("DATA_OBJECT_ID">=100 AND "DATA_OBJECT_ID"<=200)
 
Note
-----
   - SQL plan baseline "SQL_PLAN_93xyd2tw4zufv3fdbb376" used for this statement


NOW we disable the BASELINE and REQUERY the table with the same QUERY
------------------------------------------------------------------------------------------

declare
drop_result pls_integer;
begin
drop_result := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle => 'SQL_91f7cd16784fe9db',
plan_name => 'SQL_PLAN_93xyd2tw4zufv3fdbb376',
attribute_name=>'ENABLED',
attribute_value=>'NO');
dbms_output.put_line(drop_result);
end;
/

declare
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_91f7cd16784fe9db',
plan_name => 'SQL_PLAN_93xyd2tw4zufvab4563c7');
dbms_output.put_line(drop_result);
end;
/

select count(*) from T where data_object_id between 100 and 200;

--> This time oracle uses INDEX RANGE SCAN, as you may guess.. (but now we don't have any baseline set for that query, so it is excepted..)

Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |      |     1 |    13 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST |    85 |  1105 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

As you see, optimizer_capture_sql_plan_baselines doesn't make Oracle to use new sql plans (even if they are better), if there is sql baseline set for the query.

[satish]

Thanks a lot erman.It helped