Performance Tuning Oracle Reports and Programs

Here are some of the performance tuning tips and tricks that I have learned over time. Most of them will work some of the time and  some of them will work most of the time.

People often ask me - Have you tried using temporary table for Oracle reports which has performance issue or have I analysed trace file of reports. But the truth is that most of the time analysing current query in toad proves enough to find bottleneck of the program. I submit the Oracle report, and in toad window I monitor corresponding process to check for running SQL query. I watch the queries which are executing and the plan which is being used for the query. Queries which test your patience will be the queries which needs your attention. I start from these SQL statements and I try to Optimize them.

1) Do not use truncate function on database Date column instead use timestamp or explicit expression for date comparisons.

problems with date comparisons

Like  transaction_date of rcv_transactions is an indexed column, so if we use truncate on transaction_date, index will not be used.

Instead of trunc(transaction_date) = '12-JUL-2013' use transaction_date between to_date('12-JUL-2013 00:00:00','DD-MON-YYYY HH24:MI:SS') and to_date('12-JUL-2013 23:59:59','DD-MON-YYYY HH24:MI:SS')
similarly for creation_date of rcv_shipment_headers

2) Use same data type as index column type in expressions

If indexed column is number and expression uses string, index is used
if indexed column is string and number is used in expression , index is skipped

3) Use lexical parameters to build query, this will improve performance most of the time.

4) Using Index is not always good. People would often say index will lead to performance improvement. But using index of on large table instead of performing full table scan of small table may some time prove otherwise.
Modify query to use  full table scan of small table instead of using index on big table and check run time.

If temporary table is being used in report and some table join is used with large tables, do not use large table indexes instead force full table scan of temporary table.

5) When checking for existing values, like if  purchase order exists for a vendor
Use rownum in query. Instead of

select count(*) from po_headers_all where vendor_id = <>

use select 1 from po_headers_all where vendor_id = <> and rownum <2

6) Mention all column joins explicitly in SQL query.

7) Less cost doesn't always ensures improved performance

parent_transaction_id = -1 on rcv_transaction shows very less cost but takes lot of time similarly, queries with rownum <2 will show very less cost.

8) For excel reports, if post processing time is more, then generating delimited file using PL SQL package will improve performance.
To check for how much time  a program has spent in post processing,  check  fnd_concurrent_requests.PP_START_DATE column

9) Remember if report is run with trace enabled, queries will always perform hard bind.

We had a strange issue wherein after enabling trace, the program was completing faster. We could not find out the reason as to why this is happening, but I think it was related to hard bind performed each time.
The report was using bind variable and there are some known problem of bind variable peeking.

11) Using sub query in FROM clause is expensive Instead try using sub query in select clause with where condition if you want to select only few values in main select clause.

12) Do not user formula column which are header dependent at line level. header level details should be fetched only once.

13) Distinct clause is little tricky. Because even if query is wrong sometimes, it conceals the problem and presents correct output. If you have missed some join in a query with distinct clause, then it might give correct output, but performance is highly impacted.

I worked on one performance tuning of one report recently. It was taking huge time for one particular Org. I identified the query which was taking long time. When I analyzed the query I found that instead of using org dependent view, query was using _all table with no where condition for org_id. The output of report was coming as expected but in background the query was killing performance.