Oracle EBS 华为MetaERP销售订单OM Advanced Pricing Performance What Scripts do I Need To Provide

Oracle EBS 华为MetaERP销售订单OM Advanced Pricing Performance What Scripts do I Need To Provide 

In this Document
  Purpose
  Scope and Application
  Diagnosing Advanced Pricing Performance What Scripts do I Need To Provide
     List of traces, logs and files that we will need:
     Profile options that affect performance:
     Action Item Requiring Output:
     A1. Diagnostics: Apps Check
     A2. Run QP: Maintains the denormalized data in QP Qualifiers
     A3. Run Build Attribute Mapping Rules 
     A4. Run Attribute Mapping Rules Error Report 
     A5. Run Diagnostics: Performance Analysis
     A6. Please provide the raw trace with Binds and Waits. 
     A7. Provide TKPROFs for the above raw trace.
     A8. TKPROF that limits the results to the top ten queries(Optional):
     A9. What is the time spent in the pricing engine during this process? 
     A10. How to generate an OM Debug File For Performance Analysis 
     A11. Please provide the HTMOMSE.SQL report 
     A.12 Run Script qp_list_line_detail.sql 
     A.13 Execute this script and Provide the output 
     A.14 Create a TraceAnalyzer File (Optional) 
     A.15 Execute Script bde_chk_cbo.sql 
     A.16 Execute Script bde_last_analyzed.sql
     
     QUESTIONS REQUIRING RESPONSE 
  References

——————————————————————————–

Applies to: 
Oracle Advanced Pricing – Version: 11.5.9 to 12.1.3 – Release: 11.5 to 12.1
Oracle Advanced Pricing – Version: 11.5.9 to 12.1.3   [Release: 11.5 to 12.1]
Information in this document applies to any platform.
EXECUTABLE:QPXDENOB – QP: Maintains the denormalized data in QP Qualifiers
EXECUTABLE:QPXPERFO – Performance Analysis script
EXECUTABLE:QPXPSRCB – Build Attribute Mapping Rules
EXECUTABLE:QPXPSRCB2 – Attribute Mapping Rules Error Report
FORM:QPXPRMLS.FMB – Modifier Details
FORM:OEXOEORD.FMB – Sales Orders
FORM:QPXPRMLS.FMB – Modifier Details
FORM:QPXPRQFS.FMB – Qualifier Grouping
FORM:OEXOEORD.FMB – Sales Orders
FORM:QPXCONAT.FMB – Context and Segments
FORM:QPXPRDDI.FMB – Define Discounts 
Purpose
This document is intended to provide a complete list of items that will be required for development to accept and work on a performance issue in Oracle Pricing or Advanced Pricing application. This is specific to Order Management modules (QP, OM, WSH), with a focus on the Pricing Engine performance. 

Scope and Application
This note is intended for all analysts who will be researching and troubleshooting performance problems in Order Management R11i applications (OM, SE, QP), that is related Pricing Engine calls.

Diagnosing Advanced Pricing Performance What Scripts do I Need To Provide
Create or select the sample order: 
============================== 
We will want to start by creating a benchmarking order. This order should be the reference orders that we continue to use thought-out his process and the order size should be about 50 to 100 lines. The Order should be created in the instance that has the performance problem or an exact or near exact copy thereof. 

Collecting the Trace Files: 
============================== 
To get time taken by pricing engine, set profile option “QP: Debug” to “Request Viewer Off, show Diagnostic details in Trace” and generate database trace file. This profile setting will not generate trace file automatically. User will need to enable trace manually. All other debug related profile options like OM Debug, fnd debug etc. and PL/SQL profiler must be OFF while generating trace. While generating trace file, it should not be first call to pricing after starting new session. First call generally takes longer time as db packages need to be uploaded in memory. 

Collecting the Debug Files: 
============================== 
The Debug file and Trace file should be provided for same test case but run at two different times. While generating Debug file, it should not be first call to pricing after starting new session. First call generally takes longer time as db packages need to be uploaded in memory .Refer to How to set the new Time Log utility to troubleshoot QP performance issues Document 758241.1

List of traces, logs and files that we will need:

1. Raw Trace with binds and Waits. 
2. TKPROF of Raw Trace sorted prsela,exeela,fchela 
3. TKPROF of Raw Trace sorted prsela,exeela,fchela Print=10 – (Optional)
4. While tracing, OM Debug Level = 0 or NULL (Debug must be OFF) 
                         QP: Debug = Request Viewer Off,show Diagnostic details in Trace
5. qp_list_line_detail.sql 
6. qpperf.sql 
7. HTMomse11i.sql 
8. omcheck.sql 
9. QP Maintain Denormalized Data in QP Qualifiers Log 
10. Output to script BDE_CHK_CBO.SQL 
11. Output to script bde_last_analyzed.sql 

Profile options that affect performance:

Set QP:Blind Discount Option to NO and add All Items as product, if possible 
   Reference Note 435422.1 – How to Identify Blind Modifiers and Make Changes 
   to Setup for Improving Performance
Set QP:Builld Attributes mapping options to Map attributes used in active pricing setup 
Set QP:Satisfied qualifiers option to NO unless the calling application needs this info.
Set OM: Send changed lines to Pricing to YES 

Reference: How to Set Profile Options for Use With Advanced Pricing Document 553399.1

Action Item Requiring Output:

We want to use a sample of about 50-100 lines to establish a benchmark. Please retain this order for the duration of this process, as we may use it again to confirm our progress. And please ensure that we can use the same instance throughout this process. 

All Action Items with output should be gathered and compressed into one zip file and uploaded to the service request. Progress can not be made without ALL of the files and answers to ALL the questions. So it makes it easier for everyone to work, if ALL the required files are uploaded in one go, in a .zip file.

Please perform the following: 

Please provide following information to help troubleshoot the reported issue. The Key: 

A1. Items requiring action on your part, and generally an output file to be uploaded. 
Q1. Are questions requiring a response on your part? 
Actions marked as optional, are optional on your part, but should be reviewed to assist with the performance. 

A1. Diagnostics: Apps Check

Please provide a new Appscheck Report
Responsibility = OM Superuser
Navigation: Request,Reports=> Requests
Name = Diagnostics: Apps Check

ONLY Parameters to enter, so more information on the application is returned.
Application 1 = Oracle Order Management
Application 2 = Advanced Pricing

*If issue involves shipping, also include:
Application 3 = Shipping Execution

*If issue involves intercompany invoicing, also include:
Application 3 = Oracle Inventory

*If the issue involves Oracle Quoting, also include:
Application 3 = Oracle Quoting

Submit the request.
Upload the concurrent request output file.

A2. Run QP: Maintains the denormalized data in QP Qualifiers

