Seeing Execution Statistics for Currently Running SQL

Look at the V$SQLSTATS view, which gives information about currently or recently run SQL statements. If you wanted to get the top five recent SQL statements that performed the highest disk I/O, you could issue the following query:

SELECT sql_text, disk_reads FROM
(SELECT sql_text, buffer_gets, disk_reads, sorts,
cpu_time/1000000 cpu, rows_processed, elapsed_time
FROM v$sqlstats
ORDER BY disk_reads DESC)

WHERE rownum <= 5;

-------------------------------------------------------
SELECT schema, sql_text, disk_reads, round(cpu,2) FROM
(SELECT s.parsing_schema_name schema, t.sql_id, t.sql_text, t.disk_reads,
t.sorts, t.cpu_time/1000000 cpu, t.rows_processed, t.elapsed_time
FROM v$sqlstats t join v$sql s on(t.sql_id = s.sql_id)
WHERE parsing_schema_name = 'SCOTT'
ORDER BY disk_reads DESC)
WHERE rownum <= 5;

Keep in mind that V$SQL represents SQL held in the shared pool, and is aged out faster than the data
in V$SQLSTATS, so this query will not return data for SQL that has been already aged out of the shared

pool.

Using V$SQLSTAT to check queries with top disk reads
-----------------------------------------------------------------------------
SELECT disk_reads,sql_text FROM
(SELECT sql_text, buffer_gets, disk_reads, sorts,
cpu_time/1000000 cpu, rows_processed, elapsed_time
FROM gv$sqlstats
ORDER BY disk_reads DESC)
WHERE rownum <= 5;

Using SQL_MONITORING to monitor queries being currently executed 

SELECT sid, sql_text FROM v$sql_monitor
WHERE status = 'EXECUTING';

SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds,sql_id,module,username
FROM gv$sql_monitor
--WHERE SID=765
where status = 'EXECUTING';

The V$SQL_MONITOR view contains currently running SQL statements, as well as recently run SQL statements. If you wanted to see the top five most CPU-consuming queries in your database, you could issue the following query:

SELECT * FROM (
SELECT sid, buffer_gets, disk_reads, round(cpu_time/1000000,1) cpu_seconds
FROM v$sql_monitor
ORDER BY cpu_time desc)

WHERE rownum <= 5;

SQL statements are monitored in V$SQL_MONITOR under the following conditions:
• Automatically for any parallelized statements
• Automatically for any DML or DDL statements
• Automatically if a particular SQL statement has consumed at least five seconds of CPU or I/O time
• Monitored for any SQL statement that has monitoring set at the statement level
To turn monitoring on at the statement level, a hint can be used. See the following example:

SELECT /*+ monitor */ ename, dname
FROM emppart JOIN dept USING (deptno);

If, for some reason, you do not want certain statements monitored, you can use the NOMONITOR hint in the statement to prevent monitoring from occurring for a given statement.
Statistics in V$SQL_MONITOR are updated near real-time, that is, every second. Any currently executing SQL statement that is being monitored can be found in V$SQL_MONITOR. Completed queries can be found there for at least one minute after execution ends, and can exist there longer, depending on the space requirements needed for newly executed queries. One key advantage of the V$SQL_MONITOR view is it has detailed statistics for each and every execution of a given query, unlike V$SQL, where results are cumulative for several executions of a SQL statement. In order to drill down, then, to a given execution of a SQL statement, you need three columns from V$SQL_MONITOR:
1. SQL_ID
2. SQL_EXEC_START
3. SQL_EXEC_ID

If we wanted to see all executions for a given query (based on the SQL_ID column), we can get that information by querying on the three necessary columns to drill to a given execution of a SQL query:

SELECT * FROM (
SELECT sql_id, to_char(sql_exec_start,'yyyy-mm-dd:hh24:mi:ss') sql_exec_start,
sql_exec_id, sum(buffer_gets) buffer_gets,
sum(disk_reads) disk_reads, round(sum(cpu_time/1000000),1) cpu_secs
FROM v$sql_monitor
WHERE sql_id = 'fcg00hyh7qbpz'
GROUP BY sql_id, sql_exec_start, sql_exec_id
ORDER BY 6 desc)

WHERE rownum <= 5;