대메뉴 바로가기 본문 바로가기

데이터 기술 자료

데이터 기술 자료 상세보기
제목 SQL 튜닝에 Dictionary View 활용하기 - Part2
등록일 조회수 4968
첨부파일  

SQL 튜닝에 Dictionary View 활용하기 - Part2

㈜엑셈 컨설팅본부 /DB컨설팅팀 정 동기



개요

SQL 성능을 개선하기 위해서는 판단할 수 있는 근거를 수집하는 작업이 중요하다 . SQL 에 사용 된 관련 테이블 정보 및 인덱스 정보들을 수집하여 종합적으로 판단 해야만 좀더 효율적인 성능 개선을 이끌어 낼 수 있기 때문이다 . 오라클은 이 러한 정보들을 Dictionary View 를 통해서 사 용자에게 전달 하고 있다 . 그 중 이번 화이트 페이퍼에서는 테이블과 인덱스 관련 정보를 토대로 SQL 성능 개선에 어떻게 활용되는 지를 서술하고 있다 .


테이블 , 인덱스 통계를 활용 개선 사례

테이블과 인덱스 관련 정보들은 Dictionary View 를 통해서 살펴 볼 수 있다 . 오라클은 DBA_TABLES, DBA_TAB_COLUMNS, DBA_INDEXES, DBA_IND_COLUMNS, DBA_I ND_EXPRESSIONS 의 View 들을 통해서 관련 정보들을 검색 활용 할 수 있도록 제공하 고 있다 . 그렇다면 테스트를 통해 해당 View 들이 어떻게 활용 되는지를 알아 보도록 하자 .


인덱스 효율성 판단 사례


해당 SQL 의 실행 계획을 살펴 보면 총 추출 건수 가 1 건으로 매우 적지만 TABLE FULL SCAN 을 통하여 정보를 추출하고 있으므로 효율적인지를 판단해 볼 필요성이 있다 . 그렇다면 TABLE FULL SCAN 이 효율적인 것인지 그렇지 않다면 해당 TABLE 에 적합한 인덱스가 존재 하는지 , 인덱스가 존재 한다면 어떠한 인덱스가 효 율적인 지를 따져봐야 할 것이다 . 오라클은 관련 Dictionary View 를 통해서 해당 자료들을 제공하고 있다 .

우선 테이블 인덱스 존재 여부는 DBA_INDEXES 를 통해서 확인 가능하다 .


[Script 3 수행 결과 ]


또한 해당 인덱스의 구성 칼럼 정보들은 DBA_IND_ COLUMNS 를 통해서 살펴볼 수 있다 .


[Script 4 수행 결과 ]


그리고 인덱스 구성 칼럼 의 CARDINALITY 나 DISTINCT VALUE 등을 살펴 보기 위해서는 DBA_TABLES 와 DBA_TAB_COLUMNS 를 통해서 관련 정보를 얻을 수 있다 .

. CARDINALITY = ( DBA_TABLES . NUM _ ROWS . DBA_TAB_COLUMN . NUM _ NULLS ) / DBA_TAB_COLUMNS . NUM _ DISTINCT


[ 활용 Script 1 결과 ]


[ 활용 Script 2 결과 ]


이렇게 해당 Dictionary View 을 통해서 관련 정보들을 제공 받을 수 있다 . 우리는 이러한 정보 들을 활용하여 성능 문제를 개선하기 위한 판단 근거로 활용 할 수 있다 . 그렇다면 해당 SQL 의 관련 정보들을 활용하여 효율성을 판단해 보도록 하자 . 먼저 SQL 의 WHERE 조건 절을 살펴 보 면 검색 조건으로 C1 ~C5 까지 존재 한다 . 또한 TABLE DICT_VIEW_T1 에는 2 개의 인덱스가 존재하는 것을 Dictionary View 를 통해서 확인 하였다 .

먼저 2 개의 인덱스 DICT_VIEW_T1_IDX_01 , DICT_VIEW_T1_IDX_0 2 중 어떠한 인덱스가 효율적인 지를 판단 해 보도록 하자 . 인덱스 DICT_VIEW_T1_IDX_01 은 칼럼 C1 으로 구성되어 있다 . C1 의 CARDINALITY 를 살펴보면 (1000000 . 0) / 1000000 = 1 로 매우 효율적인 것을 확인 할 수 있다 . 즉 TABLE DICT_VIEW_T1 의 NUM_ ROWS 값이 1000000 이고 인덱스 DIC T_VIEW_T1_IDX_01 의 DISTINCT 값이 1000000 이므로 인덱스 평균 추출 건수가 (1000000/1000000 = 1) 약 1 건이라는 것을 판단 할 수 있다 .

이번에는 인덱스 DICT_VIEW_T1_IDX_0 2 의 효율성을 확인해 보도록 하자 . DICT_VIEW_T1_IDX_0 2 의 구성 칼럼 은 C2, C3, C4, C5 로 구성되어 있다 . 인덱스 DICT_VIEW_T1_IDX_0 2 구성 칼럼 에 각각 의 CARDINALITY 는 C2 =(10 000000 . 0) / 2 = 5000000, C3 =(10000000 . 0) / 10 = 1000000, C4 = (10000000 . 0) / 5 = 2000000, C5 = (10000000 . 0) / 10 = 1000000 이다 . 인덱스 DICT_VIEW_T1_IDX_0 2 구성 칼럼 의 CARDINALITY 는 C1 칼럼 에 비해 매우 비효율 적인 것을 확인 할 수 있다 . 다시 말해서 TABLE 의 NUM_ROWS 값이 1000000 이고 인 덱스 DICT_ VIEW_T1_IDX_0 2 의 DISTINCT 값이 10 이므로 1000000/10 = 100000 이므 로 인덱스 평균 추출 건수가 약 100000 건으로 효율적이지 않다는 것을 확인 할 수 있다 . 다시 말해서 인덱스 DICT_VIEW_T1_IDX_0 1 이 훨씬 효율적이라는 것을 판단 할 수 있다 .

그렇다면 실제로 그러한지 DATA 를 통해서 확인해 보도록 하자 .




DICT_VIEW_T1_IDX_01 활용 시 실제 DATA 를 살펴보면 TABLE 총 ROW 수 1000000, 인덱 스 구성 칼럼 C1 의 DISTINCT 값 1000000 이므로 C1 조건으로 검색할 경우 인덱스를 통한 평 균 추출 건수가 1 건 일 것으로 예측 할 수 있으며 Dictionary View 를 통해서 살펴본 것과 동일 결과를 나타내는 것을 살펴 볼 수 있다 .




DICT_VIEW_T1_IDX_0 2 활용 시 실제 DATA 를 살펴보면 TABLE 총 ROW 수 1000000, 인덱 스 구성 칼럼 C2, C3, C4, C5 의 DISTINCT 값이 10 이므로 C2, C3, C4, C5 조건으로 검색할 경우 인덱스를 통한 평균 추출 건수가 100000 건 예측 할 수 있으며 Dictionary View 를 통해 서 살펴본 것과 동일 결과를 나타내는 것을 살펴 볼 수 있다 . 다시 말해 인덱스 DICT _VIEW_T1_IDX_01 활용 시 1 번의 TABLE RANDOM ACCESS, DICT_VIEW_T1_IDX_0 2 활용 시 최대 100000 번의 TABLE RANDOM ACCESS 가 발생 할 수 있으므로 DICT_VIEW_T1_IDX_01 가 훨씬 효율적이라고 판단 할 수 있다 .

마지막으로 해당 인덱스를 활용하여 실제 실행계획을 살펴 보도록 하자 .




인덱스 DICT_VIEW_T1_ IDX_01 를 활용한 SQL 의 실행 계획이다 . 인덱스를 통해 1 건을 추출 후 1 번의 TABLE RANDOM ACCESS 가 발생 하였으며 총 4 BLOCKS 를 READ 하였다 . 인덱스 구성 칼럼 C1 이 UNIQUE 하므로 인덱스 BLOCK READS 량 또한 매우 적은 것을 알 수 있다 .




인덱스 DICT_VIEW_T1_IDX_0 2 를 활용한 SQL 의 실행 계획이다 . 인덱스를 통해 100000 건 을 추출 후 약 3715 - 338 = 3377 번의 TABLE RANDOM ACCESS 가 발생 하였으며 총 3715 BLOCK READS 가 발생하였으며 인덱스 BLOCK READS 량 또한 DICT_VIEW_T1_IDX_0 1 에 비해서 비효율 적인 것을 알 수 있다 .

총 3 가지의 SQL 실행 계획을 정리해 보면 , 첫 번째 TABLE FULL SCAN 하였을 경우 총 3348 BLOCK, 두 번째 인덱스 DICT_VIEW_T1_IDX_0 1 를 활용 하였을 경우 인덱스 3 BLOCK READS 그리고 1 번의 TABLE RANDOM ACCESS, 세 번째 DICT_VIEW_T1_IDX_0 2 를 활용 하였을 경우 인덱스 338 BLOCK READS 와 약 3377 번의 TABLE RANDOM ACCESS 가 발생 하고 있다 . 즉 Dictionary View 를 통해 살펴 본 결과와 동 일하게 인덱스 DICT_VIEW_T1_IDX_0 1 를 활용 했을 때 가장 효율적이라는 것을 실제 실행계획을 통해서 확 인 할 수 있다 .


테이블 , 인덱스 Dictionary View 상세 설명 및 활용 스크립트

위의 개선 사례로 보았듯이 Dictionary View 에는 SQL 성능 을 개선하기 위한 판단 자료로 유용 한 V iew 들이 존재 한다 . DBA_TABLES, DBA_TAB_COLUMNS, DBA_INDEXES, DBA_IND_COLUMNS, DBA_IN D_EXPRESSIONS 등을 활용하여 성능 개선에 유용하게 사용될 수 있다 .

Note. 문서에서 기술될 Dictionary View 의 버전은 11 g R2 버전이다 .


TABLE, INDEX 관련 DBA_* VIEW

SQL 성능 이슈에 도움이 되는 TABLE, INDEX 관련 Dictionary View 인 DBA_TABLES, DBA_TAB_COLUMNS, DBA_INDEXES, DBA_IND_COLUMNS, DBA_IND_EXPRESSIONS 에 관하여 자세히 알아보도록 하자 .


DB A_TABLES

DBA_TABLES 는 데이터베이스에 있는 모든 TABLE 에 관한 정보들을 설명한 VIEW 다 . 이 View 의 중요 컬러 정보는 [ 표 1] 와 같다 .





DBA_TAB_COLUMNS

DBA_TAB_COLUMNS 는 데이터베이스에 있는 Clusters, Tables, Views 의 모든 칼럼 정보들 을 담고 있다 . 이 View 의 중요 칼럼 정보는 [ 표 2] 와 같다 .




DBA_INDEXES

DBA_INDEXES 는 데이터베이스에 존재하는 모든 인덱스 정보를 담고 있다 . 이 View 의 중요 칼럼 정보는 [ 표 3] 와 같다 .




DBA_IND_COLUMNS

DBA_IND_COLUMNS 는 데이터베이스에 있는 Clusters, Tables, Views 의 모든 인덱스 칼럼 정보들을 담고 있다 . 이 View 의 중요 칼럼 정보는 [ 표 4] 와 같다




DBA_IND_EXPRESSIONS

DBA_IND_EXPRESSIONS 는 데이터베이스의 Clusters, Tables, Views 있는 모든 FUNCTION - BASE 인덱스 관련 정보를 담고 있다 . 이 Vie w 의 중요 칼럼 정보는 [ 표 5] 와 같다



2. 결론

이번 기술 백서 에서는 DBA_TABLES, DBA_TAB_COLUMNS, DBA_INDEXES, DBA_IND_COLUMNS 의 간단한 활용 사례와 각각의 구성 칼럼 들에 대하여 알아 보았다 . 이처 럼 오라클은 성능 이슈에 활용 할 수 있는 다양한 Dic ationary View 들을 제공해 오고 있다 . 각 각의 View 들이 갖고 있는 정보들을 숙지하고 활용 한다면 SQL 성능 개선의 판단 자료로 활용 할 수 있을 것이다 .



출처 : (주)엑셈

제공 : DB포탈사이트 DBguide.net