Performance

Speeding up Initial data load for Oracle to PostgreSQL using Goldengate and copy command

Oracle Goldengate supports Oracle to PostgreSQL migrations by supporting PostgreSQL as a target database, though reverse migration i.e PostgreSQL to Oracle is not supported. One of the key aspect of these database migrations is initial data load phase where full tables data have to copied to the target datastore. This can be a time consuming activity with time taken to load varying based on the table sizes. Oracle suggests to use multiple Goldengate processes to improve the database load performance or to use native database utilities to perform faster bulk-loads.

To use a database bulk-load utility, you use an initial-load Extract to extract source records from the source tables and write them to an extract file in external ASCII format. The file can be read by Oracle’s SQL*Loader, Microsoft’s BCP, DTS, or SQL Server Integration Services (SSIS) utility, or IBM’s Load Utility (LOADUTIL).

Goldengate for PostgreSQL doesn’t provide native file loader support like bcp for MS SQL and sqlloader for Oracle. As an alternative, we can use FORMATASCII option to write data into csv files (or any custom delimiter) and then load them using PostgreSQL copy command .This approach is not automated approach and you will have to ensure that all files are loaded into target database.

In this post, we will evaluate 2 approaches i.e using Multiple replicat Processes and using ASCII dump files with PostgreSQL copy command to load data and compare their performance. Below diagram shows both the approaches

Description of initsyncbulk.jpg follows — Ref: -https://docs.oracle.com/goldengate/1212/gg-winux/GWUAD/wu_initsync.htm#GWUAD561

To compare the scenarios, I created a test table with 200M rows(12GB) and used a RDS PostgreSQL instance (db.r3.4xlarge with 10k PIOPS)

CREATE TABLE scott.Big_table (
   id           NUMBER,
   small_number NUMBER(5),
   big_number   NUMBER,
   short_string VARCHAR2(50),
   created_date DATE,
   CONSTRAINT big_table_pkey PRIMARY KEY (id)
 ) tablespace LRG_TBSP;

--create sequence for PK

create sequence scott.big_table_seq start with 1 increment by 1 cache 500 nomaxvalue;
-- Load data
 INSERT /*+ APPEND */ INTO scott.Big_table
 SELECT scott.big_table_seq.nextval AS id,
        TRUNC(DBMS_RANDOM.value(1,5)) AS small_number,
        TRUNC(DBMS_RANDOM.value(100,10000)) AS big_number,
        DBMS_RANDOM.string('L',TRUNC(DBMS_RANDOM.value(10,50))) AS short_string
        TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)) AS created_date
 FROM   dual
 CONNECT BY level <= 10000;
 COMMIT;
 
 INSERT /*+ APPEND */ INTO scott.Big_table
 SELECT scott.big_table_seq.nextval AS id,
         small_number,
         big_number,
         short_string,
         long_string,
        created_date
 FROM   scott.Big_table;
 COMMIT;

--PostgreSQL Table

CREATE TABLE booker.Big_table (id bigint,
  small_number int, 
  big_number   bigint, 
  short_string VARCHAR(50),  
 created_date TIMESTAMP, 
 CONSTRAINT big_table_pk PRIMARY KEY (id) )

Approach 1 : Using Oracle Goldengate multiple replicat processes to load data

In this approach, I used multiple Oracle Goldengate Replicat processes (8) using @range filter to load data into PostgreSQL.

We were able to get 5k inserts/sec per thread and were able to load the table in ~88 mins with 8 replicat processes.
One key point to remember is that if you are working with EC2 and RDS databases, you should have EC2 machine hosting trail files and RDS instance in same AZ. During the testing, we noticed that insert rate dropped drastically (~800 insert per sec) when using cross AZ writes. Below is replicat parameter file used for performing data load.

SpecialRUN
 END Runtime
 SETENV (NLSLANG=AL32UTF8)
 SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
 SETENV ( PGCLIENTENCODING = "UTF8" )
 SETENV (ODBCINI="/opt/app/oracle/product/ggate/12.2.0.1/odbc.ini" )
 TARGETDB GG_Postgres, USERIDALIAS pguser
 Extfile /fs-a01-a/databases/ggate/initload/i5
 HANDLECOLLISIONS
 DISCARDFILE /opt/app/oracle/product/ggate/12.2.0.1/direrr/rinit1.dsc, APPEND, megabytes 20
 reportcount every 60 seconds, rate
 BATCHSQL;
 MAP scott.big_table, TARGET scott.big_table, FILTER (@RANGE (1,8));;

You will need to create additional replicat process files by making change to the range clause.e.g FILTER (@RANGE (2,8)), FILTER (@RANGE (3,8)), etc.

Approach 2: Data load using PostgreSQL copy command

In second approach, we used parameter file with FORMATASCII option(refer to below snippet) for creating a Goldengate Extract process which dumped the data with ‘|’ delimiter and then used PostgreSQL copy command to load data from these dump files.

