| Re: Purging and Archiving You should create a trigger in the database to Pin all the most commonly executed or large packages. This is in the OTM Administration Guide.
CREATEORREPLACETRIGGER glogowner.db_startup_pin_packages afterstartupondatabase
begin sys.dbms_shared_pool.keep('GLOGOWNER.VPD'); sys.dbms_shared_pool.keep('GLOGOWNER.VPDLOG'); sys.dbms_shared_pool.keep('GLOGOWNER.XVALIDATE'); sys.dbms_shared_pool.keep('GLOGOWNER.GLOG_UTIL'); sys.dbms_shared_pool.keep('GLOGOWNER.PKG_PURGE'); sys.dbms_shared_pool.keep('GLOGOWNER.PARTIT');
end;
/
Additionally, what are the queries in your BM going after? Order Release? Shipments?
I would copy someone's BM who is having the issue, and then go and add them back one by one to see which query is giving you the issue. Once you find the bad performing query, and a fix for it, you'll have to make the same fix for all the other queries in the BM going after the same subject. Order release, in my experience is a common cause of BM performance. I have had the same issues as well. We have 15+ in the BM. We were seeing 3-5 minutes to bring it up. After tuning, we are down to 5 seconds. |