| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> puzzled by this sql's explain plan result
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:06 CDT
 |
 |