Extract Parameter file

SOURCEISTABLE
 SETENV (ORACLE_SID=ggpoc)
 SETENV (NLSLANG=AL32UTF8)
 SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
 SETENV (ORACLE_HOME=/opt/app/oracle/product/11.2.0.4/A10db)
 SETENV (TNS_ADMIN=/opt/app/oracle/local/network/)
 USERIDALIAS gguser
 RMTHOST  xx.222.xx.78,  MGRPORT 8200, TCPBUFSIZE 100000, TCPFLUSHBYTES 300000
 RMTHOSTOPTIONS ENCRYPT AES256
 FORMATASCII,NOHDRFIELDS,NOQUOTE,NONAMES, DELIMITER '|'
 RMTFILE  /fs-a01-a/databases/ggate/initload/i4 , megabytes 1000
 reportcount every 60 seconds, rate
 TABLE scott.BIG_TABLE;

With above parameter file, Goldengate Extract process would send data to remote system and store the data in dump files. These files are then loaded into PostgreSQL using \copy command.

psql> \copy scott.big_table from '/fs-a01-a/databases/ggate/initload/i4000000' with DELIMITER '|';

Data load took 21 mins, which is nearly 4x faster than initial approach. If you remove the Primary key index, then it drops the time taken to ~9 mins to load 200M POC table.

Update:

Oracle GoldenGate 19.1 comes with DataDirect 7.1 PostgreSQL Wire Protocol ODBC driver for PostgreSQL connectivity. You can now add a parameter “BatchMechanism=2” to speed up the inserts. After setting this parameter, odbc driver will start batching inserts into memory buffer and will insert them together instead of single row inserts. You can find details here

To add the parameter, update odbc.ini and add BatchMechanism under the database section

[apgtarget]
Driver=/oracle/product/ggate/19.1.0.0/lib/GGpsql25.so
Description=DataDirect 7.1 PostgreSQL Wire Protocol
BatchMechanism=2
Database=pgbench
HostName=xxx.xxxxxx.us-east-1.rds.amazonaws.com
PortNumber=5432

In my testing, I saw that insert rate increased from 5.3K rows per second to 35K i.e nearly 7x increase. I also noticed that WriteIOPS on this Aurora Instance increased from 20K to 80-100K IOPS

Checking column usage information using dbms_stats

Oracle 11gR2 DBMS_STATS introduced useful function report_col_usage to report column usage i.e if column is being used for equality,like predicates. I came to know it while reading Optimizer whitepaper .As per document, DBMS_STATS.REPORT_COL_USAGE reports column usage information and records all the SQL operations the database has processed for a given object.

This information is used by Oracle gather stats job to decide whether to collect histograms and also number of buckets to be used.So if we specify method_opt =>’FOR ALL COLUMS SIZE AUTO’, oracle stats job will make use of this information. A column is a candidate for a histogram if it has been seen in a where clause predicate, e.g., an equality, range, LIKE, etc.Column usage tracking is enabled by default.

Apart from this, I see two more benefits

a)It can be used to see if all indexed column are being used. If not we can get rid of any extra indexes.

b) In case you are using method_opt =>’FOR ALL INDEXED COLUMNS SIZE AUTO’ in your stats collection script, it can tell additional columns which are being used in where clause. This is really important as this method_opt option does not capture statistics on non-indexed columns. Result can be wrong cardinality estimates and choice of wrong join methods. You can read detailed explanation on Greg Rahn’s post

To see this action I am using EMP,DEPT,SALGRADE table. You can find the scripts for creating these tables on scribd .

Since these are newly created tables, there was no column usage stats. Running the function confirms same

SELECT DBMS_STATS.REPORT_COL_USAGE('AMIT','EMP') FROM DUAL;
LEGEND:
 .......
EQ : Used in single table EQuality predicate
 RANGE : Used in single table RANGE predicate
 LIKE : Used in single table LIKE predicate
 NULL : Used in single table is (not) NULL predicate
 EQ_JOIN : Used in EQuality JOIN predicate
 NONEQ_JOIN : Used in NON EQuality JOIN predicate
 FILTER : Used in single table FILTER predicate
 JOIN : Used in JOIN predicate
 GROUP_BY : Used in GROUP BY expression
 ...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR AMIT.EMP
 ................................
###############################################################################

I ran following set of queries once

select * from emp where empno=7782;
 select ename,job,dname from emp,dept where emp.deptno=dept.deptno;
 select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;

Querying again gives following output now

###############################################################################
COLUMN USAGE REPORT FOR AMIT.EMP
 ................................
1. DEPTNO : EQ_JOIN
 2. EMPNO : EQ
 3. SAL : NONEQ_JOIN
 ###############################################################################

Ran two queries with like and range predicates

select * from emp where ename like 'A%';
 select * from emp where sal >3000;

You can see that ENAME column shows that we have like in query predicate and for SAL a range comparison has been made.