Responsibility = Oracle Pricing Manager 
Navigate: Reports 
 Select 'single request' then 'OK' 
 Name: QP: Maintains the denormalized data in QP Qualifiers 
 Pick first value in List of Values (LOV) as the low number 
 Pick the Last value in the List of Values (List of Values (LOV) as the high number. 
Submit Request 
Upload output or concurrent log. 

A3. Run Build Attribute Mapping Rules 
Responsibility = Oracle Pricing Manager 
Navigate: Reports 
Select 'single request' then 'OK' 
Name: Build Attribute Mapping Rules 
Submit Request 
Upload output or concurrent log. 

A4. Run Attribute Mapping Rules Error Report 

Navigation path to run the concurrent request: 
Pricing Manager> Reports, Requests -> Request -> Single Request -> Attribute Mapping Rules Error Report 

1. Responsibility = Oracle Pricing Manager 
2. Navigate: Reports 
3. Select 'single request' then 'OK' 
4. Name: Build Attribute Mapping Rules 
5. Request Type leave blank 
6. Submit Request 
7. Upload output or concurrent log. 

A5. Run Diagnostics: Performance Analysis

This concurrent program will provide some additional details in regards to 
your pricing data distribution.
Responsibility: Oracle Pricing Manager
Navigate to Reports
Name = Diagnostics: Performance Analysis
Please upload the concurrent request output file

Alternative, one could run qpperf.sql
Usage: Copy the following section into a file named qpperf.sql and run it from SQL*Plus, connected as APPS user, with the following SQL command: SQL> START qpperf.sql

A6. Please provide the raw trace with Binds and Waits. 

If the issue is on the Order Management sales order form, set profile options before entering the sales order form.

Set Profile “QP: Debug” to “Request Viewer Off, show Diagnostic details in Trace” 

To get the trace file (with binds and waits), follow the instructions:
If version of QPXPPREB.pls is 115.172.1159.15 or higher OR version of QPXGPREB.pls is 115.574.1159.16 or higher then set profile option 'QP: Debug' to 'Request Viewer Off, show Diagnostic details in Trace'. All other debugs like OM debug level = 0, fnd debug etc. and PL/SQL profiler must be OFF.

If the versions of QPXPPREB.pls/QPXGPREB.pls are lower than mentioned above, set 'QP: Debug' to OFF and get the trace file. Otherwise customer can apply patch 3179036 to get the above specified file versions.

Versions and patches of QPXPPREB.PLS that belong to patch set 11.5.9 that are lower than

115.172 3083412 OM
15.172 11i.QP.I QP
115.171 3525663 QP
115.171 2967418 QP
115.171 2967155 QP
115.171 2944921 QP
115.169 3126422 AU

(This list is subject to change as new patches are released)

Set Profile OM: Debug Level to 0 (Debug must be OFF) 
Set Profile FND: Debug Log Enabled to No 

PL/SQL profiler must be OFF 
While generating trace file, it should not be first call to pricing after starting new session. First call generally takes longer time as db packages need to be uploaded in memory. So load the order and perform a pricing action before starting the trace. 
Now select Trace with Binds and Waits. On the form, select Help >Diagnostics >Trace >
Trace with Binds and Waits. 
Use Price Order as the Action to trace. 
Turn Trace off 
On the form, select Help >Diagnostics >Trace > No Trace 
Exit the sales order form 
Upload the raw trace file to the Service Request. 

A7. Provide TKPROFs for the above raw trace.
 

Issue a command like the following to create a TKPROF version of the trace file. This command sorts the results with the longest running queries first:

tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)'

Upload the output file to the Service Request.

A8. TKPROF that limits the results to the top ten queries(Optional):

tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)' print=10 

Upload to the Service Request

A9. What is the time spent in the pricing engine during this process? 
The total time spent in QPXGPREB.pls can be found from the pricing trace, by searching for the string “QP_PREQ_GRP: version, start redo”. 

Please note that time printed by QPXGPREB.pls is cumulative time for successive calls. Message coming from QPXPPREB.pls can be searched using string “Total Time in QP_PREQ_PUB”, note that this is not cumulative time total. 

A10. How to generate an OM Debug File For Performance Analysis 
While generating debug file, it should not be first call to pricing after starting new session. First call generally takes longer time as db packages need to be uploaded in memory. So load the order and perform a pricing action before starting the trace. 

1. Set the following profile options at the user level of the person performing the pricing action.

QP: Debug = Time Log
OM: Debug Level = 1

*Make sure the OM: Debug Log Directory profile is set to one of the directories returned from the following query:

select value from v$parameter where name = 'utl_file_dir';

2. Turn the OM debug on to generate a debug file for the pricing action.

* If the issue is in the sales order form in Order Management, turn the debug on by clicking on the option Tools > Debug. Note down the directory and name of the debug file.

3. Perform the action that is causing the performance issue. Then set the debug off.

* If the issue is in the sales order form in Order Management, turn the debug off by clicking on the option Tools > Debug; debug will be unchecked. Then exit the sales order form..

4. Retrieve the debug file directly from the server, or by running the concurrent request “Diagnostics: OM Debug File Retrieval” and entering the name of the generated debug file in the parameter. The output of the concurrent program will contain the debug messages.

