Pages

Saturday 21 January 2012

I/O CALIBRATION IN 11G


A very good evening from chandigarh (INDIA) ... Here in koenig we come with another powerful post for 11G Oracle database's one of the most helpful goodies known as I/O calibration. In this post i have focused to make understand a DBA the concept of this calibration and how he can exploit such a wonderfull tool, I first got to know about this tool last year when i first read a blog of Vinay Pandey (The Oracle Security Vault Administrator) .. So here is my explanation for this feature...

At any given time a DBA is looking to establish a solid I/O subsystem for an applocation. If any component in the I/O stack is limited in throughput, it will become the weakest link in the I/O flow. The I/O stack offcourse includes the physical disks, the storage array, the storage switches, the HBA's.

I/O Calibration is a modified version of ORION tool and is based on the asynchronous library. This feature is based on the PL/SQL function called DBMS_RESOURCE_MANAGER.CALIBRATE_IO(). It is used to evaluate the performance of the storage subsystem and determine whether I/O performance problems comes from the database host or the storage subsystem. When it is invoked it will generate I/O intensive read-only random I/O and large-block(1MB) sequential I/O workloads. Unlike other tools, this tool uses oracle code stack the software libraries to drive the I/O; not some generic I/O generator. This capability is important - you are testing the I/O for an Oracle database right ?

Finally when the job is finished you can view the results in DBA_RSRC_IO_CALIBRATE view or the I/O calibration page: maximum I/O per second, maximum megabytes per second, and average actual latency metrics.

This calibrate I/O procedure will have 2 input variables and 3 output variables.

the input variables are NUM_DISKS and MAX_LATENCY.

NUM_DISKS = To get the most accurate results, its best to provide the actual number of physical disks that are used for this database. The Storage Administrator can provide this value. Keep in mind that when ASM is used to manage the database files, say in the DATA diskgroup, then only physical disks that make up the DATA diskgroup should be used for the NUM_DISKS variable; i.e.; do not include the disks from the FRA diskgroup.

LATENCY – This should be set to the defined response time given by your service level agreement for your application. for example your 95th percentile response time SLA is 10secs. I hope you are aware of significance level thresholds.

Now the 3 output variables are MAX_IOPS,MAX_MBPS, ACTUAL_LATENCY

MAX_IOPS - the maximum number of I/O Operations per second
MAX_MBPS - the maximum Megabytes per second
ACTUAL_LATENCY - the actual latency observed during the test

To run I/O calibration and assess the I/O capability of the storage subsystem used by Oracle Database, use the

DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure:

SET SERVEROUTPUT ON
DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
   DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);

  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
  dbms_output.put_line('max_mbps = ' || mbps);
end;
/

When running the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure, consider the following:


# Do not run the procedure multiple times across separate databases that use the same storage subsystem

# Quiesce the database to minimize I/O on the instance

# For Oracle Real Application Clusters (RAC) configurations, ensure that all instances are opened to calibrate the storage subsystem across nodes

# The execution time of the procedure is dependent on the number of disks in the storage subsystem and increases with the number of nodes in the database

# In some cases, asynchronous I/O is permitted for datafiles, but the I/O subsystem for submitting asynchronous I/O may be maximized, and I/O calibration cannot continue. In such cases, refer to the port-specific documentation for information about checking the maximum limit for asynchronous I/O on the system. The calibration will run in different phases. In the first phase, small block random I/O workload is performed on each node and then concurrently on all nodes. The second phase will generate large block sequential I/O on each node. Note, that the Calibrate I/O is expecting that a datafile is spread across all disks specified in NUM_DISKS variable. Furthermore, offline files are not considered for file I/O.

I also felt the need to explain the following metrics which were just thrown above :=

IOPS (I/O per second):= This metric represents the number of small random I/O that can be serviced in a second. The IOPS rate mainly depends on how fast a disk media can spin.

MBPS (megabytes per second) := The rate at which data can be transferred between the computing server node and the storage array depends on the capacity of I/O channel that is used to transfer data. More data can be transferred through a wider pipe.

I/O Latency: Traditionally it means the time it takes to access a particular sector on the disk. But from database point of view it means all the time it takes for a submitted I/O request to be serviced by the storage. A high latency usually indicates an overloaded system.




Have a nice day !!!

Related links:


HOME

Monday 16 January 2012

MMON WILL TRY TO PURGE OLD DATA FROM SYSAUX

Hello DBA's, specially Karan, Well i just had a strong sweet coffee and feeling good  

today I found a very  interesting thing in Alert log file that indicating that whenever SYSAUX tablespace becomes full then MMON will try to purge old data from SYSAUX tablespace to make free space for other data.

Please look the contents that I found in Alert Log file :
---------------------------------------------------------------


Tue Jan 17 10:00:03 2012
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_2503051407_14753 by 128 in tablespace SYSAUX
MMON Flush encountered SYSAUX out of space error(1688).
MMON (emergency) purge of WR snapshots (14690) and older



Related links:

HOME

Friday 13 January 2012

MUTEXES AND LATCHES


Well my friend ashish (A dangerous DBA at koenig) was literally asking me everyday to post some stuff on why oracle is switching to mutexes, so here i come with my post on mutexes and latches too, I have made this post simpler while not going much into details so that beginners can also cherish every moment too and also its a nice sunshine in new delhi despite its cold here, but no coffee here its sad though.. Here we go ..

