Search

OakieTags

Who's online

There are currently 0 users and 24 guests online.

Recent comments

Oakies Blog Aggregator

Two things I learned this week...

I often say "I learn something new about Oracle every day".  It really is true - there is so much to know about it, it is hard to keep up sometimes.

Here are the two new things I learned - the first is regarding temporary tablespaces.  In the past - when people have asked "how can I shrink my temporary tablespace" I've said "create a new one that is smaller, alter your database/users to use this new one by default, wait a bit, drop the old one".  Actually I usually said first - "don't, it'll just grow again" but some people really wanted to make it smaller.

Now, there is an easier way:

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_3002.htm#SQLRF53578

Using alter tablespace temp shrink space .

The second thing is just a little sqlplus quirk that I probably knew at one point but totally forgot.  People run into problems with &'s in sqlplus all of the time as sqlplus tries to substitute in for an &variable.  So, if they try to select '&hello world' from dual - they'll get:

ops$tkyte%ORA11GR2> select '&hello world' from dual;
Enter value for hello: 
old   1: select '&hello world' from dual
new   1: select ' world' from dual


'WORLD
------
 world


ops$tkyte%ORA11GR2> 

One solution is to "set define off" to disable the substitution (or set define to some other character).  Another oft quoted solution is to use chr(38) - select chr(38)||'hello world' from dual.  I never liked that one personally.  
Today - I was shown another way
ops$tkyte%ORA11GR2> select '&' || 'hello world' from dual;

'&'||'HELLOW
------------
&hello world

ops$tkyte%ORA11GR2>
just concatenate '&' to the string, sqlplus doesn't touch that one!  I like that better than chr(38) (but a little less than set define off....)

Website Server Move (wrap-up)…

Followers of this blog, Twitter or Google+ can’t help but have noticed me moaning about my hosting provider (Fasthosts). My site was moved on to a new server (by accident) in December:

Since then I’ve been having assorted rants on G+ and Twitter because the site has been up and down a lot.

I started looking at new (UK-based) hosting providers, but the reviews of all of them are pretty terrible. Part of this is because they are all pretty dodgy, having to cut so many corners to make a profit, and part of it is because people (like me) only tend to review things when they are really bad. So here I was, caught between a rock and a hard place. Stick with Fasthosts (who I’ve been with for 11 years), or make a move?

Whilst looking for a new hosting company, I continued to hassle Fasthosts, opening tickets for every failure and providing all sorts of tracing, screen shots. Eventually, they offered to switch me to another server. On Thursday night (about midnight UK time), the server move was initiated. I spent until 03:45 snagging the move, then went to bed. I woke up to a message from Arjen Visser from Dbvisit.com saying there was an issue with the site. By the time I picked up the message things were working again, so I filed this as a “bedding in” issue. :) Later that day, there was one 15 minute downtime, which I logged and was given a proper explanation for by the support team (the first so far!!). Since then, as far as I know, the site has bee working fine. Fingers crossed.

I pay yearly, so I’m paid up until December, but I’m going to continue to investigate other hosting options, just in case this all kicks off again. If anyone has any long-term experience of a UK-based hosting provider I am interested to know. One of my friends recently moved to 1and1.co.uk, who have pretty terrible reviews, but so far he’s having a good experience. I’ve done a bit of system admin for him and so far there has been no drama.

Cheers

Tim…




Obsolete cursors

In the previous post I wrote about strangely behaving V$SQL. For some reason there were duplicate rows leading to wrong results issue when running DBMS_XPLAN.DISPLAY_CURSOR for a particular child cursor. I tried to reproduce the issue using simple test case – and it was reproduced.

Here it is. I’m starting to execute a simple query against DUAL while constantly changing optimizer environment, forcing Oracle to build a new child cursor for each execution:

alter session set workarea_size_policy=manual;
col curr_date new_value curr_date
select to_char(sysdate, 'HH24MISS') curr_date from dual;
set define on verify off

