오라클 통계정보의 중요성 (Importance of Oracle Statistics)

Oracle DBMS 에서 통계정보는 매우 중요합니다. 

DBMS 에서 실행되는 모든 SQL 들은 Oracle Optimizer 에 의해서 실행계획(Execution Plan)이 만들어진 다음에 그 실행계획에 맞추어서 실행됩니다.

같은 SQL 이라도 Oracle Optimizer 가 이 실행계획을 어떻게 잘 만드느냐에 따라 SQL이 빨리 실행되기도 하고, 엄청 느리게 실행되기도 합니다.

 

옛날 Oracle 7, 8 버전에서는 Rule Based 방식의 옵티마이저(Optimizer)를 사용하는 경우에는 테이블의 정보와 상관없이 이미 정해진 Rule 에 의해 실행계획(Execution Plan)이 만들어졌기 때문에 통계정보가 중요하지 않았었습니다.

그 Rule 이라는게 SQL 문장에서 = 연산자를 사용하는지, between 같은 Range 연산자를 사용하는지에 따라 어떤 것이 더 우선순위가 높은지에 대한 것들입니다.

 

하지만, 이제는 강제로 /*+ rule */ 힌트를 주지 않는 이상은 디폴트로 전부 Cost Based 방식의 옵티마이저를 이용합니다.

즉, 옵티마이저가 실행계획을 세울때 테이블, 인덱스의 통계정보(Stats Info)를 사용한다는 의미입니다.

따라서 통계정보가 개판이면 옵티마이저도 완전 바보처럼 행동합니다. ^^;

오라클 엔지니어들이 통계정보의 중요성을 입에 침이 마르도록 강조하는 이유입니다.

 

 

아래처럼, USER_TABLES 딕셔너리를 조회하면 num_rows 와 last_analyzed 항목을 통해서 해당 테이블에 통계정보가 만들어졌는지를 확인할 수 있습니다.

 

 

위의 경우, EMP 테이블은 통계정보가 없습니다.

통계정보를 생성하는 커맨드(analyze 또는 dbms_stats)를 수행하면 USER_TABLES 딕셔너리의 num_rows 와 last_analyzed 에 값이 세팅됩니다.

 

Optimizer는 이런식으로 테이블에 레코드 갯수가 몇개인지를 알 수 있기때문에 Full Table Scan 을 할지 Index Scan 을 할지, 또는 조인의 경우 Hash Join 을 할지, Nested Loop Join 을 할지를 결정할 수 있는 것입니다.

 

 

그런데, 통계정보는 처음 1번 생성하면 끝나는게 아닙니다.

테이블이 그닥 변화가 없다면 상관없지만, 대량으로 데이터가 추가로 Insert 되었거나, 삭제된 경우에는 통계정보를 갱신(다시 생성) 해줄 필요가 있습니다.

 

그렇지 않으면, 아래와 같은 불상사가 생길 수 있습니다.

 

 

위에서는 EMP_BIG_IDX1 인덱스를 스캔하면서 Optimizer는 8192 건 정도가 있는 것으로 예상하고 Plan 을 수립했습니다. 하지만, 막상 SQL문을 실행해보니 실제로는 Rows(Actual) 에 보이는 것처럼 65536 건의 데이터를 Scan 하게 되었습니다. 아주 큰 차이가 있는 것이고, 통계정보가 개판임을 나타내고 있습니다.

이런 경우, 통계정보가 개판이기 때문에 Optimizer 도 개처럼 행동합니다. SQL도 개판으로 수행됩니다. ㅎㅎ

 

위와 같이 Rows Estim (예측건수) 와 Rows Actual (실제건수) 를 확인하려면, Realtime SQL Monitor 를 사용해서 Plan 을 뜨면 됩니다.

 

* Realtime SQL Monitor 는 여길 참고 ==> 오라클 Plan, Trace 쉽게 보는 - Realtime SQL Monitoring

 

 

그럼, 테이블이나 인덱스가 변경될 때마다 매번 통계정보를 갱신해줘야 하는데, Oracle 은 이걸 자동으로 하도록 설계되어 있습니다. DBA가 특별히 건들지 않았다면, 매일 저녁 10시 이 Autotask 작업이 돌아갑니다.

 

* Oracle DB Autotask 는 여길 참고 ==> 오라클 DB Autotask 와 Maintenance Window