OEM sql monitoring 类似SQL

不够OEM 全面

select a.inst_id,a.time_since_last_wait_micro,a.last_call_et,a.sql_id,a.sql_hash_value, a.username,a.pdml_enabled,a.pdml_status ,a.prev_sql_id from gv$session a
where a.status='ACTIVE'  and a.sql_id is not null and a.username is not null

select a.inst_id,sid, sql_id, sql_exec_id, to_char(sql_exec_start,'DD-Mon-YY HH24:MI:SS') sql_exec_start, sql_plan_hash_value  plan_hash_value,
elapsed_time,  buffer_gets, disk_reads,a.status--,a.*
from  gv$sql_monitor a
where  --a.sql_exec_start<sysdate-1  and
 a.status not like 'DONE%';
--where sql_id like nvl('&sql_id',sql_id)
 

SELECT DBMS_SQLTUNE.report_sql_monitor_list(type =>'TEXT',report_level => 'ALL') AS report FROM dual;

select *From gv$session gs where gs.blocking_session is not null 

------

How to view the same information from a target database without going through OEM performance to monitor SQL performance:

OEM > targets > databases >select a target database> click on performance. sql monitoring>


 

Solution

Queries are derived from the view V$SQL_MONITOR for OEM, and inbuilt query.

Use query something like below:

select sid, sql_id, sql_exec_id, to_char(sql_exec_start,'DD-Mon-YY HH24:MI:SS') sql_exec_start, sql_plan_hash_value plan_hash_value,

elapsed_time ， buffer_gets, disk_reads

from v$sql_monitor

where sql_id like nvl('&sql_id',sql_id)

Oracle® Database Administration
12c Release 1 (12.1)
16 Monitoring Database Operations

Monitoring Database Operations