Duplicate indexes ?

I don’t think this is likely to happen on a production system (until 12c) – but look what you can do if you try hard enough:

  1  select
  2     index_name, column_name from user_ind_columns
  3  where
  4     table_name = 'T1'
  5  order by
  6*    index_name , column_position
SQL> /

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
T1_I1                N1
                     V1

T1_I2                N1
                     V1


4 rows selected.

That’s a straight cut-n-paste from an Oracle 11.1.0.7 SQL*Plus session. (You can tell I typed it in real time because I missed the return before the FROM, and couldn’t be bothered to go back and do it again ;) )

Source Control

You may recall that I spent some time with the developers at the redgate offices in Cambridge (UK) a little while ago, looking at their Source Control for Oracle package. The product is about to go live, with a launch date of 12th March.

Because of the help I’ve given them they’ve offered my readers the chance of winning one of two 5-user licences for the product – provided I devise a strategy for picking the recipients.

So, to make it easy, all you have to do is persuade me that you really need or really deserve a copy. Write up a short description in the comments of the worst problem you’ve had to face because you didn’t have a decent source control system, or the  best use you think you could make of a source control system for Oracle.

I’ll get the redgate developers to read what you wrote, and they will be the final judges of the two most interesting, or possibly most horrifying, or maybe even the most entertaining, cases.

Entries to be in by 23:59 GMT on 11th March.

Update

The product has launched early !

You can find out more and see screenshots on this web page.

And there’s an online demo of the tool on March 14 at 16:00 GMT (17:00 CET / 11:00 EST / 10:00 CST / 08:00 PST), where you will be able to ask any questions. If you’re interesting in seeing it you’ll have to register at this “go to meeting” URL.

The closing date for entries is still 11th March, 23:59 GMT.

Free Space

Question – How can you have a single file in a single tablespace showing multiple free extents when there are no objects using any space in that file ? For example, from an 11.1.0.7 database:

SQL> select
  2          *
  3  from    user_free_space
  4  where
  5          tablespace_name = 'TEST_8K'
  6  order by
  7          file_id, block_id
  8  ;

TABLESPACE_N    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ---------- ---------- ---------- ---------- ------------
TEST_8K               3        128    1048576        128            3
TEST_8K               3        256    1048576        128            3
TEST_8K               3        384    1048576        128            3
TEST_8K               3        512  130023424      15872            3

4 rows selected.

The answer in this case is simple – here’s what I did just before running my query:

SQL> create table t1(n1 number);

Table created.

SQL> create table t2(n1 number);

Table created.

SQL> create table t3(n1 number);

Table created.

SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

SQL> drop table t3;

Table dropped.

SQL>

This test is on 11.1.0.7; test_8k is my default tablespace, uses an 8KB block size (did you spot the clue) with 1MB uniform extents. The segments created immediately and I haven’t purged my recyclebin. Because I’ve dropped the tables Oracle includes their space in the “free space” views, but because I need to be able to flash them back into existence the segments can’t be coalesced into the adjacent free space, and they will also be reported in dba_segments.

Here’s a harder one – there are NO objects in this tablespace, and nothing hiding in the recyclebin:

SQL> select
  2          *
  3  from    user_free_space
  4  where
  5          tablespace_name = 'TEST_2K'
  6  order by
  7          file_id, block_id
  8  ;

TABLESPACE_N    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ---------- ---------- ---------- ---------- ------------
TEST_2K               7        512   58720256      28672            7
TEST_2K               7      29184   58720256      28672            7
TEST_2K               7      57856   58720256      28672            7
TEST_2K               7      86528   32505856      15872            7

And here’s the statement I executed (as SYS) just before I ran the query – so no chance that there’s anything hidden in the file:

create
        tablespace test_2k
        datafile 'C:\ORACLE\ORADATA\d11g\d11g\test_2k.dbf'
        SIZE 200M reuse
        blocksize 2k
        extent management local
        uniform size 4k
        segment space management manual
;

Update: 26th Feb

I’m sitting in Munich airport and boarding starts in 10 minutes, so just enough time to give an answer. I’ll start with a block dump of block 3 of the data file.

Block dump from disk:
buffer tsn: 22 rdba: 0x01c00003 (7/3)
scn: 0x0b86.06d63ae4 seq: 0x01 flg: 0x04 tail: 0x3ae41e01
frmt: 0x02 chkval: 0x4e8a type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0C112200 to 0x0C112A00
C112200 0000621E 01C00003 06D63AE4 04010B86  [.b.......:......]
C112210 00004E8A 00000007 00000200 00000000  [.N..............]
C112220 00000000 00003800 00000000 00000000  [.....8..........]
C112230 00000000 00000000 00000000 00000000  [................]
        Repeat 123 times
C1129F0 00000000 00000000 00000000 3AE41E01  [...............:]
File Space Bitmap Block:
BitMap Control:
RelFno: 7, BeginBlock: 512, Flag: 0, First: 0, Free: 14336
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
...
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

This is a locally managed tablespace, so the third block of the file is the first bitmap space management block for the tablespace. The tablespace uses a 2KB block size (so the amount of space in the block for the bit map is slight under 2KB) and a 4KB uniform extent size (so each bit in the map represents 2 blocks/4KB in the tablespace).

As you can see from line 15, the first bit represents the extent starting at block 512, and there are 14,336 bits available (1,792 bytes), so the bitmap in the next block would start at block 512 + 2 * 14,336 = 512 * 28,672 = 29,184. This lines up exactly with the first chunk of free space reported in dba_free_space above.

As one of the comments indicated – the code that populates x$ktfbfe is probably called once for each bitmap space management block, and it doesn’t seem to bother trying to “coalesce” two free space fragments identified by adjacent bitmap blocks.

Deadlock Detection

By some strange coincidence, the “London Bus” effect perhaps, there have been three posts on the OTN database forum in the last couple of days relating to deadlocks; and they have prompted me to indulge in a little rant about the myth of Oracle and deadlock detection; it’s the one that goes:

“Oracle detects and resolves deadlocks automatically.”

Oracle may detect deadlocks automatically, but it doesn’t resolve them, it simply reports them (by raising error ORA-00060 and rolling back one statement) then leaves the deadlocked sessions stuck until the session that received the report resolves the problem or an external agent resolves the problem.

Consider the following example (which, I have to admit, I wrote without access to a live instance):

Session 1
delete from t1 where id between 1 and 1000000;
1000000 rows deleted

                                                   Session 2
                                                   update jobs set status = 'STARTING' where id = 99;
                                                   1 row updated

Session 1
update jobs set status = 'FINISHED' where id = 99;
-- session 1 is now waiting on session 2

                                                   Session 2
                                                   delete from t1 where id between 1 and 1000000
                                                   -- session 2 is now waiting on session 1

Session 1 (some time within the next 3 seconds)
ORA-00060: deadlock detected

At this point most of the applications that I’ve seen would crash session 1, resulting in an implicit rollback of the million row update (worst case I’ve seen: session 2 ran for 10 seconds and resulted in session 1 crashing and rolling back for 3.5 hours); some applications would log “Unexpected Oracle error” to the front-end and retry the most recent action (at which point Session 2 would receive an ORA-00060 error and the two sessions would see-saw back and fore every 3 seconds until someone noticed what was going on).

In what way has the deadlock been “resolved” ?

In this specific case I think I’d probably want a supervisor (person or program) to log and kill session 2 and allow session 1 to retry its second update – and then I’d want to find out why this sequence of events had happened at all.

Deadlocks are NOT resolved automatically by Oracle, they are merely reported so that the client code can decide how to resolve them.

Transactions 2

Here’s a little follow-on from Friday’s posting. I’ll start it off as a quiz, and follow up tomorrow with an explanation of the results (though someone will probably have given the correct solution by then anyway).

