과거의 version에서는 통계정보가 수집이 되면 바로 SQL Plan이 바뀔 수가 있었다.

 

일반적으로는 같은 조건에서 같은 방식으로 통계정보를 수집하는 경우엔 새로이 생성된 SQL Plan은 기존의 것과 동일할 것이다.

하지만, 경우에 따라서는(예: 통계정보의 수집방식 다른 경우, Index가 추가된 경우 등) 반드시 더 좋은 SQL Plan이 생성된다고 보장될 수 없었기 때문에 테스트 서버에서 먼저 수행하여 SQL Plan을 검증했다.

테스트 서버에서 검증된 SQL이 본 장비에서도 같은 Plan으로 풀린다는 가정은 테스트 DB(또는 Stage DB)와 운영 DB가 완전히 같다야 한다는 것을 전제로 하지만, 대부분의 경우 테스트 환경과 실 운영환경은 다르다.

 

Oracle 11g에서 새롭게 추가된 통계정보 반영을 수집과 분리 시킨 “Pending Statistics”라는 통계정보 관리방법을 이용하면, 새로운 통계정보에 대한 검증작업을 실 운영 DB에 영향을 미치지 않으면서도 실 운영환경 하에서 수행할 수 있게 되었다.

 

Oracle 11g는 통계정보를 수집할 때 두 가지 옵션이 있다.

 

기존 pre-11g 방식처럼 통계정보가 수집되자마자 바로 Plan이 update되어 SQL실행계획에

영향을 미치는 방법 통계정보가 수집되자마자 바로 SQL실행계획에 반영하지 않고 pending statistics로 저장하는 방식

11g에서는 통계정보의 수집 (Gathering) 과 반영(Publish) 를 분리할 수 있게 됨으로써 기존의 통계수집의 불안함을 극복하고

실 운영환경에서 테스트를 통하여 검증된 통계정보만이 Publish 되도록 하여 애플리케이션의 성능을 보장할 수 있게 되었다.

 

Pending Statistics

통계정보 Gathering 및 Publish 절차

수집된 통계정보를 pending statistics로 관리하고 publish 되는 과정은 다음과 같다.

 

image

 

Pending Statistics 장점

 

 실 운영환경 하에서 테이블에 대한 통계정보를 미리 Test 해 볼 수 있다. 통계정보에 대한 검증작업에 대한 신뢰도가 향상되었다. 검증된 통계정보만 Publish 함으로써 Application의 성능을 유지 및 개선할 수 있다.

 

통계정보 Gathering 및 Publish Commands

Publish Attribute 확인방법

SQL> select dbms_stats.get_prefs(‘PUBLISH’) publish from dual;

TRUE: 통계정보가 수집되지마자 바로 Publish
FALSE: 수집된 통계정보는 Publish 되지 않고 Pending

 

Publish 된 통계정보는 user_tab_stats, user_ind_stats에 저장되고, pending 된 통계정보는 user_tab_pending_stats, user_ind_pending_stats에 저장된다.

 

 

Publish Attribute 설정을 변경하는 방법

Publish 속성변경은 Schema Level 또는 Table Level에서 변경 가능하다.

아래의 예는 SH.CUSTOMERS의 Publish 속성을 false로 변경하여 이후 통계정보가 수집되더라도 publish되지 않고 user_tab_pending_stats에 저장된다.

SQL> Exec dbms_stats.set_table_prefs(‘SH’, ‘CUSTOMERS’, ‘PUBLISH’, ‘false’);

 

Pending 통계정보를 사용하는 방법

옵티마이져는 default로 Publish된 통계정보를 사용한다.

인위적으로 pending 통계정보를 사용토록 하기 위해서는 init파일에 OPTIMIZER_PENDING_STATISTICS=TRUE 으로 설정하거나, session level에서 다음과 같이 변경한다.

SQL> alter session set optimizer_pending_statistis=TRUE;

 

Pending 통계정보를 Publish 하는 방법

모든 valid pending 통계정보를 Publish

SQL> Exec dbms_stats.publish_pending_stats(null, null);

 

특정 object에 대한 pending 통계정보를 Publish

SQL> Exec dbms_stats.publish_pending_stats(‘SH’, ‘CUSTOMERS’);

 

참고로, pending 통계정보는 dbms_stats.export_pending_stats 함수를 사용하여 export하여 test 서버에 import하여 test를 수행할 수도 있다.

 

예제

통계정보 변경에 대한 preference값을 변경하고, pending statistics를 수집하여 SQL Plan을 검증하고, 기존의 plan보다 더 나는 SQL Plan 라는 판단이 되면 publish 하여 실 운영 DB에 반영하는 테스트이다.

Changing Global and Table Statistic Preferences

예제 실습을 위한 테이블 Import 및 확인

[oracle@obe11g gathstats]$ imp sh/sh file=customers_obe.dmp log=log full=y
Import: Release 11.1.0.5.0 - Beta on Wed Sep 12 09:59:15 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 gathstats]$
SQL> desc CUSTOMERS_OBE
Name Null? Type
----------------------------------------- -------- ----------------------------
CUST_ID NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER CHAR(1)
CUST_YEAR_OF_BIRTH NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_STATE_PROVINCE VARCHAR2(40)
COUNTRY_ID NOT NULL CHAR(2)
CUST_MAIN_PHONE_NUMBER VARCHAR2(25)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)
SQL>
SQL> select count(*) from CUSTOMERS_OBE;
COUNT(*)
----------
630

 

통계정보 수집을 위한 default preferences 또는 parameter setting 을 변경 한다.

그 중 주요 설정값은 STALE_PERCENT.STALE_PERCENT 이다.

이 설정값은 테이블의 통계정보를 재 수집해야 하는 rows  변경량(%)을 지정하는 것으로 rows변경량이 STALE_PERCENT 이상이면 Table의 통계정보의 상태는 STALE이 되어 통계정보 재수집 대상이 된다.

