Applying two anti-join conditions separately speeds up a job

I was asked to create a temporary table on a 9.2.0.8 reporting database from the following sql:

select ot.TID
from sch_a.OT ot
where ot.TT_ID='C'
  and not exists (select 1 from sch_a.OT t
                  where ot.BID=t.BID and t.TT_ID='I')
   and not exists (select 1 from sch_b.BM bm
                  where ot.BID=bm.BID);

The execution plan looks like:

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4640609 Card=360807 Bytes=5772912)
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'OT' (Cost=671732 Card=360807 Bytes=5772912)
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'OT' (Cost=8 Card=1 Bytes=9)
   4    3       INDEX (RANGE SCAN) OF 'OT_IX3' (NON-UNIQUE) (Cost=4 Card=4)
   5    1     INDEX (UNIQUE SCAN) OF 'BM_PK' (UNIQUE) (Cost=3 Card=1 Bytes=7)

The first attempt to run this sql returned "snapshot too old" error after 21 hours.

Finally I took a two-step approach to accomplish the task. I am not sure it is the best way, however, task was accomplished in less than 5 hours.


1. create a temp table "tempa" by applying the first anti-join condition using outer-join





create table tempa nologging
as
  select ot.TID, ot.BID
    from sch_a.OT ot, sch_b.BM bm
   where ot.BID = bm.BID (+)
     and bm.rowid is null
     and ot.TT_ID = 'C'
   ;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1035475 Card=144322676 Bytes=4329680280)
   1    0   FILTER
   2    1     HASH JOIN (OUTER)
   3    2       TABLE ACCESS (FULL) OF 'OT' (Cost=671732 Card=144322676 Bytes=2309162816)
   4    2       INDEX (FAST FULL SCAN) OF 'BM_PK' (UNIQUE) (Cost=168790 Card=338111450 Bytes=4733560300)

2. Apply second anti-join condition using outer-join

select tmp.TID
 from  tempa tmp,
      ( select BID
        from  sch_a.OT t
       where t.TT_ID= 'I'
      ) a
 where tmp.BID = a.BID(+)
  and a.rowid is null;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=733333 Card=361891 Bytes=15199422)
   1    0   FILTER
   2    1     HASH JOIN (OUTER)
   3    2       TABLE ACCESS (FULL) OF 'TEMPA' (Cost=75 Card=226290 Bytes=5883540)
   4    2       TABLE ACCESS (FULL) OF 'OT' (Cost=671732 Card=144322676 Bytes=2309162816)

  ------ 

Look at the exection plans in the two-step approach, there are FTS and index fast full scan operations. One good thing about these operations is they appear in the v$session_longops view. The estimation of the exection time they need from this view is usually close to reality.

Self anti-join - failed to obtain optimal exectuion plan on a 9i database

I continued to study the query I described in the previous post on Jan 18. I realized that the first part of the query is essentially a self anti-join.

The column statistics of the table BM in the production is as follows:

 COLUMN_NAME       LOW_VAL        HIGH_VAL   NUM_DISTINCT  NUM_NULLS
 ------------- ------------- --------------  -----------   ----------
 BID               3             598219339    314914980          0
 BSCI              B             S                    2          0
 BSC               1             7                    7          0
 VP_BID            0             598218162     25905093          0

I thus constructed a baseline test case as following:

create table bm
as
select
   rownum    bid,
   trunc(dbms_random.value(1,500))          vp_bid,
   mod(rownum, 7)  + 1                  bsc,
   decode(mod(rownum,2),
          0, 'A',
          1, 'S')     bsci
from all_objects 
where rownum <= 6500;  
  
create index bm_fk5 on bm(vp_bid);     

begin        
dbms_stats.gather_table_stats( user,  'bm',  cascade => true); 
end; 
/  

set autotrace traceonly  
SELECT *   FROM bm  
WHERE bm.bsc = 3    
AND bm.bsci = 'S'    
AND NOT EXISTS (           
          SELECT  1             
            FROM bm bm1            
           WHERE bm.vp_bid = bm1.vp_bid              
             AND bm1.bsci = 'S'              
             AND bm1.bsc NOT IN (3, 4)); 
set autotrace off  

