Re: puzzled by this sql's explain plan result

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:37:27 CDT