Ad blocker interference detected!
Wikia is a free-to-use site that makes money from advertising. We have a modified experience for viewers using ad blockers
Wikia is not accessible if you’ve made further modifications. Remove the custom ad blocker rule(s) and the page will load as expected.
Monitor Current Process
select s.username, s.status, a.first_load_time, executions, elapsed_time, a.sql_text stmt, hash_value, address from v$session s, v$sqlarea a where s.username is not null -- Ignore the Oracle -- background processes and s.audsid <> userenv('SESSIONID') -- Ignore the current -- session and s.sql_address = a.address and s.sql_hash_value = a.hash_value order by s.status
select a.first_load_time, executions, elapsed_time, a.sql_text stmt, hash_value, address from v$sqlarea a where users_executing > 0
elapsed_time is the accumulated microseconds elapsed time used by the SQL
The two views should be granted for select (using sys as sysdba) for the account for running this checking query
grant SELECT on V_$SQLAREA to bdsuser with grant option;
grant SELECT on V_$Session to bdsuser with grant option;
Check Long Running Process
The following SQL order the processing or processed SQLs by running time
SELECT sql_text, first_load_time, elapsed_time, executions, hash_value, address FROM v$sqlarea ORDER BY elapsed_time / Decode(executions, 0, 1, executions) desc;
Check current progress of running statement
select sid, message from v$session_longops order by start_time;
Monitor Execution Plan
Firstly, find out the hash_value and address from v$sqlarea of the sql
and then execute the following:
select * from v$sql_plan where address = ? and hash_value = ?
Monitor SQL Statistics
Check Statistics Options
SELECT system_status, activation_level FROM V$STATISTICS_LEVEL WHERE statistics_view_name = 'V$SQL_PLAN_STATISTICS';
if system_status = 'ENABLED' and activation_level = 'ALL', then it's okay, if not, enable the statistics by (use sys account for instance-wide set):
ALTER SYSTEM SET statistics_level=ALL;
Check Execution Plan Statistics
SELECT * FROM V$SQL_PLAN_STATISTICS where address = ? and hash_value = ?
address and hash_value are found by v$sqlarea