Oracle Monitoring

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

or

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 = ?

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