Monitoring Commands

   To find long running SQLS

with qduration as ( select ash.sql_exec_id,ash.sql_id,ash.module,ash.sql_plan_hash_value,max(cast(ash.sample_time as date)-cast(ash.sql_exec_start as date))*24*60 elapsed_time,(select username from dba_users 

where user_id=ash.user_id) User_name, ash.user_id,ash.sql_exec_start,min(ash.sample_time) qmin ,max(ash.sample_time) qmax ,

EXTRACT (hour FROM (max(ash.sample_time)-min(ash.sql_exec_start))) HH,

EXTRACT (MINUTE FROM (max(ash.sample_time)-min(ash.sql_exec_start))) MI ,

EXTRACT (SECOND FROM (max(ash.sample_time)-min(ash.sql_exec_start))) SS,

ash.sql_opname

from dba_hist_active_sess_history ash

where ash.sample_time BETWEEN   to_date('01-SEP-2022:21:00:28','DD-MON-YYYY:HH24:MI:SS')

and to_date('02-SEP-2022:12:58:28','DD-MON-YYYY:HH24:MI:SS')

and sql_id is not null

and sql_exec_start is not null

--and ash.user_id in (select user_id from dba_users where username in ('SCOTT'))

group by ash.sql_exec_id,ash.sql_id,ash.sql_plan_hash_value,ash.sql_exec_start,ash.sql_id,ash.module,ash.user_id,ash.sql_opname)

select  qd.sql_id,qd.sql_plan_hash_value,qd.elapsed_time,qd.user_id,qd.sql_exec_start,qd.qmax,qd.qmin,qd.HH,qd.mi,qd.ss,qd.sql_opname,qd.user_name,qd.module,substr(dh.sql_text,1,4000) "SQLTEXT" 

from qduration qd,dba_hist_sqltext dh

where qd.sql_id=dh.sql_id

and qd.sql_opname<>'PL/SQL EXECUTE'

order by 8 desc , 9 desc,10 desc;

To find the SQL_TEXT from history

------------------------------------

select * from dba_hist_Sqltext where sql_id='sdbt';

To find the bind_values from history

select * from dba_hist_sqlbind where sql_id='sdbt' order by last_captured desc;

To find plan hash value  for a sql_id

 select user_id,sql_id, sql_exec_start,sql_plan_hash_value,max(cast(sample_time as date)-cast(sql_exec_start as date))*24*60 elapsed_time from

dba_hist_active_sess_history

where sql_id='5sv64cjnskwd4'

--and user_id in (select user_id from dba_users where username in ('SDBT'))

group by user_id,sql_id,sql_exec_start ,sql_plan_hash_value having max(cast(sample_time as date)-cast(sql_exec_start as date))*24*60 is not null

order by 3 desc;

To find the sequence of SQLS that were running for a particular time (history).

select sql_exec_id,snap_id,instance_number,sql_id,sql_opname,sql_exec_start,sample_time,event,session_id,session_serial#,session_state,blocking_session,blocking_Session_Serial#,

(select username from dba_users where user_id=dba_hist_Active_Sess_history.user_id) usernaeme ,sql_opname,sql_plan_hash_value,

qc_session_id,qc_session_serial#,p1text,p2text,p3text,wait_time,

program,module,machine

from dba_hist_Active_Sess_history where sample_time between

  to_date('02-OCT-2022:20:08:57','DD-MON-YYYY:HH24:MI:SS')

and to_date('02-OCT-2022:23:08:57','DD-MON-YYYY:HH24:MI:SS')

--and sql_id='47vahb2s0m7t2'

--and program like '%P01F%'

--and user_id in (select user_id from dba_users where username in ('SDBT'))

and sql_exec_start is not null

order by sample_time desc;

To check the plans for a sql_id

select * from table(dbms_xplan.display_awr('sdbt'));