Tags10.2.0.3 10.2.0.4 10.2.0.5 10g 10gR2 22.214.171.124 126.96.36.199 11g 11gR2 ANSI SQL ASH AWR benchmark bug bug-or-feature CBQT coalesce subquery connect by dynamic views Exadata FBI indexes join elimination memory MOS nested loops ORA-04031 Oracle JVM PDML poll presentations PX scripts shared cursors SPECjAppServer2004 sql_trace statistics Statspack subqueries subquery factoring subquery unnesting Tom Kyte tpt 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 188.8.131.52 is able to do with a subquery inside an expression. It can unnest it – that is a new 184.108.40.206 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 220.127.116.11:
SQL> select name from v$sql_hint where version='18.104.22.168'; 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 22.214.171.124 has introduced one of the most intuitive features to the cost-based transformation: join factorization.