begin
    for i in 1..100 loop
        execute immediate 'alter session set sort_area_size=' || to_char(100000 + i*1024);
        execute immediate 'select /* &&curr_date */ count(*) from dual';
    end loop;
end;
/

col sql_id new_value sql_id
select sql_id, address from v$sqlarea where sql_text = 'select /* &&curr_date */ count(*) from dual';
clear columns
select count(*) from v$sql_shared_cursor where sql_id = '&&sql_id';
@unshared &&sql_id

As expected, I’ve got 100 child cursors and 99 of them were created due to OPTIMIZER_MISMATCH:

SQL> select sql_id, address from v$sqlarea where sql_text = 'select /* &&curr_date */ count(*) from dual';

SQL_ID        ADDRESS
------------- ----------------
8ugq0vhq0z7pb 000007FF5D45C0B0

SQL> clear columns
columns cleared
SQL> select count(*) from v$sql_shared_cursor where sql_id = '&&sql_id';

            COUNT(*)
--------------------
                 100

SQL> @unshared &&sql_id

SQL_ID        NONSHARED_REASON                          COUNT(*)
------------- ----------------------------- --------------------
8ugq0vhq0z7pb OPTIMIZER_MISMATCH                              99

Now let’s continue and see what will happen:

begin
    for i in 101..250 loop
        execute immediate 'alter session set sort_area_size=' || to_char(100000 + i*1024);
        execute immediate 'select /* &&curr_date */ count(*) from dual';
    end loop;
end;
/

select count(*) from v$sql_shared_cursor where sql_id = '&&sql_id';

select sum(cnt), avg(cnt), min(child_number), max(child_number)
  from (select child_number, count(*) cnt
          from v$sql
         where sql_id = '&&sql_id'
         group by child_number);

select address, count(*) from v$sql where sql_id='&&sql_id' group by address;
select sql_id, address from v$sqlarea where sql_text = 'select /* &&curr_date */ count(*) from dual';
@unshared &&sql_id
select * from table(dbms_xplan.display_cursor('&&sql_id', 1));
SQL> select count(*) from v$sql_shared_cursor where sql_id = '&&sql_id';

            COUNT(*)
--------------------
                 250

SQL>
SQL> select sum(cnt), avg(cnt), min(child_number), max(child_number)
  2    from (select child_number, count(*) cnt
  3            from v$sql
  4           where sql_id = '&&sql_id'
  5           group by child_number);

            SUM(CNT)             AVG(CNT)    MIN(CHILD_NUMBER)    MAX(CHILD_NUMBER)
-------------------- -------------------- -------------------- --------------------
                 250                  2.5                    0                   99

SQL>
SQL> select address, count(*) from v$sql where sql_id='&&sql_id' group by address;

ADDRESS                      COUNT(*)
---------------- --------------------
000007FF5D1E18A8                   50
000007FF5D45C0B0                  100
000007FF5D5BA268                  100

SQL> select sql_id, address from v$sqlarea where sql_text = 'select /* &&curr_date */ count(*) from dual';

SQL_ID        ADDRESS
------------- ----------------
8ugq0vhq0z7pb 000007FF5D1E18A8

SQL> @unshared &&sql_id

SQL_ID        NONSHARED_REASON                          COUNT(*)
------------- ----------------------------- --------------------
8ugq0vhq0z7pb OPTIMIZER_MISMATCH                             249

SQL> select * from table(dbms_xplan.display_cursor('&&sql_id', 1));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID  8ugq0vhq0z7pb, child number 1
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01422: exact fetch returns more than requested number of rows

Plan hash value: 3910148636

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

SQL_ID  8ugq0vhq0z7pb, child number 1
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01422: exact fetch returns more than requested number of rows

Plan hash value: 3910148636

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

SQL_ID  8ugq0vhq0z7pb, child number 1
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01422: exact fetch returns more than requested number of rows

Plan hash value: 3910148636

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   0 | SELECT STATEMENT |      |       |     2 (100)|          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   1 |  SORT AGGREGATE  |      |     1 |            |          |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


114 rows selected.

