{"id":417,"date":"2015-08-31T09:38:32","date_gmt":"2015-08-31T09:38:32","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=417"},"modified":"2015-08-31T09:38:32","modified_gmt":"2015-08-31T09:38:32","slug":"%eb%94%95%ec%85%94%eb%84%88%eb%a6%ac-%ed%86%b5%ea%b3%84%ec%a0%95%eb%b3%b4-%ed%99%95%ec%9d%b8-%eb%b0%a9%eb%b2%95dbmsstatssettablestats","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=417","title":{"rendered":"\ub515\uc154\ub108\ub9ac \ud1b5\uacc4\uc815\ubcf4 \ud655\uc778 \ubc29\ubc95(DBMS_STATS.SET_TABLE_STATS)"},"content":{"rendered":"

\uc774 \uc790\ub8cc\ub294 \ub515\uc154\ub108\ub9ac\uc5d0 \uc0dd\uc131\ub41c \ud1b5\uacc4\uc815\ubcf4\uac00 RDBMS\uac00 \uc0dd\uc131\ud55c \uac83\uc778\uc9c0, \uc544\ub2c8\uba74 DBMS_STATS.SET_XXX_STATS procedure\uc5d0 \uc758\ud574\uc11c \uc0dd\uc131\ub41c \uac83\uc778\uc9c0 \uad6c\ubd84\ud558\ub294 <\/span>\ubc29\ubc95\uc5d0 \ub300\ud55c \uc790\ub8cc\uc774\uba70, DBMS_STATS package\uc758 \uc0ac\uc6a9 \uc608\uc5d0 \ub300\ud558\uc5ec \uc54c\uc544\ubcf8\ub2e4. <\/span><\/p>\n

<\/p>\n

<\/p>\n

Explanation <\/p>\n

———– <\/p>\n

<\/p>\n

Oracle 8\uae4c\uc9c0\ub294 \ud14c\uc774\ube14, \uc778\ub371\uc2a4\uc5d0 \ub300\ud55c \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0dd\uc131\ud558\uae30 \uc704\ud574 ANALYZE \uba85\ub839\ub9cc\uc744 \uc0ac\uc6a9\ud560 \uc218 \uc788\uc5c8\uc73c\ub098 Oracle 8i \ubc84\uc83c\ubd80\ud130\ub294 ANALYZE \uba85\ub839 \uc678\uc5d0 <\/span>DBMS_STATS Package\ub97c \uc81c\uacf5\ud55c\ub2e4. <\/span><\/p>\n

<\/p>\n

ANALYZE \uba85\ub839\uc744 \uc0ac\uc6a9\ud560 \uacbd\uc6b0\uc5d0\ub294 \uc2e0\uaddc\ub85c \uc0dd\uc131\ud55c \ud1b5\uacc4\uc815\ubcf4\uac00 \uc131\ub2a5 \uc800\ud558\ub97c \uc720\ubc1c\uc2dc\ud0a4\ub354\ub77c\ub3c4 \uc774\uc804\uacfc \uac19\uc740 \ud1b5\uacc4\uc815\ubcf4\ub97c \ucd94\ucd9c\ud558\uae30 \uc704\ud574\uc11c\ub294 \ub2e4\uc2dc ANALYZE <\/span>\uc791\uc5c5\uc744 \uc218\ud589\ud574\uc57c \ub418\uae30 \ub54c\ubb38\uc5d0 \ud14c\uc774\ube14\uc758 \ud06c\uae30\uac00 \ud074 \uacbd\uc6b0 \ud1b5\uacc4\uc815\ubcf4\ub97c <\/span>\uc0dd\uc131\ud558\ub294 \ub370 \ub9ce\uc740 \uc2dc\uac04\uc744 \uc18c\ube44\ud558\uac8c \ub41c\ub2e4. <\/span><\/p>\n

<\/p>\n

\ub610\ud55c \uc77c\ubd80 Partitioned Table\uc5d0 \ub300\ud574 \ubd80\uc815\ud655\ud55c \ud1b5\uacc4\uc815\ubcf4\ub97c \ucd94\ucd9c\ud560 \uc218\ub3c4 \uc788\uae30 \ub54c\ubb38\uc5d0 Oracle 8i \ubd80\ud130\ub294 DBMS_STATS Package\ub97c \uc0ac\uc6a9\ud558\uc5ec \ud1b5\uacc4\uc815\ubcf4\ub97c <\/span>\uc0dd\uc131\ud558\uace0 \uad00\ub9ac\ud560 \uac83\uc744 \uad8c\uace0\ud558\uace0 \uc788\ub2e4. <\/span><\/p>\n

DBMS_STATS Package\ub97c \uc0ac\uc6a9\ud560 \uacbd\uc6b0 \ud1b5\uacc4\uc815\ubcf4\uc5d0 \ub300\ud55c \uc774\ub825 \uad00\ub9ac\uac00 \uac00\ub2a5\ud558\ub2e4. <\/p>\n

GATHER_TABLE_STATS, GATHER_INDEX_STATS, EXPORT_TABLE_STATS, <\/span>IMPORT_TABLE_STATS procedure\ub97c \uc0ac\uc6a9\ud558\uc5ec \uad00\ub9ac\uac00 \uac00\ub2a5\ud558\ub2e4. <\/span><\/p>\n

<\/p>\n

Application\uc744 \uac1c\ubc1c\ud558\ub294 \ub2e8\uacc4 \ub3d9\uc548 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0dd\uc131\ud558\ub294 \ub2e4\uc591\ud55c \ubc29\ubc95\uc744 \uc774\uc6a9\ud558\uc5ec SQL statement\uc758 \uc131\ub2a5\uc744 \uce21\uc815\ud574\ubcfc \uc218 \uc788\ub2e4. \uc774 \ub54c \ub515\uc154\ub108\ub9ac\uc5d0 <\/span>\uc0dd\uc131\ub41c \ud1b5\uacc4\uc815\ubcf4\uac00 RDBMS\uac00 \uc0dd\uc131\ud55c \uac83\uc778\uc9c0, USER\uac00 \uc815\uc758\ud55c \uac83\uc778\uc9c0 \ud655\uc778\ud574 <\/span>\ubcfc \ud544\uc694\uac00 \uc788\uc744 \uc218 \uc788\ub2e4. <\/span><\/p>\n

<\/p>\n

<\/p>\n

1. \ub515\uc154\ub108\ub9ac \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0dd\uc131\ud558\uae30 \uc704\ud574\uc11c\ub294 \ub2e4\uc74c\uacfc \uac19\uc740 \ubc29\ubc95\ub4e4\uc744 \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4. <\/p>\n

<\/p>\n

1) RDBMS-generated <\/p>\n

