Sunday, December 7, 2008

Concurrency issues - when do modifications to different rows of a table block each other?

Oracle's implementation of row level locking offers a high level of concurrency since you usually don't encounter blocking issues from concurrent modifications to different rows of a table, that could be caused e.g. by lock escalations (row locks to "page" or "block" locks, "block" locks to "table locks" etc.) that some DBMS products perform if too many low level locks are requested and a centralized lock management unit (usually a "lock manager") is unable to cope with the amount of locks. Since Oracle stores the "row level lock" information right in the block itself, it doesn't suffer from such scalability issues.

But there are certain scenarios in Oracle which still can lead to locking issues, even if different rows of a table are modified concurrently.

The following scenarios should be briefly outlined and demonstrated here along with possible remedies and supporting information how to identify if such an issue is encountered:

- Same unique key written by different sessions
- Unindexed foreign keys and modifications to parent table primary keys
- Insufficient block space and ITL slot shortage
- Bitmap indexes
- Segments with low MAXTRANS settings (Only pre-10g)

Same unique key written by different sessions

If multiple sessions attempt to write the same unique key but haven't yet completed the transactions, they will block each other, because depending on the completion of the transactions (commit or rollback) some might succeed or fail. This can be caused by updates, inserts and deletes.

The following test case run against 11.1.0.7 demonstrates the issue:


SQL>
SQL> create table unique_test as
2 select object_id as id, object_name as name
3 from all_objects
4 where rownum <= 10;

Table created.

SQL>
SQL> alter table unique_test
2 add constraint pk_unique_test
3 primary key (id);

Table altered.

SQL> -- perform this in session 1
SQL> insert into unique_test (id, name) values (100, 'NASE');

1 row created.

SQL>
SQL> pause

SQL> -- perform this in session 2
SQL> insert into unique_test (id, name) values (100, 'NASE');


Diagnosis:


SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
2 sid sess, id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request
> 0)
5 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder: 134 589845 1681 6 0 TX
Waiter: 140 589845 1681 0 4 TX

SQL> select eq_name, eq_type, req_reason
2 from v$enqueue_statistics
3 where eq_type = 'TX'
4 and req_reason = 'row lock contention';

EQ_NAME EQ REQ_REASON
-------------------- -- --------------------
Transaction TX row lock contention

SQL>
SQL> select event, total_waits from v$system_event
2 where event = 'enq: TX - row lock contention';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TX - row lock contention 2

SQL>
SQL> select event, total_waits from v$session_event
2 where event = 'enq: TX - row lock contention';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TX - row lock contention 1

SQL>



There is no easy remedy in this case, and if this happens frequently then it could be called a design flaw of the application (or it is intended behaviour). One potential workaround could be the definition of the primary key as deferred, but this has other significant implications, e.g. direct-path inserts might not work (silently fall back to conventional inserts, depending on the Oracle version) and the cost based optimizer treats deferrable constraints differently which could cause execution plan changes.

Unindexed foreign keys and modifications to parent table primary keys

If referential constraints have been defined between tables and the foreign key columns in the child table are not indexed, then modifications to the parent table's key cause locks on the child table, and if the foreign key columns are not indexed this can cause contention.

The following example run against 11.1.0.7 demonstrates the issue:


SQL>
SQL> create table parent_table
2 as
3 select distinct object_type
4 from all_objects;

Table created.

SQL>
SQL> create table child_table
2 as
3 select object_id, object_type, object_name
4 from all_objects
5 where rownum <= 10000;

Table created.

SQL>
SQL> alter table parent_table
2 add constraint pk_parent_table
3 primary key (object_type);

Table altered.

SQL>
SQL> alter table child_table
2 add constraint pk_child_table
3 primary key (object_id);

Table altered.

SQL>
SQL> alter table child_table
2 add constraint fk_child_table_parent_table
3 foreign key (object_type) references parent_table
4 on delete cascade;

Table altered.

SQL>
SQL> select count(*), object_type
2 from child_table
3 group by object_type;

COUNT(*) OBJECT_TYPE
---------- -------------------
1 EDITION
52 INDEX PARTITION
96 SEQUENCE
63 TABLE PARTITION
58 PROCEDURE
2 OPERATOR
468 PACKAGE
305 PACKAGE BODY
124 LIBRARY
44 TYPE BODY
1 TRIGGER
880 TABLE
972 INDEX
2849 SYNONYM
3278 VIEW
60 FUNCTION
10 CLUSTER
737 TYPE

18 rows selected.

SQL>
SQL> -- perform this in session 1
SQL> delete from parent_table where object_type = 'EDITION';

1 row deleted.

SQL>

-- and this in session 2
delete from parent_table where object_type = 'OPERATOR';


Although different rows in the parent and child table are affected by the two concurrent operations, session 2 is blocked.

Diagnosis:


SQL>
SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
2 sid sess, id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request
> 0)
5 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder: 140 74233 0 3 0 TM
Waiter: 144 74233 0 0 5 TM

SQL>

SQL>
SQL> select event, total_waits from v$system_event
2 where event = 'enq: TM - contention';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TM - contention 1

SQL>
SQL> select event, total_waits from v$session_event
2 where event = 'enq: TM - contention';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TM - contention 1

SQL>

SQL> select eq_name, eq_type, req_reason
2 from v$enqueue_statistics
3 where eq_type = 'TM';

EQ_NAME EQ REQ_REASON
-------------------- -- --------------------
DML TM contention


The remedy in this case is simple, create an index on the foreign key columns of the child table:


SQL> -- indexing the foreign key column in the child table
SQL> -- solves the issue
SQL> create index fk_child_table_parent_table on child_table(object_type);

Index created.

SQL> -- perform this in session 1
SQL> delete from parent_table where object_type = 'EDITION';

1 row deleted.

SQL>

SQL> -- and this in session 2
SQL> delete from parent_table where object_type = 'OPERATOR';

1 row deleted.

SQL>


As you can see session 2 is now no longer blocked.

Insufficient block space and ITL slot shortage

If multiple transactions attempt to modify the same block, they can block each other if the following conditions are fulfilled:

- There is no free ITL ("Interested Transaction List") slot available. Oracle records the lock information right in the block and each transactions allocates an ITL entry.

- Insufficient space in the block left to add a new ITL slot. Since each ITL entry requires a couple of bytes a new one cannot be created if the block doesn't have sufficient free space.

The INITRANS and MAXTRANS settings of a segment control the initial and maximum number of ITL slots per block. The default of INITRANS in recent Oracle releases is 1 resp. 2 for indexes and the default value for MAXTRANS is 255 since the 10g release.

The following example demonstrates the issue. A block is almost full and several transactions attempt to manipulate different rows that all reside in this block.


SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL>
SQL> create table t1 (c1 varchar2(1336)) pctfree 10;

Table created.

SQL>
SQL> insert into t1 values ('a');

1 row created.

SQL>
SQL> insert into t1 values ('b');

1 row created.

SQL>
SQL> insert into t1 values ('c');

1 row created.

SQL>
SQL> insert into t1 values ('d');

1 row created.

SQL>
SQL> insert into t1 values ('e');

1 row created.

SQL>
SQL> insert into t1 values ('e');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- increase the size of the rows
SQL> -- so that no space is left in the block
SQL> update t1 set c1 = rpad(c1, 1335, c1)
2 where length(c1) = 1;

6 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select substr(c1,1,1) from t1 where c1 like 'a%' for update nowait;

S
-
a

SQL> -- second session
SQL> select substr(c1,1,1) from t1 where c1 like 'b%' for update nowait;

S
-
b

SQL>

SQL> -- third session
SQL> select substr(c1,1,1) from t1 where c1 like 'c%' for update nowait;
select substr(c1,1,1) from t1 where c1 like 'c%' for update nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL>
SQL> update t1 set c1 = c1
2 where c1 like 'c%';


Diagnosis:


SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
2 sid sess, id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request
> 0)
5 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder: 138 393224 1330 6 0 TX
Waiter: 140 393224 1330 0 4 TX

SQL> select eq_name, eq_type, req_reason
2 from v$enqueue_statistics
3 where eq_type = 'TX'
4 and req_reason = 'allocate ITL entry';

EQ_NAME EQ REQ_REASON
-------------------- -- --------------------
Transaction TX allocate ITL entry

SQL>

SQL> select event, total_waits from v$system_event
2 where event = 'enq: TX - allocate ITL entry';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TX - allocate ITL entry 2

SQL>
SQL> select event, total_waits from v$session_event
2 where event = 'enq: TX - allocate ITL entry';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TX - allocate ITL entry 2

SQL>


Note that you can also query V$SEGMENT_STATISTICS for STATISTIC_NAME = 'ITL waits'.

If one of the rows is shrunk so that space is available, the third update will succeed:


SQL> -- shrink one row to release some space
SQL> -- in the block
SQL> -- and repeat the exercise
SQL> update t1 set c1 = substr(c1, 1, 1)
2 where rownum <= 1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select substr(c1,1,1) from t1 where c1 like 'a%' for update nowait;

S
-
a

SQL>

SQL> -- session 2
SQL> select substr(c1,1,1) from t1 where c1 like 'b%' for update nowait;

S
-
b

SQL>

SQL> -- session 3
SQL> select substr(c1,1,1) from t1 where c1 like 'c%' for update nowait;

S
-
c

SQL>


The remedy in this case requires a rebuild of the table segment using a higher INITRANS setting, so that initially more ITL slots are created. Note this reduces the space available for row data, so the size of the segment might increase.

Note that a rebuild using an appropriate PCTFREE setting also alleviates the problem but still a unsuitable row growth of rows by updates can again cause the issue. So the only change that helps independently of the row growth is an appropriate INITRANS setting.

Bitmap indexes

A single bitmap index entry can cover a lot of rows (rowid ranges), this is one of reasons why a bitmap index can be much smaller than a corresponding b*tree index.

But in terms of concurrency this can cause issues if different sessions attempt to modify the same bitmap index entry:


SQL>
SQL> create table fact_table2 as
2 select seq as fact_pk,
3 round(dbms_random.value(0, 1001)) as measure1,
4 round(dbms_random.value(0, 1001)) as measure2,
5 round(dbms_random.value(0, 1001)) as measure3,
6 case
7 when mod(seq, 20) >= 0 and mod(seq, 20) <= 10
8 then 1
9 else mod(seq, 20) - 9
10 end as dim_fk,
11 case when seq = 1 then 1 else 2 end as dim_fk2
12 from (
13 select level as seq from dual connect by level <= 1000
14 );

Table created.

Elapsed: 00:00:00.14
SQL>
SQL> create bitmap index fact_table2_dim_fk_idx on fact_table2(dim_fk);

Index created.

Elapsed: 00:00:00.11
SQL>
SQL> -- perform this in session 1
SQL> insert into fact_table2 (fact_pk, dim_fk) values (1001, 1);

1 row created.

Elapsed: 00:00:00.03
SQL>

SQL> -- session 2
SQL> insert into fact_table2 (fact_pk, dim_fk) values (1002, 1);


Diagnosis:


SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
2 sid sess, id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request
> 0)
5 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder: 138 589824 1682 6 0 TX
Waiter: 144 589824 1682 0 4 TX

SQL> select eq_name, eq_type, req_reason
2 from v$enqueue_statistics
3 where eq_type = 'TX'
4 and req_reason = 'row lock contention';

EQ_NAME EQ REQ_REASON
-------------------- -- --------------------
Transaction TX row lock contention

SQL> select event, total_waits from v$system_event
2 where event = 'enq: TX - row lock contention';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TX - row lock contention 1

SQL>
SQL> select event, total_waits from v$session_event
2 where event = 'enq: TX - row lock contention';

EVENT TOTAL_WAITS
---------------------------------------------------------------- -----------
enq: TX - row lock contention 1

SQL>


Two simple inserts block each other, a quite significant reason why bitmap indexes are not suitable for OLTP like applications.

There is no simple remedy in this case. Using a b*tree index instead of the bitmap index apparently doesn't show the same contention issue:


SQL>
SQL> drop index fact_table2_dim_fk_idx;

Index dropped.

Elapsed: 00:00:00.07
SQL>
SQL> -- the same using a b*tree index doesn't block each other
SQL> create index fact_table2_dim_fk_idx on fact_table2(dim_fk);

Index created.

Elapsed: 00:00:00.05
SQL>
SQL> -- perform this in session 1
SQL> insert into fact_table2 (fact_pk, dim_fk) values (1001, 1);

1 row created.

Elapsed: 00:00:00.01
SQL>
SQL> pause

SQL>
SQL> -- perform this in session 2
SQL> insert into fact_table2 (fact_pk, dim_fk) values (1002, 1);

1 row created.

Elapsed: 00:00:00.01


Segments with low MAXTRANS settings (Only pre-10g)

This scenario only applies to pre-10g databases where the MAXTRANS setting can be set to a lower value than the default of 255. 10g and later ignore the MAXTRANS value if specified and use 255 anyway.

The issue is basically the same as in the previous case: An ITL slot needs to be allocated, but cannot be created. In this case this is due to the artificial limitation caused by the low MAXTRANS setting.


SQL>
SQL> CREATE TABLE TAB1
2 ( COL1 NUMBER,
3 COL2 VARCHAR2(200))
4 INITRANS 1 MAXTRANS 1
5 --tablespace TEST_8K_ASSM
6 /

Table created.

SQL>
SQL> DECLARE
2 I NUMBER;
3 BEGIN
4 FOR I IN 1..10000 LOOP
5 INSERT INTO TAB1 VALUES
6 (I,'SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS');
7 END LOOP;
8 COMMIT;
9 END;
10 /

PL/SQL procedure successfully completed.

SQL>
SQL> -- use three different sessions to perform these updates
SQL> -- the second or the at least the third one will be blocked
SQL> -- Session 1
SQL> UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 1;

1 row updated.

SQL>
SQL> -- session 2
SQL> UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 2;

1 row updated.


SQL> -- session 3
SQL> UPDATE TAB1 SET COL2 = 'UPDATED' WHERE COL1 = 3;


Diagnosis:


SQL> --diagnosis
SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
2 sid sess, id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request
> 0)
5 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY
-------------------- ---------- ---------- ---------- ---------- --
Holder: 13 589842 976 6 0 TX
Waiter: 14 589842 976 0 4 TX


Note that the same can be shown using indexes with a low MAXTRANS setting.

The appropriate remedy is of course to use a suitable MAXTRANS setting which 10g and later applies by default.

Monday, November 3, 2008

Primary key / unique constraints enforced using a non-unique index - 11.1.0.6 and 11.1.0.7

When enforcing a primary key or unique constraint using a non-unique index 11.1.0.6 allows to use direct-path inserts (append mode) in contrast to 10.2.0.4 which silently falls back to conventional inserts in this particular case. For a demonstration, see here.

But this enhancement in 11.1.0.6 seems to allow a situation where the enabled and validated constraint can be violated without any error message. This looks like a bug. If you simply set the non-unique index to "unusable" and then attempt to insert duplicates using direct-path insert mode you'll actually succeed and therefore end up with duplicate data in your table although you have a enabled primary key/unique constraint.

A small script shall demonstrate the issue:


SQL>
SQL> -- blank sample table
SQL> create table append_test as
2 select
3 trunc(sqrt(rownum-1)) as skewed_data,
4 rownum-1 as id,
5 lpad(rownum-1,10) id_char,
6 rpad('x',50, 'x') as filler
7 from
8 all_objects
9 where 1 = 2;

Table created.

SQL>
SQL> -- create non-unique index
SQL> create index append_test_pk on append_test(id);

Index created.

SQL>
SQL> -- add primary key constraint
SQL> alter table append_test add constraint pk_append_test primary key (id);

Table altered.

SQL>
SQL> -- same applies to unique constraint
SQL> -- alter table append_test add constraint uq_append_test unique (id);
SQL>
SQL> -- make the index unusable
SQL> alter index append_test_pk unusable;

Index altered.

SQL>
SQL> -- now perform a direct-path insert
SQL> insert /*+ append */ into append_test
2 select
3 trunc(sqrt(rownum-1)) as skewed_data,
4 1 as id,
5 --rownum-1 as id,
6 lpad(rownum-1,10) id_char,
7 rpad('x',50, 'x') as filler
8 from
9 all_objects
10 where rownum <= 100;

100 rows created.

SQL>
SQL> -- this generates an error ORA-12838
SQL> -- and therefore shows that this
SQL> -- was a direct-path insert
SQL> select * from append_test;
select * from append_test
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL>
SQL> -- now we have non-unique data
SQL> -- in the table
SQL> -- although the primary key
SQL> -- constraint is enabled and
SQL> -- validated
SQL> commit;

Commit complete.

SQL>
SQL> -- try the same using conventional insert
SQL> insert /*+ noappend */ into append_test
2 select
3 trunc(sqrt(rownum-1)) as skewed_data,
4 1 as id,
5 --rownum-1 as id,
6 lpad(rownum-1,10) id_char,
7 rpad('x',50, 'x') as filler
8 from
9 all_objects
10 where rownum <= 100;
insert /*+ noappend */ into append_test
*
ERROR at line 1:
ORA-01502: index 'CBO_TEST.APPEND_TEST_PK' or partition of such index is in unusable state


SQL>
SQL> -- rebuild the index
SQL> alter index append_test_pk rebuild;

Index altered.

SQL>
SQL> -- attempt to re-validate the constraint
SQL> alter table append_test modify constraint pk_append_test novalidate;

Table altered.

SQL>
SQL> -- fails due to duplicates
SQL> alter table append_test modify constraint pk_append_test validate;
alter table append_test modify constraint pk_append_test validate
*
ERROR at line 1:
ORA-02437: cannot validate (CBO_TEST.PK_APPEND_TEST) - primary key violated


SQL> spool off


The issue can still be reproduced in the 11.1.0.7 patch set and therefore and seems to be a bug not yet fixed.

Wednesday, October 1, 2008

Bitmap Join Indexes and cardinality estimates

A bitmap join index is a special kind of index as it contains values of columns from table(s) joined to a table but holds bitmaps that point to the table being joined to. So it allows under certain circumstances to avoid join operations from taking place if you are restricting your results on your table by filtering on an attribute of a joined table.

For further information regarding bitmap (join) indexes you may want to have look at the following note by Jonathan Lewis that contains links to three Word documents that describe the basic principles of bitmap (join) indexes.

As can be seen in Jonathan's document Oracle internally supports these bitmap join indexes through various constructs - amongst these a "virtual column" is added to the column list of the "indexed" table in the data dictionary that represents the values from the joined table obviously based on the data stored in the index.

So in theory gathering statistics on this "virtual column" would allow the optimizer to get a very good estimate for the number of rows that correspond to a particular value of the attribute of the joined table, which would be very helpful especially if the "joined" value distribution of this attribute is skewed.

In Data Warehouses sometimes rows of a fact table are assigned to a special value of a dimension, like "deleted", "not mapped", "default" etc. which might become at some point a predominant value when joining the dimension to the fact table. So without the additional information stored in the bitmap join index the optimizer at parse time can hardly tell from the normal table and column statistics available how many rows a join will return if you filter on an particular attribute of a dimension table. Although the statistics can tell quite precisely how many rows of the dimension table will satisfy the filter condition, this doesn't say anything about the number of rows of the fact table that will join to the corresponding primary key values of the filtered dimension table.

However, using the data that is stored in the bitmap join index this information is available as the index can tell exactly how many rows of the fact table correspond to a particular value of an attribute of a dimension table.

Unfortunately all tested Oracle versions (9.2.0.8, 10.2.0.4 and 11.1.0.6) do not allow to gather statistics on these virtual columns that are corresponding to bitmap join indexes, and even if manually crafted statistics including histograms are applied to the virtual column by using "dbms_stats.set_column_stats" the optimizer does not consider them for cardinality estimated of the corresponding row sources.

So in future this might be a useful extension of the statistics framework to fully leverage the potential power of bitmap join indexes.

A small test case run on 11.1.0.6 (Windows 32-bit) shall demonstrate the issue. Note that 9.2.0.8 and 10.2.0.4 show similar results regarding the optimizer estimates.

First suitable tables are created that represent a fact table and its corresponding dimension table.


SQL>
SQL> drop table fact_table purge;

Table dropped.

Elapsed: 00:00:00.08
SQL>
SQL> drop table dim_table purge;

Table dropped.

Elapsed: 00:00:00.04
SQL>
SQL> create table fact_table (
2 fact_pk number not null unique,
3 measure1 number,
4 measure2 number,
5 measure3 number,
6 dim_fk number not null);

Table created.

Elapsed: 00:00:00.04
SQL>
SQL> create table dim_table (
2 dim_pk number not null unique,
3 attr1 varchar2(20),
4 attr2 varchar2(20));

Table created.

Elapsed: 00:00:00.03
SQL>
SQL> insert into dim_table (dim_pk, attr1, attr2)
2 select seq as dim_pk,
3 'A' || seq as attr1,
4 'B' || seq as attr2
5 from (
6 select level as seq from dual connect by level <= 10
7 );

10 rows created.

Elapsed: 00:00:00.01
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL>
SQL> insert into fact_table (fact_pk, measure1, measure2, measure3, dim_fk)
2 select seq as fact_pk,
3 round(dbms_random.value(0, 1001)) as measure1,
4 round(dbms_random.value(0, 1001)) as measure2,
5 round(dbms_random.value(0, 1001)) as measure3,
6 case
7 when mod(seq, 20) >= 0 and mod(seq, 20) <= 10
8 then 1
9 else mod(seq, 20) - 9
10 end as dim_fk
11 from (
12 select level as seq from dual connect by level <= 1000
13 );

1000 rows created.

Elapsed: 00:00:00.05
SQL>
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'DIM_TABLE',
4 method_opt=>'FOR ALL COLUMNS SIZE 1');
5 end;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.12
SQL>
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'FACT_TABLE',
4 method_opt=>'FOR ALL COLUMNS SIZE 1');
5 end;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.13
SQL>


Note that the foreign key of the fact table to the dimension table is skewed. The value "A1" is referenced 550 times whereas the remaining 9 values are each referenced 50 times.


SQL> select /*+ gather_plan_statistics */
2 f.*
3 from fact_table f, dim_table d
4 where d.attr1 = 'A1'
5 and f.dim_fk = d.dim_pk;

FACT_PK MEASURE1 MEASURE2 MEASURE3 DIM_FK
---------- ---------- ---------- ---------- ----------
1 569 986 154 1
2 82 576 927 1
3 925 916 586 1
...

550 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> select * from table(dbms_xplan.display_cursor(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 90z72u9c6w5wq, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ f.* from fact_table f, dim_table d
where d.attr1 = 'A1' and f.dim_fk = d.dim_pk

Plan hash value: 984687855

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 100 | 550 |00:00:00.01 | 45 | 1066K| 1066K| 354K (0)|
|* 2 | TABLE ACCESS FULL| DIM_TABLE | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 3 | TABLE ACCESS FULL| FACT_TABLE | 1 | 1000 | 1000 |00:00:00.01 | 42 | | | |
----------------------------------------------------------------------------------------------------------------------

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

1 - access("F"."DIM_FK"="D"."DIM_PK")
2 - filter("D"."ATTR1"='A1')


21 rows selected.

Elapsed: 00:00:00.09
SQL>


Without histograms no skew is detected by the optimizer. It estimates the average of 100 rows (1000 rows, 10 distinct foreign key values).


SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'DIM_TABLE',
4 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
5 end;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11
SQL>
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'FACT_TABLE',
4 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
5 end;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.36
SQL>
SQL> select /*+ gather_plan_statistics */
2 f.*
3 from fact_table f, dim_table d
4 where d.attr1 = 'A1'
5 and f.dim_fk = d.dim_pk;

FACT_PK MEASURE1 MEASURE2 MEASURE3 DIM_FK
---------- ---------- ---------- ---------- ----------
1 569 986 154 1
2 82 576 927 1
3 925 916 586 1
...
550 rows selected.

Elapsed: 00:00:00.02
SQL>
SQL> select * from table(dbms_xplan.display_cursor(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 90z72u9c6w5wq, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ f.* from fact_table f, dim_table d
where d.attr1 = 'A1' and f.dim_fk = d.dim_pk

Plan hash value: 984687855

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 1 | 100 | 550 |00:00:00.01 | 45 | 1066K| 1066K| 323K (0)|
|* 2 | TABLE ACCESS FULL| DIM_TABLE | 1 | 1 | 1 |00:00:00.01 | 3 | | | |
| 3 | TABLE ACCESS FULL| FACT_TABLE | 1 | 1000 | 1000 |00:00:00.01 | 42 | | | |
----------------------------------------------------------------------------------------------------------------------

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

1 - access("F"."DIM_FK"="D"."DIM_PK")
2 - filter("D"."ATTR1"='A1')


21 rows selected.

Elapsed: 00:00:00.09
SQL>


Generating histograms on skewed columns doesn't change the estimate.

Let's create a bitmap join index on ATTR1 of the dimension table.


SQL> create bitmap index bmji_fact_dim on fact_table (d.attr1)
2 from fact_table f, dim_table d
3 where d.dim_pk = f.dim_fk;

Index created.

Elapsed: 00:00:00.05
SQL>
SQL> select /*+ gather_plan_statistics */
2 f.*
3 from fact_table f, dim_table d
4 where d.attr1 = 'A1'
5 and f.dim_fk = d.dim_pk;

FACT_PK MEASURE1 MEASURE2 MEASURE3 DIM_FK
---------- ---------- ---------- ---------- ----------
1 569 986 154 1
2 82 576 927 1
3 925 916 586 1
...

550 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> select * from table(dbms_xplan.display_cursor(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 90z72u9c6w5wq, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ f.* from fact_table f, dim_table d
where d.attr1 = 'A1' and f.dim_fk = d.dim_pk

Plan hash value: 2240532429

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID | FACT_TABLE | 1 | 100 | 550 |00:00:00.01 | 42 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 1 | | 550 |00:00:00.01 | 2 |
|* 3 | BITMAP INDEX SINGLE VALUE | BMJI_FACT_DIM | 1 | | 1 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------------

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

3 - access("F"."SYS_NC00006$"='A1')


20 rows selected.

Elapsed: 00:00:00.08
SQL>


As we can see the bitmap join index is used to avoid the join operation but the optimizer estimate is still the average value, although the bitmap join index information could be used to determine the skew.

Now let's try to get statistics information on the "virtual" column created for the bitmap join index.


SQL> column data_type format a20
SQL> column table_name format a20
SQL> column column_name format a20
SQL>
SQL> select table_name, column_name, data_type
2 from user_tab_cols
3 where table_name = 'FACT_TABLE' and hidden_column = 'YES';

TABLE_NAME COLUMN_NAME DATA_TYPE
-------------------- -------------------- --------------------
FACT_TABLE SYS_NC00006$ VARCHAR2

1 row selected.

Elapsed: 00:00:00.01
SQL>
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'FACT_TABLE',
4 method_opt=>'FOR ALL HIDDEN COLUMNS SIZE 254');
5 end;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
SQL>
SQL> select distinct column_name from user_tab_histograms where table_name = 'FACT_TABLE';

COLUMN_NAME
--------------------
FACT_PK
DIM_FK
MEASURE2
MEASURE3
MEASURE1

5 rows selected.

Elapsed: 00:00:00.02
SQL>
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'FACT_TABLE',
4 method_opt=>'FOR COLUMNS SYS_NC00006$ SIZE 254');
5 end;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
SQL>
SQL> select distinct column_name from user_tab_histograms where table_name = 'FACT_TABLE';

COLUMN_NAME
--------------------
FACT_PK
DIM_FK
MEASURE2
MEASURE3
MEASURE1

5 rows selected.

Elapsed: 00:00:00.03
SQL>
SQL> select /*+ gather_plan_statistics */
2 f.*
3 from fact_table f, dim_table d
4 where d.attr1 = 'A1'
5 and f.dim_fk = d.dim_pk;

FACT_PK MEASURE1 MEASURE2 MEASURE3 DIM_FK
---------- ---------- ---------- ---------- ----------
1 569 986 154 1
2 82 576 927 1
3 925 916 586 1
...
550 rows selected.

Elapsed: 00:00:00.66
SQL>
SQL> select * from table(dbms_xplan.display_cursor(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 90z72u9c6w5wq, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ f.* from fact_table f, dim_table d
where d.attr1 = 'A1' and f.dim_fk = d.dim_pk

Plan hash value: 2240532429

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID | FACT_TABLE | 1 | 100 | 550 |00:00:00.01 | 42 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 1 | | 550 |00:00:00.01 | 2 |
|* 3 | BITMAP INDEX SINGLE VALUE | BMJI_FACT_DIM | 1 | | 1 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------------

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

3 - access("F"."SYS_NC00006$"='A1')


20 rows selected.

Elapsed: 00:00:00.08
SQL>


So this didn't help, we don't get a histogram (and no column statistics at all, not even basic information) for the "virtual" column this way.

Let's try to generate column statistics ourselves.


SQL> select count(*) as cnt, d.attr1
2 from fact_table f, dim_table d
3 where d.dim_pk = f.dim_fk
4 group by d.attr1
5 order by nlssort(d.attr1, 'NLS_SORT = BINARY');

CNT ATTR1
---------- --------------------
550 A1
50 A10
50 A2
50 A3
50 A4
50 A5
50 A6
50 A7
50 A8
50 A9

10 rows selected.

Elapsed: 00:00:00.03
SQL>
SQL> DECLARE
2 SREC DBMS_STATS.STATREC;
3 NOVALS DBMS_STATS.CHARARRAY;
4 BEGIN
5 SREC.EPC := 10;
6 NOVALS := DBMS_STATS.CHARARRAY(
7 'A1',
8 'A10',
9 'A2',
10 'A3',
11 'A4',
12 'A5',
13 'A6',
14 'A7',
15 'A8',
16 'A9'
17 );
18 SREC.BKVALS := DBMS_STATS.NUMARRAY(
19 550,
20 50,
21 50,
22 50,
23 50,
24 50,
25 50,
26 50,
27 50,
28 50
29 );
30 DBMS_STATS.PREPARE_COLUMN_VALUES (SREC,NOVALS);
31 DBMS_STATS.SET_COLUMN_STATS(
32 ownname=>USER,
33 tabname=>'FACT_TABLE',
34 colname=>'SYS_NC00006$',
35 distcnt=>10,
36 nullcnt=>0,
37 srec=>SREC,
38 avgclen=>2
39 );
40 end;
41 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
SQL>
SQL> select distinct column_name from user_tab_histograms where table_name = 'FACT_TABLE';

COLUMN_NAME
--------------------
FACT_PK
SYS_NC00006$
DIM_FK
MEASURE2
MEASURE3
MEASURE1

6 rows selected.

Elapsed: 00:00:00.03
SQL>
SQL> select /*+ gather_plan_statistics */
2 f.*
3 from fact_table f, dim_table d
4 where d.attr1 = 'A1'
5 and f.dim_fk = d.dim_pk;

FACT_PK MEASURE1 MEASURE2 MEASURE3 DIM_FK
---------- ---------- ---------- ---------- ----------
1 569 986 154 1
2 82 576 927 1
3 925 916 586 1
...

550 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> select * from table(dbms_xplan.display_cursor(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 90z72u9c6w5wq, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ f.* from fact_table f, dim_table d
where d.attr1 = 'A1' and f.dim_fk = d.dim_pk

Plan hash value: 2240532429

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID | FACT_TABLE | 1 | 100 | 550 |00:00:00.01 | 42 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 1 | | 550 |00:00:00.01 | 2 |
|* 3 | BITMAP INDEX SINGLE VALUE | BMJI_FACT_DIM | 1 | | 1 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------------

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

3 - access("F"."SYS_NC00006$"='A1')


20 rows selected.

Elapsed: 00:00:00.08
SQL>


Interestingly the custom column statistics for the "virtual" column are accepted by DBMS_STATS.SET_COLUMN_STATS although DBMS_STATS.GATHER_TABLE_STATS is not able to analyze this column.

But no difference regarding the estimates can be seen even with a suitable histogram in place. This shows that the optimizer doesn't evaluate the information available from the "virtual" column of the bitmap join index.

Finally let's see what happens when querying one of the other dimension values.


SQL> select /*+ gather_plan_statistics */
2 f.*
3 from fact_table f, dim_table d
4 where d.attr1 = 'A4'
5 and f.dim_fk = d.dim_pk;

FACT_PK MEASURE1 MEASURE2 MEASURE3 DIM_FK
---------- ---------- ---------- ---------- ----------
13 873 856 773 4
33 656 834 615 4
53 149 162 299 4

50 rows selected.

Elapsed: 00:00:00.01
SQL>
SQL> select * from table(dbms_xplan.display_cursor(NULL, NULL, 'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8tfp7kmaajhnv, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ f.* from fact_table f, dim_table d
where d.attr1 = 'A4' and f.dim_fk = d.dim_pk

Plan hash value: 2240532429

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID | FACT_TABLE | 1 | 100 | 50 |00:00:00.01 | 8 |
| 2 | BITMAP CONVERSION TO ROWIDS| | 1 | | 50 |00:00:00.01 | 2 |
|* 3 | BITMAP INDEX SINGLE VALUE | BMJI_FACT_DIM | 1 | | 1 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------------

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

3 - access("F"."SYS_NC00006$"='A4')


20 rows selected.

Elapsed: 00:00:00.08
SQL>


The estimate is still the same (the average of 100), so in summary the optimizer is not (yet) able to take advantage of the potential added value represented by the "virtual" column created for the bitmap join index.

Monday, September 1, 2008

Getting first rows of a large sorted result set quickly - NOSORT issues

When attempting to display only the first n rows of a result set that is ordered the Oracle optimizer offers special optimizations for these so-called top-n queries. These optimizations are based on the fact that Oracle in this case only needs to order the top n rows of the result set rather than the complete set which can make a significant difference if the result set is large. If the optimizer was able to recognize this you can see it in the execution plan: It shows as "SORT ORDER BY STOPKEY" operation.

Tom Kyte has written some nice notes about these top n and pagination queries. You can find the details here and here.

If you have created a suitable index Oracle also can use this index to avoid a sort operation from taking place by using the index to retrieve the rows already in the requested sorted order without an additional sort operation required. You can notice this in the execution plan by the absence of any ORDER BY operation although you specified a ORDER BY in your statement.

Note however in case char columns are used to order the result set depending on your session's NLS_SORT setting a normal index might not be used, since the index is by default based on the "NLS_SORT = binary" sort order, whereas your session might use a different sort order.

There are two options in this case to still take advantage of the NOSORT operation: Use the "NLS_SORT = binary" setting in your session, or create a function based index using the NLSSORT function. This should work fine if all resp. most of your clients share the same NLS_SORT session setting, but keep in mind that it is a client controlled setting and therefore theoretically could be different for each of your clients.

A short sample script run on 10.2.0.4 (32-bit Windows) shall demonstrate the issue.


SQL>
SQL> drop table nosort_order_by_test purge;

Table dropped.

Elapsed: 00:00:00.09
SQL>
SQL> create table nosort_order_by_test as
2 select * from all_objects;

Table created.

Elapsed: 00:00:02.97
SQL>
SQL> alter table nosort_order_by_test add constraint pk_nosort_order_by_test
2 primary key (object_id);

Table altered.

Elapsed: 00:00:00.23
SQL>
SQL> create index nosort_order_by_test_ix1 on nosort_order_by_test (
2 owner desc,
3 object_name
4 );

Index created.

Elapsed: 00:00:00.17
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'NOSORT_ORDER_BY_TEST')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.93
SQL>
SQL> alter session set nls_sort = german;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> select * from nls_session_parameters where parameter = 'NLS_SORT';

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_SORT GERMAN

Elapsed: 00:00:00.01
SQL>
SQL> set autotrace traceonly
SQL>
SQL> -- this apparently should use the index nosort_order_by_test_ix1 but it doesn't due to the NLS_SORT setting
SQL> select * from (select * from nosort_order_by_test order by owner desc, object_name) where rownum <= 10;

10 rows selected.

Elapsed: 00:00:00.19

Execution Plan
----------------------------------------------------------
Plan hash value: 3856848661

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1280 | | 1312 (2)| 00:00:16 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 50097 | 6262K| | 1312 (2)| 00:00:16 |
|* 3 | SORT ORDER BY STOPKEY| | 50097 | 4745K| 12M| 1312 (2)| 00:00:16 |
| 4 | TABLE ACCESS FULL | NOSORT_ORDER_BY_TEST | 50097 | 4745K| | 193 (3)| 00:00:03 |
--------------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
691 consistent gets
0 physical reads
0 redo size
1755 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10 rows processed

SQL>
SQL> alter session set nls_sort = binary;

Session altered.

Elapsed: 00:00:00.00
SQL>
SQL> -- this uses the index
SQL> select * from (select * from nosort_order_by_test order by owner desc, object_name) where rownum <= 10;

10 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2815166345

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1280 | 10 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 10 | 1280 | 10 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| NOSORT_ORDER_BY_TEST | 50097 | 4745K| 10 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | NOSORT_ORDER_BY_TEST_IX1 | 10 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<=10)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1758 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

SQL>
SQL> -- create a function based index using the NLSSORT function
SQL> create index nosort_order_by_test_ix2 on nosort_order_by_test (
2 nlssort(owner, 'NLS_SORT = GERMAN') desc,
3 nlssort(object_name, 'NLS_SORT = GERMAN')
4 );

Index created.

Elapsed: 00:00:00.37
SQL>
SQL> alter session set nls_sort = german;

Session altered.

Elapsed: 00:00:00.01
SQL>
SQL> -- now this works as supposed using the function based index nosort_order_by_test_ix2
SQL> select * from (select * from nosort_order_by_test order by owner desc, object_name) where rownum <= 10;

10 rows selected.

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 3350033264

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 1280 | 11 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 10 | 1280 | 11 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| NOSORT_ORDER_BY_TEST | 50097 | 4745K| 11 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | NOSORT_ORDER_BY_TEST_IX2 | 10 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<=10)


Statistics
----------------------------------------------------------
15 recursive calls
0 db block gets
14 consistent gets
2 physical reads
0 redo size
1755 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

SQL>
SQL> spool off


Note that the first attempt to use the index fails due to the non-default NLS_SORT session setting.
Although the SORT ORDER BY STOPKEY operation is used we still scan the whole table rather than taking advantage of the index.

If the NLS_SORT is set to the default value of "binary" the index is going to be used.

If you have a common, non-default setting of NLS_SORT you can still use an index by creating a suitable function based index.

Note that similar issues regarding index usage can arise when using non-default NLS_COMP session settings. See here a note by Jonathan Lewis covering this and some other issues.

Monday, August 4, 2008

Exchange partition and the SIZE AUTO option of DBMS_STATS column statistics in 10g

Oracle's exchange partition feature is a handy method for loading mass data. Data is usually being loaded into an "exchange" table that mirrors the attributes of a single partition or subpartition of the actual table.

Before 10g it probably didn't matter if you gathered statistics on the exchange table before performing the exchange or afterwards on the actual table, because the "SIZE AUTO" option of the method_opt parameter that was already in 9i was rarely used.

But starting with Oracle 10g the "SIZE AUTO" option is used as default when gathering table resp. column statistics. "SIZE AUTO" works in a two-level approach: In the first step it uses "column usage monitoring", another feature introduced in Oracle 9i to gather the information which columns potentially could benefit from histograms as they are used in WHERE clauses with certain kind of predicates, e.g. equal comparisons. In the next step a histogram is generated to detect if the column values are skewed or have significant gaps (which raised an interesting question about what are "gaps" in VARCHAR based data), and if they do, the histogram is stored in the statistics (this part corresponds to the "SIZE SKEWONLY" option behaviour).

So if you want to take advantage of new default "SIZE AUTO" feature, it is crucial that the statistics are gathered on the actual table because otherwise the "column usage monitoring" won't work as expected. It needs the workload of the actual table, not the one of the exchange table.

As already mentioned, from 10g on the SIZE AUTO option is the default option if you haven't changed it using DBMS_STATS.SET_PARAM resp. DBMS_STATS.SET_*_PREFS in 11g.

This small script shall demonstrate the issue:


SQL>
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> drop table size_auto_test purge;

Table dropped.

SQL>
SQL> create table size_auto_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 )
6 partition by range (x_pkey)
7 subpartition by list (x_slice)
8 (
9 partition pkey_1 values less than (2)
10 (
11 subpartition pkey_1_001 values ('001'),
12 subpartition pkey_1_101 values ('101')
13 )
14 );

Table created.

SQL>
SQL> -- "data1" is skewed
SQL> insert into size_auto_test (x_pkey, x_slice, data1)
2 select 1, '001', trunc(sqrt(seq-1)) as data1
3 from (
4 select level as seq from dual connect by level <= 10
5 );

10 rows created.

SQL>
SQL> -- "data1" is skewed
SQL> insert into size_auto_test (x_pkey, x_slice, data1)
2 select 1, '101', trunc(sqrt(seq-1)) as data1
3 from (
4 select level as seq from dual connect by level <= 1000
5 );

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> -- Gather initial statistics
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'SIZE_AUTO_TEST',
4 granularity=>'ALL',
5 method_opt=>'FOR ALL COLUMNS SIZE AUTO'); end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> -- Check the column usage monitoring
SQL> select * from sys.col_usage$ where obj# in (
2 select object_id from dba_objects
3 where object_name = 'SIZE_AUTO_TEST'
4 --and subobject_name = 'PKEY_1_101'
5 );

no rows selected

SQL>
SQL> drop table exchange_test purge;

Table dropped.

SQL>
SQL> create table exchange_test (
2 x_pkey number not null,
3 x_slice varchar2(20) not null,
4 data1 number
5 );

Table created.

SQL>
SQL> -- mirror one subpartition in exchange table
SQL> insert into exchange_test
2 select * from size_auto_test SUBPARTITION ("PKEY_1_101");

1000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'EXCHANGE_TEST',
4 method_opt=>'FOR ALL COLUMNS SIZE AUTO'); end;
5 /

PL/SQL procedure successfully completed.

SQL>
SQL> -- So this is our current situation
SQL> select table_name, column_name, num_buckets, histogram
2 from user_tab_col_statistics
3 where table_name = 'EXCHANGE_TEST';

TABLE_NAME COLUMN_NAME NUM_BUCKETS HISTOGRAM
--------------- --------------- ----------- ---------------
EXCHANGE_TEST X_PKEY 1 NONE
EXCHANGE_TEST X_SLICE 1 NONE
EXCHANGE_TEST DATA1 1 NONE

SQL>
SQL> select table_name, subpartition_name, column_name, num_buckets, histogram
2 from user_subpart_col_statistics
3 where table_name = 'SIZE_AUTO_TEST';

TABLE_NAME SUBPARTITION_NA COLUMN_NAME NUM_BUCKETS HISTOGRAM
--------------- --------------- --------------- ----------- ---------------
SIZE_AUTO_TEST PKEY_1_001 DATA1 1 NONE
SIZE_AUTO_TEST PKEY_1_001 X_SLICE 1 NONE
SIZE_AUTO_TEST PKEY_1_001 X_PKEY 1 NONE
SIZE_AUTO_TEST PKEY_1_101 DATA1 1 NONE
SIZE_AUTO_TEST PKEY_1_101 X_SLICE 1 NONE
SIZE_AUTO_TEST PKEY_1_101 X_PKEY 1 NONE

6 rows selected.

SQL>
SQL> -- Now perform an exchange
SQL> alter table size_auto_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Check the statistics
SQL> select subpartition_name, column_name, num_buckets, histogram
2 from user_subpart_col_statistics
3 where table_name = 'SIZE_AUTO_TEST';

SUBPARTITION_NA COLUMN_NAME NUM_BUCKETS HISTOGRAM
--------------- --------------- ----------- ---------------
PKEY_1_001 DATA1 1 NONE
PKEY_1_001 X_SLICE 1 NONE
PKEY_1_001 X_PKEY 1 NONE
PKEY_1_101 DATA1 1 NONE
PKEY_1_101 X_SLICE 1 NONE
PKEY_1_101 X_PKEY 1 NONE

6 rows selected.

SQL>
SQL> -- Undo the exchange
SQL> alter table size_auto_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- Perform some queries that could benefit from histograms
SQL> select count(*) from size_auto_test subpartition (pkey_1_101)
2 where data1 = 0;

COUNT(*)
----------
1

SQL>
SQL> select count(*) from size_auto_test subpartition (pkey_1_101)
2 where data1 = 11;

COUNT(*)
----------
23

SQL>
SQL> select count(*) from size_auto_test subpartition (pkey_1_101)
2 where data1 = 13;

COUNT(*)
----------
27

SQL>
SQL> select count(*) from size_auto_test subpartition (pkey_1_101)
2 where data1 = 20;

COUNT(*)
----------
41

SQL>
SQL> select count(*) from size_auto_test subpartition (pkey_1_101)
2 where data1 = 30;

COUNT(*)
----------
61

SQL>
SQL> select count(*) from size_auto_test subpartition (pkey_1_101)
2 where data1 = 31;

COUNT(*)
----------
39

SQL>
SQL> -- flush the monitoring info, so we can see it in col_usage$
SQL> -- otherwise it may take up to 15-20 minutes to see the latest changes
SQL> -- DBMS_STATS.GATHER_*_STATS will flush this as well
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

PL/SQL procedure successfully completed.

SQL>
SQL> -- Check the column usage monitoring
SQL> select * from sys.col_usage$ where obj# in (
2 select object_id from dba_objects
3 where object_name = 'EXCHANGE_TEST'
4 );

no rows selected

SQL>
SQL> -- Check the column usage monitoring
SQL> select * from sys.col_usage$ where obj# in (
2 select object_id from dba_objects
3 where object_name = 'SIZE_AUTO_TEST'
4 --and subobject_name = 'PKEY_1_101'
5 );

OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAM
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- --------
59457 3 1 0 0 0 0 0 04.08.08

SQL>
SQL> -- Obviously the exchange table does not have
SQL> -- any usage recorded
SQL> -- So gathering stats again does not make any difference
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'EXCHANGE_TEST',
4 method_opt=>'FOR ALL COLUMNS SIZE AUTO'); end;
5 /

PL/SQL procedure successfully completed.

SQL>
SQL> -- Still the same
SQL> select table_name, column_name, num_buckets, histogram
2 from user_tab_col_statistics
3 where table_name = 'EXCHANGE_TEST';

TABLE_NAME COLUMN_NAME NUM_BUCKETS HISTOGRAM
--------------- --------------- ----------- ---------------
EXCHANGE_TEST X_PKEY 1 NONE
EXCHANGE_TEST X_SLICE 1 NONE
EXCHANGE_TEST DATA1 1 NONE

SQL>
SQL> -- So if you exchange now you won't benefit
SQL> -- from any histograms potentially being generated
SQL> -- due to column usage and skewness
SQL> alter table size_auto_test exchange subpartition pkey_1_101 with table exchange_test;

Table altered.

SQL>
SQL> -- But now gathering on the actual table
SQL> begin dbms_stats.gather_table_stats(
2 ownname=>USER,
3 tabname=>'SIZE_AUTO_TEST',
4 granularity=>'ALL',
5 method_opt=>'FOR ALL COLUMNS SIZE AUTO'); end;
6 /

PL/SQL procedure successfully completed.

SQL>
SQL> -- Histograms being generated on the skewed column
SQL> select table_name, subpartition_name, column_name, num_buckets, histogram
2 from user_subpart_col_statistics
3 where table_name = 'SIZE_AUTO_TEST';

TABLE_NAME SUBPARTITION_NA COLUMN_NAME NUM_BUCKETS HISTOGRAM
--------------- --------------- --------------- ----------- ---------------
SIZE_AUTO_TEST PKEY_1_001 DATA1 4 FREQUENCY
SIZE_AUTO_TEST PKEY_1_001 X_SLICE 1 NONE
SIZE_AUTO_TEST PKEY_1_001 X_PKEY 1 NONE
SIZE_AUTO_TEST PKEY_1_101 DATA1 32 FREQUENCY
SIZE_AUTO_TEST PKEY_1_101 X_SLICE 1 NONE
SIZE_AUTO_TEST PKEY_1_101 X_PKEY 1 NONE

6 rows selected.

SQL>
SQL> spool off


Note that the test case shows the same result on 11.1.0.6.

So it can be seen from this sample that gathering statistics on the exchange table uses the workload of the exchange table and therefore the SIZE AUTO option doesn't work as expected. Gathering statistics on the actual partitioned table makes use the column workload information and generates histograms based on both workload and skewness.

Another interesting observation is that the column usage in sys.col_usage$ is maintained on table level rather on partition or subpartition level. Although I've explicitly queried on subpartition level the monitoring info refers to the table object and not to the (sub-)partition subobjects. This means that if your data in the partitions is used differently by queries depending on the way you gather statistics you might end up with histograms on particular partitions that are unnecessary. This can have an impact on both the time it takes to gather the statistics as for each histogram a separate gather step resp. query is required and the execution plans and cardinality estimates can be influenced by the existing histograms.

Tuesday, July 1, 2008

Deferrable constraints and direct path inserts resp. parallel DML

If you check the official Oracle documentation regarding the restrictions of direct path inserts you'll find the following information (taken from the "SQL Language Reference - INSERT" 11gR1 manuals):

"Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:

  • You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.

  • Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.

  • If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.

  • The target table cannot be index organized or part of a cluster.

  • The target table cannot contain object type columns.

  • The target table cannot have any triggers or referential integrity constraints defined on it.

  • The target table cannot be replicated.

  • A transaction containing a direct-path INSERT statement cannot be or become distributed."

A few but important restrictions are missing from this list, and in the "Data Warehousing Guide - Restrictions on Parallel DML" we find this more complete list, which also covers a restriction that I assume is not so well known: Deferrable constraints.

"The following restrictions apply to parallel DML (including direct-path INSERT):
  • Intra-partition parallelism for UPDATE, MERGE, and DELETE operations require that the COMPATIBLE initialization parameter be set to 9.2 or greater.

  • The INSERT VALUES statement is never parallelized.

  • A transaction can contain multiple parallel DML statements that modify different tables, but after a parallel DML statement modifies a table, no subsequent serial or parallel statement (DML or query) can access the same table again in that transaction.

    • This restriction also exists after a serial direct-path INSERT statement: no subsequent SQL statement (DML or query) can access the modified table during that transaction.

    • Queries that access the same table are allowed before a parallel DML or direct-path INSERT statement, but not after.

    • Any serial or parallel statements attempting to access a table that has already been modified by a parallel UPDATE, DELETE, or MERGE, or a direct-path INSERT during the same transaction are rejected with an error message.

  • Parallel DML operations cannot be done on tables with triggers.

  • Replication functionality is not supported for parallel DML.

  • Parallel DML cannot occur in the presence of certain constraints: self-referential integrity, delete cascade, and deferred integrity. In addition, for direct-path INSERT, there is no support for any referential integrity.

  • Parallel DML can be done on tables with object columns provided you are not touching the object columns.

  • Parallel DML can be done on tables with LOB columns provided the table is partitioned. However, intra-partition parallelism is not supported.

  • A transaction involved in a parallel DML operation cannot be or become a distributed transaction.

  • Clustered tables are not supported.

  • Parallel UPDATE, DELETE, and MERGE operations are not supported for temporary tables.

Violations of these restrictions cause the statement to execute serially without warnings or error messages (except for the restriction on statements accessing the same table in a transaction, which can cause error messages). For example, an update is serialized if it is on a nonpartitioned table."

I think there is still at least one restriction missing from the list, which will throw this error message:

ORA-12839: cannot modify an object in parallel after modifying it
Cause: Within the same transaction, an attempt was made to perform parallel modification operations on a table after it had been modified. This is not permitted.
Action: Rewrite the transaction or break it up into two transactions: one containing the parallel modification and the second containing the initial modification operation.
This means that you are not allowed to perform parallel DML on an object that has already been modified within the same transaction (by serial DML). Probably this can be explained by the fact that the parallel slave sessions are not able to see the modifications already applied to the object in the main session.

Interestingly this restriction does not apply to serial direct path inserts, these can be performed after an object has been modified by serial non-direct path DML.

Thinking about the fact that an object that has been modified by direct-path or parallel DML can not be read/modified afterwards within the same transaction it is quite obvious that a deferrable constraint can not be enforced in this mode, because I assume that in order to check the constraint the object has to be re-read and that is not possible before a commit has taken place.

So if there are any deferrable constraints enabled on the object to be modified no direct path or parallel dml operation will be performed, they will silently fall back to serial conventional dml.

So this small script shall demonstrate the latter two points. First the results from 10.2.0.4:

SQL>ִ
SQL>ִselectִ*ִfromִv$version;

BANNER
----------------------------------------------------------------
OracleִDatabaseִ10gִEnterpriseִEditionִReleaseִ10.2.0.4.0ִ-ִProd
PL/SQLִReleaseִ10.2.0.4.0ִ-ִProduction
COREִִ10.2.0.4.0ִִProduction
TNSִforִ32-bitִWindows:ִVersionִ10.2.0.4.0ִ-ִProduction
NLSRTLִVersionִ10.2.0.4.0ִ-ִProduction

SQL>ִ
SQL>ִdropִtableִdirect_path_testִcascadeִconstraints;

Tableִdropped.

SQL>ִ
SQL>ִcreateִtableִdirect_path_testִas
ִִ2ִִselect
ִִ3ִִִִִִִִrownumִִִִִִִִid,
ִִ4ִִִִִִִִmod(rownum,300)ִִִִִִִmodded,
ִִ5ִִִִִִִִlpad(rownum,1000)ִִִִִִִpadding
ִִ6ִִfrom
ִִ7ִִִִִִִִall_objects
ִִ8ִִwhere
ִִ9ִִִִִִִִrownumִ<=ִ10000
ִ10ִִ;

Tableִcreated.

SQL>ִ
SQL>ִ--ִdemonstrateִerrorִORA-12839
SQL>ִalterִsessionִenableִparallelִdml;

Sessionִaltered.

SQL>ִ
SQL>ִupdateִdirect_path_testִsetִpaddingִ=ִ'test'
ִִ2ִִwhereִrownumִ<=ִ1;

1ִrowִupdated.

SQL>ִ
SQL>ִ--ִthisִworksִfine
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִxִselectִ*ִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִupdateִdirect_path_testִsetִpaddingִ=ִ'test'
ִִ2ִִwhereִrownumִ<=ִ1;

1ִrowִupdated.

SQL>ִ
SQL>ִ--ִthisִthrowsִerrorִORA-12839
SQL>ִinsertִ/*+ִappendִparallel(x,ִ2)ִ*/ִintoִdirect_path_testִxִselectִ/*+ִparallelִ(a,ִ2)ִ*/ִ*ִfromִdirect_path_testִa;
insertִ/*+ִappendִparallel(x,ִ2)ִ*/ִintoִdirect_path_testִxִselectִ/*+ִparallelִ(a,ִ2)ִ*/ִ*ִfromִdirect_path_testִa
ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ*
ERRORִatִlineִ1:
ORA-12839:ִcannotִmodifyִanִobjectִinִparallelִafterִmodifyingִitִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִ--ִdemonstrateִdeferrableִconstraintִeffect
SQL>ִcreateִindexִdirect_path_test_pkִonִdirect_path_test(id);

Indexִcreated.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִaddִconstraintִdirect_path_test_pk
ִִ3ִִprimaryִkeyִ(id)
ִִ4ִִdeferrableִinitiallyִdeferred;

Tableִaltered.

SQL>ִ
SQL>ִ--ִthisִshouldִworkִsinceִtheִconstraintִisִdeferred
SQL>ִ--ִbutִitִwon'tִuseִdirectִpathִmodeִalthoughִrequested
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִxִselectִ*ִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִ--ִthisִshouldִnotִworkִifִtheִinsertִhadִbeenִdirectִpath
SQL>ִselectִcount(*)ִfromִdirect_path_test;

ִִCOUNT(*)
----------
ִִִִִ20000

SQL>ִ
SQL>ִ--ִconstraintִisִviolated
SQL>ִsetִconstraintִallִimmediate;
setִconstraintִallִimmediate
*
ERRORִatִlineִ1:
ORA-00001:ִuniqueִconstraintִ(CBO_TEST.DIRECT_PATH_TEST_PK)ִviolatedִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִdropִconstraintִdirect_path_test_pk;

Tableִaltered.

SQL>ִ
SQL>ִ--ִnowִweִcreateִaִnon-deferrableִprimaryִkeyִconstraint
SQL>ִ--ִusingִaִnon-uniqueִindex
SQL>ִ--ִwhichִisִalsoִsupported
SQL>ִalterִtableִdirect_path_test
ִִ2ִִaddִconstraintִdirect_path_test_pk
ִִ3ִִprimaryִkeyִ(id);

Tableִaltered.

SQL>ִ
SQL>ִ--ִthisִshouldִworkִinִdirectִpathִmode
SQL>ִ--ִbutִitִdoesn't
SQL>ִ--ִprobablyִbecauseִtheִprimaryִkeyִis
SQL>ִ--ִenforcedִusingִaִnon-uniqueִindex
SQL>ִ--ִNotִdocumentedִasִfarִasִIִknow
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִx
ִִ2ִִselectִ-id,
ִִ3ִִmodded,
ִִ4ִִpadding
ִִ5ִִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִ--ִthisִshouldִnotִworkִifִtheִinsertִhadִbeenִdirectִpath
SQL>ִ--ִbutִitִworks,ִsoִnoִdirectִpathִinsertִwasִused
SQL>ִselectִcount(*)ִfromִdirect_path_test;

ִִCOUNT(*)
----------
ִִִִִ20000

SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִdropִconstraintִdirect_path_test_pk;

Tableִaltered.

SQL>ִ
SQL>ִdropִindexִdirect_path_test_pk;

Indexִdropped.

SQL>ִ
SQL>ִcreateִuniqueִindexִdirect_path_test_pkִonִdirect_path_test(id);

Indexִcreated.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִaddִconstraintִdirect_path_test_pk
ִִ3ִִprimaryִkeyִ(id);

Tableִaltered.

SQL>ִ
SQL>ִ--ִthisִshouldִworkִinִdirectִpathִmode
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִx
ִִ2ִִselectִ-id,
ִִ3ִִmodded,
ִִ4ִִpadding
ִִ5ִִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִ--ִthisִshouldִnotִworkִifִtheִinsertִhadִbeenִdirectִpath
SQL>ִ--ִORA-12838
SQL>ִselectִcount(*)ִfromִdirect_path_test;
selectִcount(*)ִfromִdirect_path_test
ִִִִִִִִִִִִִִִִִִִִִ*
ERRORִatִlineִ1:
ORA-12838:ִcannotִread/modifyִanִobjectִafterִmodifyingִitִinִparallelִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִspoolִoff

And here is the result from 11.1.0.6:

SQL>ִ
SQL>ִselectִ*ִfromִv$version;

BANNER
--------------------------------------------------------------------------------
OracleִDatabaseִ11gִEnterpriseִEditionִReleaseִ11.1.0.6.0ִ-ִProduction
PL/SQLִReleaseִ11.1.0.6.0ִ-ִProduction
COREִ11.1.0.6.0ִProduction
TNSִforִ32-bitִWindows:ִVersionִ11.1.0.6.0ִ-ִProduction
NLSRTLִVersionִ11.1.0.6.0ִ-ִProduction

SQL>ִ
SQL>ִdropִtableִdirect_path_testִcascadeִconstraints;

Tableִdropped.

SQL>ִ
SQL>ִcreateִtableִdirect_path_testִas
ִִ2ִִselect
ִִ3ִִִִִִִִrownumִִִִִִִִid,
ִִ4ִִִִִִִִmod(rownum,300)ִִִִִִִmodded,
ִִ5ִִִִִִִִlpad(rownum,1000)ִִִִִִִpadding
ִִ6ִִfrom
ִִ7ִִִִִִִִall_objects
ִִ8ִִwhere
ִִ9ִִִִִִִִrownumִ<=ִ10000
ִ10ִִ;

Tableִcreated.

SQL>ִ
SQL>ִ--ִdemonstrateִerrorִORA-12839
SQL>ִalterִsessionִenableִparallelִdml;

Sessionִaltered.

SQL>ִ
SQL>ִupdateִdirect_path_testִsetִpaddingִ=ִ'test'
ִִ2ִִwhereִrownumִ<=ִ1;

1ִrowִupdated.

SQL>ִ
SQL>ִ--ִthisִworksִfine
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִxִselectִ*ִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִupdateִdirect_path_testִsetִpaddingִ=ִ'test'
ִִ2ִִwhereִrownumִ<=ִ1;

1ִrowִupdated.

SQL>ִ
SQL>ִ--ִthisִthrowsִerrorִORA-12839
SQL>ִinsertִ/*+ִappendִparallel(x,ִ2)ִ*/ִintoִdirect_path_testִxִselectִ/*+ִparallelִ(a,ִ2)ִ*/ִ*ִfromִdirect_path_testִa;
insertִ/*+ִappendִparallel(x,ִ2)ִ*/ִintoִdirect_path_testִxִselectִ/*+ִparallelִ(a,ִ2)ִ*/ִ*ִfromִdirect_path_testִa
ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ*
ERRORִatִlineִ1:
ORA-12839:ִcannotִmodifyִanִobjectִinִparallelִafterִmodifyingִitִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִ--ִdemonstrateִdeferrableִconstraintִeffect
SQL>ִcreateִindexִdirect_path_test_pkִonִdirect_path_test(id);

Indexִcreated.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִaddִconstraintִdirect_path_test_pk
ִִ3ִִprimaryִkeyִ(id)
ִִ4ִִdeferrableִinitiallyִdeferred;

Tableִaltered.

SQL>ִ
SQL>ִ--ִthisִshouldִworkִsinceִtheִconstraintִisִdeferred
SQL>ִ--ִbutִitִwon'tִuseִdirectִpathִmodeִalthoughִrequested
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִxִselectִ*ִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִ--ִthisִshouldִnotִworkִifִtheִinsertִhadִbeenִdirectִpath
SQL>ִselectִcount(*)ִfromִdirect_path_test;

ִִCOUNT(*)
----------
ִִִִִ20000

SQL>ִ
SQL>ִ--ִconstraintִisִviolated
SQL>ִsetִconstraintִallִimmediate;
setִconstraintִallִimmediate
*
ERRORִatִlineִ1:
ORA-00001:ִuniqueִconstraintִ(CBO_TEST.DIRECT_PATH_TEST_PK)ִviolatedִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִdropִconstraintִdirect_path_test_pk;

Tableִaltered.

SQL>ִ
SQL>ִ--ִnowִweִcreateִaִnon-deferrableִprimaryִkeyִconstraint
SQL>ִ--ִusingִaִnon-uniqueִindex
SQL>ִ--ִwhichִisִalsoִsupported
SQL>ִalterִtableִdirect_path_test
ִִ2ִִaddִconstraintִdirect_path_test_pk
ִִ3ִִprimaryִkeyִ(id);

Tableִaltered.

SQL>ִ
SQL>ִ--ִthisִshouldִworkִinִdirectִpathִmode
SQL>ִ--ִandִhereִinִ11gR1ִitִactuallyִworks
SQL>ִ--ִwhereasִinִ10.2.0.4ִitִdidn'tִwork
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִx
ִִ2ִִselectִ-id,
ִִ3ִִmodded,
ִִ4ִִpadding
ִִ5ִִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִ--ִthisִshouldִnotִworkִifִtheִinsertִhadִbeenִdirectִpath
SQL>ִselectִcount(*)ִfromִdirect_path_test;
selectִcount(*)ִfromִdirect_path_test
ִִִִִִִִִִִִִִִִִִִִִ*
ERRORִatִlineִ1:
ORA-12838:ִcannotִread/modifyִanִobjectִafterִmodifyingִitִinִparallelִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִdropִconstraintִdirect_path_test_pk;

Tableִaltered.

SQL>ִ
SQL>ִdropִindexִdirect_path_test_pk;

Indexִdropped.

SQL>ִ
SQL>ִcreateִuniqueִindexִdirect_path_test_pkִonִdirect_path_test(id);

Indexִcreated.

SQL>ִ
SQL>ִalterִtableִdirect_path_test
ִִ2ִִaddִconstraintִdirect_path_test_pk
ִִ3ִִprimaryִkeyִ(id);

Tableִaltered.

SQL>ִ
SQL>ִ--ִthisִshouldִworkִinִdirectִpathִmode
SQL>ִinsertִ/*+ִappendִ*/ִintoִdirect_path_testִx
ִִ2ִִselectִ-id,
ִִ3ִִmodded,
ִִ4ִִpadding
ִִ5ִִfromִdirect_path_testִa;

10000ִrowsִcreated.

SQL>ִ
SQL>ִ--ִthisִshouldִnotִworkִifִtheִinsertִhadִbeenִdirectִpath
SQL>ִ--ִORA-12838
SQL>ִselectִcount(*)ִfromִdirect_path_test;
selectִcount(*)ִfromִdirect_path_test
ִִִִִִִִִִִִִִִִִִִִִ*
ERRORִatִlineִ1:
ORA-12838:ִcannotִread/modifyִanִobjectִafterִmodifyingִitִinִparallelִ


SQL>ִ
SQL>ִrollback;

Rollbackִcomplete.

SQL>ִ
SQL>ִspoolִoff

It's quite interesting to note that in 10.2.0.4 the direct path insert didn't work when the primary key was enforced using a non-unique index, although the constraint was not deferrable, whereas in 11.1.0.6 the direct path insert mode was used in this case, which suggests that the 10.2.0.4 behaviour wasn't actually a feature.

Friday, June 27, 2008

Cost-Based Oracle - Fundamentals: Test cases applied against 10.2.0.4 and 11.1.0.6 (Part 1)

I'm starting here a loose series of applying the test cases used in the book "Cost-Based Oracle - Fundamentals" by Jonathan Lewis to 10.2.0.4 and 11.1.0.6.

The book covers 8i (8.1.7.4), 9iR2 (9.2.0.6) and 10gR1 (10.1.0.4), so I was keen on running the same tests on 10gR2 and 11gR1.

In the course of doing this I'll attempt to point out if there are any results that are noteworthy.

Before I begin here are the preparation steps and configuration details required to reproduce the test cases.

The INIT.ORA parameters relevant to the optimizer used for 10.2.0.4 and 11.1.0.6 have been taken from the sample INIT.ORAs provided as part of test case code depot. The INIT.ORAs used look like the following:

#10.2.0.4
*.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\orcl\control01.ctl','C:\oracle\product\10.2.0\oradata\orcl\control02.ctl','C:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
*.db_16k_cache_size=8388608
*.db_2k_cache_size=8388608
*.db_4k_cache_size=8388608
*.db_cache_size=67108864
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.java_pool_size=8388608
*.large_pool_size=8388608
*.pga_aggregate_target=209715200
*.shared_pool_size=134217728
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'
*.db_file_multiblock_read_count=8
*.db_keep_cache_size=8388608
*.parallel_max_servers=40
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.db_recycle_cache_size=8388608
*.query_rewrite_enabled='false'
*.sql_trace=FALSE
*.global_names=FALSE

#11.1.0.6
*.audit_file_dest='C:\oracle\admin\orcl11\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='C:\oracle\oradata\orcl11\control01.ctl','C:\oracle\oradata\orcl11\control02.ctl','C:\oracle\oradata\orcl11\control03.ctl'
*.db_16k_cache_size=8388608
*.db_2k_cache_size=8388608
*.db_4k_cache_size=8388608
*.db_cache_size=67108864
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl11'
*.db_recovery_file_dest='C:\oracle\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='C:\oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcl11XDB)'
*.java_pool_size=8388608
*.large_pool_size=8388608
*.pga_aggregate_target=327155712
*.shared_pool_size=134217728
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_multiblock_read_count=8
*.db_keep_cache_size=8388608
*.parallel_max_servers=40
*.nls_language='ENGLISH'
*.nls_territory='UNITED KINGDOM'
*.db_recycle_cache_size=8388608
*.query_rewrite_enabled='false'
*.sql_trace=FALSE
*.global_names=FALSE

The required tablespaces have also been created as provided:

create tablespace test_2k
blocksize 2K
datafile '_your_path_goes_here\test_2k.dbf' size 10m autoextend on next 10m maxsize 200m
extent management local
uniform size 1M
segment space management manual
;

create tablespace test_4k
blocksize 4K
datafile '_your_path_goes_here\test_4k.dbf' size 10m autoextend on next 10m maxsize 200m
extent management local
uniform size 1M
segment space management manual
;

create tablespace test_8k
blocksize 8K
datafile '_your_path_goes_here\test_8k.dbf' size 10m autoextend on next 10m maxsize 200m
extent management local
uniform size 1M
segment space management manual
;

create tablespace test_8k_assm
blocksize 8K
datafile '_your_path_goes_here\test_8k_assm.dbf' size 10m autoextend on next 10m maxsize 200m
extent management local
uniform size 1M
segment space management auto
;

create tablespace test_16k
blocksize 16K
datafile '_your_path_goes_here\test_16k.dbf' size 10m autoextend on next 10m maxsize 200m
extent management local
uniform size 1M
segment space management manual
;

An appropriate user has also been created that will host the test case objects:

create user cbo_test identified by cbo_test;

grant dba to cbo_test;


alter user cbo_test default tablespace test_8k;

I'm starting off with the test cases provided as part of the introduction and chapter one - "What do you mean by cost?".

These two chapters use only three scripts, and here are the results when applying them to 10.2.0.4 and 11.1.0.6.

The first script comes from the preface, is called "in_list.sql" and shows an error of the 8i optimizer that has been fixed from 9i on.

rem
remִScript:ִִִin_list.sql
remִAuthor:ִִִJonathanִLewis
remִDated:ִִִִSeptִ2003
remִPurpose:ִִDemonstrationִscriptִforִCostִBasedִOracle.
rem
remִVersionsִtested
remִִִ11.1.0.6
remִִִ10.2.0.4
remִִִ10.1.0.4
remִִִִ9.2.0.6
remִִִִ8.1.7.4
rem
remִNotes:
remִTheִ"direִwarning".
remִAnִupgradeִfromִ8ִtoִ9ִchangesִtheִin-listִcardinality
rem
remִWeִhaveִaִtableִwhereִeveryִvalueִforִcolumnִN1ִreturns
remִ100ִrows.ִUnderִ9iִandִ10g,ִaִlistִofִtwoִvaluesִproduces
remִaִcardinalityִofִ200ִrows.ִUnderִ8i,ִtheִestimatedִcardinality
remִisִonlyִ190ִrows.ִThisִisִanִerrorִinִtheִoptimizerִcode.
rem
remִTheִin-listִisִconvertedִtoִanִ'OR'ִlist
remִִִn1ִ=ִ1ִORִn1ִ=ִ2
rem
remִUnfortunately,ִ8iִthenִtreatsִtheִtwoִpredicatesִasִindependent,
remִsoִtheִcalculatedִcardinalityִis
remִִִestimateִofִrowsִwhereִn1ִ=ִ1ִ(oneִinִ10ִ=ִ100)ִplus
remִִִestimateִofִrowsִwhereִn1ִ=ִ2ִ(oneִinִ10ִ=ִ100)ִminus
remִִִestimateִofִrowsִwhereִ'n1ִ=ִ1ִandִn1ִ=ִ2'ִ...ִoneִinִ100ִ=ִ10.
rem
remִSeeִtheִChapterִ3ִ"BasicִSelectivity"ִforִmoreִdetails
rem

startִsetenv

dropִtableִt1;

begin
ִִbeginִִִexecuteִimmediateִ'purgeִrecyclebin';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

ִִbeginִִִexecuteִimmediateִ'beginִdbms_stats.delete_system_stats;ִend;';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

ִִbeginִִִexecuteִimmediateִ'alterִsessionִsetִ"_optimizer_cost_model"=io';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

end;
/

createִtableִt1
as
select
ִִtrunc((rownum-1)/100)ִn1,
ִִrpad('x',100)ִִִpadding
from
ִִall_objects
where
ִִrownumִ<=ִ1000
;


begin
ִִdbms_stats.gather_table_stats(
ִִִִuser,
ִִִִ't1',
ִִִִcascadeִ=>ִtrue,
ִִִִestimate_percentִ=>ִnull,
ִִִִmethod_optִ=>ִ'forִallִcolumnsִsizeִ1'
ִִ);
end;
/

setִautotraceִtraceonlyִexplain


spoolִin_list

select
ִִ*
fromִִt1
where
ִִn1ִinִ(1,2)
;


setִautotraceִoff

spoolִoff


setִdocִoff
doc

Underִ8i,ִtheִcardinalityִofִanִin-listִisִtooִlow.

ExecutionִPlanִ(8.1.7.4ִautotrace)
----------------------------------------------------------
ִִִ0ִִִִִִSELECTִSTATEMENTִOptimizer=ALL_ROWSִ(Cost=3ִCard=190ִBytes=19570)
ִִִ1ִִִִ0ִִִTABLEִACCESSִ(FULL)ִOFִ'T1'ִ(Cost=3ִCard=190ִBytes=19570)


ExecutionִPlanִ(9.2.0.6ִautotrace)
----------------------------------------------------------
ִִִ0ִִִִִִSELECTִSTATEMENTִOptimizer=ALL_ROWSִ(Cost=4ִCard=200ִBytes=20600)
ִִִ1ִִִִ0ִִִTABLEִACCESSִ(FULL)ִOFִ'T1'ִ(Cost=4ִCard=200ִBytes=20600)


ExecutionִPlanִ(10.1.0.4ִautotrace)
----------------------------------------------------------
ִִִ0ִִִִִִSELECTִSTATEMENTִOptimizer=ALL_ROWSִ(Cost=4ִCard=200ִBytes=20600)
ִִִ1ִִִִ0ִִִTABLEִACCESSִ(FULL)ִOFִ'T1'ִ(TABLE)ִ(Cost=4ִCard=200ִBytes=20600)


#

Here is the result of 10.2.0.4:

Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ3617692013

----------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִ|ִNameִ|ִRowsִִ|ִBytesִ|ִCostִִ|
----------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִ|ִִִִִִ|ִִִ200ִ|ִ20600ִ|ִִִִִ4ִ|
|*ִִ1ִ|ִִTABLEִACCESSִFULL|ִT1ִִִ|ִִִ200ִ|ִ20600ִ|ִִִִִ4ִ|
----------------------------------------------------------

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

ִִִ1ִ-ִfilter("N1"=1ִORִ"N1"=2)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)

And we get exactly the same from 11.1.0.6:

Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ3617692013

----------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִ|ִNameִ|ִRowsִִ|ִBytesִ|ִCostִִ|
----------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִ|ִִִִִִ|ִִִ200ִ|ִ20600ִ|ִִִִִ4ִ|
|*ִִ1ִ|ִִTABLEִACCESSִFULL|ִT1ִִִ|ִִִ200ִ|ִ20600ִ|ִִִִִ4ִ|
----------------------------------------------------------

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

ִִִ1ִ-ִfilter("N1"=1ִORִ"N1"=2)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)

So no surprises here, works as expected, and the results correspond to those of 9iR2 and 10gR1.

The next script comes from chapter 1 ("What do you mean by cost?") and is called "first_rows.sql". It shows potential problems that may arise from the approach the optimizer takes when using the (deprecated) FIRST_ROWS optimizer mode.

rem
remִScript:ִִִfirst_rows.sql
remִAuthor:ִִִJonathanִLewis
remִDated:ִִִִJuneִ2002
remִPurpose:ִִProblemsִwithִFIRST_ROWSִoptimisation
rem Adapted to 10g and 11g by Randolf Geist
rem
remִLastִtestedִ
remִִִ11.1.0.6
remִִִ10.2.0.4
remִִִ10.1.0.4
remִִִִ9.2.0.6
remִִִִ8.1.7.4
rem
remִTheִFIRST_ROWSִoptimizerִdoesִnotִhaveִaִsensibleִstrategy
remִforִdealingִwithִoptimisationִofִanִORDERִBYִ(andִpossibly
remִotherִvariantsִsuchִasִGROUPִBY)ִwhenִitִfindsִitִcanִuse
remִanִindexִtoִdoִtheִORDERִBYִ"freeִofִcharge".
rem
remִThisִexampleִdemonstratesִtheִpoint.ִWithoutִtheִin-line
remִview,ִOracleִdoesִaִfullִscanִonִtheִprimaryִkeyִindexִto
remִreturnִtheִ100ִrequiredִrowsִinִorder,ִatִaִtotalִcost
remִandִtotalִexecutionִtimeִthatִisִmuchִhigherִthanִthatִof
remִusingִtheִrequiredִindex.
rem
remִOfִcourse,ִitִisִarguablyִtheִcaseִthatִthisִisִcorrect
remִbehaviourִifִweִassumeִthatִtheִtimeִtoִtheִfirstִrowִis
remִimportant,ִandִthereforeִweִavoidִcollectingִaִlargeִnumber
remִofִrowsִandִsortingִthem.ִִInִpractice,ִthisִisִperhapsִnot
remִreallyִlikelyִtoִbeִtheִcase.
rem
remִBugִnumberִ670328ִapplies
rem
remִParameterִ_sort_elimination_cost_ratioִaffectsִtheִbreak
remִpointִbetweenִoptimisingִforִtheִWHEREִclauseִandִoptimising
remִforִtheִORDERִBYִclause.
rem
remִIfִtheִparameterִisִzero,ִtheִORDERִBYִclauseִtakesִprecedence
remִIfִtheִparameterִisִnon-zero,ִthenִtheִindexִisִusedִifִtheִcostִ
remִofִusingִitִisִlessִthanִtheִcostִofִdoingִtheִsortִmultipliedִ
remִbyִtheִvalueִofִtheִparameter.
rem
remִSpecialִnoteִforִ10g:
remִTheִparameterִstillִhasִtheִsameִeffectִinִgeneral,ִbutִzero
remִnowִmeansִzero,ִnotִ(effectively)ִinfinity.ִִTheִdefaultִof
remִzeroִwillִnowִeffectivelyִignoreִtheִindexִoptionִunlessִit
remִisִactuallyִcheaperִthanִtheִnon-indexִoption.ִAִnon-zero
remִvalueִwillִbehaveִasִitִalwaysִusedִto
rem

startִsetenv

dropִtableִt1ִcascadeִconstraints;

begin
ִִbeginִִִexecuteִimmediateִ'purgeִrecyclebin';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

ִִbeginִִִexecuteִimmediateִ'beginִdbms_stats.delete_system_stats;ִend;';
ִִexceptionִִִwhenִothersִthenִnull;
ִִend;

ִִbeginִִִexecuteִimmediateִ'alterִsessionִsetִ"_optimizer_cost_model"=io';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

end;
/

createִtableִt1ִas
select
ִִrownumִִִִִִid,
--ִִmod(rownum,100)ִִִmodded,
ִִmod(rownum,300)ִִִmodded,
ִִlpad(rownum,1000)ִpadding
from
ִִall_objects
where
ִִrownumִ<=ִ10000
;

alterִtableִt1ִaddִconstraintִt1_pkִprimaryִkey(id);
createִindexִt1_modִonִt1(modded);

begin
ִִdbms_stats.gather_table_stats(
ִִִִuser,
ִִִִ't1',
ִִִִcascadeִ=>ִtrue,
ִִִִestimate_percentִ=>ִnull,
ִִִִmethod_optִ=>ִ'forִallִcolumnsִsizeִ1'
ִִ);
end;
/

alterִsessionִsetִoptimizer_mode=first_rows;

setִautotraceִtraceonlyִexplain

spoolִfirst_rows

prompt
promptִִGetִaִbase-lineִcostִandִplanִforִacquiringִtheִrows
prompt

selectִִ*
fromִִt1
whereִmoddedִ=ִ0
;

prompt
promptִִSeeִwhatִhappensִifִweִaddִanִ'orderִbyִprimaryִkey'.
prompt

selectִִ*
fromִִt1
whereִmoddedִ=ִ0
orderִbyִ
ִִid
;

rem
remִBlockִtheִPKִindexִfromִbeingִusedִforִordering,ִandִsee
remִthatִOracleִcouldִnotionallyִgetִaִbetterִpath.ִStrangely
remִtheִcostִvariesִdependingִonִtheִstrategyִusedִforִblocking
remִtheִindex.ִOnִmyִ9.2.0.6ִtest,ִtheִno_mergeִhintִmanagedִto
remִreduceִtheִlengthsִofִtheִrowsִtoִbeִsorted,ִandִtherefore
remִcalculatedִaִsmallerִcost.
rem

prompt
promptִִBlockִtheִindexִwithִaִno_mergeִhint
prompt

selectִ*ִfromִ(
selectִִ/*+ִno_mergeִ*/ִ
ִִ*
fromִִt1
whereִmoddedִ=ִ0
)
orderִbyִ
ִִid
;


prompt
promptִִBlockִtheִindexִwithִaִno_indexִhint
prompt

selectִִ/*+ִno_index(t1,t1_pk)ִ*/
ִִ*
fromִִt1
whereִmoddedִ=ִ0
orderִbyִ
ִִid
;

setִautotraceִoff

rem
remִTheִcostsִunderִ10gִare:
remִִִUsingִtheִPKִindexִtoִavoidִtheִsort:ִִִִִ1,450
remִִִBlockִtheִPKִindexִ(no_index)ִandִsorting:ִִ59
rem
remִCostִratio:ִ1450/59ִ=ִ24.58,ִsoִweִtestִ
remִִִ_sort_elimination_cost_ratioִatִ24ִandִ25
rem
remִAtִ24:ִִ59ִ*ִ24ִ=ִ1,416:ִִsoִtheִPKִnosortִshouldִbeִignored
remִAtִ25:ִִ59ִ*ִ25ִ=ִ1,475:ִִsoִtheִPKִnosortִfallsִinsideִtheִlimit.
rem

setִautotraceִtraceonlyִexplainִ

alterִsessionִsetִ"_sort_elimination_cost_ratio"ִ=ִ25;

prompt
promptִִCostִratioִsetִtoִ25ִ-ִPKִpathִshouldִbeִaccepted
prompt

select
ִִ*
fromִִt1
whereִmoddedִ=ִ0
orderִbyִ
ִִid
;

alterִsessionִsetִ"_sort_elimination_cost_ratio"ִ=ִ24;

prompt
promptִִCostִratioִsetִtoִ24ִ-ִPKִNOSORTִshouldִbeִtooִexpensive
prompt

select
ִִ*
fromִִt1
whereִmoddedִ=ִ0
orderִbyִ
ִִid
;

setִautotraceִoff

spoolִoff

Here is the result when running in on 10.2.0.4:


Getִaִbase-lineִcostִandִplanִforִacquiringִtheִrows


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ3052882459

----------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
----------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|ִִִ1ִ|ִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ2ִ|ִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
----------------------------------------------------------------------

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

ִִִ2ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Seeִwhatִhappensִifִweִaddִanִ'orderִbyִprimaryִkey'.


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2004371487

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|*ִִ1ִ|ִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|ִִִ2ִ|ִִִINDEXִFULLִSCANִִִִִִִִִִִ|ִT1_PKִ|ִ10000ִ|ִִִִִִִ|ִִִִ21ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִfilter("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Blockִtheִindexִwithִaִno_mergeִhint


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ1453079715

------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ17424ִ|ִִִִ57ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ17424ִ|ִִִִ57ִ|
|ִִִ2ִ|ִִִVIEWִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ17424ִ|ִִִִ35ִ|
|ִִִ3ִ|ִִִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ4ִ|ִִִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
------------------------------------------------------------------------

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

ִִִ4ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Blockִtheִindexִwithִaִno_indexִhint


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2068790959

-----------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
-----------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ2ִ|ִִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ3ִ|ִִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
-----------------------------------------------------------------------

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

ִִִ3ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Costִratioִsetִtoִ25ִ-ִPKִpathִshouldִbeִaccepted


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2004371487

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|*ִִ1ִ|ִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|ִִִ2ִ|ִִִINDEXִFULLִSCANִִִִִִִִִִִ|ִT1_PKִ|ִ10000ִ|ִִִִִִִ|ִִִִ21ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִfilter("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Costִratioִsetִtoִ24ִ-ִPKִNOSORTִshouldִbeִtooִexpensive


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2068790959

-----------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
-----------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ2ִ|ִִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ3ִ|ִִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
-----------------------------------------------------------------------

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

ִִִ3ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)

We get the same from 11.1.0.6:


Getִaִbase-lineִcostִandִplanִforִacquiringִtheִrows


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ3052882459

----------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
----------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|ִִִ1ִ|ִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ2ִ|ִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
----------------------------------------------------------------------

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

ִִִ2ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Seeִwhatִhappensִifִweִaddִanִ'orderִbyִprimaryִkey'.


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2004371487

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|*ִִ1ִ|ִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|ִִִ2ִ|ִִִINDEXִFULLִSCANִִִִִִִִִִִ|ִT1_PKִ|ִ10000ִ|ִִִִִִִ|ִִִִ21ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִfilter("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Blockִtheִindexִwithִaִno_mergeִhint


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ1453079715

------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ17424ִ|ִִִִ57ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ17424ִ|ִִִִ57ִ|
|ִִִ2ִ|ִִִVIEWִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ17424ִ|ִִִִ35ִ|
|ִִִ3ִ|ִִִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ4ִ|ִִִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
------------------------------------------------------------------------

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

ִִִ4ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Blockִtheִindexִwithִaִno_indexִhint


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2068790959

-----------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
-----------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ2ִ|ִִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ3ִ|ִִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
-----------------------------------------------------------------------

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

ִִִ3ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Costִratioִsetִtoִ25ִ-ִPKִpathִshouldִbeִaccepted


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2004371487

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִ|ִNameִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִ|ִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|*ִִ1ִ|ִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִ|ִִִִ33ִ|ִ33264ִ|ִִ1450ִ|
|ִִִ2ִ|ִִִINDEXִFULLִSCANִִִִִִִִִִִ|ִT1_PKִ|ִ10000ִ|ִִִִִִִ|ִִִִ21ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִfilter("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Costִratioִsetִtoִ24ִ-ִPKִNOSORTִshouldִbeִtooִexpensive


Executionִplan
----------------------------------------------------------
Planִhashִvalue:ִ2068790959

-----------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִִִִִִ|ִNameִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
-----------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִִִִִִ|ִִִִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ59ִ|
|ִִִ2ִ|ִִִTABLEִACCESSִBYִINDEXִROWID|ִT1ִִִִִ|ִִִִ33ִ|ִ33264ִ|ִִִִ35ִ|
|*ִִ3ִ|ִִִִINDEXִRANGEִSCANִִִִִִִִִִ|ִT1_MODִ|ִִִִ33ִ|ִִִִִִִ|ִִִִִ1ִ|
-----------------------------------------------------------------------

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

ִִִ3ִ-ִaccess("MODDED"=0)

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)

The results in general correspond to those of the previous versions, but one interesting oddity showed up when I did the first test runs. The cost reported by my 10gR2 and 11gR1 systems for the query variations performing an ORDER BY was significantly higher than what is shown in the results that have been provided by Jonathan as part of the code depot.

It looks like the difference comes from the higher cost estimated for the ORDER BY sort. Further investigations showed that Jonathan probably performed the test runs using manual workarea_size_policy with 1M sort_area_size whereas I did the first test runs using the instance default setting of an pga_aggregate_target of 200M (see INIT.ORA parameters above).

The odd thing is that when turning CPU costing off ("_optimizer_cost_model"="io") then for that particular test case performing that tiny sort operation the cost of the sort is actually increasing when the amount of memory available for sorting is increasing. That means in other words, the higher the amount memory available the higher the cost of the small sort operation, due to the fact that the "IO cost per pass" in the optimizer trace file (10053 event) is higher if more memory is available and therefore the final IO cost of the sort is, too.

I was able to reproduce this strange behaviour in 9.2.0.8, 10.2.0.4 and 11.1.0.6. Due to the higher total cost when running the test case with more memory available I had to adjust the "_sort_elimination_cost_ratio" parameter accordingly used in the "first_rows.sql" example to get the same results. See the script for further explanation of this parameter.

All other parameters that I tried (using manual/automatic pga sizing, "all_rows" optimizer_mode instead of "first_rows") didn't change the outcome. When I was using "io" cost model I got in this particular case a consistent increase in I/O cost per pass when increasing the memory available for sorting.

I won't put further effort on this issue for the time being as there is a whole chapter (chapter 13) coming up later that covers sorting related issues in depth, so I'll see if I'm able to dig further there.

The final script from chapter 1 is called "view_merge_01.sql" and demonstrates the complex view merging transformation that can happen to your statement as part of the optimisation steps.

rem
remִScript:ִִִview_merge_01.sql
remִAuthor:ִִִJonathanִLewis
remִDated:ִִִִSeptemberִ2002
remִPurpose:ִִDemonstrateִcomplexִviewִmerging.
rem
remִLastִtestedִ
remִִִ11.1.0.6
remִִִ10.2.0.4
remִִִ10.1.0.4
remִִִִ9.2.0.6
remִִִִ8.1.7.4
rem
remִThisִshowsִtheִchangeִinִexecutionִpathִavailableִwhenִOracle
remִisִallowedִtoִmergeִpredicatesִintoִ'complex'ִviews.
rem
remִTheִmanualִ(9.2ִPerfִGuideִp.2-37)ִpointsִoutִthatִ
remִwhenִaִviewִcontainsִoneִofִtheִfollowingִstructures,ִ
remִitִcanִbeִmergedִintoִaִreferencingִqueryִblockִonlyִifִ
remִcomplexִviewִmergingִisִenabled:
remִִִAִGROUPִBYִclause
remִִִAִDISTINCTִoperatorִinִtheִselectִlist
rem
remִInִthisִexampleִwithִ9.2ִweִsee:
remִִִWithִ_complex_view_mergingִ=ִtrue,ִִweִjoinִthenִgroupִby
remִִִWithִ_complex_view_mergingִ=ִfalse,ִweִgroupִbyִthenִjoin
rem
remִTheִdefaultִforִOracleִ8ִisִFALSE
remִTheִdefaultִforִOracleִ9ִisִTRUE
remִTheִdefaultִforִOracleִ10ִisִTRUE,ִbutִOracleִ10ִworks
remִoutִtheִcostִofִtheִtwoִdifferentִapproachesִandִwill
remִchooseִtheִcheaperִoption.ִ(Runִwithִ10053ִtraceִtoִsee
remִtheִdifferentִoptionsִthatִappearִinִtheִtraceִfiles).
rem

startִsetenv

dropִtableִt2;
dropִtableִt1;

begin
ִִbeginִִִexecuteִimmediateִ'purgeִrecyclebin';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

ִִbeginִִִexecuteִimmediateִ'beginִdbms_stats.delete_system_stats;ִend;';
ִִexceptionִִִwhenִothersִthenִnull;
ִִend;

ִִbeginִִִexecuteִimmediateִ'alterִsessionִsetִ"_optimizer_cost_model"=io';
ִִexceptionִwhenִothersִthenִnull;
ִִend;

end;
/

createִtableִt1ִ(
ִִid_parִִִִnumber(6)ִnotִnull,
ִִvc1ִִִvarchar2(32)ִִnotִnull,
ִִvc2ִִִvarchar2(32)ִִnotִnull,
ִִpaddingִִִvarchar2(100)
);

alterִtableִt1ִaddִconstraintִt1_pkִprimaryִkeyִ(id_par);

createִtableִt2ִ(
ִִid_chִִִnumber(6)ִnotִnull,
ִִid_parִִִִnumber(6)ִnotִnull,
ִִvalִִִnumber(6,2),
ִִpaddingִִִvarchar2(100)
);

alterִtableִt2ִaddִconstraintִt2_pkִprimaryִkeyִ(id_ch);
alterִtableִt2ִaddִconstraintִt2_fk_t1ִforeignִkeyִ(id_par)ִreferencesִt1;

insertִintoִt1
selectִ
ִִrownum,
ִִvc1,
ִִvc2,
ִִrpad('x',100)
from
ִִ(
ִִִִselectִ
ִִִִִִlpad(trunc(sqrt(rownum)),32)ִִvc1,
ִִִִִִlpad(rownum,32)ִִִִִvc2
ִִִִfromִall_objects
ִִִִwhereִrownumִ<=ִ32
ִִ)
;

commit;

insertִintoִt2
select
ִִrownum,
ִִd1.id_par,
ִִrownum,
ִִrpad('x',100)
from
ִִt1ִִd1,
ִִt1ִִd2
;

commit;

begin
ִִdbms_stats.gather_table_stats(
ִִִִuser,
ִִִִ't1',
ִִִִcascadeִ=>ִtrue,
ִִִִestimate_percentִ=>ִnull,
ִִִִmethod_optִ=>ִ'forִallִcolumnsִsizeִ1'
ִִ);
end;
/

begin
ִִdbms_stats.gather_table_stats(
ִִִִuser,
ִִִִ't2',
ִִִִcascadeִ=>ִtrue,
ִִִִestimate_percentִ=>ִnull,
ִִִִmethod_optִ=>ִ'forִallִcolumnsִsizeִ1'
ִִ);
end;
/

createִorִreplaceִviewִavg_val_viewִAS
selectִ
ִִid_par,ִavg(val)ִavg_val_t1ִ
fromִִt2
groupִby
ִִid_par;


spoolִview_merge_01

setִautotraceִtraceonlyִexplain

prompt
promptִִBaselineִexample
promptִִDefaultִvalueִforִ_complex_view_merging
promptִִQueryִunhinted
prompt

select
ִִt1.vc1,ִavg_val_t1
from
ִִt1,ִavg_val_view
whereִ
ִִt1.vc2ִ=ִlpad(18,32)
andִavg_val_view.id_parִ=ִt1.id_par
;

alterִsessionִsetִ"_complex_view_merging"=true;

prompt
promptִִComplexִviewִmergingִenabled
promptִִQueryִunhinted
prompt

select
ִִt1.vc1,ִavg_val_t1
from
ִִt1,ִavg_val_view
whereִ
ִִt1.vc2ִ=ִlpad(18,32)
andִavg_val_view.id_parִ=ִt1.id_par
;

prompt
promptִִComplexִviewִmergingִenabled
promptִִno_mergeִhintִapplied
prompt

select
ִִ/*+ִno_mergeִ(avg_val_view)ִ*/
ִִt1.vc1,ִavg_val_t1
from
ִִt1,ִavg_val_view
whereִ
ִִt1.vc2ִ=ִlpad(18,32)
andִavg_val_view.id_parִ=ִt1.id_par
;


alterִsessionִsetִ"_complex_view_merging"=false;

prompt
promptִִComplexִviewִmergingִdisabled
promptִִQueryִunhinted
prompt

select
ִִt1.vc1,ִavg_val_t1
from
ִִt1,ִavg_val_view
whereִ
ִִt1.vc2ִ=ִlpad(18,32)
andִavg_val_view.id_parִ=ִt1.id_par
;


prompt
promptִִComplexִviewִmergingִdisabled
promptִִQueryִhintedִtoִmergeִ-ִdoesn'tִwork
prompt

select
ִִ/*+ִmerge(avg_val_view)ִ*/
ִִt1.vc1,ִavg_val_t1
from
ִִt1,ִavg_val_view
whereִ
ִִt1.vc2ִ=ִlpad(18,32)
andִavg_val_view.id_parִ=ִt1.id_par
;


setִautotraceִoff

alterִsessionִsetִ"_complex_view_merging"=true;

spoolִoff

Here is the result from 10.2.0.4:

Baselineִexample
Defaultִvalueִforִ_complex_view_merging
Queryִunhinted


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ3226881135

------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִ|ִNameִ|ִRowsִִ|ִBytesִ|ִCostִִ|
------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|ִִִ1ִ|ִִHASHִGROUPִBYִִִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|*ִִ2ִ|ִִִHASHִJOINִִִִִִִִִ|ִִִִִִ|ִִִִ32ִ|ִִ3872ִ|ִִִִִ8ִ|
|*ִִ3ִ|ִִִִTABLEִACCESSִFULL|ִT1ִִִ|ִִִִִ1ִ|ִִִ114ִ|ִִִִִ2ִ|
|ִִִ4ִ|ִִִִTABLEִACCESSִFULL|ִT2ִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
------------------------------------------------------------

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

ִִִ2ִ-ִaccess("ID_PAR"="T1"."ID_PAR")
ִִִ3ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Complexִviewִmergingִenabled
Queryִunhinted


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ3226881135

------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִ|ִNameִ|ִRowsִִ|ִBytesִ|ִCostִִ|
------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|ִִִ1ִ|ִִHASHִGROUPִBYִִִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|*ִִ2ִ|ִִִHASHִJOINִִִִִִִִִ|ִִִִִִ|ִִִִ32ִ|ִִ3872ִ|ִִִִִ8ִ|
|*ִִ3ִ|ִִִִTABLEִACCESSִFULL|ִT1ִִִ|ִִִִִ1ִ|ִִִ114ִ|ִִִִִ2ִ|
|ִִִ4ִ|ִִִִTABLEִACCESSִFULL|ִT2ִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
------------------------------------------------------------

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

ִִִ2ִ-ִaccess("ID_PAR"="T1"."ID_PAR")
ִִִ3ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Complexִviewִmergingִenabled
no_mergeִhintִapplied


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ2162534217

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִ|ִNameִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ2ִ|ִִִTABLEִACCESSִFULLִִ|ִT1ִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ69ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִVIEWִִִִִִִִִִִִִִִ|ִAVG_VAL_VIEWִ|ִִִִ32ִ|ִִִ832ִ|ִִִִ26ִ|
|ִִִ4ִ|ִִִִHASHִGROUPִBYִִִִִ|ִִִִִִִִִִִִִִ|ִִִִ32ִ|ִִִ224ִ|ִִִִ26ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULL|ִT2ִִִִִִִִִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִaccess("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
ִִִ2ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Complexִviewִmergingִdisabled
Queryִunhinted


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ2162534217

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִ|ִNameִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ2ִ|ִִִTABLEִACCESSִFULLִִ|ִT1ִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ69ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִVIEWִִִִִִִִִִִִִִִ|ִAVG_VAL_VIEWִ|ִִִִ32ִ|ִִִ832ִ|ִִִִ26ִ|
|ִִִ4ִ|ִִִִHASHִGROUPִBYִִִִִ|ִִִִִִִִִִִִִִ|ִִִִ32ִ|ִִִ224ִ|ִִִִ26ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULL|ִT2ִִִִִִִִִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִaccess("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
ִִִ2ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Complexִviewִmergingִdisabled
Queryִhintedִtoִmergeִ-ִdoesn'tִwork


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ2162534217

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִ|ִNameִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ2ִ|ִִִTABLEִACCESSִFULLִִ|ִT1ִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ69ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִVIEWִִִִִִִִִִִִִִִ|ִAVG_VAL_VIEWִ|ִִִִ32ִ|ִִִ832ִ|ִִִִ26ִ|
|ִִִ4ִ|ִִִִHASHִGROUPִBYִִִִִ|ִִִִִִִִִִִִִִ|ִִִִ32ִ|ִִִ224ִ|ִִִִ26ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULL|ִT2ִִִִִִִִִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִaccess("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
ִִִ2ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.

And we get the same from 11.1.0.6:

Baselineִexample
Defaultִvalueִforִ_complex_view_merging
Queryִunhinted


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ3226881135

------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִ|ִNameִ|ִRowsִִ|ִBytesִ|ִCostִִ|
------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|ִִִ1ִ|ִִHASHִGROUPִBYִִִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|*ִִ2ִ|ִִִHASHִJOINִִִִִִִִִ|ִִִִִִ|ִִִִ32ִ|ִִ3872ִ|ִִִִִ8ִ|
|*ִִ3ִ|ִִִִTABLEִACCESSִFULL|ִT1ִִִ|ִִִִִ1ִ|ִִִ114ִ|ִִִִִ2ִ|
|ִִִ4ִ|ִִִִTABLEִACCESSִFULL|ִT2ִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
------------------------------------------------------------

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

ִִִ2ִ-ִaccess("ID_PAR"="T1"."ID_PAR")
ִִִ3ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Complexִviewִmergingִenabled
Queryִunhinted


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ3226881135

------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִ|ִNameִ|ִRowsִִ|ִBytesִ|ִCostִִ|
------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|ִִִ1ִ|ִִHASHִGROUPִBYִִִִִִ|ִִִִִִ|ִִִִ23ִ|ִִ2783ִ|ִִִִ28ִ|
|*ִִ2ִ|ִִִHASHִJOINִִִִִִִִִ|ִִִִִִ|ִִִִ32ִ|ִִ3872ִ|ִִִִִ8ִ|
|*ִִ3ִ|ִִִִTABLEִACCESSִFULL|ִT1ִִִ|ִִִִִ1ִ|ִִִ114ִ|ִִִִִ2ִ|
|ִִִ4ִ|ִִִִTABLEִACCESSִFULL|ִT2ִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
------------------------------------------------------------

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

ִִִ2ִ-ִaccess("ID_PAR"="T1"."ID_PAR")
ִִִ3ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Complexִviewִmergingִenabled
no_mergeִhintִapplied


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ2162534217

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִ|ִNameִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ2ִ|ִִִTABLEִACCESSִFULLִִ|ִT1ִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ69ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִVIEWִִִִִִִִִִִִִִִ|ִAVG_VAL_VIEWִ|ִִִִ32ִ|ִִִ832ִ|ִִִִ26ִ|
|ִִִ4ִ|ִִִִHASHִGROUPִBYִִִִִ|ִִִִִִִִִִִִִִ|ִִִִ32ִ|ִִִ224ִ|ִִִִ26ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULL|ִT2ִִִִִִִִִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִaccess("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
ִִִ2ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.


Complexִviewִmergingִdisabled
Queryִunhinted


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ2162534217

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִ|ִNameִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ2ִ|ִִִTABLEִACCESSִFULLִִ|ִT1ִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ69ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִVIEWִִִִִִִִִִִִִִִ|ִAVG_VAL_VIEWִ|ִִִִ32ִ|ִִִ832ִ|ִִִִ26ִ|
|ִִִ4ִ|ִִִִHASHִGROUPִBYִִִִִ|ִִִִִִִִִִִִִִ|ִִִִ32ִ|ִִִ224ִ|ִִִִ26ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULL|ִT2ִִִִִִִִִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִaccess("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
ִִִ2ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Complexִviewִmergingִdisabled
Queryִhintedִtoִmergeִ-ִdoesn'tִwork


Execution plan
----------------------------------------------------------
Planִhashִvalue:ִ2162534217

---------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִ|ִNameִִִִִִִִִ|ִRowsִִ|ִBytesִ|ִCostִִ|
---------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ1ִ|ִִHASHִJOINִִִִִִִִִִִ|ִִִִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ95ִ|ִִִִ29ִ|
|*ִִ2ִ|ִִִTABLEִACCESSִFULLִִ|ִT1ִִִִִִִִִִִ|ִִִִִ1ִ|ִִִִ69ִ|ִִִִִ2ִ|
|ִִִ3ִ|ִִִVIEWִִִִִִִִִִִִִִִ|ִAVG_VAL_VIEWִ|ִִִִ32ִ|ִִִ832ִ|ִִִִ26ִ|
|ִִִ4ִ|ִִִִHASHִGROUPִBYִִִִִ|ִִִִִִִִִִִִִִ|ִִִִ32ִ|ִִִ224ִ|ִִִִ26ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULL|ִT2ִִִִִִִִִִִ|ִִ1024ִ|ִִ7168ִ|ִִִִִ5ִ|
---------------------------------------------------------------------

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

ִִִ1ִ-ִaccess("AVG_VAL_VIEW"."ID_PAR"="T1"."ID_PAR")
ִִִ2ִ-ִfilter("T1"."VC2"='ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ18')

Note
-----
ִִִ-ִcpuִcostingִisִoffִ(considerִenablingִit)


Sessionִaltered.

The results correspond to those from the previous versions.

Conclusion for part 1: For first three scripts I couldn't detect any significant differences to the previous versions. A small oddity showed up regarding the I/O cost of a tiny sort operation when disabling the cpu_costing, but that could also be reproduced in 9.2.0.8, so that's not an actual difference to previous versions.

Because I have to use this silly dot character instead of space in order to keep a reasonable formatting in this blogger environment, I'll provide an updated code depot that contains the scripts and results I used for this series.

You can download it from here: My homepage (SQLTools++, an open source lightweight SQL Oracle GUI for Windows)

I'll update it from time to time when I do significant changes, and each time I add a new part to the series you'll find the updated code depot there as well.

The original code depot (still maintained by Jonathan) can be found here.