To: Eygle
Oracle 10g R2 and Baan 5c on AIX 5.3 L (p550, 3792M , 4 lcpu)
After I reduced the max_sga_size to 36% of total memory (so to avoid the paging), now I see more ora-4031 (out of shared memory loading library cache object) warnings in the udump trc file, not in alert file. They also appeared in the log.ora.sql of Baan as an error, so the query requests were hung or stopped.
Reading from other posts and metalink note: 146599.1, it indicated that my system has extensive fragmentation problem. (REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC. )
So I used the tips form AskTom to find the query scripts that may have bind variable problem and I did found some, most of them in 2~4 multiples. However, not all of them return with ora-4031 warning/error, actually only very few.
So what should I do for the best of the performance?
1. correct the problem of bind variable? (queries were created by Baan processes)
2. increase max sga?
From: Chi 2007.07.24 07:54
|