I have a simple heap table t1(id number(6,0), n1 number, v1 varchar2(10), padding varchar2(100)). The primary key is the id column, and the table holds 3,000 rows where id takes the values from 1 to 3,000. There are no other indexes. (I’d show you the code, but I don’t want to make it too easy to run the code, I want you to try to work it out in your heads).

I run the following pl/sql block.

begin
	--
	--	Going to fail on primary key constraint
	--
	for i in 1..200 loop
		begin
			insert into t1 values(50,i,'x','x');
			commit;
		exception
			when others then null;
		end;
	end loop;

	--
	--	Explicit rollback
	--
	for i in 1..40 loop
		begin
			insert into t1 values(4000 + i,i,'x','x');
			rollback;
		end;
	end loop;

	--
	--	Explicit commit
	--
	for i in 1..50 loop
		begin
			insert into t1 values(4500 + i,i,'x','x');
			commit;
		end;
	end loop;

	--
	--	Rolling back to savepoint
	--
	for i in 1..70 loop
		begin
			savepoint A;
			insert into t1 values(4600 + i,i,'x','x');
			rollback to savepoint A;
		end;
	end loop;

end;
/

If (from another session) you take an AWR (or Statspack) snapshot before and after running the block, what will you see for the statistics

user commits
user rollbacks
transaction rollbacks
rollback changed – undo records applied

and, for a bonus point, what will the value of “Rollback per transaction %” be ?

Note – since the snapshots are global, your results will be affected by other work on the system; in particular I noticed that simply taking two AWR snapshots in a row on the instance I used resulted in 8 user commits.

 

Update

And the answers – cut from an AWR report – are:

Statistic                                     Total     per Second     per Trans
-------------------------------- ------------------ -------------- -------------
rollback changes - undo records                 420           25.2           4.3
transaction rollbacks                           240            4.7           2.5
user commits                                     58            1.1           0.6
user rollbacks                                   40            0.8           0.4

 Rollback per transaction %:   40.82       Rows per Sort:   122.70

The 40 “user rollbacks” come from the loop with the explicit rollback. At the same time the 40 rollbacks introduced 80 “rollback changes – undo records applied” – one for the table index and one for the table for each call to rollback. Our “user rollbacks” have been “real” rollbacks, so they’ve introduced 40 transaction rollbacks at the same time.

The 50 “user commits” come from the loop with the explicit commit – except that the picture is slightly blurred by the fact that simply running the AWR snapshot introduced a few extra commits.

The 40.82% is 40 / (40 + 58) expressed as a percentage.

The “rollback to savepoint” calls haven’t contributed to the user rollbacks – even though, in this case, they have rolled back 40 transaction starts, and a check of various other statistics would show a TX enqueue being requested and released, and transactions rotating through the undo segments. Despite this clearing of the TX locks, these rollbacks to savepoint don’t count towards transaction rollbacks; however, the 70 “rollback to savepoint” calls have introduced a further 140 “rollback changes – undo records applied” (again, one for the table and one for the index for each call).

Finally the failed attempts to insert duplicate keys have, in these circumstances, introduced 200 transaction rollbacks (though not user rollbacks – we didn’t ask for them explicitly). They are also responsible for the final 200 “rollback changes – undo records applied” that we need to account for. The changes, very specifically, are the changes to the table – Oracle does actually have to insert the row into the table before trying to insert the index entry (after all, the index entry needs to know the rowid), and so it has to reverse that insert when it discovers that the relevant entry already exists in the primary key index. The requirement to insert and then rollback is one of the reasons why it is often better for “batch merge by pl/sql loop” to try an update then insert is sql%rowcount is zero rather than trying to insert, capture the exception, and then update; it’s also a good reason for creating the unique indexes on a table before creating the non-unique indexes – Oracle maintains indexes in the order they were created so you want it to find the duplicate key error as soon as possible to minimise the amount of change and rollback.

If you want to experiment further, there are a couple of slightly different variations of the code that exhibit interesting variations in results. There are also other events that can cause transaction rollbacks to appear without user rollbacks being invoked.

Transactions

It’s very easy to get a lot of information from an AWR (or Statspack) report – provided you remember what all the numbers represent. From time to time I find that someone asks me a question about some statistic and my mind goes completely blank about the exact interpretation; but fortunately it’s always possible to cross check because so many of the statistics are cross-linked. Here’s an example of a brief mental block I ran into a few days ago – I thought I knew the answer, but realised that I wasn’t 100% sure that my memory was correct:

In this Load Profile (for an AWR report of 60.25 minutes), what does that Transactions figure actually represent ?

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             57,567.09             12,028.39
              Logical reads:             48,043.83             10,038.54
              Block changes:                314.07                 65.62
             Physical reads:                667.70                139.51
            Physical writes:                 46.25                  9.66
                 User calls:                619.33                129.41
                     Parses:                505.67                105.66
                Hard parses:                 36.94                  7.72
                      Sorts:                313.05                 65.41
                     Logons:                  0.56                  0.12
                   Executes:              1,165.42                243.51
               Transactions:                  4.79

  % Blocks changed per Read:    0.65    Recursive Call %:    95.18
 Rollback per transaction %:   24.95       Rows per Sort:    25.09

And, while we’re at it, what does the “Rollback per transaction %” actually mean – and what, if anything, can we infer from the value ?

Since we’re looking at transactions, it’s a fairly safe bet that the figure is something to do with commits – but is it counting only committed transactions, or does it included rolled back transactions [philosophical question - is a change that's never committed really a transaction, since it "never happened" as far as the rest of the world is concerned?]. Fortunately we can look at the Intance Activity Statistics to check.

First, though, lets convert the “Per Second” figures into an absolute value by multiplying by the duration in seconds (3,615) of the report. Allowing for rounding errors we’re looking for a value between ceiling(4.785 *3615) and floor(4.795 * 3615), i.e. between 17,298 and 17,338.  Let’s also keep in mind that the “Rollback per transaction %” is very close to 25%.

Here, then, are a couple of useful figures from the Instance Activity:

Statistic                                     Total     per Second     per Trans
-------------------------------- ------------------ -------------- -------------
user commits                                 12,986            3.6           0.8
user rollbacks                                4,316            1.2           0.3

Almost immediately you can see that user rollbacks are roughly one third of user commits (I promise I didn’t massage these number), which means the rollback percentage is just (user rollbacks / (user commits + user rollbacks)). As a further sanity check, 4,316 + 12,986 = 17,302 which falls nicely into our required range. Ta-da, job done: “Transactions” is the sum of user commits and user rollbacks (as a first approximation – but is it the whole answer).

Of course, I picked a fairly extreme example from my AWR library to make a point – which means you might now be asking whether all those rollbacks pose some sort of threat. How serious are they ? Luckily there are a couple more statistics that tell us:

Statistic                                     Total     per Second     per Trans
-------------------------------- ------------------ -------------- -------------
rollback changes - undo records                 222            0.1           0.0
transaction rollbacks                            59            0.0           0.0

The name of the first statistic has been trimmed a little in this text report, the full name is: “rolllback changes – undo records applied”. As you can see, we’ve only applied 222 undo records in our 4,316 rollback calls, so we’re not really rolling anything back (most of the time). Moreoever, the “transaction rollbacks” corroborates this observation – we have only attempted to rollback 59 “real” (data-changing) transactions. Most of those rollbacks are probably the default “rollback after every call” that some web application servers make.

As a closing thought – if you’re responsible for several different systems, it’s convenient to keep a couple of ”reference” AWR or Statspack reports from busy, normal, and quiet periods for each system; that way, if you use the figures from one system to work out the meaning of some derived value you can use the figures from another system to check if your hypothesis is correct.

Footnote: there are two other statistics that include the text “undo records applied” (not that you’ll see those words in the textual AWR report), but they both relate to creating read-consistent copies of blocks.

Footnote 2: Is anyone getting worried by my “first approximation” comment – and has anyone started wondering if there may be more to transaction rollbacks that user rollbacks, and if so where they fit into the arithmetic ?  To be continued.

 

STS, OFE and SPM

That’s SQL Tuning Sets, optimizer_features_enable, and SQL Plan Management.

There’s a recent post on OTN describing an issue when using SQL Tuning Sets to enforce plan stability when upgrading from 10.2.0.3 to 11.2.0.3 – it doesn’t always work. Here’s a very simple model to demonstrate the type of thing that can happen (the tables are cloned from a completely different demo, so don’t ask why I picked the data they hold):

create table t1
as
select
	trunc((rownum-1)/15)	n1,
	trunc((rownum-1)/15)	n2,
	rpad(rownum,180)	v1
from all_objects
where rownum <= 3000
;

create table t2
as
select
	mod(rownum,200)		n1,
	mod(rownum,200)		n2,
	rpad(rownum,180)	v1
from all_objects
where rownum <= 3000
;

create index t1_i1 on t1(n1);
create index t2_i1 on t2(n1);

execute	dbms_stats.gather_table_stats(user,'t1');
execute	dbms_stats.gather_table_stats(user,'t2');

alter session set optimizer_features_enable = '10.2.0.3';

explain plan for
select
	/*+ ordered use_nl(t1) index(t1) */
	t2.n1, t1.n2
from
	t2,t1
where
	t2.n2 = 45
and	t2.n1 = t1.n1
;

set pagesize 60
set linesize 132
set trimspool on

select * from table(dbms_xplan.display(null,null,'outline'));

You’ll notice I’ve included a directive to set the optimizer_features_enable back to 10.2.0.3. If I run this test on both 10.2.0.3 (real) and 11.2.0.3 (with ofe set) I get the same plans but slightly different outline data.

From 10.2.0.3
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   225 |  3600 |    35   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |       |   225 |  3600 |    35   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL        | T2    |    15 |   120 |     5   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$1" "T1"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N1"))
      FULL(@"SEL$1" "T2"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

From 11.2.0.3
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   225 |  3600 |    54   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |       |   225 |  3600 |    54   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL        | T2    |    15 |   120 |    24   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      NLJ_PREFETCH(@"SEL$1" "T1"@"SEL$1")
      USE_NL(@"SEL$1" "T1"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N1"))
      FULL(@"SEL$1" "T2"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Apart from the appearance of the db_version() hint in 11.2.0.3 the most important difference in the outline data is the hint nlj_prefetch() hint. 11g introduced a new mechanism for nested loop joins called NLJ Batching, at the same time introducing two new hints to allow the optimizer to specify which mechanism a plan should use, prefetch (nlj_prefetch()) or batching (nlj_batching()). Since 10g only does prefetching it doesn’t have (or need) a hint to specify the mechanism.

The outline section data from a plan is basically what Oracle stores as an SQL Plan Baseline – so if I use the approved method to turn the 10g outline data above into the 11g SQL Plan Baseline what’s going to happen to the execution plan when I run the query in the default 11g environment ? It’s easy to demonstrate (at least in this case) by simply cutting and pasting the entire 10g outline into the original SQL statement and generating its plan under 11g; here’s the result:

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |   225 |  3600 |    54   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |       |   225 |  3600 |    54   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | T2    |    15 |   120 |    24   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| T1    |    15 |   120 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | T1_I1 |    15 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$1" "T1"@"SEL$1")
      LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."N1"))
      FULL(@"SEL$1" "T2"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.3')
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

The plan has changed. I was slightly surprised to see in this case that the optimizer used neithor NLJ prefetching, nor NLJ batching, it went all the way back to the traditional nested loop mechanism.

If I had actually captured the original outline hints as an SQL Baseline the optimizer would have found the baseline in the data dictionary, generated this new plan from it, discovered that the plan hash value for this plan didn’t match the plan hash value for the stored plan, and re-optimized the query from scratch - potentially producing a totally different execution plan.

In my example 10g and 11g both wanted to use the nlj_prefetch mechanism when pushed into the nested loop join. 10g, of course, doesn’t have a relevant hint, so I got lucky that 11g wanted to do what 10g had done. In the case of the OP on the OTN forum 11g had decided that it preferred nlj_batching over nlj_prefetch when  attempting to apply the baseline, so the plan hash values didn’t match and the optimizer became free to choose a completely different plan.

Without looking very carefully through all the hints available to 11g I can’t decide whether there are other similar cases to worry about – but if you see 11g ignoring SQL Plan Baselines that have been generated from 10g, then look for operations where 11g has two options (and perhaps two hints) for performing a given operation where 10g has only one option, and perhaps that’s where the problem lies.

Footnotes:

In the case of the OP’s example, a possible workaround could start by setting the hidden parameter “_nlj_batching_enabled”=0; obviously this shouldn’t be done on a production system without approval of Oracle Support, and it’s never a desirable strategy to change a global parameter to fix a local problem – so I’d prefer to set the parameter in a session and generate a new SQL Plan Baseline that would then (probably) include either the nlj_prefetch() hint, or maybe it would turn out to be the no_nlj_batching() hint.

To see what plan (and hints) the SQL Plan Baseline would have generated, the OP enabled SPM tracing using the new event mechanism, in this case:

alter session set events 'trace[RDBMS.SQL_Plan_Management.*]';

-- run, or explain the query here

alter session set events 'trace[RDBMS.SQL_Plan_Management.*] off'; 

Optimisation ?

I was at a client site recently where one of the end-users seemed to have discovered a cunning strategy for optimising a critical SQL statement. His problem was that his query screen times out after 2 minutes, so any query he runs has to complete in less than two minutes or he doesn’t see the results. Unfortunately he had a particular query which took nearly 32 minutes from cold to complete – partly because it’s a seven-table join using ANSI OUTER joins, against tables ranging through the 10s of millions of rows and gigabytes of data – the (necessary) tablescan of the table that had to be first in the join order took 70 seconds alone.

But our intrepid user seems to have made an important discovery and engineered a solution to his performance problem. I think he’s noticed that when you run a query twice in a row the second execution is often faster than the first. I can’t think of any other reason why the same person would run the same query roughly every four minutes between 8:00 and 9:00 am every morning (and then do the same again around 5:00 in the afternoon).

Looking at the SQL Monitoring screen around 10:00 the first day I was on-site I noticed this query with a very pretty graphic effect of gradually shrinking blue bars as 32 minutes of I/O turned into 2 minutes of CPU over the course of 8 consecutive executions which reported run times something like:  32 minutes, 25 minutes, 18 minutes, 12 minutes, 6 minutes, 4 minutes, 2.1 minutes, 2 minutes.

It’s lucky (for that user) that the db_cache_size is 60GB. On the other hand this machine is one of those Solaris boxes that likes to pretend that it’s got 128 CPUs when really it’s only 16 cores with 8 lightweight threads per core – you don’t want anyone running a query that uses 2 solid CPU minute on one of those boxes because it’s taking out 1/16th of your CPU availability, while reporting a load of 1/128 of your CPUs.

Footnote: the query can be optimised (properly) – it accessed roughly 100M rows of data to return roughly 300 rows (with no aggregation), so we just need to do a little bit of work on precise access paths.

Delphix

If you’re a regular follower or my blog you may recall Kyle Hailey and the joint webinar we did nearly two years ago on “Visual SQL Tuning” covering an approach I’ve written about in the past and a product that he developed at Embarcadero to automate the work that I’d been doing by hand and eye.

Kyle has now moved on to Delphix, and has become involved with another really interesting piece of technology – database virtualization. How do you supply a terabyte sized database to five different development teams without using up 5TB of disc space ? Create an operating environment that keeps one master copy of the database while maintaining a set of (small) private files for each team that hold private copies of the blocks that have been changed by that team – and that’s just one feature of the product.

The product is sufficiently interesting (plus I have a healthy regard for Kyle’s opinions) that I’ve accepted an invitation to go over to California for a few days next month to experiment with it, see what it can do, try to stress it a bit and so on. The people at Delphix are so confident that I’ll be impressed that they’re going to let me do this and then write up a blog telling you how things went.

Have a browse around their documentation and if you’re interested add a suggestion to the comment telling me what you’d like me to test, and how, and I’ll see if I can fit it into my timetable (no promises – but if you come up with interesting ideas I’ll see what I can do).

Losing it

The example I gave last week showing how a SORT operation in an execution plan might include the work of resolving function calls in your SQL and might, therefore, be reporting much higher resource utilisation than expected reminded me of some problems I’ve had with gaps in execution plans in the past. So I thought I’d give a little demonstration of the way in which the completeness of execution plans can develop over time.

We’ll start with the same two tables I had in last week’s demo.

create table t1
as
select
	rownum 			id,
	lpad(rownum,200)	padding
from	all_objects
where	rownum <= 2500 ; create table t2 as select	* from t1 ; begin 	dbms_stats.gather_table_stats( 		ownname		 => user,
		tabname		 =>'T1',
		method_opt	 => 'for all columns size 1'
	);

	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T2',
		method_opt	 => 'for all columns size 1'
	);

end;
/

Once we have the tables and stats, we can start running a few very simple queries – I have a sequence of three queries to demonstrate, showing the lovely progression of history:

select
	(select max(t1.id) from t1 where t1.id <= t2.id) id
from	t2
;

select	id
from	t1
minus
select
	(select max(t1.id) from t1 where t1.id <= t2.id) id
from	t2
;

select
	case mod(id,2)
		when 1 then (select max(t1.id) from t1 where t1.id <= t2.id)  		when 0 then (select max(t1.id) from t1 where t1.id >= t2.id)
	end id
from	t2
;

The first, and simplest query, demonstrates 8i failing to produce the right plan – but 9i handles it correctly:

Plan from 8.1.7.4
  Id  Par  Pos  Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------
   0         3        SELECT STATEMENT (all_rows)     Cost (3,2500,10000)
   1    0    1    2     TABLE ACCESS (analyzed)  TEST_USER T2 (full)  Cost (3,2500,10000)

Plan from 9.2.0.8
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  2500 | 10000 |    12   (9)|
|   1 |  SORT AGGREGATE      |             |     1 |     4 |            |
|*  2 |   TABLE ACCESS FULL  | T1          |   125 |   500 |    12   (9)|
|   3 |  TABLE ACCESS FULL   | T2          |  2500 | 10000 |    12   (9)|
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."ID"<=:B1)

The second query, including a section of SQL that 9i handled properly, shows an incomplete plan in 9i and 10g, but gets a complete plan in 11g. (The row estimate of 125 in line 5 of the second plan is the usual 5% estimate for a range-based predicate against an unknown value: 5% of 2,500 is 125).

Plan from 9.2.0.8 / 10.2.0.5
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  2500 | 20000 |    26  (58)|
|   1 |  MINUS               |             |       |       |            |
|   2 |   SORT UNIQUE        |             |  2500 | 10000 |    13  (16)|
|   3 |    TABLE ACCESS FULL | T1          |  2500 | 10000 |    12   (9)|
|   4 |   SORT UNIQUE        |             |  2500 | 10000 |    13  (16)|
|   5 |    TABLE ACCESS FULL | T2          |  2500 | 10000 |    12   (9)|
-------------------------------------------------------------------------

Plan from 11.1.0.7
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  2500 | 20000 |    25  (56)| 00:00:01 |
|   1 |  MINUS              |      |       |       |            |          |
|   2 |   SORT UNIQUE       |      |  2500 | 10000 |    12   (9)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |  2500 | 10000 |    11   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  5 |    TABLE ACCESS FULL| T1   |   125 |   500 |    11   (0)| 00:00:01 |
|   6 |   SORT UNIQUE       |      |  2500 | 10000 |    12   (9)| 00:00:01 |
|   7 |    TABLE ACCESS FULL| T2   |  2500 | 10000 |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("T1"."ID"<=:B1)

