Overview

정확한 통계정보 수집은 Optimal Plan을 생성하기 위한 필수 요건임.
오라클 DBMS에서는 Cost-BasedOpmizer 의 완벽한 기능을 위해 다양한 통계정보 수집방법을 구사할 수 있음 (ex_ Histogram으로 Skew된 Data에 대한 정확한 통계수집)

Oracle Database 11g에서는 새로운 통계정보 수집방식을 추가하여 고객의 다향한 Data 특성을 반영하게 되었고 보다 정확한 통계정보를 기반으로 보다 정확한 Optimal Plan의 생성이 가능함

• Extended Statistics: 한 테이블 내의 여러 column을 하나의 group으로 묶어 통계정보를 수집하되 column의상호 연관성까지 파악하여 수집함
• Function-Based Statistics: 특정 column에 적용된 함수를 적용한 결과에 대한 통계정보를 수집함

Extended Statistics

예를들어, SH.customers라는 테이블에 cust_state_province 라는 컬럼과 country_id 라는 column이 있음

• 컬럼 cust_state_province의 selectivity는 0.005 이고
• 컬럼 country_id selectivity는 0.1 이다.
• cust_state_province 와 country_id가 where조건절의 equality와 and 조건으로 조회된다면 selectivity는 0.0005(=0.005 X 0.1) 임.

하지만 cust_state_province가 country_id을 결정하는 관계를 갖는다면, 이들의 selectivty는 0.0005가 아니다 .

예를들어, 미국이라는 country_id(52780) 와 캘리포니아라는 cust_state_province('CA') 를 조회하면, 결과는 아래와 같음

SQL> select count(*) from sh.customers wehre cust_state_province='CA';
COUNT(*)
----------
3341

SQL> select count(*) from sh.customers where cust_state_province='CA' and country_id=52780
COUNT(*)
----------
3341

SQL> select count(*) from sh.customers where cust_state_province='CA' and country_id=52775
COUNT(*)
----------
0

왜냐하면, cust_state_province가 결정되면 country_id는 자동적으로 결정되기때문
따라t 11g Optinizer는 column간의 상호연관성에 대한 통게정보를 이용하여 Optimal Plan을 세울 수 있음

1. Extended Statistics 장점

• 상호 연관성이 있는 column들이 AND, Equality 조건으로 조회될 경우, 정확한 Selectivity 예측
• Extended Statistics를 이용하여 최적의 SQL Plan 생성
• Application 성능향상에 기여

2. Extended Statistics Commands

2.1 Column Group 생성

Create_extended_statistics 함수를 사용하여 column group 을 생성한다.
함수의 input parameter 는 다음의 표와 같다

예를들어, SH.customers의 컬럼 cust_state_province 와 country_id 를 group으로 묶으려면:

declare  cg_name varchar2(30);
begin
 cg_name := dbms_stats.create_extended_stats(null,'customers','(cust_state_province',country_id)');
end;
/

2.2 Column Grop 조회

Column group 이름은 show_extended_stats_name 함수를 이용한다.

SQL> select sys.dbms_stats.show_extended_stats_name('sh','customers','(cust_state_province,country_id)') col_group_name 
from dual;

COL_GROUP_NAME
----------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM

2.3 Column Group 제거

Column group을 제거하기 위해서는 drop_extended_stats 함수를 이용한다.

SQL> exec dbms_stats.drop_extended_stats('sh','customers','(cust_state_province,country_id)');

2.4 Column Group 모니터링

Column group(multicolumn statistics)의 정보는 user_stats_extensions 를 조회하여 얻는다.

SQL> Select extension_name, extension
from user_stat_extensions
where table_name='CUSTOMERS';

EXTENSION_NAME EXTENSION
-------------------------------------------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ ("CUST_STATE_PROVINCE","COUNTRY_ID")

Column group에 대한 distinct value와 histogram이 사용되었는지를 조회하는 방법은 다음과 같음.

SQL> select e.extension col_group, t.num_distinct, t.histogram
from user_stat_extensions e, user_tab_col_statistics t
where e.extension_name=t.column_name
and t.table_name='CUSTOMERS';

COL_GROUP NUM_DISTINCT HISTOGRAM
-------------------------------------------------------------------------------
("COUNTRY_ID","CUST_STATE_PROVINCE") 145 FREQUENCY

Function-Based Statistics(Expression Statistics)

11g에서는 where 조건절의 column에 함수가 적용되었다 하더라도, function-based 통계정보를 수집함으로써 정확한 selectivity를 구할 수 있음

