I require your assistance. I work on Oracle 22.214.171.124 on Solaris. There is a particular issue with a table. User complains that every time he tries to access the table (select only), it takes more than one to two hours to get the results. There are no DML operations performed on the table. If I run statistics, then the select works fine for one or two days. Then it again reverts to the old stage. There is no locking involved, since there is no DML operations on the table (only select is performed). It is a long running query. Please help. Give me ideas as to what other reasons can be listed out and how it can be sorted. It is a huge table. Thanks in advance.
Dig Deeper on Oracle database performance problems and tuning
Related Q&A from Phillip Bracken
I have a table with 1.5 million records that needs to have a column updated based on a correlated subselect. This update is currently sitting at four... Continue Reading
I have always used TKPROF to do SQL tuning in previous versions of Oracle (7,8 and 9). Can I still use TKPROF in Oracle 10g R2? Continue Reading
I tried to import a database dump onto my Oracle server using the imp script. The import process currently takes 13 hours for a 3 GB dump file. Continue Reading