| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Working Query Fails with PQO.
I have a "working" query which "fails" after PQO was implemented (by changing the degree on tables and indexes). Actually, the query itself doesn't really fail, it just consumes all available temp space until it fails with an ORA-01652. The solution isn't to increase the 1GB temp TS!
I'm just wondering how and why this query is consuming so much temp space when executed in parallel... and more importantly, how to explain it to a DUHveloper. Anyone care to elucidate on theory without seeing the SQL? (We're running Oracle 8.1.6.3.0 with 4 CPU's and 4GB of memory. The database is well striped on an EMC array. We're running CBO in a mixed OLTP/DSS environment.)
The Explain Plan cost on the original SQL is 331960 with FTS's on all five tables but after my modifications the Explain Plan cost is a mere 62 with only 2 FTS's. The duhveloper says his query works without PQO but fails with PQO so there must be an "optimizer bug." I'm saying the query fails because it uses too much temp space but I'd like to get a good explanation of exactly why it's using SO much. I've got a gut feel understanding of this but how can I give a cogently articulate explanation for the naysayer?
TIA! TIA! TIA! TIA! TIA! TIA! TIA! TIA! TIA! TIA! TIA! Steve Orr
. . .
OK, for the curious, detail oriented types, I got the query from the
duhveloper and found some "tuning opportunities." Here's the original SQL:
user_education ue,
user_employment w,
users u,
user_categories uc
where uc.bio_status_cd = 'U'
and u.userid = uc.userid
and c.catid = uc.catid
and w.employment_id = (
select
min(w2.employment_id)
from user_employment w2
where w2.userid = u.userid)
and ue.education_id = (
select
min(ue2.education_id)
from user_education ue2
where ue2.userid = u.userid);
------------------------------------------------------------------------
I noticed the 5 table join with only 2 equi-join conditions. Despite my raised furry eyebrows, the duhveloper, (a former Oracle employee and otherwise bright young lad:) maintains that the two correlated sub-queries really do constitute an equi-join (?) to which I said, "Why not help the optimizer and remove needless ambiguity with something like this?"
user_education ue,
user_employment w,
users u,
user_categories uc
where uc.bio_status_cd = 'U'
and u.userid = uc.userid
and u.userid = w.userid
and u.userid = ue.userid
and c.catid = uc.catid
and (w.employment_id, ue.education_id) =
select /* notice my superior technique :) */
min(w2.employment_id), min(ue2.education_id)
from user_employment w2, user_education ue2
where w2.userid = ue2.userid
and w2.userid = u.userid
and ue2.userid = u.userid;
------------------------------------------------------------------------
BTW, when I run "my" query, it only consumes 480K of temp. The original consumes all the 1GB available before it fails.
TIA! TIA! TIA! TIA! TIA! TIA! TIA! TIA! TIA! TIA! TIA!
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steve Orr INET: sorr_at_arzoo.com 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: ListGuru_at_fatcity.com (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).Received on Tue Mar 13 2001 - 18:20:57 CST
 |
 |