Home » RDBMS Server » Performance Tuning » Indexes not working after analyzing tables
Indexes not working after analyzing tables [message #109813] Tue, 01 March 2005 03:07 Go to next message
sridcpp
Messages: 23
Registered: February 2005
Location: India
Junior Member
i had performed analyze table <table name> compute statistics for CBO to perform accurate statistics for executing plans.

after analyzing, my indexes are not getting used, all the indexes were appropriately used before analyzing tables...

any suggestions...plz...

Report message to a moderator

Re: Indexes not working after analyzing tables [message #109819 is a reply to message #109813] Tue, 01 March 2005 03:47 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
How many rows do your tables have?
How distinctive are your indexes?
Do you have an explain-plan?

Report message to a moderator

Re: Indexes not working after analyzing tables [message #109821 is a reply to message #109813] Tue, 01 March 2005 04:02 Go to previous messageGo to next message
sridcpp
Messages: 23
Registered: February 2005
Location: India
Junior Member
Table description
Name
---------
TRN_ID
DSR_NO
TDATE
EMP_ID
AG_ID
TYPE
EUSER
ETIME

Index's
------------------------
Tab Typ Ind Col Pos Tbs
------------------------------ -------------------------------- ------------------------------ -----
TRANSACTIONS NORMAL/REV IDX_TRANS_EMPAG_ID EMP_ID 1
TRANSACTIONS NORMAL/REV IDX_TRANS_EMPAG_ID AG_ID 2


SQL Statement...
----------------------------------

select * from transactions where emp_id='K000000141' and ag_id='K000000035';


I dont have execution plan for before analyze, but the explain
plan never done full access..it used to use IDX_TRANS_EMPAG_ID index


After Analyze....
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=62)
1 0 TABLE ACCESS (FULL) OF 'TRANSACTIONS' (Cost=2 Card=1 Bytes
=62)

Report message to a moderator

Re: Indexes not working after analyzing tables [message #109823 is a reply to message #109813] Tue, 01 March 2005 04:07 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Am I mistaking or is there only 1 row in your table? (card = 1)

Report message to a moderator

Re: Indexes not working after analyzing tables [message #109825 is a reply to message #109813] Tue, 01 March 2005 04:11 Go to previous messageGo to next message
sridcpp
Messages: 23
Registered: February 2005
Location: India
Junior Member
Table consists of 10000 records
TRN_ID DSR_NO TDATE EMP_ID AG_ID T EUSER
---------- -------------------- --------- ---------- ---------- - -----
K000000253 16/11/04/SR 20-NOV-04 K000000141 K000000035 D MKT
K000000277 20/11/04/SR 25-NOV-04 K000000141 K000000035 D MKT
K000000279 22/11/04/SR 27-NOV-04 K000000141 K000000035 D MKT
K000000216 03/11/04/SR 03-NOV-04 K000000141 K000000035 D MKT
K000000218 04/11/04/SR 04-NOV-04 K000000141 K000000035 D MKT
K000000225 09/11/04/SR 10-NOV-04 K000000141 K000000035 D MKT
K000000226 10/11/04/SR 11-NOV-04 K000000141 K000000035 D MKT
K000000231 14/11/04/SR 18-NOV-04 K000000141 K000000035 D MKT
K000000819 02/12/04/SR 02-DEC-04 K000000141 K000000035 D MANOJ
K000000825 05/12/04/SR 05-DEC-04 K000000141 K000000035 D MANOJ
K000000831 08/12/04/SR 09-DEC-04 K000000141 K000000035 D MANOJ
K000000836 11/12/04/SR 12-DEC-04 K000000141 K000000035 D MANOJ
K000000839 14/12/04/SR 16-DEC-04 K000000141 K000000035 D MANOJ
K000000849 17/12/04/SR 19-DEC-04 K000000141 K000000035 D MANOJ
K000000852 20/12/04/SR 23-DEC-04 K000000141 K000000035 D MANOJ
K000000855 23/12/04/SR 26-DEC-04 K000000141 K000000035 D MANOJ
K000000858 27/12/04/SR 17-DEC-04 K000000141 K000000035 D MANOJ

17 rows selected.


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=62)
1 0 TABLE ACCESS (FULL) OF 'TRANSACTIONS' (Cost=2 Card=1 Bytes
=62)

Report message to a moderator

Re: Indexes not working after analyzing tables [message #109831 is a reply to message #109813] Tue, 01 March 2005 05:03 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
what's the result of the enclosed script?
  • Attachment: show_ind.sql
    (Size: 1.19KB, Downloaded 1519 times)

Report message to a moderator

Re: Indexes not working after analyzing tables [message #109849 is a reply to message #109831] Tue, 01 March 2005 08:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Nice handy script Frank!

Report message to a moderator

Re: Indexes not working after analyzing tables [message #109871 is a reply to message #109813] Tue, 01 March 2005 10:19 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Not entirely mine Wink
I borrowed this from our performance guru; the best I ever encountered. Been lurking his knowledge for the last 2 years.

Report message to a moderator

icon14.gif  Re: Indexes not working after analyzing tables [message #109974 is a reply to message #109813] Wed, 02 March 2005 08:54 Go to previous messageGo to next message
_simma_dba
Messages: 34
Registered: November 2003
Member
Before u analyze yr tables and indexes u have to export valid statistic to some table, so you can import back this stats if new statistic cause some problem with your execution plan.
Thats the first thing you have to do in future! Now you have either to anlyze tables AND indexes again to see if that will change someting. If that not change anything you can use some optimizer hints to force oracle to use your index. You can use something like this:

select /*+ INDEX (transaction IDX_TRANS_EMPAG_ID)*/ * from transactions where emp_id='K000000141' and ag_id='K000000035';

Cool

Maybe you dont need any index to execute this query, sometimes query will work faster with full table scan.

[Updated on: Wed, 02 March 2005 08:59]

Report message to a moderator

Re: Indexes not working after analyzing tables [message #110033 is a reply to message #109813] Wed, 02 March 2005 21:48 Go to previous message
pgongloo
Messages: 8
Registered: February 2005
Junior Member
You didnt mention what version of Oracle you're running, and all the optimizer settings.., things multiblock count et al make a difference. Also, if you're on 8i +above you must be using dbms_stats package to gather the stats.

Simon's suggestion is good, infact, a you can retrieve old statistics automatically for an object/schema if required in Oracle DB 10g without need of an extra step or two..

Also, what are the data types of the columns involved (number, char?), any implicit data type conversions going on ? you really need to get a 10053 event trace and investigate. -gp

Report message to a moderator

Previous Topic: Performance difference BOLB vs. LONG RAW
Next Topic: Parallel Hint Question
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 26 11:44:19 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.