rowsource 통계 수집 부하

rowsource 통계를 수집하면 추가적인 부하가 발생할 수 있습니다.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, 1 AS c2 FROM XMLTABLE ('1 to 10000000');
CREATE INDEX t1_x1 ON t1 (c1);

 

아래와 같이 쿼리를 수행하겠습니다. rowsource 통계를 수집하지 않은 2-1번 쿼리는 6.15초, GATHER_PLAN_STATISTICS 힌트로 rowsource 통계를 수집한 2-2번 쿼리는 6.69초, STATISTICS_LEVEL 파라미터로 rowsource 통계를 수집한 2-3번 쿼리는 8.49초가 소요됩니다.

-- 2-1
SELECT /*+ INDEX(T1) */
       COUNT (c2) AS c2
  FROM t1
 WHERE c1 > 0;

경   과: 00:00:06.15

-- 2-2
SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T1) */
       COUNT (c2) AS c2
  FROM t1
 WHERE c1 > 0;

경   과: 00:00:06.69

-- 2-3
ALTER SESSION SET STATISTICS_LEVEL = ALL;

SELECT /*+ INDEX(T1) */
       COUNT (c2) AS c2
  FROM t1
 WHERE c1 > 0;

ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

경   과: 00:00:08.49

 

아래는 앞선 쿼리의 실행 통계입니다. I/O는 동일하며 CPU used by this session 값에 차이가 있는 것을 확인할 수 있습니다.

-- 3
+-------------------------+----------+----------+----------+------+
|NAME                     |       2-1|       2-2|       2-3|CLASS |
+-------------------------+----------+----------+----------+------+
|CPU used by this session |   255.00 |   274.00 |   392.00 |User  |
|DB time                  |   614.00 |   677.00 |   774.00 |User  |
|consistent gets          |    42.76K|    42.76K|    42.76K|Cache |
|physical reads           |    42.76K|    42.76K|    42.76K|Cache |
+-------------------------+----------+----------+----------+------+

 

이 현상은 _rowsource_statistics_sampfreq 파라미터와 관련이 있습니다. 샘플링 빈도에 따라 행 소스 통계의 정확도에 차이가 있을 수 있습니다.

-- 4
NAME                           VALUE DEFAULT_VALUE DESCRIPTION                                                      
------------------------------ ----- ------------- ---------------------------------------------------------------- 
_rowsource_statistics_sampfreq 128   128           frequency of rowsource statistic sampling (must be a power of 2)

 

_rowsource_statistics_sampfreq 파라미터를 1024로 설정하고 2-3번 쿼리를 수행하면 수행 시간이 8.49초에서 6.72초로 감소합니다.

-- 5-1
ALTER SESSION SET "_rowsource_statistics_sampfreq" = 1024;

-- 5-2
ALTER SESSION SET STATISTICS_LEVEL = ALL;

SELECT /*+ INDEX(T1) */
       COUNT (c2) AS c2
  FROM t1
 WHERE c1 > 0;

ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

경   과: 00:00:06.72