ciatecmx
24th July 2007, 18:10
Oracle 10g R2 and Baan 5c on AIX 5.3 L (p550, 3792M , 4 lcpu)
After I reduced the oracle´s max_sga_size (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. In addition, they appeared in the log.ora.sql of Baan as an error message, so the query requests were hung or stopped.
Reading from other posts and metalink note: 146599.1, it indicated that the 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 gave ora-4031 warning/error, actually only very few.
So how can I correct the query script produced by Baan to minimize the problem caused by the bind variable? In other word, create the query using the bind vairable in the baan procedures or module? NOT to create the query script directly manually.
After I reduced the oracle´s max_sga_size (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. In addition, they appeared in the log.ora.sql of Baan as an error message, so the query requests were hung or stopped.
Reading from other posts and metalink note: 146599.1, it indicated that the 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 gave ora-4031 warning/error, actually only very few.
So how can I correct the query script produced by Baan to minimize the problem caused by the bind variable? In other word, create the query using the bind vairable in the baan procedures or module? NOT to create the query script directly manually.