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.

Noetix: Expanding Basic Forecasting Functionality in R12

This blog post contains details of how to expand the basic Noetix Forecasting template (INV_Forecasts) to add in some of the nice new functionality in R12 such as grouping forecasts into Sets (i.e. all export forecasts could be in a set called EXPORTS and domestic forecasts one called DOMESTIC).

Looking at the existing Noetix view it accesses the Forecast data in a single table (MRP.MRP_FORECAST_DATES). The MRP Forecasting schema contains the additional tables MRP_Forecast_Designators (description and set information), MRP_Forecast_Items, MRP_Forecast_Updates, etc.

This change will add in the set and forecast description from the MRP_Forecast_Designators table. Looking at the possible joins between the two the easiest is;

This simply joins the two tables on the Forecast_Designator and Organziation_Id  if they exist in the designators table.

Looking at the INV_Forecasts queries that make up this view using the SQL;

select n.view_label || ', ' || 

       to_char(n.query_position) || ', ' ||
       nvl(n.union_minus_intersection, 'null') || ', ' || 

       n.view_comment "label,position,umi,comment"
  from n_view_query_templates n
 where n.view_label = 'INV_Forecasts'

Shows the following results;


label,position,umi,comment
INV_Forecasts, 1, null, forecast with day buckets
INV_Forecasts, 2, UNION ALL, forecast with week buckets
INV_Forecasts, 3, UNION ALL, forecast with period buckets

In order to get the view working we need to insert the new tables and columns into each of the queries (in effect doing the same thing three times).

Using the XU2 column addition generation script I have blogged about previously with the values INV_Forecasts, Forecast_Designator, and Y (include tables) gives you a script that can be quickly and easily edited.

As I'm sure you're aware you need to add in the table prior to adding in the columns and that each of the column addition scripts needs to be in a separate file. Because of this I'm going to add the table in the script which adds the Forecast_Description column.

The changes required to add the new table (and columns) to the view are;

In the SELECT;

• MFD.Description Forecast_Description,
• MFD.Disable_Date Forecast_Disable_Date
• MFD.FORECAST_SET Forecast_Set

In the FROM clause;

• MRP.Mrp_Forecast_Designators MFD

And finally in the WHERE clause;

• AND FODAT.Forecast_Designator = MFD.Forecast_Designator(+)
• AND FODAT.Organization_Id = MFD.Organization_Id(+) 

The three files are (all files stored in Google Docs);

inv_forecasts_forecast_description_xu2.sql

inv_forecasts_disable_date_xu2.sql
inv_forecasts_forecast_set_xu2.sql

Whilst we're here I'm going to add another two files to show who created the Forecast and when they did it (which I tend to find always useful - especially when identifying people to test changes!).

inv_forecasts_created_by_xu2.sql
inv_forecasts_creation_date_xu2.sql

You'll notice if you look in the "created_by" file that I'm using the AUTOJOIN column type in Noetix to quickly get this information. It can be a quick way of getting one field from another table but it does create problems if, for example, I wanted two fields (like the name and email address) and can introduce interdependencies between files that might cause problems if Noetix ever changes the way it works.

The next three changes I'm going to suggest are  replacing the existing Forecast_Quantity column with the Current and Original Forecast Quantities from the forecast table. It is, frankly, rubbish to try and hide these columns and I feel represents a complete lack of understanding on how companies use forecasting; "How close are we to the forecast?" is a frequent question and so hiding the columns just makes absolutely no logical sense. To make this change requires three files;

inv_forecasts_current_forecast_quantity_xu2
inv_forecasts_original_forecast_quantity_xu2
inv_forecasts_forecast_quantity_upd_xu2

Once you've downloaded these files and added them into your install directory you need to add a call to them in the xu2 file (wnoetxu2.sql) and then you can run a regenerate and do some additional testing. Remember that the forecast description needs to be added first as it adds the table to the view (required by the other two) for example;

@inv_forecasts_created_by_xu2.sql
@inv_forecasts_creation_date_xu2.sql
@inv_forecasts_current_forecast_quantity_xu2.sql
@inv_forecasts_forecast_description_xu2.sql -- Added MRP.MRP_FORECAST_DESIGNATORS(MFD)
@inv_forecasts_forecast_quantity_upd_xu2.sql
@inv_forecasts_disable_date_xu2.sql -- Requires MFD
@inv_forecasts_forecast_set_xu2.sql -- requires MFD
@inv_forecasts_original_forecast_quantity_xu2.sql

Hope this helps! Any questions leave a comment ...

Noetix: Extracting a View As A Script

UPDATE 16-DEC-2012: Added in the table n_view_col_property_templates. Where a view is using key flex fields the copying of the view was failing as the additional information (well, for Inventory anyway) was not being populated in this table.

The following script generates source code (i.e. uses DBMS_OUTPUT.PUT_LINE), you will need to copy/paste this into a separate file AND then change the name of the view before you run it (otherwise you will get a lot of duplicate errors).

Five fields are overwritten by this script; Last Updated By/Created By (replaced with the value in the variable at the top), Last Updated Date/Creation Date (set to today), and the product version set to 12+.