As we know that mutex is a mutual exclusion and they are the concept of multiple threads, both latches and mutexes are used for low level locking, mutexes again are used to serialize access to shared structures, most importantly they can be taken in shared or exclusive mode and also getting a mutex can de done in wait or no-wait mode.

Let me post few of my notes on mutexes which i discussed with Rajeev Ranjan and also from Tanel Poder at the oracle forum :=

1) The advantages over latches is that mutexes requires less memory and are faster to get and release.
2) Why faster ? Because the code path to get and release is shorter
3) Acquired in the same mode of immediate or willing to wait as latches ? The answer is (YES)
4) To me and Mr. RANJEEV RANJAN it seams that since mutexes can do the same things as latches do by using less memory and in a more performant way, there are already enough good reason for using them....

Some notes from TANEL PODER (An Oracle GURU)

1) Library cache latching is still needed for parsing etc, the mutexes address only the pinning issue in library cache
2) Mutexes are currently used for library cache cursors (not other objects like PL/SQL stored procs, table defs etc)
3) As mutexes are a generic mechanism (not library cache specific) they're used in V$SQLSTATS underlying structures too
4) When mutexes are enabled, you won't see cursor pins from X$KGLPN anymore (as X$KGLPN is a fixed table based on the KGL pin array which wouldn't be used for cursors anymore)

AS for now few DBA's must be still not be convinced i guess if you really know how object handles,cursors,child cursors, pinning work in the shared pool, for them i have written the following lines

# The actual benefit comes is that for each child cursor handle mutex actually acts as a cursor pin structure, so if you have a cursor open (cached in session cursor cache) you dont need to get the library cache latch (which was previosuly needed for chaning cursor pin status), but you can modify the cursor's mutex refcount directly (with help of pointers in open cursor state area in sessions UGA). Therfore you have much higher scalability when pinning/unpinning cursors (no library cache/library cache pin latching needed, therfore no false contention) and no separate pin structure need to be allocated/maintained.

If you have understood what i just said above, yes it can be leading to an assumption to a false contention thing like suppose there are lots of sessions looking for statements in the shared pool then the library cache will come under contention, now this is a false contention if we just look at if sessions are looking for soft parsed cursor then its a good thing but because the looking mechanism, latch is not available so its actually under contention which will be removed with the help of mutexes as they will be allocated as per structure so there wont be any starvation issue like in latches.

In Oracle 10.2.0.2+ the library cache pin latch usage was replaced with mutexes whenever _kks_use_mutex_pin was true, also few other things like V$SQLSTATS arrays and parent cursor examination were protected by mutexes.

In 11g all library cache related latches except “library cache load lock” are gone and corresponding operations are protected by mutexes instead. The “library cache” latches have been replaced by “Library Cache” mutexes for example.

The proof is

SQL> select name from v$latch where lower(name) like '%library%';

NAME
----------------------------------------------------------------
library cache load lock


At the end of this post i would again like to write some basic concepts if some of you need a revision sort of thing ...

Mutexes, Yes they have less potential for false contention because latches typically protect multiple objects. When a latch protects one or more hot objects, the latch itself becomes a serialization point when accessing any of the objects protected by that latch. This can be a false contention point where the contention is for protection mechanism (LATCH) rather than the target object, Because mutexes can be created for each structure protected false contention is less likely.

A mutex can ce concurrently referenced by many sessions, provided all sessions refer to it in S (Shared mode). Total numver of sessions referencing a mutex in S mode is called reference count (REF COUNT). For exclusive it is X (exclusive mode) which can be held by only 1 session. Mutexes have a dual nature, they can act as a serialization mechanism like latch and also a pin (for eg:= preventing an object from aging out). the ref count of a mutex is a replacement for library cache pin, instead of each session creating and then deleting a library cache pin when executing a cursor, each session increments and decrements the ref count (so the ref count replaces n distinct pins).... Have a nice day

--------------------------------------------------------------------------------------------------------

Related links :


HOME


LATCHES LOCKS PINS AND MUTEXES
MUTEXES AND LATCHES
PARENT AND CHILD CURSORS IN ORACLE
QUICK OVERVIEW OF SHARED POOL TUNING
SHARED POOL ARCHITECTURE

SQL PLAN MANAGEMENT – ORACLE 11g : Part-1
 http://koenigocm.blogspot.in/search/label/SQL%20PLAN%20MANAGEMENT%E2%80%93%20ORACLE%20%2011g%20%3A%20Part-1



SQL PLAN MANAGEMENT – ORACLE 11g : Part-2

 LIBRARY CACHE LOCK/PIN DEMONSTRATED
 http://koenigocm.blogspot.in/search/label/LIBRARY%20CACHE%20LOCK%2FPIN%20DEMONSTRATED


SHARED POOL TUNING - A DEMONSTRATION




Thursday 12 January 2012

SOME IMPORTANT TUNING TIPS

UGA and Oracle Shared Server: The point about UGA is that Shared server sessions take space from the available space in the shared pool for SQL and PL/SQL objects. Example the Shared pool may be tuned properly with 20 users but not large enough for 200 users. The space in the large pool can be set aside for the UGA eliminating this tuning factor. The streams pool should be configured if you are using Oracle Streams. If the streams pool is NOT configured Streams will use the Shared pool, When streams uses 10% of the shared pool Streams starts spilling to disk, so Streams could use more than than 10% of the shared pool. To avoid the memory sizing problems, fragmentation, and contention, configure the Streams Pool.