###############################################################################
COLUMN USAGE REPORT FOR AMIT.EMP
 ................................
1. DEPTNO : EQ_JOIN
 2. EMPNO : EQ
 3. ENAME : LIKE
 4. SAL : RANGE NONEQ_JOIN
 ###############################################################################

You can reset the column usage information using following procedure

exec dbms_stats.reset_col_usage('AMIT','EMP')

Note that this deletes the recorded column usage information from dictionary which can have impact on Stats job, so be careful while you are deleting it. There are two more procedures which can help to seed column usage information from other database.

SEED_COL_USAGE – This procedure iterates over the SQL statements in the specified SQL tuning set, compiles them and seeds column usage information for the columns that appear in these statements.

Syntax
DBMS_STATS.SEED_COL_USAGE (sqlset_name IN VARCHAR2, owner_name IN VARCHAR2, time_limit IN POSITIVE DEFAULT NULL);

MERGE_COL_USAGE – This procedure merges column usage information from a source database by means of a dblink into the local database. If column usage information already exists for a given table or column MERGE_COL_USAGE will combine both the local and the remote information.

Syntax
DBMS_STATS.MERGE_COL_USAGE (dblink IN VARCHAR2);

6 June, 2012 by Amit Bansal 11g Performance 2

Poll on Sql Plan Management

Dominic is conducting poll on SPM and Sql Profiles usage on his website. Link can be found here

I have been using SPM and SQL Profiles (using coe_xfr_profile.sql) to fix plans for queries and believe me its very easy and quick way of fixing problems in production database. I have not yet used SPM to baseline everything and only using it on need basis for fixing sql with bad plans. I have been reading about Automatic SQL tuning advisor in 11g and would be trying to use it and see the recommendations proposed by it (will keep accept_sql_profiles to false 🙂 )

8 May, 2012 by Amit Bansal 11g Performance 0

Optimizer Choosing Nested-Loop Joins Instead of Hash-Joins

In one of my databases, one application query suddenly started to pick Nested-Loop joins instead of Hash-Joins and took almost 6 hours to complete which gets completed in less than 10 secs with Hash-Joins.
The same query in another similar database with same configuration and same data is doing fine and using hash joins. There is no difference in data/stats/OS/init parameter etc. (Though I know that no two databases are same)

About the query:
— It is a simple select statement which selects data from a complex view.
— The view comprises of 5 different tables, four of which have more than 15K rows and one have less then 50 rows.
— Statistics are up-to-date in both databases.

I can see the optimizer behavior using 10053 event for the next run of this query but want to know what else can be checked to know why the plan changed suddenly, in this case, before using 10053 event.

Your valuable inputs on this!!!!

18 January, 2012 by Saurabh Sood Performance 10

User Sessions stuck on resmgr:cpu quantum wait event

We were experiencing lot of session getting stuck on resmgr:cpu quantum in our database.
In fact at a time we had 70 sessions which were stuck on this wait event and our cpu load average was touching 60

Checking active resource plan, we found that DEFAULT_MAINTENANCE_PLAN was active. As per 11g Docs

In this plan, any sessions in the SYS_GROUP consumer group get priority. (Sessions in this group are sessions created by user accounts SYS and SYSTEM.) Any resource allocation that is unused by sessions in SYS_GROUP is then shared by sessions belonging to the other consumer groups and subplans in the plan. Of that allocation, 25% goes to maintenance tasks, 5% goes to background processes performing diagnostic operations, and 70% goes to user sessions. To reduce or increase resource allocation to the automated maintenance tasks, you make adjustments to DEFAULT_MAINTENANCE_PLAN.

These plans are associated to 11g windows like MONDAY_WINDOW. You can check it using following query

col window_name format a17
col RESOURCE_PLAN format a25
col LAST_START_DATE format a50
col duration format a15
col enabled format a5
select window_name, RESOURCE_PLAN, LAST_START_DATE, DURATION, enabled from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME	  RESOURCE_PLAN 	    LAST_START_DATE				       DURATION        ENABL
----------------- ------------------------- -------------------------------------------------- --------------- -----
MONDAY_WINDOW	  DEFAULT_MAINTENANCE_PLAN  16-JAN-12 10.00.00.007154 PM PST8PDT	       +000 04:00:00   TRUE
TUESDAY_WINDOW	  DEFAULT_MAINTENANCE_PLAN  10-JAN-12 10.00.00.002781 PM PST8PDT	       +000 04:00:00   TRUE
WEDNESDAY_WINDOW  DEFAULT_MAINTENANCE_PLAN  11-JAN-12 10.00.00.008333 PM PST8PDT	       +000 04:00:00   TRUE
THURSDAY_WINDOW   DEFAULT_MAINTENANCE_PLAN  12-JAN-12 10.00.00.011284 PM PST8PDT	       +000 04:00:00   TRUE
FRIDAY_WINDOW	  DEFAULT_MAINTENANCE_PLAN  13-JAN-12 10.00.00.010937 PM PST8PDT	       +000 04:00:00   TRUE
SATURDAY_WINDOW   DEFAULT_MAINTENANCE_PLAN  14-JAN-12 06.00.00.146968 AM PST8PDT	       +000 20:00:00   TRUE
SUNDAY_WINDOW	  DEFAULT_MAINTENANCE_PLAN  15-JAN-12 06.00.00.003916 AM PST8PDT	       +000 20:00:00   TRUE
WEEKNIGHT_WINDOW									       +000 08:00:00   FALSE
WEEKEND_WINDOW										       +002 00:00:00   FALSE