<\/p>\n

ANALYZE SQL command <\/p>\n

DBMS_UTILITY.ANALYZE_SCHEMA procedure <\/p>\n

DBMS_UTILITY.ANALYZE_DATABASE procedure <\/p>\n

DBMS_DDL.ANALYZE_OBJECT procedure <\/p>\n

8.1 DBMS_STATS.GATHER_XXX_STATS procedure <\/p>\n

<\/p>\n

2) USER-generated <\/p>\n

<\/p>\n

DBMS_STATS.SET_XXX_STATS procedure\uc5d0 \uc758\ud574 user\uac00 \uc815\uc758\ud558\ub294 \ubc29\ubc95 <\/p>\n

<\/p>\n

<\/p>\n

2. \uac01 OBJECT\ub4e4\uc5d0 \ub300\ud55c \ud1b5\uacc4\uc815\ubcf4 \uc0dd\uc131 \ubc29\ubc95 <\/p>\n

<\/p>\n

1) TABLE\uc5d0 \ub300\ud55c \ud1b5\uacc4\uc815\ubcf4 \uc0dd\uc131 <\/p>\n

<\/p>\n

– ANALYZE TABLE … COMPUTE\/ESTIMATE STATISTICS <\/p>\n

or <\/p>\n

– DBMS_STATS.SET_TABLE_STATS procedure <\/p>\n

<\/p>\n

2) INDEX\uc5d0 \ub300\ud55c \ud1b5\uacc4\uc815\ubcf4 \uc0dd\uc131 <\/p>\n

<\/p>\n

– ANALYZE TABLE\/INDEX … COMPUTE\/ESTIMATE STATISTICS <\/p>\n

or <\/p>\n

– DBMS_STATS.SET_INDEX_STATS procedure <\/p>\n

<\/p>\n

3) COLUMN\uc5d0 \ub300\ud55c \ud1b5\uacc4\uc815\ubcf4 \uc0dd\uc131 <\/p>\n

<\/p>\n

– ANALYZE TABLE … COMPUTE\/ESTIMATE STATISTICS <\/p>\n