Large Pool: RMAN only uses the Large pool when BACKUP_TAPE_IO_SLAVE=TRUE

Database High Availability: Best Practices: Flashback Database can have a 1-2% overhead on write intensive systems.

Database High Availability: Best Practices: These practice are high recommended for HA databases, and are often implemented in other databases: Block Checking includes two parameters DB_BLOCK_CHECKSUM and DB_BLOCK_CHECKING. Major difference is that DB_BLOCK_CHECKSUM calculates the checksum to discover corruptions in block transfers (I/O), DB_BLOCK_CHECKING checks the internal consistancy of the block, by going through the data in the block, and can discover other types of corruptions. From Metalink Note: 336194.1 DB_BLOCK_CHECKSUM can be OFF (or) TYPICAL (or) FULL. OFF - DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces. TYPICAL (default) - Turning on this feature in TYPICAL mode causes only an additional 1% to 2% overhead. Oracle recommends that you set DB_BLOCK_CHECKSUM to TYPICAL. For backward compatibility we preserve use of TRUE (implying TYPICAL) and FALSE (implying OFF) values. FULL – In FULL mode setting Oracle will compute the checksum before any change and compare the checksum to the stored value and it causes 4% to 5% overhead. This approach uncovers any data corruption in the memory itself and reports errors at that point—which is highly useful for preventing data corruption at the disk level as well as its propagation to the standby database.

From the Reference guide DB_BLOCK_CHECKING controls whether or not Oracle performs block checking for database blocks. The checking that is performed depends on the value you supply, as follows: OFF (default) - no block checking is performed for blocks in the user tablespaces. However, semantic block checking for SYSTEM tablespace blocks is always turned on. LOW - basic block header checks are performed after block contents change in memory (for example, after UPDATE or INSERT statements, on-disk reads, or inter-instance block transfers in RAC) MEDIUM - all LOW checks are performed, as well as semantic block checking for all non-index-organized table blocks FULL - all LOW and MEDIUM checks are performed, as well as semantic checks for index blocks (that is, blocks of subordinate objects that can actually be dropped and reconstructed when faced with corruption) Oracle checks a block by going through the data in the block, making sure it is logically self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead, depending on workload and the parameter value. The more updates or inserts in a workload, the more expensive it is to turn on block checking. You should set DB_BLOCK_CHECKING to FULL if the performance overhead is acceptable. For backward compatibility the use of FALSE (implying OFF) and TRUE (implying FULL) is preserved.

Internal Fragmentation Considerations: The problem with all of these scenarios in the first set of bullets is that they leave empty space below the HWM. If PCTFREE is set too high the space is never used, too low and you get migrated rows. LOBs use space inside the LOB for versioning instead of undo. The amount of space that is used depends on PCTVERSION and RETENTION. PCTVERSION integer - Specify the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space. You can specify the PCTVERSION parameter whether the database is running in manual or automatic undo mode. PCTVERSION is the default in manual undo mode. RETENTION is the default in automatic undo mode. You cannot specify both PCTVERSION and RETENTION. RETENTION: Use this clause to indicate that Oracle Database should retain old versions of this LOB column for an amount of time. Oracle Database uses the value of the UNDO_RETENTION initialization parameter to determine the amount of committed undo data to retain in the database. You can specify the RETENTION parameter only if the database is running in automatic undo mode. In this mode, RETENTION is the default value unless you specify PCTVERSION. You cannot specify both PCTVERSION and RETENTION. If there is significant update activity on LOBs the amount of space used by old versions of the LOBs can be large. Direct loads insert above the HWM, when the load is complete the HWM is moved. Deletes remove rows below the HWM. Unless there are standard INSERTs the space released by the deletes will never be used. Update and deletes on indexed colums produce deleted leaf rows in the index. These occupy reusable space in the index, but if there are few or no inserts the space will not be used.

Sizing the Redo Log Buffer: From 10gR2 onward there is a separate log buffer per cpu. There are possibly multiple redo allocation latches because each log buffer requires an allocation latch. So now there are multiple redo allocation latch children and not one single latch. The log file sync wait event P1 has the buffer number experiencing the wait.

Related links:


HOME


FIX CONTROL

Oracle has introduced a fix control mechanism in 10.2 which allows customers to turn off fixes for optimizer related bugs. This is governed by the underscore parameter _fix_control. The bugs for which fixes can be turned off are listed in v$session_fix_control and v$system_fix_control and can also be seen in a 10053 output.

On a 10.2.0.2 database

SQL> select distinct bugno from v$session_fix_control;

BUGNO
----------
3499674
4556762
4569940
3118776
4519016
4175830
4663698
4631959
4550003
4584065
4487253
4611850
4663804
4602374
4728348
4723244
4554846
4545833
4488689
4519340

20 rows selected.

SQL> select distinct sid from v$mystat;

SID
----------
143

SQL> alter session set "_fix_control"='4728348:OFF';

Session altered.

SQL> select * from v$session_fix_control where session_id=143 and bugno=4728348;

SESSION_ID BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT
---------- ---------- ---------- ---------------------------------------------------------------- --
143 4728348 0 consider mjc if equi-joined pred is dropped in kkoipt 10.2.0.2

A value of 0 indicates the fix is off.

SQL> alter session set "_fix_control"='4728348:ON';

