A simple chapter from the Jonathan Lewis's book is available for download at this address: Chapter 05: The Clustering Factor

The book will be available for shipping in 5th of November at Amazon online bookstore: Cost-Based Oracle Fundamentals

Labels: CBO, Link

Permanent Link  @ 2 Comments on This Post  @ Comment Here

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

Permanent Link  @ 0 Comments on This Post  @ Comment Here

The following interesting issue does not have clear explanation till now.

I have query that is using the following predicates.
...
AND COL1 LIKE '%%'
AND COL2 LIKE '%%'
AND COL3 LIKE '%%'
AND COL4 LIKE '%%'
...

May be I should explain from where is coming this strange query.
If you are developping some application and you need to generate some dynamic search query you can put a bind variables between the '%' symbols and to build dynamically this query. Usually it looks for Oracle like this:
...
AND COL1 LIKE '%:B1%'
AND COL2 LIKE '%:B2%'
AND COL3 LIKE '%:B3%'
AND COL4 LIKE '%:B4%'
...

During the investigation for the whole query I have removed the bind variables just to test it with real values. I put some real values for LIKE clauses. I have tested it without values for the bind variables as well. During testing I have changed incidentally '%%' to '%' within the LIKE clauses:
...
AND COL1 LIKE '%'
AND COL2 LIKE '%'
AND COL3 LIKE '%'
AND COL4 LIKE '%'
...

The whole execution plan has changed! Without clear reason!
Because these different execution plans, the cost for the first and second query is 69 but for the third one is 175, the whole statistics for buffer reads and recursive calls are changed too. This unexpected behaviour have provoked me to try to find the reason why the CBO is changing the whole plan.
First of all, there is no difference for the LIKE clause when you put '%%' or '%' symbols after it. The returned results are the same in both cases. Of course when your variable have some value then the generated LIKE will looks like this LIKE '%A%' (for static SQL). In this case the count of percent symbols will matter: LIKE '%A%' will return different results than LIKE '%'. But if you don't use a variable it will does not matter, the query will return the same result set in both cases.
This case is very specific, it will matter only when you write some static SQL with LIKE '%%' clauses but they do not restrict anything, it is expected you do not put unrestrictive clauses within your SQL queries. Anyway, I have decided to make some investigation about that.
I have discovered an additional fact. The execution plan have changed when changes have been applied just for one of the columns that is allowing NULL values - COL1:
...
AND COL1 LIKE '%'
AND COL2 LIKE '%%'
AND COL3 LIKE '%%'
AND COL4 LIKE '%%'
...

You can see above that only one simple change is applied for COL1 and that's changing everything, again cost is 175. For example the following doesn't impact the execution plan in any way:
...
AND COL1 LIKE '%%'
AND COL2 LIKE '%'
AND COL3 LIKE '%'
AND COL4 LIKE '%'
...

The only difference between all four columns is that COL2, COL3, COL4 does not allows NULL values but COL1 allows. Another finding - from the execution plan I have seen that the index for COL1 is used only when LIKE '%%' is used, when you write LIKE '%' then the index is not used.
I have supposed that the decision of the CBO depends from the fact that an index for COL1 exists and that column allows NULL values which affects the decision of the CBO.
After that I have changed another of the NOT NULL columns (that have index over it) - COL3 to allow NULL values and rewrite the clauses like that:
...
AND COL1 LIKE '%%'
AND COL2 LIKE '%%'
AND COL3 LIKE '%'
AND COL4 LIKE '%%'
...

But the execution plan have not been affected - Cost: 69. Then I recompute statistics for that table and in result, different execution plan for the above query - Cost: 175.
I think this shows that the CBO is affected of the possibility to find expected rows inside the index, when column is NULLable then the possibility is small because the index will not contain the NULL values. But why it is affected from LIKE clauses in this way? When you put '%%' it is using the index, in case you put only '%' it is not using it.
I have tried to trace the CBO but I didn't succeed to find something additional that can help me on that.

I will appreciate any comments about this issue as well!

Labels: CBO

Permanent Link  @ 3 Comments on This Post  @ Comment Here