SLOW SQL QUERY DUE TO FIXED BASELINE ORACLE
There was a customer who had a performance issue for a batch query which was executing as per expected timelines earlier and now it take more than half of previous time.
After investigating the problem, the query picked up the fixed baseline plan which was not suitable for all data sets as the query had bind variables in it which fetched variable results.
Enable auto capture baselines for all the sql. Warning! this may capture millions of baseline if multiple queries are executed in database which may exhaust sysaux space.
This is just a sample test case query for understanding purpose.
Execute the query for the first time with out creating any index on the where clause.
The query execute with a full table scan and also the execution plan is captured for the query due to auto capture baseline.
SQL> ALTER system set optimizer_capture_sql_plan_baselines=TRUE;
System altered.
SQL> set autot traceonly
SQL> SELECT name,type from dspm where type='PROCEDURE' and owner='MDSYS';
45 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3837617297
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2515 | 75450 | 1570 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DSPM | 2515 | 75450 | 1570 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TYPE"='PROCEDURE' AND "OWNER"='MDSYS')
Note
-----
- SQL plan baseline "SQL_PLAN_74gw55r480awsb495c64e" used for this statement
Check if the plan is accepted as a benchmark plan. The plan is accepted but not fixed.
SQL> col SQL_HANDLE for a20
SQL> col PLAN_NAME for a20
SQL> SELECT sql_handle, sql_text, plan_name, enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE plan_name='SQL_PLAN_74gw55r480awsb495c64e';
SQL_HANDLE SQL_TEXT PLAN_NAME ENA ACC FIX
-------------------- -------------------- -------------------- --- --- ---
SQL_723f852dc8802b98 SELECT name,type fro SQL_PLAN_74gw55r480a YES YES NO
m dspm where type='P wsb495c64e
ROCEDURE' and owner=
'MDSYS'
Accidentally, the full table scan plan is fixed considering that the plan performs well at the present.
SQL> var v_5cygq95qjwmzr_ft number
SQL> EXEC :v_5cygq95qjwmzr_ft := DBMS_SPM.ALTER_SQL_PLAN_BASELINE ( sql_handle => 'SQL_723f852dc8802b98', plan_name => 'SQL_PLAN_74gw55r480awsb495c64e', attribute_name => 'FIXED', attribute_value => 'YES' );
PL/SQL procedure successfully completed.
Now, application team expects a better performance for the query and creates index on one specific column ‘type’. Again the same fixed plan is used, even though a better index plan is available.
SQL> CREATE index dtype on dspm(type);
Index created.
SQL> set autot traceonly
SQL> SELECT name,type from dspm where type='PROCEDURE' and owner='MDSYS';
45 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3837617297
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2515 | 75450 | 1570 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DSPM | 2515 | 75450 | 1570 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TYPE"='PROCEDURE' AND "OWNER"='MDSYS')
Note
-----
- SQL plan baseline "SQL_PLAN_74gw55r480awsb495c64e" used for this statement
Again, application team creates another index on ‘owner’ column. This time again the fixed full table scan is used due to baseline.
SQL> CREATE index downer on dspm(owner);
Index created.
SQL> set autot traceonly
SQL> SELECT name,type from dspm where type='PROCEDURE' and owner='MDSYS';
45 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3837617297
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2515 | 75450 | 1570 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DSPM | 2515 | 75450 | 1570 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TYPE"='PROCEDURE' AND "OWNER"='MDSYS')
Note
-----
- SQL plan baseline "SQL_PLAN_74gw55r480awsb495c64e" used for this statement
So, the above test case is just for learning purpose.
In reality, the same case may be suitable, where sql tuning advisor would have recommended to accept full table scan baseline but not to fix it. DBA might have faced performance issue during the time with a worse plan than full table scan plan and he might have fixed the fts plan considering it fast.
Consider that the DBA left the job and another new DBA comes in. After some days, there may be change on the size of data to be retrieved like more rows for the sql. At that time, optimizer still use the fts plan because of fixed baseline and another performance issue arise as a consequence of previous fix.
Summary:
- Always make sure of the existing sql profiles and baselines for the sql
- Make sure of the status of those baselines if they are enabled or accepted or fixed or all of them together
- If so, drop the unnecessary bad baselines created on older plans which may not be suitable currently
First identify the number of baselines available for the sql id.
SQL> set lines 200 pages 1000
SQL> col PLAN_NAME for a20
SQL> col SQL_TEXT for a20
SQL> SELECT SQL_TEXT,SIGNATURE,SQL_HANDLE,PLAN_NAME,ENABLED,ACCEPTED,FIXED,ELAPSED_TIME from dba_sql_plan_baselines WHERE SQL_TEXT like '%SELECT name,type from dspm where type%';
SQL_TEXT SIGNATURE SQL_HANDLE PLAN_NAME ENA ACC FIX ELAPSED_TIME
-------------------- ---------- ------------------------------ -------------------- --- --- --- ------------
SELECT name,type fro 8.2324E+18 SQL_723f852dc8802b98 SQL_PLAN_74gw55r480a YES YES YES 0
m dspm where type='P wsb495c64e
ROCEDURE' and owner=
'MDSYS'
One baseline is available for the SQL and it is fixed for the query. This is the reason why a fixed plan is used irrespective of other good plans.
Check the contents of the baseline for the plan.
SQL> SELECT * from table(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(PLAN_NAME => 'SQL_PLAN_74gw55r480awsb495c64e'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_723f852dc8802b98
SQL text: SELECT name,type from dspm where type='PROCEDURE' and owner='MDSYS'
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_74gw55r480awsb495c64e Plan id: 3029714510
Enabled: YES Fixed: YES Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
Plan hash value: 3837617297
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2515 | 75450 | 1570 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| DSPM | 2515 | 75450 | 1570 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TYPE"='PROCEDURE' AND "OWNER"='MDSYS')
25 rows selected.
Drop the baseline containing the worst plan and purge the sql_id from the shared pool.
SQL> DECLARE
varint INTEGER;
BEGIN
varint := DBMS_SPM.drop_sql_plan_baseline(plan_name => 'SQL_PLAN_74gw55r480awsb495c64e');
END;
/ 2 3 4 5 6
PL/SQL procedure successfully completed.
SQL> SELECT address,hash_value from v$sql where sql_id='3bjw18gb6jthj';
ADDRESS HASH_VALUE
---------------- ----------
0000000076701840 3597198865
SQL> EXEC dbms_shared_pool.purge('0000000076701840,3597198865','C');
PL/SQL procedure successfully completed.
SQL> SELECT address,hash_value from v$sql where sql_id='3bjw18gb6jthj';
no rows selected
After the baseline with worst plan is dropped, good plan is used.
SQL> set autot traceonly
SQL> SELECT name,type from dspm where type='PROCEDURE' and owner='MDSYS';
45 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2382681726
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2515 | 75450 | 405 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DSPM | 2515 | 75450 | 405 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DTYPE | 15536 | | 44 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='MDSYS')
2 - access("TYPE"='PROCEDURE')
Note
-----
- SQL plan baseline "SQL_PLAN_74gw55r480aws87226bf8" used for this statement
Summary:
- It is always good to verify that if there are any fixed baseline plans for a query during performance issue and also examine the history
- Do not fix the baseline plan unless until the data for the objects are used only for archival purpose which means that the table data is stable for long time and there is no data load or growth.
- Fix the baseline only if the data is constant.
- If the data is dynamic, then consider only accepting the good plan.