1. SQL 분석 도구

-- ############################################################################
-- SQL PLAN 확인 방법
--
-- 1. DBMS_XPLAN 패키지
-- 2. SQL_Monitor
-- 3. SQLTrace
-- 4. 10046 Event Trace 
-- ############################################################################
 
-------------------------------------------------------------------------------
-- 1. DBMS_XPLAN 패키지
-------------------------------------------------------------------------------
-- (1) 오렌지에서 OPEN 된 CURSOR 가 끝까지 FETCH 되지 못하면 수행통계 분석 불가
-- (2) SQL*PLUS 에서 수행 시 SET SERVEROUT OFF 변경
-------------------------------------------------------------------------------
 
-- Case 1) SQL Hint
select /*+ gather_plan_statistics */ * from small_table;
 
-- Case 2) Session Parameter
alter session set statistics_level = all;
select * from small_table;
 
-- 직전에 수행한 SQL 정보 확인
select prev_sql_id as sql_id
     , prev_child_number as child_no
  from v$session
 where sid = userenv('sid')
   and username is not null
   and prev_hash_value <> 0
;
 
select sql_text from v$sqltext where sql_id = '43sf0ffwbvsmw';
 
select * from table(dbms_xplan.display_cursor('9c9wfnv6rfvj0',null,'allstats last'));
 
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
-- SQL*PLUS 에서 수행 시
SQL> SET SERVEROUT OFF
SQL> select * from small_table;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
 
 
-------------------------------------------------------------
-- 2. SQL_Monitor
-------------------------------------------------------------
-- (1) CPU 및 I/O Time을 5초 이상 소비한 SQL 
-- (2) Parallel SQL
-- (3) monitor Hint 사용
-- (4) _sqlmon_threshold, _sqlmon_max_planlines 등 조정 가능
-- (5) 기타 오렌지 LONG SIZE -> 60000 
-------------------------------------------------------------
 
select /*+ monitor */ * from small_table;
 
select dbms_sqltune.report_sql_monitor(sql_id => '3mt2ujfaytwvm') from dual;
select dbms_sqltune.report_sql_monitor(sql_id => '3mt2ujfaytwvm', type => 'html') from dual;
 
select /*+ monitor */ count(*) from all_objects;
select dbms_sqltune.report_sql_monitor from dual;
 
 
-------------------------------------------------------------
-- 3. SQLTrace
-------------------------------------------------------------
alter session set sql_trace = true;
 
select * from small_table;
 
alter session set sql_trace = false;
 
-- Trace File 확인
select value from v$diag_info where name = 'Diag Trace';
select value from v$diag_info where name = 'Default Trace File';
 
--tkprof
$ tkprof orakim_ora_23276.trc report.txt sys=no
;
 
----------------------------------------------------------------------------------
--call     count       cpu    elapsed       disk      query    current        rows
--------- ------  -------- ---------- ---------- ---------- ----------  ----------
--Parse        2      0.00       0.00          0          0          0           0
--Execute      2      0.00       0.00          0          0          0           0
--Fetch        0      0.00       0.00          0          0          0           0
--------- ------  -------- ---------- ---------- ---------- ----------  ----------
--total        4      0.00       0.00          0          0          0           0
----------------------------------------------------------------------------------
--Parse   : SQL을 파싱하고 실행계획을 생성하는 단계
--Execute : SQL 커서를 실행하는 단계
--Fetch   : 레코드를 실제로 Fetch 하는 단계
--disk    : 디스크로부터 읽은 블록 수
--query   : Consistent 모드로 읽은 버퍼 블록 수
--current : Current 모드로 읽은 버퍼 블록 수
--rows    : 각 단계에서 읽거나 갱신한 건수
 
 
--------------------------------------------------------------------------
-- 4. 10046 Event Trace
--
-- level 12 : Call Statistics + Plan Statistics + 바인드 변수 + 대기이벤트  
--------------------------------------------------------------------------
alter session set timed_statistics = true;
alter session set max_dump_file_size = unlimited;
alter session set tracefile_identifier = 'orakim_01';
alter session set events '10046 trace name context forever, level 12';
 
select * from small_table; 
 
alter session set events '10046 trace name context off';
 
 
-- 다른 세션에 Trace 걸기
begin
    dbms_monitor.session_trace_enable (
        session_id  => 54
      , serial_num  => 44255
      , waits       => true
      , binds       => true
    );
end;
/
 
-- Trace 종료
begin
    dbms_monitor.session_trace_disable (
        session_id  => 54
      , serial_num  => 44255
    );
end;
/