Database performance monitoring

Some handy commands to help with database performance monitoring and troubleshooting.
Environment: Oracle on Solaris
  1. PGA usage, top consumers
    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:

      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.

No comments:

Post a Comment