따봉 구걸남's 블로그

성능뷰에서 SQL 수집하기 

 -- 셀프로 선정 v$sqlarea

SELECT *
FROM (
    SELECT
        parsing_schema_name,
        sql_id,
        plan_hash_value,
        executions,
        buffer_gets,       -- logical reads
        disk_reads,        -- physical reads
        rows_processed,
        cpu_time,          -- total CPU time in microseconds
        elapsed_time,      -- total elapsed time in microseconds
        ROUND(cpu_time / DECODE(executions, 0, 1, executions) / 1000000, 2) AS avg_cpu_sec,
        ROUND(elapsed_time / DECODE(executions, 0, 1, executions) / 1000000, 2) AS avg_elapsed_sec,
        SUBSTR(sql_text, 1, 100) AS sql_text_snippet
    FROM v$sqlarea
    WHERE parsing_schema_name NOT IN ('SYS', 'SYSTEM')
      AND executions > 0
    ORDER BY cpu_time DESC
)
WHERE ROWNUM <= 20;

-- sql full text 확인
SET LONG 100000
SET PAGESIZE 500
SET LINESIZE 150

--SELECT DBMS_LOB.SUBSTR(SQL_FULLTEXT, 100000, 1) AS full_sql_text
SELECT DBMS_LOB.SUBSTR(SQL_FULLTEXT, DBMS_LOB.GETLENGTH(SQL_FULLTEXT), 1)
-- SELECT SQL_FULLTEXT
FROM v$sql
WHERE sql_id = 'gngtvs38t0060';

 -- 바인드 값
SELECT sql_id, name, position, datatype_string, value_string, was_captured, last_captured
  FROM v$sql_bind_capture
 WHERE sql_id = '91gau055wpf2p'
 ORDER BY position;

-- ACS 확인
 SELECT sql_id,
              sql_fulltext,
             IS_BIND_SENSITIVE,
             IS_BIND_AWARE,
             LAST_LOAD_TIME,  
             SQL_PROFILE,
             SQL_PLAN_BASELINE,
             LAST_ACTIVE_TIME
   FROM v$sql
  WHERE sql_id =''