Re: EBS database upgrade - concurrent requests query performance
Posted by ErmanArslansOracleBlog on Oct 21, 2022; 3:53pm
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/EBS-database-upgrade-concurrent-requests-query-performance-tp11138p11155.html
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.