Bingo. After the point of 100 child cursors per parent, Oracle builds a new parent (V$SQL.ADDRESS holds its address) cursor, marking old parent and child cursors as obsolete. V$SQLAREA handles the situation well, but V$SQL doesn’t and that’s clearly a bug. The threshold point of when to build a new parent cursor is 100 by default and is controlled with a new hidden parameter _cursor_obsolete_threshold. I’ve tested setting this parameter to 150 and it worked as expected. Note that this bug-fix-improvement-again-bugs 10187168 claimed to be included into Patch Set Update 11.2.0.2.2 (and higher). I’ve tested 11.2.0.2.3 & 11.2.0.2.5 and it seems the improvement is not enabled by default and they act the old, pre-11.2.0.3 way; most likely the bug-fix have to be enabled explicitly with event 106001 in the 11.2.0.2 PSUs. I haven’t tested it though.
PS. If you have time and desire, please file a bug to Oracle.

Filed under: Oracle Tagged: 11.2.0.3, bug, dynamic views, shared cursors

Indexed Organized Tables – An Introduction to IOT Secondary Indexes (A Second Face)

Man, its been ages since I had free time to update the blog, what with birthday parties to organise, Roger Water concerts to attend and Radiohead concerts in the planning !! OK, time to take an initial look at Secondary Indexes for Index Organized Tables (IOTs). If the IOT needs to be accessed via the Primary Key (PK) column(s), [...]

No more excuse not to use large pages in Linux

This is a very quick post to remind users of the importance of large pages. Before 11.2.0.2 it was a little more elaborate process to determine the number of large pages to be set aside for Oracle, but now it’s so simple there isn’t any more excuse not to use large pages. If you have > 16G SGA and lots of processes, check the impact of large pages in /proc/meminfo before and after. By implementing large pages at a former company I reduced the page tables overhead from 20G (60G sga, processes set to 1000, most often around 800 sessions connected) to a fraction of that number.

Here is why you can’t hide anymore:

Starting ORACLE instance (normal)
****************** Large Pages Information *****************

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)
Large Pages unused system wide = 0 (0 KB) (alloc incr 64 MB)
Large Pages configured system wide = 0 (0 KB)
Large Page size = 2048 KB

RECOMMENDATION:
 Total Shared Global Region size is 20 GB. For optimal performance,
 prior to the next instance restart increase the number
 of unused Large Pages by atleast 10241 2048 KB Large Pages (20 GB)
 system wide to get 100% of the Shared
 Global Region allocated with Large pages
***********************************************************

Now just set vm.nr_hugepages (plus memlock in /etc/security/limits.conf) and off you go!

Oracle Linux 5.8 and UDEV issues…

I just did an update from Oracle Linux 5.7 to 5.8 on one of my VirtualBox RAC installations and things are not looking to clever at the moment. After a reboot, the ASM instances and therefore the database instances wouldn’t restart. A quick look showed the ASM disks were not visible. On this installation I was using UDEV, rather than ASMLib. In checking the UDEV rules I noticed the scsi_id command on OL5.8 doesn’t report an ID for partitions on disks, only the disks themselves. For example, on OL5.7 I get this,

# /sbin/scsi_id -g -u -s /block/sdb/sdb1
SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_
#

On OL5.8 I get this,

# /sbin/scsi_id -g -u -s /block/sdb/sdb1
#

If I run it against the disk, rather than the partition it works fine.

This has literally just happened, so I’ve done no further investigation, but I thought it was worth putting out there in case anyone was about to start an OS update on something they cared about. :)

At this point I’m not discounting that I’ve screwed up somewhere. My next plan is to install three clean VMs (OL 5.6, 5.7 and 5.8) and check the output of scsi_id on each of them. If that turns out OK, then I’ve screwed something else and you can probably ignore this post. I might not get to try it out until tomorrow. Either way, I’ll update this post with the results of that test.

Cheers

Tim…

