I'm getting an ORA-04031: unable to allocate 8192 bytes of shared memory ("large
pool","unknown object","sort subheap","sort key") error, and am having a hard time
solving the issue.
The SQL being ran is:
SELECT COUNT(DISTINCT mail) theCount
FROM Demo D
WHERE (EXISTS (SELECT 1 FROM mails WHERE mail = d.mail))
AND (D.countryID IN ('US'))
AND ((EXISTS (SELECT 1 FROM Interests I WHERE D.id = I.demoID AND I.interestID=31))
OR (EXISTS (SELECT 1 FROM Interests I WHERE D.id = I.demoID AND I.interestID=84)) )
AND unsub_date IS NULL
AND return_date IS NULL
demo is a 33+M record table, Mails is 10+M and Interests is 40+M.
There are indexes on demo.mail, Mails.mail, demo.id, and interests(interestid,demoid).
I tried doubling the large pool to see if that would help, but the same query runs
fine on 8i. This is currently on 9i.
Oh, there is also a degree of parallelism on each table, each set to 4.
Any advice would be appreciated.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
