Hi All,
I need help in tuning the following query. It takes around 6-7 minutes to run. I hope that someone will be able to go through the details and give me a few pointers.
I have gathered a few statistics, but don't know where to go from here.
Please view the mail in a fixed size font e.g.
Before checking stats, execution plans and the like take a look at your query. I
presume that it is generated, otherwise you would probably say that a date belongs to
a month by using a BETWEEN the first and the thirty first rather than listing all the
31 days, would you ? Now perhaps the
In addition, since you're using the cost-based optimizer (CBO), make sure
that you've used dbms_stats to gather statistics on all the objects
involved in the query recently. If you have any columns where histograms
would be useful, make sure you've gathered histograms for those columns.
of distinct values).
regards
Jo
New DBA [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/24/2003 09:39
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Tuning help required
Hi All,
I need help
Hi New DBA,
Further to what Stephane has said below, the following two stats in your
initial post are interesting ...
consistent gets 559985
table fetch continued row 212027
That suggests that there is a fair amount of row
DBA [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/24/2003 12:39 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Tuning help required
Hi All,
I need help in tuning the following query. It takes around 6-7 minutes
02:20
To: Multiple recipients of list ORACLE-L
Subject: Re: Tuning help required
Well, I'd start by writing the date part as :
MEPAI.MPAI_AS_OF_DATE between
to_date('03/01/2003','MM/DD/') and
to_date('03/31/2003','MM/DD/')
It will at the very least make the query easier