You can disable the resource_plan by using following commands

execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');

Verify that resource_plan is disabled

select window_name, RESOURCE_PLAN, LAST_START_DATE, DURATION, enabled from DBA_SCHEDULER_WINDOWS;

WINDOW_NAME	  RESOURCE_PLAN 	    LAST_START_DATE				       DURATION        ENABL
----------------- ------------------------- -------------------------------------------------- --------------- -----
MONDAY_WINDOW				    16-JAN-12 10.00.00.007154 PM PST8PDT	       +000 04:00:00   TRUE
TUESDAY_WINDOW				    10-JAN-12 10.00.00.002781 PM PST8PDT	       +000 04:00:00   TRUE
WEDNESDAY_WINDOW			    11-JAN-12 10.00.00.008333 PM PST8PDT	       +000 04:00:00   TRUE
THURSDAY_WINDOW 			    12-JAN-12 10.00.00.011284 PM PST8PDT	       +000 04:00:00   TRUE
FRIDAY_WINDOW				    13-JAN-12 10.00.00.010937 PM PST8PDT	       +000 04:00:00   TRUE
SATURDAY_WINDOW 			    14-JAN-12 06.00.00.146968 AM PST8PDT	       +000 20:00:00   TRUE
SUNDAY_WINDOW				    15-JAN-12 06.00.00.003916 AM PST8PDT	       +000 20:00:00   TRUE
WEEKNIGHT_WINDOW									       +000 08:00:00   FALSE
WEEKEND_WINDOW										       +002 00:00:00   FALSE

I was not concerned about automated tasks taking more cpu as it was already disabled using

execute DBMS_AUTO_TASK_ADMIN.DISABLE;

17 January, 2012 by Amit Bansal 11g 0

Plan Stability using Sql Profiles and SQL Plan Management

PLAN STABILITY

How many times you have noticed a query using Index X when you wanted it to use index Y or query performing Nested Loop join when Hash Join would have completed the query much faster.Or take a scenario when the application suddenly starts using wrong plan after database restart. To solve all these issues oracle provides feature called Plan stability. As per docs

“Plan stability prevents certain database environment changes from affecting the performance characteristics of applications. Such changes include changes in optimizer statistics, changes to the optimizer mode settings, and changes to parameters affecting the sizes of memory structures, such as SORT_AREA_SIZE and BITMAP_MERGE_AREA_SIZE. “

Oracle provides following ways to ensure it

1) Stored outlines
2) Sql Profiles
3)Sql Plan Management

Stored outlines

A stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.
You can read about outlines at ORACLE-BASE

Sql Profiles

As per Jonathan Lewis post,

SQL Profile consists of a stored SQL statement, and a set of hints that will be brought into play when that SQL has to be optimised. Unlike Stored Outlines, the hints for SQL Profiles do not attempt to dictate execution mechanisms directly. Instead they supply arithmetical correction factors to the optimizer as it does its arithemetic as, even with a 100% sample size, it is still possible for the optimizer to misinterpret your statistics and produce an unsuitable execution path.

In principle, if the data distributions do not change, then a stored profile will ensure that the optimizer “understands” your data and does the right thing – even when the data volume changes.

Difference between stored outline and stored profiles

Quoting Tom kyte post

Stored outlines are a set of hints that say “use this index, do this table first, do that next, use this access path, perform this filter then that filter”….

Sql profiles are more like extended statistics – they are the result of “analyzing a query”, the information provided to the optimizer is not HOW to perform the query – but rather better information about how many rows will flow out of a step in the plan, how selective something is.

Say you have a query, you generate a stored outline for it. You now add an index to the underlying table. This index would be GREAT for the query. A stored outline won’t use it, the stored outline says “use this index – query that table – then do this”. Since the GREAT index did not exist when the outline was generated – it won’t be used.

Say you have a query, you generate a profile for it. You now add an index to the underlying table. This index would be GREAT for the query. A profile can use it – since the profile is just more information for the CBO – better cardinality estimates.

So Stored outlines will ensure that whatever plan you fix, it will be used whereas a Sql profile might use different plan if we see some change in data distribution or new indexes.

There are two ways of generating sql profiles

