Sga Auto sizing ! Good or Bad ?

Have you bumped accross SIMULATOR LRU LATCH or SIMULATOR HASH LATCH ?
I have. I'm sure you have too.
What is it ? Why does it happen ?

Let's start from the basics.

What is ASMM ? Auto Shared Memory Management ?
Sure, it manages Oracle SGA memory dynamically. All you have to do is specify an upper limit using

alter system set sga_target = scope=spfile;

(and reboot !)

and Alas ! Oracle automagically sizes the buffer cache, shared pool, streams pool and the java pool. No need to measure any sizes of what buffer cache or shared pool should be.

Right ?

Wrong !

Let's dig more....

SQL> select 
2      component,  
3      parameter,  
4      initial_size,  
5      target_size,  
6      final_size,  
7      status,  
8      to_char(start_time,'dd-mon hh24:mi:ss') start_time,
9      to_char(end_time,'dd-mon hh24:mi:ss') end_time 
10   from   
       v$sga_resize_ops 
10   where  
       start_time > sysdate -1/12 
11*  order by 
       start_time

COMPONENT            PARAMETER         INITIAL_SIZE   TARGET_SIZE  FINAL_SIZE   STATUS     START_TIME      END_TIME
---------            --------------    ------------   ----------- ----------    ------     --------------- -----------
DEFAULT buffer cache db_cache_size     1.5469E+10     1.5452E+10  1.5452E+10    COMPLETE   04-jun 17:54:40 04-jun 17:54:40

shared pool          shared_pool_size  3506438144     3523215360  3523215360    COMPLETE   04-jun 17:54:40 04-jun 17:54:40
SQL> 

What is this ?
This means that ASMM is asking to increase the shared pool area and taking away space from buffer cache and that could be a reason why you will see simulator lru latch and simulator hash latch waits.

All good ?

Here's what I found. Although shared pool has enough free space available, SGA resizing still takes place.

SQL> select *  
2    from   v$sgastat  
3    where  pool like 'shared pool'  
4    and    name like 'free%';

POOL         NAME                       BYTES
------------ -------------------------- ----------
shared pool  free memory                805474392

What is this ?
The means there is 800 Megs of free space in the shared pool and ASMM is still asking for more space.

Why is that ?

I have asked Oracle. They had a wierd answer. Library object hits could be benfitial with having a larger shared pool.

Really ?
EVEN WITH FREE SPACE AVAILABLE ???

Here's the latest !
"It is cheaper for Oracle to move granules from buffer cache to Shared Pool than to use the free memory already avaiable in Shared Pool" - Doesn't make sense, does it ?

Well, this above is TRUE. ASMM works on hit ratios and when it thinks Shared Pool could do better by re-sizing it, it so does it !

Moral: Don't rely too much on the AUTOMATIC bells and whistles that Oracle provides.

Study them first !

Different Yardsticks in Oracle

1) Shared Pool has a 4k Chunk Size.

2) Granule is 16Mb (these days) for an SGA over 128Mb.

3) Shared Pool Library Cache Threshold >2048K (> 2MB)

Linux - One by One the Penguins steal my sanity.

List of some commands I like and you will too !

screen
- Running a script and got disconnected ?
- Running a script in background and forgot to pipe the output ?

These are some and numerous other reasons why you should use screen command rather than doing the above.

screen and hit enter
ctl -a d - to detach from the screen.

screen -ls (to view all disconnected screens like follows)

There are screens on:
6295.ttyp1 (Detached)
22270.ttyp3.server (Detached)
24315.ttyp5.server (Detached)
3 Sockets in /tmp/screens/S-username

screen -r 22270
lets you can get into session 22270

Having fun ?

What happens when i wnat screen's inside screens ?

well. You do the following.

ctl - a - n
gets you in the next screen.

ctl - a - p
gets you in the previous screen.




watch
Great utility ! Ever tried doing iostat -x 3. Well it gives io statictics update every 3 seconds.
Now try this,
watch -n 3 -d iostat
It also refreshes iostat every 3 seconds. But check out the difference.
watch enables the same display over and over again and the deviation portion highlighted.
Probably one of the little great utilites i have every used.

Redo copy latch, allocation latches & log file sync

What is redo copy latch ?
Generation of redo requires a redo copy latch to be acquired by a process. This is done so that the LGWR knows the data is being copied and so it doesn't flushes that data in the REDO LOG.


What is redo allocation latch ?
Redo allocation latch is obtained to allocated space in the log buffer. This is done to know which log buffer blocks are used and which are free.
If you see this event, it could be due to a number of things.
1) You don't have enough space in your log buffer and processes are waiting for space allocatioin, or lgwr is slow due to slower disks.
2) dbwr gets a latch to see whether these blocks are written to the disk so it can write these blocks to the data files.
3) The sessions that are waiting on log file sync cycle thru and acquire redo allocation latch to check on the log buffer blocks are written to the redo logs or not.

These are the basic reasons why you will see redo allocation latch. Ask me if you need to know how to fix these issues.


What is log file sync ?
Sessions waiting on the return on the commit from the log buffer to guarantee recovery.
DBWR waiting on LGWR to write redo blocks to the redo buffer so dbwr could write the corresponding blocks in the datafiles.
Slower disks
Smaller log buffer.