SQL Statistics

Info

This document has been created based on the Oracle Monitoring V2. For the Oracle monitoring V1 document, see the following.

Home > Select Project > Stat/Report > SQL Statistics

Statistics are created based on the SQL execution count, execution time, and waiting time within the active sessions collected by the agent, and a list of top SQLs for the search period is provided. It provides a basis for analyzing frequently performed SQL performance through the average and maximum execution time.

For quick performance analysis, you can search top SQLs for each frequently analyzed category (DB, USERNAME, MACHINE, PROGRAM). The main search criteria are as follows:

• execute count: Execution count of the SQL

• elapse sum (sec): Total execution time (secs) of the SQL

• elapse avg (sec): Average execution time (sec) of the SQL. It is the result of elapse sum/execute count.

• elapse max (sec): Maximum execution time (sec) of the SQL

• elapse wait (sec): Total waiting time (sec) of the SQL

• instance: Instance name

• physical reads: Number of times the SQL reads data from the disk

• session logical reads: Number of times the SQL reads data from memory

Info

SQL Statistics function creates statistics based on the SQL that excludes literal values.

Basic screen guide

1. In Time, select the time and date to view the data. You can select a lookup time after selecting the green button.

2. In Instance, select a target to view the data.

3. Select .

The top 50 entries appear in the table.

Note

• Select the clock display area to query a random date and time. If you click the date and time text area, the selectable options for the date and time appear.

• If you select each column in the table header, you can sort the list by the selected item.

• You can sort the searched results by the item selected in Sort by. Select a desired item and then select .

• In View count, you can set the number of entries to display in the table. Finish the settings and then select .

• To download the viewed data as a CSV file, select .

Checking the query-based execution trend

To classify and view data by usage category, select the desired tab from .

On the utmost left for each item of the table, select ►. A chart appears that displays the number of executions every 5 minutes and the average execution time of the query statement. If you hover your mouse over each bar graph, you can see the number of that time.

Note

If you select a bar graph for the desired time in the graph chart, the Counts Trend menu appears. You can see the metrics for the selected time. For more information about Counts Trend, see the following.

Previewing the query

You can preview the query statement by moving the mouse pointer to the sql column in the search results list.

See details

If you select query column from the searched result list, the SQL details window appears.

If you select sql from the searched result list, the SQL details window appears.

• View SQL Statistics: You can go to the SQL statistics menu where you can check statistical information related to the SQL query statement.

• Runtime Plan: It provides the execution plan and runtime information for a selected SQL query. It provides the information details such as execution count, average execution time, and average physical reads.

• Explain Plan: It displays the execution plan predicted by the optimizer. It provides information such as cost, job, object name, and cardinality.

• Plan History: You can check the history of the execution plans of the SQL queries executed in the database.

• Bind Capture: You can see the values ​​of the bind variables used in SQL queries executed in the database. This allows you to see actual content of query executions.

  Note

  This is a value captured in the database (v$sql_bind_capture), not a bind value executed in real time. If the number of cases is too large, it displays up to 100.

Filtering the searched results

Adding the filter conditions

You can perform a search by filtering by SQL text, agent name, DB user name, and the like.

1. In the Filter option, select .

2. In Filter key, select a desired filtering criteria.

   • If the value of the selected item is text, you can select any of Includes (blue) and Excludes (red).

     

   • If the value of the selected item is number, you can select any condition of == (equal to), >= (greater than or equal to), and <= (less than or equal to).

3. In Condition, select a condition.

4. Enter a string or number to match the condition.

5. Select Apply.

Note

• To add filtering conditions, select Add and then repeat Step 1 to 5. Added conditions are applied based on AND (&&).

• To delete some items while adding conditions, select on the right of the filter conditions. To delete all conditions, select Delete All.

• To quickly delete the conditions applied to the Filter option, select .

• The default setting is to exclude if sqlText contains 'WhaTap'.

  

Modifying the filter conditions

Click any applied to the Filter option. If the Edit filter window appears, modify any desired items and then select Apply.

Setting the table columns

You can hide the table header columns or add any of them. You can also change the column order. Select .

Note

• After configuration, select Confirm to apply the settings in the table.

• In the search bar, enter text to search the desired columns. Only the columns that meet the entered text are displayed.

• Images may differ depending on the product, project, or menu.

Adding columns

From the list, select the items to add as table header columns. To select all items, select Select All.

Deleting columns

From the list, unselect the columns to delete. Alternatively, select on the right of the item to delete from the list.

Changing the column order

Drag an item to reposition from the list, and then move it to the desired position.

Initializing the configuration

To cancel all changes and reset them, select Initialized.