5. In the generated debug file, look for the string “Time Log” and make sure it exists before uploading this file.

For detailed instructions, refer to Document 758241.1 for the new value “Time Log” which is used to print timing related debug messages in the OM debug file to diagnose performance issues.

A11. Please provide the HTMOMSE.SQL report 
Be sure to download and run the most recent script available, for it contains additional diagnostics. 

PARAMETERS: –
– ORDER NUMBER: Sample order where problem occurred.
– HEADER_ID: Enter the header id from the list shown.
– LINE_ID: Hit enter without entering a line_id
UPDATES: None
OUTPUT: HTMomse11i_<order number>. HTML 

Please upload the output file that was generated 

Refer to Document 133464.1  for detailed instructions and for the script.

A.12 Run Script qp_list_line_detail.sql 
Run once for the price list id and run once for any modifier that applies to the last order line of the subject order. 

This script provides information about how the price list line or modifier is setup and can be used when the engine has not selected the expected price list line/ modifier line. 

1. Obtain the Price List line ID/ Modifier line ID from the Price List/ Modifier setup window. Open the Price List/ Modifiers window. Position the Cursor on the Price List Line/ Modifier Line In Question. Navigate to : Help > Diagnostics > Examine > Pick the LIST_LINE_ID field from the Field LOV Note the value. This list_line_id value must be provided as input to the script qp_list_line_detail.sql. 
2. In Unix navigate to: $QP_TOP/path/115/sql 
3. Start SQL*Plus and run the qp_list_line_detail.sql script 
4. The script will prompt for the list_line_id 
5. Run the script $qp/patch/115/sql/qp_list_line_detail.sql and get all the Price List line/ Modifier line information, which takes list line ID as input. 
6. Script will generate outputs in <list line id>.lst file. 
7. Please upload the output file 

A.13 Execute this script and Provide the output 

select list_header_id,list_type_code from qp_list_headers_b
where active_flag = 'Y' and
nvl(end_date_active, sysdate+1) < sysdate;
This script will show expired pricing entities that are still active. 
Please upload the output.

A.14 Create a TraceAnalyzer File (Optional) 
Create a TraceAnalyzer File. 
A trace file is a raw set of data produced by the Oracle Database. 
TraceAnalyzer reformats the raw data and provide much more detailed information for analysis. It is a more powerful tool than TKPROF. 

1. Download TraceAnalyzer 
2. Install trace analyzer in SQL: @TRCACREA.sql; 
3. Retrieve the trace file. 
4. In SQL*Plus, issue a command like the following to create a TraceAnalyzer version of the trace file: TRCANLZR.sql UDUMP vis015_ora_22854.trc 

A.15 Execute Script bde_chk_cbo.sql 
1. Please provide the output to script BDE_CHK_CBO.SQL 
    Reference Note 174605.1
    This will provide the initialization parameters for the environment (EBS 11i and R12).

A.16 Execute Script bde_last_analyzed.sql

1. Please provide the output to bde_last_analyzed.sql
    Reference Note 163208.1:bde_last_analyzed.sql  – Verifies Statistics for all 
    This will provide more information on recently gathered statistics. 

QUESTIONS REQUIRING RESPONSE 

Q1. When did the performance issue start happening? 
Q2. What is the total time clock time for the benchmark orders the user experiences? 
Q3. What is the expected time for performance? 
Q4. What is the business justification for this expected time? 
Q5. How often do you “Gather Schema Statistics” and when was the last time it was executed? 
Q6. How often do you execute concurrent program “QP: Maintain denormalized data? 
Q7. What is the profile option Set QP: Blind Discount Option? 
Q8. What is the setting for profile option Set QP: Build Attributes mapping options? 
Q9. What is the setting for Set QP: Satisfied Qualifiers Option? 
Q10. What is the profile setting for OM: Deactivate Pricing at Scheduling? 
Q11. What is the profile setting for OM: Sales Order Form: Refresh Method? 
Q12. What is the profile setting for OM: send changed lines to pricing to? 
Q13. Is Tax calculation on during Order Entry? 
Q14. Are all the unneeded pricing phased end-dated? 
Q15. Are the following packages pinned to the database? 
QP_PREQ_GRP 
QP_BUILD_SOURCING_PVT 
QP_resolve_incompatability_PVT 
QP_FORMULA_PRICE_CALC_PVT 
QP_Calculate_Price_PUB 
QP_CUSTOM 
One may run the following SQL to check:

