데이터 딕셔너리

개요

데이터 딕셔너리의 정보는 오라클의 테이블 스페이스에 저장된다

1. 파싱 정보 체크

-- 하드 파싱 예제 생성
DECLARE
    v_cnt NUMBER;
BEGIN
    FOR i IN 1..10000 LOOP
        EXECUTE IMMEDIATE 'SELECT count(*) FROM emp WHERE empno =' ||i
        INTO v_cnt;
    END LOOP;
END;
/

-- 위의 pl/sql로 만든 하드 파싱정보 체크
SELECT sql_text, sql_id, parse_calls, executions, plan_hash_value
FROM v$sql
WHERE sql_text LIKE 'SELECT count(*)%';

2. NDV, 밀도 등의 정보

/*
옵티마이저 통계는 DBMS_STATS 패키지 또는 ANALYZE 명령문을 통해 수집 가능하며 수집된 통계 정보는
여러 DICTIONARY VIEW를 통해 내용을 확인할 수 있다.

아래의 EMP 테이블의 SAL 컬럼의 고유값(NDV)는 12 이고, DEPTNO 컬럼의 고유값은 3이다 
*/
SELECT COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY, NUM_NULLS
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'EMP'
ORDER BY COLUMN_ID
;

3. 히스토그램 정보

SELECT column_name, num_distinct, density, num_buckets, histogram
 FROM user_tab_columns 
WHERE table_name = 'SALES' ;

-- 히스토그램 통계 정보 생성: method_opt auto를 통해 버켓 개수 옵티마이저 자동 판단
BEGIN 
  DBMS_STATS.GATHER_TABLE_STATS(ownname       => USER
                               ,tabname       => 'CUSTOMERS'
                               ,method_opt    => 'FOR COLUMNS CUST_CITY SIZE AUTO' 
                               ,no_invalidate => FALSE ) ; 
END ; 
/

4. 인덱스 정보

SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES';

• 아래와 같이 EMPLOYEES테이블의 인덱스 명, 적용된 컬럼명, 결합 인덱스 시 순서번호가 출력된다

5. 세션 권한

/*
CONNECT
RESOURCE
SODA_APP
SELECT_TUNING_PRIVS: 
PLUSTRACE
*/
SELECT * FROM SESSION_ROLES;

-- ROLE에 포함된 객체 권한 확인
SELECT *
FROM ROLE_TAB_PRIVS
WHERE ROLE = 'SELECT_TUNING_PRIVS';

• ROLE_TAB_PRIVS 테이블로부터 SELECT_TUNING_PRIVS 권한의 세부 내용을 출력하면 아래와 같은 결과가 나온다
  

6. 클러스터링 팩터

-- 1. 인덱스 명 확인
SELECT INDEX_NAME, COLUMN_NAME, COLUMN_POSITION
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'CUSTOMERS';

-- 2. 인덱스 명 기반으로 클러스터링 팩터 확인
-- CLUSTERING FACTOR = 51552.. row 수와 근접하므로 안좋다고 판단할 수 있음 
-- 즉, 인덱스보다 TABLE FULL SCAN 선택 (테이블 랜덤 액세스 많기 때문에)
-- 결과값: CUSTS_CITY_IX	1	161	620	**51552
SELECT index_name, blevel, leaf_blocks, distinct_keys, clustering_factor 
  FROM user_indexes 
 WHERE index_name = 'CUSTS_CITY_IX' ;