과거의 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 되는 과정은 다음과 같다.
Pending Statistics 장점
실 운영환경 하에서 테이블에 대한 통계정보를 미리 Test 해 볼 수 있다. 통계정보에 대한 검증작업에 대한 신뢰도가 향상되었다. 검증된 통계정보만 Publish 함으로써 Application의 성능을 유지 및 개선할 수 있다.
통계정보 Gathering 및 Publish Commands
Publish Attribute 확인방법
|
1 |
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에 저장된다.
|
1 |
SQL> Exec dbms_stats.set_table_prefs(‘SH’, ‘CUSTOMERS’, ‘PUBLISH’, ‘false’); |
Pending 통계정보를 사용하는 방법
옵티마이져는 default로 Publish된 통계정보를 사용한다.
인위적으로 pending 통계정보를 사용토록 하기 위해서는 init파일에 OPTIMIZER_PENDING_STATISTICS=TRUE 으로 설정하거나, session level에서 다음과 같이 변경한다.
|
1 |
SQL> alter session set optimizer_pending_statistis=TRUE; |
Pending 통계정보를 Publish 하는 방법
모든 valid pending 통계정보를 Publish
|
1 |
SQL> Exec dbms_stats.publish_pending_stats(null, null); |
특정 object에 대한 pending 통계정보를 Publish
|
1 |
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 및 확인
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[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]$ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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> |
|
1 2 3 4 |
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
|
1 2 |
connect sh/sh select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual; |
현재 ‘STALE_PERCENT’ 에 대한 Default 값 10 이 설정된 것을 확인 할 수 있다.
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 |
connect / as sysdba execute dbms_stats.set_global_prefs('STALE_PERCENT', '13'); |
|
1 2 3 4 5 6 |
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 으로 변경 된 것을 확인 할수 있다.
|
1 2 3 4 5 6 7 8 9 |
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
|
1 |
execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65'); |
|
1 2 3 4 |
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 로 변경된 것을 확인 할 수 있다.
|
1 2 3 4 5 6 7 8 9 |
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> |
|
1 |
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent from dual; |
그러나 다른 테이블을 확인해 보면 Global STALE_PERCENT 값을 13 의 값이 그대로 적용되어 있는 것이 확인된다.
|
1 2 3 4 5 6 7 |
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
|
1 |
execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT'); |
dbms_stats.delete_table_prefs 을 사용해서 삭제하면 Global STALE_PERCENT 값이 13으로 적용된다.
|
1 2 3 4 |
SQL> @reset_table_prefs SQL> execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT'); PL/SQL procedure successfully completed. SQL> |
|
1 2 |
connect sh/sh select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual; |
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 |
connect / as sysdba execute dbms_stats.set_global_prefs('STALE_PERCENT', null); |
execute dbms_stats.set_global_prefs 값이 null 이면 Default 값 10 이 적용된다.
|
1 2 3 4 5 6 |
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> |
|
1 2 |
connect sh/sh select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual; |
|
1 |
SQL> @check_sales_pref.sql |
|
1 2 3 4 5 6 7 8 |
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
|
1 2 3 4 |
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'); |
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 |
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 |
select dbms_stats.get_prefs('PUBLISH') publish from dual; |
|
1 2 3 4 5 6 |
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
|
1 |
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual; |
|
1 2 3 4 5 6 |
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
|
1 |
exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false'); |
|
1 2 3 4 |
SQL> @set_table_publish_prefs_false SQL> exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false'); PL/SQL procedure successfully completed. SQL> |
|
1 2 3 4 5 6 |
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
|
1 |
execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE'); |
|
1 2 3 4 |
SQL> @gather_table_stats.sql SQL> execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE'); PL/SQL procedure successfully completed. SQL> |
통계정보를 수집했음에도 PUBLISH 되지 않았음을 확인한다.
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
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 되기 이전 수집된 통계정보를 확인한다.
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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
|
1 2 |
alter session set optimizer_use_pending_statistics = false; alter session set optimizer_dynamic_sampling = 0; |
|
1 2 3 4 5 6 |
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
|
1 2 3 4 5 |
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')); |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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
|
1 |
alter session set optimizer_use_pending_statistics = true; |
|
1 2 3 4 |
SQL> @set_optimizer_pending_stats_true SQL> alter session set optimizer_use_pending_statistics = true; Session altered. SQL> |
SQL Plan을 조회한다.
|
1 2 3 4 5 |
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')); |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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
|
1 |
exec dbms_stats.publish_pending_stats(null, null) ; |
Pending 통계정보를 publishing 한다.
|
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 26 27 28 29 30 31 32 33 34 35 36 37 |
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 되어 남아 있지 않는다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 |
exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE'); |
set_global_publish.sql
|
1 |
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual; |
|
1 2 3 4 5 6 7 8 9 10 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
[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]$ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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> |
|
1 2 3 4 |
SQL> select count(*) from CUSTOMERS_OBE; COUNT(*) ---------- 630 |
통계정보 수집을 위한 default preferences 또는 parameter setting 을 변경 한다.
그 중 하나는 STALE_PERCENT 와 관련이 있으며 테이블 안에 레코드들의 비율 을 의미하는 STALE_PERCENT 은 새로 수집해야 한다고 판단되는 테이블에 대해서 통계정보를 수집하기 전에 변경해야 한다.
check_sales_pref.sql
|
1 2 |
connect sh/sh select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual; |
현재 ‘STALE_PERCENT 에 대한 Default 값 10 이 설정된 것을 확인 할 수 있다.
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 |
connect / as sysdba execute dbms_stats.set_global_prefs('STALE_PERCENT', '13'); |
|
1 2 3 4 5 6 |
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> |
|
1 2 |
connect sh/sh select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual; |
Global STALE_PERCENT 값 변경으로 STALE_PERCENT 값이 13 으로 변경 된 것을 확인 할수 있다.
|
1 2 3 4 5 6 7 8 9 |
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
|
1 |
execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65'); |
|
1 2 3 4 |
SQL> @change_table_pref.sql SQL> execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65'); PL/SQL procedure successfully completed. SQL> |
|
1 2 |
connect sh/sh select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual; |
Sh 유저 의 SALES 테이블에 대해 STALE_PERCENT 값이 65 로 변경된 것을 확인 할 수 있다.
|
1 2 3 4 5 6 7 8 9 |
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> |
|
1 |
select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent from dual; |
그러나 다른 테이블을 확인해 보면 Global STALE_PERCENT 값을 13 의 값이 그대로 적용되어 있는 것이 확인된다.
|
1 2 3 4 5 6 7 |
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
|
1 |
execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT'); |
dbms_stats.delete_table_prefs 을 사용해서 삭제하면 Global STALE_PERCENT 값이 13으로 적용된다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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
|
1 2 |
connect / as sysdba execute dbms_stats.set_global_prefs('STALE_PERCENT', null); |
execute dbms_stats.set_global_prefs 값이 null 이면 Default 값 10 이 적용된다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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
|
1 2 3 4 |
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'); |
|
1 2 3 4 5 6 7 8 9 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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)
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 로 설정 되어 있음을 확인 할 수 있다.
|
1 |
select dbms_stats.get_prefs('PUBLISH') publish from dual; |
|
1 2 3 4 5 6 |
SQL> @check_publish_prefs SQL> select dbms_stats.get_prefs('PUBLISH') publish from dual; PUBLISH -------------------------------------------------------------------------------- TRUE SQL> |
마찬가지로 ‘CUSTOMERS_OBE’ 또한 PUBLISH 값이 Default 값 TRUE 가 적용되었음을 확인 했다.
|
1 |
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual; |
|
1 2 3 4 5 6 |
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
|
1 |
exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false'); |
‘CUSTOMERS_OBE’ 의 PUBLISH 값을 False 로 변경 했으며, 이는 통계정보 수집후 바로 자동으로 PUBLISH 하는 것을 하지 않겠다는 것을 의미한다.
|
1 2 3 4 |
SQL> @set_table_publish_prefs_false SQL> exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false'); PL/SQL procedure successfully completed. SQL> |
|
1 |
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual; |
|
1 2 3 4 5 6 |
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
|
1 |
execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE'); |
|
1 2 3 4 |
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
|
1 2 3 4 5 6 7 8 |
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 |
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
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
|
1 2 3 4 5 6 7 8 |
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 |
|
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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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
|
1 2 |
alter session set optimizer_use_pending_statistics = false; alter session set optimizer_dynamic_sampling = 0; |
|
1 2 3 4 5 6 |
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
|
1 2 3 4 5 |
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 은 점점 더 느린 결과를 가져올 것이다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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
|
1 |
alter session set optimizer_use_pending_statistics = true; |
|
1 2 3 4 |
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 로 설정해서 사용할 수 있다.
|
1 2 3 4 5 |
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 통계정보가 훨씬 더 나은 결과를 보일 것 입니다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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
|
1 |
exec dbms_stats.publish_pending_stats(null, null) |
만일 Pending 통계정보가 충분히 입증 되었다면, 아래와 같이 Publishing 할 수 있다.
publish_pending_stats.sql
|
1 2 3 4 |
SQL> @publish_pending_stats SQL> exec dbms_stats.publish_pending_stats(null, null) PL/SQL procedure successfully completed. SQL> |
show_public_stats.sql
|
1 2 3 4 5 6 7 8 |
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 한다.
|
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 26 27 28 29 30 31 32 33 34 35 36 37 |
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
|
1 2 3 4 5 6 7 8 |
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 되어 남아 있지 않는다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 |
exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE'); |
dbms_stats.delete_table_stats 를 사용해서 기존의 통계정보를 삭제 합니다.
|
1 2 3 4 |
SQL> @delete_table_stats SQL> exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE'); PL/SQL procedure successfully completed. SQL> |
set_global_publish.sql
|
1 |
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual; |
기존의 통계정보는 삭제하고, 자동 publish 가 되도록 새롭게 통계정보를 수집합니다.
|
1 2 3 4 5 6 7 8 9 10 |
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 되도록 하여 애플리케이션의 성능을 극대화 할 수 있게 되었습니다.