check_sales_pref.sql

connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;

 

현재 ‘STALE_PERCENT’ 에 대한 Default 값 10 이 설정된 것을 확인 할 수 있다.

SQL> @check_sales_pref.sql
SQL> connect sh/sh
Connected.
SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
2 from dual;
STALE_PERCENT
--------------------------------------------------------------------------------
10
SQL>

 

Global STALE_PERCENT 값을 13 으로 변경한다.

change_global_pref.sql

connect / as sysdba
execute dbms_stats.set_global_prefs('STALE_PERCENT', '13');
SQL> @change_global_pref.sql
SQL> connect / as sysdba
Connected.
SQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', '13');
PL/SQL procedure successfully completed.
SQL>

 

Global STALE_PERCENT 값 변경으로 STALE_PERCENT 값이 13 으로 변경 된 것을 확인 할수 있다.

SQL> @check_sales_pref.sql
SQL> connect sh/sh
Connected.
SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
2 from dual;
STALE_PERCENT
--------------------------------------------------------------------------------
13
SQL>

 

Global STALE_PERCENT 값과는 별개로 Single 테이블의 STALE_PERCENT 변경을 원한다면 아래와 같이 변경이 가능하다.

change_table_pref.sql

execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65');
SQL> @change_table_pref.sql
SQL> execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65');
PL/SQL procedure successfully completed.
SQL>

 

Sh 유저 의 SALES 테이블에 대해 STALE_PERCENT 값이 65 로 변경된 것을 확인 할 수 있다.

SQL> @check_sales_pref.sql
SQL> connect sh/sh
Connected.
SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
2 from dual;
STALE_PERCENT
--------------------------------------------------------------------------------
65
SQL>
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent from dual;

 

그러나 다른 테이블을 확인해 보면 Global STALE_PERCENT 값을 13 의 값이 그대로 적용되어 있는 것이 확인된다.

 

SQL> @check_products_pref.sql
SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent
2 from dual;
STALE_PERCENT
--------------------------------------------------------------------------------
13
SQL>

 

변경된 STALE_PERCENT 값을 default 값으로 변경하기 위해서는 SH.SALES의 설정값은 지우면 된다.

reset_table_prefs

execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT');

 

dbms_stats.delete_table_prefs 을 사용해서 삭제하면 Global STALE_PERCENT 값이 13으로 적용된다.

SQL> @reset_table_prefs
SQL> execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT');
PL/SQL procedure successfully completed.
SQL>
connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;
SQL> @check_sales_pref.sql
SQL> connect sh/sh
Connected.
SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
2 from dual;
STALE_PERCENT
--------------------------------------------------------------------------------
13
SQL>

Global STALE_PERCENT 값을 default로 되돌리는 방법은 아래와 같다.

reset_global_prefs.sql

connect / as sysdba
execute dbms_stats.set_global_prefs('STALE_PERCENT', null);

 

execute dbms_stats.set_global_prefs 값이 null 이면 Default 값 10 이 적용된다.

SQL> @reset_global_prefs.sql
SQL> connect / as sysdba
Connected.
SQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', null);
PL/SQL procedure successfully completed.
SQL>
connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;
SQL> @check_sales_pref.sql
SQL> connect sh/sh
Connected.
SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
2 from dual;
STALE_PERCENT
--------------------------------------------------------------------------------
10
SQL>

 

Gathering Pending Statistics

이번 테스트는 테이블의 public 과 pending 통계정보를 확인하는 방법과, publishing 없이 통계정보를 수집 하는 방법을 소개한다.

실습에 하기전에 ‘CUSTOMERS_OBE’ 테이블에 대한 날짜 형식을 맞추고, 이전의 통계정보를 삭제한다.

reset_table_stats

connect sh/sh
alter session set nls_date_format='mm/dd hh24:mi:ss';
-- delete statistics
exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');
SQL> @reset_table_stats
SQL> connect sh/sh
Connected.
SQL> alter session set nls_date_format='mm/dd hh24:mi:ss';
Session altered.
SQL> -- delete statistics
SQL> exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');
PL/SQL procedure successfully completed.
SQL>

 

CUSTOMERS_OBE 에 대한 public statistics (table, index, column) 확인하는 방법은 다음과 같다.

show_public_stats.sql

set echo off
-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null;
-- indexes
select index_name, last_analyzed "analyze time", num_rows,
leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name;
-- columns
select column_name, last_analyzed "analyze time", num_distinct,
num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name;
set echo on

 

SQL> @show_public_stats CUSTOMERS_OBE
SQL>
SQL> set echo off
old 3: where table_name = '&1'
new 3: where table_name = 'CUSTOMERS_OBE'
TABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ -------------- ---------- ---------- -----------
CUSTOMERS_OBE
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
INDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS
------------------------------ -------------- ---------- -----------
DISTINCT_KEYS
-------------
OBE_CUST_CRED_LMT_IND
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
COUNTRY_ID
CUST_CITY
CUST_CREDIT_LIMIT
CUST_EMAIL
CUST_FIRST_NAME
CUST_GENDER
CUST_ID
CUST_INCOME_LEVEL
CUST_LAST_NAME
CUST_MAIN_PHONE_NUMBER
CUST_MARITAL_STATUS
COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
CUST_POSTAL_CODE
CUST_STATE_PROVINCE
CUST_STREET_ADDRESS
CUST_YEAR_OF_BIRTH
15 rows selected.
SQL>

 

CUSTOMERS_OBE 에 대한 pending statistics (table, index, column) 확인하는 방법은 다음과 같다.

 

show_pending_stats.sql

set echo off
-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null;
-- indexes
select index_name, last_analyzed "analyze time", num_rows,
leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name;
-- columns
select column_name, last_analyzed "analyze time", num_distinct,
num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name;
set echo on

 

