INSERT INTO co_ClmLvl2 ( ..146 Columns.. ) SELECT ..146 Columns.. {all from H_I.HI_CLMS} FROM H_I.HI_CLMS a, H_RULES.HR_175_EmpGrp b, zzz_ClmLvl2 x, zzz_MemType z WHERE UPPER(a.MEMBER_GROUP_CODE) = b.GroupNumber AND CASE WHEN a.PPar_Flag = '1' THEN 'Y_' ELSE 'N_' END || REPLACE(a.CLAIM_ID,'-','') || '+' || TO_CHAR(a.LINE_NUMBER) = x.ClmNum AND COALESCE(z.LVL2ID, b.GroupNumber,'ALL_OTHERS_' || CASE WHEN SUBSTR(a.MEMBER_GROUP_CODE,1,1) = 'S' THEN 'SUPERMAX' WHEN SUBSTR(a.MEMBER_GROUP_CODE,1,1) = 'B' THEN 'BLUEADV' ELSE 'NA' END) = x.LVL2ID; COMMIT; /
Sql above gives me Ora-1652 even when I added two more tempfiles each 30gig to temp ts. Something peculiar is that when I do a simple select count(*) from H_I, it takes hours to complete. I checked dba_objects and it shows H_I as valid. And the sql is not even listed in v $session_longops. The under mentioned sql doesn't sum up to more than 4 gig at any point of time:
Code: SELECT (Sum(vss.Value)/1024/1024/1024) GB FROM v$session vs, v$sesstat vss, v$statname vsn WHERE (vss.statistic#=vsn.statistic#) AND (vs.sid = vss.sid) AND (vsn.name like '%sort%') AND vss.Value>0;
Also, when i check V$TEMP_SPACE_HEADER, BYTES_FREE is always 0. I am just confused is it an issue with temp files, does the sql need tuning, or is it a problem with H_I table? Expected number of result rows is 3 million, avg_row_len is 700.
> INSERT INTO co_ClmLvl2 > ( > ..146 Columns.. > ) > SELECT > ..146 Columns.. {all from H_I.HI_CLMS} > FROM > H_I.HI_CLMS a, > H_RULES.HR_175_EmpGrp b, > zzz_ClmLvl2 x, > zzz_MemType z > WHERE > UPPER(a.MEMBER_GROUP_CODE) = b.GroupNumber > AND CASE WHEN a.PPar_Flag = '1' THEN 'Y_' ELSE 'N_' END || > REPLACE(a.CLAIM_ID,'-','') || '+' || TO_CHAR(a.LINE_NUMBER) = x.ClmNum > AND COALESCE(z.LVL2ID, b.GroupNumber,'ALL_OTHERS_' || CASE > WHEN SUBSTR(a.MEMBER_GROUP_CODE,1,1) = 'S' THEN 'SUPERMAX' WHEN > SUBSTR(a.MEMBER_GROUP_CODE,1,1) = 'B' THEN 'BLUEADV' ELSE 'NA' END) > = x.LVL2ID; > COMMIT; > /
> Sql above gives me Ora-1652 even when I added two more tempfiles each > 30gig to temp ts. Something peculiar is that when I do a simple select > count(*) from H_I, it takes hours to complete. I checked dba_objects > and it shows H_I as valid. And the sql is not even listed in v > $session_longops. The under mentioned sql doesn't sum up to more than > 4 gig at any point of time:
> Code: > SELECT > (Sum(vss.Value)/1024/1024/1024) GB > FROM > v$session vs, > v$sesstat vss, > v$statname vsn > WHERE > (vss.statistic#=vsn.statistic#) > AND (vs.sid = vss.sid) > AND (vsn.name like '%sort%') > AND vss.Value>0;
> Also, when i check V$TEMP_SPACE_HEADER, BYTES_FREE is always 0. I am > just confused is it an issue with temp files, does the sql need > tuning, or is it a problem with H_I table? Expected number of result > rows is 3 million, avg_row_len is 700.
> Looking forward for you advices.
> Thanks in advance, > gtcol
It appears that you have a Cartesian join with the zzz_MemType table: "AND COALESCE(z.LVL2ID, b.GroupNumber,'ALL_OTHERS_' ... = x.LVL2ID"
Take a close look at the WHERE clause to see if is possible to eliminate the Cartesian join.
Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
> On Jan 4, 7:14 am, dbagtcol <cx4gt...@gmail.com> wrote:
> > Hi all,
> > INSERT INTO co_ClmLvl2 > > ( > > ..146 Columns.. > > ) > > SELECT > > ..146 Columns.. {all from H_I.HI_CLMS} > > FROM > > H_I.HI_CLMS a, > > H_RULES.HR_175_EmpGrp b, > > zzz_ClmLvl2 x, > > zzz_MemType z > > WHERE > > UPPER(a.MEMBER_GROUP_CODE) = b.GroupNumber > > AND CASE WHEN a.PPar_Flag = '1' THEN 'Y_' ELSE 'N_' END || > > REPLACE(a.CLAIM_ID,'-','') || '+' || TO_CHAR(a.LINE_NUMBER) = x.ClmNum > > AND COALESCE(z.LVL2ID, b.GroupNumber,'ALL_OTHERS_' || CASE > > WHEN SUBSTR(a.MEMBER_GROUP_CODE,1,1) = 'S' THEN 'SUPERMAX' WHEN > > SUBSTR(a.MEMBER_GROUP_CODE,1,1) = 'B' THEN 'BLUEADV' ELSE 'NA' END) > > = x.LVL2ID; > > COMMIT; > > /
> > Sql above gives me Ora-1652 even when I added two more tempfiles each > > 30gig to temp ts. Something peculiar is that when I do a simple select > > count(*) from H_I, it takes hours to complete. I checked dba_objects > > and it shows H_I as valid. And the sql is not even listed in v > > $session_longops. The under mentioned sql doesn't sum up to more than > > 4 gig at any point of time:
> > Code: > > SELECT > > (Sum(vss.Value)/1024/1024/1024) GB > > FROM > > v$session vs, > > v$sesstat vss, > > v$statname vsn > > WHERE > > (vss.statistic#=vsn.statistic#) > > AND (vs.sid = vss.sid) > > AND (vsn.name like '%sort%') > > AND vss.Value>0;
> > Also, when i check V$TEMP_SPACE_HEADER, BYTES_FREE is always 0. I am > > just confused is it an issue with temp files, does the sql need > > tuning, or is it a problem with H_I table? Expected number of result > > rows is 3 million, avg_row_len is 700.
> > Looking forward for you advices.
> > Thanks in advance, > > gtcol
> It appears that you have a Cartesian join with the zzz_MemType table: > "AND COALESCE(z.LVL2ID, b.GroupNumber,'ALL_OTHERS_' ... = x.LVL2ID"
> Take a close look at the WHERE clause to see if is possible to > eliminate the Cartesian join.
> Charles Hooper > IT Manager/Oracle DBA > K&M Machine-Fabricating, Inc.- Hide quoted text -
> - Show quoted text -
Yes, look at the explain plan to see how the CBO is solving this query. Update the statistics and re-check the plan to see if you get a change. My guess is the sort space is not being used to hold a sort but instead is hold a hash table. You can query v$sort_usage while the query is running to verify this but the plan alone should be enought to let you determine if this is true.
Determine how the query should be driving (order tables visited) and how each join operation should be done (hash, nested loops, merge join). Work to get the explain plan to show your desired path and test that version of the query.
> On Jan 4, 7:59 am, Charles Hooper <hooperc2...@yahoo.com> wrote: > > On Jan 4, 7:14 am, dbagtcol <cx4gt...@gmail.com> wrote:
> > > Hi all,
> > > INSERT INTO co_ClmLvl2 > > > ( > > > ..146 Columns.. > > > ) > > > SELECT > > > ..146 Columns.. {all from H_I.HI_CLMS} > > > FROM > > > H_I.HI_CLMS a, > > > H_RULES.HR_175_EmpGrp b, > > > zzz_ClmLvl2 x, > > > zzz_MemType z > > > WHERE > > > UPPER(a.MEMBER_GROUP_CODE) = b.GroupNumber > > > AND CASE WHEN a.PPar_Flag = '1' THEN 'Y_' ELSE 'N_' END || > > > REPLACE(a.CLAIM_ID,'-','') || '+' || TO_CHAR(a.LINE_NUMBER) = x.ClmNum > > > AND COALESCE(z.LVL2ID, b.GroupNumber,'ALL_OTHERS_' || CASE > > > WHEN SUBSTR(a.MEMBER_GROUP_CODE,1,1) = 'S' THEN 'SUPERMAX' WHEN > > > SUBSTR(a.MEMBER_GROUP_CODE,1,1) = 'B' THEN 'BLUEADV' ELSE 'NA' END) > > > = x.LVL2ID; > > > COMMIT; > > > /
> > > Sql above gives me Ora-1652 even when I added two more tempfiles each > > > 30gig to temp ts. Something peculiar is that when I do a simple select > > > count(*) from H_I, it takes hours to complete. I checked dba_objects > > > and it shows H_I as valid. And the sql is not even listed in v > > > $session_longops. The under mentioned sql doesn't sum up to more than > > > 4 gig at any point of time:
> > > Code: > > > SELECT > > > (Sum(vss.Value)/1024/1024/1024) GB > > > FROM > > > v$session vs, > > > v$sesstat vss, > > > v$statname vsn > > > WHERE > > > (vss.statistic#=vsn.statistic#) > > > AND (vs.sid = vss.sid) > > > AND (vsn.name like '%sort%') > > > AND vss.Value>0;
> > > Also, when i check V$TEMP_SPACE_HEADER, BYTES_FREE is always 0. I am > > > just confused is it an issue with temp files, does the sql need > > > tuning, or is it a problem with H_I table? Expected number of result > > > rows is 3 million, avg_row_len is 700.
> > > Looking forward for you advices.
> > > Thanks in advance, > > > gtcol
> > It appears that you have a Cartesian join with the zzz_MemType table: > > "AND COALESCE(z.LVL2ID, b.GroupNumber,'ALL_OTHERS_' ... = x.LVL2ID"
> > Take a close look at the WHERE clause to see if is possible to > > eliminate the Cartesian join.
> > Charles Hooper > > IT Manager/Oracle DBA > > K&M Machine-Fabricating, Inc.
> Yes, look at the explain plan to see how the CBO is solving this > query. Update the statistics and re-check the plan to see if you get > a change. My guess is the sort space is not being used to hold a sort > but instead is hold a hash table. You can query v$sort_usage while > the query is running to verify this but the plan alone should be > enought to let you determine if this is true.
> Determine how the query should be driving (order tables visited) and > how each join operation should be done (hash, nested loops, merge > join). Work to get the explain plan to show your desired path and > test that version of the query.
> HTH -- Mark D Powell
Mark,
Do you suspect that the OP might find a "MERGE JOIN CARTESIAN" (sort merge join) in the explain plan? The current WHERE clause seems to imply that it might be used. A simplified example:
And now a WHERE clause that does not directly relate one table to another: SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM T1, T2, T3 WHERE (T1.C1*T2.C1)=T3.C1;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
Thank you all for your responses. Unfortunately we had to drop this logic and do a select from another table(equivalent) due to time constraints. But I still have my query, does the outputs really make the temp buffer overflow even if we don't have any sorts ? And could there be any other reasons to cause Ora-01652 other than cartesian product in this case?
On Jan 8, 1:23 am, dbagtcol <cx4gt...@gmail.com> wrote:
> Thank you all for your responses. Unfortunately we had to drop this > logic and do a select from another table(equivalent) due to time > constraints. But I still have my query, does the outputs really make > the temp buffer overflow even if we don't have any sorts ? And could > there be any other reasons to cause Ora-01652 other than cartesian > product in this case?
Also, show us how you defined the temp ts. Maybe you didn't do what you thought, easy mistake to make (search for temporary tablespace on metalink for lots of info).
Since you didn't post platform info, it could be something silly like using a DMT with 2k blocks... Also, sort and aggregate init.ora parameters can make a difference, as well as autoextend (and some other obscure things can make temp usage more flagrant, I don't have time to remember right now, and there is misinformation floating about).
jg -- @home.com is bogus. "Tracers work both ways." - U.S. Army Ordnance
