Performance

Limiting I/O and CPU resources using 11g Oracle Resource Manager

Recently I was working on using Oracle DBMS_RESOURCE_MANAGER to limit resources usage by read-only queries. This was required to prevent long running ad-hoc and poorly written queries.Instead of writing custom sql /script to kill long running session, we decided to utilize Oracle Database Resource Manager.
We decided using elapsed_time as criteria for cancelling sql.But during tests we found out that any I/O bound query didn’t kill after specified switch_time.
On checking My Oracle Support, we came across Note ID 1485199.1, which discussed the exact situation faced by us.

As per note , starting 11.2 oracle has fixed the Unpublished Bug 8202097 where some waits were included in the active time and could trigger switch_time early. Due to this fix, switch_time applies for the execution time (in CPU seconds) and not for the total elapsed time (which includes wait times also) of the query.

What this means is that any query which is waiting on wait class other then CPU (e.g I/O ) will not be killed based on switch time.Switch_time applies only to time spent on CPU. Waits are not included.

Checking Oracle 11g Documentation, we found that Oracle had introduced new parameter in create_plan_directive named switch_io_megabytes which can be used to cancel sql after specified I/O limit is reached.
We used following code to create resource manager plan with following directives

a)Limit Cpu time to 3600 s
b)Limit Parallelism to 4
c)Limit I/O to 20000Mb (~20G)

begin
sys.dbms_resource_manager.clear_pending_area();
sys.dbms_resource_manager.create_pending_area();
sys.dbms_resource_manager.create_consumer_group(CONSUMER_GROUP=>'CPU_IO_LIMIT_GRP',
COMMENT=>'Consumer group for RO_USER to limit parallelism,I/O and CPU Time');
sys.dbms_resource_manager.set_consumer_group_mapping(attribute => DBMS_RESOURCE_MANAGER.ORACLE_USER,
value => 'RO_USER',consumer_group =>'CPU_IO_LIMIT_GRP');

-- Create resource plan:
sys.dbms_resource_manager.create_plan(PLAN=> 'CPU_IO_LIMIT',COMMENT=>'Cancel Sql plan for RO_USER');

--Need to create plan_directive
--Limiting parallelism to max 4, I/O Limit to 10000Mb and CPU_time to 3600 sec
sys.dbms_resource_manager.create_plan_directive(
PLAN=> 'CPU_IO_LIMIT',
GROUP_OR_SUBPLAN=>'CPU_IO_LIMIT_GRP',
COMMENT=>'Kill statement after exceeding 3600 sec , limit parallelism to max 4 and limit i/o to 20000M ',
PARALLEL_DEGREE_LIMIT_P1 => 4,
SWITCH_GROUP=>'CANCEL_SQL',
SWITCH_TIME=>3600,
SWITCH_IO_MEGABYTES=>20000,
SWITCH_ESTIMATE=>false
);

--Its compulsory to specify directive for OTHER_GROUPS else this will fail
dbms_resource_manager.create_plan_directive(PLAN=> 'CPU_IO_LIMIT',GROUP_OR_SUBPLAN=>'OTHER_GROUPS',CPU_P1=>100);
sys.dbms_resource_manager.validate_pending_area;
sys.dbms_resource_manager.submit_pending_area;
end;
/

--Grant RO_USER to switch group
exec dbms_resource_manager_privs.grant_switch_consumer_group('RO_USER','CPU_IO_LIMIT_GRP',false);
--Set initial group for RO_USER to CPU_IO_LIMIT_GRP
exec dbms_resource_manager.set_initial_consumer_group('RO_USER','CPU_IO_LIMIT_GRP');

To enable resource manager plan

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='CPU_IO_LIMIT';

We started query on big table and specified parallel degree as 8

alter session force parallel query parallel 8;

select /*+ full(a)  */ count(*) from big_table a;

From V$px_session we can verify that we requested 8 degree but we are getting only 4 due to resource manager plan directive.

INST_ID Username    QC/Slave   Slave Set  SID    QC SID Requested DOP Actual DOP MODULE                SQL_ID      EVENT             STATUS
---------- ------------ ---------- ---------- ------ ------ ------------- ---------- ------------------------------ ------------- ------------------------- --------
2  - p000    (Slave)    1          390    579        8       4 SQL*Plus                7fgfym909mqmn direct path read        ACTIVE
2    RO_USER    QC                  579    579                 SQL*Plus                7fgfym909mqmn PX Deq: Execute Reply     ACTIVE
2  - p001    (Slave)    1          580    579        8       4 SQL*Plus                7fgfym909mqmn direct path read        ACTIVE
2  - p002    (Slave)    1          772    579        8       4 SQL*Plus                7fgfym909mqmn direct path read        ACTIVE
2  - p003    (Slave)    1          966    579        8       4 SQL*Plus                7fgfym909mqmn direct path read        ACTIVE

