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
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.