개요

Tibero DBMS에서는 지원하는 기능이나 일부 기능을 자체적으로 제한함으로써 성능 저하나 장애를 예방할수 있습니다. 다만, 해당사항들은 DB 관리자의 검토하에 수행하는걸 권고 드립니다.

방법

1. DBMS기능 제한 사항

1.2. 주요 제한 사항

• HINT
• Parallel, Append 의 사용
• Dynamic SQL
• Database Link
• Trigger
• Package & Procedure & Function 
• Foreign Key
• Global Index
• Global Temporary Table
• HINT 의 사용 제한

HINT 를 사용해야 하는 경우 DB담당자와 영향도를 검토 및 사전 협의 후 사용해야합니다.

Parallel 의 경우 운영중 과도한 자원소모를 유발하여 서비스에 영향을 줄수 있습니다

Appen 힌트의 경우 nologging 테이블에 대한 작업내용 롤백이 불가능합니다.

Dynamic SQL 사용 제한

변수를 사용하지 않고 전체를 ‘%s’를 사용하여 여러 번 수행할 경우 성능 저하를 유발하는 Hard Parsing이 발생합니다.

Database Link 사용 제한

Source 서버 장애 발생 시 Target 서버까지 장애가 전파될 수 있습니다.

예상치 못한 오류로 인해 네트워크 장애 시 Session이 자동으로 정리되지 않습니다. 

Database Link 사용 현황 조회방법입니다.

[예제] DB Link 사용 조회

SELECT OBJECT_TYPE, OWNER, OBJECT_NAME 
FROM DBA_OBJECTS 
WHERE OBJECT_TYPE = 'DATABASE LINK'
AND OWNER NOT IN ('SYS','SYSCAT','SYSGIS')

Trigger 사용 제한

1개의 테이블에 insert, update, delete 각 1개씩 최대 3개까지 존재할 수 있으므로 업무 요건 변경 시 또는 대량의 Batch 작업 수행 시 시스템 성능 저하로 이어질수 있습니다.

Parallel Insert 나 Parallel Append의 경우 Trigger가 수행되지 않게 제한합니다.

Trigger 사용 현황 조회방법입니다.

[예제] Trigger 사용 조회 쿼리

SELECT OWNER, TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, TABLE_NAME
FROM DBA_TRIGGERS
WHERE OWNER NOT IN ('SYS','SYSCAT','SYSGIS');

Package & Procedure & Function 사용 제한

종속성이 설정 됨으로 인해 특정 Object에 대해 작업 시 관계되는 Object 에 대한 추가적인 작업이 요구될  수 있습니다.

일반적으로 recursive한 방법 및 다수의 SQL을 사용함에 따라 원인 추적이 번거로우며 업무 처리 도중 
발생되는 오류에 대해 처리가 어렵습니다.

Package & Procedure & Function 사용 현황 조회방법 예입니다.

[예제] PSM 사용 조회 쿼리

SELECT OWNER, OBJECT_TYPE, COUNT(1)
FROM DBA_OBJECTS
WHERE OBJECT_TYPE IN ('PACKAGE', 'FUNCTION','PROCEDURE') 
AND OWNER NOT IN ('SYS','SYSCAT','SYSGIS')
GROUP BY OWNER, OBJECT_TYPE;

Foreign Key 사용 제한

FK를 적용하면 데이터 insert, delete시 반드시 순서대로 해야 합니다.

Insert는 부모 → 자식 순, Delete는 자식 → 부모 순으로 수행해야 한다. 수행 순서가 다를 시 Insert, Delete가 수행되지 않음으로 에러가 발생합니다.

여러 단계의 상속을 거칠 경우 관계가 복잡하기에 관리에 어려움이 발생합니다.

Child 테이블에 대해 Data insert 작업 시 상속 받는 칼럼의 값이 Parent에 존재하는 값인지 확인하기 위해 Select For Update에 해당하는 Trigger가 발생하여 추가적인 자원소모가 발생합니다.

