JL Computer Consultancy

Cost Based Oracle: Fundamentals

Chapter 9: Query Transformation


Ch.9 Code Depot (22nd Nov 2005)

Addenda

Scalar Subquery cache (24th March 2006)

Explain Plan lies (22nd Nov 2005)

Errata

Tablescans (30th Dec 2005)

Typos (22nd Nov 2005)

Addenda

Scalar Subquery cache (23rd March 2006)

Page 216, update statement – this shows 67 as the first “collision value” for 9i and 432 as the first collision value for 10g.  In this case, we are talking about collisions in the hash table for scalar subqueries and their results. But there’s always another detail waiting to catch you out, and in all my tests I only ran on 32-bit Oracle, and only a couple of operating systems.

I’ve just had an email from Krishnendu Deb who has repeated these tests on 64-bit Solaris, and found that the first collision for 9i was at 84, and for 10g the collision was at 88. Tentatively, we think that this is more likely to be related to the 64-bits, rather than the Solaris – but if anyone else has a 64-bit O/S available for testing (script filter_cost_01a.sql) then I’d be interested to hear more results.

Explain Plan lies (22nd Nov 2005)

Page 240, unnest_01a.sql: I have a description showing how the execution plan of a particular type of query changes as you upgrade from 8i to 9i. The 8i plan shows a filter operation; the 9i plan doesn’t have a filter operation, instead showing a different parent/child indentation pattern implying a different order of operation and what I would term a ‘driving subquery’.

Execution Plan (9.2.0.6 Autotrace)

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

0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=35 Card=1000 Bytes=72000)

1  0   TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=1000 Bytes=72000)

2  1     SORT (AGGREGATE)

3  2       TABLE ACCESS (FULL) OF 'EMP' (Cost=35 Card=20000 Bytes=100000)

The particular pattern for the 9i execution plan is one that has been around for quite a long time, appears fairly frequently, and has become ingrained in my mind as ‘obviously’ a driving subquery – the necessity that a child rowsource feeds its parent rowsource dictates that the sort (aggregate) at line 2 must take place before the table access full at line 1.

However, it suddenly occurred to me one day that this pattern could be to be a simple case of the deceptive execution plan discussed on page 214, where there is a filter operation that does not appear in the plan as a filter line. Was it possible that Oracle 9i was actually doing exactly the same filter as Oracle 8i, but not reporting it. (See below for the 8i execution plan).

Execution Plan (8.1.7.4 Autotrace)

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

0    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=34 Card=1000 Bytes=72000)

1  0   FILTER

2  1     TABLE ACCESS (FULL) OF 'EMP' (Cost=34 Card=1000 Bytes=72000)

3  1     SORT (AGGREGATE)

4  3       TABLE ACCESS (FULL) OF 'EMP' (Cost=34 Card=20000 Bytes=100000)

The answer is yes. The execution plan in 9i is a lie. At runtime, 9i follows the same sequence of activities as 8i. This is not a ‘driving subquery’, it is simply a case of scalar subquery caching, where there is guaranteed to be only one result that needs to be cached.

It is easy to demonstrate this by using a 10046 trace. You could use just the emp table from script unnest_01a.sql, and delete a few hundred rows from the start of the table. This will leave a few empty blocks (more than one multiblock read is good) at the start of the table. When you run the query, there are two possible courses of action dictated by the two possible execution paths:

·        the emp table is scanned from end to end for the subquery, then scanned again for the outer select – the ‘driving subquery’ option.

·        The first few blocks of the emp table are scanned to find the first row still in the table, then the emp table is scanned from end to end to operate the subquery, then the first scan picks up and completes – the filter option with scalar subquery caching.

It’s the second option that actually takes place in 8i, 9i, and 10g. The execution plan from 8i is telling the truth; the others are not.

It’s much easier to see the effect if you use two copies of the emp table (so you don’t have to worry about the scan used by one operation caching blocks for the other operation). I have added script filter_plan_bad.sql to the chapter 9 code depot as the test case. The first eight wait states from the 9.2.0.6 trace file looked like this (emp1 is the outer table, emp2 is the table in the subquery):

Sample Trace 9.2.0.6

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

WAIT #3: nam='db file sequential read' ela= 392 p1=13 p2=9 p3=1           Emp1 seg header

WAIT #3: nam='db file scattered read' ela= 987 p1=13 p2=10 p3=8           Emp1 data

WAIT #3: nam='db file scattered read' ela= 1261 p1=13 p2=18 p3=8          ditto

WAIT #3: nam='db file sequential read' ela= 17526 p1=13 p2=265 p3=1       Emp2 seg header

WAIT #3: nam='db file scattered read' ela= 3435 p1=13 p2=266 p3=8         Emp2 data

WAIT #3: nam='db file scattered read' ela= 1795 p1=13 p2=274 p3=8         ditto

WAIT #3: nam='db file scattered read' ela= 1815 p1=13 p2=282 p3=8         ditto

WAIT #3: nam='db file scattered read' ela= 1825 p1=13 p2=290 p3=8         ditto

Back to Book Index

Top of page


Errata

Tablescans (30th Dec 2005)

Page 209, paragraph 1:  I refer to the hidden parameter _tablescan_cost_plus_one; I have missed an underscore from the name, it should be _table_scan_cost_plus_one. The same error appears in Chapter 1, page 14, Effects of Block Sizes, paragraph 1, and Page 19, The I/O Bit, paragraph 1:  

Thanks to Ramakrish Menon for spotting this error.

Typos (22nd Nov 2005)

Page 235, end of second (proper) paragraph: “commentedit” should be “commented it”

Page 236, Table 9-1, 3rd entry: “ordered_semi-join” should be “ordered­_semi_join”. 


Back to Book Index

Top of page