DBA Blogs

Detecting Corrupt Data Blocks

Jared Still - Thu, 2009-08-06 16:50
Or more accurately, how not to detect corrupt data blocks.

This thread on Oracle-L is regarding lost writes on a database.

One suggestion was made to use the exp utility to export the database, thereby determining if there are corrupt blocks in the database due to disk failure. I didn't give it much thought at first, but fellow Oak Table member Mark Farnham got me thinking about it.

Using exp to detect corrupt blocks, or rather, the absence of corrupt blocks may work, but then again, it may not. It is entirely possible to do a  full table scan on a table successfully, as would happen during an export, even though the blocks on disk have been corrupted.

This can be demonstrated by building a table, ensuring the contents are cached, then destroying the data in the data file, followed by a successful export of the table.

Granted, there are a lot of mitigating factors that could be taken into consideration as to whether or not this would happen in a production database. That's not the point: the point is that it could happen, so exp is not a reliable indicator of the state of the data files on disk.

This test was performed on Oracle 10.2.0.4 EE on RH Linux ES 4. Both are 32 bit.

First create a test tablespace:

create tablespace lost_write datafile '/u01/oradata/dv11/lost_write.dbf' size 1m
extent management local
uniform size 64k
/



Next the table LOST_WRITE is created in the tablespace of the same name. This will be used to test the assertion that a successful export of the table can be done even though the data on disk is corrupt.

create table lost_write
cache
tablespace lost_write
as
select * from dba_objects
where rownum <= 1000
/

begin
dbms_stats.gather_table_stats(user,'LOST_WRITE');
end;
/

select tablespace_name, blocks, bytes
from user_segments
where segment_name = 'LOST_WRITE'
/


TABLESPACE_NAME BLOCKS BYTES
------------------------------ ---------- ----------
LOST_WRITE 16 131072

1 row selected.



Next, do a full table scan and verify that the blocks are cached:

select * from lost_write;

Verify in cache:
select file#,block#,class#, status
from v$bh where ts# = (select ts# from sys.ts$ where name = 'LOST_WRITE')
order by block#
/

FILE# BLOCK# CLASS# STATUS
---------- ---------- ---------- -------
40 2 13 xcur
40 3 12 xcur
40 9 8 xcur
40 10 9 xcur
40 11 4 xcur
40 12 1 xcur
40 13 1 xcur
40 14 1 xcur
40 15 1 xcur
40 16 1 xcur
40 17 1 xcur
40 18 1 xcur
40 19 1 xcur
40 20 1 xcur
40 21 1 xcur
40 22 1 xcur
40 23 1 xcur




Now swap the bytes in the file, skipping the first 2 oracle blocks
Caveat: I don't know if that was the correct # of blocks, and I didn't spend any time trying to find out
Also, I belatedly saw that count probably should have been 22 rather than 16, but the results still served the purpose of corrupting the datafile, as we shall see in a bit.

What this dd command is doing is using the same file for both input and output, and rewriting blocks 3-18, swapping each pair of bytes.

dd if=/u01/oradata/dv11/lost_write.dbf of=/u01/oradata/dv11/lost_write.dbf bs=8129 skip=2 count=16 conv=swab,notrunc



The effect is demonstrated by this simple test:

jkstill-19 > echo hello | dd
hello
0+1 records in
0+1 records out
[ /home/jkstill ]

jkstill-19 > echo hello | dd conv=swab
ehll
o0+1 records in
0+1 records out


Now we can attempt the export:

exp tables=\(jkstill.lost_write\) ...

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table LOST_WRITE 1000 rows exported
Export terminated successfully without warnings.
19 > echo hello | dd
hello
0+1 records in
0+1 records out
[ /home/jkstill ]

jkstill-19 > echo hello | dd conv=swab
ehll
o0+1 records in
0+1 records out


So, even though the data on disk has been corrupted, the export succeeded. That is due to the table being created with the CACHE option, and all the blocks being cached at the time of export. It may not be necessary to use the CACHE option, but I used it to ensure the test would succeed.

Now let's see what happens when trying to scan the table again. First the NOCACHE option will be set on the table, then a checkpoint.

10:42:45 dv11 SQL> alter table lost_write nocache;

10:43:02 dv11 SQL> alter system checkpoint;

Now try to scan the table again:

10:43:14 ordevdb01.radisys.com - js001292@dv11 SQL> /
select * from lost_write
*
ERROR at line 1:
ORA-00376: file 40 cannot be read at this time
ORA-01110: data file 40: '/u01/oradata/dv11/lost_write.dbf'



A corollary conclusion can drawn from this example.

If you do discover bad data blocks, you just might be able to do an export of the tables that are in the affected region before doing any recovery. This might be a belt and suspenders approach, but DBA's are not generally being known for taking unnecessary chances when possible data loss is on the line.
Categories: DBA Blogs

Will the Optimizer Team be at Oracle Open World 2009?

Inside the Oracle Optimizer - Thu, 2009-07-23 20:18
With only two and a half months to go until Oracle Open World in San Francisco, October 11-15th, we have gotten several requests asking if we plan to present any session at the conference.

We have two session and a demo station in the Database campground at this year's show. We will give a technical presentation on What to Expect from the Oracle Optimizer When Upgrading to Oracle Database 11g and the Oracle Optimizer Roundtable.

The technical session, which is on Tuesday Oct 13 at 2:30 pm, gives step by step instructions and detailed examples of how to use the new 11g features to ensure your upgrade goes smoothly and without any SQL plan regressions.

The roundtable, which is on Thursday Oct. 15th at 10:30 am, will give you a first hand opportunity to pose you burning Optimizer and statistics questions directly to a panel of our leading Optimizer developers. In fact if you plan to attend the roundtable and already know what questions you would like to ask, then please send them to us via email and we will be sure to include them. Other wise, you can hand in your questions at our demo station at any stage during the week, or as you enter the actual session. Just be sure to write your questions in clear block capitals!

We look forward to seeing you all at Oracle Open World.

Categories: DBA Blogs, Development

Classic MetaLink vs. My Oracle Support

Jared Still - Thu, 2009-07-09 18:01
If you are in any way involved with supporting Oracle products, then you know that the death knell for Classic MetaLink has sounded. MetaLink will be unplugged at the end of July 2009.

The new support site, My Oracle Support, seems to be causing some pain for quite a few people in the Oracle user community.

Some of the complaints regard limited platform support due to the Adobe Flash 9 requirements, navigation and response times.

On the other hand there are some cool new features such as Power View, Configuration Manager and the new Advanced Search options.

How do you feel about it?

Here's a chance to let your opinion be know as a poll has been created where you can vote on it.


At this time 637 voters have voiced their opinion about MetaLink and My Oracle Support.

Current Poll results can be found in this Excel File: MetaLink vs My Oracle Support Results

Categories: DBA Blogs

New in Oracle VM 2.1.5: Web Services API

Sergio's Blog - Tue, 2009-06-16 00:18

Last week, ULN was updated with Oracle VM 2.1.5 RPMs. One of the main new features in Oracle VM 2.1.5 is a web services-based API to perform any of the operations in Oracle VM Manager, for example, create a server pool, add servers, or create virtual machines. Read the Oracle VM Web Services API documentation. ISOs will be published on edelivery.oracle.com/oraclevm soon.

Categories: DBA Blogs

Oracle Enterprise Linux 4 Update 8 CD and DVD ISOs Now Available

Sergio's Blog - Wed, 2009-06-03 00:53

Download OEL 4 Update 8 for i386 and x86_64 architectures for free from edelivery.oracle.com/linux. ia64 ISOs will be published in the same location a bit later.

Categories: DBA Blogs

Why do I have hundreds of child cursors when cursor_sharing set to similar in 10g

Inside the Oracle Optimizer - Tue, 2009-05-26 12:04

Recently we received several questions regarding a usual situation where a SQL Statement has hundreds of child cursors. This is in fact the expected behavior when

  1. CURSOR_SHARING is set to similar

  2. Bind peeking is in use

  3. And a histogram is present on the column used in the where clause predicate of query

You must now be wondering why this is the expected behavior. In order to explain, let's step back and begin by explaining what CURSOR_SHARING actually does. CURSOR_SHARING was introduced to help relieve pressure put on the shared pool, specifically the cursor cache, from applications that use literal values rather than bind variables in their SQL statements. It achieves this by replacing the literal values with system generated bind variables thus reducing the number of (parent) cursors in the cursor cache. However, there is also a caveat or additional requirement on CURSOR_SHARING, which is that the use of system generated bind should not negatively affect the performance of the application. CURSOR_SHARING has three possible values: EXACT, SIMILAR, and FORCE. The table below explains the impact of each setting with regards to the space used in the cursor cache and the query performance.

.nobrtable br { display: none }



CURSOR_SHARING VALUESPACE USED IN SHARED POOLQUERY PERFORMANCE
EXACT (No literal replacement)Worst possible case - each stmt issued has its own parent cursorBest possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt
FORCEBest possible case as only one parent and child cursor for each distinct stmtPotentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan
SIMILAR without histogram presentBest possible case as only one parent and child cursor for each distinct stmtPotentially the worst case as only one plan will be used for each distinct stmt and all occurrences of that stmt will use that plan
SIMILAR with histogram presentNot quite as much space used as with EXACT but close. Instead of each stmt having its own parent cursor they will have their own child cursor (which uses less space)Best possible case as each stmt has its own plan generated for it based on the value of the literal value present in the stmt




In this case the statement with hundreds of children falls into the last category in the above table, having CURSOR_SHARING set to SIMILAR and a histogram on the columns used in the where clause predicate of the statement. The presence of the histogram tells the optimizer that there is a data skew in that column. The data skew means that there could potentially be multiple execution plans for this statement depending on the literal value used. In order to ensure we don't impact the performance of the application, we will peek at the bind variable values and create a new child cursor for each distinct value. Thus ensuring each bind variable value will get the most optimal execution plan. It's probably easier to understand this issue by looking at an example. Let's assume there is an employees table with a histogram on the job column and CURSOR_SHARING has been set to similar. The following query is issued