Foreign key 사용의 조회 방법의 예시입니다.

[예제] Foreign Key 사용 조회

SELECT OWNER, CONSTRAINT_TYPE, CONSTRAINT_NAME, TABLE_NAME, DELETE_RULE 
FROM DBA_CONSTRAINTS
WHERE OWNER NOT IN ('SYS','SYSCAT','SYSGIS')
AND CONSTRAINT_TYPE = 'R'

Global Index 사용 제한

특정 파티션에 대한 Drop, Split, Move, Truncate를 수행할 경우 Index 전체가 Unusable 상태가 되
어 작업 대상이 아닌 다른 파티션에 대해서도 영향을 미치므로 성능 저하 및 과도한 리소스 소비로 이어질수 있습니다.

데이터 최적화 정책에 따라 파티션 기법을 사용 할 경우를 위해 반드시 Local Index로 만들어야 한다.

Global Index 사용 현황 조회 방법 예시입니다.

[예제] Global Index 사용 조회

SELECT A.OWNER, A.INDEX_NAME, A.TABLE_NAME, A.INDEX_TYPE 
FROM DBA_INDEXES A, DBA_PART_TABLES B
WHERE A.OWNER = B.OWNER
AND A.OWNER NOT IN ('SYS','SYSCAT','SYSGIS')
AND A.TABLE_NAME = B.TABLE_NAME
AND A.PARTITIONED = 'NO'
UNION ALL
SELECT A.OWNER, A.INDEX_NAME, A.TABLE_NAME, LOCALITY
FROM DBA_INDEXES A, DBA_PART_TABLES B
WHERE A.OWNER NOT IN ('SYS','SYSCAT','SYSGIS')
AND LOCALITY = 'GLOBAL'
ORDER BY 1,2

Global Temporary Table 사용 제한

Global Temporary Table은 Session이 유지될 때 까지만 데이터가 남아있는 휘발성 테이블입니다.

데이터가 없으므로 통계 정보를 수집할 수 없고, 이로인해 악성 실행 계획이 생성되면 성능이 저하될 수 있습니다..

Global Temporary Table 사용 여부를 조회하는 방법입니다.

[예제] Global Temporary 사용 조회

SELECT OBJECT_TYPE, OWNER, OBJECT_NAME, TEMPORARY
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = ‘TABLE’
AND TEMPORARY = 'Y'
AND OWNER NOT IN ('SYS', 'SYSCAT', 'SYSGIS')
AND NOT REGEXP_LIKE(OBJECT_NAME, 'PLAN_TABLE|_TB_STAT_SNAP_', 'i')

2. DBMS OBJECT 점검

2.1. Enable Row Movement 확인

ROW_MOVEMENT가 ENABLE 인 테이블 조회 방법에 대한 예시입니다.

[예제] Enable Row Movement 확인

SQL> 
SELECT OWNER, TABLE_NAME, ROW_MOVEMENT 
FROM DBA_TABLES
WHERE ROW_MOVEMENT = 'ENABLED'
/

2.2. Role 확인

SELECT 권한이 누락된 테이블 조회  방법에 대한 예시입니다.

[예제] Select 권한 누락 조회

SELECT 'GRANT SELECT ON '||A.OWNER||'.'||A.TABLE_NAME||' TO '||'TARGET_ROLE;' 
FROM DBA_TABLES A
WHERE OWNER = 'TIBERO' -- 원하는 유저 설정
AND NOT EXISTS ( SELECT 1 FROM DBA_TAB_PRIVS B
WHERE A.OWNER=B.OWNER
AND A.TABLE_NAME=B.TABLE_NAME
AND B.GRANTEE='TARGET_ROLE'
AND B.PRIVILEGE='SELECT' )

DML 권한이 누락된 테이블 조회  방법에 대한 예시입니다.

[예제] DML 권한 누락 조회