or <\/p>\n

– DBMS_STATS.SET_COLUMN_STATS procedure <\/p>\n

<\/p>\n

<\/p>\n

3. \ub515\uc154\ub108\ub9ac \ubdf0 \uc815\ubcf4 <\/p>\n

<\/p>\n

<\/p>\n

DBA_TABLES, ALL_TABLES, USER_TABLES \ubdf0\ub97c \ubcf4\uba74 USER_STATS\ub77c\ub294 \uceec\ub7fc\uc774 \uc788\ub2e4. <\/p>\n

<\/p>\n

YES : \ud1b5\uacc4\uc815\ubcf4\uac00 USER\uc5d0 \uc758\ud574 \uc9c1\uc811 \uc0dd\uc131\ub41c \uacbd\uc6b0 <\/p>\n

NO : \ud1b5\uacc4\uc815\ubcf4\uac00 ANALYZE command\ub97c \ud1b5\ud574\uc11c RDBMS\uc5d0 \uc758\ud574 \uc0dd\uc131\ub41c \uacbd\uc6b0 <\/p>\n

<\/p>\n

\ub2e4\uc74c\uc5d0 \ub098\uc5f4\ud55c \ubdf0\ub4e4\uc740 \ud1b5\uacc4\uc815\ubcf4\uc5d0 \ub300\ud574 \uac19\uc740 \uceec\ub7fc \uc815\ubcf4\ub4e4\uc744 \uac16\ub294\ub2e4. <\/p>\n

<\/p>\n

1) TABLE <\/p>\n

<\/p>\n

DBA_ALL_TABLES : object\uc640 \uc5f0\uad00\ub41c table \ud1b5\uacc4\uc815\ubcf4 <\/p>\n

DBA_OBJECT_TABLES : object table \ud1b5\uacc4\uc815\ubcf4 <\/p>\n

DBA_TAB_PARTITIONS : table partition \ud1b5\uacc4\uc815\ubcf4 <\/p>\n

DBA_TAB_SUBPARTITIONS : table subpartition \ud1b5\uacc4\uc815\ubcf4 <\/p>\n

<\/p>\n

2) INDEX <\/p>\n

<\/p>\n

DBA_INDEXES : index \ud1b5\uacc4\uc815\ubcf4 <\/p>\n

DBA_IND_PARTITIONS : index partition \ud1b5\uacc4\uc815\ubcf4 <\/p>\n

DBA_IND_SUBPARTITIONS : index subpartition \ud1b5\uacc4\uc815\ubcf4 <\/p>\n

<\/p>\n

3) COLUMN <\/p>\n

<\/p>\n

DBA_TAB_COLUMNS : table column \ud1b5\uacc4\uc815\ubcf4 <\/p>\n

<\/p>\n

\uc704\uc640 \uc5f0\uad00\ub41c USER_% \ubdf0\uc640 ALL_% \ubdf0 \ub0b4\uc5d0\ub3c4 \uac19\uc740 \ud1b5\uacc4\uc815\ubcf4\uac00 \uc800\uc7a5\ub41c\ub2e4. <\/p>\n

<\/p>\n

<\/p>\n

Example <\/p>\n

——- <\/p>\n

<\/p>\n

\uc774 \ud14c\uc2a4\ud2b8\ub294 Oracle 8i Enterprise Edition Release V8.1.7.4.0\uc5d0\uc11c \uc774\ub8e8\uc5b4\uc84c\ub2e4. <\/p>\n

<\/p>\n

1. Table\uc5d0 \ub300\ud55c example <\/p>\n

<\/p>\n

1) COMPUTE STATISTICS option\uc73c\ub85c table\uc744 ANALYZE\ud558\uae30 <\/p>\n

<\/p>\n

SQL> analyze table scott.emp compute statistics; <\/p>\n

Table analyzed. <\/p>\n

<\/p>\n

SQL> select table_name,num_rows,blocks,avg_row_len,user_stats <\/p>\n

FROM dba_tables <\/p>\n

where table_name=’EMP’; <\/p>\n

<\/p>\n

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN USER_STATS <\/p>\n

—————– ——– —— ———– ———- <\/p>\n

EMP 14 1 40 NO <\/p>\n

<\/p>\n

2) SET_TABLE_STATS procedure\ub97c \uc0ac\uc6a9\ud558\uc5ec \ud1b5\uacc4\uc815\ubcf4 \uc0dd\uc131\ud558\uae30 <\/p>\n

