Oracle Performance and Engineering

[Well, I am back. I took time off from blogging for last 8-9 months because I got married in between and took it kind of easy outside work.]

The view V$SGA_RESIZE_OPS gives information about if the SGA has been increased / decreased. This keeps information about last 100 resize information. Very cool to see when I see a new customer database and want to know what all have changed in between. This is kind of .bash_history for SGA.

Here's how the output looks -

Select * from v$sga_resize_ops;

component oper_type oper_mode parameter initial_size target_size final_size status start_time end_time
===============================================================
shared pool SHRINK MANUAL shared_pool_size 637534208 553648128 637534208 ERROR 3/29/2006 11:54:57 AM 3/29/2006 11:54:57 AM

shared pool GROW MANUAL shared_pool_size 637534208 671088640 654311424 ERROR 3/29/2006 11:55:10 AM 3/29/2006 11:55:10 AM

Good to see that even operations that did not succeed are recorded in this view. That could also be useful for security purpose.

Resize operations, apart from in the V$SGA_RESIZE_OPS, also get recorded in alert.log. Of course! That's how we knew it on pre-9i days.

Here's what got recorded in the alert.log when I tried to shrink and failed -


Wed Mar 29 11:55:10 2006
MEMORY TRACING: MANUAL request
MEMORY TRACING: FG request
MEMORY TRACING: Receiver of memory is shared pool
MEMORY TRACING: Receiver size is 39 granules
MEMORY TRACING: Donor of memory is System Memory
MEMORY TRACING: Donor size is 0 granules
MEMORY TRACING: Amount of memory requested is 33554432 bytes
MEMORY TRACING: Amount of memory to be got is 16777216 bytes
MEMORY TRACING: Amount of memory received is 16777216 bytes
MEMORY TRACING: Min contig requirement is 0 bytes
MEMORY TRACING: Granule size is 16777216 bytes
MEMORY TRACING: Request status is ERROR due to 4033


Next post - Reading 4030 heapdump and analyzing a particular case of memory leak from it.