Exadata Flash Cache 내용을 조회하는 External Table을 생성해보겠습니다.
먼저 아래 내용으로 fc_content_ext.sh 파일을 작성하고 권한을 설정합니다.
$ vi fc_content_ext.sh
#!/bin/bash
/usr/local/bin/dcli -g /home/oracle/cell_group -l root cellcli -e \
LIST FLASHCACHECONTENT ATTRIBUTES \
dbID, dbUniqueName, objectNumber, tableSpaceNumber, cachedSize, cachedKeepSize, cachedWriteSize, \
columnarCacheSize, columnarKeepSize, hitCount, missCount \
| /bin/sed -e "s/[[:space:]]\+/\|/g" -e "s/://g"
$ chmod 700 fc_content_ext.sh
이어서 아래 명령어로 authorized_keys 파일에 공개 키 파일을 추가합니다.
$ dcli -g cell_group -l root -k
마지막으로 아래와 같이 디렉토리와 External Table을 생성합니다.
-- 1-1
CREATE OR REPLACE DIRECTORY dir_fc_content AS '/home/oracle';
-- 1-2
DROP TABLE fc_content_ext PURGE;
CREATE TABLE fc_content_ext (
cell VARCHAR2(256)
, db_id NUMBER
, db_unique_name VARCHAR2(256)
, object_number NUMBER
, table_space_number NUMBER
, cached_size NUMBER
, cached_keep_size NUMBER
, cached_write_size NUMBER
, columnar_cache_size NUMBER
, columnar_keep_size NUMBER
, hit_count NUMBER
, miss_count NUMBER
)
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY dir_fc_content
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
PREPROCESSOR dir_fc_content:'fc_content_ext.sh'
BADFILE dir_fc_content:'fc_content_ext.bad'
LOGFILE dir_fc_content:'fc_content_ext.log'
FIELDS TERMINATED BY '|'
MISSING FIELD VALUES ARE NULL
)
LOCATION (dir_fc_content:'fc_content_ext.sh')
)
REJECT LIMIT UNLIMITED;
아래 쿼리로 Exadata Flash Cache의 내용을 조회할 수 있습니다.
-- 2
SELECT a.db_id
, a.db_unique_name
, a.object_number
, c.owner
, c.object_name
, c.subobject_name
, c.object_type
, a.table_space_number
, b.name AS tablespace_name
, a.cached_size
, a.cached_keep_size
, a.cached_write_size
, a.columnar_cache_size
, a.columnar_keep_size
, a.hit_count
, a.miss_count
, ROUND (RATIO_TO_REPORT (a.cached_size) OVER () * 100, 2) AS cached_ratio
, ROUND (a.hit_count / NULLIF (a.hit_count + a.miss_count, 0) * 100, 2) AS hit_ratio
FROM (SELECT db_id
, db_unique_name
, object_number
, table_space_number
, SUM (cached_size) AS cached_size
, SUM (cached_keep_size) AS cached_keep_size
, SUM (cached_write_size) AS cached_write_size
, SUM (columnar_cache_size) AS columnar_cache_size
, SUM (columnar_keep_size) AS columnar_keep_size
, SUM (hit_count) AS hit_count
, SUM (miss_count) AS miss_count
FROM fc_content_ext
GROUP BY db_id
, db_unique_name
, object_number
, table_space_number) a
, v$tablespace b
, dba_objects c
WHERE b.ts#(+) = a.table_space_number
AND c.data_object_id(+) = a.object_number
ORDER BY a.cached_size DESC;
아래 쿼리로 Cell 별 Exadata Flash Cache의 현황을 조회할 수 있습니다.
-- 3
SELECT cell
, cached_size
, cached_keep_size
, cached_write_size
, columnar_cache_size
, columnar_keep_size
, hit_count
, miss_count
, ROUND (ratio_to_report (cached_size) OVER () * 100, 2) AS cached_ratio
, ROUND (hit_count / NULLIF (hit_count + miss_count, 0) * 100, 2) hit_ratio
FROM (SELECT cell
, SUM (cached_size) AS cached_size
, SUM (cached_keep_size) AS cached_keep_size
, SUM (cached_write_size) AS cached_write_size
, SUM (columnar_cache_size) AS columnar_cache_size
, SUM (columnar_keep_size) AS columnar_keep_size
, SUM (hit_count) AS hit_count
, SUM (miss_count) AS miss_count
FROM fc_content_ext
GROUP BY cell)
ORDER BY cell;
관련 링크