Forum Stats

  • 3,782,046 Users
  • 2,254,585 Discussions
  • 7,879,901 Comments

Discussions

Oracle choosing different index on same query

652811
652811 Member Posts: 124
edited Oct 17, 2008 1:10AM in General Database Discussions
Hi Gurus

My one sql query taking one index in testing enviornment and different index on production .
Pls find screen shot
Oracle 10g 10.2.0.3
Testing envioronment
SQL> SELECT dm_aq_total, ndm_aq_total, mrf_code, org_id,
2 euc_number, spt_code,
3 ldz_identifier, exz_identifier, cnf_shipper_ref, ndm_soq,
4 transco_meter_read, wholly_dm_ind, num_dataloggers, num_intrptbl_days,
5 cnf_reference_num
6 FROM ai_sp_history
7 WHERE spo_identifier = 1181336
8 AND end_date <= '24-APR-07'
9 AND end_date >= '01-OCT-06'
10 ORDER BY start_date DESC;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 1113860183

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

| Id | Operation | Name | Rows | Bytes | C
ost (%CPU)| Time | Pstart| Pstop |

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

| 0 | SELECT STATEMENT | | 1 | 114 |
3 (34)| 00:00:01 | | |

| 1 | SORT ORDER BY | | 1 | 114 |
3 (34)| 00:00:01 | | |

|* 2 | FILTER | | | |
| | | |

| 3 | PARTITION RANGE ITERATOR | | 1 | 114 |
2 (0)| 00:00:01 | KEY | KEY |

|* 4 | TABLE ACCESS BY LOCAL INDEX ROWID| AI_SP_HISTORY | 1 | 114 |
2 (0)| 00:00:01 | KEY | KEY |

|* 5 | INDEX RANGE SCAN | AIH002 | 1 | |
2 (0)| 00:00:01 | KEY | KEY |

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


Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter(TO_DATE('24-APR-07')>=TO_DATE('01-OCT-06'))
4 - filter("SPO_IDENTIFIER"=1181336)
5 - access("END_DATE">='01-OCT-06' AND "END_DATE"<='24-APR-07')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
960118 consistent gets
0 physical reads
0 redo size
1314 bytes sent via SQL*Net to client
477 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed

In production
SQL> SELECT dm_aq_total, ndm_aq_total, mrf_code, org_id,
2 euc_number, spt_code,
3 ldz_identifier, exz_identifier, cnf_shipper_ref, ndm_soq,
4 transco_meter_read, wholly_dm_ind, num_dataloggers, num_intrptbl_days,
5 cnf_reference_num
6 FROM ai_sp_history
7 WHERE spo_identifier = 1181336
8 AND end_date <= '24-APR-07'
9 AND end_date >= '01-OCT-06'
10 ORDER BY start_date DESC;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 779455578

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

| Id | Operation | Name | Rows | Bytes | Co
st (%CPU)| Time | Pstart| Pstop |

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

| 0 | SELECT STATEMENT | | 1 | 114 |
26 (0)| 00:00:01 | | |

|* 1 | FILTER | | | |
| | | |

|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| AI_SP_HISTORY | 1 | 114 |
26 (0)| 00:00:01 | ROWID | ROWID |

|* 3 | INDEX RANGE SCAN DESCENDING | AIH001 | 23 | |
3 (0)| 00:00:01 | | |

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


Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_DATE('24-APR-07')>=TO_DATE('01-OCT-06'))
2 - filter("END_DATE">='01-OCT-06' AND "END_DATE"<='24-APR-07')
3 - access("SPO_IDENTIFIER"=1181336)
filter("SPO_IDENTIFIER"=1181336)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1314 bytes sent via SQL*Net to client
477 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


How oracle chooses differnt index on same query on different servers?

thanks
Tagged:

Answers

  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    1) Is END_DATE a string? Or a date? If it is a date, your query should really comparing it to a date (i.e. you should put an explicit TO_DATE around the string).

    2) Are the object statistics in both systems up to date? Are the system statistics up to date? Are the initialization parameters the same? I'm wagering that the object statistics on the slower system are out of date. How, exactly, do you gather statistics?

    3) Can you use the \
     tag around your query plan to preserve white space and formatting?  If you can post the plan including the filter and access predicates you get from the DBMS_XPLAN package, that would be useful.
    
    Justin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
This discussion has been closed.