Who's online

There are currently 0 users and 30 guests online.

Recent comments

Oakies Blog Aggregator

CBO Days in Zurich December 11-12 , Switzerland

My dear colleague from OakTable Christian Antognini is organizing an excellent event called "CBO Days 2012" which will take place in Zurich in Switzerland in December 11-12.

I am really honored to be speaking together with great names of the Cost Based Optimizer (CBO) like Jonathan Lewis, Randolf Geist, Mohamed Zait, Maria Colgan and Christian Antognini.

I remember being the attendee at the first CBO Days back in 2006. Mohamed Zait was at time  already involved in the developing of the CBO and had several excellent presentations. It was a great opportunity to discuss with him the actual problems we had with the CBO at that time like the bind variables peeking only at the parse time and run-away queries. At the OakTable dinner we suggested him several possible solutions which were in subsequent years built in the database.

Such kind of events are great opportunity to get the knowledge which one can't really get at one place. Internet is a great source of knowledge, but attending a detailed technical presentation is something quite different. 

I expect that this event will be simply the best event totally focused on the problems of the cost based optimization.  

My public appearances in autumn 2012

The era of different conferences before the end of this year is here.
Last week I was speaking at Slovenian Oracle User Group Conference in Ljubljana and later on in the same week at Croatian Oracle User Group Conference in Rovinj.

Here is the list of my forthcoming public appearances:

#1f497d; font-size: 11pt;">SANGAM12, All Indian Oracle Users Group conference, Bangalore, India, November 2-3 2012,
#1f497d; font-size: 11pt;">BGOUG, Bulgarian Oracle User Group Conference, Pravets, Bulgaria, November 16-18 2012,
#1f497d; font-size: 11pt;">DOAG, German Oracle Users Group Conference, Nurnberg, Germany, November 20-22 2012,
#1f497d; font-size: 11pt;">UKOUG, UK Oracle User Group Conference, Birmingham UK, December 3-5 2012,
#1f497d; font-size: 11pt;">Trivadis CBO Days 2012, Zurich, Switzerland, December 11-12 2012,
#1f497d; font-family: "Calibri","sans-serif"; font-size: 11.0pt;"> 

Does the Parse Time Increase Linearly with the Number Of Child Cursors?

In the last post I discussed a test case generating lot of child cursors. Today I wanted to show you, for the very same test case, that in 11.2 the parse time might increases linearly with the number of child cursors per parent cursor. This is the expected behavior. In fact, to check whether an already available child cursor can be reused, the list of child cursors must be scanned. And, in case no one of the already available child cursors is compatible, every entry needs to be probed.

Note that to generate the previous chart I simply run, in, the PL/SQL block of the previous post while SQL trace was enabled. Then I extracted from the trace file the elapsed time for every parse operation and loaded the values in Excel.

In, because of the artificial limitation of the number of child cursors per parent cursor, the parse time is almost constant. Notice, however, that in this case the number of child cursors is spread across a lot of parent cursors, not a single one as in

Skip Scan

A recent question on OTN asked how you could model a case where Oracle had the choice between a “perfect” index for a range scan and an index that could be used for an index skip scan and choose the latter path even though it was clearly (to the human eye) the less sensible choice. There have been a number of wierd and wonderful anomalies with the index skip scan and bad choice over the years, and this particular case is just one of many oddities I have seen in the past – so I didn’t think it would be hard to model one (in fact, I thought I already had at least two examples somewhere in my library – but I couldn’t find them).

Take a data set with two columns, call them id1 and id2, and create indexes on (id1), and (id2, id1). Generate the id1 column as a wide range of cyclic values, generate the id2 set with a small number of repetitive values so that a large number of physically adjacent rows hold the same value. The clustering_factor on the (id1) index will be very large, the clustering_factor on the (id2, id1) index will be relatively small because it will be controlled largely by the repetitive id2 value. Here’s the data set:

create table t1
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		level <= 1e4
	rownum - 1			id,
	mod(rownum - 1,1e4)		id1,
	trunc((rownum - 1)/1e4)		id2,
	lpad(rownum,10,'0')		small_vc
	generator	v1,
	generator	v2
	rownum <= 1e6 ;
 -- gather table stats, no histograms

