Oracle Scratchpad

February 19, 2007

Parallelism and CBO

Filed under: CBO,Parallel Execution,Troubleshooting — Jonathan Lewis @ 9:43 pm GMT Feb 19,2007

In the 9i Database Performance Tuning Guide and Reference part no: A96533-01, there is a convenient list of features that “require the CBO” on page 1-15, with the note

“Use of any of these features enables the CBO, even if the parameter OPTIMIZER_MODE is set to RULE.”

The list is as follows:

  • Partitioned tables and indexes
  • Index-organized tables
  • Reverse key indexes
  • Function-based indexes
  • SAMPLE clauses in a SELECT statement
  • Parallel query and parallel DML
  • Star transformations and star joins
  • Extensible optimizer
  • Query rewrite with materialized view
  • Enterprise Manager progress meter
  • Hash joins
  • Bitmap indexes and bitmap join indexes
  • Index skip scans

If you work through the list carefully, you will discover that the warning is a little ambiguous. For example, you can create bitmap indexes and the rule-based optimizer will ignore them; you can create function-based indexes and, if they start with simple columns, the rule-based optimizer may use them – but only the leading simple columns; you can parallel-enable a table and the rule-based optimizer will simply give up and hand over to the cost-based optimizer. So your interpreration of the phrase “use of any of these features” has to be a little flexible, and vary with feature.

The traps that this can lead can be quite funny. Here’s one I was asked about recently.

The  system is running rule-based; it’s just gone through a big export/import cycle to upgrade to 9.2.0.7. To speed things up, the indexes, primary key constraints, and statistics were created in parallel after the data had been loaded. Suddenly a lot of queries were running cost-based, even though the optimizer_mode was set to rule.

“Clearly” the problem was in the parallel indexing, so I ran up a little demonstration:

drop table t1;    

create table t1 
as 
select 
	rownum	id, 
	rpad('x',10)	small_vc, 
	rpad('x',400)	padding 
from 
	all_objects 
where 
	rownum <= 20000 
; 

create unique index t1_pk on t1(id) parallel (degree 2); 
alter table t1 add constraint t1_pk primary key(id); 

execute dbms_stats.gather_table_stats(user,'t1',cascade=>true) 

set autotrace traceonly explain    

alter session set optimizer_mode = rule; 
select count(*) from t1;    

set autotrace off 

Execution Plan (9.2.0.9) 
----------------------------------------- 
   0      SELECT STATEMENT Optimizer=RULE 
   1    0   SORT (AGGREGATE) 
   2    1     TABLE ACCESS (FULL) OF 'T1' 

So my first thought seemed to be wrong. I had been expecting the query to run parallel because there was a parallel-enabled index on the table (which you can check by looking at user_indexes.degree).

Then I had another look at the original email describing the problem – and changed the way I built the primary key. Rather than creating the index then creating the constraint, I changed the script to create the constraint and build the index at the same time, with the following impact on the execution plan:

 
-- create unique index t1_pk on t1(id) parallel (degree 2); 
alter table t1 add constraint t1_pk primary key(id) parallel 2;    

Execution Plan (9.2.0.8) 
---------------------------------------------------------- 
   0      SELECT STATEMENT Optimizer=RULE (Cost=8 Card=1) 
   1    0   SORT (AGGREGATE) 
   2    1     INDEX (FAST FULL SCAN) OF 'T1_PK' (UNIQUE) (Cost=8 Card=20000)    

This time the cost-based optimizer kicked in, and produced an execution plan that did a fast full scan on the index – but not a parallel fast full scan.

Checking the degree column from both user_tables and user_indexes, I found that the parallel degree I had used to enable the constraint had been applied to the table (hence the cost-based optimization), but not to the index (hence the serial execution path).

Does anyone want to bet on this being flagged as a bug one day, and being changed ?

2 Comments »

  1. Well…

    The fact that the parallelism used for the table is not applied to the index, it’s a good for me (different parallelism for index and table…why not?).

    I find very strange (=a bug) that the statements order you used can change how the optimizer work.

    Another question: are there differences in creating and index, then do a “alter index parallel 3″ and a “create index parallel 3″?

    I think yes. I saw this with tables, creating an object as “natively” parallel require also a different space allocation (i.e more sparse)

    Comment by Antonio — February 20, 2007 @ 2:10 pm GMT Feb 20,2007 | Reply

  2. Antonio, the comments you raise highlight the facts that (a) I didn’t write the conclusion very well, and (b) I fell into the same trap as the person who originally emailed me the problem.

    I haven’t changed the order of statements – I’ve simply moved from a ‘create index’/’add constraint’ to ‘create constraint’.

    However, my “instinctive” reading of the statement:

    alter table add constraint parallel N

    was that the operation of adding the constraint (which implicitly means the creation of the index) should be parallelised: hence my surprise that the table became parallel whilst the index did not.

    In fact, the statement is a shorthand for the pair of statements:

    alter table add constraint;
    alter table parallel (degree N);

    Creating the index as parallel 3 means you will get 6 PX slaves running, where the “top” three will each create an index on one-third of the data in its own data segment.

    This separation of work means you could typically allocate an average of half an extent per slave more than you really need for the index – with a minimum of 3 extents allocated for the index.

    In some versions of Oracle there were various features (sometimes bugs) relating to how Oracle tried to trim extents rather than putting the excess space into the index’s free lists.

    Comment by Jonathan Lewis — February 20, 2007 @ 7:20 pm GMT Feb 20,2007 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,305 other followers