Re: Performance problem - help appreciated.

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Thu, 01 Apr 2004 14:40:53 GMT
Message-ID: <VRVac.1893$KK3.1333@nwrddc02.gnilink.net>

"VC" <boston103_at_hotmail.com> wrote in message news:eRTac.157658$po.943948_at_attbi_s52... --snip--
>
> Thank you for the example. It's not quite what I expected since as you
> correctly noticed the order in this case is immaterial and the optimizer can
> as well use the tables in the order (reverse) they are specified. What I am
> interested in would be the case where the order you specify (without the
> hint, naturally) would matter and the optimizer would heed it.
>
> Thanks.
>
> VC
> >
> >

.. and like I said .. provided your stats are upto date .. it is extremely difficult to get. I too feel its such a rare case that its pointless to worry about the table order.

So hopefully these examples are showing the rarity of it .... and in no way I'm suggesting one should worry about the table order in CBO.

But here is one example which might be fixable by histogram.

SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade => true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user, 'T2', cascade => true);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly stat exp
SQL> select * from t1, t2 where t1.c1 = t2.c1 and t1.c2 = 'A' and t2.c2 = 'Y';

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=20)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=10)

   2    1     NESTED LOOPS (Cost=4 Card=1 Bytes=20)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=10)
   4    3         INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=1 Card=1)
   5    2       INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1 Card=1)

Statistics

---

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

SQL> select * from t2, t1 where t1.c1 = t2.c1 and t1.c2 = 'A' and t2.c2 = 'Y';

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=20)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=10)

   2    1     NESTED LOOPS (Cost=4 Card=1 Bytes=20)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=10)
   4    3         INDEX (RANGE SCAN) OF 'T2_IDX' (NON-UNIQUE) (Cost=1 Card=1)
   5    2       INDEX (RANGE SCAN) OF 'T1_IDX' (NON-UNIQUE) (Cost=1 Card=1)

Statistics

---

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

SQL> set autotrace off
SQL> select c2, count(*) from t1 group by c2 having count(*) > 1;

C2                                          COUNT(*)
----------------------------------------- ----------
A                                                  5
B                                                 95

SQL> select c2, count(*) from t2 group by c2 having count(*) > 1;

C2                                          COUNT(*)
----------------------------------------- ----------
X                                                 99

SQL> select count(*) from t1;

  COUNT(*)

---

     14099

SQL> select count(*) from t2;

  COUNT(*)

---

     14099

Anurag Received on Thu Apr 01 2004 - 08:40:53 CST