Sub-query unnesting and hierarchical queries oddity

Last week when tuning some query with a CONNECT BY clause in a correlated sub-query and reading it’s execution plan i discovered some strange things.Here is quick test case to demonstrate the findings :

TEST CASE :

Oracle Database version tested 11.2.0.3.9 and 12.1.0.2.0

The test data created have no sense it’s just for demonstration.

create table test_unest as select level as a,level +1 as b    from dual connect by level < 10000;
create table test_unest2 as select level as a,level +1 as b    from dual connect by level < 10000;
create table test_unest3 as select level as a,level +1 as b    from dual connect by level < 10000;

 CREATE OR REPLACE FORCE VIEW test_unest_v
(
a,
b,
l
)
AS
SELECT T.”A”,
T.”B”,
LEVEL
FROM test_unest T
START WITH T.A IN (‘1’, ‘2’, ‘3’)
CONNECT BY PRIOR T.A = T.B;

CREATE INDEX test_unest2_idx ON test_unest2 (a);

exec dbms_stats.gather_table_stats(null,’test_unest’);
exec dbms_stats.gather_table_stats(null,’test_unest2′);
exec dbms_stats.gather_table_stats(null,’test_unest3′);

TEST 1 :

select  tab1.a from test_unest3 tab1  where not exists ( select   /*+ QB_NAME(SUB1) */   1 from test_unest_v tab2,test_unest2 tab3 where tab1.a = tab3.a and tab2.b = tab3.b)  ;

(will return 9996 rows)

plan_filter

TEST 2 (add unnest hint):

select  /*+ UNNEST(@SUB1) */ tab1.a from test_unest3 tab1  where not exists ( select   /*+ QB_NAME(SUB1) */   1 from test_unest_v tab2,test_unest2 tab3 where tab1.a = tab3.a and tab2.b = tab3.b)  ;

Capture hash

Observation :

Wow ! so although there is a better plan (cost 26 and also you may have noticed the huge difference in the estimated number of rows but that’s another subject) the optimizer prefered to use the one with the filter row source (cost 53492). We need to add a hint to force the optimizer to unnest the subquery and do a hash join anti operation which is far better.Let’s take a look at the optimizer trace file .

Query without unnest hint :

$ more ORCL_2_ora_1773_10053_filter.trc | grep -i su:
SU: Considering subquery unnesting in query block SEL$1 (#0)
SU:   Checking validity of unnesting subquery SUB1 (#0)
SU:     SU bypassed: invalidated.
SU:   Validity checks failed.

Query with unnest hint :

$ more ORCL_2_ora_22812_10053_hash.trc | grep -i su:
SU: Considering subquery unnesting in query block SEL$1 (#0)
SU:   Checking validity of unnesting subquery SUB1 (#0)
SU:   Unnesting subquery query block SUB1 (#0)Registered qb: SEL$6B890ED9 0xbf392480 (SUBQ INTO VIEW FOR COMPLEX UNNEST SUB1)

Query after unnest :

Query after unnest

So the optimizer for no apparent reason marked the unnesting operation as invalid and when adding the unnest hint it is now valid (Strange).So watch out, i  tried the same test on a 12.1.0.2.0 version and there is no change.

NOTE: By the way I fixed the execution plan in production system by adding an sqlprofile using Carlos Sierra script “coe_load_sql_profile.sql” which reduced the execution time by more than 60%.

That’s it 😀

4 thoughts on “Sub-query unnesting and hierarchical queries oddity

  1. Hi Mahmoud,
    i would consider this as a cost based optimizer bug as it seems like some (new) heuristic checks or new unknown cross-limits are causing this.

    I have re-run your test case with 11.2.0.3.6 (on Linux) and it worked “out-of-the-box” with the following cbo snippet.
    ——————8<————————-
    SU: Checking validity of unnesting subquery SUB1 (#0)
    SU: Heuristic checks passed.
    SU: Unnesting subquery query block SUB1 (#0)SU: Heuristic checks passed.
    Registered qb: SEL$6B890ED9 0xd55523c0 (SUBQ INTO VIEW FOR COMPLEX UNNEST SUB1)
    ——————8<————————-

    I tried to find a corresponding fix_control for this behavior in newer Oracle versions, but found nothing. So it looks more like a bug to me. 12.1.0.1 has the same behavior as you noticed. Cost difference between 11.2.0.3.6 and 12.1.0.1: 19 vs. 49K

    Regards
    Stefan

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s