select * from employees where job = 'Clerk';

The literal value 'Clerk' will be replaced by a system generated bind variable B1 and a parent cursor will be created as

select * from employees where job = :B1;

The optimizer will peek the bind variable B1 and use the literal value 'Clerk' to determine the execution plan. 'Clerk' is a popular value in the job column and so a full table scan plan is selected and child cursor C1 is created for this plan. The next time the query is executed the where clause predicate is job='VP' so B1 will be set to 'VP', this is not a very popular value in the job column so an index range scan is selected and child cursor C2 is created. The third time the query is executed the where clause predicate is job ='Engineer' so the value for B1 is set to 'Engineer'. Again this is a popular value in the job column and so a full table scan plan is selected and a new child cursor C3 is created. And so on until we have seen all of the distinct values for job column. If B1 is set to a previously seen value, say 'Clerk', then we would reuse child cursor C1.
.nobrtable br { display: none }



Value for B1Plan UsedCursor Number
ClerkFull Table ScanC1
VPIndex Range ScanC2
EngineerFull Table ScanC3



As each of these cursors is actually a child cursor and not a new parent cursor you will still be better off than with CURSOR_SHARING set to EXACT as a child cursor takes up less space in the cursor cache. A child cursor doesn't contain all of the information stored in a parent cursor, for example, the SQL text is only stored in the parent cursor and not in each child.

Now that you know the explanation for all of the child cursors you are seeing you need to decide if it is a problem for you and if so which aspect affects you most, space used in the SHARED_POOL or query performance. If your goal is to guarantee the application performance is not affected by setting CURSOR_SHARING to SIMILAR then keep the system settings unchanged. If your goal is to reduce the space used in the shared pool then you can use one of the following solutions with different scopes:

  1. Individual SQL statements - drop the histograms on the columns for each of the affected SQL statements

  2. System-wide - set CURSOR_SHARING to FORCE this will ensure only one child cursor per SQL statement


Both of these solutions require testing to ensure you get the desired effect on your system. Oracle Database 11g provides a much better solution using the Adaptive Cursor Sharing feature. In Oracle Database 11g, all you need to do is set CURSOR_SHARING to FORCE and keep the histograms. With Adaptive Cursor Sharing, the optimizer will create a cursor only when its plan is different from any of the plans used by other child cursors. So in the above example, you will get two child cursors (C1 and C2) instead of 3.

Categories: DBA Blogs, Development

Enterprise Linux 4 Update 8 Released on ULN, public-yum.oracle.com

Sergio's Blog - Tue, 2009-05-26 07:36

We've just released Oracle Enterprise Linux 4, Update 8 on ULN (linux.oracle.com) and on public-yum.oracle.com. DVD ISOs are available for Unbreakable Linux support customers by calling support. They'll be available soon on edelivery.oracle.com/linux

Categories: DBA Blogs

Querying v$lock

Jared Still - Wed, 2009-04-29 12:24
There have been a number of scripts made available for querying v$lock to diagnose locking issues.

One example is one I got long ago from tsawmiller on Oracle-L. The original script showlock.sql, or something close to it is still available at OraFaq.com showlock.sql

showlock.sql has morphed over the years to keep up with changing versions of Oracle.

At one time the showlock.sql resembled the OH/rdbms/admin/utllockt.sql script, in that it created a temporary table to speed up the results, as the join on v$lock, dba_sessions and dba_waiters was so slow.

That was remedied at one point by the use of the ordered hint. That hint may no longer be necessary, but the script is still fast on all versions of Oracle that I need it on, (9i-11g) and I am too lazy to test something that isn't broken.

This script could still be further updated by the use of the v$lock_type view, eliminating the large decode statements in the script. As v$lock_type is not available in 9i though, I leave the decodes in. When the last 9i database is gone from our environment however, the script can be shortened considerably.

The decode statements were mostly lifted from a script provided by Oracle. MetaLink document (or My Oracle Support now I guess) # 1020008.6 has a 'fully decoded' locking script that is current though 11g I believe.

The problem with that script however is that it does not correctly look up the name of the object that is locked.

The reason I have even brought this up is that a bit of my workday yesterday was spent updating the script, and making sure it worked as expected. The COMMAND column was also added. In addition, the outer joins were converted to the much neater ANSI join syntax, and one outer join was eliminated.

Here's the output from a test. It may be easier to read if you cut and paste it into a text editor, as the formatting here doesn't work well for wide output. Better yet, test the script and look at the output for yourself.


       Oracle              Database                                  Lock                  Mode            Mode       OS                 OS
