Cost Based Oracle: Fundamentals

Chapter 9: Query Transformation

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)

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

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

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

21SORT (AGGREGATE)

32TABLE 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)

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

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

10FILTER

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

31SORT (AGGREGATE)

43TABLE 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):

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”