1)Using Sql Tuning Advisor – A lot has been written about this, so I won’t discuss this.
2)Manually – This might be a surprising for lot of people as they might not be aware that we can generate a sql profile manually.I will be discussing this in detail below

Oracle provides a script coe_xfr_sql_profile.sql as part of Note 215187.1 – SQLT (SQLTXPLAIN) – Tool That Helps To Diagnose SQL Statements Performing Poorly which can be used to manually create Sql profiles.

It’s usage is pretty simple.

a) You need to execute the script and it will prompt you to pass sql_id for statement.
b)Then it will scan AWR repository and return Plan hash value for all possible execution plans for query along with average elapsed time.
c)You need to select the plan hash value which you think corresponds to good plan.
d)This will generate a script in current working directory which can be run as sys user and it will create the sql profile

This script is also useful if we wish to move the good plan from Non production environment to Production environment. To give a demo, I will create a table PLAN_STABILITY and index IDX_PLAN_STABI_OWNER on owner column.

SQL> create table plan_stability as select * from dba_objects;
Table created.

SQL> insert into plan_stability select * from dba_objects;
75048 rows created.

SQL> insert into plan_stability select * from plan_stability;
300192 rows created.
..
..
SQL> insert into plan_stability select * from plan_stability;
1200768 rows created.
SQL> commit;

22:15:05 SQL> create index IDX_PLAN_STABI_OWNER on plan_stability(owner);

Index created.
SQL> select count(*) from plan_stability;

COUNT(*)
----------
2401536

Let’s query the table for count of objects owned by SYS

SQL> select count(*) from plan_stability where owner='SYS';

COUNT(*)
----------
1020384

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID g3wubsadyrt37, child number 0
-------------------------------------
select count(*) from plan_stability where owner='SYS'

Plan hash value: 3082746383

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 57 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| IDX_PLAN_STABI_OWNER | 23092 | 135K| 57 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"='SYS')

We see that we are using index IDX_PLAN_STABI_OWNER. Since data is less , Index access is best approach.But for this demo I want to force a Full table scan . Let’s generate a plan with FTS for plan_stability. To do this we can use Invisible indexes (11g feature) so that optimizer ignores index.

SQL> alter index IDX_PLAN_STABI_OWNER invisible;

Index altered.
SQL> select count(*) from plan_stability where owner='SYS';

COUNT(*)
----------
1020384

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID g3wubsadyrt37, child number 0
-------------------------------------
select count(*) from plan_stability where owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS')
19 rows selected.
--Let's make it index visible again

SQL> alter index IDX_PLAN_STABI_OWNER visible;

Index altered.

We will have to take manual AWR snapshots before and end of the test, so that sql plans goes into AWR repository . This is necessary as coe_xfr_profile.sql will look at AWR data for plan history for a sql. Now we run coe_xfr_profile.sql and it will prompt us for sql_id. On passing the sql_id, it reports that there are two plans. We choose plan 363261562 (having higher elapsed time) as we wish to force a Full table scan

SQL>@coe_xfr_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: g3wubsadyrt37
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
3082746383 .064
363261562 .184

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 363261562

Values passed:
~~~~~~~~~~~~~
SQL_ID : "g3wubsadyrt37"
PLAN_HASH_VALUE: "363261562"
Execute coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql
on TARGET system in order to create a custom SQL Profile
with plan 363261562 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.

This has generated a file coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql in same directory which can be run to create the sql profile.Note that script has force_match => FALSE which means that if sql varies in literal, sql_profile will not work. To force it ,we need to set the paramter to force.

SQL>@coe_xfr_sql_profile_g3wubsadyrt37_363261562
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql 11.4.1.4 2011/12/18 csierra $
SQL>REM
SQL>REM Copyright (c) 2000-2010, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID g3wubsadyrt37 based on plan hash
SQL>REM value 363261562.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_g3wubsadyrt37_363261562.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_g3wubsadyrt37_363261562');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 BEGIN
5 sql_txt := q'[
6 select count(*) from plan_stability where owner='SYS'
7 ]';
8 h := SYS.SQLPROF_ATTR(
9 q'[BEGIN_OUTLINE_DATA]',
10 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
11 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
12 q'[DB_VERSION('11.2.0.3')]',
13 q'[OPT_PARAM('_b_tree_bitmap_plans' 'false')]',
14 q'[OPT_PARAM('optimizer_dynamic_sampling' 0)]',
15 q'[ALL_ROWS]',
16 q'[OUTLINE_LEAF(@"SEL$1")]',
17 q'[FULL(@"SEL$1" "PLAN_STABILITY"@"SEL$1")]',
18 q'[END_OUTLINE_DATA]');
19 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
20 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
21 sql_text => sql_txt,
22 profile => h,
23 name => 'coe_g3wubsadyrt37_363261562',
24 description => 'coe g3wubsadyrt37 363261562 '||:signature||'',
25 category => 'DEFAULT',
26 validate => TRUE,
27 replace => TRUE,
28 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
29 END;
30 /