SID Usernam WATR BLKR Object COMMAND Type Lock Description Held Requested Program Process
------ ------- ----- ----- ------------------------- --------------- ---- ---------------- --------------- ---------- ------------------ -------
73 JKSTILL 83 JKSTILL.A SELECT TM DML enqueue lock Exclusive None sqlplus@poirot (TN 21430
83 JKSTILL 73 JKSTILL.A LOCK TABLE TM DML enqueue lock None Exclusive sqlplus@poirot (TN 21455

2 rows selected.




Though utllockt.sql may work well enough, it does have a couple of drawbacks:

1. it does not provide enough information
2. it creates a temporary table.

That second item means that you better be sure to run the script from a session separate from any holding locks. In production that probably does not matter, as that is what would normally be done anyway. During testing however it can be a bit frustrating until you realize the the DDL in the script is causing your locks to be released.

What I like about this script is that it shows me what I need to know, and it is very fast.
Of course, now that I have stated that someone will run it on a system where it performs poorly...

For showlock.sql to work, the dba_waiters view must be created.
If this has not already been done, it can be created by logging in as SYSDBA and running the OH/rdbms/admin/catblock.sql script.

Here's how you can easily test sh0wlock.sql:

Session A -
create table a (a integer);
lock table a in exclusive mode;

Session B
lock table a in exclusive mode;

Now either from session A or a new session, run the showlock.sql script.

Here's the script.


-- showlock.sql - show all user locks
--
-- see ML Note 1020008.6 for fully decoded locking script
-- parts of the that script to not work correctly, but the
-- lock types are current
-- (script doesn't find object that is locked )
--
-- speeded up greatly by changing order of where clause,
-- jks 04/09/1997 - show lock addresses and lockwait

-- jks 04/09/1997 - outer join on all_objects
-- encountered situation on 7.2
-- where there was a lock with no
-- matching object_id
-- jks 02/24/1999 - join to dba_waiters to show waiters and blockers
-- jkstill 05/22/2006 - revert back to previous version without tmp tables
-- update lock info
-- add lock_description and rearrange output
-- jkstill 04/28/2008 - added command column
-- updated lock types
-- removed one outer join by using inline view on sys.user$
-- jkstill 04/28/2008 - added subquery factoring
-- converted to ANSI joins
-- changed alias for v$lock to l and v$session to s

set trimspool on
ttitle off
set linesize 150
set pagesize 60
column command format a15
column osuser heading 'OS|Username' format a7 truncate
column process heading 'OS|Process' format a7 truncate
column machine heading 'OS|Machine' format a10 truncate
column program heading 'OS|Program' format a18 truncate
column object heading 'Database|Object' format a25 truncate
column lock_type heading 'Lock|Type' format a4 truncate
column lock_description heading 'Lock Description'format a16 truncate
column mode_held heading 'Mode|Held' format a15 truncate
column mode_requested heading 'Mode|Requested' format a10 truncate
column sid heading 'SID' format 999
column username heading 'Oracle|Username' format a7 truncate
column image heading 'Active Image' format a20 truncate
column sid format 99999
col waiting_session head 'WATR' format 9999
col holding_session head 'BLKR' format 9999

with dblocks as (
select /*+ ordered */
l.kaddr,
s.sid,
s.username,
lock_waiter.waiting_session,
lock_blocker.holding_session,
(
select name
from sys.user$
where user# = o.owner#
) ||'.'||o.name
object,
decode(command,
0,'BACKGROUND',
1,'Create Table',
2,'INSERT',
3,'SELECT',
4,'CREATE CLUSTER',
5,'ALTER CLUSTER',
6,'UPDATE',
7,'DELETE',
8,'DROP',
9,'CREATE INDEX',
10,'DROP INDEX',
11,'ALTER INDEX',
12,'DROP TABLE',
13,'CREATE SEQUENCE',
14,'ALTER SEQUENCE',
15,'ALTER TABLE',
16,'DROP SEQUENCE',
17,'GRANT',
18,'REVOKE',
19,'CREATE SYNONYM',
20,'DROP SYNONYM',
21,'CREATE VIEW',
22,'DROP VIEW',
23,'VALIDATE INDEX',
24,'CREATE PROCEDURE',
25,'ALTER PROCEDURE',
26,'LOCK TABLE',
27,'NO OPERATION',
28,'RENAME',
29,'COMMENT',
30,'AUDIT',
31,'NOAUDIT',
32,'CREATE EXTERNAL DATABASE',
33,'DROP EXTERNAL DATABASE',
34,'CREATE DATABASE',
35,'ALTER DATABASE',
36,'CREATE ROLLBACK SEGMENT',
37,'ALTER ROLLBACK SEGMENT',
38,'DROP ROLLBACK SEGMENT',
39,'CREATE TABLESPACE',
40,'ALTER TABLESPACE',
41,'DROP TABLESPACE',
42,'ALTER SESSION',
43,'ALTER USER',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
47,'PL/SQL EXECUTE',
48,'SET TRANSACTION',
49,'ALTER SYSTEM SWITCH LOG',
50,'EXPLAIN',
51,'CREATE USER',
52,'CREATE ROLE',
53,'DROP USER',
54,'DROP ROLE',
55,'SET ROLE',
56,'CREATE SCHEMA',
57,'CREATE CONTROL FILE',
58,'ALTER TRACING',
59,'CREATE TRIGGER',
60,'ALTER TRIGGER',
61,'DROP TRIGGER',
62,'ANALYZE TABLE',
63,'ANALYZE INDEX',
64,'ANALYZE CLUSTER',
65,'CREATE PROFILE',
66,'DROP PROFILE',
67,'ALTER PROFILE',
68,'DROP PROCEDURE',
69,'DROP PROCEDURE',
70,'ALTER RESOURCE COST',
71,'CREATE SNAPSHOT LOG',
72,'ALTER SNAPSHOT LOG',
73,'DROP SNAPSHOT LOG',
74,'CREATE SNAPSHOT',
75,'ALTER SNAPSHOT',
76,'DROP SNAPSHOT',
79,'ALTER ROLE',
85,'TRUNCATE TABLE',
86,'TRUNCATE CLUSTER',
87,'-',
88,'ALTER VIEW',
89,'-',
90,'-',
91,'CREATE FUNCTION',
92,'ALTER FUNCTION',
93,'DROP FUNCTION',
94,'CREATE PACKAGE',
95,'ALTER PACKAGE',
96,'DROP PACKAGE',
97,'CREATE PACKAGE BODY',
98,'ALTER PACKAGE BODY',
99,'DROP PACKAGE BODY',
command||'-UNKNOWN'
) COMMAND,
-- lock type
-- will always be TM, TX or possible UL (user supplied) for user locks
l.type lock_type,
decode
(
l.type,
'BL','Buffer hash table instance lock',
'CF',' Control file schema global enqueue lock',
'CI','Cross-instance function invocation instance lock',
'CS','Control file schema global enqueue lock',
'CU','Cursor bind lock',
'DF','Data file instance lock',
'DL','Direct loader parallel index create',
'DM','Mount/startup db primary/secondary instance lock',
'DR','Distributed recovery process lock',
'DX','Distributed transaction entry lock',
'FI','SGA open-file information lock',
'FS','File set lock',
'HW','Space management operations on a specific segment lock',
'IN','Instance number lock',
'IR','Instance recovery serialization global enqueue lock',
'IS','Instance state lock',
'IV','Library cache invalidation instance lock',
'JQ','Job queue lock',
'KK','Thread kick lock',
'LA','Library cache lock instance lock (A=namespace)',
'LB','Library cache lock instance lock (B=namespace)',
'LC','Library cache lock instance lock (C=namespace)',
'LD','Library cache lock instance lock (D=namespace)',
'LE','Library cache lock instance lock (E=namespace)',
'LF','Library cache lock instance lock (F=namespace)',
'LG','Library cache lock instance lock (G=namespace)',
'LH','Library cache lock instance lock (H=namespace)',
'LI','Library cache lock instance lock (I=namespace)',
'LJ','Library cache lock instance lock (J=namespace)',
'LK','Library cache lock instance lock (K=namespace)',
'LL','Library cache lock instance lock (L=namespace)',
'LM','Library cache lock instance lock (M=namespace)',
'LN','Library cache lock instance lock (N=namespace)',
'LO','Library cache lock instance lock (O=namespace)',
'LP','Library cache lock instance lock (P=namespace)',
'LS','Log start/log switch enqueue lock',
'MB','Master buffer hash table instance lock',
'MM','Mount definition gloabal enqueue lock',
'MR','Media recovery lock',
'PA','Library cache pin instance lock (A=namespace)',
'PB','Library cache pin instance lock (B=namespace)',
'PC','Library cache pin instance lock (C=namespace)',
'PD','Library cache pin instance lock (D=namespace)',
'PE','Library cache pin instance lock (E=namespace)',
'PF','Library cache pin instance lock (F=namespace)',
'PF','Password file lock',
'PG','Library cache pin instance lock (G=namespace)',
'PH','Library cache pin instance lock (H=namespace)',
'PI','Library cache pin instance lock (I=namespace)',
'PI','Parallel operation lock',
'PJ','Library cache pin instance lock (J=namespace)',
'PK','Library cache pin instance lock (L=namespace)',
'PL','Library cache pin instance lock (K=namespace)',
'PM','Library cache pin instance lock (M=namespace)',
'PN','Library cache pin instance lock (N=namespace)',
'PO','Library cache pin instance lock (O=namespace)',
'PP','Library cache pin instance lock (P=namespace)',
'PQ','Library cache pin instance lock (Q=namespace)',
'PR','Library cache pin instance lock (R=namespace)',
'PR','Process startup lock',
'PS','Library cache pin instance lock (S=namespace)',
'PS','Parallel operation lock',
'PT','Library cache pin instance lock (T=namespace)',
'PU','Library cache pin instance lock (U=namespace)',
'PV','Library cache pin instance lock (V=namespace)',
'PW','Library cache pin instance lock (W=namespace)',
'PX','Library cache pin instance lock (X=namespace)',
'PY','Library cache pin instance lock (Y=namespace)',
'PZ','Library cache pin instance lock (Z=namespace)',
'QA','Row cache instance lock (A=cache)',
'QB','Row cache instance lock (B=cache)',
'QC','Row cache instance lock (C=cache)',
'QD','Row cache instance lock (D=cache)',
'QE','Row cache instance lock (E=cache)',
'QF','Row cache instance lock (F=cache)',
'QG','Row cache instance lock (G=cache)',
'QH','Row cache instance lock (H=cache)',
'QI','Row cache instance lock (I=cache)',
'QJ','Row cache instance lock (J=cache)',
'QK','Row cache instance lock (L=cache)',
'QL','Row cache instance lock (K=cache)',
'QM','Row cache instance lock (M=cache)',
'QN','Row cache instance lock (N=cache)',
'QO','Row cache instance lock (O=cache)',
'QP','Row cache instance lock (P=cache)',
'QQ','Row cache instance lock (Q=cache)',
'QR','Row cache instance lock (R=cache)',
'QS','Row cache instance lock (S=cache)',
'QT','Row cache instance lock (T=cache)',
'QU','Row cache instance lock (U=cache)',
'QV','Row cache instance lock (V=cache)',
'QW','Row cache instance lock (W=cache)',
'QX','Row cache instance lock (X=cache)',
'QY','Row cache instance lock (Y=cache)',
'QZ','Row cache instance lock (Z=cache)',
'RE','USE_ROW_ENQUEUE enforcement lock',
'RT','Redo thread global enqueue lock',
'RW','Row wait enqueue lock',
'SC','System commit number instance lock',
'SH','System commit number high water mark enqueue lock',
'SM','SMON lock',
'SN','Sequence number instance lock',
'SQ','Sequence number enqueue lock',
'SS','Sort segment lock',
'ST','Space transaction enqueue lock',
'SV','Sequence number value lock',
'TA','Generic enqueue lock',
'TD','DDL enqueue lock',
'TE','Extend-segment enqueue lock',
'TM','DML enqueue lock',
'TO','Temporary Table Object Enqueue',
'TS',decode(l.id2,
0,'Temporary segment enqueue lock (ID2=0)',
1,'New block allocation enqueue lock (ID2=1)',
'UNKNOWN!'
),
'TT','Temporary table enqueue lock',
'TX','Transaction enqueue lock',
'UL','User supplied lock',
'UN','User name lock',
'US','Undo segment DDL lock',
'WL','Being-written redo log instance lock',
'WS','Write-atomic-log-switch global enqueue lock',
'UNKOWN'
) lock_description,
decode
(
l.lmode,
0, 'None', /* Mon Lock equivalent */
1, 'No Lock', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SRX)', /* C */
6, 'Exclusive', /* X */
to_char(l.lmode)
) mode_held,
decode
(
l.request,
0, 'None', /* Mon Lock equivalent */
1, 'No Lock', /* N */
2, 'Row-S (SS)', /* L */
3, 'Row-X (SX)', /* R */
4, 'Share', /* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive', /* X */
to_char(l.request)
) mode_requested,
s.osuser,
s.machine,
s.program,
s.process
from
v$lock l
join v$session s on s.sid = l.sid
left outer join sys.dba_waiters lock_blocker on lock_blocker.waiting_session = s.sid
left outer join sys.dba_waiters lock_waiter on lock_waiter.holding_session = s.sid
left outer join sys.obj$ o on o.obj# = l.id1
where s.type != 'BACKGROUND'
)
select
--kaddr,
sid,
username,
waiting_session,
holding_session,
object,
command,
lock_type,
lock_description,
mode_held,
mode_requested,
--osuser,
--machine,
program,
process
from dblocks
order by sid, object
/

Categories: DBA Blogs

OpenWorld Tokyo Oracle VM Sessions

Sergio's Blog - Mon, 2009-04-13 07:21

Next week I'll be in Tokyo to deliver two sessions on Oracle VM. Consider this post an introduction to the Virtualization blog, where Adam Hawley has been writing about Oracle VM for a few weeks now. I'll be adding this to the blog roll.

Categories: DBA Blogs

New Oracle Public Yum Server

Sergio's Blog - Thu, 2009-03-19 09:01

We've just launched a new public yum server for Oracle Enterprise Linux and Oracle VM. This yum server offers a free and convenient way to install packages from the Enterprise Linux and Oracle VM installation media via a yum client.
To get started: public-yum.oracle.com

Categories: DBA Blogs

Another One From the Archives: Easy Connection Identifier

Sergio's Blog - Tue, 2009-03-10 08:30

My good friend Joel showed me how you can connect to a (remote) database using an easy connect identifier. Here's how it works:

    sqlplus sergiodb/sergiodb@//127.0.0.1:1521/mvl

It looks to be a way of constructing a connect identifier that's new since database 10g Release 1. The syntax is as follows:

   [//]host[:port][/service_name]

The documentation sums it up nicely: The easy connection identifier can be used wherever you can use a full connection identifier, or a net service name. The easy syntax is less complex, and no tnsnames.ora entry is required.

Categories: DBA Blogs

The Evils of Encoding Meaning Into Data

Jared Still - Thu, 2009-03-05 12:48
About a year ago I worked on collating and transforming data from an application so that it could be imported into another app. I've performed this exercise a number of times in the past 20 or so years, and while it is never boring, it is sometimes quite challenging.

Oft times when trying to make the data suitable for inclusion in the new applications, I ask my self "What were they thinking?"

I will leave the answer to that up to your imagination, as my answers to that particular question are not always complimentary.

One of the problems run into is when the data modeler and database designer chose to allow data with encoded special meanings.

In other words, there is key data in the database, the meaning of which can only be ascertained by asking someone that knows, or finding it in the documentation (Is there documentation?)

The example I will use is a real one I ran into.

Given a table containing projects, and revisions of those projects that are indicated as such by a revision "number".

A sensible series of revision numbers might be a numeric range beginning with 1, or even an alpha range beginning with 'A', or even a combination thereof.

Personally, I prefer the unambiguous nature of an ascending numeric range. 2 is always greater than 1. There is no case involved as there would be with an alpha range.

Which is greater, "a1" or "A1"? You know how this will sort in the database, but will it be what the users expect?

While a numeric range would have been preferable, the data I was given to work with used a range of revision "numbers" that was numeric and alpha, with a numeric value being regarding as greater than the "numeric" value. The "numeric" is in quotes, as this obviously must be stored as a string, and in this case with a leading zero.

Given this rule, a project with revisions of A,B,C,D,01,02 would have a most recent revision of "02". This is not the way it works in the standard collating order in the database.

11:34:03 SQL> create table rev (version varchar2(2));

Table created.

11:34:03 SQL>
11:34:03 SQL> insert into rev values('A');
11:34:03 SQL> insert into rev values('B');
11:34:03 SQL> insert into rev values('C');
11:34:03 SQL> insert into rev values('01');
11:34:03 SQL> insert into rev values('02');
11:34:03 SQL>
11:34:03 SQL> select * from rev order by version;

VE
--
01
02
A
B
C

5 rows selected.

In a perfect world, the data modeler or data architect would work with the users to create a reasonable versioning method. In this case however there is no choice but to work with what I was given.

From the data provided, only the most recent version was to be included in the imported data.
With a versioning system that doesn't follow the normal collating order, this requires some code to determine what is really the greatest version.

If you know a method to accomplish this in straight SQL, please feel free to post it. I could not think of a pure SQL solution.

The following generic function was created to determine the greatest revision value given a table name and the key columns.

-- maxrev.sql
-- return the maximum numeric revision
-- returna alpha if no numeric exists
-- maximum rev is numeric
-- revs are alpha - mixed number and character

create or replace function maxrev
(
table_name_in varchar2,
key_column_in varchar2,
value_column_in varchar2,
key_value_in varchar2
)
return varchar2
as
v_max_rev integer := 0;
v_tmp_rev integer;
v_col_rev varchar2(10);
v_return_rev varchar2(30);
type curType is ref cursor;
l_cursor curType;
v_sql varchar2(1000);

function is_number( chk_data_in varchar2 )
return boolean
is
dummy number(38,4);
begin
dummy := to_number(chk_data_in);
return true;
exception
when value_error then
return false;
when others then
raise;
end;

begin
-- get the maximum rev, whether alpha or numeric
-- there may not be any numerics
v_sql := 'select max(' || value_column_in || ') from ' || table_name_in || ' where ' || key_column_in || ' = :1';

execute immediate v_sql into v_return_rev using key_value_in;

--return v_return_rev;

v_sql := 'select ' || value_column_in || ' from ' || table_name_in || ' where ' || key_column_in || ' = :1';

open l_cursor for v_sql using key_value_in;
loop
fetch l_cursor into v_col_rev;
exit when l_cursor%notfound;

if (is_number(v_col_rev)) then
v_tmp_rev := to_number(v_col_rev);
end if;
if (v_tmp_rev > v_max_rev) then
v_max_rev := v_tmp_rev;
v_return_rev := v_col_rev;
end if;
end loop;

return v_return_rev;
end;
/

( Reviewing this function just now, I see what could be considered a programming error.
Let me know if you spot it. )

Here's a test case to prove that the function works as expected.

-- maxrev_test.sql
-- should always return numeric if it exists, otherwise alpha

drop table maxrev_test;

create table maxrev_test ( id varchar2(4), rev varchar2(2));

insert into maxrev_test values('T1', 'A');
insert into maxrev_test values('T1', 'B');
insert into maxrev_test values('T1', '01');
insert into maxrev_test values('T1', '02');
insert into maxrev_test values('T2', '01');
insert into maxrev_test values('T2', '02');
insert into maxrev_test values('T2', '03');
insert into maxrev_test values('T3', 'X');
insert into maxrev_test values('T3', 'Y');
insert into maxrev_test values('T3', 'Z');

commit;

select * from maxrev_test order by id,rev;

col rev format a10

prompt
prompt Incorrect results
prompt

select id, max(rev) rev
from maxrev_test
group by id
order by id
/

prompt
prompt Correct results
prompt

select id, maxrev('maxrev_test','id','rev',id) rev
from maxrev_test
group by id
order by id
/



And the results:

Incorrect results

ID REV
---- ----------
T1 B
T2 03
T3 Z

3 rows selected.

Correct results

ID REV
---- ----------
T1 02
T2 03
T3 Z

3 rows selected.

Categories: DBA Blogs

Installing Required RPMs from DVD Before You Install Oracle Database 10g or 11g

Sergio's Blog - Tue, 2009-02-17 00:42

I came across a forum post in which one of my co-workers, Avi Miller, explained how to install Oracle Enterprise Linux from DVD so that all software requirements are met when you fire up the Oracle Database 10g or 11g installer.

There are several ways to deal with the software preinstallation requirements for the Oracle Database. And, while it's not very difficult to install the required RPMs, it can be somewhat awkward to do so in the correct dependency order.

To simplify this task, Oracle provides the oracle-validated RPM, discussed here, here, and here previously on this blog. The Oracle® Database Installation Guide 11g Release 1 (11.1) for Linux also describes how to install the oracle-validated RPM if you have access to Unbreakable Linux Network (ULN)

If you're installing Oracle Enterprise Linux, to run Oracle Database 10g or 11g, following the steps Avi outlined will save you time. Especially if you don't have access to ULN.

  1. Follow the installation process as normal until you get to the first software selection screen (it lists a series of tasks that you can add support for, and has an option at the bottom: "Customize Later" and "Customize Now"
  2. Select the "Customize Now" option and click Next




  3. Select "Base System" in the left-hand list and then "System Tools" in the right hand list. Click the checkbox next to "System Tools", then click the "Optional Packages" button.






  4. ins4.png

  5. Scroll down and select the "oracle-validated" package and click "Close"


Avi continues: "You can now click "next" and continue the installation as normal. This also creates the oracle user/groups, sets up sysctl.conf, limits.conf, etc. It's fairly nifty." I followed these steps using the Oracle Enterprise Linux 5, Update 3 DVD I downloaded via edelivery.oracle.com, and it works like a breeze. Here's a final screen shot of the installer looking happy:

Categories: DBA Blogs

Maintaining statistics on large partitioned tables

Inside the Oracle Optimizer - Wed, 2009-02-11 16:07
We have gotten a lot of questions recently regarding how to gather and maintain optimizer statistics on large partitioned tables. The majority of these questions can be summarized into two topics:

  1. When queries access a single partition with stale or non-existent partition level statistics I get a sub optimal plan due to “Out of Range” values

  2. Global statistics collection is extremely expensive in terms of time and system resources

This article will describe both of these issues and explain how you can address them both in Oracle Database 10gR2 and 11gR1.


Out of Range
Large tables are often decomposed into smaller pieces called partitions in order to improve query performance and ease of data management. The Oracle query optimizer relies on both the statistics of the entire table (global statistics) and the statistics of the individual partitions (partition statistics) to select a good execution plan for a SQL statement. If the query needs to access only a single partition, the optimizer uses only the statistics of the accessed partition. If the query access more than one partition, it uses a combination of global and partition statistics.

“Out of Range” means that the value supplied in a where clause predicate is outside the domain of values represented by the [minimum, maximum] column statistics. The optimizer prorates the selectivity based on the distance between the predicate value and the maximum value (assuming the value is higher than the max), that is, the farther the value is from the maximum value, the lower the selectivity will be. This situation occurs most frequently in tables that are range partitioned by a date column, a new partition is added, and then queried while rows are still being loaded in the new partition. The partition statistics will be stale very quickly due to the continuous trickle feed load even if the statistics get refreshed periodically. The maximum value known to the optimizer is not correct leading to the “Out of Range” condition. The under-estimation of selectivity often leads the query optimizer to pick a sub optimal plan. For example, the query optimizer would pick an index access path while a full scan is a better choice.

The "Out of Range" condition can be prevented by using the new copy table statistics procedure available in Oracle Database10.2.0.4 and 11g. This procedure copies the statistics of the source [sub] partition to the destination [sub] partition. It also copies the statistics of the dependent objects: columns, local (partitioned) indexes etc. It adjusts the minimum and maximum values of the partitioning column as follows; it uses the high bound partitioning value as the maximum value of the first partitioning column (it is possible to have concatenated partition columns) and high bound partitioning value of the previous partition as the minimum value of the first partitioning column for range partitioned table. It can optionally scale some of the other statistics like the number of blocks, number of rows etc. of the destination partition.

Assume we have a table called SALES that is ranged partitioned by quarter on the SALES_DATE column. At the end of every day data is loaded into latest partition. However, statistics are only gathered at the end of every quarter when the partition is fully loaded. Assuming global and partition level statistics (for all fully loaded partitions) are up to date, use the following steps in order to prevent getting a sub-optimal plan due to “out of range”.


  1. Lock the table statistics using LOCK_TABLE_STATS procedure in DBMS_STATS. This is to avoid interference from auto statistics job.


    EXEC DBMS_STATS.LOCK_TABLE_STATS('SH','SALES');


  2. Before beginning the initial load into each new partition (say SALES_Q4_2000) copy the statistics from the previous partition (say SALES_Q3_2000) using COPY_TABLE_STATS. You need to specify FORCE=>TRUE to override the statistics lock.

    EXEC DBMS_STATS.COPY_TABLE_STATS ('SH', 'SALES', 'SALES_Q3_2000', 'SALES_Q4_2000', FORCE=>TRUE);


Expensive global statistics collection

In data warehouse environment it is very common to do a bulk load directly into one or more empty partitions. This will make the partition statistics stale and may also make the global statistics stale. Re-gathering statistics for the effected partitions and for the entire table can be very time consuming. Traditionally, statistics collection is done in a two-pass approach:


  • In the first pass we will scan the table to gather the global statistics

  • In the second pass we will scan the partitions that have been changed to gather their partition level statistics.

The full scan of the table for global statistics collection can be very expensive depending on the size of the table. Note that the scan of the entire table is done even if we change a small subset of partitions.

In Oracle Database 11g, we avoid scanning the whole table when computing global statistics by deriving the global statistics from the partition statistics. Some of the statistics can be derived easily and accurately from partition statistics. For example, number of rows at global level is the sum of number of rows of partitions. Even global histogram can be derived from partition histograms. But the number of distinct values (NDV) of a column cannot be derived from partition level NDVs. So, Oracle maintains another structure called a synopsis for each column at the partition level. A synopsis can be considered as sample of distinct values. The NDV can be accurately derived from synopses. We can also merge multiple synopses into one. The global NDV is derived from the synopsis generated by merging all of the partition level synopses. To summarize


  1. Gather statistics and create synopses for the changed partitions only
  2. Oracle automatically merges partition level synopses into a global synopsis
  3. The global statistics are automatically derived from the partition level statistics and global synopses


Incremental maintenance feature is disabled by default. It can be enabled by changing the INCREMENTAL table preference to true. It can also be enabled for a particular schema or at the database level. If you are interested in more details of the incremental maintenance feature, please refer to the following paper presented in SIGMOD 2008 and to our previous blog entry on new ndv gathering in 11g.


Assume we have table called SALES that is range partitioned by day on the SALES_DATE column. At the end of every day data is loaded into latest partition and partition statistics are gathered. Global statistics are only gathered at the end of every month because gathering them is very time and resource intensive. Use the following steps in order to maintain global statistics after every load.

  1. Turn on incremental feature for the table.


    EXEC DBMS_STATS.SET_TABLE_PREFS('SH','SALES','INCREMENTAL','TRUE');

  2. At the end of every load gather table statistics using GATHER_TABLE_STATS command. You don’t need to specify the partition name. Also, do not specify the granularity parameter. The command will collect statistics for partitions with stale or missing statistics and update the global statistics based on the partition level statistics and synopsis.


    EXEC DBMS_STATS.GATHER_TABLE_STATS('SH','SALES');


Note: that the incremental maintenance feature was introduced in Oracle Database 11g Release 1. However, we also provide a solution in Oracle Database10g Release 2 (10.2.0.4) that simulates the same behavior. The 10g solution is a new value, 'APPROX_GLOBAL AND PARTITION' for the GRANULARITY parameter of the GATHER_TABLE_STATS procedures. It behaves the same as the incremental maintenance feature except that we don’t update the NDV for non-partitioning columns and number of distinct keys of the index at the global level. For partitioned column we update the NDV as the sum of NDV at the partition levels. Also we set the NDV of columns of unique indexes as the number of rows of the table. In general, non-partitioning column NDV at the global level becomes stale less often. It may be possible to collect global statistics less frequently then the default (when table changes 10%) since approx_global option maintains most of the global statistics accurately.

Let's take a look at an example to see how you would effectively use the Oracle Database 10g approach.

After the data load is complete, gather statistics using DBMS_STATS.GATHER_TABLE_STATS for the last partition (say SALES_11FEB2009), specify granularity => 'APPROX_GLOBAL AND PARTITION'. It will collect statistics for the specified partition and derive global statistics from partition statistics (except for NDV as described before).

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SH', 'SALES', 'SALES_11FEB2009', GRANULARITY => 'APPROX_GLOBAL AND PARTITION');

It is necessary to install the one off patch for bug 8719831 if you are using copy_table_stats procedure or APPROX_GLOBAL option in 10.2.0.4 (patch 8877245) or in 11.1.0.7 (patch 8877251).

Categories: DBA Blogs, Development

SQL Plan Management (Part 4 of 4): User Interfaces and Other Features

Inside the Oracle Optimizer - Mon, 2009-02-02 19:32
In the first three parts of this article, we have seen how SQL plan baselines are created, used and evolved. In this final installment, we will show some user interfaces, describe the interaction of SPM with other features and answer some of your questions.

DBMS_SPM package

A new package, DBMS_SPM, allows you to manage plan histories. We have already seen in previous examples how you can use it to create and evolve SQL plan baselines. Other management functions include changing attributes (like enabled status and plan name) of plans or dropping plans. You need the ADMINISTER SQL MANAGEMENT OBJECT privilege to execute this package.

Viewing the plan history

Regardless of how a plan history is created, you can view details about the various plans in the view DBA_SQL_PLAN_BASELINES. At the end of Part 3 of this blog, we saw that the SQL statement had two accepted plans:

SQL> select sql_text, sql_handle, plan_name, enabled, accepted
&nbsp 2&nbsp from dba_sql_plan_baselines;



SQL_TEXT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp SQL_HANDLE&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp PLAN_NAME&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp ENA ACC
------------------------ ------------------------ ----------------------------- --- ---
select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
&nbsp and s.time_id = t.time
_id
&nbsp and p.prod_id < :pid

select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
&nbsp and s.time_id = t.time
_id
&nbsp and p.prod_id < :pid



The SQL handle is a unique identifier for each SQL statement that you can use when managing your plan history using the DBMS_SPM package.

Creating an accepted plan by modifying the SQL text

Some of you may be manually tuning SQL statements by adding hints or otherwise modifying the SQL text. If you enable automatic capture of SQL plans and then execute this statement, you will be creating a SQL plan baseline for this modified statement. What you most likely want, however, is to add this plan to the plan history of the original SQL statement. Here's how you can do this using the above SQL statement as an example.

Let's modify the SQL statement, execute it and look at the plan:


SQL> var pid number
SQL> exec :pid := 100;

PL/SQL procedure successfully completed.

SQL> select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
2&nbsp&nbsp&nbsp from sales s, products p, times t
3&nbsp&nbsp&nbsp where s.prod_id = p.prod_id
4&nbsp&nbsp&nbsp&nbsp&nbsp and s.time_id = t.time_id
5&nbsp&nbsp&nbsp&nbsp&nbsp and p.prod_id < :pid;

PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
9 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('b17wnz4y8bqv1', 0, 'basic note'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p, times t where s.prod_id = p.prod_id&nbsp&nbsp and
s.time_id = t.time_id&nbsp&nbsp and p.prod_id < :pid

Plan hash value: 2290436051

---------------------------------------------------------------
| Id&nbsp | Operation&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | Name&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
---------------------------------------------------------------
|&nbsp&nbsp 0 | SELECT STATEMENT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 1 |&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 2 |&nbsp&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 3 |&nbsp&nbsp&nbsp TABLE ACCESS FULL&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | TIMES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 4 |&nbsp&nbsp&nbsp PARTITION RANGE ALL&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 5 |&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY LOCAL INDEX ROWID| SALES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 6 |&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP CONVERSION TO ROWIDS&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 7 |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | SALES_PROD_BIX |
| &nbsp 8 |&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | PRODUCTS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 9 |&nbsp&nbsp&nbsp INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | PRODUCTS_PK&nbsp&nbsp&nbsp |
---------------------------------------------------------------


23 rows selected.


We can now create a new accepted plan for the original SQL statement by associating the modified statement's plan to the original statement's sql handle (obtained from DBA_SQL_PLAN_BASELINES):

SQL> var pls number
SQL> exec :pls := dbms_spm.load_plans_from_cursor_cache( -
> &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp sql_id => 'b17wnz4y8bqv1', -
> &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp plan_hash_value => 2290436051, -
> &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp sql_handle => 'SYS_SQL_4bf04d85fcc170b0');


If the original SQL statement does not already have a plan history (and thus no SQL handle), another version of load_plans_from_cursor_cache allows you to specify the original statement's text.

To confirm that we now have three accepted plans for our SQL statement, let's check in DBA_SQL_PLAN_BASELINES:

SQL> select sql_text, sql_handle, plan_name, enabled, accepted
&nbsp 2&nbsp from dba_sql_plan_baselines;



SQL_TEXT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp SQL_HANDLE&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp PLAN_NAME&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp ENA ACC
------------------------ ------------------------ ----------------------------- --- ---
select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0888547d3 YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
&nbsp and s.time_id = t.time
_id
&nbsp and p.prod_id < :pid

select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
&nbsp and s.time_id = t.time
_id
&nbsp and p.prod_id < :pid

select p.prod_name, s.am SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
&nbsp and s.time_id = t.time
_id
&nbsp and p.prod_id < :pid



Displaying plans

When the optimizer uses an accepted plan for a SQL statement, you can see it in the plan table (for explain) or V$SQL_PLAN (for shared cursors). Let's explain the SQL statement above and display its plan:

SQL> explain plan for
&nbsp 2&nbsp select p.prod_name, s.amount_sold, t.calendar_year
&nbsp 3&nbsp from sales s, products p, times t
&nbsp 4&nbsp where s.prod_id = p.prod_id
&nbsp 5&nbsp&nbsp&nbsp and s.time_id = t.time_id
&nbsp 6&nbsp&nbsp&nbsp and p.prod_id < :pid;

Explained.

SQL> select * from table(dbms_xplan.display('plan_table', null, 'basic note'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2787970893

----------------------------------------------------------------
| Id&nbsp&nbsp| Operation&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| Name&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
----------------------------------------------------------------
|&nbsp&nbsp 0&nbsp| SELECT STATEMENT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 1&nbsp|&nbsp NESTED LOOPS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 2&nbsp|&nbsp&nbsp NESTED LOOPS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 3&nbsp|&nbsp&nbsp&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 4&nbsp|&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| PRODUCTS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 5&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| PRODUCTS_PK&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 6&nbsp|&nbsp&nbsp&nbsp&nbsp PARTITION RANGE ALL&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 7&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY LOCAL INDEX ROWID| SALES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 8&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP CONVERSION TO ROWIDS&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 9&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| SALES_PROD_BIX&nbsp|
|&nbsp 10&nbsp|&nbsp&nbsp&nbsp INDEX UNIQUE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| TIME_PK&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp 11&nbsp|&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| TIMES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
----------------------------------------------------------------

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

22 rows selected.



The note at the bottom tells you that the optimizer used an accepted plan.

A plan history might have multiple plans. You can see one of the accepted plans if the optimizer selects it for execution. But what if you want to display some or all of the other plans? You can do this using the display_sql_plan_baseline function in the DBMS_XPLAN package. Using the above example, here's how you can display the plan for all plans in the plan history.

SQL> select *
&nbsp 2&nbsp from table(dbms_xplan.display_sql_plan_baseline(
&nbsp 3&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp sql_handle => 'SYS_SQL_4bf04d85fcc170b0', format => 'basic'));



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

--------------------------------------------------------------------------------
SQL handle: SYS_SQL_4bf04d85fcc170b0
SQL text: select p.prod_name, s.amount_sold, t.calendar_year from sales s,
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp products p, times t where s.prod_id = p.prod_id&nbsp&nbsp and s.time_id =
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp t.time_id&nbsp&nbsp and p.prod_id < :pid
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_fcc170b0888547d3
Enabled: YES &nbsp&nbsp&nbsp Fixed: NO &nbsp&nbsp&nbsp&nbsp Accepted: YES &nbsp&nbsp&nbsp Origin: MANUAL-LOAD
--------------------------------------------------------------------------------

Plan hash value: 2290436051

---------------------------------------------------------------
| Id&nbsp | Operation&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | Name&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
---------------------------------------------------------------
| &nbsp 0 | SELECT STATEMENT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 1 |&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 2 |&nbsp&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 3 |&nbsp&nbsp&nbsp TABLE ACCESS FULL&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | TIMES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 4 |&nbsp&nbsp&nbsp PARTITION RANGE ALL&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 5 |&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY LOCAL INDEX ROWID| SALES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 6 |&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP CONVERSION TO ROWIDS&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 7 |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | SALES_PROD_BIX |
| &nbsp 8 |&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | PRODUCTS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 9 |&nbsp&nbsp&nbsp INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | PRODUCTS_PK&nbsp&nbsp&nbsp |
---------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_fcc170b08cbcb825
Enabled: YES&nbsp&nbsp&nbsp&nbsp Fixed: NO&nbsp&nbsp&nbsp&nbsp&nbsp Accepted: YES&nbsp&nbsp&nbsp&nbsp Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 2361178149

------------------------------------------
| Id&nbsp | Operation&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | Name&nbsp&nbsp&nbsp&nbsp |
------------------------------------------
|&nbsp&nbsp 0 | SELECT STATEMENT&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 1 |&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 2 |&nbsp&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 3 |&nbsp&nbsp&nbsp PARTITION RANGE ALL| &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 4 |&nbsp&nbsp&nbsp&nbsp TABLE ACCESS FULL | SALES&nbsp&nbsp&nbsp |
|&nbsp&nbsp 5 |&nbsp&nbsp&nbsp TABLE ACCESS FULL&nbsp | TIMES&nbsp&nbsp&nbsp |
|&nbsp&nbsp 6 |&nbsp&nbsp TABLE ACCESS FULL&nbsp&nbsp | PRODUCTS |
------------------------------------------

--------------------------------------------------------------------------------
Plan name: SYS_SQL_PLAN_fcc170b0a62d0f4d
Enabled: YES&nbsp&nbsp&nbsp&nbsp Fixed: NO&nbsp&nbsp&nbsp&nbsp&nbsp Accepted: YES&nbsp&nbsp&nbsp&nbsp Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------

Plan hash value: 2787970893

----------------------------------------------------------------
| Id&nbsp | Operation&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | Name&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
----------------------------------------------------------------
|&nbsp&nbsp 0 | SELECT STATEMENT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 1 |&nbsp NESTED LOOPS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 2 |&nbsp&nbsp NESTED LOOPS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 3 |&nbsp&nbsp&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 4 |&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | PRODUCTS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 5 |&nbsp&nbsp&nbsp&nbsp&nbsp INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | PRODUCTS_PK&nbsp&nbsp&nbsp |
|&nbsp&nbsp 6 |&nbsp&nbsp&nbsp&nbsp PARTITION RANGE ALL&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 7 |&nbsp&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY LOCAL INDEX ROWID| SALES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 8 |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP CONVERSION TO ROWIDS&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 9 |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | SALES_PROD_BIX |
|&nbsp 10 |&nbsp&nbsp&nbsp INDEX UNIQUE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | TIME_PK&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp 11 |&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | TIMES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
----------------------------------------------------------------

72 rows selected.


Parameters

Two parameters allow you to control SPM. The first, optimizer_capture_sql_plan_baselines, which is FALSE by default, allows you to automatically capture plans. SPM will start managing every repeatable SQL statement that is executed and will create a plan history for it. The first plan that is captured will beautomatically accepted. Subsequent plans for these statements will not be accepted until they are evolved.

The second parameter, optimizer_use_sql_plan_baselines, is TRUE by default. It allows the SPM aware optimizer to use the SQL plan baseline if available when compiling a SQL statement. If you set this parameter to FALSE, the SPM aware optimizer will be disabled and you will get the regular cost-based optimizer which will select the best plan based on estimated cost.

SPM and SQL profiles

A SQL statement can have both a SQL profile and a SQL plan baseline. Such a case was described in Part 3 where we evolved a SQL plan baseline by accepting a SQL profile. In this case, the SPM aware optimizer will use both the SQL profile and the SQL plan baseline. The SQL profile contains additional information that helps the optimizer to accurately cost each accepted plan and select the best one. The SPM aware optimizer may choose a different accepted plan when a SQL profile is present than when it is not.

SPM and stored outlines

It is possible for a SQL statement to have a stored outline as well as a SQL plan baseline. If a stored outline exists for a SQL statement and is enabled for use, then the optimizer will use it, ignoring the SQL plan baseline. In other words, the stored outline trumps a SQL plan baseline. If you are using stored outlines, you can test SPM by creating SQL plan baselines and disabling the stored outlines. If you are satisfied with SPM, you can either drop the stored outlines or leave them disabled. If SPM doesn't work for you (and we would love to know why), you can re-enable the stored outlines.

If you are using stored outlines, be aware of their limitations:


  • You can only have one stored outline at a time for a given SQL statement. This may be fine in some cases, but a single plan is not necessarily the best when the statement is executed under varying conditions (e.g., bind values).
  • The second limitation is related to the first. Stored outlines do not allow for evolution. That is, even if a better plan exists, the stored outline will continue to be used, potentially degrading your system's performance. To get the better plan, you have to manually drop the current stored outline and generate a new one.
  • If an access path (e.g., an index) used in a stored outline is dropped or otherwise becomes unusable, the partial stored outline will continue to be used with the potential of a much worse plan.


One question that readers have is what we plan to do with the stored outlines feature. Here is the official word in Chapter 20 of Oracle's Performance Tuning Guide:

Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 11g Release 1 (11.1), stored outlines continue to function as in past releases. However, Oracle strongly recommends that you use SQL plan management for new applications. SQL plan management creates SQL plan baselines, which offer superior SQL performance and stability compared with stored outlines.

If you have existing stored outlines, consider migrating them to SQL plan baselines by using the LOAD_PLANS_FROM_CURSOR_CACHE or LOAD_PLANS_FROM_SQLSET procedure of the DBMS_SPM package. When the migration is complete, you should disable or remove the stored outlines.

SPM and adaptive cursor sharing

Adaptive cursor sharing (ACS) may generate multiple cursors for a given bind sensitive SQL statement if it is determined that a single plan is not optimal under all conditions. Each cursor is generated by forcing a hard parse of the statement. The optimizer will normally select the plan with the best cost upon each hard parse.

When you have a SQL plan baseline for a statement, the SPM aware optimizer will select the best accepted plan as the optimal plan. This also applies for the hard parse of a bind sensitive statement. There may be multiple accepted plans, each of which is optimal for different bind sets. With SPM and ACS enabled, the SPM aware optimizer will select the best plan for the current bind set.

Thus, if a hard parse occurs, the normal SPM plan selection algorithm is used regardless of whether a statement is bind sensitive.

Enterprise Manager

You can view SQL plan baselines and configure and manage most SPM tasks through the Enterprise Manager. The screenshots below show two of these tasks.

Setting init.ora parameters for SPM



Loading SQL plan baselines from cursor cache



Further Reading

More details about SPM are available in the Oracle documentation, especially Chapter 15 of the Performance Tuning Guide. There is also a whitepaper, and a paper published in the VLDB 2008 conference. The VLDB paper also has experimental results that show how SPM prevents performance regressions while simultaneously allowing better plans to be used.
Categories: DBA Blogs, Development

SQL Plan Management (Part 3 of 4): Evolving SQL Plan Baselines

Inside the Oracle Optimizer - Mon, 2009-01-26 12:33
In the example in Part 2, we saw that the optimizer used an accepted plan instead of a brand new plan. The statement has two plans in its plan history, but only one is accepted and thus in the SQL plan baseline:

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


SQL_TEXT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp PLAN_NAME&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825&nbsp YES NO
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
&nbsp and s.time_id = t.time_id
&nbsp and p.prod_id < :pid

select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d&nbsp YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
&nbsp and s.time_id = t.time_id
&nbsp and p.prod_id < :pid



Non-accepted plans can be verified by executing the evolve_sql_plan_baseline function. This function will execute the non-accepted plan and compare its performance to the best accepted plan. The execution is performed using the conditions (e.g., bind values, parameters, etc.) in effect at the time the non-accepted plan was added to the plan history. If the non-accepted plan's performance is better, the function will make it accepted, thus adding it to the SQL plan baseline. Let's see what happens when we execute this function:

SQL> var report clob;


SQL> exec :report := dbms_spm.evolve_sql_plan_baseline();

PL/SQL procedure successfully completed.

SQL> print :report

REPORT
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

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

Plan: SYS_SQL_PLAN_fcc170b08cbcb825
-----------------------------------
&nbsp Plan was verified: Time used .1 seconds.
&nbsp Passed performance criterion: Compound improvement ratio >= 10.13
&nbsp Plan was changed to an accepted plan.

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Baseline Plan&nbsp&nbsp&nbsp&nbsp&nbsp Test Plan&nbsp&nbsp&nbsp&nbsp Improv. Ratio
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp -------------&nbsp&nbsp&nbsp&nbsp&nbsp ---------&nbsp&nbsp&nbsp&nbsp -------------
&nbsp Execution Status:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp COMPLETE&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp COMPLETE
&nbsp Rows Processed:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 960&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 960
&nbsp Elapsed Time(ms):&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 19&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 15&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 1.27
&nbsp CPU Time(ms):&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 18&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 15&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 1.2
&nbsp Buffer Gets:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 1188&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 116&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 10.24
&nbsp Disk Reads:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 0&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 0
&nbsp Direct Writes:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 0&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 0
&nbsp Fetches:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 0&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 0
&nbsp Executions:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 1

-------------------------------------------------------------------------------
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Report Summary
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.



The plan verification report shows that the new plan's performance was better and so it was made accepted and became part of the SQL plan baseline. We can confirm it by looking in the dba_sql_plan_baselines view:

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


SQL_TEXT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp PLAN_NAME&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825&nbsp YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
&nbsp and s.time_id = t.time_id
&nbsp and p.prod_id < :pid

select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d&nbsp YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
&nbsp and s.time_id = t.time_id
&nbsp and p.prod_id < :pid



The SQL plan baseline now has two accepted plans: SYS_SQL_PLAN_fcc170b08cbcb825 is now accepted.

You can either execute the evolve_sql_plan_baseline() function manually or schedule it to run automatically in a maintenance window.

Another way of evolving a SQL plan baseline is to use the SQL Tuning Advisor. Instead of executing evolve_sql_plan_baseline, suppose we start from the original state where we have one accepted and one non-accepted plan:

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


SQL_TEXT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp PLAN_NAME&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825&nbsp YES NO
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
&nbsp and s.time_id = t.time_id
&nbsp and p.prod_id < :pid

select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d&nbsp YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
&nbsp and s.time_id = t.time_id
&nbsp and p.prod_id < :pid



You can execute the SQL Tuning Advisor on the cursor in the cursor cache:

SQL> var tname varchar2(30);


SQL> exec :tname := dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc');

PL/SQL procedure successfully completed.

SQL> exec dbms_sqltune.execute_tuning_task(task_name => :tname);

PL/SQL procedure successfully completed.

SQL> select dbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(:TNAME,'TEXT','BASIC')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name&nbsp&nbsp : TASK_505
Tuning Task Owner&nbsp : SH
Workload Type&nbsp&nbsp&nbsp&nbsp&nbsp : Single SQL Statement
Scope&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status&nbsp : COMPLETED
Started at&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp : 11/11/2008 16:43:12
Completed at&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp : 11/11/2008 16:43:13

-------------------------------------------------------------------------------
Schema Name: SH
SQL ID&nbsp&nbsp&nbsp&nbsp : bfbr3zrg9d5cc
SQL Text&nbsp&nbsp : select p.prod_name, s.amount_sold, t.calendar_year
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp from sales s, products p, times t
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp where s.prod_id = p.prod_id
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp and s.time_id = t.time_id
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp and p.prod_id < :pid

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- A potentially better execution plan was found for this statement.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 2787970893


----------------------------------------------------------------
| Id&nbsp | Operation&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | Name&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
----------------------------------------------------------------
| &nbsp 0 | SELECT STATEMENT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 1 |&nbsp NESTED LOOPS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 2 |&nbsp&nbsp NESTED LOOPS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 3 |&nbsp&nbsp&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 4 |&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | PRODUCTS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 5 |&nbsp&nbsp&nbsp&nbsp&nbsp INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | PRODUCTS_PK&nbsp&nbsp&nbsp |
| &nbsp 6 |&nbsp&nbsp&nbsp&nbsp PARTITION RANGE ALL&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 7 |&nbsp&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY LOCAL INDEX ROWID| SALES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 8 |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP CONVERSION TO ROWIDS&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
| &nbsp 9 |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | SALES_PROD_BIX |
|&nbsp 10 |&nbsp&nbsp&nbsp INDEX UNIQUE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | TIME_PK&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp 11 |&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | TIMES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
----------------------------------------------------------------

2- Original With Adjusted Cost
------------------------------
Plan hash value: 2787970893


----------------------------------------------------------------
| Id&nbsp | Operation&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | Name&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
----------------------------------------------------------------
|&nbsp&nbsp 0 | SELECT STATEMENT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 1 |&nbsp NESTED LOOPS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 2 |&nbsp&nbsp NESTED LOOPS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 3 |&nbsp&nbsp&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 4 |&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | PRODUCTS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 5 |&nbsp&nbsp&nbsp&nbsp&nbsp INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | PRODUCTS_PK&nbsp&nbsp&nbsp |
|&nbsp&nbsp 6 |&nbsp&nbsp&nbsp&nbsp PARTITION RANGE ALL&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 7 |&nbsp&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY LOCAL INDEX ROWID| SALES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 8 |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP CONVERSION TO ROWIDS&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 9 |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | SALES_PROD_BIX |
|&nbsp 10 |&nbsp&nbsp&nbsp INDEX UNIQUE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | TIME_PK&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp 11 |&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | TIMES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
----------------------------------------------------------------

3- Using SQL Profile
--------------------
Plan hash value: 2361178149


------------------------------------------
| Id&nbsp | Operation&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp | Name&nbsp&nbsp&nbsp&nbsp |
------------------------------------------
|&nbsp&nbsp 0 | SELECT STATEMENT&nbsp&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 1 |&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 2 |&nbsp&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 3 |&nbsp&nbsp&nbsp PARTITION RANGE ALL|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp |
|&nbsp&nbsp 4 |&nbsp&nbsp&nbsp&nbsp TABLE ACCESS FULL | SALES&nbsp&nbsp&nbsp |
|&nbsp&nbsp 5 |&nbsp&nbsp&nbsp TABLE ACCESS FULL&nbsp | TIMES&nbsp&nbsp&nbsp |
|&nbsp&nbsp 6 |&nbsp&nbsp TABLE ACCESS FULL&nbsp&nbsp | PRODUCTS |
------------------------------------------

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


SQL> exec dbms_sqltune.accept_sql_profile(task_name => :tname);

PL/SQL procedure successfully completed.

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

SQL_TEXT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp PLAN_NAME&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825&nbsp YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
&nbsp and s.time_id = t.time_id
&nbsp and p.prod_id < :pid

select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d&nbsp YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
&nbsp and s.time_id = t.time_id
&nbsp and p.prod_id < :pid

SQL> select sql_text, type, status from dba_sql_profiles;

SQL_TEXT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp TYPE&nbsp&nbsp&nbsp STATUS
---------------------------------------- ------- --------
select p.prod_name, s.amount_sold, t.cal MANUAL&nbsp ENABLED
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
&nbsp and s.time_id = t.time_id
&nbsp and p.prod_id < :pid



What we see here is that SQL Tuning Advisor found a tuned plan (that coincidentally happened to be the non-accepted plan in our plan history). When we accepted the recommended SQL profile, the SQL Tuning Advisor created a SQL profile and also changed the non-accepted plan to accepted status, thus evolving the SQL plan baseline to two plans.

Note that the SQL Tuning Advisor may also find a completely new tuned plan, one that is not in the plan history. If you then accept the recommended SQL profile, the SQL Tuning Advisor will create a SQL profile and also add the tuned plan to the SQL plan baseline.

Thus, you can evolve a SQL plan baseline either by executing the evolve_sql_plan_baseline function or by using the SQL Tuning Advisor. New and provably better plans will be added by either of these methods to the SQL plan baseline.

Categories: DBA Blogs, Development

SQL Plan Management (Part 2 of 4): SPM Aware Optimizer

Inside the Oracle Optimizer - Tue, 2009-01-20 11:59
(Keep sending your feedback and questions. We'll address them in Part 4.)

In Part 1, we saw how you can create SQL plan baselines. After you create a SQL plan baseline for a statement, subsequent executions of that statement will use the SQL plan baseline. From all the plans in the SQL plan baseline, the optimizer will select the one with the best cost in the current environment (including bind values, current statistics, parameters, etc.). The optimizer will also generate the best-cost plan that it would otherwise have used without a SQL plan baseline. However, this best-cost plan will not be used but instead added to the statement's plan history for later verification. In other words, the optimizer will use a known plan from the SQL plan baseline instead of a new and hitherto unknown plan. This guarantees no performance regression.

Let's see this plan selection process in action. First, we create a SQL plan baseline by enabling automatic plan capture and executing the query twice:

SQL> alter session set optimizer_capture_sql_plan_baselines = true;

Session altered.

SQL> var pid number
SQL> exec :pid := 100;

PL/SQL procedure successfully completed.

SQL> select p.prod_name, s.amount_sold, t.calendar_year
2&nbsp&nbsp&nbsp from sales s, products p, times t
3&nbsp&nbsp&nbsp where s.prod_id = p.prod_id
4&nbsp&nbsp&nbsp&nbsp&nbsp and s.time_id = t.time_id
5&nbsp&nbsp&nbsp&nbsp&nbsp and p.prod_id < :pid;

PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
9 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select p.prod_name, s.amount_sold, t.calendar_year from sales s,
products p, times t where s.prod_id = p.prod_id and s.time_id =
t.time_id and p.prod_id < :pid

Plan hash value: 2787970893

----------------------------------------------------------------
| Id&nbsp&nbsp| Operation&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| Name&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
----------------------------------------------------------------
|&nbsp&nbsp 0&nbsp| SELECT STATEMENT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 1&nbsp|&nbsp NESTED LOOPS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 2&nbsp|&nbsp&nbsp NESTED LOOPS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 3&nbsp|&nbsp&nbsp&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 4&nbsp|&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| PRODUCTS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 5&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| PRODUCTS_PK&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 6&nbsp|&nbsp&nbsp&nbsp&nbsp PARTITION RANGE ALL&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 7&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY LOCAL INDEX ROWID| SALES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 8&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP CONVERSION TO ROWIDS&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 9&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| SALES_PROD_BIX&nbsp|
|&nbsp 10&nbsp|&nbsp&nbsp&nbsp INDEX UNIQUE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| TIME_PK&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp 11&nbsp|&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| TIMES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
----------------------------------------------------------------

25 rows selected.

SQL> select p.prod_name, s.amount_sold, t.calendar_year
2&nbsp&nbsp&nbsp from sales s, products p, times t
3&nbsp&nbsp&nbsp where s.prod_id = p.prod_id
4&nbsp&nbsp&nbsp&nbsp&nbsp and s.time_id = t.time_id
5&nbsp&nbsp&nbsp&nbsp&nbsp and p.prod_id < :pid;

PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
9 rows selected.

SQL> alter session set optimizer_capture_sql_plan_baselines = false;

Session altered.

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

SQL_TEXT &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp PLAN_NAME &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d&nbsp YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid



We can see that a SQL plan baseline was created for the statement. Suppose the statement is hard parsed again (we do it here by flushing the shared pool). Let's turn off SQL plan management and execute the query with a different bind value:


SQL> exec :pid := 100000;

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.

SQL> alter session set optimizer_use_sql_plan_baselines = false;

Session altered.

SQL> select p.prod_name, s.amount_sold, t.calendar_year
2&nbsp&nbsp&nbsp from sales s, products p, times t
3&nbsp&nbsp&nbsp where s.prod_id = p.prod_id
4&nbsp&nbsp&nbsp&nbsp&nbsp and s.time_id = t.time_id
5&nbsp&nbsp&nbsp&nbsp&nbsp and p.prod_id < :pid;

PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
960 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select p.prod_name, s.amount_sold, t.calendar_year from sales s,
products p, times t where s.prod_id = p.prod_id and s.time_id = t.time_id and
p.prod_id < :pid

Plan hash value: 2361178149

------------------------------------------
| Id&nbsp&nbsp| Operation&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| Name&nbsp&nbsp&nbsp&nbsp&nbsp|
------------------------------------------
|&nbsp&nbsp 0&nbsp| SELECT STATEMENT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 1&nbsp|&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 2&nbsp|&nbsp&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 3&nbsp|&nbsp&nbsp&nbsp PARTITION RANGE ALL|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 4&nbsp|&nbsp&nbsp&nbsp&nbsp TABLE ACCESS FULL&nbsp| SALES&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 5&nbsp|&nbsp&nbsp&nbsp TABLE ACCESS FULL&nbsp&nbsp| TIMES&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 6&nbsp|&nbsp&nbsp TABLE ACCESS FULL&nbsp&nbsp&nbsp| PRODUCTS&nbsp|
------------------------------------------

20 rows selected.



We can see that the optimizer selected a different plan because the new bind value makes the predicate less selective. Let's turn SQL plan management back on and re-execute the query with the same bind value:

SQL> alter session set optimizer_use_sql_plan_baselines = true;

Session altered.

SQL> select p.prod_name, s.amount_sold, t.calendar_year
2&nbsp&nbsp&nbsp from sales s, products p, times t
3&nbsp&nbsp&nbsp where s.prod_id = p.prod_id
4&nbsp&nbsp&nbsp&nbsp&nbsp and s.time_id = t.time_id
5&nbsp&nbsp&nbsp&nbsp&nbsp and p.prod_id < :pid;

PROD_NAME AMOUNT_SOLD CALENDAR_YEAR
--------- ----------- -------------
...
960 rows selected.

SQL> select * from table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select p.prod_name, s.amount_sold, t.calendar_year from sales s,
products p, times t where s.prod_id = p.prod_id and s.time_id =
t.time_id and p.prod_id < :pid

Plan hash value: 2787970893

----------------------------------------------------------------
| Id&nbsp&nbsp| Operation&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| Name&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
----------------------------------------------------------------
|&nbsp&nbsp 0&nbsp| SELECT STATEMENT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 1&nbsp|&nbsp NESTED LOOPS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 2&nbsp|&nbsp&nbsp NESTED LOOPS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 3&nbsp|&nbsp&nbsp&nbsp HASH JOIN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 4&nbsp|&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| PRODUCTS&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 5&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| PRODUCTS_PK&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 6&nbsp|&nbsp&nbsp&nbsp&nbsp PARTITION RANGE ALL&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 7&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp TABLE ACCESS BY LOCAL INDEX ROWID| SALES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 8&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP CONVERSION TO ROWIDS&nbsp&nbsp&nbsp&nbsp&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp&nbsp 9&nbsp|&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp BITMAP INDEX RANGE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| SALES_PROD_BIX |
|&nbsp 10&nbsp|&nbsp&nbsp&nbsp INDEX UNIQUE SCAN&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| TIME_PK&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
|&nbsp 11&nbsp|&nbsp&nbsp TABLE ACCESS BY INDEX ROWID&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp| TIMES&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp|
----------------------------------------------------------------

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

29 rows selected.



The note at the bottom tells you that the optimizer is using the SQL plan baseline. In other words, we can see that the optimizer used an accepted plan in the SQL plan baseline in favor of a new plan. In fact, we can also check that the optimizer inserted the new plan into the statement's plan history:

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


SQL_TEXT&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp PLAN_NAME&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp ENA ACC
---------------------------------------- ------------------------------ --- ---
select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825&nbsp YES NO
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
&nbsp and s.time_id = t.time_id
&nbsp and p.prod_id < :pid

select p.prod_name, s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d&nbsp YES YES
endar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
&nbsp and s.time_id = t.time_id
&nbsp and p.prod_id < :pid



The 'NO' value for the accepted column implies that the new plan is in the plan history but is not available for use until it is verified to be a good plan. The optimizer will continue to use an accepted plan until new plans are verified and added to the SQL plan baseline. If there is more than one plan in the SQL plan baseline, the optimizer will use the one with the best cost under the then-current conditions (statistics, bind values, parameter settings and so on).

When you create a SQL plan baseline for a SQL statement, the SPM aware optimizer thus guarantees that no new plans will be used other than the ones in the SQL plan baseline. This prevents unexpected plan changes that sometimes lead to performance regressions.

Preventing new plans from being used is fine, but what if the new plans are in fact better? In Part 3, we will describe how new and improved plans are added to a SQL plan baseline.

Categories: DBA Blogs, Development

Silent upgrade troubles

Freek D’Hooge - Sun, 2009-01-18 17:08

Last week I was asked to write a little script to automate an upgrade of oracle client 9.2.0.1 to 9.2.0.8.
Reason for this was that we needed to update arround 1.300 clients to enable them to connect to a 10g database (we couldn’t install 10g clients because of other applications restricted the client version to 9i).

Ok, easy enough. Oracle allows you to automate installations and upgrades via response files and the response file for a client upgrade from 9.2.0.1 to 9.2.0.8 is very simple.
When I started testing the upgrade, I immediately spotted a first problem. The setup.exe (it was on windows xp) started a new console and then returned directly to the prompt in the original console. This would make it impossible to check the return codes to know if a upgrade was successful or not.

The upgrade itself finished without a problem, but at the end the following message appeared in the newly started console: “Press enter to exit”.
Huh!? This was supposed to be a “silent” install, meaning no interraction needed. But here it was, asking to press enter to exit.
And the documentation was not telling anything about it.
After some searching, I found that you can specify the “-noconsole” flag when starting the setup, which would surpress the new console and avoid the question to press enter.
You still would see the question in the logfiles, but the installation presumed you responded to it and finishes the upgrade.

This left me with the first problem: the prompt would still directly return while the upgrade was running in the background.
After some searching in the documentation I found a note stating that you need to modify the oraparam.ini file and change the BOOTSTRAP parameter from TRUE to FALSE.
Unfortunately this did not help. Yelling at it did either.

Then I found that in 10g, you had a “-waitforcompletion” flag you could set, that would do exactly what I needed. So I tried if it would work for the oui shipped in the 9.2.0.8 patchset.
At first, it didn’t, but then I found metalink note 293044.1 that said that the setup.exe in Disk1 and Disk1/install where not the same and that the one in Disk1/install should be used for the “-waitforcompletion” flag.
At last it worked.

For those interested, here is the full command I used to start the silent upgrade:

start /wait C:\oracle\patches\9.2.0.8\Disk1\install\setup.exe -silent -noconsole -waitforcompletion -responsefile c:\oracle\patches\9.2.0.8\patchset.rsp -paramfile c:\oracle\patches\9.2.0.8\oraparam.ini

—————————-

Thanks to Geert for the yelling link :)


Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs