How to view failure time waiting events, problem sql and session access times in the database

This article mainly introduces how to check the failure time waiting events, problem sql and the number of session visits in the database, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot after reading this article.

DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_ACTIVE_SESS_HISTORY displays the history of the contents of the in-memory active session history of recent system activity. This view contains snapshots of V$ACTIVE_SESSION_HISTORY. See "V$ACTIVE_SESSION_HISTORY" for further interpretation details for many of these columns (except SNAP_ID, DBID, and INSTANCE_NUMBER).

View waiting events during failure period, problem sql id, and number of session visits

-- alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss' Select trunc (sample_time, 'mi') tm, sql_id,nvl (event,'CPU'), count (distinct session_id) cnt from dba_hist_active_sess_history where sample_time between to_date (' 2019-08-22 14 mi' 30 group by trunc group by trunc (sample_time, 'mi'), sql_id,nvl (event,'CPU') order by cnt desc

View the wait events related to the sql and the corresponding number of session visits

Select sql_id, nvl (event, 'CPU'), count (distinct session_id) sz from dba_hist_active_sess_history a, dba_hist_snapshot b where sample_time between to_date (' 2019-08-22 1400lv 00') and to_date ('2019-08-22 1500lv 00') and sql_id =' 4ksvn2rgjnhcm' and a.snap_id = b.snap_id and a.instance_number = b.instance_number group by sql_id, nvl (event) 'CPU') order by sz desc

Thank you for reading this article carefully. I hope the article "how to check downtime waiting events, problem sql and session visits in the database" shared by the editor will be helpful to you. At the same time, I hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!