create index t1_i1 on t1(id1);
create index t1_i2 on t1(id2,id1);

If you examine the code you will see that id2 repeats each value 10,000 times, and as it does so id1 ranges from 0 to 9,9999; this means the index on (id2,, id1) will essentially be listing the data in exactly the order it appears in the table, while each entry in the index will either be jumping 10,000 rows down the table from the previous one, except of the cases where it jumps from the end of the table to the start of the table. As a quick sanity check on the indexes I’ve created – it can be quite easy to fool yourself that you’ve done what you’ve want when the indexes prove otherwise (for reference, there are 3,872 blocks in the table):

 	index_name, num_rows, distinct_keys, leaf_blocks, blevel, clustering_factor 
 	table_name = 'T1' 
order by

-------------------- ---------- ------------- ----------- ---------- ----------------- 
T1_I1                   1000000         10000        2090          2           1000000 
T1_I2                   1000000       1000000        2504          2              3872 

Now all we have to do is run a suitable query – so here’s a simple query that does the “wrong” thing (in I’ve shown the query (unhinted, then hinted to use the “correct” index) with the output from autotrace, but the plan that appears from autotrace is the same plan that actually gets executed at run-time. I’ve disabled CPU costing to reduce the risk of variation in results, but I see the same effects when any reasonable values for system statistics are enabled. The text is a straight cut-n-paste from an SQL*Plus session

SQL> set autotrace traceonly explain
SQL> select * from t1
  2  where id1 between 501 and 502
  3  ;

Execution Plan
Plan hash value: 3617828059

| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT            |       |   299 |  6578 |   290 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   299 |  6578 |   290 |
|*  2 |   INDEX SKIP SCAN           | T1_I2 |   285 |       |   288 |

Predicate Information (identified by operation id):

   2 - access("ID1">=501 AND "ID1"<=502)
        filter("ID1">=501 AND "ID1"<=502)

    - cpu costing is off (consider enabling it)

SQL> select /*+ index_rs_asc(t1(id1)) */ * from t1
  2  where id1 between 501 and 502
  3  ;

Execution Plan
Plan hash value: 1429545322

| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT            |       |   299 |  6578 |   303 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   299 |  6578 |   303 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |   300 |       |     3 |

Predicate Information (identified by operation id):

   2 - access("ID1">=501 AND "ID1"<=502)

    - cpu costing is off (consider enabling it)

I have to say I was expecting to have to play around a little bit with unpeeked bind variables, or scalar subqueries in the where clause to get Oracle to pick the wrong index, but in the latest version of this wasn’t necessary. (I chose this version, by the way, because the question on OTN related to 10gR2.)

If you think carefully about the arithmetic of the skip scan execution plan, you’ll notice a couple of oddities. The cost of the index probe is 288, but the optimizer should know from the column statistics that there are 100 distinct values for id2 and might, therefore, calculate the cost as 100 (plus a bit), because it will have to find 100 leaf blocks.

The second oddity is that the cost of finding the estimated 299 rows in the table seems to have been calculated by applying the table selectivity (derived from the predicate id1 between 501 and 502) to the clustering_factor of the (id2, id1) index – which isn’t a good idea given the extreme difference between the two available clustering factors. Without looking at the (id1) clustering_factor, perhaps Oracle should have taken the cost of (id2 = constant and id1 between 501 and 502) and multiplied the result by the number of distinct values of id2.

If you’re wondering why the estimated cardinality is (nearly) 300, by the way, this is simply the standard selectivitity calculation: (range required by predicate)/(total range of column) + 1/num_distinct + 1/num_distinct that you get from a between clause. Of course, once you’ve got a model – especially a model that seems to do the wrong thing – you might as well run it on as many sensible versions of Oracle as possible to see how things change over time. My example didn’t change on, but on the cost of the skip scan increased slightly, just enough to make the “expected” range scan appear. I didn’t bother to pursue this in detail, butI did have a couple of other scenarios to play around with that I might write up some time soon.

I’ll leave you with one interesting thought. The skip scan is, in effect, a query that probes the index for every possible value of the leading edge – so what to do you think the costs will show if I write a query that explicity runs my range scan on id1 for every value that is currently in id2. (Logically it’s not the same query, of course, but coincidentally it is temporarily equivalent.)

SQL> set autotrace traceonly explain
SQL> select
  2          *
  3  from t1
  4  where
  5          id2 in (
  6                   0, 1, 2, 3, 4, 5, 6, 7, 8, 9,
  7                  10,11,12,13,14,15,16,17,18,19,
  8                  20,21,22,23,24,25,26,27,28,29,
  9                  30,31,32,33,34,35,36,37,38,39,
 10                  40,41,42,43,44,45,46,47,48,49,
 11                  50,51,52,53,54,55,56,57,58,59,
 12                  60,61,62,63,64,65,66,67,68,69,
 13                  70,71,72,73,74,75,76,77,78,79,
 14                  80,81,82,83,84,85,86,87,88,89,
 15                  90,91,92,93,94,95,96,97,98,99
 16          )
 17  and     id1 between 501 and 502
 18  ;

Execution Plan
Plan hash value: 2879882323

| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT             |       |   296 |  6512 |   102   (0)| 00:00:02 |
|   1 |  INLIST ITERATOR             |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |   296 |  6512 |   102   (0)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN          | T1_I2 |   297 |       |   100   (0)| 00:00:02 |

Predicate Information (identified by operation id):
   3 - access(("ID2"=0 OR "ID2"=1 OR "ID2"=2 OR "ID2"=3 OR "ID2"=4 OR "ID2"=5
              OR "ID2"=6 OR "ID2"=7 OR "ID2"=8 OR "ID2"=9 OR "ID2"=10 OR "ID2"=11 OR
              "ID2"=12 OR "ID2"=13 OR "ID2"=14 OR "ID2"=15 OR "ID2"=16 OR "ID2"=17 OR
              "ID2"=18 OR "ID2"=19 OR "ID2"=20 OR "ID2"=21 OR "ID2"=22 OR "ID2"=23 OR
              "ID2"=24 OR "ID2"=25 OR "ID2"=26 OR "ID2"=27 OR "ID2"=28 OR "ID2"=29 OR
              "ID2"=30 OR "ID2"=31 OR "ID2"=32 OR "ID2"=33 OR "ID2"=34 OR "ID2"=35 OR
              "ID2"=36 OR "ID2"=37 OR "ID2"=38 OR "ID2"=39 OR "ID2"=40 OR "ID2"=41 OR
              "ID2"=42 OR "ID2"=43 OR "ID2"=44 OR "ID2"=45 OR "ID2"=46 OR "ID2"=47 OR
              "ID2"=48 OR "ID2"=49 OR "ID2"=50 OR "ID2"=51 OR "ID2"=52 OR "ID2"=53 OR
              "ID2"=54 OR "ID2"=55 OR "ID2"=56 OR "ID2"=57 OR "ID2"=58 OR "ID2"=59 OR
              "ID2"=60 OR "ID2"=61 OR "ID2"=62 OR "ID2"=63 OR "ID2"=64 OR "ID2"=65 OR
              "ID2"=66 OR "ID2"=67 OR "ID2"=68 OR "ID2"=69 OR "ID2"=70 OR "ID2"=71 OR
              "ID2"=72 OR "ID2"=73 OR "ID2"=74 OR "ID2"=75 OR "ID2"=76 OR "ID2"=77 OR
              "ID2"=78 OR "ID2"=79 OR "ID2"=80 OR "ID2"=81 OR "ID2"=82 OR "ID2"=83 OR
              "ID2"=84 OR "ID2"=85 OR "ID2"=86 OR "ID2"=87 OR "ID2"=88 OR "ID2"=89 OR
              "ID2"=90 OR "ID2"=91 OR "ID2"=92 OR "ID2"=93 OR "ID2"=94 OR "ID2"=95 OR
              "ID2"=96 OR "ID2"=97 OR "ID2"=98 OR "ID2"=99) AND "ID1">=501 AND "ID1"<=502)

Unfortunately the costing isn’t consistent with the skip scan costing.

Footnote: I haven’t looked at the 10053 trace files for these plans – they take a lot of time to read and don’t often help very much, so it’s only the sort of thing I do if I think it’s necessary to solve an important production problem or (if I’m doing some R&D) it looks like it might be entertaining.

Oaktable World first 3 vidoes up

The first 3 videos of talks at Oaktable World are up:

Connor McDonald 11g RAC upgrade drama

Tanel Poder hacking session:

Tim Gorman on exchange partition

We still have more video material to come but only limited resources to go through and edit them.  Comment here if you are interested in help out edit video. For a list of talks see:



Movember at…

Movember is coming to…

You will be able to track my progress here:

If you can afford to make a donation, no matter how small, it would be much appreciated!

I’ll try and post a photo each day for the whole month, so you can see how bad I am at growing facial hair. :)



PS. I feel really nervous now. It’s suddenly become very real!

Movember at… was first posted on October 22, 2012 at 7:53 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

DOUG presentation on dbms_xplan

Please join us at the DOUG (DALLAS ORACLE USERS GROUP) Oracle Database Forum meeting on Thursday, October 25, 2012 from 5 pm – 7 pm.
Presented by Riyaj Shamsudeen, OraInternals, & Sahil Thapar:

“Out with the old way, Enter dbms_xplan: A Swiss army knife for performance engineers”

Rough outline:
(i) Ability to query access path from memory, AWR repository
(ii) Ability to use cardinality feedback method to understand access plan issues. Few tips from a real world experience will be provided too.
(iii) Ability to understand issues with database links etc.
(iv) Options such as ADVANCED, ALLSTATS etc
(v) Why should you choose dbmx_xplan over tkprof+sql_trace combination?
(vi) Disadvantages of dbms_xplan and a quick introduction to dbms_monitor.

Refreshments sponsored by me :)

Update: Uploading the presentation pdf files. Enjoy :)


oci-24550 [3221225477] [Unhandled exception: Code=c0000005 Flags=0

October 22, 2012 Lately, I have been busy with a couple of items that are not completely Oracle Database specific.  I tried experimenting with high definition 1080p security cameras.  After wasting an unacceptable amount of money to purchase two Y-Cam Wireless High Definition 1080p video cameras (model YCBLHD6), and spending in excess of 38 hours fighting with the cameras [...]

What Cancer Cannot Do

For my sister and all those who battle cancer...

Cancer is so limited...
It cannot cripple love.
It cannot shatter hope.
It cannot corrode faith.
It cannot eat away peace.
It cannot destroy confidence.
It cannot kill friendship.
It cannot shut out memories.
It cannot quench the spirit.
It cannot silence courage.
It cannot reduce eternal life.

Bring Your Own Device (BYOD) : Context is everything…

Bring Your Own Device (BYOD) is definitely an important issue for companies and IT departments, but what the vast majority of reporters and CEOs seem to forget is that context is extremely important.

Looking at this from a company perspective, when we are talking about phones and tablets, then BYOD is a pretty important issue. It seems to be creeping into the laptop side of business a little, but as far as desktops are concerned, BYOD it is virtually non-existent. So when Marc Benioff talks about the affect of BYOD on Windows 8 uptake I think he is living in some sort of dream land. I’m not suggesting Windows 8 will be a success in business, but it’s failure will not be down to BYOD.

Ignoring the board rooms and the sales teams, who are essentially consumers, the real IT work in companies is still being done on PCs. Yes, you can use a cloud service on your phone, but look around the office and you will see those cloud services are actually being used by people on PCs. Applications such as Oracle Fusion Apps have mobile device interfaces, but typically people will be sitting at a PC doing all the real work, not using some crappy little software keyboard. This idea that the office is full of people doing their job on an iPhone is just stupid.

Since we are going to have PCs on our desks for quite some time, then Microsoft are going to do OK. The PC on your desk at work will be replaced and Microsoft will get their pound of flesh, because there is no real alternative. Apple is a tiny fraction of this market and Linux desktops have never taken off. Whether the company install Windows 7 or Windows 8 is another issue, but Microsoft get paid all the same.

So please stop insulting our intelligence by telling us BYOD is a game changer on the office desktop. It’s not!

Note. I’m looking at this from a company IT perspective, so don’t start telling me how your granny got rid of her PC and happily replaced it with an iPad! Your Granny is not running!




Bring Your Own Device (BYOD) : Context is everything… was first posted on October 21, 2012 at 3:44 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.