정확한 통계정보 수집은 Optimal Plan을 생성하기 위한 필수 요건이다. 오라클 DBMS에서는 Cost-Based Opmizer 의 완벽한 기능을 위해 다양한 통계정보 수집방법을 구사할 수 있다.
대표적인 예가 Histogram으로 Skew된 Data에 대한 정확한 통계수집이 가능하다.
10g에서는 통계정보 수집을 자동화 framework을 제공하여 DBA들에게 편의성을 제공하기도 했다.
Oracle Database 11g에서는 새로운 통계정보 수집방식을 추가하여 고객의 다향한 Data 특성을 반영하게 되었고 보다 정확한 통계정보를 기반으로 보다 정확한 Optimal Plan의 생성이 가능하게 되었다.
새롭게 추가된 통계정보 수집방식은 “Extended Statistics” 와 “Function-Based Statistics” 이다.
Extended Statistics: 한 테이블 내의 여러 column을 하나의 group으로 묶어 통계정보를 수집하되 column의 상호 연관성까지 파악하여 수집한다.
Function-Based Statistics: 특정 column에 적용된 함수를 적용한 결과에 대한 통계정보를 수집한다.
이 장에서는 11g 의 향상된 통계수집 방법을 설명하고 optimizer가 이러한 통계정보를 어떻게 활용하는지를 테스트를 통하여 확인해 보도록 한다.
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’) 를 조회하면,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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는 자동적으로 결정되기 때문이다. 따라서 11g Optinizer는 column간의 상호연관성에 대한 통게정보를 이용하여 Optimal Plan을 세울 수 있다.
Extended Statistics 장점
상호 연관성이 있는 column들이 AND, Equality 조건으로 조회될 경우, 정확한 Selectivity 예측 Extended Statistics를 이용하여 최적의 SQL Plan 생성 Application 성능향상에 기여
Extended Statistics Commands
Column Group 생성
Create_extended_statistics 함수를 사용하여 column group 을 생성한다. 이 함수의 input parameter 는 다음의 표와 같다.
| Parameter | Description | |||||
| Owner | Schema owner. NULL indicates current schema. | |||||
| Tab_name | Name of the table to which the column group Is being added | |||||
| extension | Columns in the column group | |||||
예를들어, 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;
/
Column Grop 조회
Column group 이름은 show_extended_stats_name 함수를 이용한다.
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
Column Group 제거
Column group을 제거하기 위해서는 drop_extended_stats 함수를 이용한다.
exec dbms_stats.drop_extended_stats(‘sh’,’customers’,'(cust_state_province,country_id)’);
Column Group 모니터링
Column group(multicolumn statistics)의 정보는 user_stats_extensions 를 조회하여 얻는다.
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이 사용되었는지를 조회하는 방법은 다음과 같다.
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
——————————————————————————-
(“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 통계정보를 수집한다.
Function_Based Statistics Commands
Expression Statistics 모니터링
Expression Statistics의 정보는 user_stats_extentions 를 조회하여 얻는다.
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
Expression Statistics 제거
Expression statistics 는 drop_extended_stats를 이용하여 제거한다.
exec dbms_stats.drop_extended_stats(null,’customers’,'(lower(country_id))’);
Selectivity & Cardinality
참고로 SQL Plan을 결정하는데 사용되는 selectivity와 cardinality 개념에 대해 설명한다.
SELECTIVITY
전체 레코드수와 해당 레코드 수의 비율이 selectivity이다.
Selectivity = 해당 레코드 수 /전체 레코드수
사원테이블 1000 개 레포드 중 부서=’인사팀’ 이 그중 10 개 라면 선택도 는 0.01 이다.
Selectivity는 행 집합으로부터의 행들의 일부분을 나타낸다.
행 집합은 기본 테이블, 뷰, 조인이나 GROUP BY의 결과일 수도 있고 행 집합에서 행들의 특정 수를 걸러내는 필터의 역할을 한다.
컬럼에 대한 히스토그램(histogram)이 사용가능 하다면, 유일 값 대신 그것을 사용한다.
히스토그램은 컬럼의 다른 값의 분산도를 저장해 놓는다. 분포가 불균형인 컬럼에 히스토그램을 사용하면, CBO 가 더 낮은 selectivity 를 결정하는데 상당하게 도움을 준다.
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(조회 건수) 확인한다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[oracle@obe11g multistats]$ imp sh/sh file= customers_obe.dmp log=imp.log full=y Import: Release 11.1.0.5.0 - Beta on Mon Sep 10 16:04:56 2007 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.5.0 - Beta With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V11.01.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) . importing SH's objects into SH . importing SH's objects into SH . . importing table "CUSTOMERS_OBE" 630 rows imported Import terminated successfully without warnings. [oracle@obe11g multistats]$ |
check_cardinality.sql
|
1 2 |
select count(*) from customers_obe where country_id = 'US' and cust_state_province = 'CA'; |
check_cardinality 의 조회결과는 다음과 같고 실제 조회건수는 29임을 확인한다.
gather_stats.sql
|
1 |
exec dbms_stats.gather_table_stats(null,'customers_obe', method_opt => 'for all columns size 1'); |
SH.customers_obe의 통계정보를 수집한다.
|
1 2 3 |
SQL> @gather_stats.sql PL/SQL procedure successfully completed. SQL> |
review_stats.sql
|
1 |
select column_name, num_distinct, histogram from user_tab_col_statistics where table_name = 'CUSTOMERS_OBE'; |
통계정보를 조회하여 cust_state_province 와 country_id의 distinct value를 확인한다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SQL> @review_stats 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 COLUMN_NAME NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- CUST_MAIN_PHONE_NUMBER 630 NONE CUST_INCOME_LEVEL 12 NONE CUST_CREDIT_LIMIT 8 NONE CUST_EMAIL 400 NONE 15 rows selected. SQL> |
explain_plan.sql
|
1 2 3 4 5 6 |
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을 생성하여 optimizer가 예상하는 조회건수를 확인한다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> @explain_plan Explained. 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. SQL> |
Opmizer는 조회건수가 1로 예상하고 있다. 이 예상값이 틀리다는 것은 미리 조회해 본 실제조회건수로부터 알 수 있다. 따라서 opimizer가 좀 더 정확한 조회건수를 예상할 수 있도록 더 좋은 통계정보를 생성할 필요가 있다.
Gathering Histograms on Skewed Columns
11g 이전까지는 상호연관된 column들의 selectivity를 정확히 생설하는 방법은 없었다.
다만, skew된 data에 대한 정확한 selecivity는 histogram을 통하여 계산할 수 있었으므로 SH.customers_obe에 histogram을 생성해서 opmizer가 어떤 예상을 하는지 테스트 해 본다.
gather_histogram.sql
|
1 |
exec dbms_stats.gather_table_stats(null,'customers_obe', method_opt => 'for all columns size skewonly'); |
Histogram을 생성한 후의 통계정보를 조회한다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SQL> @gather_histogram PL/SQL procedure successfully completed. SQL> @review_stat 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 COLUMN_NAME NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- CUST_MAIN_PHONE_NUMBER 630 HEIGHT BALANCED CUST_INCOME_LEVEL 12 FREQUENCY CUST_CREDIT_LIMIT 8 FREQUENCY CUST_EMAIL 400 HEIGHT BALANCED 15 rows selected. SQL> |
SQL Plan을 생성하여 optimizer가 예상하는 조회건수를 조사한다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> @explain_plan Explained. 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. SQL> |
Histogram을 생성한 이후 이전보다는 조회건수의 예상치가 좋아지긴 했지만 opimizer는 여전히 상호연관성이 있는 column들의 관계는 알지 못하므로 정확한 조회건수를 예상하지 못했다.
Creating Extended Statistics to Correlate Columns
11g의 신기능인 extended statistics를 수집하여 optimizer가 컬럼간 상호관계를 알게 한후, 조화건수를 어떻게 예상하는지 테스트해 본다.
create_extended_stats.sql
|
1 |
select dbms_stats.create_extended_stats(null,'customers_obe', '(country_id, cust_state_province)') from dual; |
Country_id 와 cust_state_province를 group으로 하는 extended 통계정보를 수집한다.
|
1 2 3 4 |
SQL> @create_extended_stats.sql DBMS_STATS.CREATE_EXTENDED_STATS(NULL,'CUSTOMERS_OBE','(COUNTRY_ID,CUST_STATE_PR -------------------------------------------------------------------------------- SYS_STUJGVLRVH5USVDU$XNV4_IR#4 |
Histogram을 생성한다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SQL> @gather_histogram.sql PL/SQL procedure successfully completed. SQL> @review_stats 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 COLUMN_NAME NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- 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> |
SQL Plan을 생성하여 optimizer가 예상하는 조회건수를 조사한다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> @explain_plan.sql Explained. 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. SQL> |
테스트 결과와 같이 optimizer가 정확한 조회건수를 예상하는 것을 확인할 수 있다.
Creating Extended Statistics for a Function Used on a Column
Column에 함수가 적용된 경우에도 extended statistics를 생성하여 optimizer가 정확한 cardinality를 예상하는를 테스트 한다. 우선, country_id에 lower 함수를 적용한 경우의 실제 cardinality를 조회한다.
get_count_ower.sql
|
1 |
select count(*) from customers_obe where lower(country_id) = 'us'; |
조회건수가 165 이다.
|
1 2 3 4 5 |
SQL> @get_count_lower.sql COUNT(*) ---------- 165 SQL> |
explain_plan_lowercase.sql
|
1 2 3 4 5 6 |
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')); |
SQL Plan을 생성하여 optimizer가 예상하는 조회건수를 조사한다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> @explain_plan_lowercase.sql Explained. 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. SQL> |
테스트 결과처럼 6건이 조회될 것이라고 예상했지만 실제 조회건수와는 큰 차이가 있다.
따라서 lower(country_id) 에 대한 extended statistics를 수집한다.
gather_stats_lower_col.sql
|
1 2 3 4 5 |
exec dbms_stats.gather_table_stats(null,'customers_obe', method_opt => 'for all columns size skewonly for columns (lower(country_id))'); SQL> @gather_stats_gather_col.sql PL/SQL procedure successfully completed. |
테이블의 column 통계정보를 조회해 본다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SQL> @review_col_stats.sql 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 COLUMN_NAME NUM_DISTINCT HISTOGRAM ------------------------------ ------------ --------------- 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> |
SQL Plan을 생성하여 optimizer가 예상하는 조회건수를 조사한다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> @explain_plan_lowercase.sql Explained. 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. SQL> |
함수가 적용된 column에대한 extended statistics를 수집한 후 optimizer가 정확한 cardinality를 예상하는 것을 확인할 수 있다.
Dbms_metadata패키지를 사용하여 SH.customers_obe에 대한 정의를 조회해 보면, lower(country_id)에 대한 system-generated virtual column이 정의되어 있음이 확인된다.
|
1 2 3 4 5 6 |
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 SQL> |
의견
Oracle Database 11g에서는 새로운 통계정보 수집방식을 추가하여 고객의 다향한 Data 특성을 반영하게 되었고 보다 정확한 통계정보를 기반으로 보다 정확한 Optimal Plan의 생성이 가능하게 되었다.
새롭게 추가된 통계정보 수집방식은 “Extended Statistics” 와 “Function-Based Statistics” 이다. Extended Statistics: 한 테이블 내의 여러 column을 하나의 group으로 묶어 통계정보를 수집하되 column의 상호 연관성까지 파악하여 수집한다.
Function-Based Statistics: 특정 column에 적용된 함수를 적용한 결과에 대한 통계정보를 수집한다. 11g Optinizer는 column간의 상호연관성에 대한 통게정보를 이용하여 Optimal Plan을 세울 수 있습니다.