Obsolete cursors

In the previous post I wrote about strangely behaving V$SQL. For some reason there were duplicate rows leading to wrong results issue when running DBMS_XPLAN.DISPLAY_CURSOR for a particular child cursor. I tried to reproduce the issue using simple test case – and it was reproduced.

_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.

Missing wait event

Oracle 11.2.0.1 for Windows is out for some time. I’ve installed it on my 32-bit Windows XP machine because I like Windows 🙂 – just to check that it’s actually working fine. Playing with TPC-H test using Hammerora I’ve noticed an anomaly in the way Oracle reports IO waits for some queries.

When column name matters

There’s a thread on the OTN forum with a case of amusing behavior of the 11.2.0.1 JDBC driver: it fails to detect properly all bind variables in an SQL statement referencing column named NORDER.
WTF?!

PS.
1) Oracle’s SSO is actually Multi Sign-On because I have two accounts (one for OTN forums and one for MOS) and have to relogin each time I switch from MOS to OTN forums.
2) I hate that.

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.

Join factorization

Oracle 11.2.0.1 has introduced one of the most intuitive features to the cost-based transformation: join factorization.

OPT_PARAM and OPTIMIZER_FEATURES_ENABLE

Here’s a question appeared on the oracle-l mailing list recently:

Does anyone know if opt_param hint works with optimizer_features_enable in 11.1.0.7?