비효율 SQL을 찾아내기 위해 FullTableScan 을 찾는 작업을 준비중이다.
실제 실행결과를 가지고 해볼 수 도 있지만
소스코드로 정적으로 도출할 수 있는 방법으로 먼저 접근해보았다.
물론 ORACLE을 대상으로 하고 있으며 타 DB (ORACLE과 구조상 거의 유사한 Tibero는 제외)는
어떻게 유사한 기능을 제공하는지 확인하지 못했다.
기본적으로 Oracle은 SQL문장을 파싱한 뒤에 선정된 Optimizer를 기반으로 실행계획을 세우게 된다.
실행계획은 PLAN_TABLE 에 기록되므로
이 내용을 정리하면 예상되는 실행 경로를 추적하면서 비효율적으로 수행될 가능성을 찾아낼 수 있다.
장점은 다음과 같다.
1) 실행결과 없이 SQL 문장만으로 비효율성을 검사할 수 있다. ( 정적 검사)
2) 추정이 아닌 DBMS가 제공하는 기능을 이용해 높은 정확도를 제공한다.
3) 자동화가 가능함
이슈가 될만한 사항
1) 실행계획과 실제 수행시의 경로는 달라질 수 있다. ( cf-1 참조)
2) TEST DB와 REAL DB의 환경차이로 인해 다른 결과를 얻을 수 있다.
3) FullTableScan이 허용되어야 할 경우도 존재한다. (예를 들어 코드성 테이블이나 의도적으로 FullTableScan을 유도하는 SQL 등 )
이슈에 대한 생각
1) 정적 검사방식의 근본적인 제약이므로 한계를 인정 ( 실행결과 기반 검사로 해결하는게 맞다. )
2) 달라지는게 걱정이라면 명시적인 힌트를 부여해주는 것이 더 바람직하다.
3) 문제가 될 소지가 있는 Table들만을 대상으로 검사를 한정하고, 검사결과에서도 예외를 처리한다.
(마스터성, 히스토리성 테이블, 데이터 증가율이 지나치게 높은 테이블 등이 대상이 될 수 있다.)
확장 아이디어
1) FullTableScan인 경우에 대해 효과적인 인덱스 전략 추천 (이건 가능성 여부를 DBA와 타진해야 할듯.. )
/* 개인적으로는 Hibernate나 nosql 쪽을 해보고 싶은데 기회가 없다. */
기타 참고사항
=====================================
- plan 생성 스크립트 : $ORACLE_HOME/rdbms/admin/utlxplan.sql
cf) SYSTEM tablespace에서 수행하지 않도록 주의 할 것
- plan 실행방법
EXPLAIN PLAN (INTO {NEW_PLAN_TABLE} ) SET STATEMENT_ID = 'plan_id' FOR {SQL}
ex)
EXPLAIN PLAN
SET STATEMENT_ID = 'my_ex_01'
FOR
SELECT * FROM XXX
WHERE Y=100;
- plan table 조회
utlxpls.sql (with serial processing), utlxplp.sql (with parallel execution)
또는 직접 SQL 조회
SELECT * FROM PLAN_TABLE WHERE STATEMENT_ID = :QUERY_ID
CF) RBO 사용시에는 COST 컬럼이 NULL 이 됨
cf-1) PLAN과 달리 실제 수행시에 실행 경로가 변경될 수 있는데, 다음과 같은 조건에 따른다.
- bind variable type
- data volume
- accuracy & timeliness of statistics
- indexing differences
- global parameter setting, static or dynamic modified
- session parameter setting
- plan 사용 절차
1. setup : connection.autocommit = false
2. execute plan : explain plan SET STATEMENT_ID = 'PLAN_ID' for {sql}
3. select plan : select * from PLAN_TABLE WHERE OPERATION = 'TABLE ACCESS' AND STATEMENT_ID ='PLAN_ID'
4. tearDown: connection.rollback; connection.autocommit = true;
reference) Toad Knowledge Xpert
