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.
Thanks to Nigel Antell – he has posted a comment on one of Tanel Poder’s blog notes with a link to MOS Doc ID 1269139.1. In this note a parameter called _MEMORY_IMM_MODE_WITHOUT_AUTOSGA is described. It allows Oracle 11gR2 to adjust sizes of shared memory structures even if Automatic (Shared) Memory Management have been explicitly disabled; [...]
Oracle 11g has introduced a hell lot of new features related to Parallel Execution – like statement queuing, in-memory PX and auto-DOP, to name a few. There are also small bits of enhancements here and there – like default value of the PARALLEL_EXECUTION_MESSAGE_SIZE and an introduction of an easy way to stick PX slaves to [...]
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 [...]
Here is a recent thread from OTN forums. The question raised is WTF? whether a hint in the examples section of DBMS_PARALLEL_EXECUTE documentation have to be used or not and why it is there. What do you think about it? Have a look in the documentation and mark your choice in a poll: PS. In [...]
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.
You most likely have seen this error before: ORA-01719: outer join operator (+) not allowed in operand of OR or IN Cause: An outer join appears in an or clause. Action: If A and B are predicates, to get the effect of (A(+) or B), try (select where (A(+) and not B)) union all (select [...]
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.
There was a thread recently on the OTN forum (it’s currently down, that’s why I’m writing in the blog; here is a stale version of the page from Google cache; at the time of writing this version had 1 comment) about reasons for control file sequential read accounted to particular query.
As pointed out by Greg Rahn in the comments to Mark Rittman’s blog post, new Oracle 11gR2 feature called In-Memory Parallel Execution work well in a single-instance environment.
There are 19 new hints in Oracle 11.2.0.1: 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).
Today I’ve run into a deadlock on my test 11gR2 database. The deadlock wasn’t something peculiar, but generated trace file was.
Oracle 11.2.0.1 has introduced one of the most intuitive features to the cost-based transformation: join factorization.
