21 rules for faster SQL queries

Pro Tips listed in the below web page gives good insights and changes the view of looking at code while building a optimized applications on rdbms databases.

Follow these tried-and-true techniques to improve the speed and scalability of your relational database

Infoworld.com - 21 rules for faster SQL queries

Amount Released calculation in the PO Headers/Summary Screen

 Steps followed to identify the calculation

 

Enable the log profiles at user level.

FND: Debug Log Enabled: yes

FND: Debug Log Level: Statement

 

Run the below query and take the maximum seq number from the debug log table. This table contains huge data and we only want the logs created after this seq number.

select max(log_sequence) from fnd_log_messages

Assume, seq number is 1001

navigate to the Purchase order screen, query for the purchase order document, then take the seq number again.

select max(log_sequence) from fnd_log_messages

Assume, seq number is 1010

Now query the log between these two numbers

select * from fnd_log_messages where log_sequence between 1001 and  1010

 

Concentrate on columns module, message_text to analyze log data.

The API that does amount_released calculation is PO_CORE_S.get_gc_amount_released

 

Excel template XML Publisher report – data exceeding more than 65k rows

There was a requirement from client to build a report using xml publisher 5.6.3 for getting Purchase order commitments. The intention is to use excel template to achieve the table formatting such as auto expansion of height and width for a cel, and adding headers and company logos are achieved in excel template using Alt-text option. During the time when report was built and tested the total row count in the outcome of the report was less than 50,000 records, we never had a thought of going with csv file because excel is feasible with presenting data in required fashion and total row count was within the limits of older version of MS excel (.xls template excel 2003). XML Publisher 5.6.3 which is a embedded version in e-business suite R12.1.3 only supports excel 2003 version templates to run reports. After an year, transaction data surged in the system only to affect the report to end in warning status when ran and output is not viewable as .xls file only allows 65k rows in a sheet.

As an interim solution to provide report to user, I had to run the script manually in toad and extract to excel (latest version). An oracle SR was raised for the issue, they only said they stopped to support for this version of bi publisher, and no patches are available for this issue. Although rebuilding the report with csv would fix the issue but user will lose the presentable way of viewing data, i then built a XSL template to overcome this product limitation. With XSL template, we will be able to show all the data with perfect formatting standards as user needs, this solution comes with an additional step to perform to get the concurrent request output file.

When the report is submitted, user needs to give the contacts of recipients who want to receive output file. After the request is completed normal it sends an notification to user with a link to open output file, they then have to right click on the link and click save as, it saves the file to their local machine which then has to be opened with Microsoft excel. This is required because when user clicks on View Output button in the SRS window, it opens an xsl gobbledegook in an internet explorer. This way it doesn’t matter what the record count is, they will get it as they need.

There are options to convert your excel template to xsl template online.

Thanks,

Nithin

XML Publisher v 5.6.3 issue with excel based template bursting

Introduction to Problem Statements:

Bursting with excel template is not allowed in xml publisher 5.6.3.

Proposed Solution:

This can be overcome by going little deep in to the standard tables.
When building the xml publisher report with excel template. You need to be cautious while writing bursting control file, particularly in the syntax where system understands from where the template needs to be picked up.
An issue was identified with bursting when used excel based template (.xls) in xml publisher (bi publisher) 5.6.3 version.

 Error shown in log is

--Exception ZIP file must have at least one entry java.util.zip.ZipException: ZIP file must have at least one entry at java.util.zip.ZipOutputStream.finish(ZipOutputStream.java:304) at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.zipOutputFiles(JCP4XDOBurstingEngine.java:534) at oracle.apps.xdo.oa.cp.JCP4XDOBurstingEngine.runProgram(JCP4XDOBurstingEngine.java:292) at oracle.apps.fnd.cp.request.Run.main(Run.java:157)

After troubleshooting, it is identified that the issue is in the below statement.

<xapi:template type="excel" location="blob://xdo_lobs.file_data/VARCHAR2:LOB_TYPE=TEMPLATE_SOURCE/VARCHAR2:APPLICATION_SHORT_NAME=ENEAP/VARCHAR2:LOB_CODE=ENEAPDRINV/VARCHAR2:LANGUAGE=en/VARCHAR2:TERRITORY=US"/>

This statement informs system to check in the XDO_TEMPLATES_TL table with template type "TEMPLATE_SOURCE". We only find rtf templates with this template type, so we get the above error, all excel based templates are stored with template type "TEMPLATE".
You can change the LOB_TYPE = TEMPLATE instead of TEMPLATE_SOURCE in the above template tag, else use the below statement which works perfectly fine.
<xapi:template type="excel" location="xdo://ENEAP.ENEAPDRINV.en.US/?getSource=true"/>

useful query for this situation

SELECT    lt.application_short_name

       || '.'

       || lt.template_code

       || '.'

       || lt.default_language

       || '.'

       || lt.default_territory

          AS burst_template_xdo_location

  FROM apps.xdo_templates_b lt

 WHERE lt.template_code = 'ENEQMSQLTYFNDASSTYPE' -- where 'ENEQMSQLTYFNDASSTYPE' is a data definition code.

References:

• Bursting with excel template is given as a limitation in Metalink note (1351502.1).

Thanks,
Nithin