Update 1: It’s definitely changed. See the following.

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.6 (Tikanga)
# /sbin/scsi_id -g -u -s /block/sda/sda1
SATA_VBOX_HARDDISK_VB54dff07f-931ce4d7_
#

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.7 (Tikanga)
# /sbin/scsi_id -g -u -s /block/sda/sda1
SATA_VBOX_HARDDISK_VBx180d717-f896e661_
#

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.8 (Tikanga)
# /sbin/scsi_id -g -u -s /block/sda/sda1
#

Update 2: As John Sobecki correctly pointed out in the comments, the title of the post is misleading. UDEV is not at fault here. The problem is the “/sbin/scsi_id” command is behaving differently, which is making my rules useless. The UDEV issue is the symptom, not the cause. The post is clearly focusing on the scsi_id issue, but I’ve picked a pretty bad title to go with it. :)

Update 3: John Sobecki pointed me at “[block] fail SCSI passthrough ioctls on partition devices CVE-2011-4127″, a mainline kernel security fix that seems to be the cause of this. It affects all new kernels which include this change (RHEL5/6, UEK etc). Oracle are testing the impact of this. Initially ASMLib and OCFS seem unaffected.

Update 4: MOS Note 1438604.1 (currently in review) contains more information about this issue. ASMLib and OCFS are unaffected by CVE-2011-4127, so ASMLib should probably be used in preference to UDEV with newer kernels.




Quick SQLDeveloper Hint — Dial0gInput

The other day I was attempting to debug a SQL statement which was doing a REGEXP_REPLACE and the comments said something like “remove all zero’s from the string”.

However, in looking at the code, it looked like it was removing all capital O’s from the string instead of 0′s — see even here within WordPress I can’t tell the difference between an O (the letter) and a 0 (the number).

At first I thought it a coding mistake until I tried to type both of them into a window within SQL Developer — nope, they looked the same. Must be a font issue.

Fonts can be changed in SQL Developer under the Preferences / Code Editor / Fonts selection — you may want to limit choices to fixed-width fonts by checking “Display Only Fixed-Width Fonts”. The default on my system was DialogInput — which appears to have the O/0 problem. I ended up choosing Consolas 12pt and exiting back out to the editor.

Only to see a horribly grainy looking font.  Yuck!

Back to the Preferences — somewhat hidden under Preferences / Code Editor / Display is an item for “Enable Text Anti-Aliasing” — check it.

Much, much better.

I think that preference item should be moved to the Fonts category, but at least I was able to find it — and my eyes feel a lot better.

Oh yeah, now I can tell zeros from o’s again — now on to those pesky 1′s and l’s … :-)

UPDATE

You may also want to adjust the font used for Printing to match your Editor selection — to do so, navigate to Preferences / Code Editor / Printing as well as Printing HTML to change the fonts to match your Editor selection.

AIM SIG and my talk about Enterprise Manager 12c

Yesterday I presented at UKOUG’s Availability, Infrastructure and Management Special Interest Group (hey, say this 3 times in a row, quickly!) about Oracle Enterprise Manager 12c and my experience with it. As my good fried Piet de Visser pointed out I had way too much to say for the 45 minute slot allocated. But then Piet always tells me that. Sadly he is also often right :) That’s why I like seeing him during my talks!

In summary I would have liked to do a different presentation, and that’s for two reasons: 1) I overran and 2) I haven’t managed to show the patching part which is hugely interesting, at least to me.

Now here’s the reason for the blog post. I haven’t done online seminars yet, and was wondering if people were interested in a 1-1.5 hour UKOUG-like presentation from myself, broadcast via Goto Meeting or similar to an audience. Would that be of interest? The topics to be covered are:

  • Thinking about the installation of OEM 12c including HA and other deployment options
  • Walking through the installation using print screens (it takes too long and I would bore you showing a slow moving progress bar)
  • Demo time, i.e. logging on to the system and showing a few things

For the interactive part I’d

  • show the new user interface
  • walk through the agent push
  • demonstrate how to add a target
  • and finally guide you through the patching process