Here are the results of Autotrace

1. When executed the baseline test case in a 9.2.0.8 database:

Execution Plan
-------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=14 Card=1 Bytes=12)
1    0   FILTER
2    1     TABLE ACCESS (FULL) OF 'BM' (Cost=4 Card=1 Bytes=12)
3    1     TABLE ACCESS (BY INDEX ROWID) OF 'BM' (Cost=10 Card=1 Bytes=12)
4    3       INDEX (RANGE SCAN) OF 'BM_FK5' (NON-UNIQUE) (Cost=1 Card=13)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1508  consistent gets
          0  physical reads
          0  redo size
        278  bytes sent via SQL*Net to client
        234  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

2. When executed the baseline test case in a 10.2.0.1 database:

---------------------------------------------------------------------------
 Id   Operation           Name  Rows   Bytes  Cost (%CPU) Time  
---------------------------------------------------------------------------
   0  SELECT STATEMENT              1     21     14  (15) 00:00:01
*  1   HASH JOIN ANTI               1     21     14  (15) 00:00:01
*  2    TABLE ACCESS FULL BM      464   5568      7  (15) 00:00:01
*  3    TABLE ACCESS FULL BM     2388  21492      7  (15) 00:00:01
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("BM"."VP_BID"="BM1"."VP_BID")
   2 - filter("BM"."BSC"=3 AND "BM"."BSCI"='S')
   3 - filter("BM1"."BSCI"='S' AND "BM1"."BSC"<>3 AND "BM1"."BSC"<>4)


Statistics
----------------------------------------------------------
        166  recursive calls
          0  db block gets
         55  consistent gets
          0  physical reads
          0  redo size
        689  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          8  rows processed

Note: 10g picked up hash join anti acccess path and the consistent gets is only 55 vs 1508 in the case of 9i.

3. Drop the index BM_FK5, and run the baseline test case in the 9.2.0.8 database

   Execution Plan
   ----------------------------------------------------------
      0      SELECT STATEMENT Optimizer=CHOOSE (Cost=9 Card=1 Bytes=21)
      1    0   HASH JOIN (ANTI) (Cost=9 Card=1 Bytes=21)
      2    1     TABLE ACCESS (FULL) OF 'BM' (Cost=4 Card=464 Bytes=5568)
      3    1     TABLE ACCESS (FULL) OF 'BM' (Cost=4 Card=2388 Bytes=21492)


   Statistics
   ----------------------------------------------------------
           153  recursive calls
             0  db block gets
            54  consistent gets
             0  physical reads
             0  redo size
           389  bytes sent via SQL*Net to client
           234  bytes received via SQL*Net from client
             2  SQL*Net roundtrips to/from client
             3  sorts (memory)
             0  sorts (disk)
             8  rows processed

Note: without that index, 9i can choose right executin plan.

I then tested different ways in order to get 9.2.0.8 database to pick up the good execution plan when the index is present.

I tried:

1. NO_INDEX hint
2. collect histogram on the indexed column
3. rewrite the SQL to use NOT IN
4. NOT IN with HASH_AJ hint
5. HASH_AJ hint
6. Not analyzing the table, using dynamic sampling feaure

None of above succeeded. After playing with it the whole afternoon. I decided to move on and document it here for reference.

Anti-Join - A test case showing difference between NOT IN and NOT EXISTS on a 9i database

This test case is built based on Tom Kyte's book "Effective Oracle by Design" p477.

Summary of logical reads of anti-join queris when using deffierent structure

---------------------------------------------------------
Structure    NOT IN       NOT EXISTS      Outer join
---------------------------------------------------------
9i               160           20133          160
10g              155            155            155
----------------------------------------------------------

 The test case is as follows: 

 
rem script: anti_join_cbo.sql
rem    
rem    anti-join: used to return rows from a table that are not 
rem               present in some other row source
rem 

set echo on
drop table t1;
drop table t2;

create table t1 as select *
from all_objects where rownum <=10000;


create table t2 as select *
from all_objects where rownum <=9950;

create index t2_idx on t2(object_id);



begin
      dbms_stats.gather_table_stats(
            user,
            't1',
           cascade => true
      );
end;
/

