Q

Select on Oracle table takes hours

A user complains that every time he tries to access a table (select only), it takes more than two hours to get the results. There are no DML operations performed on the table.

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.
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

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close