<\/p>\n

SQL> execute sys.dbms_stats.set_table_stats( – <\/p>\n

ownname => ‘SCOTT’, – <\/p>\n

tabname => ‘EMP’, – <\/p>\n

partname => null, – <\/p>\n

stattab => null, – <\/p>\n

statid => null, – <\/p>\n

numrows => 100, – <\/p>\n

numblks => 10, – <\/p>\n

avgrlen => 33); <\/p>\n

PL\/SQL procedure successfully completed. <\/p>\n

<\/p>\n

SQL> SELECT table_name,num_rows,blocks,avg_row_len,user_stats <\/p>\n

FROM dba_tables <\/p>\n

where table_name=’EMP’; <\/p>\n

<\/p>\n

TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN USER_STATS <\/p>\n

—————– ——– —— ———– ———- <\/p>\n

EMP 100 10 33 YES <\/p>\n

<\/p>\n

<\/p>\n

2. Index\uc5d0 \ub300\ud55c example <\/p>\n

<\/p>\n

1) COMPUTE STATISTICS option\uc73c\ub85c index\ub97c ANALYZE\ud558\uae30 <\/p>\n

<\/p>\n

SQL> analyze index pk_emp compute statistics; <\/p>\n

Index analyzed. <\/p>\n

<\/p>\n

SQL> select index_name, user_stats, BLEVEL, LEAF_BLOCKS, <\/p>\n

CLUSTERING_FACTOR, NUM_ROWS <\/p>\n

from dba_indexes <\/p>\n

where table_name=’EMP’; <\/p>\n

<\/p>\n

INDEX_NAME USER_STATS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS <\/p>\n

———- ———- —— ———– —————– ——– <\/p>\n

PK_EMP NO 0 1 1 14 <\/p>\n

<\/p>\n

2) SET_INDEX_STATS procedure\ub97c \uc0ac\uc6a9\ud558\uc5ec \ud1b5\uacc4\uc815\ubcf4 \uc0dd\uc131\ud558\uae30 <\/p>\n

<\/p>\n

SQL> execute sys.dbms_stats.set_index_stats ( – <\/p>\n

ownname => ‘SCOTT’, – <\/p>\n

indname => ‘PK_EMP’, – <\/p>\n

numrows => 100 , – <\/p>\n

numlblks => 10, – <\/p>\n

clstfct => 4, – <\/p>\n

indlevel => 3); <\/p>\n

PL\/SQL procedure successfully completed. <\/p>\n

<\/p>\n

SQL> select index_name, user_stats, BLEVEL, LEAF_BLOCKS, <\/p>\n

CLUSTERING_FACTOR, NUM_ROWS <\/p>\n

from dba_indexes where table_name=’EMP’; <\/p>\n

<\/p>\n

INDEX_NAME USER_STATS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS <\/p>\n

———- ———- —— ———– —————– ——– <\/p>\n

PK_EMP YES 3 10 4 100 <\/p>\n

<\/p>\n

<\/p>\n

\uc8fc\uc758 <\/p>\n

—- <\/p>\n

\ub9cc\uc57d, index \ub610\ub294 \uc804\uccb4 table\uc744 reanalyze\ud558\uba74 \ube44\ub85d \ud1b5\uacc4\uc815\ubcf4\uac00 RESET \ub418\uc5b4\ub3c4 USER_STATS\ub294 \uc5ec\uc804\ud788 YES\ub85c \ub098\ud0c0\ub09c\ub2e4. <\/span><\/p>\n

<\/p>\n

SQL> analyze index pk_emp compute statistics; <\/p>\n

Index analyzed. <\/p>\n

<\/p>\n

SQL> select index_name, user_stats, BLEVEL, LEAF_BLOCKS, <\/p>\n

CLUSTERING_FACTOR, NUM_ROWS <\/p>\n

from dba_indexes where table_name=’EMP’; <\/p>\n

<\/p>\n

INDEX_NAME USER_STATS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS <\/p>\n

———- ———- —— ———– —————– ——– <\/p>\n

PK_EMP YES 0 1 1 14 <\/p>\n

<\/p>\n

<\/p>\n

SQL> analyze table emp compute statistics; <\/p>\n

Table analyzed. <\/p>\n

<\/p>\n

SQL> select index_name, user_stats, BLEVEL, LEAF_BLOCKS, <\/p>\n