예를들어, where 절에 lower(cust_state_province)='ca' 조건이 있을 때

• cust_state_province의 selectivity가 0.005 라면
• lower(cust_state_province) selectivity는 0.005가 아닐 것임.

함수가 적용된 column의 보다 장확한 selectivity를 구하기 위해 Function-Based 통계정보를 수집함

1. Function_Based Statistics Commands

1.1 Expression Statistics 모니터링

Expression Statistics의 정보는 user_stats_extentions 를 조회하여 얻는다.

SQL> select e.extension col_group, t.num_distinct, t.histogram
2 from user_stat_extensions e, user_tab_col_statistics t
3 where e.extension_name=t.column_name
4 and t.table_name='CUSTOMERS';

COL_GROUP NUM_DISTINCT HISTOGRAM
------------------------------------------------------------------------
(LOWER("CUST_STATE_PROVINCE")) 145 FREQUENCY

1.2 Expression Statistics 제거

Expression statistics 는 drop_extended_stats를 이용하여 제거한다.

exec dbms_stats.drop_extended_stats(null,'customers','(lower(country_id))');

2. Selectivity & Cardinality

참고로 SQL Plan을 결정하는데 사용되는 selectivity와 cardinality 개념에 대해 설명한다.

2.1 SELECTIVITY

전체 레코드수와 해당 레코드 수의 비율이 selectivity이다.

Selectivity = 해당 레코드 수 /전체 레코드수

사원테이블 1000 개 레포드 중 부서='인사팀' 이 그중 10 개 라면 선택도 는 0.01 이다.
Selectivity는 행 집합으로부터의 행들의 일부분을 나타낸다.
행 집합은 기본 테이블, 뷰, 조인이나 GROUP BY의 결과일 수도있고 행 집합에서 행들의 특정 수를 걸러내는 필터의 역할을 한다.
컬럼에 대한 히스토그램(histogram)이 사용가능 하다면, 유일 값 대신 그것을 사용한다.
히스토그램은 컬럼의 다른 값의 분산도를 저장해 놓는다.
분포가 불균형인 컬럼에 히스토그램을 사용하면, CBO 가 더 낮은 selectivity 를 결정하는데 상당하게 도움을 준다.

2.2 CARDINALITY

Cardinality 는 행 집합에서 행의 수를 나타낸다.
여기에 행 집합은 기본 테이블, 뷰, 조인이나 GROUP BY 의 결과일 수도 있다.
어떤 쿼리의 수행결과로 나오는 ROWS 로 cardinality 의 계산은

Cardinality = 전체로우수 * Selectivity

예를들어 전체로우수가 1000 이고 selecivity 가 0.01 이다면 cardinality 는 10(=1000 * 0.01) 이다.

실습 예제

SH.customers_obe라는 테이블에 상호연관성이 있는 두개의 column(country_id, cust_state_province)을 equality 조건으로 조회할 경우,
어떤 통계정보를 제공해야 optimizer가 정확한 cardinality를 예상하는지를 테스트해 본다.

Determining Single Column Statistics

테이블 SH.customers_obe의 country_id 가 'US'이고 cust_state_province가 'CA'이 경우의 실제 cardinaltiry(조회 건수) 확인한다.

/**
-- 조회결과는 다음과 같고 실제 조회건수는 29임을 확인
**/
SQL> select count(*)
from customers_obe
where country_id = 'US' and cust_state_province = 'CA';

COUNT(*)
----------
29

/**
-- SH.customers_obe의 통계정보를 수집한다.
**/
SQL> exec dbms_stats.gather_table_stats(null,'customers_obe', method_opt => 'for all columns size 1');

/**
-- 통계정보를 조회하여 cust_state_province 와 country_id의 distinct value를 확인
**/
SQL> select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';

COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
CUST_ID 630 NONE
CUST_FIRST_NAME 450 NONE
CUST_LAST_NAME 400 NONE
CUST_GENDER 2 NONE
CUST_YEAR_OF_BIRTH 66 NONE
CUST_MARITAL_STATUS 2 NONE
CUST_STREET_ADDRESS 630 NONE
CUST_POSTAL_CODE 301 NONE
CUST_CITY 300 NONE
CUST_STATE_PROVINCE 120 NONE
COUNTRY_ID 19 NONE
CUST_MAIN_PHONE_NUMBER 630 NONE
CUST_INCOME_LEVEL 12 NONE
CUST_CREDIT_LIMIT 8 NONE
CUST_EMAIL 400 NONE
15 rows selected.