SELECT 'GRANT INSERT, DELETE, UPDATE ON '||A.OWNER||'.'||A.TABLE_NAME||' TO '||'TARGET_ROLE;'
FROM DBA_TABLES A
WHERE OWNER = 'TIBERO' -- 원하는 유저 설정
AND NOT EXISTS ( SELECT 1 FROM DBA_TAB_PRIVS B
WHERE A.OWNER=B.OWNER
AND A.TABLE_NAME=B.TABLE_NAME
AND B.GRANTEE='TARGET_ROLE'
AND B.PRIVILEGE IN ('INSERT','UPDATE','DELETE') )

Role에 부여된 DML, Alter 권한 회수 조회  방법에 대한 예시입니다.

[예제] 권한 회수

SELECT 'REVOKE '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' FROM '||'TARGET_ROLE;'
FROM DBA_TAB_PRIVS
WHERE GRANTEE='TARGET_ROLE'
AND PRIVILEGE IN ('INSERT','UPDATE','DELETE', 'ALTER')

2.3. Synonym 생성 및 삭제

Public Synonym 생성 방법에 대한 예시입니다.

[예제] Public Synonym 생성

SELECT 'CREATE PUBLIC SYNONYM '||'SYNONYM_NAME'||' FOR '||OWNER||'.'||OBJECT_NAME||';'
FROM DBA_OBJECTS A
WHERE OWNER = 'TIBERO' -- 원하는 유저 설정
AND OBJECT_TYPE IN ('TABLE', 'VIEW','SEQUENCE','PROCEDURE', 'FUNCTION', 'PACKAGE') 
AND NOT EXISTS (SELECT 1 FROM DBA_SYNONYMS B
WHERE A.OWNER = B.ORG_OBJECT_OWNER
AND A.OBJECT_NAME = B.ORG_OBJECT_NAME
AND B.OWNER = 'PUBLIC')

Public Synonym 삭제 방법에 대한 예시입니다.

[예제] Public Synonym 삭제

SELECT 'DROP PUBLIC SYNONYM '||SYNONYM_NAME||';'
FROM DBA_SYNONYMS
WHERE ORG_OBJECT_OWNER = 'TIBERO' -- 원하는 시노님의 원본유저 설정
AND OWNER = 'PUBLIC'

2.4. 테이블 PCTFREE 설정 확인

테이블 PCTREE 설정값 조회 방법에 대한 예시입니다.

[예제] PCTFREE 설정 확인

SELECT OWNER, TABLE_NAME,PCT_FREE 
FROM DBA_TABLES
WHERE PCT_FREE != 10
UNION
SELECT OWNER, TABLE_NAME,PCT_FREE 
FROM DBA_TAB_PARTITIONS 
WHERE PCT_FREE != 10
UNION
SELECT OWNER, TABLE_NAME,PCT_FREE 
FROM DBA_TAB_SUBPARTITIONS 
WHERE PCT_FREE != 10

2.5. 월 단위 파티션 점검

현 시점에서 2개월 후의 파티션이 없을 경우 결과로 나타납니다.

[예제] 월 단위 파티션 점검

SELECT OWNER, TABLE_NAME, MAXPART
FROM (
SELECT OWNER, TABLE_NAME, MAX(PARTITION_NAME) AS MAXPART
FROM DBA_TAB_PARTITIONS
WHERE OWNER = 'TIBERO' -- 원하는 유저 설정
AND PARTITION_NAME NOT LIKE '%MAX%'
GROUP BY OWNER, TABLE_NAME
) 
WHERE MAXPART <= 'PT_'|| TO_CHAR(ADD_MONTHS(SYSDATE,2),'YYYYMMDD');

2.6. 연 단위 파티션 점검

현 시점에서 24개월 후의 파티션이 없을 경우 결과로 나타납니다.

[예제] 연 단위 파티션 점검