Session altered.

SQL> select * from v$session_fix_control where session_id=143 and bugno=4728348;

SESSION_ID BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT
---------- ---------- ---------- ---------------------------------------------------------------- --
143 4728348 1 consider mjc if equi-joined pred is dropped in kkoipt 10.2.0.2

It appears to me that if you need two fixes off in a session you have to specify them together else you lose the first change.

SQL> alter session set "_fix_control"='4728348:OFF';

Session altered.

SQL> alter session set "_fix_control"='4663698:OFF';

Session altered.

SQL> select * from v$session_fix_control where session_id=143 and bugno in (4728348,4663698);

SESSION_ID BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT
---------- ---------- ---------- ---------------------------------------------------------------- --
143 4663698 0 for cached NL table set tab_cost_io to zero 10.2.0.2 0 0
143 4728348 1 consider mjc if equi-joined pred is dropped in kkoipt 10.2.0.2

Hence if you need more than one fix OFF you need to supply both bug numbers in the same command

SQL> alter session set "_fix_control"='4728348:OFF','4663698:OFF';

Session altered.

SQL> select * from v$session_fix_control where session_id=143 and bugno in (4728348,4663698);

SESSION_ID BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE EVENT IS_DEFAULT
---------- ---------- ---------- ---------------------------------------------------------------- --
143 4663698 0 for cached NL table set tab_cost_io to zero 10.2.0.2 0 0
143 4728348 0 consider mjc if equi-joined pred is dropped in kkoipt 10.2.0.2

----------------------------------------------------------------------------------------------------------

Related links:

Wednesday 11 January 2012

SQL PLAN MANAGEMENT – ORACLE 11g : Part-2


SQL Plan Management – Oracle Database 11g

In SPM part 1 we saw information about SPM and how to automatically capture the baseline.
In this part we will see how to manually capture the baseline and the affect of using FIXED variable.

Capturing Baseline Manually

Occasionally we have to to capture the baseline manually. This could happen while tuning the query in our database where we have optimizer_capture_sql_plan_baselines parameter set to FALSE.
We have 2 procedures available for capturing the baselines manually.
  1. LOAD_PLANS_FROM_SQLSET

  2. LOAD_PLANS_FROM_CURSOR_CACHE

Above procedures are part of DBMS_SPM packages.
LOAD_PLANS_FROM_SQLSET procedure is used mainly while doing the upgrade from Oracle database 10g to Oracle database 11g.
The procedure is to
  1. create a SQL Tuning set in 10g and pack all the SQLs in the library cache along with there plans into SQL tuning set  – Procedure DBMS_SQLTUNE.CREATE_SQLSET and DBMS_SQLTUNE.LOAD_SQLSET
  2. Then to create a staging table and load this SQL tuning set into the staging table – Procedure DBMS_SQLTUNE.CREATE_STGTAB_SQLSET and DBMS_SQLTUNE.PACK_STGTAB_SQLSET
  3. Update database to 11g
  4. Unpack the staging table into SQL Tuning set you created before – Procedure DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET
  5. Create SQL Baselines from the SQL tuning set – Procedure DBMS_SPM.LOAD_PLANS_FROM_SQLSET
LOAD_PLANS_FROM_CURSOR_CACHE is used after 11g upgrade when we have optimizer_capture_sql_plan_baselines set to false and we want to create baseline for a query in order to stablize the plan.
SQL> select col1, count(1) from T group by col1;

      COL1   COUNT(1)
---------- ----------
         1          1
         2          3
         3      98304

SQL> show parameters capture

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE

SQL>
Currently we dont have any baselines in the database.
SQL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

no rows selected

SQL>

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

SQL> set line 999
SQL> set pagesize 999
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49154 |  2976K|   260   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 49154 |  2976K|   260   (2)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

13 rows selected.

SQL>
Lets create a baseline manually for this statement. For creating baselines manually we need to have the statement in cache first.
Lets run the statement once and find the SQL ID.
SQL> select sql_id, sql_text from v$sql where sql_text like 'select * from T where%';

SQL_ID        SQL_TEXT
------------- ------------------------------------------------------------------------------------------
5pvxxjg6n7mrp select * from T where col1 = 1

Now we will use LOAD_PLANS_FROM_CURSOR_CACHE to create a baseline

SQL> DECLARE
  2  l_plans_loaded  PLS_INTEGER;
  3  BEGIN
  4  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
  5  sql_id => '5pvxxjg6n7mrp');
  6  END;
  7  /

PL/SQL procedure successfully completed.

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

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                         ENA ACC FIX
------------------------------ ------------------------------ -------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  select * from T where col1 = 1   YES YES NO

SQL>
So we can see that SQL Plan baseline is created as well as accepted.
We will check out the plan from baseline now, but it should be full table scan.
SQL> SET LONG 10000
SQL> select * from TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920f94ecae5c'));

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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_1447ba3a1d83920f
SQL text: select * from T where col1 = 1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1d83920f94ecae5c
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49154 |  2976K|   260   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 49154 |  2976K|   260   (2)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

24 rows selected.

SQL>

Now lets create an index on the table on COL1.

SQL> create index T_IDX on T(COL1);

Index created.

SQL>
Now since the baseline is used, same query will go for a full table scan even after creating an index.
SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49154 |  2976K|   260   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 49154 |  2976K|   260   (2)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_1d83920f94ecae5c" used for this statement

