Heatmap
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
◆ 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 (좀더 자세한 정보 조회)
<sourcel lang=sql> 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; </source>
- 실행결과
◆ Heatmap 정보 제거 (Clear)
sys 유저에서 clear_heat_map_all 프로시져를 호출하면 모든 Heatmap 정보가 삭제됩니다.
<sourcel lang=sql>
sqlplus / as sysdba
SQL> exec dbms_ilm_admin.CLEAR_HEAT_MAP_ALL; </source>