'통계정보'에 해당되는 글 1건

  1. 2009/04/07 [ORACLE] Oracle과 통계정보

[ORACLE] Oracle과 통계정보

 DBA가 아니라면 크게 와 닿지 않는 것이 통계정보(statistics)이다. 통계정보의 용도는 oracle의 optimizer가 올바른 query plan을 짤수 있도록 정보를 제공하는 것이다. 즉, 이 query를 1번 index를 이용해서 실행할 것인지, 2번 index를 이용할지, 그도 아니면 full table scan을 할 것인지를 결정하는데 통계정보를 활용한다. 이 말은 이 통계정보가 잘못되어 있다면 DB의 성능잇슈를 불러오게 될 것이란 말이 된다.

 일반적으로 데이터의 수가 많지 않으면, 아무리 full table scan을 하더라도 성능문제는 일어나지 않는다. 왠만큼 데이터가 쌓이면 성능문제가 일어나고 이 시점에선 적절한 index를 생성해주면 성능문제는 사라진다. 하지만 여기서 더 데이터가 쌓이게 되고, 필요한 index의 수가 많아지면 얘기가 달라진다. 잘못된 index를 선택하는 것은 full table scan을 하는 것보다 성능이 느리다. 이 때 올바른 index를 선택 할 수 있도록 정보를 제공하는 것이 통계정보의 용도이다.

 그렇다면 이 통계정보는 어떤때에 갱신되어야 할까? 대량의 정보(10g에서는 10%정도가 바뀌면 통계정보를 갱신해야 할 것으로 판단한다)가 갱신되거나(insert, delete, update), 새로운 index가 추가되었을때는 갱신할 필요가 있다(무조건 해야하는 것은 아니다. 데이터양이 많지 않다면 크게 문제되진 않는다. 0.01초나 0.001초나 산술적으론 10배차이지만 체감속도엔 큰 차이가 없다).

 통계정보는 언제나 갱신되는 것이 좋은가? 결론은 아니다. 통계정보의 갱신은 SGA안의 모든 SQL Cursor의 validation을 무너뜨린다. 그에 따라 갱신된 이후부터 들어오는 모든 SQL에 대해 hard parse를 불러오게 되고, 결과적으로 이 시점에서 DB성능의 문제를 야기한다(이 시점 - hard parse를 야기하는 - 을 넘긴다면 성능은 좋아질 수도 있다). 그렇다고 통계정보를 갱신하지 않을수도 없다.

 9i에서는 통계정보의 갱신으로 모든 SQL이 hard parse되는 사태를 막기 위해, analyze option에 no_invalidate option(true or false)를 제공했다. false는 invalidate 시키지 않고, age-out(oracle은 새로운 SQL을 SGA에 올리고, 그때가지 SGA에 들어 있는 SQL중에서 사용된지 가장 오래된 SQL을 SGA에서 제거한다. 이를 age-out이라 한다)될때가지 기다린 후에 새로 load될때 새로운 통계정보로 plan을 활용하게 되고, true는 즉시 invalidate시키고 hard parse를 일으킨다. 이런 방식으로 hard parse가 일순간에 몰리는 현상을 분산시킬 수 있지만, 이 옵션은 false로 했을때, age-out 되는 시점을 알아내기가 어렵다는 단점이 있다(즉, 그냥 기다려야 한다).

 10g에서는 유사한 문제(hard parse 문제)를 피하기 위해서, 갱신된 통계정보를 즉시 반영하지 않는다. 대신dbms_stats.auto_invalidate 옵션을 통해서 반영되는 시간을 결정할 수 있다(default  5시간). 이는 9i에서 반영시점을 DBA가 결정하지 못해서 생겼던 문제들을 위한 해결책이라 할 수 있다. DBA는 통계정보는 언제나 갱신 할 수 있고, DB의 사용시간이 적은 시간대를 통해 hard parse를 발생시킬 수 있다. 또는, 지연시간을 유연하게 분배해서 hard parse가 일어나는 시점을 적절하게 분배할 수 있다.

10g는 기본적으로 자동화된 통계수집을 제공한다(자동으로 통계정보를 갱신한다는 뜻). 이를 제어할 수 있는 방법은 다음과 같다(dbms_stats 패키지 활용).
 
1. 특정 owner나 object의 통계정보 수집 중지
EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('owner name', 'table name');

EXECUTE DBMS_STATS.LOCK_SCHEMA_STATS ('owner name');


2. 통계수집 자체를 중지
exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');

3. 수집된 통계정보를 즉시 반영

alter system flush shared_pool;


또, 10g에서 dbms_stats. 패키지로 갱신되는 통계정보는 history 형태로 SGA에 저장하며 mmon은 주기적으로(default 60분) 이를 스냅샷 형태로 AWR에 저장한다. 이 AWR은 SYSAUX 테이블 스페이스에 존재하며,

$ORACLE_HOME/rdbms/admin/awrrpt.sql 를 이용해서 정보를 조회할 수 있다.

'IT > ORACLE' 카테고리의 다른 글

[Oracle] oradebug session trace 뜨기  (2) 2009/04/27
최근에 DML 문이 수행된 시간을 알아내기  (0) 2009/04/14
[ORACLE] Oracle과 통계정보  (0) 2009/04/07
[ORACLE] Lock에 관련된 몇가지 쿼리  (1) 2009/03/13
오라클 NLS_*에 관한 잡담.  (2) 2009/02/10
감리때 유용한 몇가지 Oracle 쿼리  (0) 2009/02/04
트랙백0개 댓글0개