begin
      dbms_stats.gather_table_stats(
            user,
            't2',
           cascade => true
      );
end;
/

alter session set tracefile_identifier = anti_join;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

-- 1. NOT IN
select count(*) from t1 cbo
where object_id not in (select object_id from t2);


-- 2. NOT EXISTS

select count(*) from t1 cbo
where not exists (select null from t2 where t2.object_id = cbo.object_id );


-- 3. OUTER JOIN

select count(*) from t1, t2 cbo
where  t1.object_id = cbo.object_id(+) 
 and  cbo.object_id is NULL;


alter session set events '10046 trace name context off'; 
exit;

set doc off
doc

------- 9.2.0.8   optimizer_mode=choose  ------

1. NOT IN

   select count(*) from t1 cbo
   where object_id not in (select object_id from t2)

   call     count cpu  elapsed  disk  query  current   rows
   ------- ------ ---- -------- ----- ------ --------  -----
   Parse        1 0.01     0.00     0      0        0      0
   Execute      1 0.00     0.00     0      0        0      0
   Fetch        2 0.04     0.03     0    160        0      1
   ------- ------ ---- -------- ----- ------ --------  -----
   total        4 0.05     0.03     0    160        0      1

   Misses in library cache during parse: 1
   Optimizer goal: CHOOSE
   Parsing user id: 178

   Rows     Row Source Operation
   -------  ---------------------------------------------------
  1  SORT AGGREGATE
 50   HASH JOIN ANTI
     10000    TABLE ACCESS FULL T1
      9950    INDEX FAST FULL SCAN T2_IDX (object id 443635)





2. NOT EXISTS

select count(*) from t1 cbo
where not exists (select null from t2 where t2.object_id = cbo.object_id )

call     count    cpu  elapsed   disk  query current   rows
------- ------  ----- -------- ------ ------ -------  -----
Parse        1   0.00     0.00      0      0       0      0
Execute      1   0.00     0.00      0      0       0      0
Fetch        2   0.17     0.16      0  20133       0      1
------- ------  ----- -------- ------ ------ -------  -----
total        4   0.17     0.16      0  20133       0      1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 178

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
     50   FILTER
  10000    TABLE ACCESS FULL T1
   9950    INDEX RANGE SCAN T2_IDX (object id 443635)


3. OUTER JOIN

select count(*) from t1, t2 cbo
where  t1.object_id = cbo.object_id(+)
 and  cbo.object_id is NULL

call     count    cpu   elapsed  disk  query  current   rows
------- ------  ----- --------- ----- ------ --------  -----
Parse        1   0.01      0.00     0      0        0      0
Execute      1   0.00      0.00     0      0        0      0
Fetch        2   0.04      0.04     0    160        0      1
------- ------  ----- --------- ----- ------ --------  -----
total        4   0.05      0.04     0    160        0      1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 178

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
     50   FILTER
  10000    HASH JOIN OUTER
  10000     TABLE ACCESS FULL T1
   9950     INDEX FAST FULL SCAN T2_IDX (object id 443635)

------- 10.2.0.1   optimizer_mode=all_rows  ------


1. NOT IN 
   select count(*) from t1 cbo
   where object_id not in (select object_id from t2)

   call     count    cpu  elapsed disk   query  current  rows
   ------- ------  ----- -------- ---- ------- --------  ----
   Parse        1   0.01     0.11    0       0        0     0
   Execute      1   0.00     0.03    0       0        0     0
   Fetch        2   0.01     0.02    0     155        0     1
   ------- ------  ----- -------- ---- ------- --------  ----
   total        4   0.03     0.17    0     155        0     1

   Misses in library cache during parse: 1
   Optimizer mode: ALL_ROWS
   Parsing user id: 62

   Rows     Row Source Operation
   -------  ---------------------------------------------------
  1  SORT AGGREGATE (cr=155 pr=0 pw=0 time=25632 us)
 50   HASH JOIN ANTI (cr=155 pr=0 pw=0 time=25386 us)
     10000    TABLE ACCESS FULL T1 (cr=129 pr=0 pw=0 time=30170 us)
      9950    INDEX FAST FULL SCAN T2_IDX (cr=26 pr=0 pw=0 time=29952 us)(object id 58211)

