Here’s a little demonstration of a feature that can cause random fluctuations in performance because of an unlucky data item. It starts with an emp table holding 20.000 employees spread across six departments, and then moves one employee to a new (carefully chosen) department. You will have to run this in version 9i or later, as it makes use of subquery factoring to generate the emp table.
create table emp( dept_no not null, sal, emp_no not null, padding, constraint e_pk primary key(emp_no) ) as with generator as ( select --+ materialize rownum id from all_objects where rownum ) select mod(rownum,6), rownum, rownum, rpad('x',60) from generator v1, generator v2 where rownum user, tabname => 'EMP', estimate_percent => null, block_sample => true, method_opt => 'for all columns size 1', degree => null, granularity => 'default', cascade => true ); end; / select count(*) from ( select /*+ no_merge */ outer.* from emp outer where outer.sal > ( select /*+ no_unnest */ avg(inner.sal) from emp inner where inner.dept_no = outer.dept_no ) ) ;
The query is a little strange as it uses the /*+ no_unnest */ hint to stop the optimizer from unnesting the subquery. Unnesting would normally happen in simple cases like this one, but more complex queries aren’t alway able to undergo the unnest transformation. The query also has a /*+ no_merge */ hint so that it can execute and instantiate the main body of the query, then only output a count. You should find that the query takes a fraction of a second to run – possibly less than one hundredth if you are able to hold the emp table cached in memory – returning the value 9,998.
Now execute the following update, and run the query again:
update emp set dept_no = 432 where dept_no = 1 and rownum = 1 ;
Running at 1.6GHz, my laptop takes about 21 seconds of flat out CPU time to complete the query with just this one row-change, so be prepared for a bit of a wait. This change in performance is what happens when a particular run-time optimisation known as scalar subquery caching fails (and this correlated subquery is just one example of the more general class of scalar subquery).
When the query ran quickly, Oracle managed to cache the average salaries for the six departments as it ran through the first six rows in the tablescan. But these values were stored in a hash table keyed by the department number.
When I changed the first occurrence of department 1 to 432, Oracle got to the row with department 432 before it got to a row with department 1, and stored the average salary for department 432 in the hash table location that would otherwise have been used by department 1.
Unfortunately, the caching algorithm does not allow for hash collisions of this type, so the next 3,333 times Oracle needed the average salary for department 1 it discovered that it wasn’t cached, and ran the subquery again, .. and again, .. and again …
If you do find that you have a particular query that is subject to this problem you may have to write it to avoid the filter subquery (and you may find that a simple /*+ unnest */ hint will be enough to pre-empt the issue).
Here is a counter-intuitive mechanism that will be highly cost-effective in some cases – I replace the first occurrence of the emp table with a non-mergeable inline view that sorts the emp table by department number **:
select count(*) from ( select /*+ no_merge */ outer.* from ( select /*+ no_merge qb_name(ordering) no_eliminate_oby(@ordering) */ * from emp order by dept_no ) outer where outer.sal > ( select /*+ no_unnest */ avg(inner.sal) from emp inner where inner.dept_no = outer.dept_no ) ) /
On my laptop, using this sorting strategy, my response time dropped back to 0.02 seconds. This works because the code now acquires the department numbers in order, and Oracle doesn’t even do a lookup to the hash table if the current row uses the same input as the previous row.
** It has occurred to me that in 10gR2 you may need to include a qb_name() hint and a no_eliminate_oby hint in the inline view to stop the optimizer from eliminating what it sees as a redundant order by. (See the end of this article for an example of the necessary hints). But I don’t have a 10.2 database to hand to check this at the moment – so I’ll have to test that some time in the next couple of days. (Relevant 10g hint introduced in ordering sub-query after receiving note from Jeff Moss – see comments below).