Database performance monitoring

Some handy commands to help with database performance monitoring and troubleshooting.
Environment: Oracle 10.2.0.4 on Solaris

1. PGA usage, top consumers
   select
   round(p.pga_alloc_mem/1024/1024) "pga_alloc_MiB",
   round(p.pga_used_mem/1024/1024) "pga_used_MiB",
   p.spid "OS_pid",
   s.sid, s.serial#, s.username, s.status, s.client_info, s.osuser, s.machine, s.terminal, s.program, s.module,s.sql_id
   from v$process p, v$session s
   where p.addr=s.paddr
   order by p.pga_alloc_mem desc;
   
2. UNDO usage, top consumers
   select round(t.used_ublk*(select block_size from dba_tablespaces where tablespace_name=(select value from v$parameter where name = 'undo_tablespace'))/1024/1024) "undo_used_MiB",
   t.used_ublk used_blocks,
   t.START_DATE trn_started,
   s.sid, s.serial#, s.username, s.status,s.osuser, s.machine, s.terminal, s.program, s.module,s.sql_id
   from V$TRANSACTION t, v$session S
   where t.SES_ADDR=s.saddr
   order by used_ublk desc;
   
3. TEMP usage, top consumers
   select t.tablespace, t.blocks,
   round((t.blocks * ts.block_size)/1024/1024) "Size_MiB", t.segtype,
   s.sid, s.serial#, s.username, s.status,s.osuser, s.machine, s.terminal, s.program, s.module,s.sql_id
   from v$session s, v$tempseg_usage t, dba_tablespaces ts
   where t.tablespace=ts.tablespace_name and s.saddr = t.session_addr
   order by t.blocks desc;
   
4.  CPU usage
   
   • UNIX top command, it can be sorted by CPU or memory (SIZE or RES). Press “h” for help.
   • In OEM you can look at Performance page of host.
   • UNIX Solaris command prstat
     sorted by CPU usage:
     
     
     prstat
     processes sorted by CPU and totals per user
     prstat -a
     sorted by memory and totals per user. Careful! It is different from top command as it shows shared memory as well.
     prstat -a -s rss
     
     
     
   
   
5. Free memory
   UNIX top command and OEM host performance page can be used. They show the same result.
    
6.  Paging and Swapping
   In OEM:
   Go to target host, "Performance" tab. Click "Swap Utilization" number to see a graph or click "Paging Activity" to see more metrics.
   Unix commands:
   vmstat -S 3 12
   Where 3 is the number of seconds between statistics samplings and 12 is the number of samples to take. Watch "si" and "so", swap in/out columns, and scan rate "sr". If scan rate is non-zero for an extended period of time, you need more RAM.
   Note that first line of output includes all activity since the last vmstat report.