But if we start to hide subqueries inside CASE operators (decodes would do the same), 11g starts to get it a little wrong, as shown by the third example. The depth column of the plan_table can be calculated incorrectly, in this case giving the impression that line 6 is a descendent of line 5. (Funnily enough, 9i gets this example right because the code to display the plan from the plan_table uses the old “connect by” query on id and parent_id rather than the calculated depth column.

Plan from 11.1.0.7 / 11.2.0.3
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |  2500 | 20000 |    25  (56)| 00:00:01 |
|   1 |  MINUS                |      |       |       |            |          |
|   2 |   SORT UNIQUE         |      |  2500 | 10000 |    12   (9)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T1   |  2500 | 10000 |    11   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE      |      |     1 |     4 |            |          |
|*  5 |    TABLE ACCESS FULL  | T1   |   125 |   500 |    11   (0)| 00:00:01 |
|   6 |     SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  7 |      TABLE ACCESS FULL| T1   |   125 |   500 |    11   (0)| 00:00:01 |
|   8 |   SORT UNIQUE         |      |  2500 | 10000 |    12   (9)| 00:00:01 |
|   9 |    TABLE ACCESS FULL  | T2   |  2500 | 10000 |    11   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("T1"."ID"<=:B1)    7 - filter("T1"."ID">=:B1)

You might note, by the way, that in all these examples the estimated COST of the plans is very misleading. The optimizer has made no attempt to allow for the cost of the repeated execution of the scalar subqueries. This doesn’t really matter, of course, for very simple queries like this, but it could make a big difference if something of this sort were embedded in the middle of a more complex statement.

Next time you have to unravel the execution plan for a complex query with scalar subqueries floating around the place – there may be bits of the plan that you can’t see, or that aren’t doing quite what you think you’re being told. When interpretation gets tough make sure you track through the query and the plan to see if the plan is likely to be a complete and truthful representation of what the statement has to do.

Sorting

Here’s a little quirk of execution plans that came up recently on the Oak Table network. If you call a function in a query, and do some sorting with the results, where does the work of calling the function get reported in the execution plan if you trace the query or look at the in-memory rowsource execution stats. Let’s take a look at a simple example:

create table t1
as
select
	rownum 			id,
	lpad(rownum,200)	padding
from	all_objects
where	rownum <= 2500
;

create table t2
as
select	* from t1
;

-- collect stats

create or replace function f (i_target in number)
return number
as
	m_target	number;
begin
	select max(id) into m_target from t1 where id <= i_target;
	return m_target;
end;
/

So I’ve got two tables with exactly the same data and a function that will do a full tablescan of t1 (which is going to be 75 blocks) and return the original input (assuming the input was between 1 and 2,500). Here’s the query I want to run (and it will return no rows), followed by the base execution plan.

select	/*+ gather_plan_statistics */
	id
from 	t1
minus
select
	f(id)
from	t2
;

select * from table(dbms_xplan.display_cursor(null,null,'basic +rows'));

--------------------------------------------
| Id  | Operation           | Name | Rows  |
--------------------------------------------
|   0 | SELECT STATEMENT    |      |       |
|   1 |  MINUS              |      |       |
|   2 |   SORT UNIQUE       |      |  2500 |
|   3 |    TABLE ACCESS FULL| T1   |  2500 |
|   4 |   SORT UNIQUE       |      |  2500 |
|   5 |    TABLE ACCESS FULL| T2   |  2500 |
--------------------------------------------

Because of the call to f() in the select against t2, I’m going to call the function 2,500 times and incur a load of buffer reads (2,500 * 75) doing so. Where will those buffer gets and the attendant CPU appear in the plan ? This example is by no means an exhaustive analysis of all the possible options when you include functions in your select list, but in this particular case the function isn’t called until we run the SORT UNIQUE operation at line 4:

select * from table(dbms_xplan.display_cursor(null,null,'allstats last projection'));

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.54 |     187K|       |       |          |
|   1 |  MINUS              |      |      1 |        |      0 |00:00:00.54 |     187K|       |       |          |
|   2 |   SORT UNIQUE       |      |      1 |   2500 |   2500 |00:00:00.01 |      75 | 70656 | 70656 |63488  (0)|
|   3 |    TABLE ACCESS FULL| T1   |      1 |   2500 |   2500 |00:00:00.01 |      75 |       |       |          |
|   4 |   SORT UNIQUE       |      |      1 |   2500 |   2500 |00:00:00.54 |     187K| 70656 | 70656 |63488  (0)|
|   5 |    TABLE ACCESS FULL| T2   |      1 |   2500 |   2500 |00:00:00.01 |      75 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - STRDEF[22]
   2 - (#keys=1) "ID"[NUMBER,22]
   3 - "ID"[NUMBER,22]
   4 - (#keys=1) "F"("ID")[22]
   5 - "ID"[NUMBER,22]

The 187K buffer gets for the function calls (and any other resources) appear at line 4 of the plan, when you might have expected them to appear in line5 as part of the tablescan. This observation can be confirmed by checking the column projection information – the output from line 5 is the “ID”, the output from line 4 includes “F”(“ID”). I don’t often look at the projection information, but it’s nice to know that sometimes it can give you some ideas of what’s going on when the row source execution stats don’t seem to be what you were expecting.

Footnote: The same sort of effect appears with scalar subqueries in the select lists, although in any  recent versions of Oracle the plan for the scalar subquery appears in the main plan (although sometimes in a counter-intuitive position) and will give you a much better idea of where and why the work is being done.

 

Compression

Red Gate have asked me to write a few articles for their Oracle site, so I’ve sent them a short series on “traditional” compression in Oracle – which means I won’t be mentioning Exadata hybrid columnar compression (HCC a.k.a. EHCC). There will be five articles, published at the rate of one per week starting Tuesday (15th Jan). I’ll be supplying links for them as they are published.

• Basic Compression – the mechanics
• Basic Compression – the implications
• OLTP compression
• Index Compression – the mechanics
• Index compression – the implications

dbms_xplan bug

Here’s a very long post (which is mainly an example) demonstrating a little bug in the “explain plan” functionality. It’s a variation of a bug which I thought had been fixed in 11g, but it still appears in some cases. Take a look at this execution plan, which comes from explaining “select * from dba_tab_cols” – the bit I want to emphasise is in lines 1 to 10:

------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |            |     8 | 21704 |       |  2387   (1)| 00:00:29 |
|   1 |  TABLE ACCESS BY INDEX ROWID    | OBJ$       |   480 | 14400 |       |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN              | I_OBJ1     |   192 |       |       |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID   | OBJ$       |   480 | 14400 |       |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN             | I_OBJ1     |   192 |       |       |     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID  | OBJ$       |   480 | 14400 |       |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN            | I_OBJ1     |   192 |       |       |     2   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID | OBJ$       |   480 | 14400 |       |     3   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN           | I_OBJ1     |   192 |       |       |     2   (0)| 00:00:01 |
|   9 |      TABLE ACCESS BY INDEX ROWID| OBJ$       |   480 | 14400 |       |     3   (0)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN          | I_OBJ1     |   192 |       |       |     2   (0)| 00:00:01 |
|* 11 |  HASH JOIN                      |            |   480 |  1929K|       |     5  (20)| 00:00:01 |
|* 12 |   INDEX RANGE SCAN              | I_OBJ1     |   480 | 12480 |       |     2   (0)| 00:00:01 |
|  13 |   TABLE ACCESS FULL             | USER$      |    59 |   117K|       |     2   (0)| 00:00:01 |
|  14 |  NESTED LOOPS OUTER             |            |     1 |  2084 |       |     3   (0)| 00:00:01 |
|  15 |   TABLE ACCESS BY INDEX ROWID   | COL$       |     1 |    56 |       |     2   (0)| 00:00:01 |
|* 16 |    INDEX UNIQUE SCAN            | I_COL3     |     1 |       |       |     1   (0)| 00:00:01 |
|  17 |   TABLE ACCESS BY INDEX ROWID   | ATTRCOL$   |     1 |  2028 |       |     1   (0)| 00:00:01 |
|* 18 |    INDEX UNIQUE SCAN            | I_ATTRCOL1 |     1 |       |       |     0   (0)| 00:00:01 |
|  19 |    TABLE ACCESS BY INDEX ROWID  | ATTRCOL$   |     1 |  2028 |       |     2   (0)| 00:00:01 |
|* 20 |     INDEX UNIQUE SCAN           | I_ATTRCOL1 |     1 |       |       |     1   (0)| 00:00:01 |
|* 21 |  FILTER                         |            |       |       |       |            |          |
|* 22 |   HASH JOIN RIGHT OUTER         |            | 72962 |   188M|       |  2387   (1)| 00:00:29 |
|  23 |    TABLE ACCESS FULL            | USER$      |    59 |   118K|       |     3   (0)| 00:00:01 |
|* 24 |    HASH JOIN RIGHT OUTER        |            | 72962 |    45M|       |  2384   (1)| 00:00:29 |
|* 25 |     TABLE ACCESS FULL           | OBJ$       |   583 | 30899 |       |   205   (0)| 00:00:03 |
|* 26 |     HASH JOIN RIGHT OUTER       |            | 72962 |    41M|       |  2178   (1)| 00:00:27 |
|  27 |      TABLE ACCESS FULL          | COLTYPE$   |  2886 |   174K|       |   275   (0)| 00:00:04 |
|* 28 |      HASH JOIN                  |            | 72962 |    37M|       |  1902   (1)| 00:00:23 |
|  29 |       TABLE ACCESS FULL         | USER$      |    59 |  1770 |       |     3   (0)| 00:00:01 |
|* 30 |       HASH JOIN                 |            | 72962 |    35M|       |  1899   (1)| 00:00:23 |
|  31 |        INDEX FAST FULL SCAN     | I_USER2    |    59 |  3068 |       |     2   (0)| 00:00:01 |
|* 32 |        HASH JOIN RIGHT OUTER    |            | 72962 |    31M|  2976K|  1896   (1)| 00:00:23 |
|  33 |         TABLE ACCESS FULL       | HIST_HEAD$ | 16280 |  2782K|       |    70   (0)| 00:00:01 |
|  34 |         NESTED LOOPS            |            | 72962 |    19M|       |   660   (1)| 00:00:08 |
|  35 |          TABLE ACCESS FULL      | OBJ$       | 47960 |  3840K|       |   205   (0)| 00:00:03 |
|  36 |          TABLE ACCESS CLUSTER   | COL$       |     2 |   404 |       |     1   (0)| 00:00:01 |
|* 37 |           INDEX UNIQUE SCAN     | I_OBJ#     |     1 |       |       |     0   (0)| 00:00:01 |
|* 38 |   TABLE ACCESS CLUSTER          | TAB$       |     1 |    26 |       |     2   (0)| 00:00:01 |
|* 39 |    INDEX UNIQUE SCAN            | I_OBJ#     |     1 |       |       |     1   (0)| 00:00:01 |
|  40 |   NESTED LOOPS                  |            |     1 |    78 |       |     3   (0)| 00:00:01 |
|* 41 |    INDEX RANGE SCAN             | I_OBJ4     |     1 |    39 |       |     2   (0)| 00:00:01 |
|* 42 |    INDEX RANGE SCAN             | I_USER2    |     1 |    39 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("O"."OBJ#"=:B1)
   4 - access("O"."OBJ#"=:B1)
   6 - access("O"."OBJ#"=:B1)
   8 - access("O"."OBJ#"=:B1)
  10 - access("O"."OBJ#"=:B1)
  11 - access("O"."OWNER#"="USER#")
  12 - access("O"."OBJ#"=:B1)
  16 - access("CL"."OBJ#"=:B1 AND "CL"."INTCOL#"=:B2-1)
  18 - access("RC"."OBJ#"(+)=:B1 AND "RC"."INTCOL#"(+)=:B2-1)
       filter("CL"."INTCOL#"="RC"."INTCOL#"(+))
  20 - access("TC"."OBJ#"=:B1 AND "TC"."INTCOL#"=:B2)
  21 - filter((("O"."TYPE#"=3 OR "O"."TYPE#"=4) OR "O"."TYPE#"=2 AND  NOT EXISTS (SELECT 0
              FROM "SYS"."TAB$" "T" WHERE "T"."OBJ#"=:B1 AND (BITAND("T"."PROPERTY",512)=512 OR
              BITAND("T"."PROPERTY",8192)=8192))) AND ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7
              AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND
              "O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND
              "O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR
              "O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR
              "O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR
              "O"."TYPE#"=87) AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND
              "U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
              ion_id')) OR  EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."TYPE#"=88
              AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND "O2"."OWNER#"="U2"."USER#" AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
  22 - access("OT"."OWNER#"="USER#"(+))
  24 - access("AC"."TOID"="OT"."OID$"(+))
  25 - filter("OT"."TYPE#"(+)=13)
  26 - access("C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+))
  28 - access("O"."SPARE3"="U"."USER#")
  30 - access("O"."OWNER#"="U"."USER#")
  32 - access("C"."OBJ#"="H"."OBJ#"(+) AND "C"."INTCOL#"="H"."INTCOL#"(+))
  37 - access("O"."OBJ#"="C"."OBJ#")
  38 - filter(BITAND("T"."PROPERTY",512)=512 OR BITAND("T"."PROPERTY",8192)=8192)
  39 - access("T"."OBJ#"=:B1)
  41 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
  42 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
       filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))

If you try reading this execution plan using the rule of thumb (ROT) “the first thing that happens is near the top and over to the right” you’ll run into problems because (a) that’s not a good guideline and (b) there are a number of scalar subqueries in the select list and some filter subqueries in the where clause confusing the issue. Here’s the definition of the view dba_tab_cols:

select u.name, o.name,
       c.name,
       decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                       2, decode(c.scale, null,
                                 decode(c.precision#, null, 'NUMBER', 'FLOAT'),
                                 'NUMBER'),
                       8, 'LONG',
                       9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                       12, 'DATE',
                       23, 'RAW', 24, 'LONG RAW',
                       58, nvl2(ac.synobj#, (select o.name from obj$ o
                                where o.obj#=ac.synobj#), ot.name),
                       69, 'ROWID',
                       96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                       100, 'BINARY_FLOAT',
                       101, 'BINARY_DOUBLE',
                       105, 'MLSLABEL',
                       106, 'MLSLABEL',
                       111, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                       113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                       121, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       122, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       123, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       178, 'TIME(' ||c.scale|| ')',
                       179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
                       180, 'TIMESTAMP(' ||c.scale|| ')',
                       181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
                       231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
                       182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
                       183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
                             c.scale || ')',
                       208, 'UROWID',
                       'UNDEFINED'),
       decode(c.type#, 111, 'REF'),
       nvl2(ac.synobj#, (select u.name from "_BASE_USER" u, obj$ o
                         where o.owner#=u.user# and o.obj#=ac.synobj#),
            ut.name),
       c.length, c.precision#, c.scale,
       decode(sign(c.null$),-1,'D', 0, 'Y', 'N'),
       decode(c.col#, 0, to_number(null), c.col#), c.deflength,
       c.default$, h.distcnt,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.lowval
            else null
       end,
       case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
            then h.hival
            else null
       end,
       h.density, h.null_cnt,
       case when nvl(h.distcnt,0) = 0 then h.distcnt
            when h.row_cnt = 0 then 1
	    when (h.bucket_cnt > 255
                  or
                  (h.bucket_cnt > h.distcnt
                   and h.row_cnt = h.distcnt
                   and h.density*h.bucket_cnt < 1))
                then h.row_cnt
            else h.bucket_cnt
       end,
       h.timestamp#, h.sample_size,
       decode(c.charsetform, 1, 'CHAR_CS',
                             2, 'NCHAR_CS',
                             3, NLS_CHARSET_NAME(c.charsetid),
                             4, 'ARG:'||c.charsetid),
       decode(c.charsetid, 0, to_number(NULL),
                           nls_charset_decl_len(c.length, c.charsetid)),
       decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
       decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
       h.avgcln,
       c.spare3,
       decode(c.type#, 1, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
                      96, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
                      null),
       decode(bitand(ac.flags, 128), 128, 'YES', 'NO'),
       decode(o.status, 1, decode(bitand(ac.flags, 256), 256, 'NO', 'YES'),
                        decode(bitand(ac.flags, 2), 2, 'NO',
                               decode(bitand(ac.flags, 4), 4, 'NO',
                                      decode(bitand(ac.flags, 8), 8, 'NO',
                                             'N/A')))),
       decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32, 'YES',
                                          'NO')),
       decode(c.property, 0, 'NO', decode(bitand(c.property, 8), 8, 'YES',
                                          'NO')),
       decode(c.segcol#, 0, to_number(null), c.segcol#), c.intcol#,
       case when nvl(h.row_cnt,0) = 0 then 'NONE'
            when (h.bucket_cnt > 255
                  or
                  (h.bucket_cnt > h.distcnt and h.row_cnt = h.distcnt
                   and h.density*h.bucket_cnt < 1))
                then 'FREQUENCY'
            else 'HEIGHT BALANCED'
       end,
       decode(bitand(c.property, 1024), 1024,
              (select decode(bitand(cl.property, 1), 1, rc.name, cl.name)
               from sys.col$ cl, attrcol$ rc where cl.intcol# = c.intcol#-1
               and cl.obj# = c.obj# and c.obj# = rc.obj#(+) and
               cl.intcol# = rc.intcol#(+)),
              decode(bitand(c.property, 1), 0, c.name,
                     (select tc.name from sys.attrcol$ tc
                      where c.obj# = tc.obj# and c.intcol# = tc.intcol#)))
from sys.col$ c, sys."_CURRENT_EDITION_OBJ" o, sys.hist_head$ h, sys.user$ u,
     sys.coltype$ ac, sys.obj$ ot, sys."_BASE_USER" ut
where o.obj# = c.obj#
  and o.owner# = u.user#
  and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
  and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
  and ac.toid = ot.oid$(+)
  and ot.type#(+) = 13
  and ot.owner# = ut.user#(+)
  and (o.type# in (3, 4)                                     /* cluster, view */
       or
       (o.type# = 2     /* tables, excluding iot - overflow and nested tables */
        and
        not exists (select null
                      from sys.tab$ t
                     where t.obj# = o.obj#
                       and (bitand(t.property, 512) = 512 or
                            bitand(t.property, 8192) = 8192))))

I’ve left the line numbers in for this one to make it easier to refer to bits of the code. Notice that there are several nvl2() calls in the select list that include the option to query table obj$ – these are at lines 11, 19, 23, 25 and 27; these are the five scalar subqueries represented by lines 1 to 10 of the execution plan, and given that they are all within the same decode() statement you can appreciate that the corresponding pairs of lines in the execution plan should all be at the same indentation rather than forming the steadily increasing cascade that they do. This is an error in the calculated depth column of the plan table – an error which I first mentioned a few years ago with an example from 10g adding a throwaway comment that I had found a similar bug in 11g – but I’d not got around to showing an example until today and this example from 11.2.0.3

Footnote:

As a little instruction in reading execution plans – the main section of the query starts at line 21, with lines 22 – 37 representing the list of tables in the “from” list – including two tables representing the view _CURRENT_EDITION_OBJ.
Lines 38 and 39 represent the “not exists” subquery against table tab$.
Lines 40 – 42 represent an existence subquery embedded in the definition of view _CURRENT_EDITION_OBJ.
Lines 11 – 13 represent the inline scalar subquery in the nvl2() call at line 40 of the view definition.
Lines 14 – 20 represent the scalar subqueries in the decode() call at line 99 of the view definition, and again we have a depth problem, because lines 19 and 20 represent the second scalar subquery in that decode and line 19 ought to be indented to the same level as line 14.

If you write some old-style code to query the plan table, using a connect by between the id and parent_id columns, you’ll find that the indentation is correct – here’s the output from the plan table when reported by the old utlxpls.sql script from Oracle 8i:

Plan Table
--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     8 |   21K|   2387 |       |       |
|  TABLE ACCESS BY INDEX ROW|OBJ$      |   480 |   14K|      3 |       |       |
|   INDEX RANGE SCAN        |I_OBJ1    |   192 |      |      2 |       |       |
|  TABLE ACCESS BY INDEX ROW|OBJ$      |   480 |   14K|      3 |       |       |
|   INDEX RANGE SCAN        |I_OBJ1    |   192 |      |      2 |       |       |
|  TABLE ACCESS BY INDEX ROW|OBJ$      |   480 |   14K|      3 |       |       |
|   INDEX RANGE SCAN        |I_OBJ1    |   192 |      |      2 |       |       |
|  TABLE ACCESS BY INDEX ROW|OBJ$      |   480 |   14K|      3 |       |       |
|   INDEX RANGE SCAN        |I_OBJ1    |   192 |      |      2 |       |       |
|  TABLE ACCESS BY INDEX ROW|OBJ$      |   480 |   14K|      3 |       |       |
|   INDEX RANGE SCAN        |I_OBJ1    |   192 |      |      2 |       |       |
|  HASH JOIN                |          |   480 |    1M|      5 |       |       |
|   INDEX RANGE SCAN        |I_OBJ1    |   480 |   12K|      2 |       |       |
|   TABLE ACCESS FULL       |USER$     |    59 |  117K|      2 |       |       |
|  NESTED LOOPS OUTER       |          |     1 |    2K|      3 |       |       |
|   TABLE ACCESS BY INDEX RO|COL$      |     1 |   56 |      2 |       |       |
|    INDEX UNIQUE SCAN      |I_COL3    |     1 |      |      1 |       |       |
|   TABLE ACCESS BY INDEX RO|ATTRCOL$  |     1 |    1K|      1 |       |       |
|    INDEX UNIQUE SCAN      |I_ATTRCOL |     1 |      |      0 |       |       |
|  TABLE ACCESS BY INDEX ROW|ATTRCOL$  |     1 |    1K|      2 |       |       |
|   INDEX UNIQUE SCAN       |I_ATTRCOL |     1 |      |      1 |       |       |
|  FILTER                   |          |       |      |        |       |       |
|   HASH JOIN RIGHT OUTER   |          |    72K|  188M|   2387 |       |       |
|    TABLE ACCESS FULL      |USER$     |    59 |  118K|      3 |       |       |
|    HASH JOIN RIGHT OUTER  |          |    72K|   45M|   2384 |       |       |
|     TABLE ACCESS FULL     |OBJ$      |   583 |   30K|    205 |       |       |
|     HASH JOIN RIGHT OUTER |          |    72K|   41M|   2178 |       |       |
|      TABLE ACCESS FULL    |COLTYPE$  |     2K|  174K|    275 |       |       |
|      HASH JOIN            |          |    72K|   37M|   1902 |       |       |
|       TABLE ACCESS FULL   |USER$     |    59 |    1K|      3 |       |       |
|       HASH JOIN           |          |    72K|   35M|   1899 |       |       |
|        INDEX FAST FULL SCA|I_USER2   |    59 |    2K|      2 |       |       |
|        HASH JOIN RIGHT OUT|          |    72K|   31M|   1896 |       |       |
|         TABLE ACCESS FULL |HIST_HEAD |    16K|    2M|     70 |       |       |
|         NESTED LOOPS      |          |    72K|   19M|    660 |       |       |
|          TABLE ACCESS FULL|OBJ$      |    47K|    3M|    205 |       |       |
|          TABLE ACCESS CLUS|COL$      |     2 |  404 |      1 |       |       |
|           INDEX UNIQUE SCA|I_OBJ#    |     1 |      |      0 |       |       |
|   TABLE ACCESS CLUSTER    |TAB$      |     1 |   26 |      2 |       |       |
|    INDEX UNIQUE SCAN      |I_OBJ#    |     1 |      |      1 |       |       |
|   NESTED LOOPS            |          |     1 |   78 |      3 |       |       |
|    INDEX RANGE SCAN       |I_OBJ4    |     1 |   39 |      2 |       |       |
|    INDEX RANGE SCAN       |I_USER2   |     1 |   39 |      1 |       |       |
--------------------------------------------------------------------------------

Quiz Night

Warning – this is a catch question, and I haven’t given you enough information to have any idea of the right answer; though, by telling you that I haven’t given you enough information to have any idea of the right answer, you now have some information that might help you to get closer to the right answer.

I have a simple heap table with no indexes. Immediately after flushing the buffer_cache I’ve run a query that looks ike this:

select max(column_ZZZ) from table_X;

The most significant session stats for this operation are as follows:

Name                                           Value
----                                        ---------
session logical reads                          20,651
consistent gets                                20,651
consistent gets direct                         20,649
physical reads                                    655
physical reads cache                                1
physical reads direct                             654
Number of read IOs issued                           6
no work - consistent read gets                 20,649
table scan rows gotten                        329,922
table scan blocks gotten                       10,649
table fetch continued row                         645
buffer is not pinned count                     10,000

The instance is 11.2.0.3, so serial direct path reads have been used for the (necessary) tablescan. The tablespace is using 8KB blocks, 1MB uniform extent sizing, and manual (freelist) segment space management. The data in the table was created by a pl/sql loop of inserts with commits, there have been no updates, deletes, merges or rollbacks. Here’s the code (with one crtical detail hidden) that populated the table:

begin
	for i in 1..X loop
		insert into member(member_id, block_age_max_nbr) values (1,1);
		commit;
	end loop;
end;
/

execute dbms_stats.gather_table_stats(user,'member',method_opt => 'for all columns size 1')

Roughly how many rows are there in the table ?

Update 13th Jan:

The answer is 10,000; the interesting observations are in this comment and the reply.

Over-indexing

This is the text of an article I published in the UKOUG magazine a few years ago, but it caught my eye while I was browsing through my knowledge base recently, and it’s still relevant. I haven’t altered the original apart from adding a couple of very brief comments in brackets [Ed: like this].

One of the strengths of a relational database is that you should be able to throw any reasonable query (and even some unreasonable queries) at it and it will be able to return the right answer without being told how to navigate through the data.

There’s no guarantee, though, that you’ll get the answer quickly unless you’ve given the database some help by turning your logical model into a sensible physical implementation. Part of the physical implementation will be the choice of indexes – and this article reviews one of the commonest indexing issues that I see in OLTP systems

Costs and Benefits.

Why do we create indexes? There are three main reasons as far as Oracle is concerned. We need some indexes to help Oracle enforce uniqueness; we need some indexes for performance reasons to supply a high-precision access path to important data; and we may need some indexes to help Oracle enforce referential integrity constraints.

Theoretically, of course, we don’t need any indexes to ensure data correctness, so you could say that the only reason for having indexes is to improve performance.  For example, you could enforce primary key constraints simply by locking tables on every insert, update or delete and then doing a tablescan to make sure that there is no conflicting data – but having an index “for” the primary key allows you to check just one block in the index without locking the table, so you get better performance and better concurrency.  (In passing, an index that covers a primary key or unique constraint need not be a unique index, and the index definition need not be an exact match for the constraint – provided it starts with the relevant columns.)

So indexes can be a massive aid to performance and concurrency. But indexes have a price – you have to maintain them, usually in real-time, and that can cause problems. Roughly every two weeks [Ed: not quite so frequently in 2012], someone writes into the Oracle Forums with a question like: “I have a batch process that inserts 1 million rows of data into a table every night. But it’s very slow and I see lots of waits for ‘db file sequential read’ while it’s going on. Why is this happening?”

If you insert 1 million rows into a table you might have to create and (eventually) write something in the region of 25,000 table blocks (assuming a row size of about 200 bytes … 40 rows per block). If you have one index on that table then, in principle, you might have to do a random read to find an index leaf block for every single row you insert – and you may have to write the updated block out very soon afterwards to make space for the next random read. If the table is large and you have a couple of dozen indexes this “in principle” threat very soon becomes a real one.

There is a very important difference between (heap) tables and their indexes – a row can go into a table wherever the next empty space happens to be; a row has to go into an index in exactly the right place, and finding that place can be expensive. Things get worse if you are doing updates – you can update a row in the table “in situ”, but if you change the value of an indexed column you have to find an index entry for the old value, delete it, and then insert an index entry for the new value in the right place.

So don’t create indexes unless they really pay for their existence when you come to query the data – and when you do create an index make sure you maximise the payback.

The Commonest Error.

There are a number of subtle ways in which you can create too many indexes, but there is one error that is easy to spot – the foreign key index.

When you create a referential integrity constraint (foreign key) you do not need to create an index to support it and (unlike the unique constraint and primary key constraint) Oracle will not create such an index automatically. However, if you are going to update the primary key or unique key at the “parent” end of the referential integrity constraint Oracle will lock the “child” table to check for committed and uncommitted child rows if a suitable index does not exist. Consequently many people (and some application generators) automatically create a matching index for every foreign key they create.

Rule 1: don’t create “foreign key” indexes unless they are really necessary, or unless they are sufficiently useful that you would have created them any way even if there hadn’t been a referential integrity constraint to protect.

If you do have a foreign key constraint that needs an index, though, remember that the index does not have to be an exact match for the constraint – the critical feature is that it should start with the same columns (not necessarily in the same order) as the constraint definition. This means that you can always ‘add value’ to a foreign key index that might otherwise be just a technical overhead.

Rule 2: consider adding columns to foreign key indexes to make them useful to high precision queries.

Moreover, when you think about the parent/child relationship remember that you often see one parent row with several child rows – think orders/order_lines, or currency_codes/payments. In cases like this there may be an opportunity for saving quite a lot of space (and reducing the impact on the buffer cache) by compressing the index on some, or all, of the foreign key column(s).

Case Study.

Here’s a list of indexes (with a little camouflage) from a system I was looking at recently [Ed: some time in 2008]. It’s typical of the over-indexing problems that I see fairly frequently. I got this list by running a query similar to:

select
        index_name, column_name
from
        user_ind_columns
where
        table_name = 'XXXXX'
order by
        index_name, column_position
;

You could enhance this by query to use dba_ind_columns, of course, and add all sorts of extras by joining to dba_tab_columns, dba_indexes, and dba_ind_expressions to pick up details about mandatory columns, column types, uniqueness, index types, and functions involved in function-based indexes. In the case of my example, I used a slightly more sophisticated query to show the expressions used in function-based indexes – and here’s the list of indexes I found on one table:

ap_fun_fk_i ( fun_id )
ap_grp_fk_i ( grp_id )
ap_grp_fun_id_i( grp_id,fun_id )
ap_org_ap_i ( org_id,ap_id )
ap_org_fk_i ( org_id )
ap_per_ap_i ( per_id,ap_id )
ap_per_fk_i ( per_id )
ap_pk ( ap_id )
ap_ud_i ( trunc(update_date) )

This system had a convenient naming convention, PK in the name indicates the primary key, and FK indicates foreign keys. You can see immediately that we seem to have several redundant indexes as far as protecting foreign keys is concerned.

• AP_GRP_FK_I is made redundant by AP_GRP_FUN_ID_I,
• AP_ORG_FK_I is made redundant by AP_ORG_AP_I,
• AP_PER_FK_I is made redundant by AP_PER_AP_I.

On top of this, we might decide that since there are only a few organizations (org_id) we could benefit by compressing the index on (org_id, ap_id) on its first column. We might also compress the index on functions (fun_id) as well – although we might go one step further and decide that we will never delete functions or update the key values, and drop the index altogether.

Following the thought about compression, we could also consider compressing the index on (trunc(update_date)) – a fairly large amount of data gets updated each day, so the value is quite repetitive, similarly (grp_id, fun_id) is also fairly repetitive, so we might compress on both columns.

This leaves us with:

ap_grp_fun_id_i ( grp_id,fun_id ) compress 2
ap_org_ap_i ( org_id,ap_id )  compress 1
ap_per_ap_i ( per_id,ap_id )
ap_pk ( ap_id )
ap_ud_i ( trunc(update_date) )  compress 1

The reduction in the number of indexes is likely to reduce the undo and redo by about 40% as we insert (single row) data into this table, as well as reducing the possible I/O demand by the same sort of percentage. At the same time, the effect of the compression could (in this case) eliminate a further 10% to 15% of I/O related to this table because of the reduction in size of the indexes.

Further considerations.

The only remaining issue to consider is whether there is anything about the nature of our processing that suggests the need for housekeeping on these indexes – and the index on trunc(update_date) is an ideal candidate for consideration.

As we update data, we are going to delete entries from blocks in the left hand end of the index and insert them at the right hand end of the index. Depending on the rate and pattern of updates it is possible that a large number of blocks at the left hand end of the index will become close to empty –  this could have a significant impact on the effectiveness of the buffer cache and might encourage us to use the coalesce command on the index every few days. (If this sounds familiar, I have written about this particular index in the past.)

Having thought about that, you might then wonder why we have an index on trunc(update_date) at all. A query that tries to find all rows updated on a certain date, or range of dates, may have to jump around all over the table to acquire a lot of data. Who wants to do this, and how often? It’s possible that the index exists for a particular report that runs just once per day – perhaps there is an argument for dropping this index as well.

Conclusion:

It’s very easy to create more indexes than you really need, and indexes can be very expensive to maintain. Sometimes you can spot “obviously” redundant indexes simply by inspecting index names and column names.  A few reasonable guesses may allow you to drop some indexes and make others more efficient with a minimum of risk and effort.