2. NOT EXISTS

    Same as "NOT IN"


3. Outer join

    Same as "NOT IN"

#

An experience of tunning a SQL with EXISTS and NOT EXISTS subqueries

Our team was requested to run a SQL on Saturday aternoon. The time window was from 4PM to midnight. Since from midnight a maintenance job was scheduled and the database won't be available, we promised to deliver the results by midnight. However, the DBA responsible for carring out the task had no idea how long this SQl would take. So he would do a dry run on Friday afternoon. I was called to help him.

The SQL has the following structure (actual table names, column name were modified of course):

SELECT *
  FROM a,
       bi,
       bm
 WHERE bm.bsc = 3
   AND bm.bsci = 'S'
   AND a.bid = bi.bid
   AND a.bid = bm.vp_bid
   AND
   NOT EXISTS (
          SELECT  1
            FROM bm bm1
           WHERE bm.vp_bid = bm1.vp_bid
             AND bm1.bsci = 'S'
             AND bm1.bsc NOT IN (3, 4));
union
SELECT  *
  FROM a,
       bi,
       bm
 WHERE bm.bsc = 3
   AND bm.bsci = 'S'
   AND a.bid = bi.bid
   AND a.bid = bm.vp_bid
   AND
      EXISTS (
          SELECT  1
            FROM bm bm1
           WHERE bm.vp_bid = bm1.vp_bid
             AND bm1.bsci = 'S'
             AND bm1.bsc NOT IN (3, 4));

The DBA initially tried to do a dry run using hint parallel 5 for all the three tables. He said using parallel hint he got smaller cost value from the explain plan. I told him if he used parallel 10, he would get even smaller cost value, but this did not mean the SQL would run faster. I told him just start the SQL without any hint and see how long it would take.

The number of rows of the table involved are as follows:

 TABLE_NAME          NUM_ROWS LAST_ANALYZED
 ----------------- ---------- -------------------
 BM                 309688610 2009-01-03 07:22:04
 A                   15837920 2009-01-03 10:54:31
 BI                  15840300 2009-01-03 11:08:33

Our databae is Oracle 9.2.0.8.

It turned out the SQL did not finish after 5.5 hours running. The execution plan looked like:

 
------------------------------------------------------------------------
|Id | Operation                      |  Name | Rows| Bytes |Temp |
                                                             Spc |Cost |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT               |       |     |       |     |  11M|
| 1 |  SORT UNIQUE                   |       | 110M|  9017M|  19G|  11M|
| 2 |   UNION-ALL                    |       |     |       |     |     |
| 3 |    FILTER                      |       |     |       |     |     |
| 4 |     HASH JOIN                  |       |1124K|    79M|  61M| 334K|
| 5 |      HASH JOIN                 |       |1124K|    48M|  38M| 305K|
| 6 |       TABLE ACCESS FULL        | BM    |1124K|    25M|     | 271K|
| 7 |       TABLE ACCESS FULL        | A     |  15M|   319M|     |24889|
| 8 |      TABLE ACCESS FULL         | BI    |  15M|   440M|     |17719|
| 9 |     TABLE ACCESS BY INDEX ROWID| BM    |   5 |    60 |     |   8 |
|10 |      INDEX RANGE SCAN          | BM_FK5|  13 |       |     |   4 |
|11 |    HASH JOIN                   |       | 108M|  8937M|1844M| 703K|
|12 |     HASH JOIN                  |       |  22M|  1587M| 772M| 359K|
|13 |      TABLE ACCESS FULL         | BM    |  22M|   514M|     |  71K|
|14 |      HASH JOIN                 |       |  15M|   759M| 501M|60525|
|15 |       TABLE ACCESS FULL        | A     |  15M|   319M|     |24889|
|16 |       TABLE ACCESS FULL        | BI    |  15M|   440M|     |17719|
|17 |     TABLE ACCESS FULL          | BM    | 115M|  1323M|     | 271K|
------------------------------------------------------------------------

We were worried about this SQL, our manager set up a call with developers at about 7PM. I learned from them that we can use inion all instead of union since there are no possible dulpicate rows from the two parts. Finally we agreed with runing the two parts of the SQL separatly and load the results to two separate temp table. And DBA team only need diliver these two temp talbe to the devlopers.

I took over the task. I started a new test and using the nested loop hint for the first part:

 

SELECT /*+ use_nl(bm, a) use_nl(a, bi) */
   *
  FROM a,
       bi,
       bm
 WHERE bm.bsc = 3
   AND bm.bsci = 'S'
   AND a.bid = bi.bid
   AND a.bid = bm.vp_bid
   AND
   NOT EXISTS (
          SELECT  1
            FROM bm bm1
           WHERE bm.vp_bid = bm1.vp_bid
             AND bm1.bsci = 'S'
             AND bm1.bsc NOT IN (3, 4));

The execution plan for this part is as follows:

------------------------------------------------------------------------
| Id | Operation                       |  Name | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |       |       |       |    14M|
|  1 |  FILTER                         |       |       |       |       |
|  2 |   TABLE ACCESS BY INDEX ROWID   | BI    |     1 |    29 |     3 |
|  3 |    NESTED LOOPS                 |       |  1106K|    78M|  5797K|
|  4 |     NESTED LOOPS                |       |  1106K|    47M|  2479K|
|  5 |      TABLE ACCESS FULL          | BM    |  1106K|    25M|   267K|
|  6 |      TABLE ACCESS BY INDEX ROWID| A     |     1 |    21 |     2 |
|  7 |       INDEX UNIQUE SCAN         | A_PK  |     1 |       |     1 |
|  8 |     INDEX RANGE SCAN            | BI_PK |     1 |       |     2 |
|  9 |   TABLE ACCESS BY INDEX ROWID   | BM    |     5 |    60 |     8 |
| 10 |    INDEX RANGE SCAN             | BM_FK5|    13 |       |     4 |
------------------------------------------------------------------------

I used parallel hint for the second part:

 
SELECT /*+ parallel (bm, 4) */  
  *
  FROM a,
       bi,
       bm
 WHERE bm.bsc = 3
   AND bm.bsci = 'S'
   AND a.bid = bi.bid
   AND a.bid = bm.vp_bid
   AND
      EXISTS (
          SELECT  1
            FROM bm bm1
           WHERE bm.vp_bid = bm1.vp_bid
             AND bm1.bsci = 'S'
             AND bm1.bsc NOT IN (3, 4));

And the execution plan looks like:

----------------------------------------------------------------------------
Id | Operation            | Name|Rows|Bytes|Temp|Cost | TQ  |IN-OUT|PQ 
                                           |Spc |     |     |      |Distrib 
----------------------------------------------------------------------------
 0 | SELECT STATEMENT     |     |    |     |    | 404K|     |      |        
 1 |  HASH JOIN           |     | 22M|1814M|154M| 404K|98,00| P->S |QC(RAND)
 2 |   TABLE ACCESS FULL  | BI  | 15M| 438M|    |17565|98,00| S->P | HASH   
 3 |   HASH JOIN          |     | 22M|1202M|124M| 378K|98,00| PCWP |        
 4 |    TABLE ACCESS FULL | A   | 15M| 317M|    |24624|98,00| S->P | HASH   
 5 |    HASH JOIN SEMI    |     | 22M| 759M|189M| 348K|98,00| PCWP |        
 6 |     TABLE ACCESS FULL| BM  | 22M| 506M|    |66931|98,00| P->P | HASH   
 7 |     TABLE ACCESS FULL| BM  |113M|1301M|    | 267K|98,00| S->P | HASH   
----------------------------------------------------------------------------

Good news was the first part finished in 3 hours, and the second part took 2 h 40 min. On Saturday afternoon, I did the real run and it took a little longer but finished in 4 hours.

At this point, I am not sure I chose the best way to accomplish this task. I just ensured that we deliverd as we promised. I don't know if it could be better if we just adopt the approach of using parallel hint 5 for all the three tables. It was too late for DBA to having sufficient time to find the best way to execute this SQL.

I will re-visit this SQL if I have time. Acutally I already invest some time to understand anti-join this evening - the first part of this SQL is acutally anti-join, but I have not seen Oracle choose anti-join access path in the current execution plan.