Count query taking too long vs identical query with columns returned
Date: 13 Sep 2001 15:22:34 -0700
Message-ID: <fc5a28de.0109131422.1f3878c_at_posting.google.com>
I'm having trouble trying to understand why we are getting poor performance on a particular query. The 1st query below selects from tables which are fairly large (1.5 million rows) and returns in a reasonable 3-4 seconds. The 2nd query is identical to the 1st except the column returned is simply a count but takes well over 2 minutes. By comparing the explain plans (they are nearly identical) I can make no sense why the count query is taking such a significant amount of time to complete.
I've included the queries and the explain plans below and I would truly appreciate any help you can provide. We are using Oracle 8.1.6 and ADO to retrieve the data, although the performance problem is identical when the query is run from the application and when it's run from a SQL tool.
Please excuse the messy explain plans, I tried to make them as readable as I could, I wrapped '*' around the two items which are different between the plans.
1st query (roughly takes 3-4 seconds to complete)
SELECT FamilyName, GivenNames, DateOfBirth, SINDigits,
VoterPerson.status || ' - ' || VoterPerson.StatusReason AS
status,LTRIM(RTRIM(ADDR_SUMRY(addrdesc.addrdesc_sk,addrdesc.addrdesc_type)))
AS BldngStreet, city.name AS res_city, LPAD(VoterRegDoc.CardNumber,
8, '0') CardNumber, VoterPerson.VoterPerson_sk,
FamilyName.StandardSearchName,mailinginfo.city AS mail_city
FROM VOTERPERSON, VOTERRES, VOTERREGDOC, VOTERMAILING, ADDRDESC,
CITY, FAMILYNAME, MAILINGINFO
WHERE VOTERRES.VoterPerson_sk (+)= VOTERPERSON.VoterPerson_sk
AND VOTERMAILING.VoterPerson_sk (+)= VOTERPERSON.VoterPerson_sk
AND FAMILYNAME.FamilyName_sk = VOTERPERSON.FamilyName_sk
AND ADDRDESC.addrdesc_sk (+)= VOTERRES.addrdesc_sk
AND CITY.city_sk (+)= ADDRDESC.city_sk
AND voterperson.VOTERPERSON_SK = voterregdoc.VOTERPERSON_SK
AND VOTERREGDOC.VoterRegDoc_sk =
(SELECT vr2.VoterRegDoc_sk
FROM VOTERREGDOC vr2
WHERE vr2.VoterPerson_sk = VOTERPERSON.VoterPerson_sk
AND ROWNUM=1
AND vr2.entered =
(SELECT MIN(vr3.entered)
FROM VOTERREGDOC vr3
WHERE vr3.Voterperson_sk = vr2.Voterperson_sk))
AND FAMILYNAME.StandardSearchName = 'WILLIAMS'
AND MAILINGINFO.MailingInfo_sk (+)= VOTERMAILING.MailingInfo_sk
AND VOTERPERSON.STATUS = 'Active'
ORDER BY FamilyName.StandardSearchName
1st query EXPLAIN PLAN
SELECT STATEMENT Optimizer=CHOOSE (Cost=128 Card=1 Bytes=133)
NESTED LOOPS (OUTER) (Cost=128 Card=1 Bytes=133)
NESTED LOOPS (OUTER) (Cost=126 Card=1 Bytes=110)
NESTED LOOPS (OUTER) (Cost=123 Card=1 Bytes=100)
NESTED LOOPS (OUTER) (Cost=122 Card=1 Bytes=85)
NESTED LOOPS (OUTER) (Cost=120 Card=1 Bytes=76)
NESTED LOOPS (Cost=117 Card=1 Bytes=66)
NESTED LOOPS (Cost=71 Card=23 Bytes=1104)
INDEX (FAST FULL SCAN) OF FAMILYNAME_STDSEARCHNAME_PK_I
(UNIQUE) (Cost=45 Card=2 Bytes=24)
TABLE ACCESS (BY INDEX ROWID) OF VOTERPERSON (Cost=13
Card=1645170 Bytes=59226120)
INDEX (RANGE SCAN) OF VOTERPERSON_SRCH_5 (NON-UNIQUE)
(Cost=2
Card=1645170)
TABLE ACCESS (BY INDEX ROWID) OF VOTERREGDOC (Cost=2
Card=1645174
Bytes=29613132)
INDEX (UNIQUE SCAN) OF XPKVOTERREGDOC (UNIQUE) (Cost=1
Card=1645174)
COUNT (STOPKEY)
TABLE ACCESS (BY INDEX ROWID) OF VOTERREGDOC
(Cost=4 Card=1
Bytes=17)
INDEX (RANGE SCAN) OF VOTERREGDOC_ENTERED_I
(NON-UNIQUE)
(Cost=3 Card=1)
SORT (AGGREGATE)
FIRST ROW (Cost=3 Card=2 Bytes=24)
INDEX (RANGE SCAN (MIN/MAX)) OF
VOTERREGDOC_ENTERED_I (NON-UNIQUE)
(Cost=3 Card=2)
INDEX (RANGE SCAN) OF VOTERRES_VPERSON_ADDRDESC_I
(NON-UNIQUE)
(Cost=3 Card=1645170 Bytes=16451700)
TABLE ACCESS (BY INDEX ROWID) OF ADDRDESC (Cost=2
Card=1486490
Bytes=13378410)
INDEX (UNIQUE SCAN) OF XPKADDRDESC (UNIQUE) (Cost=1
Card=1486490)
*TABLE ACCESS (BY INDEX ROWID) OF CITY (Cost=1 Card=2856
Bytes=42840)*
INDEX (UNIQUE SCAN) OF XPKCITY (UNIQUE)
INDEX (RANGE SCAN) OF VOTERMAILING_MAILING (NON-UNIQUE) (Cost=3
Card=1645170 Bytes=16451700)
TABLE ACCESS (BY INDEX ROWID) OF MAILINGINFO (Cost=2 Card=1757891
Bytes=40431493)
INDEX (UNIQUE SCAN) OF XPKMAILINGINFO (UNIQUE) (Cost=1
Card=1757891)
2nd query (roughly takes 2-3 minutes to complete)
SELECT COUNT(1)
FROM VOTERPERSON, VOTERRES, VOTERREGDOC, VOTERMAILING, ADDRDESC,
CITY,
FAMILYNAME, MAILINGINFO
WHERE VOTERRES.VoterPerson_sk (+)= VOTERPERSON.VoterPerson_sk
AND VOTERMAILING.VoterPerson_sk (+)= VOTERPERSON.VoterPerson_sk
AND FAMILYNAME.FamilyName_sk = VOTERPERSON.FamilyName_sk
AND ADDRDESC.addrdesc_sk (+)= VOTERRES.addrdesc_sk
AND CITY.city_sk (+)= ADDRDESC.city_sk
AND voterperson.VOTERPERSON_SK = voterregdoc.VOTERPERSON_SK
AND VOTERREGDOC.VoterRegDoc_sk =
(SELECT vr2.VoterRegDoc_sk
FROM VOTERREGDOC vr2
WHERE vr2.VoterPerson_sk = VOTERPERSON.VoterPerson_sk
AND ROWNUM=1
AND vr2.entered =
(SELECT MIN(vr3.entered)
FROM VOTERREGDOC vr3
WHERE vr3.Voterperson_sk = vr2.Voterperson_sk))
AND FAMILYNAME.StandardSearchName = 'WILLIAMS'
AND MAILINGINFO.MailingInfo_sk (+)= VOTERMAILING.MailingInfo_sk
AND VOTERPERSON.STATUS = 'Active'
2nd query EXPLAIN PLAN
SELECT STATEMENT Optimizer=CHOOSE (Cost=127 Card=1 Bytes=82)
*SORT (AGGREGATE)*
NESTED LOOPS (OUTER) (Cost=127 Card=1 Bytes=82)
NESTED LOOPS (OUTER) (Cost=125 Card=1 Bytes=68)
NESTED LOOPS (OUTER) (Cost=122 Card=1 Bytes=58)
NESTED LOOPS (OUTER) (Cost=122 Card=1 Bytes=55)
NESTED LOOPS (OUTER) (Cost=120 Card=1 Bytes=47)
NESTED LOOPS (Cost=117 Card=1 Bytes=37)
NESTED LOOPS (Cost=71 Card=23 Bytes=621)
INDEX (FAST FULL SCAN) OF
FAMILYNAME_STDSEARCHNAME_PK_I
(UNIQUE) (Cost=45 Card=2 Bytes=24)
TABLE ACCESS (BY INDEX ROWID) OF VOTERPERSON (Cost=13
Card=1645170 Bytes=24677550)
INDEX (RANGE SCAN) OF VOTERPERSON_SRCH_5
(NON-UNIQUE)
(Cost=2 Card=1645170)
TABLE ACCESS (BY INDEX ROWID) OF VOTERREGDOC (Cost=2
Card=1645174 Bytes=16451740)
INDEX (UNIQUE SCAN) OF XPKVOTERREGDOC (UNIQUE)
(Cost=1
Card=1645174)
COUNT (STOPKEY)
TABLE ACCESS (BY INDEX ROWID) OF VOTERREGDOC
(Cost=4
Card=1 Bytes=17)
INDEX (RANGE SCAN) OF VOTERREGDOC_ENTERED_I
(NON-
UNIQUE) (Cost=3 Card=1)
SORT (AGGREGATE)
FIRST ROW (Cost=3 Card=2 Bytes=24)
INDEX (RANGE SCAN (MIN/MAX)) OF
VOTERREGDOC_ENTERED_I (NON-UNIQUE)
(Cost=3 Card=2)
INDEX (RANGE SCAN) OF VOTERRES_VPERSON_ADDRDESC_I
(NON-UNIQUE)
(Cost=3 Card=1645170 Bytes=16451700)
TABLE ACCESS (BY INDEX ROWID) OF ADDRDESC (Cost=2
Card=1486490
Bytes=11891920)
INDEX (UNIQUE SCAN) OF XPKADDRDESC (UNIQUE) (Cost=1
Card=1486490)
INDEX (UNIQUE SCAN) OF XPKCITY (UNIQUE)
INDEX (RANGE SCAN) OF VOTERMAILING_MAILING (NON-UNIQUE) (Cost=3
Card=1645170 Bytes=16451700)
TABLE ACCESS (BY INDEX ROWID) OF MAILINGINFO (Cost=2 Card=1757891
Bytes=24610474)
INDEX (UNIQUE SCAN) OF XPKMAILINGINFO (UNIQUE) (Cost=1
Card=1757891)
Thanks,
Andreas
Received on Fri Sep 14 2001 - 00:22:34 CEST