/**
-- Plan을 생성하여 optimizer가 예상하는 조회건수를 확인
**/
SQL> explain plan for
select *
from customers_obe
where country_id = 'US' and cust_state_province = 'CA';
select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 520139036
---------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 1 |
---------------------------------------------------
8 rows selected.

Opmizer는 조회건수가 1로 예상하고 있다. 이 예상값이 틀리다는 것은 미리 조회해 본 실제조회건수로부터 알 수 있다.
따라서 opimizer가 좀 더 정확한 조회건수를 예상할 수 있도록 더 좋은 통계정보를 생성할 필요함

Gathering Histograms on Skewed Columns

11g 이전까지는 상호연관된 column들의 selectivity를 정확히 생성하는 방법은 없었음.
다만, skew된 data에 대한 정확한 selecivity는 histogram을 통하여 계산할 수 있었으므로 SH.customers_obe에 histogram을 생성해서 opmizer가 어떤 예상을 하는지 테스트 해 본다.

/**
-- Histogram을 생성한 후의 통계정보를 조회한다
**/
SQL> exec dbms_stats.gather_table_stats(null,'customers_obe', method_opt => 'for all columns size skewonly');

/**
--  통계정보를 조회하여 cust_state_province 와 country_id의 distinct value를 확인
**/
SQL> select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';

COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
CUST_ID 630 HEIGHT BALANCED
CUST_FIRST_NAME 450 HEIGHT BALANCED
CUST_LAST_NAME 400 HEIGHT BALANCED
CUST_GENDER 2 FREQUENCY
CUST_YEAR_OF_BIRTH 66 FREQUENCY
CUST_MARITAL_STATUS 2 FREQUENCY
CUST_STREET_ADDRESS 630 HEIGHT BALANCED
CUST_POSTAL_CODE 301 HEIGHT BALANCED
CUST_CITY 300 HEIGHT BALANCED
CUST_STATE_PROVINCE 120 FREQUENCY
COUNTRY_ID 19 FREQUENCY
CUST_MAIN_PHONE_NUMBER 630 HEIGHT BALANCED
CUST_INCOME_LEVEL 12 FREQUENCY
CUST_CREDIT_LIMIT 8 FREQUENCY
CUST_EMAIL 400 HEIGHT BALANCED

/**
-- Plan을 생성하여 optimizer가 예상하는 조회건수를 확인
**/
SQL> explain plan for
select *
from customers_obe
where country_id = 'US' and cust_state_province = 'CA';
select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 520139036
---------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | 8 |
| 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 8 |
---------------------------------------------------

8 rows selected.

Histogram을 생성한 이후 이전보다는 조회건수의 예상치가 좋아지긴 했지만 opimizer는 여전히 상호연관성이 있는 column들의 관계는 알지 못하므로 정확한 조회건수를 예상하지 못함

Creating Extended Statistics to Correlate Columns

11g의 신기능인 extended statistics를 수집하여 optimizer가 컬럼간 상호관계를 알게 한후, 조화건수를 어떻게 예상하는지 테스트해 본다.

/**
-- Country_id 와 cust_state_province를 group으로 하는 extended 통계정보를 수집
**/
SQL> select dbms_stats.create_extended_stats(null,'customers_obe','(country_id, cust_state_province)')
from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'CUSTOMERS_OBE','(COUNTRY_ID,CUST_STATE_PR
--------------------------------------------------------------------------------
SYS_STUJGVLRVH5USVDU$XNV4_IR#4

/**
-- Histogram을 생성
**/
exec dbms_stats.gather_table_stats(null,'customers_obe', method_opt => 'for all columns size skewonly');

/**
--  통계정보를 조회하여 cust_state_province 와 country_id의 distinct value를 확인
**/
SQL> select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';

------------------------------ ------------ ---------------
CUST_ID 630 HEIGHT BALANCED
CUST_FIRST_NAME 450 HEIGHT BALANCED
CUST_LAST_NAME 400 HEIGHT BALANCED
CUST_GENDER 2 FREQUENCY
CUST_YEAR_OF_BIRTH 66 FREQUENCY
CUST_MARITAL_STATUS 2 FREQUENCY
CUST_STREET_ADDRESS 630 HEIGHT BALANCED
CUST_POSTAL_CODE 301 HEIGHT BALANCED
CUST_CITY 300 HEIGHT BALANCED
CUST_STATE_PROVINCE 120 FREQUENCY
COUNTRY_ID 19 FREQUENCY
CUST_MAIN_PHONE_NUMBER 630 HEIGHT BALANCED
CUST_INCOME_LEVEL 12 FREQUENCY
CUST_CREDIT_LIMIT 8 FREQUENCY
CUST_EMAIL 400 HEIGHT BALANCED
SYS_STUJGVLRVH5USVDU$XNV4_IR#4 120 FREQUENCY

