Re: Query taking too long - please help

Just for kicks, why don't you try running the query with the RULE hint on? This will tell you if it's a problem with the cost optimizer and/or statistics. You might also want to compare the cost plan to the rule plan and see what the differences are. I've found sorts to just nearly die at times with CBO, but not consistently. Also, when you ran statistics, are you SURE statistics were collected for BOTH the index(es) and tables?? That's important.

Assuming you're running the query on the same machine that you ran before, I don't think I'd focus too much on RAM or IO being a problem, although you might see if the workload on the machine is different than the time you ran before in 5 hours.

> -----Original Message-----
> From: Lisa Lewis [SMTP:lewis_at_med.pitt.edu]
> Posted At: Friday, February 05, 1999 10:38 AM
> Posted To: comp.databases.oracle.server
> Conversation: Query taking too long - please help
> Subject: Re: Query taking too long - please help
>
> Hi again,
>
> An update: I created a temporary table in place of the correlated
> subquery
> and the query now takes about 7 hours instead of 7 days to complete.
> However, I am still a little confused because like I said in my
> original
> post I had ran the query with the correlated subquery the other day in
> 5
> hours. I suspect that it has something to do with the way the
> optimizer
> chose the execution path ( of course, what else could it be). By the
> way I
> have CHOOSE as my optimizer setting and I'm not sure if all of my
> tables
> were analyzed when I got the good performance of 5 hours out of that
> query.
> Any insights would be greatly appreciated!!
> Lisa
>
> Lisa Lewis wrote in message
> <79epvu$qnb$1_at_usenet01.srv.cis.pitt.edu>...
> >Hi all,
> >
> >I am hoping someone can give me suggestions on how to get this query
> to run
> >faster. This query is part of a pro*c program.
> >
> > EXEC SQL DECLARE c_adtdemo CURSOR FOR
> > SELECT p.mrn, p.source, p.lname, p.fname, p.mname, p.dob, p.sex,
> p.race,
> > p.addr1, p.addr2, p.city, p.state, p.zip, p.country,
> p.hphone,
> > p.bphone, p.acctid, p.ssn, v.domain,
> > nr.lname, nr.fname, nr.addr1, nr.addr2,
> > nr.city, nr.state, nr.zip, nr.country, nr.hphone,
> > ec.lname, ec.fname, ec.addr1, ec.addr2,
> > ec.city, ec.state, ec.zip, ec.country, ec.hphone
> > FROM Mars.patient p, Mars.patientnkin nr, Mars.Visit v,
> Mars.patientnkin
> >ec
> > WHERE p.acctid = nr.acctid (+)
> > AND p.source = nr.source (+)
> > AND nr.contactrole (+) = 'NR'
> > AND p.acctid = ec.acctid (+)
> > AND p.source = ec.source (+)
> > AND ec.contactrole (+) = 'EC'
> > AND p.acctid = v.acctid
> > AND p.source = v.source
> > AND v.updatedate = ( SELECT MAX(v2.updatedate)
> > FROM visit v2
> > WHERE v2.acctid = v.acctid
> > AND v2.source = v.source );
> >
> >Note: the purpose of this query is to extract patient demographics
> along
> >with data about the patients latest visit and some next of kin
> information.
> >A little background: The patient table has about 500,000 rows. The
> visit
> >table has about 2,000,000 rows and the patientnkin has about 450,000
> rows.
> >The pk for all of the tables has acctid and source as the first two
> elements
> >of the pk. There is no indexes defined on ec.contactrole. Note:
> This is
> a
> >decision support system with no users currently so there is no
> activity
> >going on that should slow this down.
> >
> >I am obviously most concerned about the correlated subquery. What is
> >strange is that the other day I ran this query against the tables
> described
> >above and it took about 5 hours and produced about 460,000 rows. The
> next
> >day I ran this query, based on the progress it was making,
> estimations
> would
> >put it at about 7 days to complete. The only thing I did between the
> two
> >runs is to re-analyze some tables so that the statistics could be
> most up
> to
> >date. ( I thought that this would help but it seemed to have the
> opposite
> >effect). I am now in the process of deleting some of my statistics
> to see
> >what the impact of that would be. I know that I can create a
> temporary
> >table to instead of using the correlated subquery, I will try that
> as a
> >last resort. The strange thing is that this query did once run to
> >completion in 5 hours.
> >
> >Please respond via e-mail.
> >I would appreciate all help!!!
> >
> >Thanks
> >Lisa
> >
> >
>
Received on Fri Feb 05 1999 - 10:16:27 CST