오라클 Heatmap 정보 조회 및 Clear 하는 방법

Oracle Heatmap 기능을 On 했을때 ( alter system set heat_map=ON; ) 참조할 수 있는 Heatmap 관련 스크립트입니다.

 

◆ Heatmap 정보 조회

 

* 스크립트 #1

SQL>
set lines 150
col owner              format a10;
col object_name        format a20;
col subobject_name     format a15;
col segment_read_time  format a20;
col segment_write_time format a20;
col full_scan          format a20;
col lookup_scan        format a20;
 
select owner, object_name, subobject_name, 
       segment_read_time, segment_write_time, full_scan, lookup_scan
from dba_heat_map_segment;
 

 

* 실행결과

 

시분초까지 보려면 alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss'; 수행후 실행하면 됩니다.

 

 

* 스크립트 #2 (좀더 자세한 정보 조회)

SQL>
set lines 150
col owner           format a10;
col object_name     format a20;
col subobject_name  format a15;
col track_time      format a20;
col segment_write   heading 'SEG|WRITE'       format a10;
col segment_read    heading 'SEG|READ'        format a10;
col full_scan       heading 'FULL|SCAN'       format a10;
col lookup_scan     heading 'LOOKUP|SCAN'     format a10;
col n_fts           heading 'NUM FULL|SCAN'   format 99999999;
col n_lookup        heading 'NUM LOOKUP|SCAN' format 99999999;
col n_write         heading 'NUM SEG|WRITE'   format 99999999;
 
select
  owner,
  object_name,
  subobject_name,
  to_char(track_time,'yyyy/mm/dd hh24:mi:ss') track_time,
  segment_write,
  segment_read,
  full_scan,
  lookup_scan,
  n_write,
  n_fts,
  n_lookup
from
  sys."_SYS_HEAT_MAP_SEG_HISTOGRAM" h,
  dba_objects o
where o.object_id = h.obj#
order by owner, object_name, subobject_name, track_time;
 

 

* 실행결과

 

 

◆ Heatmap 정보 제거 (Clear)

 

sys 유저에서 clear_heat_map_all 프로시져를 호출하면 모든 Heatmap 정보가 삭제됩니다.

 
sqlplus / as sysdba
 
SQL> exec dbms_ilm_admin.CLEAR_HEAT_MAP_ALL;
 

 

 

▶▶▶ 오라클 Heatmap, ADO, ILM 기능 설명