AWR 리포트의 Flash Cache Space Usage 항목은 DBA_HIST_CELL_GLOBAL 테이블의 데이터로 flash cache의 사용량을 비율로 표시합니다.
19.19 버전부터 Default OLTP에 synced dirty data를 표시하는 %Synced 값이 추가되었습니다. 이번 버전까지는 %Unflushed 값에 %Synced 값이 포함되어 있었습니다.
아래 쿼리로 현재 Flash Cache Space Usage 정보를 조회할 수 있습니다.
SELECT cell_name
, ROUND (space / POWER (1024, 3), 2) AS space
--
, ROUND (def_oltp_clean / POWER (1024, 3), 2) AS def_oltp_clean
, ROUND (def_oltp_synced / POWER (1024, 3), 2) AS def_oltp_synced
, ROUND (def_oltp_unflushed / POWER (1024, 3), 2) AS def_oltp_unflushed
, ROUND (def_lw_temp_spill / POWER (1024, 3), 2) AS def_lw_temp_spill
, ROUND (def_lw_data_temp / POWER (1024, 3), 2) AS def_lw_data_temp
, ROUND (def_lw_write_only / POWER (1024, 3), 2) AS def_lw_write_only
, ROUND (def_scan / POWER (1024, 3), 2) AS def_scan
, ROUND (def_columnar / POWER (1024, 3), 2) AS def_columnar
, ROUND (keep_oltp_clean / POWER (1024, 3), 2) AS keep_oltp_clean
, ROUND (keep_oltp_unflushed / POWER (1024, 3), 2) AS keep_oltp_unflushed
, ROUND (keep_scan / POWER (1024, 3), 2) AS keep_scan
, ROUND (keep_columnar / POWER (1024, 3), 2) AS keep_columnar
--
, ROUND (def_oltp_clean / space * 100, 2) AS def_oltp_clean_rt
, ROUND (def_oltp_synced / space * 100, 2) AS def_oltp_synced_rt
, ROUND (def_oltp_unflushed / space * 100, 2) AS def_oltp_unflushed_rt
, ROUND (def_lw_temp_spill / space * 100, 2) AS def_lw_temp_spill_rt
, ROUND (def_lw_data_temp / space * 100, 2) AS def_lw_data_temp_rt
, ROUND (def_lw_write_only / space * 100, 2) AS def_lw_write_only_rt
, ROUND (def_scan / space * 100, 2) AS def_scan_rt
, ROUND (def_columnar / space * 100, 2) AS def_columnar_rt
, ROUND (keep_oltp_clean / space * 100, 2) AS keep_oltp_clean_rt
, ROUND (keep_oltp_unflushed / space * 100, 2) AS keep_oltp_unflushed_rt
, ROUND (keep_scan / space * 100, 2) AS keep_scan_rt
, ROUND (keep_columnar / space * 100, 2) AS keep_columnar_rt
FROM (SELECT cell_name
--
, total AS space
--
, (oltp - oltp_keep) - ((unflushed - unflushed_keep) - lw) AS def_oltp_clean
, synced_dirty AS def_oltp_synced
, (unflushed - unflushed_keep) - lw - synced_dirty AS def_oltp_unflushed
, lw_temp_spill AS def_lw_temp_spill
, lw_data_temp AS def_lw_data_temp
, lw_write_only AS def_lw_write_only
, (total - keep) - (oltp - oltp_keep) - lw - (columnar - columnar_keep) AS def_scan
, (columnar - columnar_keep) AS def_columnar
--
, oltp_keep - unflushed_keep AS keep_oltp_clean
, unflushed_keep AS keep_oltp_unflushed
, keep - oltp_keep - columnar_keep AS keep_scan
, columnar_keep AS keep_columnar
FROM (SELECT cell_name
--
, NVL (SUM (CASE WHEN metric_id = 202 THEN metric_value END), 0) AS total
, NVL (SUM (CASE WHEN metric_id = 183 THEN metric_value END), 0) AS keep
--
, NVL (SUM (CASE WHEN metric_id = 311 THEN metric_value END), 0) AS oltp
, NVL (SUM (CASE WHEN metric_id = 310 THEN metric_value END), 0) AS unflushed
, NVL (SUM (CASE WHEN metric_id = 300 THEN metric_value END), 0) AS columnar
--
, NVL (SUM (CASE WHEN metric_id = 313 THEN metric_value END), 0) AS oltp_keep
, NVL (SUM (CASE WHEN metric_id = 312 THEN metric_value END), 0) AS unflushed_keep
, NVL (SUM (CASE WHEN metric_id = 308 THEN metric_value END), 0) AS columnar_keep
--
, NVL (SUM (CASE WHEN metric_id = 388 THEN metric_value END), 0) AS synced_dirty
, NVL (SUM (CASE WHEN metric_id = 370 THEN metric_value END), 0) AS lw_data_temp
, NVL (SUM (CASE WHEN metric_id = 371 THEN metric_value END), 0) AS lw_write_only
, NVL (SUM (CASE WHEN metric_id = 372 THEN metric_value END), 0) AS lw_temp_spill
, NVL (SUM (CASE WHEN metric_id IN (370, 371, 372) THEN metric_value END), 0) AS lw
FROM (SELECT EXTRACTVALUE(XMLTYPE (b.confval), '/cli-output/context/@cell') AS cell_name
, a.metric_id
, a.metric_value
FROM v$cell_global a
, v$cell_config b
WHERE a.metric_id IN (
-- 182 -- Flash cache bytes used
183 -- Flash cache bytes used - keep objects
, 202 -- Flash cache bytes allocated
-- , 203 -- Flash cache bytes used for OLTP data
, 300 -- Flash cache bytes used - columnar
, 308 -- Flash cache bytes used - columnar KEEP
, 310 -- Flash cache bytes allocated for unflushed data
, 311 -- Flash cache bytes allocated for OLTP data
, 312 -- Flash cache bytes allocated for unflushed keep objects
, 313 -- Flash cache bytes allocated for OLTP keep objects
, 370 -- Flash cache bytes allocated - large writes (data and temp tables)
, 371 -- Flash cache bytes allocated - large writes (write only)
, 372 -- Flash cache bytes allocated - large writes (temp spill)
, 388 -- Flash cache bytes allocated for synced dirty data
)
AND b.cellname = a.cell_name
AND b.conftype = 'CELL')
GROUP BY cell_name))
ORDER BY cell_name;