Thursday, September 11, 2008

How To Purge Pricing Debug information

In order to purge pricing debug information, run the concurrent program Purge Pricing Engine Requests. This concurrent program executes procedure QP_PURGE_PRICING_REQUESTS.PURGE. This is a good procedure than can give you insight into QP debug tables but it's coding can serve an example of how to write worst possible code. Given below is the example of deleting record a from table.


LOOP
DELETE QP_DEBUG_TEXT WHERE request_id in
(SELECT request_id FROM qp_debug_req
WHERE TO_DATE(creation_date, 'DD-MM-YYYY') <=
TO_DATE((SYSDATE - l_no_of_days), 'DD-MM-YYYY') AND
request_name like l_request_name
) AND rownum <= 500;
IF SQL%NOTFOUND THEN
EXIT;
END IF;
COMMIT;
END LOOP;

If your company gets large number of orders and by mistake this profile option was ON at site level for few days, the number rows in this table would be enormous. For one pricing calls, this table gets more than 3000 rows. At my site, in few days this table accumulated more 17 million rows.


After you purge the debug information, you must gather statistics on QP Debug tables otherwise 'Pricing Engine Request Viewer' will be annoyingly slow. You may use following script to gather stats on QP debug tables

DECLARE
TYPE t_tbl_names IS TABLE of VARCHAR2(40);
l_tbl_names t_tbl_names := t_tbl_names( 'QP_DEBUG_REQ'
, 'QP_DEBUG_REQ_LINES'
, 'QP_DEBUG_REQ_LDETS'
, 'QP_DEBUG_REQ_LINE_ATTRS'
, 'QP_DEBUG_REQ_RLTD_LINES'
, 'QP_DEBUG_FORMULA_STEP_VALUES'
, 'QP_DEBUG_TEXT'
) ;
BEGIN
FOR i IN l_tbl_names.FIRST..l_tbl_names.LAST
LOOP
dbms_stats.gather_table_stats
( ownname => 'QP'
, tabname => l_tbl_names(i)
, estimate_percent => dbms_stats.auto_sample_size
, method_opt => 'for all columns size auto'
, cascade => true
, degree => 5
) ;
END LOOP;
END;

No comments:

Post a Comment