AskDba.org Weblog - Page 10 of 34 - Oracle DBA Journey to Cloud, DevOps and Big Data..

Cluster SSH tool Utility

Many times you come across scenario’s when you wish to open multiple ssh windows and execute same commands.e.g You wish to see alert log for multi-node RAC simultaneously or edit sysctl.conf files for multiple machines.

Cluster SSH utility helps solve this problem as it opens multiple SSH sessions and allows simultaneous control.

In case you are using MAC OS X, you can download from Google Code site . Utility is called csshX.

For Linux you can download from Sourceforge site and utility is called Cluster SSH.

I am using csshX to show demo. Suppose I have 3 hosts host1,host2,host3 I need to use following commands on terminal app

$csshX host1 host2 host3

$csshX host[1-3]

As you can see we can specify range using square brackets. csshX will create an SSH session to each remote host in separate Terminal.app windows. A master window will also be created. All keyboard input in the master will be sent to all the slave windows. Below screenshot displays how windows will look like

I can enter commands in Master (Red Color) window and it will execute same commands in all 3 windows.If you want to execute commands in particular window then go directly to that window. In case we need to open 3 sessions for host1 then we execute

$csshx host1+3

26 January, 2012 by Amit Bansal Unix 0

11g: Multiple failed login attempt can block New Application connections

In Oracle database 11g, a new feature/enhancement has potential of bringing down the application due to multiple sessions hitting database with wrong password.

As per Bug 7715339 – Logon failures causes “row cache lock” waits – Allow disable of logon delay [ID 7715339.8]

In 11g there is an intentional delay between allowing failed logon attempts to retry. After 3 successive failures a sleep delay is introduced starting
at 3 seconds and extending to 10 seconds max. During each delay the user X row cache lock is held in exclusive mode preventing any concurrent logon attempt as user X (and preventing any other operation which would need the row cache lock for user X).

Let’s simulate this using a test case.I am using a 11.2.0.3 database.Open two or more database server connections

Connection 1

create user amit identified by amit;
grant connect to amit;
alter user amit profile MONITORING_PROFILE;

MONITORING_PROFILE has failed_login_attempts set to unlimited.

Create two scripts

$cat 1.sh
sqlplus -s amit/test123 <<EOF
EOF
$cat lock_db.sh
#!/bin/bash
for i in {1..1000}
do
nohup sh 1.sh &
done

I have set loop to 1000 which you can increase depending on your process limit. This is test database with process parameter set to 150. Execute script

sh lock_db.sh

Now if you go to Connection 2, you will see following issues

a) Database will not allow fresh connections for short time as you will get ORA-00020: maximum number of processes (150) exceeded errors.
After that database connections will be possible
b) You cannot connect to AMIT user for considerable time even with correct password. All sessions will be waiting on library cache lock

 select event,count(*) from V$session group by event order by 2;

EVENT								   COUNT(*)
---------------------------------------------------------------- ----------
Streams AQ: waiting for time management or cleanup tasks		  1
VKTM Logical Idle Wait							  1
Space Manager: slave idle wait						  1
SQL*Net message from client						  1
ges remote message							  1
ASM background timer							  1
GCR sleep								  1
smon timer								  1
pmon timer								  1
Streams AQ: qmn coordinator idle wait					  1
asynch descriptor resize						  1
Streams AQ: qmn slave idle wait 					  1
PING									  1
class slave wait							  2
gcs remote message							  2
DIAG idle wait								  2
wait for unread message on broadcast channel				  2
rdbms ipc message							 18
library cache lock							101

select
 distinct
   ses.ksusenum sid, ses.ksuseser serial#, ses.ksuudlna username,KSUSEMNM module,
   ob.kglnaown obj_owner, ob.kglnaobj obj_name
   ,lk.kgllkcnt lck_cnt, lk.kgllkmod lock_mode, lk.kgllkreq lock_req
   , w.state, w.event, w.wait_Time, w.seconds_in_Wait
 from
  x$kgllk lk,  x$kglob ob,x$ksuse ses
  , v$session_wait w
where lk.kgllkhdl in
(select kgllkhdl from x$kgllk where kgllkreq >0 )
and ob.kglhdadr = lk.kgllkhdl
and lk.kgllkuse = ses.addr
and w.sid = ses.indx
order by seconds_in_wait desc
/

      SID    SERIAL# USERNAME	 MODULE 			     OBJ_OWNER	     OBJ_NAME		LCK_CNT  LOCK_MODE   LOCK_REQ STATE		  EVENT 			  WAIT_TIME SECONDS_IN_WAIT
---------- ---------- ---------- ----------------------------------- --------------- --------------- ---------- ---------- ---------- ------------------- ------------------------------ ---------- ---------------
       153	   77		 testbox01		     84 		      0 	 0	    3 WAITING		  library cache lock			  0		  3
	36	  101		 testbox01		     84 		      0 	 0	    3 WAITING		  library cache lock			  0		  3
	16	  137		testbox01		     84 		      0 	 0	    3 WAITING		  library cache lock			  0		  3
       143	  203		 testbox01		     84 		      0 	 0	    3 WAITING		  library cache lock			  0		  3
	26	  261		 testbox01		     84 		      1 	 3	    0 WAITED KNOWN TIME   library cache lock			119		  3

You will notice that seconds_in_wait value will increase . Also ‘alter system kill session’ takes long time to kill session (it seems as if hung). As per bug ,any operation involving row cache lock for that user will not be allowed. I confirmed by trying to reset profile to Default and session was stuck.

Till all these sessions are cleared from database, you will not be allowed to login. This can severy impact applications as fresh connection would not be possible. Pre-existing sessions will work
If you repeat same experiment in 10g, you will not face similar issue.

To disable this behavior i.e no sleep delay set following event

event=”28401 trace name context forever, level 1″

You can set it dynamically too using

alter system set events ‘28401 trace name context forever, level 1’;

To go back to original behavior

alter system set events ‘28401 trace name context off’;

Bug 7715339 mentions that if FAILED_LOGIN_ATTEMPTS is set to some value, then further attempts to log in will then correctly fail immediately with no delay

I tested this and found that this is not entirely true as my script was able to again spawn 101 sessions which were waiting on library cache lock again. Fresh connection with correct password was again stuck. DBA_USERS view was showing that account is locked. Though here the session cleared in quick time as compared to test case involving failed_login_attempts to unlimited.

19 January, 2012 by Amit Bansal 11g Featured 5

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

Oracle Critical Patch Update January 2012

I guess most of us having My Oracle Support have got email from Oracle mentioning that Critical Patch Update January 2012 has been released. Main reason behind this is Security bug found by Infoworld which can make database run out of SCN. You can read the article here

Summary

There is bug 12371955 which causes scn to be incremented at very high rate when we issue begin backup command in 11g database. There is already patch available for this.
But Infoworld seems to have found security flaws which can cause scn to be be raised by say 1 trillion on interconnected database. Jan 2012 CPU patch seems to address this issue.

18 January, 2012 by Amit Bansal 11g 0

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