| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: ASMM and poor SQL caching
I just saw an issue few days ago (10.1.0.3 on SPARC) where shared_pool took
8000MB of 8GB SGA_TARGET, leaving buffer cache to 96MB for this data
warehouse.
Eventually the instance crashed due some failed granule allocation operation (trying to get even more memory for shared pool). And that was so even though SGA advisories "advised" to increase buffer cache instead and decrease shared pool.
SGA_TARGET is useful in cases:
1) small to medium database, DBA doesn't want to waste 2 minutes to
configure those parameters manually (once)
2) databases with fluctuating workload (1 large batch at night, 10000 OLTP
users during day) under physical memory pressure.
Then it's good to have ASMM juggling around with memory to get better performance for different workloads.
If you don't have memory pressure - you don't need ASMM. Especially if it's causing you problems on its own.
Tanel.
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Susan White
Sent: Saturday, August 19, 2006 04:53
To: 'Oracle-L_at_freelists.org'
Subject: ASMM and poor SQL caching
How does ASMM handle a system with high volumes of non-shareable SQL? When manually sizing the shared pool, we have always been reminded that a large shared pool size could lead to serious performance problems strictly because of the size of the pool. Assuming you have this situation in an ASMM environment, how does Oracle handle it? Does the shared pool grow so large that the other auto-sized caches are affected? Do the advisors recommend increasing the sga_target to accommodate a huge shared pool and if so, what are the performance ramifications? Are they similar to those encountered with large shared pools in a manually sized environment?
I realize that one of the answers is to fix the app. However, I'm trying to understand how ASMM would handle this situation in case it isn't a good solution for some applications.
Thanks
Susan White
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 18 2006 - 23:23:18 CDT
 |
 |