I was monitoring the resource usage using v$rsrc_session_info in separate session

SELECT s.sid sess_id, g.name consumer_group,
s.state, s.consumed_cpu_time cpu_time, s.cpu_wait_time, s.queued_time,(s.CURRENT_SMALL_READ_MEGABYTES+s.CURRENT_LARGE_READ_MEGABYTES) read_MB,(s.CURRENT_SMALL_WRITE_MEGABYTES+s.CURRENT_LARGE_WRITE_MEGABYTES) write_mb
FROM v$rsrc_session_info s, v$rsrc_consumer_group g
WHERE s.current_consumer_group_id = g.id and g.name='CPU_IO_LIMIT_GRP';

After some time our original query got cancelled with following error

ERROR at line 1:
ORA-12801: error signaled in parallel query server P001, instance prod02:orcl02 (2)
ORA-56720: I/O data limit exceeded - call aborted

Just before failure , v$rsrc_session_info output looked like this indicating that we reached 20000M limit specified for I/O

SESS_ID CONSUMER_GROUP            STATE     CPU_TIME CPU_WAIT_TIME QUEUED_TIME    READ_MB     WRITE_MB
---------- -------------------------------- ---------- ---------- ------------- ----------- ---------- ----------
1347 CPU_IO_LIMIT_GRP        WAITING           62          0       0         1        0
1154 CPU_IO_LIMIT_GRP        WAITING        38050          0       0     18707        0
774 CPU_IO_LIMIT_GRP        WAITING        37400          0       0     19871        0
582 CPU_IO_LIMIT_GRP        WAITING        36267          0       0     19073        0
10 CPU_IO_LIMIT_GRP        WAITING        41515          0       0     19843        0

You can notice that when we specify parallelism, each process had individual 20000M I/O limit.
Previous query was only read operation and we were wondering if this I/O limit applied to both read/write operation. To confirm this we ran query with 4 table join doing merge cartersian join (This was one of query which used 100G of temp tablespace and prompted us to start this exercise 🙂

To remove the plan, we have to unset resource_manager_plan parameter and then delete it using following code

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN ='';
begin
sys.dbms_resource_manager.clear_pending_area();
sys.dbms_resource_manager.create_pending_area();
sys.dbms_resource_manager.delete_plan_cascade ('CPU_IO_LIMIT');
sys.dbms_resource_manager.submit_pending_area();
end;
/

We recreated plan with 10000M as new I/O limit and ran the query.(We can also use dbms_resource_manager.update_plan_directive to update the settings).

Meanwhile I used new sql to get single /multi block read/write I/O along with totals.

col state for a10
col CONSUMER_GROUP for a20
SELECT s.sid sess_id, g.name consumer_group,
s.state, s.consumed_cpu_time cpu_time, s.cpu_wait_time, s.queued_time,s.CURRENT_SMALL_READ_MEGABYTES S_RIO,s.CURRENT_LARGE_READ_MEGABYTES M_RIO,s.CURRENT_SMALL_WRITE_MEGABYTES S_WIO,s.CURRENT_LARGE_WRITE_MEGABYTES M_WIO,(s.CURRENT_SMALL_READ_MEGABYTES+s.CURRENT_LARGE_READ_MEGABYTES) read_MB,(s.CURRENT_SMALL_WRITE_MEGABYTES+s.CURRENT_LARGE_WRITE_MEGABYTES) write_mb
FROM v$rsrc_session_info s, v$rsrc_consumer_group g
WHERE s.current_consumer_group_id = g.id and g.name='CPU_IO_LIMIT_GRP';

Before failure we had following output from v$rsrc_session_info with 3341M as reads and 6614 writes (total of 9955M ~10000M). It confirms that I/O limit here includes both read/write I/O

SESS_ID CONSUMER_GROUP    STATE         CPU_TIME CPU_WAIT_TIME QUEUED_TIME      S_RIO    M_RIO       S_WIO      M_WIO    READ_MB     WRITE_MB
---------- -------------------- ---------- ---------- ------------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
199 CPU_IO_LIMIT_GRP    WAITING        104786          0          0        233     3108         203       6295      3341         6498

SESS_ID CONSUMER_GROUP    STATE         CPU_TIME CPU_WAIT_TIME QUEUED_TIME      S_RIO    M_RIO       S_WIO      M_WIO    READ_MB     WRITE_MB
---------- -------------------- ---------- ---------- ------------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
199 CPU_IO_LIMIT_GRP    RUNNING        106749          0          0        233     3108         207       6407      3341         6614

One last test was to test for queries exceeding specified CPU Time. To perform this test we altered switch_time to 120 seconds and re-ran the query involving joins.After nearly 5 minutes, our sql got killed with following error

ORA-00040: active time limit exceeded - call aborted
Elapsed: 00:04:47.13

--Resource usage
SESS_ID CONSUMER_GROUP    STATE         CPU_TIME CPU_WAIT_TIME QUEUED_TIME      S_RIO    M_RIO       S_WIO      M_WIO    READ_MB     WRITE_MB
---------- -------------------- ---------- ---------- ------------- ----------- ---------- ---------- ---------- ---------- ---------- ----------
199 CPU_IO_LIMIT_GRP    RUNNING        141184          0          0        236     3108         254       7885      3344         8139

Above query reported 140s as total consumed time even though we had specified 120 s.(Not sure if this is expected behavior as note 1485199.1 mentioned that it does not include any wait) Note that we used switch_group as CANCEL_SQL which cancels the running sql after limit is reached.In case you plan to kill the session, you need to use KILL_SESSION.

You would notice that we used switch_estimate to false . This parameter tells oracle to estimate the execution time before the operation starts.If you use switch_estimate to true in above code, you will notice that it will kill the session immediately along with error message indicating which I/O limit was reached.

If you set switch_estimate to true and set max_est_exec_time which specifies the maximum execution time (in CPU seconds) allowed for a session. If the optimizer estimates that an operation will take longer than MAX_EST_EXEC_TIME, the operation is not started and ORA-07455 is issued.

You can notice that this is far better approach of enforcing limits rather then writing your own script/triggers. Some of advantages in this case were

a)Both the cases we got pretty informative error indicating that we are excluding specified limits.