select distinct name from V$DB_OBJECT_CACHE where
name in ('QP_PREQ_GRP',
'QP_BUILD_SOURCING_PVT',
'QP_RESOLVE_INCOMPATABILITY_PVT',
'QP_FORMULA_PRICE_CALC_PVT',
'QP_CALCULATE_PRICE_PUB');

If 0 rows are returned, the packages are not pinned.
If any names listed in the where clause are not returned, those packages are not pinned.

Q17. Have these performance notes been reviewed? 
    a. Document 137328.1 Tuning Advanced Pricing for Optimal Performance 
    b. Document 130511.1 Performance Issues In OM, SE, QP 
    c. Document 169935.1 Troubleshooting Oracle Apps Performance Issues 
    d. Document 136687.1 Search for Pricing Diagnostics and Troubleshooting (PDF, 359Kb) 

Q18. Is anyone charged with the maintenance of your pricing data? 
Q19. What data cleaning steps are routinely used from the above white papers? 
      
For example:

a. Pricing Performance can be improved by inactivating these headers. The pricing engine will still have to look at these records, even though the effective date has passed. Unchecking the active box, will prevent the engine from considering these and help performance. This applies to both price lists and modifiers.'
b. ATTENTION – Use of 'Not=' Operator causes performance issues because many records are matched with 'Not=' qualifier
c. ATTENTION – Blind modifiers will get selected with every line hence they could cause performance issue
d. ATTENTION – Unnecessary event phases should be end dated
e. ACTION – For better performance 'OM Debug Level' should be set to '0'
f. ACTION – For better performance 'QP Debug' should be null or 'N'
g. Product Attributes With Low Selectivity, ATTENTION – These List lines could cause performance issues
h. ATTENTION – Blind modifiers will get selected with every line hence they could cause performance issue. select count(*) from qp_list_lines where pricing_phase_id > 1 and qualification_ind = 0

Q20. Do you purge the debugs before your run? Especially Purge Pricing Engine Requests and set the QP Debug to Off?  Refer to Note 308959.1  -How Can Qp.Qp_debug_text Table be Truncated?

References
NOTE:133464.1 – HTMOMSE Sales Order Diagnostic Script
NOTE:156860.1 – OMCHECK.SQL Oracle Order Management Diagnostic Tool
NOTE:163913.1 – Debugging Advanced Pricing Modifier and Price List Lines (qp_list_line_detail.sql)
NOTE:163914.1 – Diagnosing Advanced Pricing Performance (qpperf.sql)
NOTE:261366.1 – QP: Maintain the Denormalized Data in QP Qualifiers
NOTE:308959.1 – How Can Qp.Qp_debug_text Table be Truncated?
NOTE:363049.1 – Oracle Advanced Pricing Performance Analysis Data Collection Test
NOTE:419728.1 – Concurrent Processing – How To Gather Statistics On Oracle Applications 11.5.10(and above) – Concurrent Process,Temp Tables, Manually
NOTE:435422.1 – How to Identify Blind Modifiers and Make Changes to Setup for Improving Performance
NOTE:435426.1 – Oracle Applications 11.5.10 “poor performance” with Pricing Recommended Patch Strategy

 Related

——————————————————————————–
Products 
——————————————————————————–

Oracle E-Business Suite > Order Management > Price Management > Oracle Advanced Pricing 
Oracle E-Business Suite > Order Management > Price Management > Oracle Advanced Pricing 
Oracle E-Business Suite > Order Management > Price Management > Oracle Advanced Pricing