Using SQL Performance package on Oracle

Navigating to the SQL Performance package dashboard

Once the SQL statistics job is installed and have collected data, you can navigate to the dashboard.
It is located in the management interface for that database instance. The pictures and descriptions are from Oracle.

There should be a “SQL performance” node in the three structure. Click on it to open the SQL Performance dashboard.

Using the SQL Performance dashboard

Navigating time

The structure of the SQL Performance dashboard is a top graph showing historical data, such as buffer gets, direct writes, and disk reads.

The buffer gets, direct writes, and disk reads graph is used to select the timeframe you want to look at.

You can select predefined time selections, such as Last 8 hours, Last 24 hours, Last 2 days, Last week, and all statistics:

You can also use the mouse to select a timeframe from the graph. Click on your start time, hold the mouse button, drag it to the desired end time, and release the mouse button.

This will change the selection of the SQL statistics table and the additional information graphs.

SQL statistics

The SQL statistics will list SQL statements that have been active in the selected time period.

The fields

Records:
The number of times this SQL statements has been detected by dbWatch

Schema:
The schema executing the SQL or procedure

DBW:
Is this a SQL or procedure associated with dbWatch

First execution:
The first time in the selected time frame where this SQL or procedure was executed

Last execution:
The last time in the selected time frame where this SQL or procedure was executed

Executions:
The number of times in the selected time frame where this SQL or procedure was executed

Elapsed time:
The total elapsed time (in milliseconds) in the selected time frame where this SQL or procedure was executed

Rows:
The total number of rows in the selected time frame where this SQL or procedure was executed

Buffer gets:
The total number of buffer gets in the selected time frame where this SQL or procedure was executed

Direct writes:
The total number of direct writes in the selected time frame where this SQL or procedure was executed

Disk reads:
The total number of disk reads in the selected time frame where this SQL or procedure was executed

Navigating SQL statistics

You can investigate the SQL further by right-clicking on one of the lines. Show statistics will open a new dashboard focusing on one SQL statement.

This dashboard will show buffer gets, direct writes and disk reads statistics for this statement, the statistics for the SQL statement, and the SQL code that has been run.

You can also open show query plan to open the query plan for the statement.

Additional graphs

In addition to the logical/physical reads and writes and the SQL statistics overview, there are additional graphs to visualize other performance statistics from the same selected timeframe.

Examples:

Elapsed time: