indexes problem

[mary]

Some of my user's complain about slowleness of their retrieval. I do have indexes on columns they're using in a where clause. Those indexes used to work just fine. There was no deletes performed on the table. What can I do to fix the problem?

[sreddy]

REBUILD your indexes and analyze the tables, Oracle updates statistics for Optimizer... which helps for fast retrieval of data.

[sambavan]

periodically have a plan to analyze your tables and indices, such that this would provide your cost based optimizer to make a good decision.

Sam

[tamilselvan]

Though, CBO will produce good execution (access) paths many times, the response time may not be acceptable by end users at some time.

What you need to do :
1 Capture the heavy SQL statements when the system is running slow.
2 Do EXPLAIN PLAN.
3 Add hints in the SQL statement that means over ride the CBO execution paths.
4 If all blocks in a table is needed for a SQL statement, it is better not to use INDEX. Use HASH JOIN instead of Nested Loops. Allo full table scan.
5 Configure Buffer Cache correctly. Allocate BUFFER POOL KEEP for all the look up tables.

There are plenty of ways to improve SQL statements. What you need is analytical mind.