Hi,
I require your assistance. I work on Oracle 9.2.0.4 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.
I require your assistance. I work on Oracle 9.2.0.4 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.
Requires Free Membership to View
Sounds like to you are not gathering your table/index statistics on a regular basis. The first thing I would do is set up a batch job to run the DBMS_STATS.Gather_Schema_Stats package every night during a batch window when there is not much activity in your database. Use the Estimate option and not compute since your tables are large.
This was first published in June 2007
Join the conversationComment
Share
Comments
Results
Contribute to the conversation