CLUSTERING_FACTOR, NUM_ROWS <\/p>\n

from dba_indexes where table_name=’EMP’ <\/p>\n

<\/p>\n

INDEX_NAME USER_STATS BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS <\/p>\n

———- ———- —— ———– —————– ——– <\/p>\n

PK_EMP YES 0 1 1 14 <\/p>\n

<\/p>\n

<\/p>\n

3. Column\uc5d0 \ub300\ud55c example <\/p>\n

<\/p>\n

1) COMPUTE STATISTICS option\uc73c\ub85c ANALYZE\ud558\uae30 <\/p>\n

<\/p>\n

SQL> select num_distinct, num_nulls, avg_col_len, user_stats <\/p>\n

from dba_tab_columns <\/p>\n

where table_name=’EMP’ and column_name= ‘SAL’; <\/p>\n

<\/p>\n

NUM_DISTINCT NUM_NULLS AVG_COL_LEN USER_STATS <\/p>\n

———— ——— ———– ———- <\/p>\n

12 0 3 NO <\/p>\n

<\/p>\n

2) SET_COLUMN_STATS procedure\ub97c \uc0ac\uc6a9\ud558\uc5ec \ud1b5\uacc4\uc815\ubcf4 \uc0dd\uc131\ud558\uae30 <\/p>\n

<\/p>\n

SQL> execute sys.dbms_stats.set_column_stats(- <\/p>\n

ownname => ‘SCOTT’, – <\/p>\n

tabname => ‘EMP’,- <\/p>\n

colname => ‘SAL’, – <\/p>\n

distcnt => 1500, – <\/p>\n

nullcnt => 1, – <\/p>\n

avgclen => 9); <\/p>\n

PL\/SQL procedure successfully completed. <\/p>\n

<\/p>\n

<\/p>\n

SQL> select num_distinct, num_nulls, avg_col_len, user_stats <\/p>\n

from dba_tab_columns <\/p>\n

where table_name=’EMP’ and column_name= ‘SAL’; <\/p>\n

<\/p>\n

NUM_DISTINCT NUM_NULLS AVG_COL_LEN USER_STATS <\/p>\n

———— ——— ———– ———- <\/p>\n

1500 1 9 YES <\/p>\n

<\/p>\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

\uc774 \uc790\ub8cc\ub294 \ub515\uc154\ub108\ub9ac\uc5d0 \uc0dd\uc131\ub41c \ud1b5\uacc4\uc815\ubcf4\uac00 RDBMS\uac00 \uc0dd\uc131\ud55c \uac83\uc778\uc9c0, \uc544\ub2c8\uba74 DBMS_STATS.SET_XXX_STATS procedure\uc5d0 \uc758\ud574\uc11c \uc0dd\uc131\ub41c \uac83\uc778\uc9c0 \uad6c\ubd84\ud558\ub294 \ubc29\ubc95\uc5d0 \ub300\ud55c \uc790\ub8cc\uc774\uba70, DBMS_STATS package\uc758 \uc0ac\uc6a9 \uc608\uc5d0 \ub300\ud558\uc5ec \uc54c\uc544\ubcf8\ub2e4.  Explanation  ———–  Oracle 8\uae4c\uc9c0\ub294 \ud14c\uc774\ube14, \uc778\ub371\uc2a4\uc5d0 \ub300\ud55c \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0dd\uc131\ud558\uae30 \uc704\ud574 ANALYZE \uba85\ub839\ub9cc\uc744 \uc0ac\uc6a9\ud560 \uc218 \uc788\uc5c8\uc73c\ub098 Oracle 8i \ubc84\uc83c\ubd80\ud130\ub294 ANALYZE \uba85\ub839 \uc678\uc5d0 DBMS_STATS Package\ub97c \uc81c\uacf5\ud55c\ub2e4.  ANALYZE \uba85\ub839\uc744 \uc0ac\uc6a9\ud560 \uacbd\uc6b0\uc5d0\ub294 \uc2e0\uaddc\ub85c \uc0dd\uc131\ud55c \ud1b5\uacc4\uc815\ubcf4\uac00 \uc131\ub2a5 \uc800\ud558\ub97c \uc720\ubc1c\uc2dc\ud0a4\ub354\ub77c\ub3c4 \uc774\uc804\uacfc \uac19\uc740 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[161,133,134,162],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/417"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=417"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/417\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=417"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=417"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=417"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}