Thank you Tom,
The cardinality hint worked great.

Hi Tom !

Why explain plan does not works when case statement is used in the Select ? Consider:

  1* select case when dummy='X' then '1' else '2' end a from dual
SQL> /

A
================================
1

Elapsed: 00:00:00.97

Execution Plan
==========================================================
ERROR:
ORA-00938: not enough arguments for function


SP2-0612: Error generating AUTOTRACE EXPLAIN report


  1  select decode(dummy,'X','1','2') a
  2* from dual
SQL> /

A
================================
1

Elapsed: 00:00:00.28

Execution Plan
==========================================================
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=1)
   1    0   TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=1 Bytes=1)


Is there something wrong ??? 

I am using 8.1.7 on Windows 2000 Box and structure of my plan_table is:

SQL> desc plan_table
 Name                    Null?    Type
 ----------------------- -------- -------------
 STATEMENT_ID                     VARCHAR2(30)
 TIMESTAMP                        DATE
 REMARKS                          VARCHAR2(80)
 OPERATION                        VARCHAR2(30)
 OPTIONS                          VARCHAR2(30)
 OBJECT_NODE                      VARCHAR2(128)
 OBJECT_OWNER                     VARCHAR2(30)
 OBJECT_NAME                      VARCHAR2(30)
 OBJECT_INSTANCE                  NUMBER(38)
 OBJECT_TYPE                      VARCHAR2(30)
 OPTIMIZER                        VARCHAR2(255)
 SEARCH_COLUMNS                   NUMBER
 ID                               NUMBER(38)
 PARENT_ID                        NUMBER(38)
 POSITION                         NUMBER(38)
 COST                             NUMBER(38)
 CARDINALITY                      NUMBER(38)
 BYTES                            NUMBER(38)
 OTHER_TAG                        VARCHAR2(255)
 PARTITION_START                  VARCHAR2(255)
 PARTITION_STOP                   VARCHAR2(255)
 PARTITION_ID                     NUMBER(38)
 OTHER                            LONG
 DISTRIBUTION                     VARCHAR2(30) 

You are true.I have cursor_sharing=force for my database. When it set it to exact, it worked like a charm.

Tom! Can you tell us why the explain plan didn't worked with cursor_sharing=force ?

Tom,
I have a join with a gtt and a real table. In cases where there are many rows in the gtt, the cbo seems to be choosing a NL join where a hash join is clearly preferrable due to the consistent gets count.
Part of the cause, I think, is that the cost of a FTS on the gtt is always 17. Whether 2 rows or 20000 rows the cost is 17.
I did a 10053 trace on these sql (which I'll spare you the details) but dynamic sampling shows the number of blocks in the gtt is 1 for 2 rows and 611 for 20000.

How does the cbo calculate, if it does, the cost for a FTS on a gtt?
I noticed the first post on this thread shows the cost of accessing gt_item_copy was 11. I created that table on our system and got cost=17, just like the rest of my gtt.
That makes me think the cost is derived from init.ora parameters and not the data.

Thanks.

Example:
4253(4255)jixe-> @create_ama_t1
4253(4255)jixe-> create table ama_t1(
2 f1 number not null
3 ,f2 varchar2(50) not null
4 ,constraint ama_t1_pk primary key (f1)
5 );

Table created.

Elapsed: 00:00:00.05
4253(4255)jixe->
4253(4255)jixe-> insert into ama_t1 (select rownum, lpad(rownum, 50, 'A')
2 from dba_objects
3 where rownum <=2000
4 )
5 ;

2000 rows created.

Elapsed: 00:00:00.09
4253(4255)jixe-> analyze table ama_t1 compute statistics
2 for table
3 for all indexes
4 for all columns
5 ;

Table analyzed.

Elapsed: 00:00:00.13
4253(4255)jixe-> @create_gtt_test
4253(4255)jixe-> drop table gtt_test;

Table dropped.

Elapsed: 00:00:00.05
4253(4255)jixe-> create global temporary table gtt_test
2 (f1 number not null
3 ,f2 varchar2(200) not null
4 ,f3 date not null
5 ,constraint gtt_test_pk primary key (f1,f2)
6 )
7 on commit preserve rows
8 ;

Table created.

4253(4255)jixe-> @load_gtt
4253(4255)jixe-> variable nStop number
4253(4255)jixe-> col f2 format a30
4253(4255)jixe-> exec :nStop := &nStop
Enter value for nstop: 2

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
4253(4255)jixe->
4253(4255)jixe-> begin
2 for i in 1 .. :nStop
3 loop
4 begin
5 insert into gtt_test
6 values (i, lpad(i, 200, 'A'), sysdate);
7 exception
8 when dup_val_on_index then
9 null;
10 when others then
11 raise;
12 end;
13 end loop;
14 end;
15 /

PL/SQL procedure successfully completed.

4253(4255)jixe-> get dyns
1 select /*+ dynamic_sampling (g 2) */
2 g.*
3 , length(g.f2) l_f2
4 from gtt_test g
5 , ama_t1 a
6* where g.f1 = a.f1
4253(4255)jixe-> @trace_only
4253(4255)jixe-> set autotrace traceonly
4253(4255)jixe-> /

Elapsed: 00:00:00.83

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=19 Card=2 Bytes=254)
1 0 NESTED LOOPS (Cost=19 Card=2 Bytes=254)
2 1 TABLE ACCESS (FULL) OF 'GTT_TEST' (Cost=17 Card=2 Bytes=248)
3 1 INDEX (UNIQUE SCAN) OF 'AMA_T1_PK' (UNIQUE)




Statistics
----------------------------------------------------------
75 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
1129 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
2 rows processed

4253(4255)jixe-> ed
1 select --+ dynamic_sampling (g 2) */
2 /*+ dynamic_sampling (g 2) use_hash(g) use_hash(a) */
3 g.*
4 , length(g.f2) l_f2
5 from gtt_test g
6 , ama_t1 a
7* where g.f1 = a.f1
4253(4255)jixe-> /

Elapsed: 00:00:00.82

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=19 Card=2 Bytes=254)
1 0 NESTED LOOPS (Cost=19 Card=2 Bytes=254)
2 1 TABLE ACCESS (FULL) OF 'GTT_TEST' (Cost=17 Card=2 Bytes=248)
3 1 INDEX (UNIQUE SCAN) OF 'AMA_T1_PK' (UNIQUE)




Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1129 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
4253(4255)jixe-> @load_gtt
4253(4255)jixe-> variable nStop number
4253(4255)jixe-> col f2 format a30
4253(4255)jixe-> exec :nStop := &nStop
Enter value for nstop: 20000
...

1 select /*+ dynamic_sampling (g 2) */
2 --/*+ dynamic_sampling (g 2) use_hash(g) use_hash(a) */
3 g.*
4 , length(g.f2) l_f2
5 from gtt_test g
6 , ama_t1 a
7* where g.f1 = a.f1
4253(4255)jixe-> /

2000 rows selected.

Elapsed: 00:00:01.03

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=19 Card=2000 Bytes=254000)
1 0 NESTED LOOPS (Cost=19 Card=2000 Bytes=254000)
2 1 TABLE ACCESS (FULL) OF 'GTT_TEST' (Cost=17 Card=22287 Bytes=2763588)
3 1 INDEX (UNIQUE SCAN) OF 'AMA_T1_PK' (UNIQUE)




Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
20786 consistent gets
0 physical reads
0 redo size
424082 bytes sent via SQL*Net to client
860 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2000 rows processed

1 select --/*+ dynamic_sampling (g 2) */
2 /*+ dynamic_sampling (g 2) use_hash(g) use_hash(a) */
3 g.*
4 , length(g.f2) l_f2
5 from gtt_test g
6 , ama_t1 a
7* where g.f1 = a.f1
4253(4255)jixe-> /

2000 rows selected.

Elapsed: 00:00:00.94

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=21 Card=2000 Bytes=254000)
1 0 HASH JOIN (Cost=21 Card=2000 Bytes=254000)
2 1 INDEX (FULL SCAN) OF 'AMA_T1_PK' (UNIQUE) (Cost=5 Card=2000 Bytes=6000)
3 1 TABLE ACCESS (FULL) OF 'GTT_TEST' (Cost=17 Card=22287 Bytes=2763588)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
639 consistent gets
0 physical reads
0 redo size
424082 bytes sent via SQL*Net to client
860 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2000 rows processed

Sorry for the confusion with that SQL. I merely meant to comment out the previous hint.

I see your point that the gtt FTS is not the direct cause in this case. But I still have questions.

1) Why is the index (unique) access cost seemingly zero when the access to a table can be satisfied with only an index?
2) Though it may not have a major impact in this particular case, a FTS cost for a gtt should change with the dynamic sampling, but it doesn't seem too. I can see cases where that can have an impact with the NL and HJ costs.

The second question I'll leave as a stand alone question about gtts. The first I'll illustrate by running a different test with the previous tables (with proper hints :).
gtt : 20,000 rows
physical: 2000 rows

Case 1: <select only from gtt>
1 select /*+ dynamic_sampling (g 2) */
2 g.*
3 , length(g.f2) l_f2
4 from gtt_test g
5 , ama_t1 a
6* where g.f1 = a.f1
21437(21439)jixe-> /

2000 rows selected.

Elapsed: 00:00:01.06

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=17 Card=2000 Bytes=254000)
1 0 NESTED LOOPS (Cost=17 Card=2000 Bytes=254000)
2 1 TABLE ACCESS (FULL) OF 'GTT_TEST' (Cost=17 Card=21569 Bytes=2674556)
3 1 INDEX (UNIQUE SCAN) OF 'AMA_T1_PK' (UNIQUE)

Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
20783 consistent gets
5 physical reads
0 redo size
424058 bytes sent via SQL*Net to client
860 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2000 rows processed

<10053 output, abridged version>
NL Join
Outer table: cost: 17 cdn: 21569 rcz: 124 resp: 17
Inner table: AMA_T1
Access path: tsc Resc: 5
Join: Resc: 107862 Resp: 107862
Access path: index (unique)
Index: AMA_T1_PK
TABLE: AMA_T1
RSC_CPU: 0 RSC_IO: 0
IX_SEL: 5.0000e-04 TB_SEL: 5.0000e-04
Join: resc: 17 resp: 17


Here is where my question 1) is from. A NL cost is:
cost= outer.tsc + (outer.cardinality * inner.tsc)
Why is unique access cost using AMA_T1_PK (inner.tsc) effectively zero? There is seemingly no cost associated with accessing the index.
By choosing the NL path, the consistent gets becomes 20783. Shouldn't I try to reduce that count for scalability?

case 2: <select from both gtt and physical table>
1 select /*+ dynamic_sampling (g 2) */
2 g.*
3 , a.*
4 , length(g.f2) l_f2
5 from gtt_test g
6 , ama_t1 a
7* where g.f1 = a.f1
21437(21439)jixe-> /

2000 rows selected.

Elapsed: 00:00:00.95

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=30 Card=2000 Bytes=354000)
1 0 HASH JOIN (Cost=30 Card=2000 Bytes=354000)
2 1 TABLE ACCESS (FULL) OF 'AMA_T1' (Cost=5 Card=2000 Bytes=106000)
3 1 TABLE ACCESS (FULL) OF 'GTT_TEST' (Cost=17 Card=21569 Bytes=2674556)

Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
724 consistent gets
21 physical reads
0 redo size
534055 bytes sent via SQL*Net to client
860 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2000 rows processed

<10053 output, abridged version>

Outer table: cost: 17 cdn: 21569 rcz: 124 resp: 17
Access path: index (unique)
Index: AMA_T1_PK
TABLE: AMA_T1
RSC_CPU: 0 RSC_IO: 1
IX_SEL: 5.0000e-04 TB_SEL: 5.0000e-04
Join: resc: 21586 resp: 21586

Now that we must access the table to satisfy the query, the NL cost using AMA_T1_PK is what I would expect:


cost = 17 + 21569*1 = 21586
Additionally, the consistent gets is 724.

As I'm writing this, I guess my question can be reworded as: if a query can be satisfied with accessing only an index, does the CBO truly see that as a free cost and will tend toward NL regardless of the cardinality of the outer table?
Is the amount of work (consistent gets) never accounted for?
Or am I missing something about what the CBO should be doing?

Thanks for your response and time.
Andrew

In 10g, you'll see the dynamic sampling has changed much and that the costing of a gtt is different as well:

ops$tkyte@ORA10G> drop table ama_t1;
 
Table dropped.
 
ops$tkyte@ORA10G> create table ama_t1(
  2       f1     number          not null
  3      ,f2     varchar2(50)    not null
  4      ,constraint ama_t1_pk primary key (f1)
  5  );
 
Table created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> insert into ama_t1 (select rownum, lpad(rownum, 50, 'A')
  2                        from dba_objects
  3                       where rownum <=2000
  4                     )
  5  ;
 
2000 rows created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> analyze table ama_t1 compute statistics
  2  for table
  3  for all indexes
  4  for all columns
  5  ;
 
Table analyzed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> drop table gtt_test;
 
Table dropped.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create global temporary table gtt_test
  2      (f1     number          not null
  3      ,f2     varchar2(200)   not null
  4      ,f3     date            not null
  5      ,constraint gtt_test_pk primary key (f1,f2)
  6  )
  7  on commit preserve rows
  8  ;
 
Table created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> insert into gtt_test select rownum, rpad('a',200,'a'), sysdate from all_objects where rownum <= 2;
 
2 rows created.
 
ops$tkyte@ORA10G> commit;
 
Commit complete.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set autotrace traceonly
ops$tkyte@ORA10G> select /*+ dynamic_sampling (g 2) */
  2         g.*
  3       , length(g.f2) l_f2
  4    from gtt_test g
  5       , ama_t1   a
  6   where g.f1 = a.f1
  7  /
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=2 Bytes=254)
   1    0   NESTED LOOPS (Cost=2 Card=2 Bytes=254)
   2    1     TABLE ACCESS (FULL) OF 'GTT_TEST' (TABLE (TEMP)) (Cost=2 Card=2 Bytes=248)
   3    1     INDEX (UNIQUE SCAN) OF 'AMA_T1_PK' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=3)
 
 
 
 
Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        993  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select /*+ dynamic_sampling (g 2) use_hash(g a) */
  2         g.*
  3       , length(g.f2) l_f2
  4    from gtt_test g
  5       , ama_t1   a
  6   where g.f1 = a.f1
  7  /
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=2 Bytes=254)
   1    0   HASH JOIN (Cost=6 Card=2 Bytes=254)
   2    1     TABLE ACCESS (FULL) OF 'GTT_TEST' (TABLE (TEMP)) (Cost=2 Card=2 Bytes=248)
   3    1     INDEX (FAST FULL SCAN) OF 'AMA_T1_PK' (INDEX (UNIQUE)) (Cost=3 Card=2000 Bytes=6000)
 
 
 
 
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        993  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed
 
ops$tkyte@ORA10G> set autotrace off
ops$tkyte@ORA10G> insert into gtt_test select rownum+2, rpad('a',200,'a'), sysdate from all_objects where rownum <= 20000;
 
20000 rows created.
 
ops$tkyte@ORA10G> commit;
 
Commit complete.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set autotrace traceonly
ops$tkyte@ORA10G> select /*+ dynamic_sampling (g 2) */
  2         g.*
  3       , length(g.f2) l_f2
  4    from gtt_test g
  5       , ama_t1   a
  6   where g.f1 = a.f1
  7  /
 
2000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=172 Card=2000 Bytes=254000)
   1    0   HASH JOIN (Cost=172 Card=2000 Bytes=254000)
   2    1     INDEX (FAST FULL SCAN) OF 'AMA_T1_PK' (INDEX (UNIQUE)) (Cost=3 Card=2000 Bytes=6000)
   3    1     TABLE ACCESS (FULL) OF 'GTT_TEST' (TABLE (TEMP)) (Cost=168 Card=18883 Bytes=2341492)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      21492  consistent gets
        368  physical reads
          0  redo size
     436842  bytes sent via SQL*Net to client
       1971  bytes received via SQL*Net from client
        135  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2000  rows processed
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select /*+ dynamic_sampling (g 2) use_hash(g a) */
  2         g.*
  3       , length(g.f2) l_f2
  4    from gtt_test g
  5       , ama_t1   a
  6   where g.f1 = a.f1
  7  /
 
2000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=172 Card=2000 Bytes=254000)
   1    0   HASH JOIN (Cost=172 Card=2000 Bytes=254000)
   2    1     INDEX (FAST FULL SCAN) OF 'AMA_T1_PK' (INDEX (UNIQUE)) (Cost=3 Card=2000 Bytes=6000)
   3    1     TABLE ACCESS (FULL) OF 'GTT_TEST' (TABLE (TEMP)) (Cost=168 Card=18883 Bytes=2341492)
 
 
 
 
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
        639  consistent gets
        582  physical reads
          0  redo size
     436842  bytes sent via SQL*Net to client
       1971  bytes received via SQL*Net from client
        135  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2000  rows processed
 
ops$tkyte@ORA10G> set autotrace off
 

Thanks Tom.
One last follow-up question.

Why are the consistent gets in your last two queries different (21492 vs 639) even though the plans are identical? The second also has 5 recursive calls while the first has 0. Is the work just transferred to the recursive calls?

Thanks again.

Hi Tom,

But how about MACH_PROD? It uses TABLE ACCESS FULL for BIG table MACH_PROD in Case I. Why is it different for 2 cases? Will this affect the time?

Case I
| 3 | TABLE ACCESS FULL | MACH_PROD | 3254K| 114M| 3403 |

Case II
| 3 | TABLE ACCESS BY INDEX ROWID| MACH_PROD | 1 | 37 | 2 |
|* 4 | INDEX UNIQUE SCAN | PK_MACH_PROD | 1 | | 1 |


Eddy Mak