1. Using Oracle IT Analytics
  2. Maximize Performance and Availability
  3. Maximize Database Performance
  4. Analyze Database Performance Degradation

Analyze Database Performance Degradation

From the Performance Degradation section in Database Performance Analytics, you can identify the databases that have degraded in performance as follows:

  • Degradation with decrease in demand

    • Change in activity greater than 20%

    • Change in demand less than -10%

  • Degradation without change in demand

    • Change in activity greater than 20%

    • Change in demand between 10% and -10%

Database Performance Analytics lets you view the change in activity and demand for databases with the highest performance degradation.

Clicking the values for each category of databases (Degradation with decrease in demand and Degradation without change in demand) opens a list of the top databases within that specific category, along with the database type, change in activity, and change in demand for each. You can select a database from the list to view more information specific to that database. From the database drill-down page, you can:

  • View Performance Trend: View the actual and forecasted response time by demand and activity or active sessions.

  • View Workload Performance Variability: View the relative variability of SQL statements against response time variation. Variant SQLs are the ones whose relative variability is high. Highly variant SQLs are the ones that need attention.

  • View Inefficiency: View a chart that provides a breakdown of inefficient waits, I/O, and CPU. See Analyze Database Inefficiency.

  • Access Workload Stability: You can identify databases with workloads running, and SQL statements with a high degree of performance variability. Varying SQL statements have a relative variability greater than 1.66, and highly varying SQL statements have a relative variability of more than 3. SQL statements that are highly varying in performance need attention to ensure they have a stable response time. To calculate the percentage of the workload caused by the varying SQL statements for each database:

    1. Calculate which SQL statements are varying over the time period.

    2. Calculate the elapsed time for each varying SQL statement.

    3. Divide the sum of the elapsed time for each varying SQL statement by the total elapsed time on the database.

  • Identify Top SQLs: You can identify the top SQL statements across your entire database environment. Top SQL statements may consume an uneven amount of system resources. These SQL statements often cause a large effect on database performance and resource consumption. Identifying top SQL statements is an important SQL tuning activity that you must perform regularly.

  • Analyze workload with unusual characteristics: Inefficient wait time is calculated using the I/O, CPU and inefficient waits time. Most databases are likely to have some inefficiency. However, over time, if the database is getting increasingly inefficient, and the Inefficient percentage is greater that 50%, then, as the database administrator, you must pay attention to this database to maximize its performance.

You can drill down further to view details of the SQL queries run on that particular database. See Analyze SQL Execution Performance According to Database.