Pricing API Optimization – Status
Review
Copyright © 2019 Oracle and/or its affiliates.
Possible Options :-
• Custom Attributes code Optimization
• Pricing Archival Process & Evaluation on Pricing API Perf
• Pricing API Optimization of market volume offer Package
(QPXGPREB)
Copyright © 2019 Oracle and/or its affiliates.
Custom Attributes – Code Optimization
For custom attribute package optimization, we've modified the packages to handle null
parameters. The updated packages include:
• MICH_OZF_QP_QUAL_PKB
• MICH_QP_ADDITIONAL_ATTRIBS_PKG
• MICH_QP_BP_ADDTL_ATR_PKG
• MICH_OZF_QP_EXCL_BG_PKB
One key optimization involved the customer group hierarchy, which was being executed for each
run. To improve performance, we created a custom table preloaded with customer group data.
This change has yielded positive results, as reflected in the attached performance metrics.
By leveraging the custom table, we've reduced the overhead associated with repeated
executions of the customer group hierarchy logic. The attached data highlights the
performance improvements achieved through these optimizations.
Copyright © 2019 Oracle and/or its affiliates.
Custom Attributes – Code Optimization
Data used for testing :- One customer , 50 line Items
Time Taken (Secs)
Before Code Optimization Non-Volume 16.64
After Code Optimization Non-Volume (With Trace 11:53 9.14
AM IST)
After Code Optimization Non-Volume (Without Trace 2:41 6.10
PM IST)
After Code Optimization Non-Volume (Without Trace 5:21 6.71
PM IST)
After Code Optimization Volume Offer (With Trace) 38.98
After Code Optimization Volume Offer (Without Trace) 25.63
Observation :- Non-Volume 55%
Copyright © 2019 Oracle and/or its affiliates.
Custom Attributes – Code Optimization
Before Code Change :-
Copyright © 2019 Oracle and/or its affiliates.
Custom Attributes – Code Optimization
After Code Change With OZF261 Table – Non-Volume offer:- Without
Trace
Copyright © 2019 Oracle and/or its affiliates.
Custom Attributes – Code Optimization
After Code Change With OZF261 Table (Without Trace) :-
Copyright © 2019 Oracle and/or its affiliates.
Custom Attributes – Code Optimization
After Code Change With OZF261 Table – Volume Offer :- Without
Trace
with SERVEROUTPUT without SERVEROUTPUT and trace
1)PL/SQL procedure successfully 1)PL/SQL procedure successfully completed.
completed. Elapsed: 00:00:28.43
Elapsed: 00:00:58.61 2)PL/SQL procedure successfully completed.
=====>TFOPEURC2_ora_208898.prf Elapsed: 00:00:24.32
2)PL/SQL procedure successfully 3)@SQL_VOL_50_2_1.sql
completed. PL/SQL procedure successfully completed.
Elapsed: 00:00:33.90 Elapsed: 00:00:25.63
=======>TFOPEURC2_ora_305887.prf
3)PL/SQL procedure successfully
completed.
Elapsed: 00:00:38.98
====>TFOPEURC2_ora_369220.prf
Copyright © 2019 Oracle and/or its affiliates.
Custom Attributes – Code Optimization
Next Steps:
1. We're currently working on optimizing other functions used in the
custom packages.
• By implementing this optimization, we aim to improve the
performance of our custom packages by reducing unnecessary
computations and leveraging the build context more efficiently.
2. Our plan is to categorize pricing attributes into static and dynamic
types.
• Before calling the build context, we'll retrieve all static attributes and
then use the build context to fetch only dynamic attribute values.
• To achieve this, we're attempting to clone the standard package.
However, we're encountering compilation issues, which have been
reported to the development team. We're awaiting their updates to
proceed further. Copyright © 2019 Oracle and/or its affiliates.
Pricing API Optimization (Market Volume
Offer for Package - QPXGPREB) - SR Ref # 3-
40864877461
DEV team confirmed that NO further optimization possible with query
tuning
Best code is the one that was released previously with the Patch
Copyright © 2019 Oracle and/or its affiliates.
Pricing Archival Process
Script to build the API as concurrent request is completed and validated
in CQT
Code shared with Michelin team for Analysis and execution in PERF
Data backup and count from the base table before archiving
Michelin team to provide the following for analysis:
Pricing API performance validation prior to Archival for all applicable
test cases in PERF instance.
Pricing API performance validation post the Archival for all applicable
test cases in PERF instance.
Log details of Archival to verify the time taken for completion of given
set of PL/PL lines and Modifiers.
Note :- Based on the data analysis for the period upto 2023, could observe
approximately 43 Million records to be archived (Refer Annexure).
Copyright © 2019 Oracle and/or its affiliates.
Annexure :-
Q&A
Thank You