17 rows selected.

SQL>
So as you can see we have a baseline SYS_SQL_PLAN_1d83920f94ecae5c getting used for this query.
Lets go ahead and try creating another baseline for this SQL. May be this time we will get better plan.
SQL ID remains the same.
SQL> DECLARE
  2  l_plans_loaded  PLS_INTEGER;
  3  BEGIN
  4  l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
  5  sql_id => '5pvxxjg6n7mrp');
  6  END;
  7  /

PL/SQL procedure successfully completed.

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

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                         ENA ACC FIX
------------------------------ ------------------------------ -------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  select * from T where col1 = 1   YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  select * from T where col1 = 1   YES NO  NO
And yes, we can see Oracle has created another baseline here. But ACCEPTED is still NO for the new plan.
This is because we haven’t evaluated the plan yet. If we would have set optimizer_capture_sql_plan_baselines parameter to true, it would have automatically captured and made ACCEPTED=YES after doing automatic evaluation.
Since this is false, we have to evaluate whether new plan is better or not. This process of evaluating new baseline is called evolving.
Evolving a plan actually compares the cost and several other factors like DISK_READS, BUFFER_GETS, ELAPSED_TIME etc for all the plans that are available in DBA_SQL_PLAN_BASELINES table for that particular SQL HANDLE.
So in our case when we evolve the plan it will compare plan SYS_SQL_PLAN_1d83920f94ecae5c and SYS_SQL_PLAN_1d83920fae82cf72
Just to be certain we will display the plan SYS_SQL_PLAN_1d83920fae82cf72
SQL> select * from TABLE(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920fae82cf72'));

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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_1447ba3a1d83920f
SQL text: select * from T where col1 = 1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1d83920fae82cf72
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 49154 |  2976K|     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 49154 |  2976K|     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX | 49154 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

25 rows selected.

SQL>
So it is indeed using the index on the table.
Here there are 2 things again.
  1. Evolving the plan automatically – Using procedure DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE
  2. Manually accepting the plan – Using procedure ALTER_SQL_PLAN_BASELINE setting ACCEPTED=>YES
In the first method optimizer will evaluate based on all the factors (we will see those factors below) and “selects” the best plan. I have highlighted select here because optimizer is going to select from the set of existing plans, its not going to parse and create a whole other plan.
So its upto the optimizer to decide which plan is best among the available plan.
In method 2, we can decide and select which ever plan we think is correct. This based on our knowledge of the type of SQL we are running and what resources we have with us.

Can we have 2 plans in ACCEPTED status for a query?

Yes we can.

Which plan optimizer will choose out of the two?

Always the best among the two. If more than 1 plan is ACCEPTED than optimizer will choose the best plan to be used for the query.
Example if we use a query “select * from T where col = :B1″
Here we have used a bind variable. Lets say we have 2 plans with us right now
Plan 1) Full table scan
Plan 2) Index Range scan
Which plan optimizer will choose depends upon the value of bind variable.
Yes, in 11g we have something called “Adaptive Cursor Sharing”, but this is little off the topic. We will cover “Adaptive Cursor Sharing” in different article.
So if we supply value as 1 or 2 and since there is only 1 row with col1=1 and 3 rows with col1=2, optimizer will use Plan 2) Index Range Scan.
If we supply value as 3, optimizer will use Plan 1) Full table scan.
So imagine a case where we have 5-6 plans based on selectivity of different tables involved in a complex query, in that case its a good idea to keep all those plans as ACCEPTED. Optimizer will always choose the best among those based on selectivity of the data.
We have seen evolving the plan in Part 1 – http://avdeo.com/2011/06/02/oracle-sql-plan-management-part-1/
Lets set ACCEPTED=TRUE manually now.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_plans_altered  PLS_INTEGER;
  3  BEGIN
  4    l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
  5      sql_handle      => 'SYS_SQL_1447ba3a1d83920f',
  6      plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',
  7      attribute_name  => 'ACCEPTED',
  8      attribute_value => 'YES');
  9
 10    DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
 11  END;
 12  /
Plans Altered: 1

PL/SQL procedure successfully completed.

SQL>

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

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                         ENA ACC FIX
------------------------------ ------------------------------ -------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  select * from T where col1 = 1   YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  select * from T where col1 = 1   YES YES NO

SQL>
Lets check the explain plan for value 1.
SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 49154 |  2976K|    56   (2)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 49154 |  2976K|    56   (2)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX | 49154 |       |    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_1d83920fae82cf72" used for this statement

18 rows selected.

SQL>
So its using SYS_SQL_PLAN_1d83920fae82cf72 baselines.

What is the affect of using FIXED=YES ?

Fixed attribute will fix the plan for a query. So even if we have a better plan, the query will still not be using it. It is like disabling (ENABLED=NO, ACCEPTED=NO) all the plans for that SQL Handle.
Let make FIXED=YES for the first baseline (SYS_SQL_PLAN_1d83920f94ecae5c) which does full table scan.
To change this attribute we can again use DBMS_SPM.ALTER_SQL_PLAN_BASELINE procedure
SQL> set serveroutput on
SQL> DECLARE
  2    l_plans_altered  PLS_INTEGER;
  3  BEGIN
  4    l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
  5      sql_handle      => 'SYS_SQL_1447ba3a1d83920f',
  6      plan_name       => 'SYS_SQL_PLAN_1d83920f94ecae5c',
  7      attribute_name  => 'FIXED',
  8      attribute_value => 'YES');
  9
 10    DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
 11  END;
 12  /
