Measurement of SQL performance-SQL monitoring using Hints and dbms_sqltune

You can use Hints to enable real-time monitoring of a SQL.

Select / * + monitor*/ count (*) from scott.emp where sal > 2000

Use dbms_sqltune to view monitoring information

Set long 9999999

Set longchunksize 9999999

Set linesize 200

Select dbms_sqltune.report_sql_monitor from dual

REPORT_SQL_MONITOR

-

SQL Monitoring Report

SQL Text

-

Select / * + monitor*/ count (*) from scott.emp where sal > 2000

Global Information

-

Status: DONE (ALL ROWS)

Instance ID: 1

Session: SYS (9VR 15084)

SQL ID: 6k8qh0ubz4mnw

SQL Execution ID: 16777216

Execution Started: 05/27/2017 23:10:31

First Refresh Time: 05/27/2017 23:10:31

Last Refresh Time: 05/27/2017 23:10:31

Duration: .0003s

Module/Action: sqlplus.exe/-

Service: SYS$USERS

Program: sqlplus.exe

Fetch Calls: 1

Global Stats

=

| | Elapsed | IO | Other | Fetch | Buffer | |

| | Time (s) | Waits (s) | Waits (s) | Calls | Gets |

=

| | 0.00 | 0.00 | 0.00 | 1 | 7 |

=

SQL Plan Monitoring Details (Plan Hash Value=2083865914)

=

| | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |

| | (Estim) | | Active (s) | Active | | (Actual) | (%) | (# samples) |

=

| | 0 | SELECT STATEMENT | 1 | + 0 | 1 | 1 |

| | 1 | SORT AGGREGATE | | 1 | | 1 | + 0 | 1 | 1 |

| | 2 | TABLE ACCESS FULL | EMP | 10 | 3 | 1 | + 0 | 1 | 6 |

=

The monitoring information always points to the SQL statement that was last monitored and will be cleared after the database is restarted.