11 Replies Latest reply: Aug 22, 2012 12:36 PM by 946141 RSS

    DBMS_XMLGEN - Performance issue

    946141
      Hi,
      We are generating a report in 11g using DBMS_XMLGEN. The query has many cursors, using lot of tables , packages and views. I tried passing a string and also ref cursor to DBMS_XMLGEN. In both cases the performance is slow. The same query that runs in SQL for 600 msecs is taking 16 secs when I genarte XML from PL/SQL. The query is too big to post here. Help is really appreciated.
      Thanks in advance.
        • 1. Re: DBMS_XMLGEN - Performance issue
          Marco Gralike
          I know its probably not the solution you want to here, but let go of DBMS_XMLGEN and use the methods as shown here: Re: How to generate xml file from database table
          • 2. Re: DBMS_XMLGEN - Performance issue
            damorgan
            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.
            • 3. Re: DBMS_XMLGEN - Performance issue
              946141
              Hi ,
              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.

              SELECT
              CURSOR
              (SELECT A_NUM,
              ADDRESS_CODE,
              ADDRESS_TYPE,
              STREET1,
              STREET2,
              STREET3,
              CITY ,
              STATE_NAME,
              ZIP_CODE,
              COUNTRY_NAME,
              PHONE
              FROM VW_CR_ADDRESS VCA
              WHERE A_NUM = 2)
              AND ADDRESS_CODE IN (1,2,3)) AS CORP_ADDRESS,
              CURSOR
              (SELECT Sections(
              STD_REPORT_SECTION_ID,
              STD_REPORT_SECTION_NAME,
              PARENT_AMB_REPORT_SECTION_ID,
              CAST(MULTISET(
              SELECT
              SEQ_NUM,
              STATIC_TEXT_ID,
              TEXT,
              IS_BOLD,
              IS_ITALICS,
              IS_UNDERLINE,
              IS_PARAGRAPH,
              URL,
              URL_CAPTION,
              IMAGE_PATH
              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,
              CURSOR
              (SELECT YEAR,
              NET_PREM_WRITN,
              NET_PREM_WRITN_CHG,
              NET_PREM_EARNED,
              NET_PREM_EARNED_CHG
              FROM VW_CR_PRE_COMP_GROWTH_ANALYSIS
              WHERE AMB_NUM = 2283) AS GROWTH_ANALYSIS2,
              CURSOR
              (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,
              CURSOR
              (SELECT FIELD_DESCR,
              PAID_UNPAID_LOSSES,
              IBNR,
              UNEARNED_PREMIUMS,
              OTHER_RECOV,
              TOTAL_REINS_RECOV,
              DECODE(FIELD_DESCR,
              'US Affiliates','A',
              'Foreign Affiliates','B',
              'US Insurers','C',
              'Pools/Associations','D',
              'Other Non-Us','E',
              '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,
              CURSOR
              (SELECT YEAR,
              COMP_CLASS_3_6_BONDS,
              COMP_REAL_ESTATE_MTG,
              COMP_OTHER_INVESTED_ASSETS,
              COMP_COMMON_STOCKS,
              COMP_NON_AFFL_INV_LEV,
              COMP_AFFIL_INV
              FROM VW_CR_COMP_INV_LEV_ANALYS -- View uses MODEL clause
              WHERE A_NUM = 2)
              ORDER BY YEAR DESC) AS INV_LEV_ANALYSIS,
              CURSOR
              (SELECT A.YEAR,
              COMP_QUICK_LIQ_PERS,
              COMP_CURR_LIQ_PERS,
              COMP_OVERALL_LIQ_PERS,
              GROSS_AGENTS_BAL_TO_PHS,
              IND_COMP_QUICK_LIQ_PERS,
              IND_COMP_CURR_LIQ_PERS,
              IND_COMP_OVERALL_LIQ_PERS,
              IND_GROSS_AGENTS_BAL_TO_PHS
              FROM
              (SELECT YEAR,
              COMP_QUICK_LIQ_PERS,
              COMP_CURR_LIQ_PERS,
              COMP_OVERALL_LIQ_PERS,
              GROSS_AGENTS_BAL_TO_PHS
              FROM VW_CR_COMP_LIQUIDITY_ANALYSIS
              WHERE A_NUM = 2)) A,
              (SELECT YEAR,
              IND_COMP_QUICK_LIQ_PERS,
              IND_COMP_CURR_LIQ_PERS,
              IND_COMP_OVERALL_LIQ_PERS,
              IND_GROSS_AGENTS_BAL_TO_PHS
              FROM VW_CR_IND_COMP_LIQUID_ANALYSIS
              WHERE A_NUM = 2) B
              WHERE A.YEAR = B.YEAR
              ORDER BY A.YEAR DESC) AS LIQUIDITY_ANALYSIS
              FROM DUAL;
              • 4. Re: DBMS_XMLGEN - Performance issue
                Marco Gralike
                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)
                • 5. Re: DBMS_XMLGEN - Performance issue
                  damorgan
                  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.
                  • 6. Re: DBMS_XMLGEN - Performance issue
                    946141
                    Hi ,
                    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.
                    • 7. Re: DBMS_XMLGEN - Performance issue
                      odie_63
                      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.
                      • 8. Re: DBMS_XMLGEN - Performance issue
                        odie_63
                        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.
                        • 9. Re: DBMS_XMLGEN - Performance issue
                          946141
                          Hi,
                          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 11.1.0.7.0 - 64bit Production
                          PL/SQL Release 11.1.0.7.0 - Production
                          "CORE     11.1.0.7.0     Production"
                          TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
                          NLSRTL Version 11.1.0.7.0 - Production
                          • 10. Re: DBMS_XMLGEN - Performance issue
                            Marco Gralike
                            We are generating a report in 11g using DBMS_XMLGEN
                            I 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.
                            • 11. Re: DBMS_XMLGEN - Performance issue
                              946141
                              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.