PL/SQL procedure successfully completed.

SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;

SIGNATURE
---------------------
4782703451567619555
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_g3wubsadyrt37_363261562 completed

Let’s verify the plan again now

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID g3wubsadyrt37, child number 1
-------------------------------------
select count(*) from plan_stability where owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS')

Note
-----
- SQL profile coe_g3wubsadyrt37_363261562 used for this statement

In the Note section we can see that Sql Profile “coe_g3wubsadyrt37_363261562” has been used for this statement.

Suppose we now have to create a sql profile, when we don’t have good plan in AWR, then we will have to do a hack. There are two ways to do it

a)You can use coe_xfr_profile.sql to do same. You will have to run the script twice, one for original statement and secondly for hinted statement. Once done you need to copy the values corresponding to h := SYS.SQLPROF_ATTR from hinted sql and replace it in original script.e.g

I am creating a sql profile for following hinted statement

SQL> select /*+ full(plan_stability) */ count(*) from plan_stability where owner='SYS';

  COUNT(*)
----------
   1020384

 SQL> select /*+ full(plan_stability) */ count(*) from plan_stability where owner='SYS';

  COUNT(*)
----------
   1020384

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID	9p07a64q8fgrn, child number 0
-------------------------------------
select /*+ full(plan_stability) */ count(*) from plan_stability where
owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id  | Operation	   | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |		    |	    |	    |  2476 (100)|	    |
|   1 |  SORT AGGREGATE    |		    |	  1 |	  6 |		 |	    |
|*  2 |   TABLE ACCESS FULL| PLAN_STABILITY | 23092 |	135K|  2476   (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("OWNER"='SYS')

--Creating Sql profile now

SQL>@coe_xfr_profile.sql 9p07a64q8fgrn 363261562

Parameter 1:
SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      363261562        .193

Parameter 2:
PLAN_HASH_VALUE (required)

Values passed:
~~~~~~~~~~~~~
SQL_ID	       : "9p07a64q8fgrn"
PLAN_HASH_VALUE: "363261562"

Execute coe_xfr_sql_profile_9p07a64q8fgrn_363261562.sql
on TARGET system in order to create a custom SQL Profile
with plan 363261562 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

Now we create sql profile script for original statement using plan_hash_value corresponding to index access

20:47:52 SQL> @coe_xfr_profile.sql g3wubsadyrt37 3082746383

Parameter 1:
SQL_ID (required)

PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     3082746383        .084
      363261562        .184

Parameter 2:
PLAN_HASH_VALUE (required)

Values passed:
~~~~~~~~~~~~~
SQL_ID	       : "g3wubsadyrt37"
PLAN_HASH_VALUE: "3082746383"

Execute coe_xfr_sql_profile_g3wubsadyrt37_3082746383.sql
on TARGET system in order to create a custom SQL Profile
with plan 3082746383 linked to adjusted sql_text.

COE_XFR_SQL_PROFILE completed.

Now copy following section from coe_xfr_sql_profile_9p07a64q8fgrn_363261562.sql and replace in the coe_xfr_sql_profile_g3wubsadyrt37_3082746383.sql. Also change the name from coe_g3wubsadyrt37_3082746383 to coe_g3wubsadyrt37_363261562

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
q'[DB_VERSION('11.2.0.3')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "PLAN_STABILITY"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

This has again created a sql profile with FTS plan.

SQL> select name,SIGNATURE,SQL_TEXT,FORCE_MATCHING,STATUS from dba_sql_profiles;

NAME						    SIGNATURE SQL_TEXT								     FOR STATUS
------------------------------ ------------------------------ ---------------------------------------------------------------------- --- --------
coe_g3wubsadyrt37_363261562		  4782703451567619555 select count(*) from plan_stability where owner='SYS'		     NO  ENABLED

b) You can use scripts provided by Kerry Osborne in following article
http://kerryosborne.oracle-guy.com/2009/10/how-to-attach-a-sql-profile-to-a-different-statement-take-2/

SQL Plan Management

Let’s use the 11g feature SQL Plan Management to implement plan stability.SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient.
The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system. We can capture plans

a)Automatically – We can use OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true and capture the sql baseline plans. This is pretty useful if you are upgrading database as you can capture good plans and then upgrade the database without worrying about plan change. e.g Post 11g upgrade from 10.2.0.4, set optimizer_features_enable=10.2.0.4 and capture all the plans.Once done you can set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false and also set optimizer_features_enable back to 11.1/11.2. You can then enable/disable the plans or mark them Fixed. Fixed plans get preference over non-fixed plans.

Note that only plans for repeatable statements are stored in the SPM

b)Manually – We can use DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE and DBMS_SPM.LOAD_PLANS_FROM_SQLSET to load plans from cursor cache and Sqlset respectively.

Loading plan from Cursor Cache

We will discuss fucntion DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE to load plans from the cursor cache.In our test,we need to fix plan 363261562 for sql_id g3wubsadyrt37.Since the plan is available in cursor cache, we will use following syntax

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => 'g3wubsadyrt37',
PLAN_HASH_VALUE =>363261562,
FIXED =>'YES');
dbms_output.put_line('Value is '||my_plans);
END;
/

It will return number of plans loaded.Let’s verify baseline by querying dba_sql_plan_baselines

SQL>select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3621540b0 YES YES YES

We can plan corresponding to this baseline

SQL> select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'SQL_425f937308b8fde3',
format=>'basic')); 

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------

SQL handle: SQL_425f937308b8fde3
SQL text: select count(*) from plan_stability where owner='SYS'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_44rwmfc4bjzg3621540b0	  Plan id: 1645559984
Enabled: YES	 Fixed: NO	Accepted: YES	  Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 363261562

---------------------------------------------
| Id  | Operation	   | Name	    |
---------------------------------------------
|   0 | SELECT STATEMENT   |		    |
|   1 |  SORT AGGREGATE    |		    |
|   2 |   TABLE ACCESS FULL| PLAN_STABILITY |
---------------------------------------------

20 rows selected.

We can check if our original query is using the plan

SQL> select count(*) from plan_stability where owner='SYS';

COUNT(*)
----------
1020384

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID g3wubsadyrt37, child number 1
-------------------------------------
select count(*) from plan_stability where owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS')

Note
-----
- SQL plan baseline SQL_PLAN_44rwmfc4bjzg3621540b0 used for this statement

We see from Note section that SQL plan baseline SQL_PLAN_44rwmfc4bjzg3621540b0 has been used for the statement.

Loading plan from AWR/SQL Tuning set

If you know that good plan of the query is available in AWR, then you can use dbms_spm.load_plans_from_sqlset. To do this we need to first create a sql tuning set. This can be done by using DBMS_SQLTUNE

BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'sqlset_g3wubsadyrt37',
description => 'Example to show dbms_spm.load_plans_from_sqlset');
END;
/

Let’s load the sql_id g3wubsadyrt37 in this Sql tuning set from AWR. We need to pass begin_snap and end_snap for SQL. We can also use basic_filter clause to restrict this sqlset to only one particular sql_id.

DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
BEGIN_SNAP => 641,
END_SNAP => 667,
BASIC_FILTER => 'sql_id = ''g3wubsadyrt37''',
ATTRIBUTE_LIST =>'ALL')) p;

DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'sqlset_g3wubsadyrt37',
populate_cursor => baseline_cursor);
END;
/

Lets verify that both plans are loaded in the sqlset

SELECT SQL_ID,PLAN_HASH_VALUE FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(
'sqlset_g3wubsadyrt37'));

SQL_ID PLAN_HASH_VALUE
------------- ---------------
g3wubsadyrt37 363261562
g3wubsadyrt37 3082746383

Next we create sql plan baseline using DBMS_SPM.LOAD_PLANS_FROM_SQLSET

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
SQLSET_NAME => 'sqlset_g3wubsadyrt37'
);
dbms_output.put_line('Value is '||my_plans);
END;
/

We now see that two plans are loaded

SQL> select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3621540b0 YES YES NO
SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3ec110d89 YES YES NO

We can disable the SQL_PLAN_44rwmfc4bjzg3ec110d89 as we only want FTS plan

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle =>'SQL_425f937308b8fde3',
PLAN_NAME =>'SQL_PLAN_44rwmfc4bjzg3ec110d89',
attribute_name =>'enabled',
attribute_value=>'NO');
dbms_output.put_line('Value is '||my_plans);
END;
/

Above statement will mark the index plan as disabled and our query will use only FTS plan.

To drop the baselines, we can use following syntax

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_425f937308b8fde3'
);
dbms_output.put_line('Value is '||my_plans);
END;
/

Till now we have discussed scenarios when we have good plan in cursor cache/AWR. Suppose there is no good plan available but we can generate a good plan using hints. With SPM we can easily transfer profile from hinted sql to our original statement. Let’s see it in action

First we need to create a baseline with existing plan for query. This is required as it would generate a sql_handle which can be used to assign a plan. We are using PLAN_HASH_VALUE =>3082746383 i.e Indexed access path to create the sql baseline

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => 'g3wubsadyrt37',
PLAN_HASH_VALUE =>3082746383
);
dbms_output.put_line('Value is '||my_plans);
END;
/

SQL>select sql_handle, plan_name, enabled, accepted, fixed,sql_text from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC FIX SQL_TEXT
------------------------------ ------------------------------ --- --- --- --------------------------------------------------------------------------------
SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3ec110d89 YES YES NO select count(*) from plan_stability where owner='SYS'

Querying dba_sql_plan_baselines we get sql_handle as SQL_425f937308b8fde3.Now let’s create a plan for query using hints.

