Jason Wong     Career Education Travel Music HD HTPC Tennis Health Database Scripts Speak at SQLPASS Present to NASA, Robot14

  Oracle and SQL-Server Database Administration Scripts 

    ===================================================================== Oracle ========================================================================
    Check out scripts I wrote on Linux shell and Windows Powershell scripts, plus super PL/SQL scripts to alert notify OS and database conditions, including DataGuard and alerts.

    Download my Oracle DBA scripts, both Linux and Windows (upon request).

    Database Performance issue? Typical Diagnosis and prevention: (I learned a lot from Oracle - Tom Kyte, from his books, blogs, and presentations.) 
    Database performance problems: First check wait statistics - 
Wait event statistics history data reveals various symptoms of problems. I have written a comprehensive running differential query to allow you see the instant wait stats in a time-interval into a history table based on Oracle Tom Kyte's simple illustration example.
The key for diagnosis and prevention of database performance issue is to record historical data so you know the baseline and when problem was/is happening. Wait stats is the best indicator so you know how to remedy or prevent repeated incidents.
You could run AWR and ADDM if you have license, use statpack if you don't have diagnostic tuning option license.

    To improve Oracle database performance, first check session and database server performance by checking the following statistics:
  • Wait Events
  • Session Wait
  • Time Model Statistics
  • Active Session History (if you have license)
  • System and Session Statistics
    • I have many performance-checking super scripts. I learned a lot from Oracle Tom Kyte, from his books, blogs, and presentations. One of them sets up any interval of repeating capturing statistics per interval for later non-realtime analysis. You can use AWR snapshots or any 3rd party tools you have that can capture intervals of history.
    Once you identify the performance is on a particular sql statement, after reviewing explain-plan, the following revision can improve performance:
  • Partitioning - improves query response time by grouping focus on a smaller dataset.
  • Query Rewrite - good syntax helps optimizer, bad query syntax spin-wastes database engine time.
  • Materialized Views - pre-construct data that already summarize aggregation that saves query execution time.
  • Indexes - improves query efficiency by searching index to reduce response time.
  • Compression - reduces the amount of I/O required from query.
    • You can also associate a sql statement to a particular statistic profile that improves performance by using the much more co-related correct statistics.
    If none of the above is identified to help, then resize server capacity on where the shortage of resources, such as CPU, memory, IO, is the only last resort.
    I have 600+ Oracle DBA scriptlets I often used. (including performance checkup and tuning, duplicate database script, Setup/Configure Data Guard Primary and Standby script, DR standby check-up script. RMAN PIT recovery, database management scripts....)