This content has been marked as final. Show 11 replies
Using an unknown version of the Oracle database against tables with an unknown design you have implemented something utilizing DBMS_XMLGEN that is not performing well.
I'd really like to help you but you've not provided sufficient information from which to formulate a recommendation as to either improving your use of DBMS_XMLGEN or utilizing a different solution.
Phrases like "many cursors" immediately lead me to expecting the worst ... but that is only an instinct as I've not seen a single line of code.
The query is really big , so we couldn't give the entire thing. As I mentioned earlier the Oracle version is 11g.
I am posting here only a part of the SQL. We have atleast 20 Pipelined functions, 40 cursors, views that has model clause. Our concern is the whole query is taking only 600 msecs if we run it without the XMLGEN. But generating XML is taking more than 20 secs. I am not sure if the XMLGEN performance itself is slower. Does XMLGEN slow down the peformance and do we have any alternative for it? We are little reluctant to use XMLelement as the query gets too cumbersome.
We really appreciate your help.
FROM VW_CR_ADDRESS VCA
WHERE A_NUM = 2)
AND ADDRESS_CODE IN (1,2,3)) AS CORP_ADDRESS,
FROM CR_TMP_ELEMENTS e
WHERE e.section_id = CSRS.STD_REPORT_SECTION_ID )
AS ELEMENT_LIST)) AS SECTIONS
FROM CR_STD_REPORT_SECTION CSRS, CR_RPT_AMB_SECTION CRAS
WHERE CSRS.STD_REPORT_SECTION_ID =
CRAS.AMB_REPORT_SECTION_ID(+)) AS AMBReportSectionsTextData,
WHERE AMB_NUM = 2283) AS GROWTH_ANALYSIS2,
(SELECT '5-Yr CAGR' AS CAGR_DESC,
MAX(DECODE(ID_CODE,'1',VALUE,NULL)) AS N1_CHG,
MAX(DECODE(ID_CODE,'2',VALUE,NULL)) AS P_CHG,
'5-Yr Change' AS CHANGE_DESCR,
MAX(DECODE(ID_CODE,'3',VALUE,NULL)) AS N2_CHG,
MAX(DECODE(ID_CODE,'4',VALUE,NULL)) AS N3_CHG
FROM VW_CR_FINANCIAL_DATA_YEARLY_MV -- Materialized veiw
WHERE A_NUM = 2)
AND YEAR = 2010
AND ID_CODE IN ('1','2','3','4')) AS CHG2,
'Total(ex Us Affils)','F',
'Grand Total','G', NULL) AS DISPLAY_ORDER
FROM TABLE(PKG_CR_FINANCIAL_DATA.PR_CR_REINS_RECOVER(2283)) -- Pipelined function
ORDER BY DISPLAY_ORDER ) AS REINS_RECOVERABLES,
FROM VW_CR_COMP_INV_LEV_ANALYS -- View uses MODEL clause
WHERE A_NUM = 2)
ORDER BY YEAR DESC) AS INV_LEV_ANALYSIS,
WHERE A_NUM = 2)) A,
WHERE A_NUM = 2) B
WHERE A.YEAR = B.YEAR
ORDER BY A.YEAR DESC) AS LIQUIDITY_ANALYSIS
We are little reluctant to use XMLelement as the query gets too cumbersome.LOL, believe me its not. Especially if this bit is only "a part" of it. If you see my formatting with xmlelement etc, and your formatting, do you see a big difference. If you won't believe it is faster than DBMS_XMLGEN than just create a small test case and actually compare (+ explain plan output)
Still no version number ... no help is possible.
Neither can I tell what you are doing with these cursors ... a BULK COLLECT for some kind of archaic cursor loop?
When people ask you for information it is because it is REQUIRED. If you do not provide it then what are you expecting us to respond with other than wild guesses likely of little or no value to you.
I tried XMLelement and it really improved the performance. Thanks a lot for forcing me to use it :) . Didn't check with the entire query but that is going to take some time. Just a quick question. Heard some where that DBMS_XMLGEN sometimes doesn't use indexes. Is it true? Thank you so much for your help. Will let you know after I finish the entire query.
Heard some where that DBMS_XMLGEN sometimes doesn't use indexes. Is it true?No, it's not true, and that assertion doesn't make sense either.
Internally, DBMS_XMLGEN builds a cursor to query the data.
That cursor is then processed by the database just like any other cursor.
If the CBO doesn't use indexes to resolve a particular query, it's not the fact of the API that built the corresponding cursor.
I tried XMLelement and it really improved the performance. Thanks a lot for forcing me to use it :) . Didn't check with the entire query but that is going to take some time. Just a quick question.The other advantage is that you can build the final XML document using several logical pieces (hence different independent queries), something you couldn't do with DBMS_XMLGEN since you had to use a single input query.
I thought I gave the version as 11g. Here are the version details. Didn't use bulk collect. Sorry I am not able to give the table structure. Was trying to find if there are any known issues with DBMS_XMLGEN used on pipelined functions, model clause etc. Thank you for your time.
Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit Production
PL/SQL Release 126.96.36.199.0 - Production
"CORE 188.8.131.52.0 Production"
TNS for 64-bit Windows: Version 184.108.40.206.0 - Production
NLSRTL Version 220.127.116.11.0 - Production
We are generating a report in 11g using DBMS_XMLGENI didn't ask for it. :-0 The remark "11g" was in this context enough info when dealing with general behavior of DBMS_XMLGEN questions.
Adding to Odie's coding remark, DBMS_XMLGEN is often used passing the SQL via a string. Due to this nature Oracle can not check the validity of this statement, that is, if tables, columns etc exist and are of the right format and or syntax issues. You will get in such a case a standard error message which is very difficult (at least can be) to debug if you're not aware of the embedded problem.
You are so right. I had my share of those cryptic messages with XMLGEN :). Thank you for your help. As Odie mentioned, It might also help us in dividing the program into modules and lead to better maintenance. I really appreciate your time.