I prefer this to be interactive (technology permitting), with a timescale of 1 to 1.5 hours and a Q&A session at the end. I will try to set something up if there is sufficient interest.

WARNING & disclaimer

I’m not an expert in OEM 12c! I installed it, played with it and can show you what I know.

AllThingsOracle.com

Just a short note that as already announced last week the second part of the post on how to control Dynamic Sampling activity has been published on AllThingsOracle.com.

You can read there about the (not so much known) DYNAMIC_SAMPLING_EST_CDN hint, the new Dynamic Sampling auto adjustment feature introduced in release 11.2 and other interesting details.

Displaying SQL Baseline Plans

Since I’m on vacation and not “really” working, I thought I might have time to write up a quick blog post. The idea for this one was triggered by one of Maria Colgan’s presentations at Hotsos last week. Maria was talking about SQL Plan Management and Baselines and somehow got me thinking about the DBMS_XPLAN option to display plans for Baselines. This is a pretty neat feature that allows you to the see the plan associated with a Baseline (well sort of).

The 11.2 documentation (Oracle® Database PL/SQL Packages and Types Reference) says this about the DISPLAY_SQL_PLAN_BASELINE function:

This procedure uses plan information stored in the plan baseline to explain and display the plans.It is possible that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan. A mismatch between stored plan_id and generated plan_id means that it is a non-reproducible plan. Such a plan is deemed invalid and is bypassed by the optimizer during SQL compilation.

But what does that mean? Well in short it means that Baselines don’t store plans, they store hints that when fed to the optimizer will hopefully cause it to come up with the desired plan. Baselines also store a plan_hash_value so it’s possible to tell whether the hints worked or not. Baselines do not actually store all the steps of a plan. So if that’s the case, then it’s obviously not possible for the display_sql_plan_baseline function to show the plan if the optimizer can’t reproduce it for some reason. When the doc’s say “it is possible that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan”, that’s what they are talking about. I decided to create a test case to see what happens when the generated plan can’t match the original. Here’s the basic idea:

  1. run a statement that uses an index and check the plan
  2. create a Baseline on the statement using the index (using my create_baseline.sql script)
  3. check the hints stored with the baseline (using my baselines_hints.sql script)
  4. run the statement again and check the real plan to see that the Baseline was used
  5. use the display_sql_plan_baseline function to show the Baseline plan
  6. make the index invisible (thus rendering the Baseline plan non-reproducible)
  7. execute the statement again and check the real plan
  8. use the display_sql_plan_baseline function to show the Baseline plan

So here’s the test:

 
SYS@dbm1> @avgskewi
 
AVG(PK_COL)
-----------
 
 
Elapsed: 00:00:00.02
SYS@dbm1> select * from table(dbms_xplan.display_cursor(null,null,'LAST'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    33 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    32 |   352 |    33   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    33 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=136133)
 
 
20 rows selected.
 
Elapsed: 00:00:00.03
SYS@dbm1>
SYS@dbm1> -- Create the Baseline
SYS@dbm1>
SYS@dbm1> @create_baseline
Enter value for sql_id: 7s0b9ygcrj77u
Enter value for plan_hash_value: 3723858078
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (ID_sqlid_planhashvalue): 
 
Baseline SQLID_7S0B9YGCRJ77U_3723858078 created.
 
Elapsed: 00:00:00.08
SYS@dbm1>
SYS@dbm1> -- Check Baseline Hints
SYS@dbm1>
SYS@dbm1> @baseline_hints
Enter value for baseline_plan_name: SQLID_7S0B9YGCRJ77U_3723858078
 
OUTLINE_HINTS
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "SKEW"@"SEL$1" ("SKEW"."COL1"))
 
6 rows selected.
 
Elapsed: 00:00:00.04
SYS@dbm1> @avgskewi
 
AVG(PK_COL)
-----------
 
 
1 row selected.
 
Elapsed: 00:00:00.01
SYS@dbm1> /
 
AVG(PK_COL)
-----------
 
 
1 row selected.
 
