Re: puzzled by this sql's explain plan result

Hi Guang,

I don't know why, and I can't find good information from meta-link or from other dbas I have spoken too, but estimated statistics are not reliable in 8.1.6.x. We have run into similar performance issues, which is why I suggested that you compute full statistics, and we have had a faulty disk cause other issues - unexplained slowness, which is why I recommended bstat/estat.

Sometimes index hints will overcome bad statistics, but not always. We are upgrading to 8.1.7.1 in August, and the problem is supposed to be less of an issue in that version.

Good Luck,

Chris

{ Chris Weiss Chief Scientist PureCarbon }

"Guang Mei" <gmei_at_proteome.com> wrote in message news:QPJ37.65$M6.7068_at_news.shore.net...
> 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
> ---------- ----------- ------------- -----------------------
> AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
> ----------------------- -----------------
> 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:59 CDT