Plans Altered: 1

PL/SQL procedure successfully completed.

SQL>

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

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                                           ENA ACC FIX
------------------------------ ------------------------------ -------------------------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  select * from T where col1 = 1                     YES YES YES
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  select * from T where col1 = 1                     YES YES NO

SQL>
Lets check out the explain plan for “select * from T where col1 = 1″ query
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 49154 |  2976K|   260   (2)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    | 49154 |  2976K|   260   (2)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_1d83920f94ecae5c" used for this statement

17 rows selected.

SQL>
So if you see now, same query which was earlier using index scan has now shifted to full table scan. Thats the magic of SPM !!
I would recommend not using FIXED attribute at all. You are just killing the functionality provided by 11g.
However, if we make both plans as FIXED then ??
set serveroutput on                                           
DECLARE                                                       
  l_plans_altered  PLS_INTEGER;                               
BEGIN                                                         
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(        
    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',            
    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',       
    attribute_name  => 'FIXED',                               
    attribute_value => 'YES');                                
                                                              
  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;                                                          
/                                                             

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

SQL_HANDLE                     PLAN_NAME                      SQL_TEXT                                           ENA ACC FIX
------------------------------ ------------------------------ -------------------------------------------------- --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  select * from T where col1 = 1                     YES YES YES
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  select * from T where col1 = 1                     YES YES YES

SQL> explain plan for
  2  select * from T where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 49154 |  2976K|     6   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 49154 |  2976K|     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX | 49154 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_1d83920fae82cf72" used for this statement

18 rows selected.

SQL>

It will again go back to best among the fixed plans.

In the next part, I will cover how to transfer the baseline from one database to another and also how to set a specific plan for a query using hint and then creating baseline for that.

Hope this helps !!

-----------------------------------------------------------------------------------------------------------

Related links :


HOME

LATCHES LOCKS PINS AND MUTEXES
MUTEXES AND LATCHES
PARENT AND CHILD CURSORS IN ORACLE
QUICK OVERVIEW OF SHARED POOL TUNING
SHARED POOL ARCHITECTURE

SQL PLAN MANAGEMENT – ORACLE 11g : Part-1
 http://koenigocm.blogspot.in/search/label/SQL%20PLAN%20MANAGEMENT%E2%80%93%20ORACLE%20%2011g%20%3A%20Part-1



SQL PLAN MANAGEMENT – ORACLE 11g : Part-2

 LIBRARY CACHE LOCK/PIN DEMONSTRATED
 http://koenigocm.blogspot.in/search/label/LIBRARY%20CACHE%20LOCK%2FPIN%20DEMONSTRATED

SHARED POOL TUNING - A DEMONSTRATION
http://koenigocm.blogspot.in/search/label/SHARED%20POOL%20TUNING%20-%20A%20DEMONSTRATION 

MIGRATE OUTLINES TO SQL MANAGEMENT BASE
http://koenigocm.blogspot.in/search/label/MIGRATE%20OUTLINES%20TO%20SQL%20MANAGEMENT%20BASE



SQL PLAN MANAGEMENT– ORACLE 11g : Part-1


SQL Plan Management or SPM is a new feature introduced in 11g. We will take a detailed look at SPM in the following sessions.
Before starting with practical example lets see some theory.
What is SQL Plan Management?
SPM is a new feature introduced in Oracle database 11g which stores the baseline (Plans + Hints) inside database.
SPM allows an Oracle DBA to capture and preserve the most efficient execution plans for any SQL statement, thus limiting the impact of refreshed optimizer statistics, changes to existing applications, and even upgraded database versions
Why do we need SPM?
SPM is basically used for plan stability. If you have a database environment with several queries running, you always want your queries to run the way they are running irrespective of the changes that you make in your environment.
Example, If you upgrade your database, your queries might behave differently because of the change in optimizer or change in environment and at initial run you have to fix many queries in your production DB because of the upgrade or change in the env.
To avoid this, SPM is introduce to give you plan stability. So even after change in the environment, your queries will use same plan as it was before. More plans (Better or worse) could exists in the new environment, but optimizer is not allowed to use those plans without you confirming the plan change.
What was wrong with outlines?
Well, outlines are more about fixing the plan for a query that optimizer will use. So if there is change in environment, the plan would still be the same.
There are certain situations where you want the plan to change with change in lets say data. What if your data gets skewed over time.
Intially you had 100 of entries for month “DEC” out of total 150 entries. So it was using FTS. Now with increase in the data, there are 1 million records and entries for month “DEC” are around 1000. In that case it makes sense for optimizer to change plan and start using Index scan instead of FTS.
But since you used outline, you are forcing optimizer to go for a FTS. This is just an example situation and things can change in your environment and so outline is not a very good approach for such situations.
What was wrong with profile?
Profile is another feature introduced in 10g and can be implemented at SQL level. Profiles are better than outlines in that they are not fixing a plan through out the life of SQL. With change in the environment the plans can change even if the profile is used.
So whats the difference between profile and SQL plan baselines?
Profile is more of a advisors. They give advice to optimizer while executing the query. They provide optimizer with all the precise estimates. Profiles are more abount correcting optimizer to use correct plan when the underlying data is skewed and changed drastically.
The goal is to create best execution plan for the SQL by giving the very precise data to the optimizer. Its the optimizer who will decide what should be the explain plan based on the information/hints it has received from profile.
SQL Plan baselines works differently. It enforces the plan to be used for a SQL. Baseline does not provide and estimates or it does not help optimizer in anyway. It just tells optimizer to ignore everything and use the plan that we are giving you.
Optimizer just followes the plan provided by baselines. So here baseline is the driving mechanism for getting the correct plan.
Finally how baselines are different then outline then?
Well, baseline always keeps the optimized plans for your SQL. If there is a change in the environment you will have a new baseline created for the same SQL. Oracle will stop that baseline in the baseline history.
Depending on the baseline parameters setting in your environment, new plan will be automatically used or you have to evolve the new plan for optimizer to use it.
So in a way SQL baselines are combination of outlines and profiles. It gives the stability of plan similar to outlines and it also allows capturing better plans in case the environment changes.
Not only that, SQL Baselines give the complete control to the DBA on
  1. Whether to capture the new and better plans?
  2. Whether to use the new plans automaticallly without DBA’s intervention.
