Actually it’s probably not the NOT IN that’s nasty, it’s the thing you get if you don’t use NOT IN that’s more likely to be nasty. Just another odd little quirk of the optimizer, which I’ll demonstrate with a simple example (running under 22.214.171.124 in this case):
I thought I’d try to spend some of today catching up on old comments – first the easier ones, then the outstanding questions on Oracle Core.
The very first one I looked at was about pushing predicates, and the specific comment prompted me to jot down this little note about the 10053 trace file (the CBO trace).
An interesting little problem appeared on the Oracle-L mailing list earlier on this week – a query ran fairly quickly when statistics hadn’t been collected on the tables, but then ran rather slowly after stats collection even though the plan hadn’t changed, and the tkprof results were there to prove the point. Here are the two outputs (edited slightly for width – the original showed three sets of row stats, the 1st, avg and max, but since the query had only been run once the three columns showed the same results in each case):
In my previous post, I discussed how for larger tables that really do have randomised data, not even setting the new TABLE_CACHED_BLOCKS to its 255 maximum value is going to make any real difference to the resultant Clustering Factor (CF) of an index. The 255 maximum value here protects us somewhat from abusing this capability […]
Here’s a live example demonstrating a point I’ve often made – you have to be very detailed in your hinting or Oracle will find a way to obey your hints and do the wrong thing. A recent posting on the OTN database forum gave use the following query and execution plan:
I’ve written about dynamic sampling in the past, but here’s a little wrinkle that’s easy to miss. How do you get the optimizer to work out the correct cardinality for a query like (the table creation statement follows the query):
select count(*) from t1 where n1 = n2 ; create table t1 as with generator as ( select --+ materialize rownum id from dual connect by level <= 1e4 ) select mod(rownum, 1000) n1, mod(rownum, 1000) n2 from generator v1, generator v2 where rownum <= 1e6 ;
If you’re running 11g and can changed the code there are a couple of easy options – adding a virtual column, or applying extended stats and then modifying the SQL accordingly would be appropriate.
This post is a brief discussion about the advantages of activating parallelism by altering the session environment instead of using the alternative ways (hints, DDL). The latter ways are the most popular in my experience, but I have noticed that their popularity is actually due, quite frequently, more to imperfect understanding rather than informed decision - and that's a pity since "alter session force parallel query" can really save everyone a lot of tedious work and improve maintainability a great deal.
We will also check that issuing
alter session force parallel query parallel N;
is the same as specifying the hints
/*+ parallel (t,N) */ /*+ parallel_index (t, t_idx, N) */
for all tables referenced in the query, and for all indexes defined on them (the former is quite obvious, the latter not that much).
Cost Based Oracle – Fundamentals (November 2005)
But the most interesting function for our purposes is sys_op_countchg(). Judging from its name, this function is probably counting changes, and the first input parameter is the block ID portion (object_id, relative file number, and block number) of the table’s rowid, so the function is clearly matching our notional description of how the clustering_factor is calculated. But what is that 1 we see as the second parameter?
Believe me, this article is worth reading I’m currently not allowed to discuss Oracle 12c Database goodies but I am allowed to discuss things perhaps initially intended for 12c that are currently available and already back-ported to 11g. This includes a wonderful improvement in the manageability of how the Clustering Factor (CF) of an index can now […]
Recent thread in the OakTable mailing list prompted me to create a poll and ask about the ways DBAs use system statistics in real systems. If you struggle to understand what system statistics is and what are the available options, here is the suggested reading:
Documentation – System Statistics
Best Practices for Gathering Optimizer Statistics, Oracle whitepaper
System Statistics – Troubleshooting Oracle Performance