Search

Top 60 Oracle Blogs

Recent comments

11.1.0.7

Multi-column joins

Consider the following scenario with four tables. Two of them represent master data, the third one uses a concatenated primary key consisting of foreign keys to the first two, and the fourth one has a foreign key to the third one.

drop table t1 cascade constraints purge;

drop table t2 cascade constraints purge;

drop table t3 cascade constraints purge;

drop table t4 cascade constraints purge;

create table t1 (
t1_id integer not null constraint pk_t1 primary key,
filler1 varchar2(40),
filler2 varchar2(40)
);

create table t2 (
t2_id integer not null constraint pk_t2 primary key,
filler1 varchar2(40),
filler2 varchar2(40)
);

create table t3 (
t1_id integer not null,
t2_id integer not null,
filler1 varchar2(40),
filler2 varchar2(40),
constraint pk_t3 primary key (t1_id, t2_id) using index (
create index pk_t3 on t3 (t1_id, t2_id)
),
constraint fk_t3_1 foreign key (t1_id) references t1 (t1_id),
constraint fk_t3_2 foreign key (t2_id) references t2 (t2_id));

create table t4 (
t4_id integer not null constraint pk_t4 primary key,
t1_id integer,
t2_id integer,
filler1 varchar2(40),
filler2 varchar2(40),
constraint t4_fk_1 foreign key (t1_id, t2_id) references t3 (t1_id, t2_id)
);

Notice that the primary key of "t3" is using a non-unique index, which is supported and can be used e.g. for deferrable constraints or when loading data into tables that might be non-unique so that the constraint can be disabled without dropping the (unique) index. This allows to simply re-enable the constraint after cleaning up the non-unique rows instead of re-creating an unique index (and the risk of losing the index if anything goes wrong).

Now when using an uncorrelated data set for the concatenated keys, Oracle's default (join) selectivity formulas apply and the estimated cardinalities are correct. Table "t1" has 10,000 rows, "t2" 3 rows, table "t3" holds 30,000 rows combining "t1" and "t2" data. "t4" has 300,000 rows.

-- non-correlated column values
exec dbms_stats.set_table_stats(null, 't1', numrows=>10000, numblks=>100, avgrlen=>100)

exec dbms_stats.set_table_stats(null, 't2', numrows=>3, numblks=>1, avgrlen=>100)

exec dbms_stats.set_table_stats(null, 't3', numrows=>30000, numblks=>300, avgrlen=>100)

exec dbms_stats.set_table_stats(null, 't4', numrows=>300000, numblks=>3000, avgrlen=>100)

exec dbms_stats.set_column_stats(null, 't1', 't1_id', distcnt=>10000, nullcnt=>0)

exec dbms_stats.set_column_stats(null, 't2', 't2_id', distcnt=>3, nullcnt=>0)

exec dbms_stats.set_column_stats(null, 't3', 't1_id', distcnt=>10000, nullcnt=>0)

exec dbms_stats.set_column_stats(null, 't3', 't2_id', distcnt=>3, nullcnt=>0)

exec dbms_stats.set_column_stats(null, 't4', 't4_id', distcnt=>300000, nullcnt=>0)

exec dbms_stats.set_column_stats(null, 't4', 't1_id', distcnt=>10000, nullcnt=>0)

exec dbms_stats.set_column_stats(null, 't4', 't2_id', distcnt=>3, nullcnt=>0)

exec dbms_stats.set_index_stats(null, 'pk_t1', numdist=>10000, clstfct=>10000, indlevel=>2, numlblks=>10, numrows=>10000)

exec dbms_stats.set_index_stats(null, 'pk_t2', numdist=>3, clstfct=>3, indlevel=>1, numlblks=>1, numrows=>3)

exec dbms_stats.set_index_stats(null, 'pk_t3', numdist=>30000, clstfct=>30000, indlevel=>2, numlblks=>30, numrows=>30000)

exec dbms_stats.set_index_stats(null, 'pk_t4', numdist=>300000, clstfct=>300000, indlevel=>2, numlblks=>300, numrows=>30000)

Joining t4 to t3 results in a correct estimate of 300K rows:

select
count(*)
from
t4
, t3
where
t3.t1_id = t4.t1_id
and t3.t2_id = t4.t2_id;

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | | 1456 (3)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | 52 | | | |
|* 2 | HASH JOIN | | 300K| 14M| 1120K| 1456 (3)| 00:00:18 |
| 3 | INDEX FAST FULL SCAN| PK_T3 | 30000 | 761K| | 11 (10)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T4 | 300K| 7617K| | 833 (3)| 00:00:10 |
----------------------------------------------------------------------------------------

Things look however different if we have the awkward situation of correlated column values for the concatenated keys:

-- correlated column values
exec dbms_stats.set_table_stats(null, 't1', numrows=>10000, numblks=>100, avgrlen=>100)