/* 파티션이름이 PT_년도로 가정(PT_2013)
MAXVALUE의 경우 이름에 MAX라는 단어가 들어간다는 가정
위 2개부분은 상황에 맞게 수정 */ -> 파티션 크리에이트타임으로 조회 안되나

SQL> 
SELECT OWNER, TABLE_NAME, MAXPART
FROM (
SELECT OWNER, TABLE_NAME, MAX(PARTITION_NAME) AS MAXPART
FROM DBA_TAB_PARTITIONS
WHERE OWNER = 'TIBERO' -- 원하는 유저 설정
AND PARTITION_NAME NOT LIKE '%MAX%'
GROUP BY OWNER, TABLE_NAME
) 
WHERE MAXPART <= 'PT_'|| TO_CHAR(ADD_MONTHS(SYSDATE,12),'YYYY')
/

2.7. Index Unable 상태 확인

Unable 상태인 Index Check 하는 방법 예시입니다.

[예제] Index Unusable Check

SELECT OWNER, INDEX_NAME, STATUS FROM DBA_INDEXES
WHERE STATUS != 'VALID'
UNION
SELECT OWNER, INDEX_NAME, STATUS
FROM DBA_IND_PARTITIONS
WHERE STATUS != 'USABLE'
UNION
SELECT OWNER, INDEX_NAME, STATUS
FROM DBA_IND_SUBPARTITIONS
WHERE STATUS != 'USABLE'

2.8. Invalid Object 확인

Invalid Object 컴파일 구문 생성 예시 입니다.

[예제] Invalid Object 확인

SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;' AS "TEXT"
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER') 
UNION ALL
SELECT 'SELECT * FROM '||OWNER||'.'||OBJECT_NAME||' WHERE ROWNUM < 1;' AS "TEXT" 
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE = 'VIEW'

2.9. Invalid Object 컴파일 구문 생성

Invalid Object 컴파일 구문 생성 예시 입니다.

[예제] Invalid Object 확인

SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||' COMPILE;' AS "TEXT"
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TRIGGER') 
UNION ALL
SELECT 'SELECT * FROM '||OWNER||'.'||OBJECT_NAME||' WHERE ROWNUM < 1;' AS "TEXT" 
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
AND OBJECT_TYPE = 'VIEW'

2.10. PK 인덱스이면서 Non Unique인 인덱스

Normal PK index 확인 방법입니다.

[예제] Normal Pk Index Check

-- 쿼리 수정필요
SELECT OWNER, TABLE_NAME, INDEX_NAME 
FROM DBA_INDEXES
WHERE INDEX_NAME LIKE 'PK%'
AND UNIQUENESS != 'UNIQUE'

2.11. No Parallel Logging 설정 변경

테이블의 NOPARALLEL, LOGGING 설정 한는 쿼리 구문 생성 방법 예시 입니다.

[예제] NOPARALLEL, LOGGING 설정 변경

S
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' NOPARALLEL LOGGING;' 
FROM DBA_TABLES
WHERE LOGGING != 'YES' 
OR DEGREE > 1

2.12. 파티션 통계 정보 삭제

인덱스 파티션 통계 정보를 삭제하는 방법 예시입니다.

[예제] Index 파티션 통계 정보 삭제

SELECT 'EXEC DBMS_STATS.DELETE_INDEX_STATS('''||OWNER||''', '''||INDEX_NAME||''', '''||PARTITION_NAME||''');' as "Text"
FROM DBA_IND_PARTITIONS
WHERE LAST_ANALYZED IS NOT NULL

테이블 파티션 통계 정보를 삭제하는 방법 예시입니다.

[예제] 테이블 파티션 통계 정보 삭제

SELECT 'EXEC DBMS_STATS.DELETE_TABLE_STATS('''||OWNER||''', '''||TABLE_NAME||''', '''||PARTITION_NAME||''', CASCADE_INDEXES => TRUE);' 
FROM DBA_TAB_PARTITIONS
WHERE LAST_ANALYZED IS NOT NULL