SQL Profile and Plan Baseline Notes

Optimizer with Oracle Database 12c
Oracle White Paper – June 2013

Click to access twp-optimizer-with-oracledb-12c-1963236.pdf

What is the difference between SQL Profiles and SQL Plan Baselines?

The difference between SQL profiles and SQL plan baselines and why SQL profiles can be shared but SQL plan baselines can’t.

By Maria Colgan-Oracle on May 08, 2012
https://blogs.oracle.com/optimizer/entry/what_is_the_different_between

Plan Stability using Sql Profiles and SQL Plan Management
Amit Bansal / 20 December, 2011
http://askdba.org/weblog/2011/12/plan-stability-using-sql-profiles-and-sql-plan-management/

what-is-the-difference-between-sql-profile-and-spm-baseline
3 OCT/10
http://intermediatesql.com/oracle/what-is-the-difference-between-sql-profile-and-spm-baseline/

Comments from Kerry Osborne September 9th, 2011 – 18:31
You didn’t mention the two biggest differences (in my opinion).

1.
Baselines know what plan they are trying recreate and SQL Profiles do not.
SQL Profiles will blindly apply any hints it has and what you get is what you get.
Baselines will apply the hints and if the optimizer gets the plan it was expecting, it uses the plan.
If it doesn’t come up with the expected plan, the hints are thrown away and the optimizer tries again (possibly with the hints from another accepted Baseline).

2.
Profiles have a “force matching” capability that allows them to be applied to multiple statements that differ only in the values of literals.
Think of it as a just in time cursor sharing feature. Baselines do not have this ability to act on multiple statements.

Comments from Kerry Osborne January 25th, 2012 – 16:38

I have seen Baselines be disregarded, even without such extreme conditions as a specified index having been removed.

The reason for this is that Baselines attempt to apply enough hints to limit the choices the optimizer has to a single plan,
but there are situations where the set of hints is not sufficient to actually force the desired plan.

What I mean is that the hints will eliminate virtually all possibility but there still may be a few that are valid and so it’s possible to get a different plan.

In fact, I have even seen situations where the act of creating a Baseline causes the plan to change.

This is clearly not intended behavior but it can happen.

I think I blogged about that once. At any rate, in these cases where the optimizer arrives at a different plan than was expected,
the optimizer uses a plan generated completely without the hints (or possible picks another accepted Baseline and applies it’s hints).

This behavior is very different from SQL Profiles which blindly apply the hints.

I should also note that SQL Profiles can also be created manually with the same set of hints that would be used in a Baseline and there is a MOS note regarding how to do this.

I have blogged about that as well here:
http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/

Updated Oct 19, 2015

Strategies for Minimising SQL Execution Plan Instability
https://orastory.wordpress.com/2015/05/01/strategies-for-minimising-sql-execution-plan-instability

To baseline or not to baseline?
https://mikepargeter.wordpress.com/2015/07/21/to-baseline-or-not-to-baseline/

What you need to know about SQL Plan Management and Auto Capture
https://blogs.oracle.com/optimizer/entry/what_you_need_to_know

Advertisement

Scaling Export and Import Tables Residing in Different Schemas 10gR2

Our team was tasked to unpartion all partition tables.

Import: Release 11.2.0.4.0 has PARTITION_OPTIONS=DEPARTITION but we are on Release 10.2.0.4.0

The first step was to find all schemas with partition tables.

select owner, table_name, partitioning_type, subpartitioning_type, partition_count, status 
from dba_part_tables where owner not in ('SYS','SYSTEM') 
and (owner,table_name) not in (
 select owner,mview_name table_name 
 from dba_mviews 
 where owner not in ('SYS','SYSTEM') 
) 
order by 1,2
;

NOTE: SQL is not 100% fail proof as we ran into a scenario where the table and materialized view had the same name.

Export the metatadata for table from multiple schemas failed:

UDE-00012: table mode exports only allow objects from one schema

Export/Import DataPump Parameter TABLES – How to Export and Import Tables Residing in Different Schemas (Doc ID 277905.1)

Solution 1: Use combination of SCHEMAS and INCLUDE parameters.
Example:
File: expdp_tabs.par 
-------------------- 
DIRECTORY = my_dir  
DUMPFILE  = expdp_tabs.dmp  
LOGFILE   = expdp_tabs.log  
SCHEMAS   = scott,hr,oe   
INCLUDE   = TABLE:"IN ('EMP','EMPLOYEES','CATEGORIES_TAB')" 

Great solution but not scalable.

Dig to find – How to export tables from multiple schemas with Oracle Data Pump in Oracle 10g and 11g databases
http://uralural.blogspot.com/2011/06/how-to-export-tables-from-multiple.html

I will let you read the post; however, the following was the key for me.

[oracle@srvdb01]:/transfer/uural/datapumpdemo > expdp '"/ as sysdba"' directory=UURAL_DATAPUMPDEMO 
dumpfile=u0001-u0002_tables logfile=u0001-u0002_tables schemas=U0001,U0002 
INCLUDE=TABLE:\"IN \(SELECT table_name FROM u0001.expdp_tables\)\"                                            

Database has 22 partition tables.

*** List partition tables, exluding MVIEW ***
22 rows selected.

There are 11 schemas with partition tables.

*** List partition tables count by owner, exluding MVIEW ***
11 rows selected.

Partition table SYSTEM_QUEUE resides in 7 different schemas and ACCOUNT_OBJECT_TRANSACTIONS resides in 2 different schemas.

*** List same table name across owner, exluding MVIEW ***
TABLE_NAME			 COUNT(*)
------------------------------ ----------
SYSTEM_QUEUE				7
ACCOUNT_OBJECT_TRANSACTIONS		2

Create tables to use for export.

create table OWNER01.expdp_tables (table_name varchar2(30))
;
insert into OWNER01.expdp_tables
select DISTINCT table_name
from dba_part_tables
where owner not in ('SYS','SYSTEM')
and (owner,table_name) not in (
  select owner,mview_name table_name from dba_mviews where owner not in ('SYS','SYSTEM')
)
; 

Create export parameter.

$ cat expdp_schema_TEST.par 
directory=EXP
userid="/ as sysdba"
metrics=Y
content=METADATA_ONLY
dumpfile=TEST.dmp
logfile=exp_TEST.log
schemas=OWNER01,OWNER02,OWNER03,OWNER04,OWNER05,OWNER06,OWNER07,OWNER08,OWNER09,OWNER10,OWNER11
INCLUDE=TABLE:"IN (SELECT table_name FROM OWNER01.expdp_tables)"

Perform export.

$ expdp parfile=expdp_schema_TEST.par 

Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 September, 2015 14:04:34

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  parfile=expdp_schema_TEST.par 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 22 TABLE objects in 10 seconds
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 116 OBJECT_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 85 INDEX objects in 20 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 31 CONSTRAINT objects in 5 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
     Completed 85 INDEX_STATISTICS objects in 1 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 2 REF_CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 29 TRIGGER objects in 30 seconds
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
     Completed 22 TABLE_STATISTICS objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
     Completed 1 POST_TABLE_ACTION objects in 0 seconds
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u02/oracle/exp/TEST.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:05:46

Create import parameter.

$ cat impdp_sqlfile_TEST.par 
directory=EXP
userid="/ as sysdba"
metrics=Y
exclude=STATISTICS
dumpfile=TEST.dmp
logfile=imp_TEST.log
sqlfile=create_TEST.sql

Perform import.

$ impdp parfile=impdp_sqlfile_TEST.par 

Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 10 September, 2015 14:07:36

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  parfile=impdp_sqlfile_TEST.par 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
     Completed 22 TABLE objects in 2 seconds
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
     Completed 116 OBJECT_GRANT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
     Completed 85 INDEX objects in 8 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     Completed 31 CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     Completed 2 REF_CONSTRAINT objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
     Completed 29 TRIGGER objects in 0 seconds
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
     Completed 1 POST_TABLE_ACTION objects in 0 seconds
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 14:07:48

BVT

$ grep -c "CREATE TABLE" /u02/oracle/exp/create_TEST.sql|sort
22