Home » RDBMS Server » Performance Tuning » Not using Index (10.2.0.1, Windows 2003)
Not using Index [message #383413] Wed, 28 January 2009 16:29 Go to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Hi all,

I'm running a sql query(part of a query from the application code) to see if the index is being used on our production database. I set autotrace and here is the execution plan:

SQL> select  * from  bog where natur = 1  and   nat_code = '1' and   currn = 'Y'  and   tetd <> 'Y';

...
...
...


Execution Plan
----------------------------------------------------------

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2845 |   502K| 10540 |
|   1 |  TABLE ACCESS FULL| BOG  |  2845 |   502K| 10540 |
----------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
         99  recursive calls
          0  db block gets
      48143  consistent gets
          0  physical reads
        116  redo size
       3420  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed


There is an foreign key index FK_BOG_IDX ON BOG table for (natur,nat_code). Apparently, this index is not being used in the query.

But, for the exact number of rows in BOG table in our test database, for the same sql query the index is being used.

SQL> select  * from  bog where natur = 1  and   nat_code = '1' and   currn = 'Y'  and   tetd <> 'Y';

...
...
...


Execution Plan
----------------------------------------------------------

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

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

| Id  | Operation                   | Name                           | Rows  | B

ytes | Cost  |

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

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

|   0 | SELECT STATEMENT            |                                |    22 |
3300 |    40 |

|   1 |  TABLE ACCESS BY INDEX ROWID| COI                            |    22 |
3300 |    40 |

|   2 |   INDEX RANGE SCAN          | FK_ARCST__COMPANY__CUST_C__COI |    86 |
     |     3 |

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

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


Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
        554  recursive calls
          0  db block gets
       2565  consistent gets
          0  physical reads
        116  redo size
       3891  bytes sent via SQL*Net to client
        338  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          9  rows processed


I tried to force the index FK_BOG_IDX ON BOG in the production database by using Hint. THen the query uses this index. But at this point, I need to find out why the index is not being used in the production database and its been used in the test database with same number of rows and the tables are analyzed pretty recently on both the databases.

Please give me your thoughts.

Thank you.
Re: Not using Index [message #383414 is a reply to message #383413] Wed, 28 January 2009 16:38 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> for the exact number of rows in BOG table in our test database,
HUH? What does this really mean?

>3 rows processed
>9 rows processed

I'd conclude something is different between the 2 tables.
Re: Not using Index [message #383415 is a reply to message #383413] Wed, 28 January 2009 16:41 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Sorry about the wrong information. The BOG database in production database has 1423987 records and test database has 1411390 records in it. That could be the difference in the number of rows processed..
Re: Not using Index [message #383417 is a reply to message #383413] Wed, 28 January 2009 16:42 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Since it might not be feasible to force the indexes for the sql's in the application code, I'm thinking if it will make a difference if we rebuild the index in the BOG table in the production database? Please give me your suggestions

Thanks for your help

[Updated on: Wed, 28 January 2009 16:47]

Report message to a moderator

Re: Not using Index [message #383418 is a reply to message #383417] Wed, 28 January 2009 16:48 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Most probably statistics are outdated.
Collect the table and index stats again.
Re: Not using Index [message #383419 is a reply to message #383413] Wed, 28 January 2009 16:50 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Execution Plan
----------------------------------------------------------

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2845 |   502K| 10540 |
|   1 |  TABLE ACCESS FULL| BOG  |  2845 |   502K| 10540 |
----------------------------------------------------------


>The BOG database in production database has 1423987 records and test database has 1411390 records in it.

I have no idea who or what to believe so I'll not waste more of my time on this thread.

Re: Not using Index [message #383420 is a reply to message #383413] Wed, 28 January 2009 16:53 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Black swan, That is the total number of records in the tables. The select query is not fetching the records for the where condition.
Re: Not using Index [message #383422 is a reply to message #383413] Wed, 28 January 2009 17:26 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Mahesh,

I collected statistics and it is still not using the index..

EXECUTE DBMS_STATS.GATHER_INDEX_STATS(ownname => 'bint', indname => 'FK_BOG_IDX', ESTIMATE_PERCENT=>10)


Is there a way to find out why the index is not being used??

Thanks


Re: Not using Index [message #383423 is a reply to message #383422] Wed, 28 January 2009 17:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Again,
this is not the index used in test box.
You are providing very conflicting information.
I would use dbms_stats.gather_table_stats on the base table with cascade=>true and method_opt=>'For all indexed columns size 250').
Re: Not using Index [message #383424 is a reply to message #383422] Wed, 28 January 2009 17:35 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
One way to find why CBO is ignoring the index is to enable 10053 event.

[Updated on: Wed, 28 January 2009 17:48]

Report message to a moderator

Re: Not using Index [message #383622 is a reply to message #383422] Thu, 29 January 2009 05:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Gather stats on the whole table, rather than for just one index. I'd do it with METHOD_OPT = 'FOR ALL INDEXED COLUMNS'

Re: Not using Index [message #386316 is a reply to message #383413] Fri, 13 February 2009 10:10 Go to previous messageGo to next message
sant_new
Messages: 165
Registered: June 2008
Senior Member
Thanks for all your replies. I gathered statistics for that index and I rebuild the index. The program was considerably faster. But today we came across the problem again that now the rebuild index is not being used in the queries. Could it be because of the rebuild??

Please give me your suggestions. Thanks again

Re: Not using Index [message #386319 is a reply to message #386316] Fri, 13 February 2009 10:33 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
May be the data has changed a lot.
Try gathering stats again.
Re: Not using Index [message #386320 is a reply to message #383413] Fri, 13 February 2009 10:35 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> I gathered statistics for that index and I rebuild the index
In this order?

How much DML occurs against this table on percentage basis?
Re: Not using Index [message #386989 is a reply to message #386320] Tue, 17 February 2009 08:42 Go to previous message
sant_new
Messages: 165
Registered: June 2008
Senior Member
After the rebuild, I analyzed the full table now, and it uses the index. Thank you for your help.

[Updated on: Tue, 17 February 2009 08:42]

Report message to a moderator

Previous Topic: exchange partition
Next Topic: Tune a select query which contains a pl/sql table type
Goto Forum:
  


Current Time: Sun Dec 04 16:34:44 CST 2016

Total time taken to generate the page: 0.25174 seconds