b)Gave us control of limiting parallelism

c)Allows us to specify I/O based limits

We didn’t use cpu allocation in this case but you can also limit cpu available for users using mgmt_p1 (cpu_p1 ,etc are deprecated).In case you are planning to implement this in your production database with cancel_sql directive, I would recommend ensuring that it should not be application user else you will start experiencing unwanted results.

1 September, 2012 by Amit Bansal 11g Performance 6

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

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

V$SQL_SHARED_CURSOR in 11.2.0.2

Oracle 11.2.0.2 has introduced new column “REASON” to V$SQL_SHARED_CURSORS. I came across this extremely useful column while debugging multiple child cursor issue (11.2.0.2 seems to have too many bugs which can cause multiple child cursors).

As per documentation , REASON column has been introduced in 11.2.0.2

REASON - CLOB	Child number, id, and reason the cursor is not shared. The content of this column is structured using XML.

Reason column is helpful as we can easily interpret the reason why child cursors are not being shared. e.g

<ChildNode>
<ChildNumber>151</ChildNumber>
<ID>9</ID>
<reason>PQ Slave mismatch(5)</reason>
<size>2x4</size>
<ctxpq_StmtId_pqctx>0</ctxpq_StmtId_pqctx>
<fxu_kxfxutqidb>2422463</fxu_kxfxutqidb>
</ChildNode>
<ChildNode><ChildNumber>151</ChildNumber>
<ID>3</ID>
<reason>Optimizer mismatch(2)</reason>
<size>4x4</size>
<parallel_query_default_dop>16</parallel_query_default_dop>
<kxfr_Default_DOP>80</kxfr_Default_DOP>
<isParallel>1</isParallel>
<Need_Default_Dop>0</Need_Default_Dop>
</ChildNode>
<ChildNode>
<ChildNumber>151</ChildNumber>
<ID>9</ID><reason>PQ Slave mismatch(5)</reason><size>2x4</size><ctxpq_StmtId_pqctx
>0</ctxpq_StmtId_pqctx><fxu_kxfxutqidb>1433944</fxu_kxfxutqidb></ChildNode>

We can verify the same thing by checking PQ_SLAVE_MISMATCH column

select sql_id,PQ_SLAVE_MISMATCH,OPTIMIZER_MODE_MISMATCH from  V$SQL_SHARED_CURSOR where sql_id='b9uz0akdcx58q' and child_number=151;

SQL_ID	      P O
------------- - -
b9uz0akdcx58q Y N

There is one more column PURGED_CURSOR which is new (I guess it came from 11.2.0.1) which tells if the cursor has been purged using dbms_shared_pool.purge package.

16 December, 2011 by Amit Bansal Performance 0