Oracle's Query Optimizer

Oracle's Catalog

select * from DICT;

select * from DICT_COLUMNS where table_name = 'ALL_INDEXES';

We'll discuss some tables of particular interest.

Oracle's Statistics

Statistics can be computed by doing a complete scan of an object, or then can be reasonably estimated based upon analysis of a random sample of the data. It is often possible to get a very decent estimate based on a much smaller random sample.

A database might be configured to recompute statistics upon each update to the database, to recompute less frequently but at fixed intervals, or to only recompute if explicitly instructed to do so by the database administrator.

For our course, we have chosen to compute statistics for all three versions of the TIGER database. We chose to do 25% sampling, and to create histograms with up to 20 buckets. We can examine some of these statistics, for example with the query

select * from ALL_TABLES where owner = 'TIGER';

select * from DICT_COLUMNS where table_name = 'ALL_INDEXES';

In contrast, table Point has approximately 915004 records over 3022 blocks, and an average record length of only 18 bytes. The table with the largest records on average is CFCC (presumably because some of the descriptions are quite long).

We can get more detailed information on a column-by-column basis with the query

select * from ALL_TAB_COLUMNS where owner = 'TIGER';

We can find even more specific details by looking at a histogram for each column, via

select * from ALL_TAB_HISTOGRAMS where owner = 'TIGER';

We can also gather statistics on the indexes, which can then be viewed via:

select * from ALL_INDEXES where owner = 'TIGER';

Oracle's Query Optimizer

On any given query, we can have Oracle explain its chosen plan to us.

---

Note: In order to use these features yourself, you must run the script utlxplan.sql (a copy of this script is also on patel2 in /home/cs341/scripts).

---

In the case that you want to see a trace of a query which produce a lot of output, you can use set autotrace traceonly, in which case the trace information and statistics will be shown for each query, but the user's output will not. In this mode, the query still gets evaluated; so if it takes a long time, you will have to wait, but the output is shielded.

For those who wish to dig even deeper, there is actually a way to force Oracle to show you a chosen evaluation plan for a specific query, without actually running that query. This might be helpful if you are trying to avoid starting a query that may take a long time. It can be done using a command EXPLAIN PLAN, however viewing the results takes considerably more effort with this syntax than with the autotrace setting.

To examine a sample plan, we consider the following query which finds the street name and address range for all chains involving the point with TZID = 11233807 (this happens to be the intersection of Grand and Lindell):

SELECT CS.fraddr || '-' || CS.toaddr as Address,
        F.NamePrefix || ' ' || F.Name || ' ' || F.NameType || ' ' || F.NameSuffix as "Street Name"
FROM Feature F, Chainside CS
WHERE F.feat = CS.feat AND F.fips_s = CS.fips_s AND F.fips_c = CS.fips_c
      AND CS.TLID IN (SELECT C.TLID FROM Chain C, Point P
                      WHERE (C.tzids = P.tzid OR  C.tzide = P.tzid)
                            AND P.tzid = 11233807)

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=490 Card=7 Bytes=511 )
1 0     NESTED LOOPS (Cost=490 Card=7 Bytes=511)
2 1         NESTED LOOPS (Cost=476 Card=7 By tes=336)
3 2             NESTED LOOPS (Cost=467 Card=3 Bytes=75)
4 3                 INDEX (UNIQUE SCAN) OF 'POINT_PRIMARY' (UNIQUE) (Cost=2 Card=1 Bytes=6)
5 3                 TABLE ACCESS (FULL) OF 'CHAIN' (Cost=465 Card=3 Bytes=57)
6 2             TABLE ACCESS (BY INDEX ROWID) OF 'CHAINSIDE' (Cost=3 Card=2565188 Bytes=589993 24)
7 6                 INDEX (RANGE SCAN) OF 'CHAINSIDE_PRIMARY' (UNIQUE) (Cost=2 Card=2)
8 1         TABLE ACCESS (BY INDEX ROWID) OF 'FEATURE' (Cost=2 Card=1 Bytes=25)
9 8             INDEX (UNIQUE SCAN) OF 'FEATURE_PRIMARY' (UNIQUE) (Cost=1 Card=1)

For the above example, we can trivially convert this table into a tree representation, as follows:

Of course, to fully understand the execution plan, we may wish to convert it back into the Query Evaluation plan as defined in Ch. 12.4.1 of the text, namely the extended relational algebra tree, with annotations at each node to indicate the access and implementation method. This takes a bit more work, since the Oracle evaluation plan omits some details, such as what conditions are being enforced on a join or selection, or what fields are being used in a projection. Also, we can only hypothesize on issues such as pipelined instructions. Revisiting the above example, we might construct the following diagram:

