Filter Subqueries

Precis: An early explanation of Scalar Subquery Caching (and its impact on performance)

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.

rem
rem     Script:         filter_cost_01.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Sept 2003
rem

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 <= 1000 -- > comment to avoid WordPress format issue
)
select
        mod(rownum,6),
        rownum,
        rownum,
        rpad('x',60)
from
        generator       v1,
        generator       v2
where
        rownum  <= 20000 -- > comment to avoid WordPress format issue
;

begin
        dbms_stats.gather_table_stats(
                ownname          => 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 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 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) and I introduced the relevant 10g hint after receiving a note from Jeff Moss – see comment below.

Footnote

I’ve re-run this test many times over the years since I first wrote it, and the same behaviour appears in all the versions I’ve tested so far, all the way up to 19.3.0.0. The only change is in the speed of execution as the hardware got faster and some of the buffer access interrnals were enhanced.  The run-time for the “unlucky” query had dropped to 5.33 (CPU) second in my most recent test, mostly due to the 3,338 executions of the subquery involving 734K buffer visits and 11M row accesses.