View Single Post
  #9 (permalink)  
Old November 28th, 2007, 17:21
miks miks is offline
Junior Member
 
Join Date: May 2007
Posts: 19
Thanks: 1
Thanked 2 Times in 2 Posts
Groans: 0
Groaned at 0 Times in 0 Posts
Rep Power: 0
miks is on a distinguished road
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.

CREATE
ORREPLACETRIGGER 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.

Reply With Quote