행위

"Heatmap"의 두 판 사이의 차이

DB CAFE

◆ 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;