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;