SQL> select /*+ full(plan_stability) */ count(*) from plan_stability where owner='SYS';

COUNT(*)
----------
1020384

SQL> select /*+ full(plan_stability) */ count(*) from plan_stability where owner='SYS';

COUNT(*)
----------
1020384

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID 9p07a64q8fgrn, child number 0
-------------------------------------
select /*+ full(plan_stability) */ count(*) from plan_stability where
owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS')

We need to now copy plan corresponding to sql_id=9p07a64q8fgrn and plan_hash_value=363261562. This can be done by using sql_handle for original statement and using sql_id/plan_hash_value of hinted statement

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '9p07a64q8fgrn',
sql_handle =>'SQL_425f937308b8fde3',
PLAN_HASH_VALUE =>363261562,
FIXED =>'YES'
);
dbms_output.put_line('Value is '||my_plans);
END;
/

Note that I have used FIXED=>YES argument which would mark this plan as Fixed.A fixed plan takes precedence over a non-fixed plan. We can drop the old baseline plan now by passing sql_handle and plan_name. If you use only sql_handle, it will drop both the plans

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_425f937308b8fde3',
PLAN_NAME =>'SQL_PLAN_44rwmfc4bjzg3ec110d89');
dbms_output.put_line('Value is '||my_plans);
END;
/

Instead of dropping plan, you can also disable the above plan using DBMS_SPM.ALTER_SQL_PLAN_BASELINE

DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle =>'SQL_425f937308b8fde3',
PLAN_NAME =>'SQL_PLAN_44rwmfc4bjzg3ec110d89',
attribute_name =>'enabled',
attribute_value=>'NO');
dbms_output.put_line('Value is '||my_plans);
END;
/

We now have two plans in baseline but only enabled plan SQL_PLAN_44rwmfc4bjzg3621540b0 will be used by the query.

Note: Whenever we create/drop sql baseline plan, sql is purged from cursor cache and we perform a hard parse.

While testing this , I found that if I create a alias for table , SQL Plan baseline is not working

SQL> select /*+ full(a) */ count(*) from plan_stability a where owner='SYS';

COUNT(*)
----------
1020384

Elapsed: 00:00:00.18
select /*+ full(a) */ count(*) from plan_stability a where owner='SYS';

COUNT(*)
----------
1020384

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID 85hj3c8570fdu, child number 0
-------------------------------------
select /*+ full(a) */ count(*) from plan_stability a where owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS')

SQL>DECLARE
my_plans pls_integer;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
sql_id => '85hj3c8570fdu',
sql_handle =>'SQL_425f937308b8fde3',
PLAN_HASH_VALUE =>363261562,
FIXED =>'YES'
);
dbms_output.put_line('Value is '||my_plans);
END;
/

SQL>select sql_handle, plan_name, enabled, accepted, fixed,sql_text from dba_sql_plan_baselines;

SQL_HANDLE PLAN_NAME ENA ACC FIX SQL_TEXT
------------------------------ ------------------------------ --- --- --- --------------------------------------------------------------------------------
SQL_425f937308b8fde3 SQL_PLAN_44rwmfc4bjzg3621540b0 YES YES YES select count(*) from plan_stability where owner='SYS'

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------
SQL_ID g3wubsadyrt37, child number 1
-------------------------------------
select count(*) from plan_stability where owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2476 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"='SYS')

We can check baseline information using dbms_xplan.display_sql_plan_baseline function.Let’s see why we didnt use FTS even though profile was successfully created

SQL> select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'SQL_425f937308b8fde3',
format=>'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------

SQL handle: SQL_425f937308b8fde3
SQL text: select count(*) from plan_stability where owner='SYS'
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_44rwmfc4bjzg3621540b0 Plan id: 1645559984
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 3082746383

--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX RANGE SCAN| IDX_PLAN_STABI_OWNER |
--------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_44rwmfc4bjzg3ec110d89 Plan id: 3960540553
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 3082746383

--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | INDEX RANGE SCAN| IDX_PLAN_STABI_OWNER |
--------------------------------------------------

From above we see that SQL_PLAN_44rwmfc4bjzg3621540b0 is showing plan which uses Index and not FTS.I am not sure if this is correct behavior or a bug. One reason could be that same alias is not present in parent table.

I believe Sql Plan Management is superior to SQL Profile and outlines as it allows you to have multiple plans for a given sql statement. Also a nightly maintenance job runs automatic SQL tuning task and targets high-load SQL statements.If it thinks that there is better plan , then it adds that SQL Plan baseline to history. This is called Evolving Sql Plan Baselines.

Christian Antognini has written a good article on Automatic Evolution of SQL Plan Baselines which explains this feature.

Licensing

As per Optimizer blog article ,DBMS_SPM is not licensed until we are loading plans from SQL Tuning set (which requires tuning pack).SQL profiles are licensed and require diagnostic and tuning pack.

20 December, 2011 by Amit Bansal Featured Performance 10