Hi,
I am trying to use imported baseline I set for one query but it's not working.
What I did so far :
@db1
1) alter system set optimizer_capture_sql_plan_baselines=true;
2) execute query so baseline would be created
3) create staging table using the DBMS_SPM.CREATE_STGTAB_BASELINE procedure
4) pack that baseline using the DBMS_SPM.PACK_STGTAB_BASELINE procedure
5) export staging table using data pump
6) scp the dump file to the target server
@db2
1) alter system set optimizer_use_sql_plan_baselines = true;
2) import the staging table using the data pump
3) succesfully unpacked baseline using the DBMS_SPM.UNPACK_STGTAB_BASELINE procedure
4) execute the same sql as the one on the db1 for which is created,packed and unpaced baseline
5) query is now using that baseline, which is fixed,accepted and enabled but with the execution plan the same as it was without the baseline
To sum it up,
this is the query ,
select * from atable order by id desc ;
On db1 this query uses this plan ,
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1956699976
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 112M| 20G| 2775K (1)| 00:04:58 |
| 1 | TABLE ACCESS BY INDEX ROWID| ATABLE | 112M| 20G| 2775K (1)| 00:04:58 |
| 2 | INDEX FULL SCAN DESCENDING| A_PK | 112M| | 21099 (1)| 00:00:03 |
---------------------------------------------------------------------------------------
Note
-----
- SQL plan baseline "SQL_PLAN_bb0pj1mwnykvh9ec174aa" used for this statement
And exactly the same query on db2 is still having 'old' plan , although OEM grid is showing it has that unpacked plan attached ,
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2902006383
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 105M| 19G| | 5473K (1)| 00:09:46 |
| 1 | SORT ORDER BY | | 105M| 19G| 28G| 5473K (1)| 00:09:46 |
| 2 | TABLE ACCESS FULL| ATABLE | 105M| 19G| | 869K (1)| 00:01:34 |
--------------------------------------------------------------------------------------
9 rows selected.
What I'm trying to do is force the index operation for that query on db2 , gathering fresh stats and hinting didn't help , just like imported baseline.
Full table scan is always used.
DDL of both tables are the same, size of the table is maybe 0,1 % bigger @ db1 then @ db2 , and exact matching signature is the same as the signature for that sql_id in the v$sql .
Any suggestions what can I check next, did I missed something in the process of baseline packing/migrating/unpacking ?