Noetix: Improving Performance of the GMF_SLA_Cost_Subledger Template at R12

Looking at the source code behind this view it quickly becomes apparent that, with nine UNIONS each of them with a full GROUP BY, unless you have a very finely tuned database you are going to get performance problems when accessing this view. This is especially true if you are querying it using something like the Production_Batch_Number (only populated by Query position 9).

Probably the quickest (and easiest) way to improve performance is to cut out any of the UNIONS that you actually aren't using. The easiest way to identify these is to edit the source code of the view and;

• Add a column called "block_number" to each block with the number of that block,
• Remove the GROUP BY clauses (this will just improve performance for what we are about to do),
• Wrap the modified view SQL with the query "SELECT DISTINCT block_number FROM ( ... )"
• Execute the query, and note the time it takes

On our system (with about 12 years of GL data) the query took 5:29 (5 and half minutes) and returned the following results;

Result of SQL

As you can see query blocks 3 and 5 are contributing zero records to the result and, as we're looking at every record that query block returns, we can safely remove them (of course, if at a later date we change the way our e-Business Suite is configured we might need to look back at this but then before we'd do that we'd fully test reporting wouldn't we?!).

So now we have two query blocks 3,5 and that we think we can remove.

As a sanity check I commented out blocks 3 and 5 in the SQL we had modified with the block numbers and re-ran it to see if we got the same results (and performance improved). Sure enough the results were the same and the query returned in 1:26 (down from 5:29) - of course some of this will be due to the intelligent optimiser having kept results of the previous run but still it's an encouraging sign.

The next step is to identify the Noetix query blocks that we can "comment out", to do this we need to look in the N_View_Query_Templates table and see how the view is currently configured;

select *
  from n_view_query_templates n
 where n.view_label = 'GMF_SLA_Cost_Subledger'

If you look at the VIEW_COMMENT column there is some useful information (from Noetix) regarding what each union is used for. Looking at blocks 3 and 5 the comments are RMA Transactions, and Drop Shipment Transactions - which makes sense as we use neither.

Now to remove the queries from the view we need to create a simple script we can call from WNOETXU2.sql such as;

@utlspon gmf_sla_cost_subledger_xu2
 
UPDATE N_View_Query_Templates N
   SET N.product_version  = '9',
       N.last_updated_by  = 'A Pellew',
       N.last_update_date = TO_DATE('28-FEB-2012')
 WHERE 1 = 1
   AND N.view_label = 'GMF_SLA_Cost_Subledger'
   AND N.query_position IN (3, 5)
;
 
COMMIT;
 
@utlspoff

Maybe future versions of Noetix will do this sort of performance turning automatically.