SQL> @show_pending_stats CUSTOMERS_OBE
SQL>
SQL> set echo off
old 3: where table_name = '&1' and partition_name is null
new 3: where table_name = 'CUSTOMERS_OBE' and partition_name is null
no rows selected
old 4: where table_name = '&1' and partition_name is null
new 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null
no rows selected
old 4: where table_name = '&1' and partition_name is null
new 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null
no rows selected
SQL>

 

현재 CUSTOMERS_OBE 테이블에 대한 public, pending 통계정보는 없는 것을 확인할 수 있다.

11g는 default로 통계정보가 수집되자 마자 Publish 된다.

이러한 PUBLISH (default : TRUE) 설정값은 dbms_stats.get_prefs로 확인할 수 있다.

check_publish_prefs.sql

select dbms_stats.get_prefs('PUBLISH') publish from dual;
SQL> @check_publish_prefs
SQL> select dbms_stats.get_prefs('PUBLISH') publish from dual;
PUBLISH
--------------------------------------------------------------------------------
TRUE
SQL>

 

마찬가지로 ‘CUSTOMERS_OBE’ 또한 PUBLISH 값이 Default 값 TRUE 가 적용되었음을 확인할 수 있다.

check_table_publish_prefs.sql

select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
SQL> @check_table_publish_prefs
SQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
PUBLISH
--------------------------------------------------------------------------------
TRUE
SQL>

 

‘CUSTOMERS_OBE’ 의 PUBLISH 값을 False 로 변경한다.

이는 통계정보 수집 후 바로 자동으로 PUBLISH 하는것을 하지 않겠다는 것을 의미한다.

set_table_publish_prefs_false.sql

exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false');
SQL> @set_table_publish_prefs_false
SQL> exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false');
PL/SQL procedure successfully completed.
SQL>
SQL> @check_table_publish_prefs
SQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
PUBLISH
--------------------------------------------------------------------------------
FALSE
SQL>

 

SH.CUSTOMERS_OBE 테이블의 통계정보를 수집한다.

gather_table_stats.sql

execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE');
SQL> @gather_table_stats.sql
SQL> execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE');
PL/SQL procedure successfully completed.
SQL>

 

통계정보를 수집했음에도 PUBLISH 되지 않았음을 확인한다.

 

SQL> @show_public_stats CUSTOMERS_OBE
SQL>
SQL> set echo off
old 3: where table_name = '&1'
new 3: where table_name = 'CUSTOMERS_OBE'
TABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ -------------- ---------- ---------- -----------
CUSTOMERS_OBE
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
INDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS
------------------------------ -------------- ---------- -----------
DISTINCT_KEYS
-------------
OBE_CUST_CRED_LMT_IND
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
TABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ -------------- ---------- ---------- -----------
CUSTOMERS_OBE
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
INDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS
------------------------------ -------------- ---------- -----------
DISTINCT_KEYS
-------------
OBE_CUST_CRED_LMT_IND
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
COUNTRY_ID
CUST_CITY
CUST_CREDIT_LIMIT
CUST_EMAIL
CUST_FIRST_NAME
CUST_GENDER
CUST_ID
CUST_INCOME_LEVEL
CUST_LAST_NAME
CUST_MAIN_PHONE_NUMBER
CUST_MARITAL_STATUS
COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
CUST_POSTAL_CODE
CUST_STATE_PROVINCE
CUST_STREET_ADDRESS
CUST_YEAR_OF_BIRTH
15 rows selected.
SQL>

 

User_tab_pending_stats 를 통해서 PUBLISH 되기 이전 수집된 통계정보를 확인한다.

 

SQL> @show_pending_stats CUSTOMERS_OBE
SQL>
SQL> set echo off
old 3: where table_name = '&1' and partition_name is null
new 3: where table_name = 'CUSTOMERS_OBE' and partition_name is null
TABLE_NAME analyze time NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ -------------- ---------- ---------- -----------
CUSTOMERS_OBE 09/11 17:59:05 630 12 137.646032
old 4: where table_name = '&1' and partition_name is null
new 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null
INDEX_NAME analyze time NUM_ROWS LEAF_BLOCKS
------------------------------ -------------- ---------- -----------
DISTINCT_KEYS
-------------
OBE_CUST_CRED_LMT_IND 09/11 17:59:08 630 2
8
old 4: where table_name = '&1' and partition_name is null
new 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null
COLUMN_NAME analyze time NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
COUNTRY_ID 09/11 17:59:05 19 0 .052631579
CUST_CITY 09/11 17:59:05 300 0 .003333333
CUST_CREDIT_LIMIT 09/11 17:59:05 8 0 .125
CUST_EMAIL 09/11 17:59:05 400 0 .0025
CUST_FIRST_NAME 09/11 17:59:05 450 0 .002222222
CUST_GENDER 09/11 17:59:05 2 0 .5
CUST_ID 09/11 17:59:05 630 0 .001587302
CUST_INCOME_LEVEL 09/11 17:59:05 12 0 .083333333
CUST_LAST_NAME 09/11 17:59:05 400 0 .0025
CUST_MAIN_PHONE_NUMBER 09/11 17:59:05 630 0 .001587302
CUST_MARITAL_STATUS 09/11 17:59:05 2 234 .5
COLUMN_NAME analyze time NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
CUST_POSTAL_CODE 09/11 17:59:05 301 0 .003322259
CUST_STATE_PROVINCE 09/11 17:59:05 120 0 .008333333
CUST_STREET_ADDRESS 09/11 17:59:05 630 0 .001587302
CUST_YEAR_OF_BIRTH 09/11 17:59:05 66 0 .015151515
15 rows selected.
SQL>

 

Testing Pending Statistics

 

현재 CUSTOMERS_OBE 테이블에 대한 모든 통계정보는 pending 상태 이다.

Pending statistics를 export하여 test 장비에서 테스트를 수행할 수 있다. 그리고 만약 테스트한 pending statistics가 만족스럽지 않다면(성능향상이 없다면), pending statistics를 지워버릴 수 있다.

Sessin level에서 pending statistics를 사용하지 않기 위해서 다음의 명령을 수행한다.

 

set_pending_stats_off.sql

alter session set optimizer_use_pending_statistics = false;
alter session set optimizer_dynamic_sampling = 0;
SQL> @set_pending_stats_off
SQL> alter session set optimizer_use_pending_statistics = false;
Session altered.
SQL> alter session set optimizer_dynamic_sampling = 0;
Session altered.
SQL>

 

Query에 대한 SQL Plan을 얻는다.

get_execplan.sql

set linesize 140
set pagesize 40
explain plan for
select * from customers_obe where CUST_CREDIT_LIMIT=1500;
select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));
SQL> @get_execplan
SQL> set linesize 140
SQL> set pagesize 40
SQL> explain plan for
2 select * from customers_obe where CUST_CREDIT_LIMIT=1500;
Explained.
SQL>
SQL> select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));
PLAN
-------------------------------------------------------------------------------------------------------
Plan hash value: 2572487643
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 2080 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS_OBE | 10 | 2080 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBE_CUST_CRED_LMT_IND | 4 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_CREDIT_LIMIT"=1500)
14 rows selected.
SQL>

 

optimizer_use_pending_statistics = false 로 설정하여 pending statistics 를 사용하지 않을 경우, SQL Plan을 확인해 보면 Index scan 후 각각의 Row 마다 CUST_CREDIT_LIMIT=1500  조건이 충족될 때 까지 테이블에 a single-row access 한다.

Cust_credit_limit의 distinct value는 8로서 하나의 cust_credit_limit는 전체 row의 약 12.5%를 차지하고 있어 Cust_credit_limit=1500인 row수가 점점 증가한다면 해당 SQL의 속도는 점점 늦어질 것이므로 index range scan은 최적의 Plan은 아니다.

 

이번에는 pending statistics를 이용했을 때, 더 좋는 Plan이 생성되는지 확인해 보도록 한다.

 

우선, Sessin level에서 pending statistics가 사용되도록 설정값은 ture로 변경한다.

set_optimizer_pending_stats_true.sql

alter session set optimizer_use_pending_statistics = true;
SQL> @set_optimizer_pending_stats_true
SQL> alter session set optimizer_use_pending_statistics = true;
Session altered.
SQL>

 

SQL Plan을 조회한다.

set linesize 140
set pagesize 40
explain plan for
select * from customers_obe where CUST_CREDIT_LIMIT=1500;
select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));
SQL> @get_execplan
SQL> set linesize 140
SQL> set pagesize 40
SQL> explain plan for
2 select * from customers_obe where CUST_CREDIT_LIMIT=1500;
Explained.
SQL>
SQL> select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));
PLAN
--------------------------------------------------------------------------------------------------------
------------------------------------
Plan hash value: 520139036
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 79 | 10823 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 79 | 10823 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUST_CREDIT_LIMIT"=1500)
13 rows selected.
SQL>

 

여기서는 Full TABLE SCAN 을 하지만 이전의 Index Range Scan 보다는더 나은 Plan을 생성해 주므로, pending statistics가 실 운영 DB에 적용할 적절한 통계정보이다.

Publishing Pending Statistics

만일 Pending 통계정보가 충분히 입증 되었다면, 아래와 같이 Publish 한다.

 

publish_pending_stats.sql

exec dbms_stats.publish_pending_stats(null, null) ;

 

Pending 통계정보를 publishing 한다.

SQL> @publish_pending_stats
SQL> exec dbms_stats.publish_pending_stats(null, null)
PL/SQL procedure successfully completed.
SQL> @show_public_stats CUSTOMERS_OBE
SQL>
SQL> set echo off
old 3: where table_name = '&1'
new 3: where table_name = 'CUSTOMERS_OBE'
TABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ -------------- ---------- ---------- -----------
CUSTOMERS_OBE 09/11 17:59:05 630 12 137
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
INDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
------------------------------ -------------- ---------- ----------- -------------
OBE_CUST_CRED_LMT_IND 09/11 17:59:08 630 2 8
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
COUNTRY_ID 09/11 17:59:05 19 0 .052631579
CUST_CITY 09/11 17:59:05 300 0 .003333333
CUST_CREDIT_LIMIT 09/11 17:59:05 8 0 .125
CUST_EMAIL 09/11 17:59:05 400 0 .0025
CUST_FIRST_NAME 09/11 17:59:05 450 0 .002222222
CUST_GENDER 09/11 17:59:05 2 0 .5
CUST_ID 09/11 17:59:05 630 0 .001587302
CUST_INCOME_LEVEL 09/11 17:59:05 12 0 .083333333
CUST_LAST_NAME 09/11 17:59:05 400 0 .0025
CUST_MAIN_PHONE_NUMBER 09/11 17:59:05 630 0 .001587302
CUST_MARITAL_STATUS 09/11 17:59:05 2 234 .5
CUST_POSTAL_CODE 09/11 17:59:05 301 0 .003322259
CUST_STATE_PROVINCE 09/11 17:59:05 120 0 .008333333
CUST_STREET_ADDRESS 09/11 17:59:05 630 0 .001587302
CUST_YEAR_OF_BIRTH 09/11 17:59:05 66 0 .015151515
15 rows selected.
SQL>

 

Pending 통계정보를 다시 확인해 보니 publishing 되어 남아 있지 않는다.

SQL> @show_pending_stats CUSTOMERS_OBE
SQL>
SQL> set echo off
old 3: where table_name = '&1' and partition_name is null
new 3: where table_name = 'CUSTOMERS_OBE' and partition_name is null
no rows selected
old 4: where table_name = '&1' and partition_name is null
new 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null
no rows selected
old 4: where table_name = '&1' and partition_name is null
new 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null
no rows selected
SQL>

 

