Strange behaviour of the CBO, part 2

My experience, findings and thoughts in my daily work with Oracle products

Published Thursday, August 11, 2005 by Radoslav Rusinov | E-mail this post

After playing around with setting of columns to allow NULL values or not (setting COL1 and COL3 to allow NULL values, test and put it again to their default condition) and precomputing statistics, the issue from previous post become more unclear.
Now the structure of the table is the same like it was before, statistics are fresh but cost for the execution plan is always 175. It doesn't matter what is inside the LIKE clauses
...
AND COL1 LIKE '%%'
AND COL2 LIKE '%%'
AND COL3 LIKE '%%'
AND COL4 LIKE '%%'
...

or

...
AND COL1 LIKE '%'
AND COL2 LIKE '%%'
AND COL3 LIKE '%%'
AND COL4 LIKE '%%'
...

or

...
AND COL1 LIKE '%a%'
AND COL2 LIKE '%b%'
AND COL3 LIKE '%c%'
AND COL4 LIKE '%d%'
...

I have removed all LIKE clauses from the query - no difference.
After that I recreate the index for COL1 and again recompute the statistics, the cost become 68 for the following query:
...
AND COL1 LIKE '%%'
AND COL2 LIKE '%%'
AND COL3 LIKE '%%'
AND COL4 LIKE '%%'
...

If you remember my previous post the cost and execution plan was absolutely opposite than current results. Now the following query have cost of 175:
...
AND COL1 LIKE '%'
AND COL2 LIKE '%%'
AND COL3 LIKE '%%'
AND COL4 LIKE '%%'
...

But the expected from me was 69! For all other variants of LIKE clauses it is the same: 175.

Next step: I have recreated the index for the second column (COL3) that I have played arround and then I have recomputed statistics again. In result, the cost is 18 and now the execution plan is stable independently from all different kind of LIKE clauses.
One friend of mine had suggestion about the count of '%' symbols and in result I run this query:
...
AND COL1 LIKE '%%%'
AND COL2 LIKE '%%'
AND COL3 LIKE '%%'
AND COL4 LIKE '%%'
...

But the cost remains 18. It seems that count of '%' does not matter when they are more than one.

At the end, this query have stable execution plan but nothing is clear for me. It seems the indexes was fragmented and the CBO chooses the execution plan according to the computed height and clustering factor of the indexes. After reorganizing, the CBO choose a better execution plan according to new statistics coming from recreated indexes.
Anyway, it is still not clear what exactly is happening.

In conclusion I can say few things at least:
1. Keep your table statistics fresh.
2. Reorganize your indexes that suffer from heavy update and delete activity. It will affect the CBO execution plans as well
3. Don't forget to keep backup of your old statistics in case of emergency case of slow query (or slow application) that will let you to return faster the application in its previous and stable condition (by importing old stable statistics) and after that to have enough time to investigate how the new statistics have affected the application.

Labels: CBO

0 Responses to “Strange behaviour of the CBO, part 2”

About me

» I'm Radoslav Rusinov
» From Sofia, Bulgaria
» Employer TechnoLogica Ltd.
» I am working as a Database Consultant in Sofia, Bulgaria. My main professional interests are in the database area and especially in the Oracle RDBMS, including database design, development, security and administration.
» The views expressed on this blog are my own and do not necessarily reflect the views of my employing company and its affiliates
» My profile