So DBAs have complete control of the environment now. Far better than profiles and outlines.
SQL Plan Baseline Parameters:
Before we check how to use SQL Plan baselines, lets consider the significance of 2 important baseline parameters
1) optimizer_capture_sql_plan_baselines – Default “FALSE”
This parameter is responsible for automatic capturing the baselines for SQLs. If we set this parameter to TRUE we are asking oracle to automatically gather the baselines for the SQL.
When you run the query for the first time (and parsed version is not present in shared_pool), oracle consider that as a fresh new query and does not create a baseline.
When you run the query for second time, oracle will consider the query as repetative and will automatically create the baseline.
These baseline are stored in DBA_SQL_PLAN_BASELINES table.
If this parameter is set to FALSE, then we (DBA) has to create baselines for the SQL manually. There are 2 procedure available for creating baselines manually.
1) Using DBMS_SPM.LOAD_PLANS_FROM_SQLSET
This procedure is usually used when we create SQL tuning set and store our SQLs into the tuning sets. Normally done before we upgrade the database to 11g. In our 10g database we create SQL tuning set and store all our SQL. Once we upgrade to 11g, we can create baselines for all our plans in SQL tuning set. That why what ever plans were effective in 10g, same will be used in 11g and there wont be any plan flips.
2) Using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
This is used when we want to fix one of the SQL currently running in our 11g database. We just load the plans from cursor cache (shared_pool) and create baseline out of that. We need to give SQL ID as input to this procedure.
We will see how to create baseline using manual method at later point of time.
2) optimizer_use_sql_plan_baselines – Default “TRUE”
This parameter will allow optimizer to use the baselines present in DBA_SQL_PLAN_BASELINES table. If you set this parameter to FALSE, then your 11g DB will start behaving same as 10g DB.
If there is any change in environment then it might flip the plan. Keeping this parameter TRUE is important in 11g.
How to use SQL Plan Management baselines ?
Lets take an example in a test database.
Table T with 1540 records.
SQL> select count(1) from t;

  COUNT(1)
----------
      1540
Data is skewed and distribution is as given below.
SQL> select col1, count(1) from t group by col1;

      COL1   COUNT(1)
---------- ----------
         1          1
         2          3
         3       1536
Gather the stats on the table
SQL> exec dbms_stats.gather_table_stats(OWNNAME=> 'ADVAITD_DBA',TABNAME => 'T', DEGREE => 6, GRANULARITY => 'ALL' ,CASCADE => TRUE , METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 254');

SQL>

Currently I dont have any baseline.

SQL> select count(1) from dba_sql_plan_baselines;

  COUNT(1)
----------
         0

SQL>
My baseline parameters setting is as below.
SQL> show parameters baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL>
Let us first consider the auto capture utility for baselines.
AUTO Capture of baseline
SQL> alter session set optimizer_capture_sql_plan_baselines=TRUE;

Session altered.

SQL>
As mention earlier, we need to run the query 2 times in order to automatically create the baseline.
SQL> select * from t where col1 = 1;

      COL1 COL2                                               COL3
---------- -------------------------------------------------- -----------
         1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 01-JUN-2011

SQL> select * from t where col1 = 1;

      COL1 COL2                                               COL3
---------- -------------------------------------------------- -----------
         1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 01-JUN-2011
If we check DBA_SQL_PLAN_BASELINES we will see a sql baseline created.
SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
  2  from dba_sql_plan_baselines
  3  WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
Turning off auto SQL plan baseline capture
SQL> alter session set optimizer_capture_sql_plan_baselines=FALSE;

Session altered.

SQL>
Following statement gives the plan stored in the baseline. DBMS_XPLAN has a new procedure DISPLAY_SQL_PLAN_BASELINE which will display the baseline.
SQL> set line 999
SQL> set pagesize 999
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920f94ecae5c'));

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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_1447ba3a1d83920f
SQL text: select * from t where col1 = 1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1d83920f94ecae5c
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |   735 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    15 |   735 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

24 rows selected.

SQL>
Origin: AUTO-CAPTURE shown above tell us that this baseline is captured automatically.
Lets now create an index and gather stats over index.
SQL> create index t_idx on t(col1);

Index created.

SQL> exec DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => 'ADVAITD_DBA', INDNAME=>'T_IDX');
Run the same query now, since the index is created, we expect the query to use the index.
SQL> explain plan for
  2  select * from t where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    15 |   735 |     9   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    15 |   735 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("COL1"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_1d83920f94ecae5c" used for this statement

17 rows selected.

SQL>
The reason we are seeing full table scan is because of the NOTE at the end, which says “SQL plan baseline “SYS_SQL_PLAN_1d83920f94ecae5c” used for this statement”
Since we have a baseline created for this SQL, it will not allow the plan to be changed. This is the kind of stability that SQL Plan baseline gives.
But using an index will be beneficial in our case.
If we check DBA_SQL_PLAN_BASELINES we can see a new plan has been created (PLAN_NAME = SYS_SQL_PLAN_1d83920fae82cf72), but it is not yet ACCEPTED. The plan is enabled though.
SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
  2  from dba_sql_plan_baselines
  3  WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES NO  NO
We can check what the new plan looks like using dbms_xplan.display_sql_plan_baseline
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_1d83920fae82cf72'));

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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_1447ba3a1d83920f
SQL text: select * from t where col1 = 1
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_1d83920fae82cf72
Enabled: YES     Fixed: NO      Accepted: NO      Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    15 |   735 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    15 |   735 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

25 rows selected.

SQL>
As seen above, new plan uses index. Lets evolve this plan now.
Evolving a plan includes evaluating the cost of the plan and accepting if the plan seems to be better than all accepted plan for this query.
SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_1447ba3a1d83920f') from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_1447BA3A1D83920F')
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SYS_SQL_1447ba3a1d83920f
  PLAN_NAME  =
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = YES
  COMMIT     = YES

Plan: SYS_SQL_PLAN_1d83920fae82cf72
-----------------------------------
  Plan was verified: Time used .01 seconds.
  Passed performance criterion: Compound improvement ratio >= 7.33
  Plan was changed to an accepted plan.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
  Execution Status:        COMPLETE       COMPLETE
  Rows Processed:                 1              1
  Elapsed Time(ms):               0              0
  CPU Time(ms):                   0              0
  Buffer Gets:                   22              3              7.33
  Disk Reads:                     0              0
  Direct Writes:                  0              0
  Fetches:                        0              0
  Executions:                     1              1

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
Sometimes, your plan may not get evolved because oracle see that there are other already ACCEPTED plans which are better than the plan you are trying to evolve.
But if you know your plan will be better and still want to deploy the same, you can do so by manually changing the attributes ACCEPTED and ENABLED as shown below.
SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',
    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',
    attribute_name  => 'ENABLED',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SYS_SQL_1447ba3a1d83920f',
    plan_name       => 'SYS_SQL_PLAN_1d83920fae82cf72',
    attribute_name  => 'ACCEPTED',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
You can also set the value of attribute FIXED using the above function. Here is the meaning of ENABLED, ACCPETED and FIXED
ENABLED   – ‘YES’ means the plan is available for use by the optimizer. It may or may not be used depending on accepted status.
ACCPETED – ‘YES’ means the plan will be used by optimizer while running the query. ‘NO’ means optimizer will not use the plan.
FIXED        – ‘YES’ means the SQL plan baseline is not evolved over time. A fixed plan takes precedence over a non-fixed plan.
Once you evolve the plan, you can see that plan is ACCEPTED now.
SQL> select SQL_HANDLE, PLAN_NAME, ENABLED, ACCEPTED, FIXED
  2  from dba_sql_plan_baselines
  3  WHERE sql_text like 'select * from t%';

SQL_HANDLE                     PLAN_NAME                      ENA ACC FIX
------------------------------ ------------------------------ --- --- ---
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920f94ecae5c  YES YES NO
SYS_SQL_1447ba3a1d83920f       SYS_SQL_PLAN_1d83920fae82cf72  YES YES NO
Now if you run the explain plan you can see Index T_IDX is getting used.
SQL> explain plan for
  2  select * from t where col1 = 1;

Explained.

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    15 |   735 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |    15 |   735 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     6 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("COL1"=1)

Note
-----
   - SQL plan baseline "SYS_SQL_PLAN_1d83920fae82cf72" used for this statement

18 rows selected.

SQL>

Well !! DBAs,  Hope this helps .
-----------------------------------------------------------------------------------------------------------

Related links :


HOME
LATCHES LOCKS PINS AND MUTEXES
MUTEXES AND LATCHES
PARENT AND CHILD CURSORS IN ORACLE
QUICK OVERVIEW OF SHARED POOL TUNING
SHARED POOL ARCHITECTURE


SQL PLAN MANAGEMENT – ORACLE 11g : Part-1
 http://koenigocm.blogspot.in/search/label/SQL%20PLAN%20MANAGEMENT%E2%80%93%20ORACLE%20%2011g%20%3A%20Part-1




SQL PLAN MANAGEMENT – ORACLE 11g : Part-2



 LIBRARY CACHE LOCK/PIN DEMONSTRATED
 http://koenigocm.blogspot.in/search/label/LIBRARY%20CACHE%20LOCK%2FPIN%20DEMONSTRATED


SHARED POOL TUNING - A DEMONSTRATION

MIGRATE OUTLINES TO SQL MANAGEMENT BASE

http://koenigocm.blogspot.in/search/label/MIGRATE%20OUTLINES%20TO%20SQL%20MANAGEMENT%20BA