놀멍

-- 부하 시 wait event 확인 (v$session 대체해서 실시간 성능 이력 분석 (PLAN 포함됨))
------------------------------------------------------------------------------------------------------------------------
select sample_time,session_id,session_serial#,user_id,sql_id,
       sql_plan_operation, sql_plan_options, -- 11g 이상
       event,wait_time,time_waited, blocking_session, p1text,p1,p2text,p2,p3text,p3,
       (SELECT object_name
            FROM dba_objects
           WHERE object_id = current_obj#) "object_name", MODULE, 
       (SELECT sql_text
          FROM v$sqlarea a
         WHERE a.sql_id = h.sql_id)
          AS sql_text    
  from v$active_session_history h --> dba_hist_active_sess_history 
 where 1=1
-- and sample_time between to_date ('20201104 092500', 'yyyymmdd hh24miss') and to_date ('20201104 093000', 'yyyymmdd hh24miss')
   and sample_time between sysdate-(interval '5' minute) and sysdate
--  (interval '5' [year|month|day|hour|minute|second]) 
   and session_type <> 'BACKGROUND' ;
-- and event = 'log file switch (checkpoint incomplete)'

------------------------------------------------------------------------------------------------------------------------
-- 실행중 SQL 확인
------------------------------------------------------------------------------------------------------------------------
-- 6초이상 TABLE FULLSCAN/SORT/GROUPBY
SELECT * FROM V$SESSION_LONGOPS 
--WHERE SOFAR <> TOTALWORK
--AND TIME_REMAINING <> '0'
ORDER BY START_TIME DESC ;

-- TEMP TABLESPACE---> SORT/GROUPBY
SELECT SS.TABLESPACE_NAME
     , SUM((SS.USED_BLOCKS*TS.BLOCKSIZE))/1024/1024 MB
FROM GV$SORT_SEGMENT SS , SYS.TS$ TS 

WHERE SS.TABLESPACE_NAME = TS.NAME

GROUP B
Y SS.TABLESPACE_NAME ;

SELECT * --SUM(BLOCKS*8/1024) MB
FROM V$SORT_USAGE;

SELECT * 
FROM V$SQL
WHERE SQL_ID ='4kux1kp2srdq3';

-- UPDATE/INSERT/DELETE ---> UNDO 
SELECT STATUS, TABLESPACE_NAME, SUM(BYTES)/1024/1024 MB
FROM DBA_UNDO_EXTENTS
WHERE TABLESPACE_NAME LIKE 'UNOD%'
GROUP BY STATUS, TABLESPACE_NAME
ORDER BY STATUS;

-- INDEX 생성시 
SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES/1024/1024 MB
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE='TEMPORARY';