Resetting Statistics and Preferences

SH.customer_obe에 설정된 publish perference를 default 값(false)로 reset 한다.

dbms_stats.delete_table_stats 를 사용해서 기존의 통계정보를 삭제하고 publish preference를 false로 변경한다.

delete_table_stats.sql

exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');

 

set_global_publish.sql

select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;

 

SQL> @delete_table_stats
SQL> exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');
PL/SQL procedure successfully completed.
SQL>
SQL> @set_global_publish
SQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
PUBLISH
--------------------------------------------------------------------------------------------------------------------------------------------
FALSE
SQL>

 


Change global and table statistic preferences
Gather pending statistics
Test pending statistics
Publish pending statistics
Reset statistics and preferences


 

Changing Global and Table Statistic Preferences

예제 실습을 위한 테이블 생성 및 Data import

[oracle@obe11g gathstats]$ imp sh/sh file=customers_obe.dmp log=log full=y
Import: Release 11.1.0.5.0 - Beta on Wed Sep 12 09:59:15 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 gathstats]$
SQL> desc CUSTOMERS_OBE
Name Null? Type
----------------------------------------- -------- ----------------------------
CUST_ID NUMBER
CUST_FIRST_NAME NOT NULL VARCHAR2(20)
CUST_LAST_NAME NOT NULL VARCHAR2(40)
CUST_GENDER CHAR(1)
CUST_YEAR_OF_BIRTH NUMBER(4)
CUST_MARITAL_STATUS VARCHAR2(20)
CUST_STREET_ADDRESS NOT NULL VARCHAR2(40)
CUST_POSTAL_CODE NOT NULL VARCHAR2(10)
CUST_CITY NOT NULL VARCHAR2(30)
CUST_STATE_PROVINCE VARCHAR2(40)
COUNTRY_ID NOT NULL CHAR(2)
CUST_MAIN_PHONE_NUMBER VARCHAR2(25)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
CUST_EMAIL VARCHAR2(30)
SQL>
SQL> select count(*) from CUSTOMERS_OBE;
COUNT(*)
----------
630

 

통계정보 수집을 위한 default preferences 또는 parameter setting 을 변경 한다.

그 중 하나는 STALE_PERCENT 와 관련이 있으며 테이블 안에 레코드들의 비율 을 의미하는 STALE_PERCENT 은 새로 수집해야 한다고 판단되는 테이블에 대해서 통계정보를 수집하기 전에 변경해야 한다.

 

check_sales_pref.sql

connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;

 

현재 ‘STALE_PERCENT 에 대한 Default 값 10 이 설정된 것을 확인 할 수 있다.

SQL> @check_sales_pref.sql
SQL> connect sh/sh
Connected.
SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
2 from dual;
STALE_PERCENT
--------------------------------------------------------------------------------
10
SQL>

Global STALE_PERCENT 값을 13 으로 변경한다.

change_global_pref.sql

connect / as sysdba
execute dbms_stats.set_global_prefs('STALE_PERCENT', '13');
SQL> @change_global_pref.sql
SQL> connect / as sysdba
Connected.
SQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', '13');
PL/SQL procedure successfully completed.
SQL>
connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;

 

Global STALE_PERCENT 값 변경으로 STALE_PERCENT 값이 13 으로 변경 된 것을 확인 할수 있다.

SQL> @check_sales_pref.sql
SQL> connect sh/sh
Connected.
SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
2 from dual;
STALE_PERCENT
--------------------------------------------------------------------------------
13
SQL>

Single 테이블 변경을 원한다면 아래와 같이 변경이 가능하다.

change_table_pref.sql

execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65');
SQL> @change_table_pref.sql
SQL> execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65');
PL/SQL procedure successfully completed.
SQL>
connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;

 

Sh 유저 의 SALES 테이블에 대해 STALE_PERCENT 값이 65 로 변경된 것을 확인 할 수 있다.

SQL> @check_sales_pref.sql
SQL> connect sh/sh
Connected.
SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
2 from dual;
STALE_PERCENT
--------------------------------------------------------------------------------
65
SQL>
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent from dual;

 

그러나 다른 테이블을 확인해 보면 Global STALE_PERCENT 값을 13 의 값이 그대로 적용되어 있는 것이 확인된다.

SQL> @check_products_pref.sql
SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent
2 from dual;
STALE_PERCENT
--------------------------------------------------------------------------------
13
SQL>

 

reset_table_prefs.sql

execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT');

 

dbms_stats.delete_table_prefs 을 사용해서 삭제하면 Global STALE_PERCENT 값이 13으로 적용된다.

SQL> @reset_table_prefs
SQL> execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT');
PL/SQL procedure successfully completed.
SQL>
connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
from dual;
SQL> @check_sales_pref.sql
SQL> connect sh/sh
Connected.
SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
2 from dual;
STALE_PERCENT
--------------------------------------------------------------------------------
13
SQL>

 

reset_global_prefs.sql

connect / as sysdba
execute dbms_stats.set_global_prefs('STALE_PERCENT', null);

 

execute dbms_stats.set_global_prefs 값이 null 이면 Default 값 10 이 적용된다.

SQL> @reset_global_prefs.sql
SQL> connect / as sysdba
Connected.
SQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', null);
PL/SQL procedure successfully completed.
SQL> connect sh/sh
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;
SQL> @check_sales_pref.sql
SQL> connect sh/sh
Connected.
SQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent
2 from dual;
STALE_PERCENT
--------------------------------------------------------------------------------
10
SQL>

 

 

Gathering Pending Statistics

당신은 테이블의 public 과 pending 통계정보를 확인 할 수 있고, publishing 없이 어떻게 통계정보를 수집 할 수 있는지를 확인 하게 될 것이다.

실습에 임하기전 ‘CUSTOMERS_OBE 테이블에 대한 날짜 형식을 맞출 것입니다. 그리고 이전의 통계정보를 삭제 합니다.

 

reset_table_stats.sql

connect sh/sh
alter session set nls_date_format='mm/dd hh24:mi:ss';
-- delete statistics
exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');
SQL> @reset_table_stats
SQL> connect sh/sh
Connected.
SQL> alter session set nls_date_format='mm/dd hh24:mi:ss';
Session altered.
SQL> -- delete statistics
SQL> exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');
PL/SQL procedure successfully completed.
SQL>

 

show_public_stats.sql

set echo off
-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null ;

-- indexes
select index_name, last_analyzed "analyze time", num_rows, leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name ;

-- columns
select column_name, last_analyzed "analyze time", num_distinct, num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name ;
set echo on

 

CUSTOMERS_OBE 에 대한 public_stats 확인 (tables, indexes, columns)

 

SQL> @show_public_stats CUSTOMERS_OBE
SQL>
SQL> set echo off
old 3: where table_name = '&1'
new 3: where table_name = 'CUSTOMERS_OBE'
TABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ -------------- ---------- ---------- -----------
CUSTOMERS_OBE
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
INDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS
------------------------------ -------------- ---------- -----------
DISTINCT_KEYS
-------------
OBE_CUST_CRED_LMT_IND
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
COUNTRY_ID
CUST_CITY
CUST_CREDIT_LIMIT
CUST_EMAIL
CUST_FIRST_NAME
CUST_GENDER
CUST_ID
CUST_INCOME_LEVEL
CUST_LAST_NAME
CUST_MAIN_PHONE_NUMBER
CUST_MARITAL_STATUS
COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
CUST_POSTAL_CODE
CUST_STATE_PROVINCE
CUST_STREET_ADDRESS
CUST_YEAR_OF_BIRTH

15 rows selected.
SQL>

 

show_pending_stats.sql

set echo off
-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len
from user_tab_pending_stats
where table_name = '&1' and partition_name is null;
-- indexes
select index_name, last_analyzed "analyze time", num_rows,leaf_blocks, distinct_keys
from user_ind_pending_stats
where table_name = '&1' and partition_name is null
order by index_name;

-- columns
select column_name, last_analyzed "analyze time", num_distinct, num_nulls, density
from user_col_pending_stats
where table_name = '&1' and partition_name is null
order by column_name;
set echo on

 

CUSTOMERS_OBE 에 대한 pending_stats 확인 (tables, indexes, columns)

 

SQL> @show_pending_stats CUSTOMERS_OBE
SQL>
SQL> set echo off
old 3: where table_name = '&1' and partition_name is null
new 3: where table_name = 'CUSTOMERS_OBE' and partition_name is null
no rows selected
old 4: where table_name = '&1' and partition_name is null
new 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null
no rows selected
old 4: where table_name = '&1' and partition_name is null
new 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null
no rows selected
SQL>

 

기본적으로 Oracle Database 11g 에서도 수집 되자 마다 통계 정보가 Publish 되는것은 동일하다.

Default PUBLISH 값이 TRUE 로 설정 되어 있음을 확인 할 수 있다.

 

select dbms_stats.get_prefs('PUBLISH') publish from dual;
SQL> @check_publish_prefs
SQL> select dbms_stats.get_prefs('PUBLISH') publish from dual;
PUBLISH
--------------------------------------------------------------------------------
TRUE
SQL>

 

마찬가지로 ‘CUSTOMERS_OBE’  또한  PUBLISH 값이 Default 값 TRUE 가 적용되었음을 확인 했다.

 

select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
SQL> @check_table_publish_prefs
SQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
PUBLISH
--------------------------------------------------------------------------------
TRUE
SQL>

 

set_table_publish_prefs_false.sql

exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false');

 

‘CUSTOMERS_OBE’ 의 PUBLISH 값을 False 로 변경 했으며, 이는 통계정보 수집후 바로 자동으로 PUBLISH 하는 것을 하지 않겠다는 것을 의미한다.

 

SQL> @set_table_publish_prefs_false
SQL> exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false');
PL/SQL procedure successfully completed.
SQL>
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
SQL> @check_table_publish_prefs
SQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
PUBLISH
--------------------------------------------------------------------------------
FALSE
SQL>

 

SH 스키마의 ‘CUSTOMERS_OBE’ 테이블 통계정보수집

gather_table_stats.sql

execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE');
SQL> @gather_table_stats.sql
SQL> execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE');
PL/SQL procedure successfully completed.
SQL>

 

통계정보를 수집 했음 에도 PUBLISH 되지 않았음을 확인 할 수 있다.

show_public_stats.sql

set echo off
-- tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len from user_tables where table_name = '&1';
-- indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = '&1' order by index_name;
-- columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct,num_nulls, density from user_tab_columns where table_name = '&1' order by column_name;
set echo on
SQL> @show_public_stats CUSTOMERS_OBE
SQL>
SQL> set echo off
old 3: where table_name = '&1'
new 3: where table_name = 'CUSTOMERS_OBE'
TABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ -------------- ---------- ---------- -----------
CUSTOMERS_OBE
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
INDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS
------------------------------ -------------- ---------- -----------
DISTINCT_KEYS
-------------
OBE_CUST_CRED_LMT_IND
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'

TABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ -------------- ---------- ---------- -----------
CUSTOMERS_OBE
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
INDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS
------------------------------ -------------- ---------- -----------
DISTINCT_KEYS
-------------
OBE_CUST_CRED_LMT_IND
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
COUNTRY_ID
CUST_CITY
CUST_CREDIT_LIMIT
CUST_EMAIL
CUST_FIRST_NAME
CUST_GENDER
CUST_ID
CUST_INCOME_LEVEL
CUST_LAST_NAME
CUST_MAIN_PHONE_NUMBER
CUST_MARITAL_STATUS
COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
CUST_POSTAL_CODE
CUST_STATE_PROVINCE
CUST_STREET_ADDRESS
CUST_YEAR_OF_BIRTH
15 rows selected.
SQL>

 

