Database Performance Auditing

Introduction

Part I: Simple Method

• A: Data Sources - Three Tables
  • Table 1: Wait Events - v$system_event
    v$system_event
    references
    v$session_event
    v$session_wait
    v$event_name
    dictionary
    group by type
  • Table 2: Sqlarea - v$sqlarea
    v$sqlarea
  • Table 3: System Statistics - v$sysstat
    v$systat
    reference
    v$systat
    v$sesstat
    v$statname
    dictionary
    grouped by type
• B: Collecting Data
  • bstat/estat
    report.txt
  • sqldmp.sh
    sqldmp.sh
• C: Analysing Data
  • wait events
    example
  • statistics
    through put
  • sql statements
• D: Tuning Example
  • tuning example

• Other Tools - Collecting Data Revisited
  • collect.sh and utlstat.sh
    running
    data
    output
  • diff'ing statistics
    utlstat_dif.sh
    example latch
    example stat
    example sevt
    example init.ora
  • graphic displays
    stats_hist.sh
    graphic display
  • mon.sh
    mon.sh sevt
    mon.sh stat
    mon.sh [waits] - default
    mon.sh sessevt sid
    mon.sh sesstat sid
    mon.sh topstat [statname]
    mon.sh topsevt [eventname]
    mon.sh topsql
  • analyzing buffer cache
  • stats pack
    sql scripts
    setup
    running
    reporting
    output
  • audit triggers
    audit.sql
    noaudit.sql
  • sga direct access (m2)
    how
    example
  • io.sh
  • wait.sh and 10046

Questions or comments ? Send a note to Kyle Hailey