Hi there,
We've got a major problem when using XML in our database.
Our current configuration:
-Oracle 10.2.0.3.0
-Microsoft Windows Server 2003 Server 5.2 Service Pack 2 (32-bit)
-Hardware Platform VMware Virtual Platform
-4 CPUs
-Memory Size (MB) 7936
-pga_aggregate_target 700MB
-sga_max_size 2200MB
-sga_target 1968MB
The problem is as follows:
The following (large segment of) code returns 15k invoices that are aggregated to one row (using the xmlagg function, see the beginning of the query).
However, when running this code, our database regularly crashes with the ORA-4030 error, indicating max. memory usage (out of process memory).
Indeed, when running the code, the PGA usage starts slowly expanding (starting at 5MB, but after a while it 'explodes': ~50MB->100MB->150MB, 250MB, etc), reaching the 3GB mark combined with the SGA usage.
Do you have any ideas how to solve this problem? Ofcourse, migrating to a 64 bits environment would be an option, but I am quite certain that there also has to be another solution.
select xmlroot
( xmlelement
( "NOTA_KV_XML.V8"
, xmlattributes
(
)
, xmlagg
( xmlelement
( "BilledContract"
, xmlelement
( "ContractSeq"
, ftr.seq
)
-- Afzender
, xmlelement
( "VasteTxt"
, xmlforest
( (pgl.bedrijf_straat ||' ' || pgl.bedrijf_huisnummer) "TxtKopregel1"
, (fkn_ola_lbl_bdf_postbus ||' ' ||pgl.bedrijf_postbus ||', ' ||pgl.bedrijf_pb_postcode ||' ' ||pgl.bedrijf_pb_plaats) "TxtKopregel2"
, (pgl.fkn_ola_lbl_bdf_telefoon ||' ' ||pgl.bedrijf_telefoon) "TxtKopregel3"
, (pgl.bedrijf_bankrekeningnr) "TxtKopregel4"
, (pgl.fkn_ola_lbl_bdf_web ||' ' ||pgl.bedrijf_website) "TxtKopregel5"
, (pgl.fkn_ola_lbl_bdf_email || ' ' || pgl.bedrijf_email) "TxtKopregel6"
, (pgl.bedrijf_banknaam ||' ' ||pgl.bedrijf_bankrekeningnr) "TxtKopregel7"
, (pgl.bedrijf_naam ||' ' ||pgl.bedrijf_bu_inf_beh) "TxtKopregel8"
, (pgl.fkn_ola_lbl_bdf_kvk ||' ' ||pgl.bedrijf_kvk_plaats ||' ' ||pgl.bedrijf_kvk_nummer) "TxtKopregel9"
, (pgl.fkn_ola_lbl_bdf_btw ||' ' ||pgl.bedrijf_btwnr) "TxtKopregel10"
)
)
-- Factuurgegegevens
, xmlforest
( to_char( ftr.creation_date, pgl.datum_formaat_dd_mm_yy) "FactuurDatum"
, ftr.external_id "FactuurNummer"
, ftr.act_ext_account_id "KlantNummer"
, ftr.act1_dear "FNaam"
, ftr.aln1_address1 "FStraat"
, ftr.aln1_house_number "FHuisNummer"
, ftr.aln1_postal_code_id "FPostcode"
, ftr.aln1_city "FPlaats"
, ftr.address1 "VStraat"
, ftr.house_number "VHuisnummer"
, ftr.postal_code_id "Vpostcode"
, ftr.city "VPlaats"
)
-- Type nota
, case
when ftr.type_of_note = 'JAAR'
then
xmlelement
( "TypeNota"
, pgl.fkn_ola_lbl_type_nota_jaar
||' '
||to_char
( ftr.billing_stop_date
- 1
, pgl.datum_formaat_month_yyyy
, 'nls_date_language = dutch'
)
)
when ftr.type_of_note = 'EIND'
then
xmlelement
( "TypeNota"
, pgl.fkn_ola_lbl_type_eind
||' '
||to_char
( ftr.billing_stop_date
- 1
, pgl.datum_formaat_month_yyyy
, 'nls_date_language = dutch'
)
)
when ftr.type_of_note = 'MND'
then
xmlelement
( "TypeNota"
, pgl.fkn_ola_lbl_type_maand
||' '
||to_char
( ftr.billing_stop_date
- 1
, pgl.datum_formaat_month_yyyy
, 'nls_date_language = dutch'
)
)
else
xmlelement
( "TypeNota"
, ' '
)
end
-- Factuurregels
, xmlelement
( "Verzamel"
, xmlelement
( "VerzamelCategorie"
, xmlforest
( pgl.fkn_ola_lbl_ftrrgl_hd1_oms "VerzOmschrijving"
, pgl.fkn_ola_lbl_ftrrgl_hd1_veld2 "Veld2"
, pgl.fkn_ola_lbl_ftrrgl_hd1_veld5 "Veld5"
, pgl.fkn_ola_lbl_ftrrgl_hd1_veld6 "Veld6"
, pgl.fkn_ola_lbl_ftrrgl_hd1_veld10 "Veld10"
)
)
)
, xmlelement
( "Verzamel"
, xmlelement
( "VerzamelCategorie"
, xmlforest
( pgl.fkn_ola_lbl_ftrrgl_dl1_oms "VerzOmschrijving"
, ' ' "Veld2"
, to_char
( ftr.billing_start_date
, pgl.datum_formaat_dd_mm_yyyy
) "Veld5"
, to_char
( ftr.billing_stop_date
- 1
, pgl.datum_formaat_dd_mm_yyyy
) "Veld6"
)
)
)
, xmlelement
( "Verzamel"
, xmlelement
( "VerzamelCategorie"
, ( select xmlagg
( xmlforest
( pgl.fkn_ola_lbl_ftrrgl_dl3_oms "VerzOmschrijving"
, ctc.code "Veld2"
, to_char
( greatest
( trunc
( cte.start_date
)
, to_date('01-01-2009', 'dd-mm-yyyy')
)
, pgl.datum_formaat_dd_mm_yyyy
) "Veld5"
, to_char
( trunc
( cte.end_date
)
- 1
, pgl.datum_formaat_dd_mm_yyyy
) "Veld6"
)
)
from capacity_tariff_histories cte
, capacity_tariff_codes ctc
where cte.ctc_code = ctc.code
and cte.acn_seq = ftr.acn_seq
and trunc
( cte.start_date
) < ftr.billing_stop_date
and ( trunc
( cte.end_date
) is null
or cte.end_date > ftr.billing_start_date
)
group by cte.acn_seq
)
)
)
, xmlelement
( "Verzamel"
, xmlelement
( "VerzamelCategorie"
, xmlforest
( replace
( pgl.fkn_ola_lbl_ftrrgl_dl4_oms
, '[AANSLUITINGSTYPE]'
, ftr.aansluitingstype
) "VerzOmschrijving"
, ftr.ean_id "Veld2"
, ' ' "Veld5"
, ' ' "Veld6"
)
)
)
, xmlelement
( "Verzamel"
, xmlelement
( "VerzamelCategorie"
, xmlforest
( ' ' "VerzOmschrijving"
, ' ' "Bedrag3"
, ' ' "Bedrag4"
, ' ' "Bedrag5"
, ' ' "Bedrag6"
, ' ' "Bedrag7"
, ' ' "Bedrag8"
)
)
)
-- Produktregels
, xmlelement
( "Verzamel"
, xmlelement
( "VerzamelCategorie"
, xmlforest
( pgl.fkn_ola_lbl_pdtrgl_hd1_oms "VerzOmschrijving"
, pgl.fkn_ola_lbl_pdtrgl_hd1_bedrag2 "Bedrag2"
, pgl.fkn_ola_lbl_pdtrgl_hd1_bedrag3 "Bedrag3"
, pgl.fkn_ola_lbl_pdtrgl_hd1_bedrag4 "Bedrag4"
, pgl.fkn_ola_lbl_pdtrgl_hd1_bedrag5 "Bedrag5"
, pgl.fkn_ola_lbl_pdtrgl_hd1_bedrag6 "Bedrag6"
, pgl.fkn_ola_lbl_pdtrgl_hd1_bedrag7 "Bedrag7"
, pgl.fkn_ola_lbl_pdtrgl_hd1_bedrag8 "Bedrag8"
, pgl.fkn_ola_lbl_pdtrgl_hd1_veld10 "Veld10"
)
)
)
, xmlelement
( "Verzamel"
, xmlelement
( "VerzamelCategorie"
, xmlforest
( pgl.fkn_ola_lbl_pdtrgl_hd2_oms "VerzOmschrijving"
, pgl.fkn_ola_lbl_pdtrgl_hd2_bedrag2 "Bedrag2"
, pgl.fkn_ola_lbl_pdtrgl_hd2_bedrag3 "Bedrag3"
, pgl.fkn_ola_lbl_pdtrgl_hd2_bedrag4 "Bedrag4"
, pgl.fkn_ola_lbl_pdtrgl_hd2_bedrag5 "Bedrag5"
, pgl.fkn_ola_lbl_pdtrgl_hd2_bedrag6 "Bedrag6"
, pgl.fkn_ola_lbl_pdtrgl_hd2_bedrag7 "Bedrag7"
, pgl.fkn_ola_lbl_pdtrgl_hd2_bedrag8 "Bedrag8"
, pgl.fkn_ola_lbl_pdtrgl_hd2_veld10 "Veld10"
)
)
)
, xmlelement
( "Verzamel"
, ( select xmlagg
( xmlelement
( "VerzamelCategorie"
, ( xmlforest
( btr.name "VerzOmschrijving"
, to_char
( sum(btr.quantity)
, '9999990'
) "Bedrag2"
, btr.quantity_unit "Bedrag3"
, to_char
( sum(btr.calculated_price) / sum(btr.quantity)
, '9999990D9990'
) "Bedrag4"
, to_char
( sum(btr.calculated_price)
, '9999990D90'
) "Bedrag5"
, to_char
( btr.tax
, '990D90'
) "Bedrag6"
, to_char
( sum(btr.tax_amount)
, '9999990D90'
) "Bedrag7"
, to_char
( sum(btr.calculated_price)
+ sum(btr.tax_amount)
, '9999990D90'
) "Bedrag8"
)
)
)
)
from billed_contract_cost_items btr
where btr.bct_seq = ftr.seq
and btr.calculated_price <> 0
group by btr.bct_seq
, btr.name
, btr.quantity_unit
, btr.tax
)
)
-- Notabedrag
, xmlelement
( "TeBetTeOntv"
, xmlforest
( replace
( pgl.fkn_ola_txt_te_betalen
, '[TAX_AMOUNT]'
, to_char
( ftr.tax_amount
, 'fm9999990D90'
)
) "TxtTeBetTeOntv"
, replace
( pgl.fkn_ola_txt_bdg_met_btw
, '[PRICE+TAX_AMOUNT]'
, to_char
( ftr.price+ftr.tax_amount
, 'fm9999990D90'
)
) "BedragInclBTW"
)
)
, xmlelement
( "Betaalwijze"
, xmlelement
( "TxtBetaalwijze"
, replace
( pgl.fkn_ola_txt_betaalwijze
, '[DUE_DATE]'
, to_char
( ftr.due_date
, pgl.datum_formaat_dd_mm_yyyy
)
)
)
)
-- Ola
, xmlelement
( "Ola"
, xmlforest
( ftr.act1_dear "OlaFAdresNaam"
, ftr.aln1_address1 "OlaFAdresStraat"
, ftr.aln1_house_number "OlaFAdresHuisnr"
, ftr.aln1_postal_code_id "OlaFAdresPostcode"
, ftr.aln1_city "OlaFAdresPlaats"
)
, case
when ftr.ind_ander_factuuradres = 'J'
then
xmlforest
( ftr.address1 "OlaVAdresStraat"
, ftr.house_number "OlaVAdresHuisnr"
, ftr.postal_code_id "OlaVAdresPostcode"
, ftr.city "OlaVAdresPlaats"
)
end
, xmlforest
(ftr.act_ext_account_id "OlaDebiteurnr"
, '1111' "OlaBetKmrk0104"
, '1111' "OlaBetkmrk0508"
, '1111' "OlaBetkmrk0912"
, '1111' "OlaBetkmrk1316"
, pgl.ola_reknr_ocr "OlaReknrCogasOCR"
, '123456789012' "OlaBetalingskmrkOCR"
, pgl.ola_stortingkst_ocr "OlaCd_StortingkstOCR"
, (to_char( ftr.due_date, pgl.datum_formaat_dd_mm_yyyy)) "OlaBetaaltermijn"
, (to_char( ftr.price+ftr.tax_amount, '9999990D90')) "OlaBedrag"
, '12345' "OlaBedragOCR"
)
)
-- Printgegevens
, xmlforest
( 'J' "IndicatiePDF"
, 'J' "BulkrunJaNee"
, 'oce9260' "PrinterNaam"
, ftr.omr_regel "OMR"
)
)
)
)
, version max(pgl.fkn_ola_xml_version)
) xmldocument
from ftr_kvn_ola_prt_vw ftr
, pgl pgl
where ftr.frn_id = nvl
( b_facturatierun_id
, ftr.frn_id
)
and ftr.seq = nvl
( b_factuur_id
, ftr.seq
)
group by ftr.frn_id
;