The Pitfalls of Oracle’s Automatic SGA Management
Dec 14, 2006 / By Raj Thukral
Of late, Oracle has had the tendency to automate a lot of the usual DBA tasks.
I didn’t complain on autoextensible datafiles, I just had to make sure my disk had enough space to handle the maximum potential space requirement. I also didn’t complain too loudly when a file extended to a size that triggered an Oracle bug and caused the database to crash irrecoverably. It was 8i after all, and things have improved since. Or have they?
I was mildly amused by automatic PGA and for the most part it worked well.
The default dba_scheduler job to refresh statistics interfered with my own job to gather statistics, and the schedule was the worst possible for the db I first saw it on, after an upgrade from 9i to 10.1 — but that was a minor irritant and quickly resolved.
However, I have to draw the line at automatic SGA management. I thought it seemed like a good feature and left it at its default in a new 10.2.0.2 database that I recently set up. As production ramps up on the db, I can see how a combination of application issues (non-bound SQL) and automatic SGA can quickly cause problems. The database has been running for about a week now. The application has very little bound SQL and our best efforts to get the developers to start binding SQL have been mostly unsuccessful, since they are hard–pressed to maintain the legacy code, which is mostly spaghetti.
So here’s how the SGA looks after one week of running in production:
SQL> show sga Total System Global Area 7516192768 bytes Fixed Size 2082856 bytes Variable Size 4513073112 bytes Database Buffers 2952790016 bytes Redo Buffers 48246784 bytes SQL>
Wow… 4.5G of the 7.5G or so is “Variable Size” — I would expect that the db cache should have been bigger than the variable size, but no. The db cache, which started out at 6G+ is down to half its size.
Lets see where the variable size is allocated:
SQL> select component,current_size/1024/1024 as "Size MB" from v$sga_dynamic_components 2 / COMPONENT Size MB ---------------------------------------------------------------- ---------- shared pool 4272 large pool 16 java pool 16 streams pool 0 DEFAULT buffer cache 2816 KEEP buffer cache 0 RECYCLE buffer cache 0 DEFAULT 2K buffer cache 0 DEFAULT 4K buffer cache 0 DEFAULT 8K buffer cache 0 DEFAULT 16K buffer cache 0 DEFAULT 32K buffer cache 0 ASM Buffer Cache 0 13 rows selected. SQL>
4.2G in the shared pool. It was just under 4G yesterday, it’s still growing!
Oracle thinks it can make queries run better by not having to parse them repeatedly, so it grows the shared pool to keep as many queries as it can. Problem: the shared pool is now 4G+. That takes a while to trawl through, which of course adds to the spins on the library cache lock and pushes up CPU usage. And of course I/O usage is high(er) because the db cache is now lower. A lose-lose situation I think. I’m going to get into more detail on this when I have the time, but for this db, I think I’m going to switch to manual SGA and set a hard limit of 500M-1G on the shared pool. There isn’t any point in trying to hold on to what is going to be mostly–unique SQL anyway.
Oracle, please spare me the “automatic” stuff unless you can make it a little more intelligent!