실행중 SQL 확인 (SORT/GROUPBY/INDEX )

------------------------------------------------------------------------------------------------------------------------
-- 실행중 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';