Category Archives: CBO

Cost-based optimizer

OIC(A) again – 2

Continuing from the previous post, here is one more case when adjusting optimizer_index_cost_adj may hurt you.

Continue reading

System statistics poll

Recent thread in the OakTable mailing list prompted me to create a poll and ask about the ways DBAs use system statistics in real systems. If you struggle to understand what system statistics is and what are the available options, here is the suggested reading:
Documentation – System Statistics
Best Practices for Gathering Optimizer Statistics, Oracle whitepaper
System Statistics – Troubleshooting Oracle Performance
Understanding the different modes of System Statistics, blog series

Ignoring hints

A hint is an instruction to the optimizer

This is what’s written in Oracle documentation. Instruction is defined as

a code that tells a computer to perform a particular operation

Which means Oracle CBO must obey the hints and must perform particular operation. The latter is hard to define correctly and explain precisely because it involves the logic of the block-box (what Cost Based Optimizer is). Some of the operations are mentioned in the standard Oracle documentation, some of them scattered across different places, and there are exceptions as usual. I think I’ll list here these cases which could lead to “ignoring hints” with the links to documentation/blogs.

Continue reading

OIC(A) again

Issues with OICA/OIC (OPTIMIZER_INDEX_COST_ADJ/OPTIMIZER_INDEX_CACHING) parameters have already been mentioned many times. Recently I’ve noticed one more and I think I didn’t see this case somewhere else on the Internet so I’ll share it.

Continue reading

Consultancy

I have a permanent job at the NetCracker‘s System Performance group. Recently I was offered to do one day job outside, on-site in another company, which coincidentally has an office close to NetCracker’s Moscow office. It was an opportunity to apply my skills in a completely different situation which I couldn’t miss; plus I’ve never done public presentations before and this was a good occasion to practice that. Here I’d like to write down some notes how the event went.

Continue reading

Unnesting disjunctive subqueries (with OR predicate)

Jonathan Lewis has recently posted a good example of CBO not good enough in transforming specific query types. A recent thread on the SQL.ru Oracle forum reminded me of this issue.

Continue reading

FBI trouble

In our application we extensively use a function-based index on an important table. Couple of days ago I’ve seen an interesting issue associated with this FBI, view and a GROUP BY query. I have to say I don’t have an explanation what exactly it is and how I should call it properly, hence just “trouble” in the subject line.

Continue reading

CBO isn’t perfect

And you should remember that. Here is a nice example how Cost Based Optimizer can miss an obvious option (which is available to human eye and Oracle run-time with a hint) while searching for the best plan. CBO simply doesn’t consider Index Skip Scan with constant ‘in list’ predicates in the query, although it costs skip scan for a join. Such bits are always popping up here and there, so you just can’t say “The Cost Based Optimizer examines all of the possible plans for a SQL statement …”, even if Optimizer Team tells you CBO should do so. There will always be places where CBO will do less than possible to come to the best plan and will need a help from your side, such as re-written SQL or a hint.

Scalar subquery unnesting

Here is a nice example of what Oracle 11.2.0.2 is able to do with a subquery inside an expression. It can unnest it – that is a new 11.2.0.2 feature of the transformation part of the CBO.

Continue reading

ORDER BY

This is a quick note on the importance of ORDER BY for the order of the result set produced by a SELECT. The mantra is very simple:

Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

Repeat it as necessary many times everywhere, because it’s true (Update: but see the first comment from Sokrates for an exception to the rule). I want to say it again because yesterday I’ve seen it in action. A query that has been working correctly for several years without an issue has started to randomly return rows without an expected order. The query looks like this:

select ...
  from (select ...
          from t
          where ...
         order by col1
       ) t1
     , t2
 where t1.id = t2.id;

i.e. it joins an inline view with another table on the primary key column. It worked well before 11g due to the fact that t2 was always joined using nested loops. However, after an upgrade the CBO has decided to use nested loops join batching which causes the order of rows to change when the data is on disk. That’s it – the query starts to return “unordered” result set sometimes, which in fact doesn’t have to be ordered. I also recommend to read Doc 378254.1 Order of the resultset changes when using a different version/patchset.

Distinct placement

As a follow-up to a recent post on different names Oracle can use for the intermediate views, here is a quick example of the technique called distinct placement.

Continue reading

Enabling constraint in parallel

Recently I did some tuning of data generation scripts, which purpose is to build large amount of representative data for application testing. Direct-path inserts are in use and as a prerequisite all constraints and indexes on target tables are disabled before the load and are enabled after it. Since I wanted to utilize available resources on the machine for that task, almost each step uses parallel execution. Well, kind of almost, because enabling constraints didn’t run in parallel, although I’ve politely asked Oracle to do so. I’ll explain here why it didn’t work.

Continue reading

_connect_by_use_union_all

This is just a short note on the parameter introduced in the 11gR2 called _connect_by_use_union_all. I’ve noticed it for the first time in Doc ID 7210630.8, which gives a brief overview of the changes made to the way CBO generates plans for hierarchical queries. As usually happens, the change helps to one problem, but produces a bunch of unexpected side effects, such as wrong or incorrect results, and even ORA-00904 in a simple case. All these bugs have been fixed in the 11.2.0.2 patch set, but who knows how many issues related to the change are still there? If you see something unusual with a standard Oracle hierarchical query in the 11gR2, I think it’s good to try turning this parameter off and see if it helps.

How to kill performance with 16MB table

If you think 16MB tables are so small that can’t be root cause of significant performance problems with SELECT queries, then read about my today’s headache – a story of 16MB table worth something like 270 seconds of response time.

Continue reading

Extended statistics and function-based indexes

About a year ago I’ve discovered nice feature of Oracle 10gR2 CBO: to overcome an issue with calculated selectivity for predicates on multiple columns, it can use DISTINCT_KEYS of the available index. Couple of weeks ago the bug fix mentioned in the OTN thread actually helped to solve a performance issue of a query. And about a week ago I found that this fix doesn’t work with function-based indexes. So, this post is about it.

Continue reading

NL join (ordered)

Some time ago there was a thread on the SQL.ru forum where user has asked the never-ending question “why CBO is doing this?”. The problem was a simple count(*) of parent-child tables join with no FK constraint was executed in very strange way: via NESTED LOOPS using child as a driving table.

Continue reading

CONNECT BY oddity

This week I’ve seen an issue with a CONNECT BY query: for some reason Oracle 10.2.0.4 decided to build a weird plan (the query is weird too, but that’s not my point here :)). An explanation of why that happened looks interesting, so here it is.

Continue reading

Enhanced Subquery Optimizations in Oracle

While googling I found a fresh article from Oracle for the VLDB journal: Enhanced Subquery Optimization in Oracle. It primarily discusses subqueries – how Oracle deals with them on optimization and at run-time.

Continue reading

Cardinality of table collection expression

Table collection expression has been in Oracle for more than 10 years. It is handy for passing an array to Oracle. Quite often, though, there is was a well-known problem with a default cardinality associated with table collection expressions used in the SQL.

Continue reading

Native full outer join

Starting with version 11gR1 Oracle database uses native FULL JOIN implementation based on HASH JOIN whenever possible. It’s good to know that this functionality first appeared in Oracle 10.2.0.3 and could be used to overcome different issues, including performance and bugs. Here is an example appeared on the SQL.ru recently:

Continue reading

Follow

Get every new post delivered to your Inbox.

Join 290 other followers