| application not utilizing indexes [message #54758] |
Tue, 03 December 2002 09:17  |
Mahesh
Messages: 90
Registered: January 2001
|
Member |
|
|
Hi,
i am working on application which is not utilizing indexes table consists of 50 million rows when trying to find through statspack it shows in the top 5 wait events
scattered read
sequential read
latchwaits
and the queries are doing lot of parsing
when talked to the developers the application has to be like that, so how would be the idea to utilize cursor_sharing = force and second for inserting rows into the table i am thinking of adding more freelists when adding freelists what all the consideration i have to take
secondly can you suggest me where i can get documentation on statspack (analyzing the reports and taking necessary steps
)
would appreciate if some one can help me with these issues
Mahesh Chalasani
|
|
|
|
| Re: application not utilizing indexes [message #54763 is a reply to message #54758] |
Tue, 03 December 2002 21:36  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
If the execution plan is incorrect, then I guess the statspack data won't be useful. Are you SURE that indexes should be used? What is SQL the statement and what are the indexes created?
Have you analyzed the table & indexes?
select index_name, num_rows, distinct_keys, last_analyzed from user_indexes where table_name = 'XYZ';
select table_name, num_rows, last_analyzed from user_tables where table_name = 'XYZ';
Are you using cost based optimizer?
|
|
|
|
| Re: application not utilizing indexes [message #54800 is a reply to message #54758] |
Thu, 05 December 2002 09:55 |
Mahesh
Messages: 90
Registered: January 2001
|
Member |
|
|
Hi andrew,
I do rebuild the indexes daily and analyze them
i ran the scription select table_name ,last_analyzed from user_tables and all the tables are analyzed today
why i am so concenred of using indexes is if the application uses full table scans it will be kept in lru in data buffer and will be pushed immediately
i use cost based optimization (optimizer_mode = choose ) in init.ora and my table has 50 million records
so i am concerned about using indexes
Mahesh
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
