Hi ,
Can you guys help me in identifying problem. I am doing CTAS using and i am getting this error.
ORA-32690: HASH TABLE INFRASTRUCTURE RAN OUT OF MEMORY
Is there any other way to avoide this has join. As, i saw it's using too much TEMP space and may be
this would be an issue.
Also, i had set below event and this has generated dump file for me.
ALTER SESSION SET EVENTS '32690 trace name errorstack level 3';
create table sas_stage.pqri8_clm_lines
parallel 2
nologging
tablespace sas_stage_data
as
select distinct b.run_dt,
b.part_b_link_num,
c.FICARR_carrier_IDENT,
b.CARR_CLM_CNTL_NUM,
substr(b.CARR_CLM_CNTL_NUM, 1, 14) as CARR_CLM_CNTL_NUM14,
t.bene_clm_num_equated as bene_clm_num_eq,
translate(substr(b.CARR_CLM_CNTL_NUM, 15, 1),
'0246897531',
'0123456789') as CARR_CLM_CNTL_NUM_LAST1,
b.nch_clm_type_cd,
t.bene_clm_num,
b.hse_clm_from_dt,
b.hse_clm_thru_dt,
b.hcfa_clm_proc_dt,
c.hse_clm_pymt_dnl_cd,
e.BENE_CRNT_HIC_NUM as BENE_CLM_NUM_CURR,
t.BENE_SEX_IDENT_CD,
b.BENE_BIRTH_DT
from part_b.part_b_fact b,
part_b.ptb_bene_at_time_clm t,
part_b.carrier_processing_dimension c,
part_d.beneficiary_dimension e,
part_b.final_dimension f,
part_b.claim_line_demog_dimension cl
where b.first_expns_dt between to_date('11/01/2007', 'mm/dd/yyyy') and
to_date('12/31/2008', 'mm/dd/yyyy')
and b.last_expns_dt between to_date('11/01/2007', 'mm/dd/yyyy') and
to_date('12/31/2008', 'mm/dd/yyyy')
and b.clm_line_demog_id = cl.clm_line_demog_id
and cl.line_type_srvc_cd <> 'F'
and b.ptb_bene_at_time_clm_id = t.ptb_bene_at_time_clm_id
and b.carr_prcsg_id = c.carr_prcsg_id
and b.bene_id = e.beneficiary_id
and b.not_final_id = f.final_id
and f.FINAL_CD in ('X', 'Y')
and b.hcfa_clm_proc_dt < to_date('03/01/2009', 'mm/dd/yyyy')
and b.run_dt < to_date('04/01/2009', 'mm/dd/yyyy')
and ((b.not_final_id = 1) OR
(b.not_final_id <> 1 and
f.final_time_stamp > to_date('02/28/2009', 'mm/dd/yyyy')));
Plan hash value: 1576076526
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 721M| 134G| | 40M (1)|164:58:35 | | |
| 1 | HASH UNIQUE | | 721M| 134G| 282G| 40M (1)|164:58:35 | | |
|* 2 | HASH JOIN | | 721M| 134G| | 20M (1)| 84:55:14 | | |
|* 3 | TABLE ACCESS FULL | CLAIM_LINE_DEMOG_DIMENSION | 25412 | 198K| | 7 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 721M| 128G| 2126M| 20M (1)| 84:54:08 | | |
| 5 | TABLE ACCESS FULL | PTB_BENE_AT_TIME_CLM | 49M| 1559M| | 20337 (4)| 00:04:59 | | |
|* 6 | HASH JOIN | | 712M| 105G| 2175M| 15M (2)| 64:12:36 | | |
| 7 | TABLE ACCESS FULL | BENEFICIARY_DIMENSION | 73M| 1333M| | 144K (2)| 00:35:21 | | |
|* 8 | HASH JOIN | | 712M| 92G| 3936K| 11M (2)| 45:14:50 | | |
|* 9 | TABLE ACCESS FULL | FINAL_DIMENSION | 134K| 2357K| | 54 (23)| 00:00:01 | | |
|* 10 | HASH JOIN | | 735M| 83G| 3088K| 7083K (3)| 28:51:41 | | |
| 11 | TABLE ACCESS FULL | CARRIER_PROCESSING_DIMENSION | 126K| 1603K| | 26 (8)| 00:00:01 | | |
| 12 | PARTITION RANGE ALL | | 735M| 74G| | 3451K (4)| 14:03:43 | 1 | 75 |
| 13 | PARTITION LIST ITERATOR| | 735M| 74G| | 3451K (4)| 14:03:43 | KEY | KEY |
|* 14 | TABLE ACCESS FULL | PART_B_FACT | 735M| 74G| | 3451K (4)| 14:03:43 | 1 | 1917 |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."CLM_LINE_DEMOG_ID"="CL"."CLM_LINE_DEMOG_ID")
3 - filter("CL"."LINE_TYPE_SRVC_CD"<>'F')
4 - access("B"."PTB_BENE_AT_TIME_CLM_ID"="T"."PTB_BENE_AT_TIME_CLM_ID")
6 - access("B"."BENE_ID"="E"."BENEFICIARY_ID")
8 - access("B"."NOT_FINAL_ID"="F"."FINAL_ID")
filter("B"."NOT_FINAL_ID"=1 OR "F"."FINAL_TIME_STAMP">TIMESTAMP'2009-02-28 00:00:00' AND "B"."NOT_FINAL_ID"<>1)
9 - filter("F"."FINAL_CD"='X' OR "F"."FINAL_CD"='Y')
10 - access("B"."CARR_PRCSG_ID"="C"."CARR_PRCSG_ID")
14 - filter("B"."FIRST_EXPNS_DT">=TO_DATE('2007-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"B"."LAST_EXPNS_DT">=TO_DATE('2007-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "B"."FIRST_EXPNS_DT"<=TO_DATE('2008-12-31
00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "B"."LAST_EXPNS_DT"<=TO_DATE('2008-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"B"."HCFA_CLM_PROC_DT"<TO_DATE('2009-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "B"."RUN_DT"<TO_DATE('2009-04-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))