Because TIGER and TIGER3 have a different set of indices to consider, the same query produces a different execution plan when executed on the Tiger3 version of the database:

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=1457 Card=1 Bytes=61 )
1 0     HASH JOIN (Cost=1457 Card=1 Bytes=61)
2 1         HASH JOIN (Cost=1392 Card=8 Byte s=288)
3 2             VIEW OF 'VW_NSO_1' (Cost=466 Card=4 Bytes=52)
4 3                 SORT (UNIQUE)
5 4                     NESTED LOOPS (Cost=466 Card=4 Bytes=100)
6 5                         INDEX (UNIQUE SCAN) OF 'POINT_TZID' (UNIQUE) (Cost=2 Card=1 Bytes=6)
7 5                         TABLE ACCESS (FULL) OF 'CHAIN' (Cost =464 Card=4 Bytes=76)
8 2             TABLE ACCESS (FULL) OF 'CHAINSIDE' (Cost=911 Card=2562340 Bytes=58933820)
9 1         TABLE ACCESS (FULL) OF 'FEATURE' (Cost=64 Card=162344 Bytes=4058600)

For the above example, we can trivially convert this table into a tree representation, as follows:

and with deeper understanding, the likely Query Evaluation plan:

Providing Hints to Oracle

SELECT /*+ ...hint... */ [rest of query]

An explanation of using Optimizer Hints is provided by Oracle's Documentation (local copy).

As a few examples, if we are using Tiger3, but for some reason we prefered to use the sort-merge join algorithm for the top level join involving table Feature F. We could give such a hint as follows:

SELECT /*+ USE_MERGE(Feature F) */
        CS.fraddr || '-' || CS.toaddr as Address,
        F.NamePrefix || ' ' || F.Name || ' ' || F.NameType || ' ' || F.NameSuffix as "Street Name"
FROM Tiger3.Feature F, Tiger3.Chainside CS
WHERE F.feat = CS.feat AND F.fips_s = CS.fips_s AND F.fips_c = CS.fips_c
      AND CS.TLID IN (SELECT C.TLID FROM Tiger3.Chain C, Tiger3.Point P
                      WHERE (C.tzids = P.tzid OR  C.tzide = P.tzid)
                            AND P.tzid = 11233807)

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=2255 Card=1 Bytes=61 )
1 0     MERGE JOIN (Cost=2255 Card=1 Bytes=61)
2 1         SORT (JOIN) (Cost=1394 Card=8 By tes=288)
3 2             HASH JOIN (Cost=13 92 Card=8 Bytes=288)
4 3                 VIEW OF 'VW_NSO_1' (Cost=466 Card=4 Bytes=52)
5 4                     SORT (UNIQUE)
6 5                         NESTED LOOPS (Cost=466 Card=4 Bytes= 100)
7 6                             INDEX (UNIQUE SCAN) OF 'POINT_TZID' (UNIQUE) (Cost=2 Card=1 Bytes=6)
8 6                             TABLE ACCESS (FULL) OF 'CHAIN' (Cost=464 Card=4 Bytes=76)
9 3                 TABLE ACCESS (FULL) OF 'CHAINSIDE' (Cost=911 Card=2562340 Bytes= 58933820)
10 1         SORT (JOIN) (Cost=861 Card=16234 4 Bytes=4058600)
11 10             TABLE ACCESS (FULL ) OF 'FEATURE' (Cost=64 Card=162344 Bytes=4058600)

Another type of hint we can make is that a join of multiple tables be done in the specific order specified in the SQL query. If you look back at the default evaluation plan in Tiger3, you find that the nested query is joined with Chainside, and then that result is joined to Feature. This is despite the fact that the high-level SQL query ordered the from line as FROM Feature F, Chainside CS. If we wished to have joins done in order, we could specify a hint as follows:

SELECT /*+ ORDERED */
        CS.fraddr || '-' || CS.toaddr as Address,
        F.NamePrefix || ' ' || F.Name || ' ' || F.NameType || ' ' || F.NameSuffix as "Street Name"
FROM Tiger3.Feature F, Tiger3.Chainside CS
WHERE F.feat = CS.feat AND F.fips_s = CS.fips_s AND F.fips_c = CS.fips_c
      AND CS.TLID IN (SELECT C.TLID FROM Tiger3.Chain C, Tiger3.Point P
                      WHERE (C.tzids = P.tzid OR  C.tzide = P.tzid)
                            AND P.tzid = 11233807)

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=3492 Card=1 Bytes=61 )
1 0     HASH JOIN (Cost=3492 Card=1 Bytes=61)
2 1         MERGE JOIN (CARTESIAN) (Cost=725 Card=649376 Bytes=24676288)
3 2             VIEW OF 'VW_NSO_1' (Cost=466 Card=4 Bytes=52)
4 3                 SORT (UNIQUE)
5 4                     NESTED LOOPS (Cost=466 Card=4 Bytes=100)
6 5                         INDEX (UNIQUE SCAN) OF 'POINT_TZID' (UNIQUE) (Cost=2 Card=1 Bytes=6)
7 5                         TABLE ACCESS (FULL) OF 'CHAIN' (Cost=464 Card=4 Bytes=76)
8 2             BUFFER (SORT) (Cos t=725 Card=162344 Bytes=4058600)
9 8                 TABLE ACCESS (FULL) OF 'FEATURE' (Cost=64 Card=162344 Bytes=4058 600)
10 1         TABLE ACCESS (FULL) OF 'CHAINSIDE' (Cost=911 Card=2562340 Bytes=58933820)