Elapsed: 00:00:00.01
SYS@dbm1>
SYS@dbm1> -- Check to make sure statement is using the Baseline
SYS@dbm1>
SYS@dbm1> select * from table(dbms_xplan.display_cursor(null,null,'LAST'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7s0b9ygcrj77u, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 23489
 
Plan hash value: 3723858078
 
-----------------------------------------------------------
| Id  | Operation                    | Name      | E-Rows |
-----------------------------------------------------------
|   0 | SELECT STATEMENT             |           |        |
|   1 |  SORT AGGREGATE              |           |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |     35 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |     37 |
-----------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=23489)
 
Note
-----
   - SQL plan baseline SQLID_7S0B9YGCRJ77U_3723858078 used for this statement
 
27 rows selected.
 
Elapsed: 00:00:00.05
SYS@dbm1>
SYS@dbm1> -- Use display_sql_plan_baseline to see Baseline Plan
SYS@dbm1>
SYS@dbm1> select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle','&plan_name','typical'));
Enter value for sql_handle: SQL_1e2d7159fc8f7496
Enter value for plan_name: SQLID_7S0B9YGCRJ77U_3723858078
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
SQL handle: SQL_1e2d7159fc8f7496
SQL text: select avg(pk_col) from kso.skew where col1 = 23489
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQLID_7S0B9YGCRJ77U_3723858078         Plan id: 1416105523
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    11 |    36   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |           |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    35 |   385 |    36   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    37 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=23489)
 
26 rows selected.
 
Elapsed: 00:00:00.05
SYS@dbm1>
SYS@dbm1> -- Hide the index and rerun the statement
SYS@dbm1>
SYS@dbm1> alter index kso.skew_col1 invisible;
 
Index altered.
 
Elapsed: 00:00:00.01
SYS@dbm1> @avgskewi
 
AVG(PK_COL)
-----------
 
 
1 row selected.
 
Elapsed: 00:00:01.14
SYS@dbm1> /
 
AVG(PK_COL)
-----------
 
 
1 row selected.
 
Elapsed: 00:00:01.12
SYS@dbm1>
SYS@dbm1> -- Check the actual plan and see that the Baseline is no longer used
SYS@dbm1>
SYS@dbm1> select * from table(dbms_xplan.display_cursor(null,null,'LAST'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7s0b9ygcrj77u, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 23489
 
Plan hash value: 568322376
 
----------------------------------------------------
| Id  | Operation                  | Name | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT           |      |        |
|   1 |  SORT AGGREGATE            |      |      1 |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |     35 |
----------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1"=23489)
       filter("COL1"=23489)
 
 
20 rows selected.
 
Elapsed: 00:00:00.02
SYS@dbm1>
SYS@dbm1> -- Use display_sql_plan_baseline to see Baseline Plan now
SYS@dbm1>
SYS@dbm1> select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle','&plan_name','typical'));
Enter value for sql_handle: SQL_1e2d7159fc8f7496
Enter value for plan_name: SQLID_7S0B9YGCRJ77U_3723858078
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
SQL handle: SQL_1e2d7159fc8f7496
SQL text: select avg(pk_col) from kso.skew where col1 = 23489
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQLID_7S0B9YGCRJ77U_3723858078         Plan id: 1416105523
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
 
Plan hash value: 568322376
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    11 | 44513   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    35 |   385 | 44513   (2)| 00:00:02 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1"=23489)
       filter("COL1"=23489)
 
26 rows selected.
 
Elapsed: 00:00:00.04

|
So in the last step you can see that the display_sql_plan_baseline function does not actually show the plan that was associated with the baseline. It can’t because the plan is not stored. So the optimizer must attempt to reproduce the plan with the hints and if the plan is not reproducible, it spits out some other plan. Note that the plan_id in the output still matches the desired plan even though the optimizer was unable to reproduce this plan.

Note: After I wrote this up I realized that Coskan Gundogar had already blogged about it here (pretty thoroughly I might add). So please see his post as well. By the way, I do agree with his point that when a plan is not reproducible the display_sql_plan_baseline function should probably just throw an error.