Tags10.2.0.3 10.2.0.4 10.2.0.5 10g 10gR2 126.96.36.199 188.8.131.52 184.108.40.206 11g 11gR2 ANSI SQL ASH AWR benchmark bind peeking bug bug-or-feature CBQT coalesce coalesce subquery collections connect by control files docs dynamic views EBR Exadata fail FBI Firefox full join funny stuff Hotsos indexes iPX ITL javelin JNI join elimination join factorization memory merge MOS muxers nested loops OIC OICA OOM optimizer_features_enable opt_param ORA-04031 Oracle JVM outer join PDML plugins poll presentations PX RAC scripts shared cursors SPECjAppServer2004 sql_trace statistics Statspack subqueries subquery factoring subquery unnesting Sun Tom Kyte tpt trace files undocumented wait events wish list
Tag Archives: CBQT
June 7, 2012 – 13:40
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.
June 28, 2011 – 21:03
Here is a nice example of what Oracle 220.127.116.11 is able to do with a subquery inside an expression. It can unnest it – that is a new 18.104.22.168 feature of the transformation part of the CBO.
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.
April 7, 2010 – 23:09
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.
March 25, 2010 – 23:28
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.
January 18, 2010 – 00:16
There are 19 new hints in Oracle 22.214.171.124:
SQL> select name from v$sql_hint where version='126.96.36.199'; 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).
December 1, 2009 – 01:07
Oracle 188.8.131.52 has introduced one of the most intuitive features to the cost-based transformation: join factorization.