Re: Oracle 9i database index

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sat, 07 Jul 2007 04:25:47 -0700
Message-ID: <1183807547.560775.188250@g4g2000hsf.googlegroups.com>

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;

|*  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. Received on Sat Jul 07 2007 - 06:25:47 CDT