User_tab_pending_stats 를 통해서 PUBLISH 되기 이전 수집된 통계정보를 확인 할 수 있다.

show_pending_stats.sql

set echo off
-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len from user_tab_pending_stats where table_name = '&1' and partition_name is null;
-- indexes
select index_name, last_analyzed "analyze time", num_rows, leaf_blocks, distinct_keys from user_ind_pending_stats where table_name = '&1' and partition_name is null order by index_name;
-- columns
select column_name, last_analyzed "analyze time", num_distinct, num_nulls, density from user_col_pending_stats where table_name = '&1' and partition_name is null order by column_name;
set echo on
SQL> @show_pending_stats CUSTOMERS_OBE
SQL>
SQL> set echo off
old 3: where table_name = '&1' and partition_name is null
new 3: where table_name = 'CUSTOMERS_OBE' and partition_name is null
TABLE_NAME analyze time NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ -------------- ---------- ---------- -----------
CUSTOMERS_OBE 09/11 17:59:05 630 12 137.646032
old 4: where table_name = '&1' and partition_name is null
new 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null
INDEX_NAME analyze time NUM_ROWS LEAF_BLOCKS
------------------------------ -------------- ---------- -----------
DISTINCT_KEYS
-------------
OBE_CUST_CRED_LMT_IND 09/11 17:59:08 630 2
8
old 4: where table_name = '&1' and partition_name is null
new 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null
COLUMN_NAME analyze time NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
COUNTRY_ID 09/11 17:59:05 19 0 .052631579
CUST_CITY 09/11 17:59:05 300 0 .003333333
CUST_CREDIT_LIMIT 09/11 17:59:05 8 0 .125
CUST_EMAIL 09/11 17:59:05 400 0 .0025
CUST_FIRST_NAME 09/11 17:59:05 450 0 .002222222
CUST_GENDER 09/11 17:59:05 2 0 .5
CUST_ID 09/11 17:59:05 630 0 .001587302
CUST_INCOME_LEVEL 09/11 17:59:05 12 0 .083333333
CUST_LAST_NAME 09/11 17:59:05 400 0 .0025
CUST_MAIN_PHONE_NUMBER 09/11 17:59:05 630 0 .001587302
CUST_MARITAL_STATUS 09/11 17:59:05 2 234 .5
COLUMN_NAME analyze time NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
CUST_POSTAL_CODE 09/11 17:59:05 301 0 .003322259
CUST_STATE_PROVINCE 09/11 17:59:05 120 0 .008333333
CUST_STREET_ADDRESS 09/11 17:59:05 630 0 .001587302
CUST_YEAR_OF_BIRTH 09/11 17:59:05 66 0 .015151515
15 rows selected.
SQL>

 

Testing Pending Statistics

CUSTOMERS_OBE 테이블에 대한 통계정보는 pending 상태 이다.

optimizer_use_pending_statistics = false
optimizer_dynamic_sampling = 0

set_pending_stats_off.sql

alter session set optimizer_use_pending_statistics = false;
alter session set optimizer_dynamic_sampling = 0;
SQL> @set_pending_stats_off
SQL> alter session set optimizer_use_pending_statistics = false;
Session altered.
SQL> alter session set optimizer_dynamic_sampling = 0;
Session altered.
SQL>

 

get_execplan.sql

set linesize 140
set pagesize 40
explain plan for
select * from customers_obe where CUST_CREDIT_LIMIT=1500;
select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));

 

optimizer_use_pending_statistics = false 일때 통계정보를 확인해 보면 Index SCAN 후 각각의 Row 마다 CUST_CREDIT_LIMIT=1500 조건이 충족될 때 까지 테이블에 a single – row access 를 해야 하기 때문에 이것은 최적의 Plan 을 보여 주는 것이 아니다.

CUST_CREDIT_LIMIT=1500 에 따른 결과가 테이블안에 Rows 수 20% 를 넘을 경우 , 따라서 CUSTOMERS_OBE 테이블은 계속 증가할 것이고, 이와 같은 Plan 은 점점 더 느린 결과를 가져올 것이다.

 

SQL> @get_execplan
SQL> set linesize 140
SQL> set pagesize 40
SQL> explain plan for
2 select * from customers_obe where CUST_CREDIT_LIMIT=1500;
Explained.
SQL>
SQL> select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));
PLAN
-------------------------------------------------------------------------------------------------------
Plan hash value: 2572487643
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 2080 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS_OBE | 10 | 2080 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBE_CUST_CRED_LMT_IND | 4 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUST_CREDIT_LIMIT"=1500)
14 rows selected.
SQL>

 

optimizer_use_pending_statistics = true

 

alter session set optimizer_use_pending_statistics = true;
SQL> @set_optimizer_pending_stats_true
SQL> alter session set optimizer_use_pending_statistics = true;
Session altered.
SQL>

 

optimizer 가 pending statistic 을 사용한다면 더 나은 결과를 보일 것이라고 생각된다면,

아래와 같이 optimizer_use_pending_statistics 을 true 로 설정해서 사용할 수 있다.

 

set linesize 140
set pagesize 40
explain plan for
select * from customers_obe where CUST_CREDIT_LIMIT=1500;
select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));

 

여기서는 Full TABLESCAN 을 하겠지만, pending 통계정보가 훨씬 더 나은 결과를 보일 것 입니다.

 