To add other tables you can just add calls to "ProcessTable" (rows 91-96) for the additional tables you want to include.

The script is available here (via Google Docs) or is copy/ pasted below;

declare
  v_ViewLabel     n_view_column_templates.view_label%TYPE := 'GMD_Recipes'; -- Must be direct value from table n_views, case sensitive!
  v_LastUpdatedBy n_view_templates.last_updated_by%TYPE := 'A Pellew'; -- The user who performed the last update (i.e. you)

  procedure ProcessTable(v_TableName in varchar2) as
    TYPE rowidRec IS RECORD(
      ri rowid);
    TYPE rowidSet IS TABLE OF rowidRec;
    v_Items rowidSet;

    v_HeaderSQL varchar2(4000);
    v_DataSQL   varchar2(4000);
    v_SQL       varchar2(4000);
    v_result    varchar2(4000);
    procedure AddToHeader(v_Text in varchar) as
    begin
      if length(v_HeaderSQL) > 100 then
        dbms_output.put_line(v_headerSQL);
        v_headerSQL := '  ';
      end if;
      v_headerSQL := v_headerSQL || v_Text;
    end;
  begin
    dbms_output.put_line('-- Performing updates to table ' ||
                         upper(v_tablename));
    EXECUTE IMMEDIATE 'SELECT n.rowid FROM ' || v_TableName ||
                      ' n WHERE n.view_label = ''' || v_ViewLabel || '''' BULK
                      COLLECT
      INTO v_Items;
    for v_Item in v_Items.First .. v_items.Last loop
      v_HeaderSQL := '  ';
      v_DataSQL   := '';
      dbms_output.put_line('INSERT INTO ' || Lower(v_TableName) || ' (');
      for v_Column in (select atc.column_name,
                              atc.data_type,
                              atc.column_id,
                              (select max(atc2.column_id)
                                 from all_tab_columns atc2
                                where atc2.owner = USER
                                  AND atc2.column_name not in
                                      ('INCLUDE_FLAG')
                                  and atc2.table_name = atc.table_name) as max_column_id
                         from all_tab_columns atc
                        where atc.owner = USER
                          and atc.table_name = Upper(v_TableName)
                          AND atc.column_name not in ('INCLUDE_FLAG')
                        order by atc.column_id) loop
        v_SQL := 'SELECT T.' || V_Column.Column_name || ' FROM ' ||
                 Upper(v_TableName) || ' T WHERE T.ROWID = ''' || v_Items(v_Item).ri || '''';
        EXECUTE IMMEDIATE v_SQL
          into v_result;
        if instr(v_Result, '''') > 0 then
          v_Result := Replace(v_result, '''', '''''');
        end if;
        if v_Column.Column_Name in ('LAST_UPDATE_DATE', 'CREATION_DATE') then
          v_result := SYSDATE;
        end if;
        if v_Column.Column_Name IN ('LAST_UPDATED_BY', 'CREATED_BY') then
          v_result := v_LastUpdatedBy;
        end if;
        if v_Column.Column_Name IN ('T_COLUMN_PROPERTY_ID') then
          v_result := '(SELECT MAX(t_column_property_id)+1 FROM n_view_col_property_templates)';
        end if;
    
        if (v_result is not null) then
          if (v_Column.Column_Id = v_Column.Max_Column_Id) then
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''') -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ') -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || ''')) -- ' || lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end;
            AddToheader(lower(V_Column.Column_name) || ')');
          else
            v_DataSQL := v_DataSQL || case
                           when v_Column.Data_Type = 'VARCHAR2' then
                            '  ''' || v_result || ''', -- ' ||
                            lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'NUMBER' then
                            '  ' || v_result || ', -- ' || lower(V_Column.Column_name)
                           when v_Column.Data_Type = 'DATE' then
                            '  TO_DATE(''' || v_result || '''), -- ' ||
                            lower(V_Column.Column_name)
                           else
                            '** ERROR **' || v_Result
                         end;
            AddToHeader(lower(V_Column.Column_name) || ', ');
            v_DataSQL := v_DataSQL || chr(13);
          end if;
        end if;
      end loop;
      dbms_output.put_line(v_headerSQL);
      dbms_output.put_line('VALUES(');
      dbms_output.put_line(v_DataSQL);
      dbms_output.put_line(';');
      dbms_output.put_line('');
    end loop;
  end;
begin
  dbms_output.put_line('@utlspon ' || lower(v_ViewLabel) || '_xu2');
  dbms_output.put_line('');
  dbms_output.put_line('SET SCAN OFF');
  dbms_output.put_line('');
  ProcessTable('n_view_templates');
  ProcessTable('n_role_view_templates');
  ProcessTable('n_view_query_templates');
  ProcessTable('n_view_table_templates');
  ProcessTable('n_view_where_templates');
  ProcessTable('n_view_column_templates');
  ProcessTable('n_view_col_property_templates');

  dbms_output.put_line('COMMIT;');  dbms_output.put_line('');  dbms_output.put_line('SET SCAN ON');  dbms_output.put_line('');
  dbms_output.put_line('@utlspoff');
end;