On Jul 7, 9:25 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Jul 7, 5:55 am, KL <klw..._at_swire.com.au> wrote:
>
> > Are there anyway to tell/check if the database performance issue
> > relates to an inefficient database index or not ?? We are running 9i.
> > Many thanks. KL
>
> You should be able to use DBMS XPlan with a particular SQL statement
> to determine if an index is being used, and the actual time required
> to execute the SQL statement. With a hint, you can tell Oracle to not
> use that index, or to specifically use a different index. You would
> then use the same DBMS XPlan approach to determine if the execution
> performance is better or worse for the particular SQL statement.
> Let's assume that the table name is T1 and the index name in question
> is T1_I1, and there is a second index on the same table named T1_I2.
> Take a look at the following SQL statement:
> SELECT
> MY_DATE,
> MY_NUMBER,
> TEMP_WATER_FREEZE
> FROM
> T1
> WHERE
> MY_DATE BETWEEN TRUNC(SYSDATE-100) AND (SYSDATE-1)
> AND TEMP_WATER_FREEZE=0;
>
> ---------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> Time | Buffers | Reads |
> ---------------------------------------------------------------------------------------------------------
> |* 1 | FILTER | | 1 | | 100 |00:00:00.02
> | 459 | 185 |
> |* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 3 | 100
> |00:00:00.02 | 459 | 185 |
> |* 3 | INDEX RANGE SCAN | T1_I1 | 1 | 120K| 100K|
> 00:00:00.20 | 184 | 183 |
> ---------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> 1 - filter(TRUNC(SYSDATE@!-100)<=SYSDATE@!-1)
> 2 - filter(("MY_DATE"<=SYSDATE@!-1 AND
> "MY_DATE">=TRUNC(SYSDATE@!-100)))
> 3 - access("TEMP_WATER_FREEZE"=0)
>
> [NOTE: INDEX(T1 T1_I1) hint was used to produce the above as a
> demonstration]
>
> If the T1_I1 index is on the TEMP_WATER_FREEZE column, and the T1_I2
> index is on the MY_DATE column, the execution performance would
> probably not be optimal if the data was accessed by the T1_I1 index.
> If the DBMS XPlan showed that the index was being used, you could add
> a hint to the SQL statement to see how the performance would be
> affected:
> SELECT /*+ NO_INDEX(T1 T1_I1) */
> MY_DATE,
> MY_NUMBER,
> TEMP_WATER_FREEZE
> FROM
> T1
> WHERE
> MY_DATE BETWEEN TRUNC(SYSDATE-100) AND (SYSDATE-1)
> AND TEMP_WATER_FREEZE=0;
>
> -or-
>
> SELECT /*+ INDEX(T1 T1_I2) */
> MY_DATE,
> MY_NUMBER,
> TEMP_WATER_FREEZE
> FROM
> T1
> WHERE
> MY_DATE BETWEEN TRUNC(SYSDATE-100) AND (SYSDATE-1)
> AND TEMP_WATER_FREEZE=0;
>
> ------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Starts | E-Rows | A-Rows | A-
> Time | Buffers |
> ------------------------------------------------------------------------------------------------
> |* 1 | FILTER | | 1 | | 100 |00:00:00.01
> | 5 |
> |* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 3 | 100
> |00:00:00.01 | 5 |
> |* 3 | INDEX RANGE SCAN | T1_I2 | 1 | 3 | 100 |
> 00:00:00.01 | 3 |
> ------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> 1 - filter(TRUNC(SYSDATE@!-100)<=SYSDATE@!-1)
> 2 - filter("TEMP_WATER_FREEZE"=0)
> 3 - access("MY_DATE">=TRUNC(SYSDATE@!-100) AND
> "MY_DATE"<=SYSDATE@!-1)
>
> Keep in mind that the performance of the SQL statement could change
> with a change in the WHERE conditions.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.
But how about if I would like to know if I need an index or not ?
Received on Sat Jul 07 2007 - 07:23:03 CDT