16 rows selected.

/**
-- SQL Plan을 생성하여 optimizer가 예상하는 조회건수를 조사한다.
**/
SQL> explain plan for
select *
from customers_obe
where country_id = 'US' and cust_state_province = 'CA';
select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 520139036
---------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | 29 |
| 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 29 |
---------------------------------------------------

8 rows selected.

테스트 결과와 같이 optimizer가 정확한 조회건수를 예상하는 것을 확인할 수 있다.

Creating Extended Statistics for a Function Used on a Column

Column에 함수가 적용된 경우에도 extended statistics를 생성하여 optimizer가 정확한 cardinality를 예상하는를 테스트 한다.

/**
-- 우선, country_id에 lower 함수를 적용한 경우의 실제 cardinality를 조회한다.
**/
SQL> select count(*)
from customers_obe
where lower(country_id) = 'us';

COUNT(*)
----------
165

/**
-- SQL Plan을 생성하여 optimizer가 예상하는 조회건수를 조사한다
**/
SQL> explain plan for
select *
from customers_obe
where lower(country_id) = 'us';
select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 520139036
---------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | 6 |
| 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 6 |
---------------------------------------------------
8 rows selected.

-- 테스트 결과처럼 6건이 조회될 것이라고 예상했지만 실제 조회건수와는 큰 차이가 있다. 

/**
-- 따라서lower(country_id) 에 대한 extended statistics를 수집한다
**/
SQL> exec dbms_stats.gather_table_stats(null,'customers_obe', method_opt => 'for all columns size skewonly for columns (lower(country_id))');


/**
--  통계정보를 조회하여 cust_state_province 와 country_id의 distinct value를 확인
**/
SQL> select column_name, num_distinct, histogram
from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE';

COLUMN_NAME NUM_DISTINCT HISTOGRAM
------------------------------ ------------ ---------------
CUST_ID 630 HEIGHT BALANCED
CUST_FIRST_NAME 450 HEIGHT BALANCED
CUST_LAST_NAME 400 HEIGHT BALANCED
CUST_GENDER 2 FREQUENCY
CUST_YEAR_OF_BIRTH 66 FREQUENCY
CUST_MARITAL_STATUS 2 FREQUENCY
CUST_STREET_ADDRESS 630 HEIGHT BALANCED
CUST_POSTAL_CODE 301 HEIGHT BALANCED
CUST_CITY 300 HEIGHT BALANCED
CUST_STATE_PROVINCE 120 FREQUENCY
COUNTRY_ID 19 FREQUENCY
CUST_MAIN_PHONE_NUMBER 630 HEIGHT BALANCED
CUST_INCOME_LEVEL 12 FREQUENCY
CUST_CREDIT_LIMIT 8 FREQUENCY
CUST_EMAIL 400 HEIGHT BALANCED
SYS_STUJGVLRVH5USVDU$XNV4_IR#4 120 FREQUENCY
SYS_STUYYRO5KJCK7IDGUI37HEGCKQ 19 FREQUENCY

17 rows selected.

/**
-- SQL Plan을 생성하여 optimizer가 예상하는 조회건수를 조사한다
**/
SQL> explain plan for
select *
from customers_obe
where lower(country_id) = 'us';
select plan_table_output
from table(dbms_xplan.display('plan_table',null,'BASIC ROWS'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 520139036
---------------------------------------------------
| Id | Operation | Name | Rows |
---------------------------------------------------
| 0 | SELECT STATEMENT | | 165 |
| 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 165 |
---------------------------------------------------

8 rows selected.

-- 함수가 적용된 column에대한 extended statistics를 수집한 후 optimizer가 정확한 cardinality를 예상하는것을 확인할 수 있다.

/**
-- Dbms_metadata패키지를 사용하여 SH.customers_obe에 대한 정의를 조회해 보면, lower(country_id)에 대한 system-generated virtual column이 정의되어 있음이 확인된다
**/
SQL> select dbms_metadata.get_ddl('TABLE','CUSTOMERS_OBE') from dual;

DBMS_METADATA.GET_DDL('TABLE','CUSTOMERS_OBE')
--------------------------------------------------------------------------------
CREATE TABLE "SH"."CUSTOMERS_OBE"
( "SYS_STUJGVLRVH5USVDU$XNV4_IR#4" NUMB