Re: CBO cost and avg_row

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Thu, 10 Jul 2003 22:36:04 +0100
Message-ID: <3f0ddbbb$0$15035$cc9e4d1f@news.dial.pipex.com>

"Quarkman" <quarkman_at_myrealbox.com> wrote in message news:oprr32f4jor9lm4d_at_haydn...
> Just a wild stab in the dark here... but if BLOCKS=400000 and
> avg_row_len=50 and num_rows=10, you can be pretty certain that most of
> those blocks under the high water mark are full of nothing but fresh air.
> Packed optimally, the table should only consume 500 bytes, which should
> comfortably fit into one block. The statistics are therefore screaming at
> you, "Inflated High Water Mark Syndrome!!" (ie, huge inserts, followed by
> massive deletes).
>
> If you asked me then to do a select * from table, I would ordinarily
expect
> to do a full table scan, and never mind visiting the index. But with
> statistics like those, I suspect I'd rather access the table via the
index.
> Indexes give you rowid access to the table data, and you don't have to
> wander through 399999 empty blocks to retrieve all 10 rows.
>
> So, yes: knowing your table is fluffier than a very fluffy soufflé is
> actually important to the optimizer, and avg_row_len is needed to tell it
> that.

Except it don't necessarily work that way

SQL*Plus: Release 9.0.1.0.1 - Production on Thu Jul 10 22:13:18 2003

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> create table tester(id number,padder char(42));

Table created.

SQL> begin

  2     for i in 1..10 loop
  3             for j in 1..150000 loop
  4                     insert into tester values(j,to_char(j));
  5             end loop;
  6     commit;
  7     delete tester where id < 2000000;
  8     insert into tester values (10000000*i,to_char(i));
  9     end loop;

10 commit;
11* end;
SQL> / PL/SQL procedure successfully completed.

SQL> create index idx_tester on tester(id);

Index created.

SQL> analyze table tester compute statistics;

Table analyzed.

SQL> select blocks,num_rows,avg_row_len from dba_tables where table_name='TESTER
';

BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------

1182 10 49

SQL> alter table tester modify id not null;

Table altered.

SQL> analyze table tester compute statistics;

Table analyzed.

SQL> select blocks,num_rows,avg_row_len from dba_tables where table_name='TESTER
';

BLOCKS NUM_ROWS AVG_ROW_LEN
---------- ---------- -----------

1182 10 49

SQL> set autot on explain stat
SQL> ;
1* select /*+ index(tester idx_tester) */ * from tester SQL> / ID PADDER

---------- ------------------------------------------

  10000000 1
  20000000 2
  30000000 3
  40000000 4
  50000000 5
  60000000 6
  70000000 7
  80000000 8
  90000000 9
100000000 10

10 rows selected.

Execution Plan

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

Bytes=440)

   2    1     INDEX (FULL SCAN) OF 'IDX_TESTER' (NON-UNIQUE) (Cost=1 C
          ard=10)

Statistics

          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1252  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL> select * from tester;

ID PADDER

---------- ------------------------------------------

  20000000 2
  40000000 4
  60000000 6
  80000000 8
100000000 10
  90000000 9
  50000000 5
  30000000 3
  70000000 7
  10000000 1

10 rows selected.

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=180 Card=10 Bytes=44
          0)

   1    0   TABLE ACCESS (FULL) OF 'TESTER' (Cost=180 Card=10 Bytes=44
          0)

Statistics

          0  recursive calls
         10  db block gets
       1183  consistent gets
          0  physical reads
          0  redo size
       1252  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

So without the hint but with the stats avg_row_len and num_rows correctly populated Oracle still chooses the wrong plan.

Jonathan's suggestion does make sense though. In general row size might be relevant for costs of various joins. Not just hash but sort-merge etc might be affected as well.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************

Received on Thu Jul 10 2003 - 16:36:04 CDT

Re: CBO cost and avg_row_len