SQL> @get_execplan
SQL> set linesize 140
SQL> set pagesize 40
SQL> explain plan for
2 select * from customers_obe where CUST_CREDIT_LIMIT=1500;
Explained.
SQL>
SQL> select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));
PLAN
--------------------------------------------------------------------------------------------------------
------------------------------------
Plan hash value: 520139036
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 79 | 10823 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 79 | 10823 | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUST_CREDIT_LIMIT"=1500)
13 rows selected.
SQL>

 

Publishing Pending Statistics

Pending 통계정보 의 Publishing.

publish_pending_stats.sql

exec dbms_stats.publish_pending_stats(null, null)

만일 Pending 통계정보가 충분히 입증 되었다면, 아래와 같이 Publishing 할 수 있다.

publish_pending_stats.sql

SQL> @publish_pending_stats
SQL> exec dbms_stats.publish_pending_stats(null, null)
PL/SQL procedure successfully completed.
SQL>

 

show_public_stats.sql

set echo off
-- tables
select table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len from user_tables where table_name = '&1';
-- indexes
select index_name, last_analyzed ANALYZE_TIME, num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = '&1' order by index_name;
-- columns
select column_name, last_analyzed ANALYZE_TIME, num_distinct, num_nulls, density from user_tab_columns where table_name = '&1' order by column_name;
set echo on

 

Pending 통계정보를 publishing 한다.

 

SQL> @publish_pending_stats
SQL> exec dbms_stats.publish_pending_stats(null, null)
PL/SQL procedure successfully completed.
SQL> @show_public_stats CUSTOMERS_OBE
SQL>
SQL> set echo off
old 3: where table_name = '&1'
new 3: where table_name = 'CUSTOMERS_OBE'
TABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN
------------------------------ -------------- ---------- ---------- -----------
CUSTOMERS_OBE 09/11 17:59:05 630 12 137
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
INDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS
------------------------------ -------------- ---------- ----------- -------------
OBE_CUST_CRED_LMT_IND 09/11 17:59:08 630 2 8
old 4: where table_name = '&1'
new 4: where table_name = 'CUSTOMERS_OBE'
COLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY
------------------------------ -------------- ------------ ---------- ----------
COUNTRY_ID 09/11 17:59:05 19 0 .052631579
CUST_CITY 09/11 17:59:05 300 0 .003333333
CUST_CREDIT_LIMIT 09/11 17:59:05 8 0 .125
CUST_EMAIL 09/11 17:59:05 400 0 .0025
CUST_FIRST_NAME 09/11 17:59:05 450 0 .002222222
CUST_GENDER 09/11 17:59:05 2 0 .5
CUST_ID 09/11 17:59:05 630 0 .001587302
CUST_INCOME_LEVEL 09/11 17:59:05 12 0 .083333333
CUST_LAST_NAME 09/11 17:59:05 400 0 .0025
CUST_MAIN_PHONE_NUMBER 09/11 17:59:05 630 0 .001587302
CUST_MARITAL_STATUS 09/11 17:59:05 2 234 .5
CUST_POSTAL_CODE 09/11 17:59:05 301 0 .003322259
CUST_STATE_PROVINCE 09/11 17:59:05 120 0 .008333333
CUST_STREET_ADDRESS 09/11 17:59:05 630 0 .001587302
CUST_YEAR_OF_BIRTH 09/11 17:59:05 66 0 .015151515
15 rows selected.
SQL>

 

show_pending_stats.sql

set echo off
-- tables
select table_name, last_analyzed "analyze time", num_rows, blocks, avg_row_len from user_tab_pending_stats where table_name = '&1' and partition_name is null;
-- indexes
select index_name, last_analyzed "analyze time", num_rows,leaf_blocks, distinct_keys from user_ind_pending_stats where table_name = '&1' and partition_name is null order by index_name;
-- columns
select column_name, last_analyzed "analyze time", num_distinct, num_nulls, density from user_col_pending_stats where table_name = '&1' and partition_name is null order by column_name;
set echo on

 

Pending 통계정보를 다시 확인해 보니 publishing 되어 남아 있지 않는다.

SQL> @show_pending_stats CUSTOMERS_OBE
SQL>
SQL> set echo off
old 3: where table_name = '&1' and partition_name is null
new 3: where table_name = 'CUSTOMERS_OBE' and partition_name is null
no rows selected
old 4: where table_name = '&1' and partition_name is null
new 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null
no rows selected
old 4: where table_name = '&1' and partition_name is null
new 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null
no rows selected
SQL>

 

Resetting Statistics and Preferences

delete_table_stats.sql

exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');

 

dbms_stats.delete_table_stats 를 사용해서 기존의 통계정보를 삭제 합니다.

SQL> @delete_table_stats
SQL> exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');
PL/SQL procedure successfully completed.
SQL>

 

set_global_publish.sql

select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;

 

기존의 통계정보는 삭제하고, 자동 publish 가 되도록 새롭게 통계정보를 수집합니다.

SQL> @delete_table_stats
SQL> exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');
PL/SQL procedure successfully completed.
SQL>
SQL> @set_global_publish
SQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;
PUBLISH
--------------------------------------------------------------------------------------------------------------------------------------------
FALSE
SQL>

 

 

Oracle 11g 이전에는 데이터베이스 관리자가 통계정보를 수집하면 바로 Plan 에 반영되어 예기치 못한 결과를 초래 할 수 가 있었습니다.

이제 Oracle 11g 에서는 통계정보의 수집 ( Gathering ) 과 반영 ( Publish ) 를 분리할 수 있게 됨으로써 기존의 통계수집의 불안함을 극복하고

실 운영환경에서 테스트를 통하여 검증된 통계정보만이 Publish 되도록 하여 애플리케이션의 성능을 극대화 할 수 있게 되었습니다.

By haisins

오라클 DBA 박용석 입니다. haisins@gmail.com 으로 문의 주세요.

답글 남기기

이메일 주소를 발행하지 않을 것입니다. 필수 항목은 *(으)로 표시합니다