exec dbms_stats.set_table_stats(null, 't2', numrows=>20000, numblks=>200, avgrlen=>100)

exec dbms_stats.set_table_stats(null, 't3', numrows=>30000, numblks=>300, avgrlen=>100)

exec dbms_stats.set_table_stats(null, 't4', numrows=>300000, numblks=>3000, avgrlen=>100)

exec dbms_stats.set_column_stats(null, 't1', 't1_id', distcnt=>10000, nullcnt=>0)

exec dbms_stats.set_column_stats(null, 't2', 't2_id', distcnt=>20000, nullcnt=>0)

exec dbms_stats.set_column_stats(null, 't3', 't1_id', distcnt=>10000, nullcnt=>0)

exec dbms_stats.set_column_stats(null, 't3', 't2_id', distcnt=>20000, nullcnt=>0)

exec dbms_stats.set_column_stats(null, 't4', 't4_id', distcnt=>300000, nullcnt=>0)

exec dbms_stats.set_column_stats(null, 't4', 't1_id', distcnt=>10000, nullcnt=>0)

exec dbms_stats.set_column_stats(null, 't4', 't2_id', distcnt=>20000, nullcnt=>0)

exec dbms_stats.set_index_stats(null, 'pk_t1', numdist=>10000, clstfct=>10000, indlevel=>2, numlblks=>10, numrows=>10000)

exec dbms_stats.set_index_stats(null, 'pk_t2', numdist=>20000, clstfct=>20000, indlevel=>2, numlblks=>20, numrows=>20000)

exec dbms_stats.set_index_stats(null, 'pk_t3', numdist=>30000, clstfct=>30000, indlevel=>2, numlblks=>30, numrows=>30000)

exec dbms_stats.set_index_stats(null, 'pk_t4', numdist=>300000, clstfct=>300000, indlevel=>2, numlblks=>300, numrows=>30000)

Here we simulate 20,000 distinct values in one column, 10,000 distinct values in the second one, but only 30,000 distinct values for the combination of both columns. In this case Oracle's default selectivity formula underestimates the cardinality since it is assuming uncorrelated values:

select /*+ opt_param('_optimizer_join_sel_sanity_check', 'false') */
count(*)
from
t4
, t3
where
t3.t1_id = t4.t1_id
and t3.t2_id = t4.t2_id;

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | | 1455 (3)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | 52 | | | |
|* 2 | HASH JOIN | | 45 | 2340 | 1120K| 1455 (3)| 00:00:18 |
| 3 | INDEX FAST FULL SCAN| PK_T3 | 30000 | 761K| | 11 (10)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T4 | 300K| 7617K| | 833 (3)| 00:00:10 |
----------------------------------------------------------------------------------------

Oracle simply multiplies the selectivity of the two columns and arrives at a join cardinality of 45 rows (1/20,000*1/10,000*300,000*30,000).

You'll notice that I had to use a undocumented optimizer parameter to arrive at that default selectivity. If you run an EXPLAIN PLAN for the same statement without the hint, you'll get the following estimate:

select
count(*)
from
t4
, t3
where
t3.t1_id = t4.t1_id
and t3.t2_id = t4.t2_id;

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | | 1455 (3)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | 52 | | | |
|* 2 | HASH JOIN | | 30000 | 1523K| 1120K| 1455 (3)| 00:00:18 |
| 3 | INDEX FAST FULL SCAN| PK_T3 | 30000 | 761K| | 11 (10)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T4 | 300K| 7617K| | 833 (3)| 00:00:10 |
----------------------------------------------------------------------------------------

It can be seen from a 10053 optimizer trace file that Oracle uses a "Multi-column cardinality sanity check" by default in cases where the calculated multi-column selectivity falls below a certain limit, obviously using the smaller selectivity available from the different 1/num_rows of the tables/row sources involved in the join, arriving at an estimate 30,000 rows in this particular case.

Changing the non-unique index used for the primary key on "t3" to a unique index will bring another sanity check into the picture: The "concatenated index" sanity check that uses the number of distinct values of an unique index that corresponds exactly to the join columns used.

create table t3 (
t1_id integer not null,
t2_id integer not null,
filler1 varchar2(40),
filler2 varchar2(40),
constraint pk_t3 primary key (t1_id, t2_id) using index (
create unique index pk_t3 on t3 (t1_id, t2_id)
),
constraint fk_t3_1 foreign key (t1_id) references t1 (t1_id),
constraint fk_t3_2 foreign key (t2_id) references t2 (t2_id));

With this unique index in place Oracle uses the number of distinct keys from this index to calculate the selectivity of the join and therefore arrives at the correct cardinality again:

select
count(*)
from
t4
, t3
where
t3.t1_id = t4.t1_id
and t3.t2_id = t4.t2_id;

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | | 1455 (3)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | 52 | | | |
|* 2 | HASH JOIN | | 300K| 14M| 1120K| 1455 (3)| 00:00:18 |
| 3 | INDEX FAST FULL SCAN| PK_T3 | 30000 | 761K| | 11 (10)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T4 | 300K| 7617K| | 833 (3)| 00:00:10 |
----------------------------------------------------------------------------------------

So this is another case where the uniqueness of an index makes a significant difference for optimizer calculations.

Note that from 11g on there more options to help the optimizer to come up with a better estimate even with the non-unique index on (t3.t1_id, t3.t2_id). Obviously 11g introduced extended statistics on column groups, so we can do the following:

variable ext_name varchar2(30)

exec :ext_name := dbms_stats.create_extended_stats(null, 't3', '(t1_id, t2_id)')

exec dbms_stats.set_column_stats(null, 't3', :ext_name, distcnt=>30000, nullcnt=>0)

This allows to derive the correct selectivity for these correlated column values using the extended statistics set.

Another option in 11g is adding an index on (t4.t1_id, t4.t2_id), like that:

create index ix_t4 on t4 (t1_id, t2_id);

exec dbms_stats.set_index_stats(null, 'ix_t4', numdist=>30000, clstfct=>30000, indlevel=>2, numlblks=>30, numrows=>300000)

Having now two non-unique indexes Oracle 11g comes up again with the correct join cardinality of 300K. Notice that this doesn't work in pre-11g. Pre-11g versions require the index on t3 to be unique to take advantage of the "concatenated index" sanity check.

Having demonstrated all these sanity checks available for multi-column joins (the general multi-column and the concatenated index sanity check), let's see what happens when joining three tables:

select
count(*)
from
t1
, t3
, t4
where
t4.t1_id = t1.t1_id
and t3.t1_id = t4.t1_id
and t3.t2_id = t4.t2_id;

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | | 1468 (4)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | 65 | | | |
|* 2 | HASH JOIN | | 300K| 18M| | 1468 (4)| 00:00:18 |
| 3 | INDEX FAST FULL SCAN | PK_T1 | 10000 | 126K| | 4 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 300K| 14M| 1120K| 1455 (3)| 00:00:18 |
| 5 | INDEX FAST FULL SCAN| PK_T3 | 30000 | 761K| | 11 (10)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T4 | 300K| 7617K| | 833 (3)| 00:00:10 |
-----------------------------------------------------------------------------------------

All I've done is to add "t1", in this case joining to "t4" on "t1_id". Thanks to the concatenated index sanity check (or the extended column group statistics in 11g) the calculated join cardinality is still 300K.

Now what happens if one decides to join "t3" to "t1" on "t1_id" instead of "t4.t1_id"? From a logical point of view this should lead to exactly the same result, since we can deduce that if "t4.t1_id" = "t1.t1_id" and "t3.t1_id = t1.t1_id" then "t3.t1_id = t4.t1_id".

select
count(*)
from
t1
, t3
, t4
where
t4.t1_id = t1.t1_id
and t3.t1_id = t1.t1_id
and t3.t2_id = t4.t2_id;

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | | 1475 (4)| 00:00:18 |
| 1 | SORT AGGREGATE | | 1 | 65 | | | |
|* 2 | HASH JOIN | | 45 | 2925 | | 1475 (4)| 00:00:18 |
| 3 | INDEX FAST FULL SCAN | PK_T1 | 10000 | 126K| | 4 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 450K| 22M| 1120K| 1459 (3)| 00:00:18 |
| 5 | INDEX FAST FULL SCAN| PK_T3 | 30000 | 761K| | 11 (10)| 00:00:01 |
| 6 | TABLE ACCESS FULL | T4 | 300K| 7617K| | 833 (3)| 00:00:10 |
-----------------------------------------------------------------------------------------

The result is astonishing. By making this simple change we have effectively disabled all available sanity checks and arrive at the result based on the the default, uncorrelated selectivity.

So whenever you perform multi-column joins and the column data is correlated, be very careful how you join the tables - it might make a significant difference to the calculations of the optimizer.

Optimizer cleverness

At present I'm quite busy and therefore don't have much time to spent on writing blog notes, but I couldn't resist to publish this small and simple test case.

Often you can read (mostly unqualified) rants in various places and forums about the Cost Based Optimizer how stupid, unpredictable etc. it seems to be.

So I think it's time to demonstrate how clever the optimizer sometimes can be.

Consider the following setup:

drop table t_opt_clever purge;

