Recently I’ve seen not so smart optimizer behavior: one query took long time to parse, and ended with an error hitting PGA_AGGREGATE_LIMIT in few minutes; another query was just parsed for ages while using reasonable (under 2G :)) amount of PGA and still could hit PGA_AGGREGATE_LIMIT but after way more time – up to an hour.
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.
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.
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.
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.
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.
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.
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.
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;
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.
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.
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.
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.
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.
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.
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.
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.
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.
Timur Akhmadeev's blog 