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?
Printable View
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?
REBUILD your indexes and analyze the tables, Oracle updates statistics for Optimizer... which helps for fast retrieval of data.
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
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.