-- Use PCTFREE 99 so that only one row per (leaf) block
-- This can tell us how many "rows" had to be inspected
-- by checking the number of (leaf) blocks accessed
-- Unfortunately Oracle (usually) doesn't provide the information
-- how many rows have been accessed in the execution plan,
-- but only how many rows are returned by an operation
create table t_opt_clever (
id not null constraint pk_opt_clever primary key,
col1 not null,
col2 not null,
col3 not null,
col4 not null,
col5 not null,
filler
)
pctfree 99
pctused 1
as
select
level as id
, round(dbms_random.value(0, 200)) as col1
, round(dbms_random.value(0, 400)) as col2
, case
when level <= 666
then 'FIRST_BUCKET'
when level <= 833
then 'SECOND_BUCKET'
when level <= 1000
then 'THIRD_BUCKET'
end as col3
, round(dbms_random.value(0, 600)) as col4
, round(dbms_random.value(0, 800)) as col5
, rpad('x', 100, 'x') as filler
from
dual
connect by
level <= 1000;

create index idx_opt_clever1 on t_opt_clever (col5, col1, col4, col2) pctfree 99 compute statistics;

create index idx_opt_clever2 on t_opt_clever (col5, col1, col3, col4, col2) pctfree 99 compute statistics;

exec dbms_stats.gather_table_stats(null, 'T_OPT_CLEVER')

-- scale the table and index by factor 1000
exec dbms_stats.set_table_stats(null, 'T_OPT_CLEVER', numrows => 1000000, numblks => 30000)

exec dbms_stats.set_index_stats(null, 'PK_OPT_CLEVER', numrows=> 1000000, numlblks => 2000, numdist=>1000000, clstfct => 100000, indlevel => 3)

exec dbms_stats.set_index_stats(null, 'IDX_OPT_CLEVER1', numrows=> 1000000, numlblks => 14000, numdist=>1000000, clstfct => 1000000, indlevel => 3)

exec dbms_stats.set_index_stats(null, 'IDX_OPT_CLEVER2', numrows=> 1000000, numlblks => 16000, numdist=>1000000, clstfct => 1000000, indlevel => 3)

Basically this simulates a 1,000,000 rows table with two suboptimal indexes given the following Top 100 query:

-- Now which index can be efficiently used by the optimizer?
select
*
from (
select
*
from
t_opt_clever
where
col3 = 'FIRST_BUCKET'
order by
col3, col5, col1, col4, col2
)
where
rownum <= 100;

Now what do you think, can one of these indexes efficiently be used by the optimizer, and if yes, which one?

At first sight both indexes can't be used to satisfy the requested sort order to avoid a costly full scan of data and a corresponding SORT ORDER BY (STOPKEY) operation, and can't be used efficiently to filter the data because the filter predicate is not among the leading columns.

Let's check the result:

