RULE Optimizer Vs. CBO

With Oracle 10g RULE based optimizer is not supported by Oracle. All DBA’s with a bit of experience know that internal queries of Oracle in the dictionary have a lot of hints ” /*+ RULE *”. This morning (in Italy) i’ve read i question about the slowness of the query

SQL> select bytes
2 from dba_extents i
3 where i.owner=’QSDF’
4 and i.segment_name=’MLKQJSDF’
5* and i.extent_id=0
SQL> /
BYTES
——————
131072
Elapsed: 00:01:21.03

This made me curios, effectively the query take a lot of cpu resources, executed with autotrace in SQL*Plus it reveals a huge amount of logical reads with an execution plan with over 100 of executions steps. We use optimizer_mode=FIRST_ROWS system wide, with ALL_ROWS the logical read are also more.

Adding the /*+ RULE */ hint the query is not a flash but even faster than with default CBO.

The moral i think is: with dictionary queries consider using RULE optimizer.

Advertisements

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

%d bloggers like this: