February 20, 2017 – 20:32
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.
Continue reading →
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 →
December 14, 2011 – 18:45
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 →
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 →
February 28, 2011 – 21:58
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 →
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 →
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 →
There are 19 new hints in Oracle 11.2.0.1:
SQL> select name from v$sql_hint where version='11.2.0.1';
NAME
------------------------------
COALESCE_SQ
NO_COALESCE_SQ
TRANSFORM_DISTINCT_AGG
NO_TRANSFORM_DISTINCT_AGG
CONNECT_BY_ELIM_DUPS
NO_CONNECT_BY_ELIM_DUPS
DST_UPGRADE_INSERT_CONV
NO_DST_UPGRADE_INSERT_CONV
STATEMENT_QUEUING
NO_STATEMENT_QUEUING
EXPAND_TABLE
NO_EXPAND_TABLE
XMLINDEX_SEL_IDX_TBL
FACTORIZE_JOIN
NO_FACTORIZE_JOIN
APPEND_VALUES
PLACE_DISTINCT
NO_PLACE_DISTINCT
NO_SUBSTRB_PAD
19 rows selected.
Some of them represents new CBO features (like FACTORIZE_JOIN/NO_FACTORIZE_JOIN). Here I’ll show purpose of COALESCE_SQ hint (NO_COALESCE is it’s inverse).
Continue reading →
Oracle 11.2.0.1 has introduced one of the most intuitive features to the cost-based transformation: join factorization.
Continue reading →