SQL> select * from table(dbms_xplan.display_cursor(null, null, '+COST ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fz6vky8n5a3xq, child number 0
-------------------------------------
select * from ( select * from t_opt_clever where
col3 = 'FIRST_BUCKET' order by col3, col5, col1, col4, col2 ) where
rownum <= 100

Plan hash value: 4203008252

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | | 100 |00:00:00.29 | 256 | 100 |
| 2 | VIEW | | 1 | 101 | 109 (0)| 100 |00:00:00.29 | 256 | 100 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_OPT_CLEVER | 1 | 333K| 109 (0)| 100 |00:00:00.29 | 256 | 100 |
|* 4 | INDEX FULL SCAN | IDX_OPT_CLEVER2 | 1 | 101 | 8 (0)| 100 |00:00:00.01 | 156 | 0 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=100)
4 - access("COL3"='FIRST_BUCKET')
filter("COL3"='FIRST_BUCKET')

24 rows selected.

That is quite interesting, the index IDX_OPT_CLEVER2 is used and no SORT ORDER BY operation can be found in the execution plan, although the index doesn't match the requested sort order. And here comes the cleverness of the optimizer: It recognizes that due to the filter predicate on COL3 this index can actually be used to satisfy the sort order because it is not relevant for the resulting order since COL3 will always be the constant value of the filter predicate. And the same applies to IDX_OPT_CLEVER1, by the way.

But IDX_OPT_CLEVER2 is more efficient than using IDX_OPT_CLEVER1 because the filter predicate can be evaluated on the index data already eliminating some of the rows before visiting the table. Depending on the clustering factor this can make a significant difference to the cost of the operation, since random row accesses to table rows potentially require to access a different block per row.

This can be seen when forcing the usage of IDX_OPT_CLEVER1:

SQL> select * from table(dbms_xplan.display_cursor(null, null, '+COST ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5tgmgfvyyx6z6, child number 0
-------------------------------------
select * from ( select /*+ index(t_opt_clever idx_opt_clever1) */ * from
t_opt_clever where col3 = 'FIRST_BUCKET' order by col3,
col5, col1, col4, col2 ) where rownum <= 100

Plan hash value: 678132971

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | | 100 |00:00:00.20 | 310 | 54 |
| 2 | VIEW | | 1 | 101 | 312 (1)| 100 |00:00:00.20 | 310 | 54 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T_OPT_CLEVER | 1 | 101 | 312 (1)| 100 |00:00:00.20 | 310 | 54 |
| 4 | INDEX FULL SCAN | IDX_OPT_CLEVER1 | 1 | 1000K| 8 (0)| 154 |00:00:00.01 | 156 | 0 |
---------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=100)
3 - filter("COL3"='FIRST_BUCKET')

23 rows selected.

Two things can be seen here:

1. The optimizer is again smart and is able to avoid the SORT ORDER BY operation, because the index IDX_OPT_CLEVER1 can also be used to return in the data in the requested order, again because COL3 is constant.

2. Using IDX_OPT_CLEVER1 is less efficient because more table rows have to be visited to apply the filter predicate.

The fact that the indexes can only be used efficiently under this special circumstance can be verified by changing the filter predicate so that COL3 can have more than a single value and therefore it's no longer possible to avoid an ORDER BY operation:

-- Change the filter predicate and force index
select
*
from (
select /*+ index(t_opt_clever idx_opt_clever2) */
*
from
t_opt_clever
where
col3 in ('FIRST_BUCKET', 'SECOND_BUCKET')
order by
col5, col1, col4, col2
)
where
rownum <= 100;
SQL> select * from table(dbms_xplan.display_cursor(null, null, '+COST ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID axr6u0yvdk50f, child number 0
-------------------------------------
select * from ( select /*+ index(t_opt_clever idx_opt_clever2) */ * from
t_opt_clever where col3 in ('FIRST_BUCKET', 'SECOND_BUCKET') order by col3, col5, col1,
col4, col2 ) where rownum <= 100

Plan hash value: 2229390605

----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | | 100 |00:00:00.02 | 1835 | | | |
| 2 | VIEW | | 1 | 666K| 703K (1)| 100 |00:00:00.02 | 1835 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 666K| 703K (1)| 100 |00:00:00.02 | 1835 | 20480 | 20480 |18432 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| T_OPT_CLEVER | 1 | 666K| 683K (1)| 833 |00:00:00.01 | 1835 | | | |
|* 5 | INDEX FULL SCAN | IDX_OPT_CLEVER2 | 1 | 666K| 16100 (1)| 833 |00:00:00.01 | 1002 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<=100)
3 - filter(ROWNUM<=100)
5 - filter(("COL3"='FIRST_BUCKET' OR "COL3"='SECOND_BUCKET'))

25 rows selected.

Without the index hint the optimizer chooses a full table scan. Forcing e.g. the index IDX_OPT_CLEVER2 shows that indeed all rows had to be processed first and additionally a sort operation was necessary.

So it's interesting to note that the optimizer recognizes special cases where single value predicates allow an index usage that otherwise wouldn't be possible. This is a nice move, since it allows to perform above query in quite an efficient manner although the setup is suboptimal (e.g. a different index with COL3 as leading column or an appropriate IOT could be more suitable, depending on what else is done with the table). Under these (simulated) circumstances this optimization makes quite a difference compared to the otherwise only possible full table scan operation of a 30,000 blocks table.

By the way, above results could be reproduced on 10.2.0.4 and 11.1.0.7 Win32 using default system statistics and an 8KB LMT MSSM tablespace.

Locked table statistics and subsequent create index

Just a minor thing to consider: By default in 10g and later index statistics are generated along with an index creation (option COMPUTE STATISTICS in previous releases enabled by default), so a newly created index usually has computed statistics.

10g also introduced the option to lock table statistics.

Now if you lock statistics in 10g in later using DBMS_STATS.LOCK_TABLE_STATS or LOCK_SCHEMA_STATS and create an index on a locked table the statistics for the index will not be generated along with the CREATE INDEX command. Unfortunately there is no corresponding "FORCE" option in CREATE INDEX available to overwrite that behaviour that I'm aware of so it looks like you're only left with two choices:

1. Use a separate DBMS_STATS.GATHER_INDEX_STATS call with the FORCE=>true option to override the lock on the statistics

2. Temporarily unlock the table statistics before creating the index

The first option can be costly if the index is large, the second option requires additional steps to be taken, and it obviously needs to be ensured that the table statistics are not modified while they are unlocked (e.g. by the default statistics job in 10g and later).

A small testcase run on 10.2.0.4 Win32 follows to demonstrate the issue. I got the same result on 11.1.0.7 Win32.

SQL>
SQL> drop table lock_test purge;

Table dropped.

SQL>
SQL> create table lock_test
2 as
3 select
4 *
5 from
6 all_objects
7 where
8 rownum <= 1000;

Table created.

SQL>
SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> create index lock_test_idx on lock_test (object_name) compute statistics;
create index lock_test_idx on lock_test (object_name) compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

SQL>
SQL> create index lock_test_idx on lock_test (object_name);

Index created.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------

SQL>
SQL> exec dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX')
BEGIN dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10872
ORA-06512: at "SYS.DBMS_STATS", line 10896
ORA-06512: at line 1

SQL>
SQL> exec dbms_stats.gather_index_stats(null, 'LOCK_TEST_IDX', force=>true)

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------
1000 21.06.09

SQL>
SQL> drop index lock_test_idx;

Index dropped.

SQL>
SQL> exec dbms_stats.unlock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> create index lock_test_idx on lock_test (object_name);

Index created.

SQL>
SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------
1000 21.06.09

SQL>
SQL> delete from lock_test where rownum <= 500;

500 rows deleted.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> alter index lock_test_idx rebuild compute statistics;
alter index lock_test_idx rebuild compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

SQL>
SQL> alter index lock_test_idx rebuild;

Index altered.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------
1000 21.06.09

SQL>
SQL> exec dbms_stats.unlock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> alter index lock_test_idx rebuild;

Index altered.

SQL>
SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')

PL/SQL procedure successfully completed.

SQL>
SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX';

NUM_ROWS LAST_ANA
---------- --------
500 21.06.09

SQL>
SQL> analyze index lock_test_idx compute statistics;
analyze index lock_test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

SQL>
SQL> spool off

The same applies to index rebuilds obviously.

Consequently the hopefully no longer used ANALYZE INDEX ESTIMATE/COMPUTE STATISTICS command can also not be used on locked tables and its indexes.

Temporary Tablespace Groups

Oracle 10g introduced the concept of temporary tablespace groups.

These allow to group multiple temporary tablespaces into a single group and assign a user this group of tablespaces instead of a single temporary tablespace.

This raises some interesting questions, and for some of these I don't find answers in the official documentation. Some of these questions are:

- Can a single workarea execution allocate space from more than one temporary tablespace, e.g. to support large serial sort operations?

A workarea belongs to a single operation of an execution plan. There are several different types of operations that require a workarea, among them are sorts, hash joins, group bys and sort/merge joins.

This workarea can fit into available PGA memory, but can also spill to disk in case there is insufficient memory available to support the operation.

Furthermore this implies that a execution of a single SQL statement can require multiple workareas, e.g. a quite simple statement might need two workareas for two hash joins and a third one for a subsequent sort order by operation.

Note that there are other types of operations that don't require a workarea, e.g. a nested loop join doesn't require a workarea (and therefore will never acquire temporary space).

Details about workareas can be obtained from various dynamic performance views, e.g. V$SQL_WORKAREA, V$SQL_WORKAREA_ACTIVE and V$SQL_WORKAREA_HISTOGRAM.

- Can multiple workareas of a single session allocate space from different tablespaces?

- According to the documentation different sessions of the same user can use different temporary tablespaces from the group. Is this correct?

- What about parallel execution? The documentation says that the parallel slaves can use different temporary tablespaces. Is this correct?

Here's a simple testcase that generates two small temporary tablespaces and assigns them to the same group. A test user is assigned first a single temporary tablespace and afterwards the group to see if this makes any difference.

It uses a carefully crafted table for which a sort operation doesn't fit into a single small temporary tablespace but is supposed to fit if both temporary tablespaces of the group can be used.

The session is deliberately using a very small sort_area_size in manual workarea policy mode to force the sort operation to spill to disk.

The following are the results from 11.1.0.7 Win32. Similar results can be seen from 10.2.0.4 Win32. The database used a 8KB default block size and a locally managed tablespace with manual segment space management (no ASSM) for the data but that shouldn't matter here much.

SQL>
SQL> drop tablespace temp1_grp including contents and datafiles;

Tablespace dropped.

SQL>
SQL> drop tablespace temp2_grp including contents and datafiles;

Tablespace dropped.

SQL>
SQL> create temporary tablespace temp1_grp
2 tempfile 'C:\ORACLE\ORADATA\ORCL11\TEMP1_GRP.DBF'
3 size 2M reuse uniform size 100K
4 tablespace group temp_grp;

Tablespace created.

SQL>
SQL> create temporary tablespace temp2_grp
2 tempfile 'C:\ORACLE\ORADATA\ORCL11\TEMP2_GRP.DBF'
3 size 2M reuse uniform size 100K
4 tablespace group temp_grp;

Tablespace created.

SQL>
SQL> drop user tempgrp_user cascade;

User dropped.

SQL>
SQL> create user tempgrp_user identified by tempgrp_user;

User created.

SQL>
SQL> grant create session to tempgrp_user;

Grant succeeded.

SQL>
SQL> exec dbms_random.seed(0)

PL/SQL procedure successfully completed.

SQL>
SQL> drop table test_temp_grp purge;

Table dropped.

SQL>
SQL> create table test_temp_grp
2 as
3 select
4 sys.dbms_random.string('U', 140) as object_name
5 from
6 dual
7 connect by
8 rownum <= 10000;

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'TEST_TEMP_GRP')

PL/SQL procedure successfully completed.

SQL>
SQL> select blocks from dba_tables where table_name = 'TEST_TEMP_GRP';

BLOCKS
----------
205

SQL>
SQL> grant select on test_temp_grp to tempgrp_user;

Grant succeeded.

SQL>
SQL> create or replace synonym tempgrp_user.test_temp_grp for test_temp_grp;

Synonym created.

SQL>
SQL> -- This is the behaviour when using traditional tablespaces
SQL> alter user tempgrp_user temporary tablespace temp1_grp;

User altered.

SQL>
SQL> connect tempgrp_user/tempgrp_user
Connect durchgeführt.
SQL>
SQL> alter session set nls_language = 'AMERICAN';

Session altered.

SQL>
SQL> variable r refcursor
SQL>
SQL> variable t refcursor
SQL>
SQL> -- Force sort on disk
SQL> alter session set workarea_size_policy = manual sort_area_size = 65536;

Session altered.

SQL>
SQL> exec open :r for select * from test_temp_grp order by object_name

PL/SQL procedure successfully completed.

SQL>
SQL> -- Sort doesn't fit into available temp space
SQL> declare
2 rec test_temp_grp%rowtype;
3 begin
4 fetch :r into rec;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 13 in tablespace TEMP1_GRP
ORA-06512: at line 4

SQL>
SQL> exec open :r for select * from test_temp_grp where rownum <= 5000 order by object_name

PL/SQL procedure successfully completed.

SQL>
SQL> -- This works
SQL> declare
2 rec test_temp_grp%rowtype;
3 begin
4 fetch :r into rec;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> connect cbo_test/cbo_test
Connect durchgeführt.
SQL>
SQL> alter session set nls_language = 'AMERICAN';

Session altered.

SQL>
SQL> -- Switch to tablespace group
SQL> alter user tempgrp_user temporary tablespace temp_grp;

User altered.

SQL>
SQL> connect tempgrp_user/tempgrp_user
Connect durchgeführt.
SQL>
SQL> alter session set nls_language = 'AMERICAN';

Session altered.

SQL>
SQL> -- Force sort on disk
SQL> alter session set workarea_size_policy = manual sort_area_size = 65536;

Session altered.

SQL>
SQL> exec open :r for select * from test_temp_grp order by object_name

PL/SQL procedure successfully completed.

SQL>
SQL> -- Sort still doesn't fit into available temp space (but it should as you'll see later)
SQL> -- so obviously a single workarea can't use space from different tablespaces
SQL> declare
2 rec test_temp_grp%rowtype;
3 begin
4 fetch :r into rec;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 13 in tablespace TEMP1_GRP
ORA-06512: at line 4

SQL>
SQL> -- What if we have multiple workareas per session
SQL> exec open :r for select * from test_temp_grp where rownum <= 5000 order by object_name

PL/SQL procedure successfully completed.

SQL>
SQL> -- First one works
SQL> declare
2 rec test_temp_grp%rowtype;
3 begin
4 fetch :r into rec;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> exec open :t for select * from test_temp_grp where rownum <= 5000 order by object_name

PL/SQL procedure successfully completed.

SQL>
SQL> -- Second one fails
SQL> declare
2 rec test_temp_grp%rowtype;
3 begin
4 fetch :t into rec;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 13 in tablespace TEMP1_GRP
ORA-06512: at line 4

SQL>
SQL> -- So it looks like a session is limited to a particular tablespace taken from the group
SQL> -- What happens to a second session of the same user
SQL> -- One tablespace is still in use by cursor R
SQL> -- Check V$SORT_USAGE if interested
SQL>
SQL> -- Run the same query in second session
SQL> -- You might need multiple attempts to get this working
SQL> -- Depending on the tablespace taken from the group
SQL> -- and then close second session to free temp space
SQL> -- Press ENTER to continue
SQL>
SQL> pause

SQL>
SQL> -- This works if the second session is assigned a different tablespace taken from the group
SQL> -- So different sessions of the same user will potentially be assigned to different tablespaces
SQL>
SQL> -- What about parallel execution
SQL> exec close :r

PL/SQL procedure successfully completed.

SQL>
SQL> exec open :r for select /*+ parallel(test_temp_grp, 2) */ * from test_temp_grp order by object_name

PL/SQL procedure successfully completed.

SQL>
SQL> -- This works
SQL> -- So the PX sessions can be assigned to different tablespaces taken from the group
SQL> -- Check V$SORT_USAGE if interested
SQL> declare
2 rec test_temp_grp%rowtype;
3 begin
4 fetch :r into rec;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> spool off

So in summary the following can be deduced:

1. A single workarea execution can't use space from more a single tablespace even when the user is assigned a temporary tablespace group. Or in other words: A single workarea execution can only allocate a single temporary segment, and segments in general can't span multiple tablespaces in Oracle, i.e. they must fit into a single tablespace.

2. Even multiple workareas for a single session can't use space from the multiple temporary tablespaces available. So this looks like a session attribute, i.e. assigned to the session, and all workareas of that session will use the single temporary tablespace assigned (taken from the group)

3. Multiple sessions of the same user can be assigned to different temporary tablespaces taken from the group.

4. Parallel execution slave sessions can do the same, they can be assigned to different temporary tablespaces taken from the group.

So in order to leverage the power of temporary tablespace groups you need either multiple sessions running serial SQL or the parallel execution option.

User objects created in the SYS schema and the (cost based) optimizer

The answer - as already disclosed by Nicolas Gasparotto - to the question that I asked here in the "Weekend Quiz" is to run the script as SYS user, and then run the query shown against these objects in the SYS schema (tested against 10g XE, 10.2.0.4 and 11.1.0.7 on Win32).

Note: It's not recommended to create any non-SYS objects in the SYS schema and you should only perform this (if at all) in a test database.

All this came up in this recent OTN forum thread where it became obvious that the issue can only be reproduced if the objects are owned by SYS.

There are two interesting points to derive from this (apart from the obvious that one should not create any user objects in the SYS schema):

1. The optimizer seems to treat objects owned by SYS differently, in particular regarding the transformations applied. Note that the crucial point is not that the query is executed as SYS user, but that the objects are owned by the SYS user. Granting appropriate privileges to a non-SYS user on the objects owned by SYS allows to reproduce the issue even with a non-SYS user.

2. It's something to remind if there is the need to understand a performance issue with a recursive dictionary query performed on SYS-owned objects. Although you obviously can't influence the SQL generated by Oracle itself it might help to understand the issue and take appropriate steps to rectify the issue.

Oh, by the way, have I already mentioned that it's really a bad idea to create user objects in the SYS schema?

Read Consistency, "ORA-01555 snapshot too old" errors and the SCN_ASCENDING hint

Oracle uses for its read consistency model a true multi-versioning approach which allows readers to not block writers and vice-versa, writers to not block readers. Obviously this great feature allowing highly concurrent processing doesn't come for free, since somewhere the information to build multiple versions of the same data needs to be stored.

Oracle uses the so called undo information not only to rollback on-going transactions but also to re-construct old versions of blocks if required. Very simplified when reading data Oracle knows the point in time (which corresponds to an internal counter called SCN, System Change Number) that data needs to be consistent with. In the default READ COMMITTED isolation mode this point in time is defined when a statement starts to execute. You could also say at the moment a statement starts to run its result is pre-ordained. When Oracle processes a block it checks if the block is "old" enough and if it discovers that the block content is too new (has been changed by other sessions but the current access is not supposed to see this updated content according to the point-in-time assigned to the statement execution) it will start to create a copy of the block and use the information available from the corresponding undo segment to re-construct an older version of the block. Note that this process can be iterative: If after re-constructing the older version of the block it's still not sufficiently old more undo information will be used to go further back in time.

Optimizer partition oddities, part 2: List partitioning

Back to part 1

Some time ago on the OTN forum the following table layout was part of a discussion regarding performance issues and it revealed an interesting anomaly regarding list partition pruning:

If you're using list partitioning with partitions that use multiple values that map to a single list partition then the optimizer obviously uses a questionable approach when you're using multiple values on the partition key to prune to a single partition.

Consider the following table layout:

CREATE TABLE XYZ
(
TICKER VARCHAR2(22 BYTE) NOT NULL,
EXCH_CODE VARCHAR2(25 BYTE) NOT NULL,
ID_ISIN VARCHAR2(12 BYTE),
HIGH_52WEEK NUMBER(28,10),
LOW_52WEEK NUMBER(28,10),
PX_OPEN NUMBER(28,10),
PX_HIGH NUMBER(28,10),
BLOOMBERG_FILE_SOURCE VARCHAR2(100 BYTE),
LATEST_VERSION_FLAG CHAR(1 BYTE)
)
PARTITION BY LIST (EXCH_CODE)
(
PARTITION BBO_ASIA VALUES ('SL','IS','SP','JF','JN','PK','KP','VM','JS','IN','TB','KQ','JP','NV','JJ','MK','HK','IJ','JT','TT','PA','CS','JX',
'IB','AU','FS','VN','NZ','KS','PM','CH','BD','JQ','VH','CG','JO')
,
PARTITION BBO_NAMR VALUES ('UO','US','UN','PQ','TR','UD','UP','TX','UL','UB','UU','UX','UT','TN','UQ','UR','UW','UV','TA','CT','CV','UC','CJ','UA','UM','CN','UF','CF')
,
PARTITION BBO_LAMR VALUES ('AR','BS','AC','CR','EG','EK','VB','BN','EQ','PE','AF','CX','KY','CC','MM','BM','TP','BV','BH','UY','BZ','ED','VC','VS','BO','CI','CB','PP','BA','JA','CE')
,
PARTITION BBO_EURO VALUES (DEFAULT)
);

I'm now going to populate that table using this sample data: