Re: puzzled by this sql's explain plan result

After I did

 analyze TABLE BLASTRESULTS compute statistics;

It fixed the problem.

Does this means I can not rely on "analyze TABLE BLASTRESULTS estimate statistics;"? Why it's off?

SQL> select BLEVEL ,
  2 LEAF_BLOCKS,
  3 DISTINCT_KEYS,
  4 AVG_LEAF_BLOCKS_PER_KEY,
  5 AVG_DATA_BLOCKS_PER_KEY,
  6 CLUSTERING_FACTOR
  7 from dba_indexes
  8 where owner='MT'
  9 and index_name ='BLASTRESULTS_SSPID_INDEX';

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY

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

----------------------- -----------------
         3       73121            26                    2812
                  15512            403323

SQL> explain plan for
  2 select id from blastresults
  3 where subjspid = 24;

Explained.

SQL> select
to_number(substr(id,1,2))id,substr(parent_id,1,2)parent_id,substr(operation, 1,18) operat
ion, substr(options,1,8) options, substr(object_name,1,16)object_name,   2 substr(position,1,5) position, substr(cost,1,8)cost from plan_table order by id, parent_id;

        ID PA OPERATION OPTIONS OBJECT_NAME POSIT COST

---------- -- ------------------ -------- ---------------- ----- --------
         0    SELECT STATEMENT                             18329 18329
         1 0  TABLE ACCESS       BY INDEX BLASTRESULTS     1     18329
         2 1  INDEX              RANGE SC BLASTRESULTS_SSP 1     2816

"Chris Weiss" <weisschr_at_tcimet.net> wrote in message news:9inh34$232h$1_at_msunews.cl.msu.edu...
> On your production server try computing rather than estimating the
 indexes,
> and do this for the entire schema using DBMS_UTILITY.ANALYZE_SCHEMA.
>
> If this fails, you probably have a hardware related performance issue in
> your database, which could include a faulty disk or controller. Use
> bstat/estat to collect statistics and looks for performance issues related
> to I/O.
>
> Good Luck,
> Chris
>
>
>
>
> "Guang Mei" <gmei_at_proteome.com> wrote in message
> news:9lE37.18$M6.2815_at_news.shore.net...
> > Hi:
> >
> > I have something that I am trying to understand. We have two oracle
> > instances (production and development server). Every night we have a
 cron
> > job to "exp" production schema and then "imp" it to dev schema. So the
 data
> > set are identical (I double checked). But I found that a simplified sql
> > would generate two different explain plan, one use index and another use
> > full table scan. I want to know why.
> >
> > Here is some info about two servers:
> >
> > Prod: oracle 8.0.5 on Sun
> >
> > db_block_size : 2048
> >
> > DB_FILE_MULTIBLOCK_READ_COUNT : 16
> >
> > optimizer_mode: CHOOSE
> >
> > each table is analyzed nightly: (analyze TABLE xxxx estimate
 statistics;)
> >
> > when exp: STATISTICS = NONE, compress=y
> >
> > I rebuilt all indexes about two weeks ago on Porduction server.
> >
> >
> >
> > Dev: oracle 8.1.6 on Sun
> >
> > db_block_size : 4096
> >
> > DB_FILE_MULTIBLOCK_READ_COUNT : 8
> >
> > optimizer_mode: CHOOSE
> >
> > after imp: each table is analyzed: (analyze TABLE xxxx estimate
 statistics;)
> >
> >
> >
> >
> > All objects in schema are valid.
> >
> > I found that dba_indexes shows a very different results (see below). Has
 it
> > anything to do with how explain plan choose the best execution plan?
> >
> > How could I make any changes so that this sql can use "index scan" on
> > production server?
> >
> > TIA.
> >
> > Guang
> >
> > -------------------
> >
> > On production server:
> >
> > SQL> delete from plan_table;
> >
> > 2 rows deleted.
> >
> > SQL> explain plan for
> >
> > 2 select id from blastresults
> >
> > 3 where queryspid=24 ;
> >
> > Explained.
> >
> > SQL> select
> >
>

 to_number(substr(id,1,2))id,substr(parent_id,1,2)parent_id,substr(operation,
> > 1,18) operat
> >
> > ion, substr(options,1,8) options, substr(object_name,1,16)object_name,
> >
> > 2 substr(position,1,5) position, substr(cost,1,8)cost from plan_table
 order
> > by id, parent_id;
> >
> > ID PA OPERATION OPTIONS OBJECT_NAME POSIT COST
> >
>
> ---------- -- ------------------ -------- ---------------- ----- --------
> >
> > 0 SELECT STATEMENT 36088 36088
> >
> > 1 0 TABLE ACCESS FULL BLASTRESULTS 1 36088
> >
> >
> >
> > SQL> delete from plan_table;
> >
> > 2 rows deleted.
> >
> > SQL> explain plan for
> >
> > 2 select /* + index (BLASTRESULTS, BLASTRESULTS_SSPID_INDEX) */ id from
> > BLASTRESULTS
> >
> > 3 where subjspid = 24;
> >
> > Explained.
> >
> > SQL> select
> >
>

 to_number(substr(id,1,2))id,substr(parent_id,1,2)parent_id,substr(operation,
> > 1,18) operat
> >
> > ion, substr(options,1,8) options, substr(object_name,1,16)object_name,
> >
> > 2 substr(position,1,5) position, substr(cost,1,8)cost from plan_table
 order
> > by id, parent_id;
> >
> > ID PA OPERATION OPTIONS OBJECT_NAME POSIT COST
> >
>
> ---------- -- ------------------ -------- ---------------- ----- --------
> >
> > 0 SELECT STATEMENT 36088 36088
> >
> > 1 0 TABLE ACCESS FULL BLASTRESULTS 1 36088
> >
> >
> >
> >
> >
> > SQL> select owner, substr(segment_name,1,30) NAME,
> >
> > 2 substr(segment_type,1,5) TYPE,
> >
> > 3 extents,
> >
> > 4 bytes,
> >
> > 5 initial_extent INIT,
> >
> > 6 next_extent NEXT,
> >
> > 7 pct_increase PCT
> >
> > 8 from dba_segments
> >
> > 9 where segment_type ='TABLE'
> >
> > 10 and owner='MT'
> >
> > 11 and segment_name ='BLASTRESULTS';
> >
> > OWNER NAME TYPE EXTENTS
> >
>
> ------------------------------ ------------------------------ ----- ------
> --
> > --
> >
> > BYTES INIT NEXT PCT
> >
> > ---------- ---------- ---------- ----------
> >
> > MT BLASTRESULTS TABLE 31
> >
> > 775188480 25001984 25006080 0
> >
> >
> >
> > SQL> select BLEVEL ,
> >
> > 2 LEAF_BLOCKS,
> >
> > 3 DISTINCT_KEYS,
> >
> > 4 AVG_LEAF_BLOCKS_PER_KEY,
> >
> > 5 AVG_DATA_BLOCKS_PER_KEY,
> >
> > 6 CLUSTERING_FACTOR
> >
> > 7 from dba_indexes
> >
> > 8 where owner='MT'
> >
> > 9 and index_name ='BLASTRESULTS_SSPID_INDEX';
> >
> > BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY
> >
> > ---------- ----------- ------------- -----------------------
> >
> > AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
> >
> > ----------------------- -----------------
> >
> > 3 73061 414 176
> >
> > 1 715997
> >
> > -------------
> >
> > On development server:
> >
> > SQL> delete from plan_table;
> >
> > 3 rows deleted.
> >
> > SQL> explain plan for
> >
> > 2 select id from blastresults
> >
> > 3 where subjspid = 24;
> >
> > Explained.
> >
> > SQL> select
> >
>

 to_number(substr(id,1,2))id,substr(parent_id,1,2)parent_id,substr(operation,
> > 1,18) operat
> >
> > ion, substr(options,1,8) options, substr(object_name,1,16)object_name,
> >
> > 2 substr(position,1,5) position, substr(cost,1,8)cost from plan_table
 order
> > by id, parent_id;
> >
> > ID PA OPERATION OPTIONS OBJECT_NAME POSIT COST
> >
>
> ---------- -- ------------------ -------- ---------------- ----- --------
> >
> > 0 SELECT STATEMENT 13174 13174
> >
> > 1 0 TABLE ACCESS BY INDEX BLASTRESULTS 1 13174
> >
> > 2 1 INDEX RANGE SC BLASTRESULTS_SSP 1 1782
> >
> >
> >
> >
> >
> > SQL> select owner, substr(segment_name,1,30) NAME,
> >
> > 2 substr(segment_type,1,5) TYPE,
> >
> > 3 extents,
> >
> > 4 bytes,
> >
> > 5 initial_extent INIT,
> >
> > 6 next_extent NEXT,
> >
> > 7 pct_increase PCT
> >
> > 8 from dba_segments
> >
> > 9 where segment_type ='TABLE'
> >
> > 10 and owner='MT'
> >
> > 11 and segment_name ='BLASTRESULTS';
> >
> > OWNER NAME TYPE EXTENTS
> >
>
> ------------------------------ ------------------------------ ----- ------
> --
> > --
> >
> > BYTES INIT NEXT PCT
> >
> > ---------- ---------- ---------- ----------
> >
> > MT BLASTRESULTS TABLE 1
> >
> > 775188480 775188480 25006080 0
> >
> >
> >
> > SQL> select BLEVEL ,
> >
> > 2 LEAF_BLOCKS,
> >
> > 3 DISTINCT_KEYS,
> >
> > 4 AVG_LEAF_BLOCKS_PER_KEY,
> >
> > 5 AVG_DATA_BLOCKS_PER_KEY,
> >
> > 6 CLUSTERING_FACTOR
> >
> > 7 from dba_indexes
> >
> > 8 where owner='MT'
> >
> > 9 and index_name ='BLASTRESULTS_SSPID_INDEX';
> >
> > BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY
> >
> > ---------- ----------- ------------- -----------------------
> >
> > AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
> >
> > ----------------------- -----------------
> >
> > 2 33818 141 239
> >
> > 1 216435
> >
> >
> >
>
>
Received on Sat Jul 21 2001 - 16:36:40 CDT