\ud1b5\uacc4\uc815\ubcf4 Gathering \ubc0f Publish Commands<\/strong><\/p>\nPublish Attribute \ud655\uc778\ubc29\ubc95<\/p>\n
SQL> select dbms_stats.get_prefs(\u2018PUBLISH\u2019) publish from dual;<\/pre>\nTRUE: \ud1b5\uacc4\uc815\ubcf4\uac00 \uc218\uc9d1\ub418\uc9c0\ub9c8\uc790 \ubc14\ub85c Publish<\/strong>
\nFALSE: \uc218\uc9d1\ub41c \ud1b5\uacc4\uc815\ubcf4\ub294 Publish \ub418\uc9c0 \uc54a\uace0 Pending <\/strong><\/p>\n <\/p>\n
Publish \ub41c \ud1b5\uacc4\uc815\ubcf4\ub294 user_tab_stats, user_ind_stats\uc5d0 \uc800\uc7a5\ub418\uace0, pending \ub41c \ud1b5\uacc4\uc815\ubcf4\ub294 user_tab_pending_stats, user_ind_pending_stats\uc5d0 \uc800\uc7a5\ub41c\ub2e4.<\/strong><\/span><\/p>\n <\/p>\n
<\/p>\n
Publish Attribute \uc124\uc815\uc744 \ubcc0\uacbd\ud558\ub294 \ubc29\ubc95<\/strong><\/em><\/span><\/p>\nPublish \uc18d\uc131\ubcc0\uacbd\uc740 Schema Level \ub610\ub294 Table Level\uc5d0\uc11c \ubcc0\uacbd \uac00\ub2a5\ud558\ub2e4.<\/p>\n
\uc544\ub798\uc758 \uc608\ub294 SH.CUSTOMERS\uc758 Publish \uc18d\uc131\uc744 false\ub85c \ubcc0\uacbd\ud558\uc5ec \uc774\ud6c4 \ud1b5\uacc4\uc815\ubcf4\uac00 \uc218\uc9d1\ub418\ub354\ub77c\ub3c4 publish\ub418\uc9c0 \uc54a\uace0 user_tab_pending_stats\uc5d0 \uc800\uc7a5\ub41c\ub2e4.<\/p>\n
SQL> Exec dbms_stats.set_table_prefs(\u2018SH\u2019, \u2018CUSTOMERS\u2019, \u2018PUBLISH\u2019, \u2018false\u2019);<\/pre>\n <\/p>\n
Pending \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0ac\uc6a9\ud558\ub294 \ubc29\ubc95<\/span><\/strong><\/em><\/p>\n\uc635\ud2f0\ub9c8\uc774\uc838\ub294 default\ub85c Publish\ub41c \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0ac\uc6a9\ud55c\ub2e4.<\/p>\n
\uc778\uc704\uc801\uc73c\ub85c pending \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0ac\uc6a9\ud1a0\ub85d \ud558\uae30 \uc704\ud574\uc11c\ub294 init\ud30c\uc77c\uc5d0 OPTIMIZER_PENDING_STATISTICS=TRUE \uc73c\ub85c \uc124\uc815\ud558\uac70\ub098, session level\uc5d0\uc11c \ub2e4\uc74c\uacfc \uac19\uc774 \ubcc0\uacbd\ud55c\ub2e4.<\/p>\n
SQL> alter session set optimizer_pending_statistis=TRUE;<\/pre>\n <\/p>\n
Pending \ud1b5\uacc4\uc815\ubcf4\ub97c Publish \ud558\ub294 \ubc29\ubc95<\/strong><\/em><\/p>\n\ubaa8\ub4e0 valid pending \ud1b5\uacc4\uc815\ubcf4\ub97c Publish<\/p>\n
SQL> Exec dbms_stats.publish_pending_stats(null, null);<\/pre>\n <\/p>\n
\ud2b9\uc815 object\uc5d0 \ub300\ud55c pending \ud1b5\uacc4\uc815\ubcf4\ub97c Publish<\/p>\n
SQL> Exec dbms_stats.publish_pending_stats(\u2018SH\u2019, \u2018CUSTOMERS\u2019);<\/pre>\n <\/p>\n
\ucc38\uace0\ub85c, pending \ud1b5\uacc4\uc815\ubcf4\ub294 dbms_stats.export_pending_stats \ud568\uc218\ub97c \uc0ac\uc6a9\ud558\uc5ec export\ud558\uc5ec test \uc11c\ubc84\uc5d0 import\ud558\uc5ec test\ub97c \uc218\ud589\ud560 \uc218\ub3c4 \uc788\ub2e4.<\/p>\n
<\/p>\n
\uc608\uc81c<\/strong><\/span><\/p>\n\ud1b5\uacc4\uc815\ubcf4 \ubcc0\uacbd\uc5d0 \ub300\ud55c preference\uac12\uc744 \ubcc0\uacbd\ud558\uace0, pending statistics\ub97c \uc218\uc9d1\ud558\uc5ec SQL Plan\uc744 \uac80\uc99d\ud558\uace0, \uae30\uc874\uc758 plan\ubcf4\ub2e4 \ub354 \ub098\ub294 SQL Plan \ub77c\ub294 \ud310\ub2e8\uc774 \ub418\uba74 publish \ud558\uc5ec \uc2e4 \uc6b4\uc601 DB\uc5d0 \ubc18\uc601\ud558\ub294 \ud14c\uc2a4\ud2b8\uc774\ub2e4.<\/p>\n
Changing Global and Table Statistic Preferences<\/strong><\/span><\/p>\n\uc608\uc81c \uc2e4\uc2b5\uc744 \uc704\ud55c \ud14c\uc774\ube14 Import \ubc0f \ud655\uc778<\/p>\n
[oracle@obe11g gathstats]$ imp sh\/sh file=customers_obe.dmp log=log full=y\r\nImport: Release 11.1.0.5.0 - Beta on Wed Sep 12 09:59:15 2007\r\nCopyright (c) 1982, 2007, Oracle. All rights reserved.\r\nConnected to: Oracle Database 11g Enterprise Edition Release 11.1.0.5.0 - Beta\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\nExport file created by EXPORT:V11.01.00 via conventional path\r\nimport done in US7ASCII character set and AL16UTF16 NCHAR character set\r\nimport server uses AL32UTF8 character set (possible charset conversion)\r\n. importing SH's objects into SH\r\n. importing SH's objects into SH\r\n. . importing table \"CUSTOMERS_OBE\" 630 rows imported\r\nImport terminated successfully without warnings.\r\n[oracle@obe11g gathstats]$<\/pre>\nSQL> desc CUSTOMERS_OBE\r\nName Null? Type\r\n----------------------------------------- -------- ----------------------------\r\nCUST_ID NUMBER\r\nCUST_FIRST_NAME NOT NULL VARCHAR2(20)\r\nCUST_LAST_NAME NOT NULL VARCHAR2(40)\r\nCUST_GENDER CHAR(1)\r\nCUST_YEAR_OF_BIRTH NUMBER(4)\r\nCUST_MARITAL_STATUS VARCHAR2(20)\r\nCUST_STREET_ADDRESS NOT NULL VARCHAR2(40)\r\nCUST_POSTAL_CODE NOT NULL VARCHAR2(10)\r\nCUST_CITY NOT NULL VARCHAR2(30)\r\nCUST_STATE_PROVINCE VARCHAR2(40)\r\nCOUNTRY_ID NOT NULL CHAR(2)\r\nCUST_MAIN_PHONE_NUMBER VARCHAR2(25)\r\nCUST_INCOME_LEVEL VARCHAR2(30)\r\nCUST_CREDIT_LIMIT NUMBER\r\nCUST_EMAIL VARCHAR2(30)\r\nSQL><\/pre>\nSQL> select count(*) from CUSTOMERS_OBE;\r\nCOUNT(*)\r\n----------\r\n630<\/pre>\n <\/p>\n
\ud1b5\uacc4\uc815\ubcf4 \uc218\uc9d1\uc744 \uc704\ud55c default preferences \ub610\ub294 parameter setting \uc744 \ubcc0\uacbd \ud55c\ub2e4.<\/p>\n
\uadf8 \uc911 \uc8fc\uc694 \uc124\uc815\uac12\uc740 STALE_PERCENT.STALE_PERCENT \uc774\ub2e4.<\/p>\n
\uc774 \uc124\uc815\uac12\uc740 \ud14c\uc774\ube14\uc758 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc7ac \uc218\uc9d1\ud574\uc57c \ud558\ub294 rows\u00a0 \ubcc0\uacbd\ub7c9(%)\uc744 \uc9c0\uc815\ud558\ub294 \uac83\uc73c\ub85c rows\ubcc0\uacbd\ub7c9\uc774 STALE_PERCENT \uc774\uc0c1\uc774\uba74 Table\uc758 \ud1b5\uacc4\uc815\ubcf4\uc758 \uc0c1\ud0dc\ub294 STALE\uc774 \ub418\uc5b4 \ud1b5\uacc4\uc815\ubcf4 \uc7ac\uc218\uc9d1 \ub300\uc0c1\uc774 \ub41c\ub2e4.<\/p>\n
check_sales_pref.sql<\/p>\n
connect sh\/sh\r\nselect dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;<\/pre>\n <\/p>\n
\ud604\uc7ac ‘STALE_PERCENT’ \uc5d0 \ub300\ud55c Default \uac12 10 \uc774 \uc124\uc815\ub41c \uac83\uc744 \ud655\uc778 \ud560 \uc218 \uc788\ub2e4.<\/p>\n
SQL> @check_sales_pref.sql\r\nSQL> connect sh\/sh\r\nConnected.\r\nSQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent\r\n2 from dual;\r\nSTALE_PERCENT\r\n--------------------------------------------------------------------------------\r\n10\r\nSQL><\/pre>\n <\/p>\n
Global STALE_PERCENT \uac12\uc744 13 \uc73c\ub85c \ubcc0\uacbd\ud55c\ub2e4.<\/p>\n
change_global_pref.sql<\/p>\n
connect \/ as sysdba\r\nexecute dbms_stats.set_global_prefs('STALE_PERCENT', '13');<\/pre>\nSQL> @change_global_pref.sql\r\nSQL> connect \/ as sysdba\r\nConnected.\r\nSQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', '13');\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\n <\/p>\n
Global STALE_PERCENT \uac12 \ubcc0\uacbd\uc73c\ub85c STALE_PERCENT \uac12\uc774 13 \uc73c\ub85c \ubcc0\uacbd \ub41c \uac83\uc744 \ud655\uc778 \ud560\uc218 \uc788\ub2e4.<\/p>\n
SQL> @check_sales_pref.sql\r\nSQL> connect sh\/sh\r\nConnected.\r\nSQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent\r\n2 from dual;\r\nSTALE_PERCENT\r\n--------------------------------------------------------------------------------\r\n13\r\nSQL><\/pre>\n <\/p>\n
Global STALE_PERCENT \uac12\uacfc\ub294 \ubcc4\uac1c\ub85c Single \ud14c\uc774\ube14\uc758 STALE_PERCENT \ubcc0\uacbd\uc744 \uc6d0\ud55c\ub2e4\uba74 \uc544\ub798\uc640 \uac19\uc774 \ubcc0\uacbd\uc774 \uac00\ub2a5\ud558\ub2e4.<\/p>\n
change_table_pref.sql<\/p>\n
execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65');<\/pre>\nSQL> @change_table_pref.sql\r\nSQL> execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65');\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\n <\/p>\n
Sh \uc720\uc800 \uc758 SALES \ud14c\uc774\ube14\uc5d0 \ub300\ud574 STALE_PERCENT \uac12\uc774 65 \ub85c \ubcc0\uacbd\ub41c \uac83\uc744 \ud655\uc778 \ud560 \uc218 \uc788\ub2e4.<\/p>\n
SQL> @check_sales_pref.sql\r\nSQL> connect sh\/sh\r\nConnected.\r\nSQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent\r\n2 from dual;\r\nSTALE_PERCENT\r\n--------------------------------------------------------------------------------\r\n65\r\nSQL><\/pre>\nselect dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent from dual;<\/pre>\n <\/p>\n
\uadf8\ub7ec\ub098 \ub2e4\ub978 \ud14c\uc774\ube14\uc744 \ud655\uc778\ud574 \ubcf4\uba74 Global STALE_PERCENT \uac12\uc744 13 \uc758 \uac12\uc774 \uadf8\ub300\ub85c \uc801\uc6a9\ub418\uc5b4 \uc788\ub294 \uac83\uc774 \ud655\uc778\ub41c\ub2e4.<\/p>\n
<\/p>\n
SQL> @check_products_pref.sql\r\nSQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent\r\n2 from dual;\r\nSTALE_PERCENT\r\n--------------------------------------------------------------------------------\r\n13\r\nSQL><\/pre>\n <\/p>\n
\ubcc0\uacbd\ub41c STALE_PERCENT \uac12\uc744 default \uac12\uc73c\ub85c \ubcc0\uacbd\ud558\uae30 \uc704\ud574\uc11c\ub294 SH.SALES\uc758 \uc124\uc815\uac12\uc740 \uc9c0\uc6b0\uba74 \ub41c\ub2e4.<\/p>\n
reset_table_prefs<\/p>\n
execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT');<\/pre>\n <\/p>\n
dbms_stats.delete_table_prefs \uc744 \uc0ac\uc6a9\ud574\uc11c \uc0ad\uc81c\ud558\uba74 Global STALE_PERCENT \uac12\uc774 13\uc73c\ub85c \uc801\uc6a9\ub41c\ub2e4.<\/p>\n
SQL> @reset_table_prefs\r\nSQL> execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT');\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\nconnect sh\/sh\r\nselect dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;<\/pre>\nSQL> @check_sales_pref.sql\r\nSQL> connect sh\/sh\r\nConnected.\r\nSQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent\r\n2 from dual;\r\nSTALE_PERCENT\r\n--------------------------------------------------------------------------------\r\n13\r\nSQL><\/pre>\nGlobal STALE_PERCENT \uac12\uc744 default\ub85c \ub418\ub3cc\ub9ac\ub294 \ubc29\ubc95\uc740 \uc544\ub798\uc640 \uac19\ub2e4.<\/p>\n
reset_global_prefs.sql<\/p>\n
connect \/ as sysdba\r\nexecute dbms_stats.set_global_prefs('STALE_PERCENT', null);<\/pre>\n <\/p>\n
execute dbms_stats.set_global_prefs \uac12\uc774 null \uc774\uba74 Default \uac12 10 \uc774 \uc801\uc6a9\ub41c\ub2e4.<\/p>\n
SQL> @reset_global_prefs.sql\r\nSQL> connect \/ as sysdba\r\nConnected.\r\nSQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', null);\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\nconnect sh\/sh\r\nselect dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;<\/pre>\nSQL> @check_sales_pref.sql<\/pre>\nSQL> connect sh\/sh\r\nConnected.\r\nSQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent\r\n2 from dual;\r\nSTALE_PERCENT\r\n--------------------------------------------------------------------------------\r\n10\r\nSQL><\/pre>\n <\/p>\n
Gathering Pending Statistics<\/strong><\/span><\/p>\n\uc774\ubc88 \ud14c\uc2a4\ud2b8\ub294 \ud14c\uc774\ube14\uc758 public \uacfc pending \ud1b5\uacc4\uc815\ubcf4\ub97c \ud655\uc778\ud558\ub294 \ubc29\ubc95\uacfc, publishing \uc5c6\uc774 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1 \ud558\ub294 \ubc29\ubc95\uc744 \uc18c\uac1c\ud55c\ub2e4.<\/p>\n
\uc2e4\uc2b5\uc5d0 \ud558\uae30\uc804\uc5d0 ‘CUSTOMERS_OBE\u2019 \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \ub0a0\uc9dc \ud615\uc2dd\uc744 \ub9de\ucd94\uace0, \uc774\uc804\uc758 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0ad\uc81c\ud55c\ub2e4.<\/p>\n
reset_table_stats<\/p>\n
connect sh\/sh\r\nalter session set nls_date_format='mm\/dd hh24:mi:ss';\r\n-- delete statistics\r\nexec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');<\/pre>\nSQL> @reset_table_stats\r\nSQL> connect sh\/sh\r\nConnected.\r\nSQL> alter session set nls_date_format='mm\/dd hh24:mi:ss';\r\nSession altered.\r\nSQL> -- delete statistics\r\nSQL> exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\n <\/p>\n
CUSTOMERS_OBE \uc5d0 \ub300\ud55c public statistics (table, index, column) \ud655\uc778\ud558\ub294 \ubc29\ubc95\uc740 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n
show_public_stats.sql<\/p>\n
set echo off\r\n-- tables\r\nselect table_name, last_analyzed \"analyze time\", num_rows, blocks, avg_row_len\r\nfrom user_tab_pending_stats\r\nwhere table_name = '&1' and partition_name is null;\r\n-- indexes\r\nselect index_name, last_analyzed \"analyze time\", num_rows,\r\nleaf_blocks, distinct_keys\r\nfrom user_ind_pending_stats\r\nwhere table_name = '&1' and partition_name is null\r\norder by index_name;\r\n-- columns\r\nselect column_name, last_analyzed \"analyze time\", num_distinct,\r\nnum_nulls, density\r\nfrom user_col_pending_stats\r\nwhere table_name = '&1' and partition_name is null\r\norder by column_name;\r\nset echo on<\/pre>\n <\/p>\n
SQL> @show_public_stats CUSTOMERS_OBE\r\nSQL>\r\nSQL> set echo off\r\nold 3: where table_name = '&1'\r\nnew 3: where table_name = 'CUSTOMERS_OBE'\r\nTABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN\r\n------------------------------ -------------- ---------- ---------- -----------\r\nCUSTOMERS_OBE\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nINDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS\r\n------------------------------ -------------- ---------- -----------\r\nDISTINCT_KEYS\r\n-------------\r\nOBE_CUST_CRED_LMT_IND\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nCOLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCOUNTRY_ID\r\nCUST_CITY\r\nCUST_CREDIT_LIMIT\r\nCUST_EMAIL\r\nCUST_FIRST_NAME\r\nCUST_GENDER\r\nCUST_ID\r\nCUST_INCOME_LEVEL\r\nCUST_LAST_NAME\r\nCUST_MAIN_PHONE_NUMBER\r\nCUST_MARITAL_STATUS\r\nCOLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCUST_POSTAL_CODE\r\nCUST_STATE_PROVINCE\r\nCUST_STREET_ADDRESS\r\nCUST_YEAR_OF_BIRTH\r\n15 rows selected.\r\nSQL><\/pre>\n <\/p>\n
CUSTOMERS_OBE \uc5d0 \ub300\ud55c pending statistics (table, index, column) \ud655\uc778\ud558\ub294 \ubc29\ubc95\uc740 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n
<\/p>\n
show_pending_stats.sql<\/p>\n
set echo off\r\n-- tables\r\nselect table_name, last_analyzed \"analyze time\", num_rows, blocks, avg_row_len\r\nfrom user_tab_pending_stats\r\nwhere table_name = '&1' and partition_name is null;\r\n-- indexes\r\nselect index_name, last_analyzed \"analyze time\", num_rows,\r\nleaf_blocks, distinct_keys\r\nfrom user_ind_pending_stats\r\nwhere table_name = '&1' and partition_name is null\r\norder by index_name;\r\n-- columns\r\nselect column_name, last_analyzed \"analyze time\", num_distinct,\r\nnum_nulls, density\r\nfrom user_col_pending_stats\r\nwhere table_name = '&1' and partition_name is null\r\norder by column_name;\r\nset echo on<\/pre>\n <\/p>\n
SQL> @show_pending_stats CUSTOMERS_OBE\r\nSQL>\r\nSQL> set echo off\r\nold 3: where table_name = '&1' and partition_name is null\r\nnew 3: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nno rows selected\r\nold 4: where table_name = '&1' and partition_name is null\r\nnew 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nno rows selected\r\nold 4: where table_name = '&1' and partition_name is null\r\nnew 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nno rows selected\r\nSQL><\/pre>\n <\/p>\n
\ud604\uc7ac CUSTOMERS_OBE \ud14c\uc774\ube14\uc5d0 \ub300\ud55c public, pending \ud1b5\uacc4\uc815\ubcf4\ub294 \uc5c6\ub294 \uac83\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub2e4.<\/p>\n
11g\ub294 default\ub85c \ud1b5\uacc4\uc815\ubcf4\uac00 \uc218\uc9d1\ub418\uc790 \ub9c8\uc790 Publish \ub41c\ub2e4.<\/p>\n
\uc774\ub7ec\ud55c PUBLISH (default : TRUE) \uc124\uc815\uac12\uc740 dbms_stats.get_prefs\ub85c \ud655\uc778\ud560 \uc218 \uc788\ub2e4.<\/p>\n
check_publish_prefs.sql<\/p>\n
select dbms_stats.get_prefs('PUBLISH') publish from dual;<\/pre>\nSQL> @check_publish_prefs\r\nSQL> select dbms_stats.get_prefs('PUBLISH') publish from dual;\r\nPUBLISH\r\n--------------------------------------------------------------------------------\r\nTRUE\r\nSQL>\r\n<\/pre>\n <\/p>\n
\ub9c8\ucc2c\uac00\uc9c0\ub85c ‘CUSTOMERS_OBE’ \ub610\ud55c PUBLISH \uac12\uc774 Default \uac12 TRUE \uac00 \uc801\uc6a9\ub418\uc5c8\uc74c\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub2e4.<\/p>\n
check_table_publish_prefs.sql<\/p>\n
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;<\/pre>\nSQL> @check_table_publish_prefs\r\nSQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;\r\nPUBLISH\r\n--------------------------------------------------------------------------------\r\nTRUE\r\nSQL><\/pre>\n <\/p>\n
‘CUSTOMERS_OBE’ \uc758 PUBLISH \uac12\uc744 False \ub85c \ubcc0\uacbd\ud55c\ub2e4.<\/p>\n
\uc774\ub294 \ud1b5\uacc4\uc815\ubcf4 \uc218\uc9d1 \ud6c4 \ubc14\ub85c \uc790\ub3d9\uc73c\ub85c PUBLISH \ud558\ub294\uac83\uc744 \ud558\uc9c0 \uc54a\uaca0\ub2e4\ub294 \uac83\uc744 \uc758\ubbf8\ud55c\ub2e4.<\/p>\n
set_table_publish_prefs_false.sql<\/p>\n
exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false');<\/pre>\nSQL> @set_table_publish_prefs_false\r\nSQL> exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false');\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\nSQL> @check_table_publish_prefs\r\nSQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;\r\nPUBLISH\r\n--------------------------------------------------------------------------------\r\nFALSE\r\nSQL><\/pre>\n <\/p>\n
SH.CUSTOMERS_OBE \ud14c\uc774\ube14\uc758 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud55c\ub2e4.<\/p>\n
gather_table_stats.sql<\/p>\n
execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE');<\/pre>\nSQL> @gather_table_stats.sql\r\nSQL> execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE');\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\n <\/p>\n
\ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud588\uc74c\uc5d0\ub3c4 PUBLISH \ub418\uc9c0 \uc54a\uc558\uc74c\uc744 \ud655\uc778\ud55c\ub2e4.<\/p>\n
<\/p>\n
SQL> @show_public_stats CUSTOMERS_OBE\r\nSQL>\r\nSQL> set echo off\r\nold 3: where table_name = '&1'\r\nnew 3: where table_name = 'CUSTOMERS_OBE'\r\nTABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN\r\n------------------------------ -------------- ---------- ---------- -----------\r\nCUSTOMERS_OBE\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nINDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS\r\n------------------------------ -------------- ---------- -----------\r\nDISTINCT_KEYS\r\n-------------\r\nOBE_CUST_CRED_LMT_IND\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nTABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN\r\n------------------------------ -------------- ---------- ---------- -----------\r\nCUSTOMERS_OBE\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nINDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS\r\n------------------------------ -------------- ---------- -----------\r\nDISTINCT_KEYS\r\n-------------\r\nOBE_CUST_CRED_LMT_IND\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nCOLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCOUNTRY_ID\r\nCUST_CITY\r\nCUST_CREDIT_LIMIT\r\nCUST_EMAIL\r\nCUST_FIRST_NAME\r\nCUST_GENDER\r\nCUST_ID\r\nCUST_INCOME_LEVEL\r\nCUST_LAST_NAME\r\nCUST_MAIN_PHONE_NUMBER\r\nCUST_MARITAL_STATUS\r\nCOLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCUST_POSTAL_CODE\r\nCUST_STATE_PROVINCE\r\nCUST_STREET_ADDRESS\r\nCUST_YEAR_OF_BIRTH\r\n15 rows selected.\r\nSQL><\/pre>\n <\/p>\n
User_tab_pending_stats \ub97c \ud1b5\ud574\uc11c PUBLISH \ub418\uae30 \uc774\uc804 \uc218\uc9d1\ub41c \ud1b5\uacc4\uc815\ubcf4\ub97c \ud655\uc778\ud55c\ub2e4.<\/p>\n
<\/p>\n
SQL> @show_pending_stats CUSTOMERS_OBE\r\nSQL>\r\nSQL> set echo off\r\nold 3: where table_name = '&1' and partition_name is null\r\nnew 3: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nTABLE_NAME analyze time NUM_ROWS BLOCKS AVG_ROW_LEN\r\n------------------------------ -------------- ---------- ---------- -----------\r\nCUSTOMERS_OBE 09\/11 17:59:05 630 12 137.646032\r\nold 4: where table_name = '&1' and partition_name is null\r\nnew 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nINDEX_NAME analyze time NUM_ROWS LEAF_BLOCKS\r\n------------------------------ -------------- ---------- -----------\r\nDISTINCT_KEYS\r\n-------------\r\nOBE_CUST_CRED_LMT_IND 09\/11 17:59:08 630 2\r\n8\r\nold 4: where table_name = '&1' and partition_name is null\r\nnew 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nCOLUMN_NAME analyze time NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCOUNTRY_ID 09\/11 17:59:05 19 0 .052631579\r\nCUST_CITY 09\/11 17:59:05 300 0 .003333333\r\nCUST_CREDIT_LIMIT 09\/11 17:59:05 8 0 .125\r\nCUST_EMAIL 09\/11 17:59:05 400 0 .0025\r\nCUST_FIRST_NAME 09\/11 17:59:05 450 0 .002222222\r\nCUST_GENDER 09\/11 17:59:05 2 0 .5\r\nCUST_ID 09\/11 17:59:05 630 0 .001587302\r\nCUST_INCOME_LEVEL 09\/11 17:59:05 12 0 .083333333\r\nCUST_LAST_NAME 09\/11 17:59:05 400 0 .0025\r\nCUST_MAIN_PHONE_NUMBER 09\/11 17:59:05 630 0 .001587302\r\nCUST_MARITAL_STATUS 09\/11 17:59:05 2 234 .5\r\nCOLUMN_NAME analyze time NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCUST_POSTAL_CODE 09\/11 17:59:05 301 0 .003322259\r\nCUST_STATE_PROVINCE 09\/11 17:59:05 120 0 .008333333\r\nCUST_STREET_ADDRESS 09\/11 17:59:05 630 0 .001587302\r\nCUST_YEAR_OF_BIRTH 09\/11 17:59:05 66 0 .015151515\r\n15 rows selected.\r\nSQL><\/pre>\n <\/p>\n
Testing Pending Statistics<\/strong><\/span><\/p>\n <\/p>\n
\ud604\uc7ac CUSTOMERS_OBE \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \ubaa8\ub4e0 \ud1b5\uacc4\uc815\ubcf4\ub294 pending \uc0c1\ud0dc \uc774\ub2e4.<\/p>\n
Pending statistics\ub97c export\ud558\uc5ec test \uc7a5\ube44\uc5d0\uc11c \ud14c\uc2a4\ud2b8\ub97c \uc218\ud589\ud560 \uc218 \uc788\ub2e4. \uadf8\ub9ac\uace0 \ub9cc\uc57d \ud14c\uc2a4\ud2b8\ud55c pending statistics\uac00 \ub9cc\uc871\uc2a4\ub7fd\uc9c0 \uc54a\ub2e4\uba74(\uc131\ub2a5\ud5a5\uc0c1\uc774 \uc5c6\ub2e4\uba74), pending statistics\ub97c \uc9c0\uc6cc\ubc84\ub9b4 \uc218 \uc788\ub2e4.<\/p>\n
Sessin level\uc5d0\uc11c pending statistics\ub97c \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uae30 \uc704\ud574\uc11c \ub2e4\uc74c\uc758 \uba85\ub839\uc744 \uc218\ud589\ud55c\ub2e4.<\/p>\n
<\/p>\n
set_pending_stats_off.sql<\/p>\n
alter session set optimizer_use_pending_statistics = false;\r\nalter session set optimizer_dynamic_sampling = 0;<\/pre>\nSQL> @set_pending_stats_off\r\nSQL> alter session set optimizer_use_pending_statistics = false;\r\nSession altered.\r\nSQL> alter session set optimizer_dynamic_sampling = 0;\r\nSession altered.\r\nSQL><\/pre>\n <\/p>\n
Query\uc5d0 \ub300\ud55c SQL Plan\uc744 \uc5bb\ub294\ub2e4.<\/p>\n
get_execplan.sql<\/p>\n
set linesize 140\r\nset pagesize 40\r\nexplain plan for\r\nselect * from customers_obe where CUST_CREDIT_LIMIT=1500;\r\nselect plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));<\/pre>\nSQL> @get_execplan\r\nSQL> set linesize 140\r\nSQL> set pagesize 40\r\nSQL> explain plan for\r\n2 select * from customers_obe where CUST_CREDIT_LIMIT=1500;\r\nExplained.\r\nSQL>\r\nSQL> select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));\r\nPLAN\r\n-------------------------------------------------------------------------------------------------------\r\nPlan hash value: 2572487643\r\n-----------------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n-----------------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 10 | 2080 | 4 (0)| 00:00:01 |\r\n| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS_OBE | 10 | 2080 | 4 (0)| 00:00:01 |\r\n|* 2 | INDEX RANGE SCAN | OBE_CUST_CRED_LMT_IND | 4 | | 1 (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n2 - access(\"CUST_CREDIT_LIMIT\"=1500)\r\n14 rows selected.\r\nSQL>\r\n<\/pre>\n <\/p>\n
optimizer_use_pending_statistics = false \ub85c \uc124\uc815\ud558\uc5ec pending statistics \ub97c \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uc744 \uacbd\uc6b0, SQL Plan\uc744 \ud655\uc778\ud574 \ubcf4\uba74 Index scan \ud6c4 \uac01\uac01\uc758 Row \ub9c8\ub2e4 CUST_CREDIT_LIMIT=1500\u00a0 \uc870\uac74\uc774 \ucda9\uc871\ub420 \ub54c \uae4c\uc9c0 \ud14c\uc774\ube14\uc5d0 a single-row access<\/strong> \ud55c\ub2e4. <\/span><\/p>\nCust_credit_limit\uc758 distinct value\ub294 8\ub85c\uc11c \ud558\ub098\uc758 cust_credit_limit\ub294 \uc804\uccb4 row\uc758 \uc57d 12.5%\ub97c \ucc28\uc9c0\ud558\uace0 \uc788\uc5b4 Cust_credit_limit=1500\uc778 row\uc218\uac00 \uc810\uc810 \uc99d\uac00\ud55c\ub2e4\uba74 \ud574\ub2f9 SQL\uc758 \uc18d\ub3c4\ub294 \uc810\uc810 \ub2a6\uc5b4\uc9c8 \uac83\uc774\ubbc0\ub85c index range scan\uc740 \ucd5c\uc801\uc758 Plan\uc740 \uc544\ub2c8\ub2e4. <\/strong><\/span><\/p>\n <\/p>\n
\uc774\ubc88\uc5d0\ub294 pending statistics\ub97c \uc774\uc6a9\ud588\uc744 \ub54c, \ub354 \uc88b\ub294 Plan\uc774 \uc0dd\uc131\ub418\ub294\uc9c0 \ud655\uc778\ud574 \ubcf4\ub3c4\ub85d \ud55c\ub2e4. <\/strong><\/p>\n <\/p>\n
\uc6b0\uc120, Sessin level\uc5d0\uc11c pending statistics\uac00 \uc0ac\uc6a9\ub418\ub3c4\ub85d \uc124\uc815\uac12\uc740 ture\ub85c \ubcc0\uacbd\ud55c\ub2e4.<\/p>\n
set_optimizer_pending_stats_true.sql<\/p>\n
alter session set optimizer_use_pending_statistics = true;<\/pre>\nSQL> @set_optimizer_pending_stats_true\r\nSQL> alter session set optimizer_use_pending_statistics = true;\r\nSession altered.\r\nSQL><\/pre>\n <\/p>\n
SQL Plan\uc744 \uc870\ud68c\ud55c\ub2e4.<\/p>\n
set linesize 140\r\nset pagesize 40\r\nexplain plan for\r\nselect * from customers_obe where CUST_CREDIT_LIMIT=1500;\r\nselect plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));<\/pre>\nSQL> @get_execplan\r\nSQL> set linesize 140\r\nSQL> set pagesize 40\r\nSQL> explain plan for\r\n2 select * from customers_obe where CUST_CREDIT_LIMIT=1500;\r\nExplained.\r\nSQL>\r\nSQL> select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));\r\nPLAN\r\n--------------------------------------------------------------------------------------------------------\r\n------------------------------------\r\nPlan hash value: 520139036\r\n-----------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n-----------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 79 | 10823 | 5 (0)| 00:00:01 |\r\n|* 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 79 | 10823 | 5 (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n1 - filter(\"CUST_CREDIT_LIMIT\"=1500)\r\n13 rows selected.\r\nSQL><\/pre>\n <\/p>\n
\uc5ec\uae30\uc11c\ub294 Full TABLE SCAN \uc744 \ud558\uc9c0\ub9cc \uc774\uc804\uc758 Index Range Scan \ubcf4\ub2e4\ub294\ub354 \ub098\uc740 Plan\uc744 \uc0dd\uc131\ud574 \uc8fc\ubbc0\ub85c, pending statistics\uac00 \uc2e4 \uc6b4\uc601 DB\uc5d0 \uc801\uc6a9\ud560 \uc801\uc808\ud55c \ud1b5\uacc4\uc815\ubcf4\uc774\ub2e4.<\/p>\n
Publishing Pending Statistics<\/strong><\/span><\/p>\n\ub9cc\uc77c Pending \ud1b5\uacc4\uc815\ubcf4\uac00 \ucda9\ubd84\ud788 \uc785\uc99d \ub418\uc5c8\ub2e4\uba74, \uc544\ub798\uc640 \uac19\uc774 Publish \ud55c\ub2e4.<\/p>\n
<\/p>\n
publish_pending_stats.sql<\/p>\n
exec dbms_stats.publish_pending_stats(null, null) ;\r\n<\/pre>\n <\/p>\n
Pending \ud1b5\uacc4\uc815\ubcf4\ub97c publishing \ud55c\ub2e4.<\/p>\n
SQL> @publish_pending_stats\r\nSQL> exec dbms_stats.publish_pending_stats(null, null)\r\nPL\/SQL procedure successfully completed.\r\nSQL> @show_public_stats CUSTOMERS_OBE\r\nSQL>\r\nSQL> set echo off\r\nold 3: where table_name = '&1'\r\nnew 3: where table_name = 'CUSTOMERS_OBE'\r\nTABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN\r\n------------------------------ -------------- ---------- ---------- -----------\r\nCUSTOMERS_OBE 09\/11 17:59:05 630 12 137\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nINDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS\r\n------------------------------ -------------- ---------- ----------- -------------\r\nOBE_CUST_CRED_LMT_IND 09\/11 17:59:08 630 2 8\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nCOLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCOUNTRY_ID 09\/11 17:59:05 19 0 .052631579\r\nCUST_CITY 09\/11 17:59:05 300 0 .003333333\r\nCUST_CREDIT_LIMIT 09\/11 17:59:05 8 0 .125\r\nCUST_EMAIL 09\/11 17:59:05 400 0 .0025\r\nCUST_FIRST_NAME 09\/11 17:59:05 450 0 .002222222\r\nCUST_GENDER 09\/11 17:59:05 2 0 .5\r\nCUST_ID 09\/11 17:59:05 630 0 .001587302\r\nCUST_INCOME_LEVEL 09\/11 17:59:05 12 0 .083333333\r\nCUST_LAST_NAME 09\/11 17:59:05 400 0 .0025\r\nCUST_MAIN_PHONE_NUMBER 09\/11 17:59:05 630 0 .001587302\r\nCUST_MARITAL_STATUS 09\/11 17:59:05 2 234 .5\r\nCUST_POSTAL_CODE 09\/11 17:59:05 301 0 .003322259\r\nCUST_STATE_PROVINCE 09\/11 17:59:05 120 0 .008333333\r\nCUST_STREET_ADDRESS 09\/11 17:59:05 630 0 .001587302\r\nCUST_YEAR_OF_BIRTH 09\/11 17:59:05 66 0 .015151515\r\n15 rows selected.\r\nSQL>\r\n<\/pre>\n <\/p>\n
Pending \ud1b5\uacc4\uc815\ubcf4\ub97c \ub2e4\uc2dc \ud655\uc778\ud574 \ubcf4\ub2c8 publishing \ub418\uc5b4 \ub0a8\uc544 \uc788\uc9c0 \uc54a\ub294\ub2e4.<\/p>\n
SQL> @show_pending_stats CUSTOMERS_OBE\r\nSQL>\r\nSQL> set echo off\r\nold 3: where table_name = '&1' and partition_name is null\r\nnew 3: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nno rows selected\r\nold 4: where table_name = '&1' and partition_name is null\r\nnew 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nno rows selected\r\nold 4: where table_name = '&1' and partition_name is null\r\nnew 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nno rows selected\r\nSQL><\/pre>\n <\/p>\n
Resetting Statistics and Preferences<\/strong><\/span><\/p>\nSH.customer_obe\uc5d0 \uc124\uc815\ub41c publish perference\ub97c default \uac12(false)\ub85c reset \ud55c\ub2e4.<\/p>\n
dbms_stats.delete_table_stats \ub97c \uc0ac\uc6a9\ud574\uc11c \uae30\uc874\uc758 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0ad\uc81c\ud558\uace0 publish preference\ub97c false\ub85c \ubcc0\uacbd\ud55c\ub2e4.<\/p>\n
delete_table_stats.sql<\/p>\n
exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');<\/pre>\n <\/p>\n
set_global_publish.sql<\/p>\n
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;<\/pre>\n <\/p>\n
SQL> @delete_table_stats\r\nSQL> exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');\r\nPL\/SQL procedure successfully completed.\r\nSQL>\r\nSQL> @set_global_publish\r\nSQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;\r\nPUBLISH\r\n--------------------------------------------------------------------------------------------------------------------------------------------\r\nFALSE\r\nSQL><\/pre>\n <\/p>\n
\nChange global and table statistic preferences<\/strong><\/span>
\nGather pending statistics<\/strong><\/span>
\nTest pending statistics<\/strong><\/span>
\nPublish pending statistics<\/strong><\/span>
\nReset statistics and preferences<\/strong><\/span><\/p>\n
\n <\/p>\n
Changing Global and Table Statistic Preferences<\/strong><\/span><\/p>\n\uc608\uc81c \uc2e4\uc2b5\uc744 \uc704\ud55c \ud14c\uc774\ube14 \uc0dd\uc131 \ubc0f Data import<\/p>\n
[oracle@obe11g gathstats]$ imp sh\/sh file=customers_obe.dmp log=log full=y\r\nImport: Release 11.1.0.5.0 - Beta on Wed Sep 12 09:59:15 2007\r\nCopyright (c) 1982, 2007, Oracle. All rights reserved.\r\nConnected to: Oracle Database 11g Enterprise Edition Release 11.1.0.5.0 - Beta\r\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options\r\nExport file created by EXPORT:V11.01.00 via conventional path\r\nimport done in US7ASCII character set and AL16UTF16 NCHAR character set\r\nimport server uses AL32UTF8 character set (possible charset conversion)\r\n. importing SH's objects into SH\r\n. importing SH's objects into SH\r\n. . importing table \"CUSTOMERS_OBE\" 630 rows imported\r\nImport terminated successfully without warnings.\r\n[oracle@obe11g gathstats]$<\/pre>\nSQL> desc CUSTOMERS_OBE\r\nName Null? Type\r\n----------------------------------------- -------- ----------------------------\r\nCUST_ID NUMBER\r\nCUST_FIRST_NAME NOT NULL VARCHAR2(20)\r\nCUST_LAST_NAME NOT NULL VARCHAR2(40)\r\nCUST_GENDER CHAR(1)\r\nCUST_YEAR_OF_BIRTH NUMBER(4)\r\nCUST_MARITAL_STATUS VARCHAR2(20)\r\nCUST_STREET_ADDRESS NOT NULL VARCHAR2(40)\r\nCUST_POSTAL_CODE NOT NULL VARCHAR2(10)\r\nCUST_CITY NOT NULL VARCHAR2(30)\r\nCUST_STATE_PROVINCE VARCHAR2(40)\r\nCOUNTRY_ID NOT NULL CHAR(2)\r\nCUST_MAIN_PHONE_NUMBER VARCHAR2(25)\r\nCUST_INCOME_LEVEL VARCHAR2(30)\r\nCUST_CREDIT_LIMIT NUMBER\r\nCUST_EMAIL VARCHAR2(30)\r\nSQL><\/pre>\nSQL> select count(*) from CUSTOMERS_OBE;\r\nCOUNT(*)\r\n----------\r\n630<\/pre>\n <\/p>\n
\ud1b5\uacc4\uc815\ubcf4 \uc218\uc9d1\uc744 \uc704\ud55c default preferences \ub610\ub294 parameter setting \uc744 \ubcc0\uacbd \ud55c\ub2e4.<\/p>\n
\uadf8 \uc911 \ud558\ub098\ub294 STALE_PERCENT \uc640 \uad00\ub828\uc774 \uc788\uc73c\uba70 \ud14c\uc774\ube14 \uc548\uc5d0 \ub808\ucf54\ub4dc\ub4e4\uc758 \ube44\uc728 \uc744 \uc758\ubbf8\ud558\ub294 STALE_PERCENT \uc740 \uc0c8\ub85c \uc218\uc9d1\ud574\uc57c \ud55c\ub2e4\uace0 \ud310\ub2e8\ub418\ub294 \ud14c\uc774\ube14\uc5d0 \ub300\ud574\uc11c \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud558\uae30 \uc804\uc5d0 \ubcc0\uacbd\ud574\uc57c \ud55c\ub2e4.<\/p>\n
<\/p>\n
check_sales_pref.sql<\/p>\n
connect sh\/sh\r\nselect dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;<\/pre>\n <\/p>\n
\ud604\uc7ac ‘STALE_PERCENT \uc5d0 \ub300\ud55c Default \uac12 10 \uc774 \uc124\uc815\ub41c \uac83\uc744 \ud655\uc778 \ud560 \uc218 \uc788\ub2e4.<\/p>\n
SQL> @check_sales_pref.sql\r\nSQL> connect sh\/sh\r\nConnected.\r\nSQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent\r\n2 from dual;\r\nSTALE_PERCENT\r\n--------------------------------------------------------------------------------\r\n10\r\nSQL><\/pre>\nGlobal STALE_PERCENT \uac12\uc744 13 \uc73c\ub85c \ubcc0\uacbd\ud55c\ub2e4.<\/p>\n
change_global_pref.sql<\/p>\n
connect \/ as sysdba\r\nexecute dbms_stats.set_global_prefs('STALE_PERCENT', '13');<\/pre>\nSQL> @change_global_pref.sql\r\nSQL> connect \/ as sysdba\r\nConnected.\r\nSQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', '13');\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\nconnect sh\/sh\r\nselect dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;<\/pre>\n <\/p>\n
Global STALE_PERCENT \uac12 \ubcc0\uacbd\uc73c\ub85c STALE_PERCENT \uac12\uc774 13 \uc73c\ub85c \ubcc0\uacbd \ub41c \uac83\uc744 \ud655\uc778 \ud560\uc218 \uc788\ub2e4.<\/p>\n
SQL> @check_sales_pref.sql\r\nSQL> connect sh\/sh\r\nConnected.\r\nSQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent\r\n2 from dual;\r\nSTALE_PERCENT\r\n--------------------------------------------------------------------------------\r\n13\r\nSQL><\/pre>\nSingle \ud14c\uc774\ube14 \ubcc0\uacbd\uc744 \uc6d0\ud55c\ub2e4\uba74 \uc544\ub798\uc640 \uac19\uc774 \ubcc0\uacbd\uc774 \uac00\ub2a5\ud558\ub2e4.<\/p>\n
change_table_pref.sql<\/p>\n
execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65');<\/pre>\nSQL> @change_table_pref.sql\r\nSQL> execute dbms_stats.set_table_prefs('SH', 'SALES', 'STALE_PERCENT', '65');\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\nconnect sh\/sh\r\nselect dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;<\/pre>\n <\/p>\n
Sh \uc720\uc800 \uc758 SALES \ud14c\uc774\ube14\uc5d0 \ub300\ud574 STALE_PERCENT \uac12\uc774 65 \ub85c \ubcc0\uacbd\ub41c \uac83\uc744 \ud655\uc778 \ud560 \uc218 \uc788\ub2e4.<\/p>\n
SQL> @check_sales_pref.sql\r\nSQL> connect sh\/sh\r\nConnected.\r\nSQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent\r\n2 from dual;\r\nSTALE_PERCENT\r\n--------------------------------------------------------------------------------\r\n65\r\nSQL><\/pre>\nselect dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent from dual;<\/pre>\n <\/p>\n
\uadf8\ub7ec\ub098 \ub2e4\ub978 \ud14c\uc774\ube14\uc744 \ud655\uc778\ud574 \ubcf4\uba74 Global STALE_PERCENT \uac12\uc744 13 \uc758 \uac12\uc774 \uadf8\ub300\ub85c \uc801\uc6a9\ub418\uc5b4 \uc788\ub294 \uac83\uc774 \ud655\uc778\ub41c\ub2e4.<\/p>\n
SQL> @check_products_pref.sql\r\nSQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'PRODUCTS') stale_percent\r\n2 from dual;\r\nSTALE_PERCENT\r\n--------------------------------------------------------------------------------\r\n13\r\nSQL><\/pre>\n <\/p>\n
reset_table_prefs.sql<\/p>\n
execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT');<\/pre>\n <\/p>\n
dbms_stats.delete_table_prefs \uc744 \uc0ac\uc6a9\ud574\uc11c \uc0ad\uc81c\ud558\uba74 Global STALE_PERCENT \uac12\uc774 13\uc73c\ub85c \uc801\uc6a9\ub41c\ub2e4.<\/p>\n
SQL> @reset_table_prefs\r\nSQL> execute dbms_stats.delete_table_prefs('SH', 'SALES', 'STALE_PERCENT');\r\nPL\/SQL procedure successfully completed.\r\nSQL>\r\nconnect sh\/sh\r\nselect dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent\r\nfrom dual;\r\nSQL> @check_sales_pref.sql\r\nSQL> connect sh\/sh\r\nConnected.\r\nSQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent\r\n2 from dual;\r\nSTALE_PERCENT\r\n--------------------------------------------------------------------------------\r\n13\r\nSQL><\/pre>\n <\/p>\n
reset_global_prefs.sql<\/p>\n
connect \/ as sysdba\r\nexecute dbms_stats.set_global_prefs('STALE_PERCENT', null);<\/pre>\n <\/p>\n
execute dbms_stats.set_global_prefs \uac12\uc774 null \uc774\uba74 Default \uac12 10 \uc774 \uc801\uc6a9\ub41c\ub2e4.<\/p>\n
SQL> @reset_global_prefs.sql\r\nSQL> connect \/ as sysdba\r\nConnected.\r\nSQL> execute dbms_stats.set_global_prefs('STALE_PERCENT', null);\r\nPL\/SQL procedure successfully completed.\r\nSQL> connect sh\/sh\r\nselect dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent from dual;\r\nSQL> @check_sales_pref.sql\r\nSQL> connect sh\/sh\r\nConnected.\r\nSQL> select dbms_stats.get_prefs('STALE_PERCENT', 'SH', 'SALES') stale_percent\r\n2 from dual;\r\nSTALE_PERCENT\r\n--------------------------------------------------------------------------------\r\n10\r\nSQL><\/pre>\n <\/p>\n
<\/p>\n
Gathering Pending Statistics<\/strong><\/span><\/p>\n\ub2f9\uc2e0\uc740 \ud14c\uc774\ube14\uc758 public \uacfc pending \ud1b5\uacc4\uc815\ubcf4\ub97c \ud655\uc778 \ud560 \uc218 \uc788\uace0, publishing \uc5c6\uc774 \uc5b4\ub5bb\uac8c \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1 \ud560 \uc218 \uc788\ub294\uc9c0\ub97c \ud655\uc778 \ud558\uac8c \ub420 \uac83\uc774\ub2e4.<\/p>\n
\uc2e4\uc2b5\uc5d0 \uc784\ud558\uae30\uc804 ‘CUSTOMERS_OBE \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \ub0a0\uc9dc \ud615\uc2dd\uc744 \ub9de\ucd9c \uac83\uc785\ub2c8\ub2e4. \uadf8\ub9ac\uace0 \uc774\uc804\uc758 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0ad\uc81c \ud569\ub2c8\ub2e4.<\/p>\n
<\/p>\n
reset_table_stats.sql<\/p>\n
connect sh\/sh\r\nalter session set nls_date_format='mm\/dd hh24:mi:ss';\r\n-- delete statistics\r\nexec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');<\/pre>\nSQL> @reset_table_stats\r\nSQL> connect sh\/sh\r\nConnected.\r\nSQL> alter session set nls_date_format='mm\/dd hh24:mi:ss';\r\nSession altered.\r\nSQL> -- delete statistics\r\nSQL> exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\n <\/p>\n
show_public_stats.sql<\/p>\n
set echo off\r\n-- tables\r\nselect table_name, last_analyzed \"analyze time\", num_rows, blocks, avg_row_len\r\nfrom user_tab_pending_stats\r\nwhere table_name = '&1' and partition_name is null ;\r\n\r\n-- indexes\r\nselect index_name, last_analyzed \"analyze time\", num_rows, leaf_blocks, distinct_keys\r\nfrom user_ind_pending_stats\r\nwhere table_name = '&1' and partition_name is null\r\norder by index_name ;\r\n\r\n-- columns\r\nselect column_name, last_analyzed \"analyze time\", num_distinct, num_nulls, density\r\nfrom user_col_pending_stats\r\nwhere table_name = '&1' and partition_name is null\r\norder by column_name ;\r\nset echo on<\/pre>\n <\/p>\n
CUSTOMERS_OBE \uc5d0 \ub300\ud55c public_stats \ud655\uc778 (tables, indexes, columns)<\/p>\n
<\/p>\n
SQL> @show_public_stats CUSTOMERS_OBE\r\nSQL>\r\nSQL> set echo off\r\nold 3: where table_name = '&1'\r\nnew 3: where table_name = 'CUSTOMERS_OBE'\r\nTABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN\r\n------------------------------ -------------- ---------- ---------- -----------\r\nCUSTOMERS_OBE\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nINDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS\r\n------------------------------ -------------- ---------- -----------\r\nDISTINCT_KEYS\r\n-------------\r\nOBE_CUST_CRED_LMT_IND\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nCOLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCOUNTRY_ID\r\nCUST_CITY\r\nCUST_CREDIT_LIMIT\r\nCUST_EMAIL\r\nCUST_FIRST_NAME\r\nCUST_GENDER\r\nCUST_ID\r\nCUST_INCOME_LEVEL\r\nCUST_LAST_NAME\r\nCUST_MAIN_PHONE_NUMBER\r\nCUST_MARITAL_STATUS\r\nCOLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCUST_POSTAL_CODE\r\nCUST_STATE_PROVINCE\r\nCUST_STREET_ADDRESS\r\nCUST_YEAR_OF_BIRTH\r\n\r\n15 rows selected.\r\nSQL><\/pre>\n <\/p>\n
show_pending_stats.sql<\/p>\n
set echo off\r\n-- tables\r\nselect table_name, last_analyzed \"analyze time\", num_rows, blocks, avg_row_len\r\nfrom user_tab_pending_stats\r\nwhere table_name = '&1' and partition_name is null;\r\n-- indexes\r\nselect index_name, last_analyzed \"analyze time\", num_rows,leaf_blocks, distinct_keys\r\nfrom user_ind_pending_stats\r\nwhere table_name = '&1' and partition_name is null\r\norder by index_name;\r\n\r\n-- columns\r\nselect column_name, last_analyzed \"analyze time\", num_distinct, num_nulls, density\r\nfrom user_col_pending_stats\r\nwhere table_name = '&1' and partition_name is null\r\norder by column_name;\r\nset echo on<\/pre>\n <\/p>\n
CUSTOMERS_OBE \uc5d0 \ub300\ud55c pending_stats \ud655\uc778 (tables, indexes, columns)<\/p>\n
<\/p>\n
SQL> @show_pending_stats CUSTOMERS_OBE\r\nSQL>\r\nSQL> set echo off\r\nold 3: where table_name = '&1' and partition_name is null\r\nnew 3: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nno rows selected\r\nold 4: where table_name = '&1' and partition_name is null\r\nnew 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nno rows selected\r\nold 4: where table_name = '&1' and partition_name is null\r\nnew 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nno rows selected\r\nSQL><\/pre>\n <\/p>\n
\uae30\ubcf8\uc801\uc73c\ub85c Oracle Database 11g \uc5d0\uc11c\ub3c4 \uc218\uc9d1 \ub418\uc790 \ub9c8\ub2e4 \ud1b5\uacc4 \uc815\ubcf4\uac00 Publish \ub418\ub294\uac83\uc740 \ub3d9\uc77c\ud558\ub2e4.<\/p>\n
Default PUBLISH \uac12\uc774 TRUE \ub85c \uc124\uc815 \ub418\uc5b4 \uc788\uc74c\uc744 \ud655\uc778 \ud560 \uc218 \uc788\ub2e4.<\/p>\n
<\/p>\n
select dbms_stats.get_prefs('PUBLISH') publish from dual;<\/pre>\nSQL> @check_publish_prefs\r\nSQL> select dbms_stats.get_prefs('PUBLISH') publish from dual;\r\nPUBLISH\r\n--------------------------------------------------------------------------------\r\nTRUE\r\nSQL><\/pre>\n <\/p>\n
\ub9c8\ucc2c\uac00\uc9c0\ub85c ‘CUSTOMERS_OBE’\u00a0 \ub610\ud55c\u00a0 PUBLISH \uac12\uc774 Default \uac12 TRUE \uac00 \uc801\uc6a9\ub418\uc5c8\uc74c\uc744 \ud655\uc778 \ud588\ub2e4.<\/p>\n
<\/p>\n
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;<\/pre>\nSQL> @check_table_publish_prefs\r\nSQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;\r\nPUBLISH\r\n--------------------------------------------------------------------------------\r\nTRUE\r\nSQL><\/pre>\n <\/p>\n
set_table_publish_prefs_false.sql<\/p>\n
exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false');<\/pre>\n <\/p>\n
‘CUSTOMERS_OBE’ \uc758 PUBLISH \uac12\uc744 False \ub85c \ubcc0\uacbd \ud588\uc73c\uba70, \uc774\ub294 \ud1b5\uacc4\uc815\ubcf4 \uc218\uc9d1\ud6c4 \ubc14\ub85c \uc790\ub3d9\uc73c\ub85c PUBLISH \ud558\ub294 \uac83\uc744 \ud558\uc9c0 \uc54a\uaca0\ub2e4\ub294 \uac83\uc744 \uc758\ubbf8\ud55c\ub2e4.<\/p>\n
<\/p>\n
SQL> @set_table_publish_prefs_false\r\nSQL> exec dbms_stats.set_table_prefs('SH', 'CUSTOMERS_OBE', 'PUBLISH', 'false');\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\nselect dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;<\/pre>\nSQL> @check_table_publish_prefs\r\nSQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;\r\nPUBLISH\r\n--------------------------------------------------------------------------------\r\nFALSE\r\nSQL><\/pre>\n <\/p>\n
SH \uc2a4\ud0a4\ub9c8\uc758 ‘CUSTOMERS_OBE’ \ud14c\uc774\ube14 \ud1b5\uacc4\uc815\ubcf4\uc218\uc9d1<\/p>\n
gather_table_stats.sql<\/p>\n
execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE');<\/pre>\nSQL> @gather_table_stats.sql\r\nSQL> execute dbms_stats.gather_table_stats('SH', 'CUSTOMERS_OBE');\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\n <\/p>\n
\ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1 \ud588\uc74c \uc5d0\ub3c4 PUBLISH \ub418\uc9c0 \uc54a\uc558\uc74c\uc744 \ud655\uc778 \ud560 \uc218 \uc788\ub2e4.<\/p>\n
show_public_stats.sql<\/p>\n
set echo off\r\n-- tables\r\nselect table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len from user_tables where table_name = '&1';\r\n-- indexes\r\nselect index_name, last_analyzed ANALYZE_TIME, num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = '&1' order by index_name;\r\n-- columns\r\nselect column_name, last_analyzed ANALYZE_TIME, num_distinct,num_nulls, density from user_tab_columns where table_name = '&1' order by column_name;\r\nset echo on<\/pre>\nSQL> @show_public_stats CUSTOMERS_OBE\r\nSQL>\r\nSQL> set echo off\r\nold 3: where table_name = '&1'\r\nnew 3: where table_name = 'CUSTOMERS_OBE'\r\nTABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN\r\n------------------------------ -------------- ---------- ---------- -----------\r\nCUSTOMERS_OBE\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nINDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS\r\n------------------------------ -------------- ---------- -----------\r\nDISTINCT_KEYS\r\n-------------\r\nOBE_CUST_CRED_LMT_IND\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\n\r\nTABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN\r\n------------------------------ -------------- ---------- ---------- -----------\r\nCUSTOMERS_OBE\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nINDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS\r\n------------------------------ -------------- ---------- -----------\r\nDISTINCT_KEYS\r\n-------------\r\nOBE_CUST_CRED_LMT_IND\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nCOLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCOUNTRY_ID\r\nCUST_CITY\r\nCUST_CREDIT_LIMIT\r\nCUST_EMAIL\r\nCUST_FIRST_NAME\r\nCUST_GENDER\r\nCUST_ID\r\nCUST_INCOME_LEVEL\r\nCUST_LAST_NAME\r\nCUST_MAIN_PHONE_NUMBER\r\nCUST_MARITAL_STATUS\r\nCOLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCUST_POSTAL_CODE\r\nCUST_STATE_PROVINCE\r\nCUST_STREET_ADDRESS\r\nCUST_YEAR_OF_BIRTH\r\n15 rows selected.\r\nSQL><\/pre>\n <\/p>\n
User_tab_pending_stats \ub97c \ud1b5\ud574\uc11c PUBLISH \ub418\uae30 \uc774\uc804 \uc218\uc9d1\ub41c \ud1b5\uacc4\uc815\ubcf4\ub97c \ud655\uc778 \ud560 \uc218 \uc788\ub2e4.<\/p>\n
show_pending_stats.sql<\/p>\n
set echo off\r\n-- tables\r\nselect 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;\r\n-- indexes\r\nselect 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;\r\n-- columns\r\nselect 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;\r\nset echo on<\/pre>\nSQL> @show_pending_stats CUSTOMERS_OBE\r\nSQL>\r\nSQL> set echo off\r\nold 3: where table_name = '&1' and partition_name is null\r\nnew 3: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nTABLE_NAME analyze time NUM_ROWS BLOCKS AVG_ROW_LEN\r\n------------------------------ -------------- ---------- ---------- -----------\r\nCUSTOMERS_OBE 09\/11 17:59:05 630 12 137.646032\r\nold 4: where table_name = '&1' and partition_name is null\r\nnew 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nINDEX_NAME analyze time NUM_ROWS LEAF_BLOCKS\r\n------------------------------ -------------- ---------- -----------\r\nDISTINCT_KEYS\r\n-------------\r\nOBE_CUST_CRED_LMT_IND 09\/11 17:59:08 630 2\r\n8\r\nold 4: where table_name = '&1' and partition_name is null\r\nnew 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nCOLUMN_NAME analyze time NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCOUNTRY_ID 09\/11 17:59:05 19 0 .052631579\r\nCUST_CITY 09\/11 17:59:05 300 0 .003333333\r\nCUST_CREDIT_LIMIT 09\/11 17:59:05 8 0 .125\r\nCUST_EMAIL 09\/11 17:59:05 400 0 .0025\r\nCUST_FIRST_NAME 09\/11 17:59:05 450 0 .002222222\r\nCUST_GENDER 09\/11 17:59:05 2 0 .5\r\nCUST_ID 09\/11 17:59:05 630 0 .001587302\r\nCUST_INCOME_LEVEL 09\/11 17:59:05 12 0 .083333333\r\nCUST_LAST_NAME 09\/11 17:59:05 400 0 .0025\r\nCUST_MAIN_PHONE_NUMBER 09\/11 17:59:05 630 0 .001587302\r\nCUST_MARITAL_STATUS 09\/11 17:59:05 2 234 .5\r\nCOLUMN_NAME analyze time NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCUST_POSTAL_CODE 09\/11 17:59:05 301 0 .003322259\r\nCUST_STATE_PROVINCE 09\/11 17:59:05 120 0 .008333333\r\nCUST_STREET_ADDRESS 09\/11 17:59:05 630 0 .001587302\r\nCUST_YEAR_OF_BIRTH 09\/11 17:59:05 66 0 .015151515\r\n15 rows selected.\r\nSQL><\/pre>\n <\/p>\n
Testing Pending Statistics<\/strong><\/span><\/p>\nCUSTOMERS_OBE \ud14c\uc774\ube14\uc5d0 \ub300\ud55c \ud1b5\uacc4\uc815\ubcf4\ub294 pending \uc0c1\ud0dc \uc774\ub2e4.<\/p>\n
optimizer_use_pending_statistics = false<\/span>
\noptimizer_dynamic_sampling = 0<\/span><\/p>\nset_pending_stats_off.sql<\/p>\n
alter session set optimizer_use_pending_statistics = false;\r\nalter session set optimizer_dynamic_sampling = 0;<\/pre>\nSQL> @set_pending_stats_off\r\nSQL> alter session set optimizer_use_pending_statistics = false;\r\nSession altered.\r\nSQL> alter session set optimizer_dynamic_sampling = 0;\r\nSession altered.\r\nSQL><\/pre>\n <\/p>\n
get_execplan.sql<\/p>\n
set linesize 140\r\nset pagesize 40\r\nexplain plan for\r\nselect * from customers_obe where CUST_CREDIT_LIMIT=1500;\r\nselect plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));<\/pre>\n <\/p>\n
optimizer_use_pending_statistics = false \uc77c\ub54c \ud1b5\uacc4\uc815\ubcf4\ub97c \ud655\uc778\ud574 \ubcf4\uba74 Index SCAN \ud6c4 \uac01\uac01\uc758 Row \ub9c8\ub2e4 CUST_CREDIT_LIMIT=1500 \uc870\uac74\uc774 \ucda9\uc871\ub420 \ub54c \uae4c\uc9c0 \ud14c\uc774\ube14\uc5d0 a single – row access \ub97c \ud574\uc57c \ud558\uae30 \ub54c\ubb38\uc5d0 \uc774\uac83\uc740 \ucd5c\uc801\uc758 Plan \uc744 \ubcf4\uc5ec \uc8fc\ub294 \uac83\uc774 \uc544\ub2c8\ub2e4.<\/p>\n
CUST_CREDIT_LIMIT=1500 \uc5d0 \ub530\ub978 \uacb0\uacfc\uac00 \ud14c\uc774\ube14\uc548\uc5d0 Rows \uc218 20% \ub97c \ub118\uc744 \uacbd\uc6b0 , \ub530\ub77c\uc11c CUSTOMERS_OBE \ud14c\uc774\ube14\uc740 \uacc4\uc18d \uc99d\uac00\ud560 \uac83\uc774\uace0, \uc774\uc640 \uac19\uc740 Plan \uc740 \uc810\uc810 \ub354 \ub290\ub9b0 \uacb0\uacfc\ub97c \uac00\uc838\uc62c \uac83\uc774\ub2e4.<\/p>\n
<\/p>\n
SQL> @get_execplan\r\nSQL> set linesize 140\r\nSQL> set pagesize 40\r\nSQL> explain plan for\r\n2 select * from customers_obe where CUST_CREDIT_LIMIT=1500;\r\nExplained.\r\nSQL>\r\nSQL> select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));\r\nPLAN\r\n-------------------------------------------------------------------------------------------------------\r\nPlan hash value: 2572487643\r\n-----------------------------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n-----------------------------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 10 | 2080 | 4 (0)| 00:00:01 |\r\n| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS_OBE | 10 | 2080 | 4 (0)| 00:00:01 |\r\n|* 2 | INDEX RANGE SCAN | OBE_CUST_CRED_LMT_IND | 4 | | 1 (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n2 - access(\"CUST_CREDIT_LIMIT\"=1500)\r\n14 rows selected.\r\nSQL>\r\n<\/pre>\n <\/p>\n
optimizer_use_pending_statistics = true<\/p>\n
<\/p>\n
alter session set optimizer_use_pending_statistics = true;<\/pre>\nSQL> @set_optimizer_pending_stats_true\r\nSQL> alter session set optimizer_use_pending_statistics = true;\r\nSession altered.\r\nSQL><\/pre>\n <\/p>\n
optimizer \uac00 pending statistic \uc744 \uc0ac\uc6a9\ud55c\ub2e4\uba74 \ub354 \ub098\uc740 \uacb0\uacfc\ub97c \ubcf4\uc77c \uac83\uc774\ub77c\uace0 \uc0dd\uac01\ub41c\ub2e4\uba74,<\/p>\n
\uc544\ub798\uc640 \uac19\uc774 optimizer_use_pending_statistics \uc744 true \ub85c \uc124\uc815\ud574\uc11c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4.<\/p>\n
<\/p>\n
set linesize 140\r\nset pagesize 40\r\nexplain plan for\r\nselect * from customers_obe where CUST_CREDIT_LIMIT=1500;\r\nselect plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));\r\n<\/pre>\n <\/p>\n
\uc5ec\uae30\uc11c\ub294 Full TABLESCAN \uc744 \ud558\uaca0\uc9c0\ub9cc, pending \ud1b5\uacc4\uc815\ubcf4\uac00 \ud6e8\uc52c \ub354 \ub098\uc740 \uacb0\uacfc\ub97c \ubcf4\uc77c \uac83 \uc785\ub2c8\ub2e4.<\/p>\n
<\/p>\n
SQL> @get_execplan\r\nSQL> set linesize 140\r\nSQL> set pagesize 40\r\nSQL> explain plan for\r\n2 select * from customers_obe where CUST_CREDIT_LIMIT=1500;\r\nExplained.\r\nSQL>\r\nSQL> select plan_table_output plan from table(dbms_xplan.display('plan_table',null,'serial'));\r\nPLAN\r\n--------------------------------------------------------------------------------------------------------\r\n------------------------------------\r\nPlan hash value: 520139036\r\n-----------------------------------------------------------------------------------\r\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\r\n-----------------------------------------------------------------------------------\r\n| 0 | SELECT STATEMENT | | 79 | 10823 | 5 (0)| 00:00:01 |\r\n|* 1 | TABLE ACCESS FULL| CUSTOMERS_OBE | 79 | 10823 | 5 (0)| 00:00:01 |\r\n-----------------------------------------------------------------------------------\r\nPredicate Information (identified by operation id):\r\n---------------------------------------------------\r\n1 - filter(\"CUST_CREDIT_LIMIT\"=1500)\r\n13 rows selected.\r\nSQL>\r\n<\/pre>\n <\/p>\n
Publishing Pending Statistics<\/strong><\/span><\/p>\nPending \ud1b5\uacc4\uc815\ubcf4 \uc758 Publishing.<\/p>\n
publish_pending_stats.sql<\/p>\n
exec dbms_stats.publish_pending_stats(null, null)<\/pre>\n\ub9cc\uc77c Pending \ud1b5\uacc4\uc815\ubcf4\uac00 \ucda9\ubd84\ud788 \uc785\uc99d \ub418\uc5c8\ub2e4\uba74, \uc544\ub798\uc640 \uac19\uc774 Publishing \ud560 \uc218 \uc788\ub2e4.<\/p>\n
publish_pending_stats.sql<\/p>\n
SQL> @publish_pending_stats\r\nSQL> exec dbms_stats.publish_pending_stats(null, null)\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\n <\/p>\n
show_public_stats.sql<\/p>\n
set echo off\r\n-- tables\r\nselect table_name, last_analyzed analyze_time, num_rows, blocks, avg_row_len from user_tables where table_name = '&1';\r\n-- indexes\r\nselect index_name, last_analyzed ANALYZE_TIME, num_rows, leaf_blocks, distinct_keys from user_indexes where table_name = '&1' order by index_name;\r\n-- columns\r\nselect column_name, last_analyzed ANALYZE_TIME, num_distinct, num_nulls, density from user_tab_columns where table_name = '&1' order by column_name;\r\nset echo on<\/pre>\n <\/p>\n
Pending \ud1b5\uacc4\uc815\ubcf4\ub97c publishing \ud55c\ub2e4.<\/p>\n
<\/p>\n
SQL> @publish_pending_stats\r\nSQL> exec dbms_stats.publish_pending_stats(null, null)\r\nPL\/SQL procedure successfully completed.\r\nSQL> @show_public_stats CUSTOMERS_OBE\r\nSQL>\r\nSQL> set echo off\r\nold 3: where table_name = '&1'\r\nnew 3: where table_name = 'CUSTOMERS_OBE'\r\nTABLE_NAME ANALYZE_TIME NUM_ROWS BLOCKS AVG_ROW_LEN\r\n------------------------------ -------------- ---------- ---------- -----------\r\nCUSTOMERS_OBE 09\/11 17:59:05 630 12 137\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nINDEX_NAME ANALYZE_TIME NUM_ROWS LEAF_BLOCKS DISTINCT_KEYS\r\n------------------------------ -------------- ---------- ----------- -------------\r\nOBE_CUST_CRED_LMT_IND 09\/11 17:59:08 630 2 8\r\nold 4: where table_name = '&1'\r\nnew 4: where table_name = 'CUSTOMERS_OBE'\r\nCOLUMN_NAME ANALYZE_TIME NUM_DISTINCT NUM_NULLS DENSITY\r\n------------------------------ -------------- ------------ ---------- ----------\r\nCOUNTRY_ID 09\/11 17:59:05 19 0 .052631579\r\nCUST_CITY 09\/11 17:59:05 300 0 .003333333\r\nCUST_CREDIT_LIMIT 09\/11 17:59:05 8 0 .125\r\nCUST_EMAIL 09\/11 17:59:05 400 0 .0025\r\nCUST_FIRST_NAME 09\/11 17:59:05 450 0 .002222222\r\nCUST_GENDER 09\/11 17:59:05 2 0 .5\r\nCUST_ID 09\/11 17:59:05 630 0 .001587302\r\nCUST_INCOME_LEVEL 09\/11 17:59:05 12 0 .083333333\r\nCUST_LAST_NAME 09\/11 17:59:05 400 0 .0025\r\nCUST_MAIN_PHONE_NUMBER 09\/11 17:59:05 630 0 .001587302\r\nCUST_MARITAL_STATUS 09\/11 17:59:05 2 234 .5\r\nCUST_POSTAL_CODE 09\/11 17:59:05 301 0 .003322259\r\nCUST_STATE_PROVINCE 09\/11 17:59:05 120 0 .008333333\r\nCUST_STREET_ADDRESS 09\/11 17:59:05 630 0 .001587302\r\nCUST_YEAR_OF_BIRTH 09\/11 17:59:05 66 0 .015151515\r\n15 rows selected.\r\nSQL><\/pre>\n <\/p>\n
show_pending_stats.sql<\/p>\n
set echo off\r\n-- tables\r\nselect 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;\r\n-- indexes\r\nselect 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;\r\n-- columns\r\nselect 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;\r\nset echo on<\/pre>\n <\/p>\n
Pending \ud1b5\uacc4\uc815\ubcf4\ub97c \ub2e4\uc2dc \ud655\uc778\ud574 \ubcf4\ub2c8 publishing \ub418\uc5b4 \ub0a8\uc544 \uc788\uc9c0 \uc54a\ub294\ub2e4.<\/p>\n
SQL> @show_pending_stats CUSTOMERS_OBE\r\nSQL>\r\nSQL> set echo off\r\nold 3: where table_name = '&1' and partition_name is null\r\nnew 3: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nno rows selected\r\nold 4: where table_name = '&1' and partition_name is null\r\nnew 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nno rows selected\r\nold 4: where table_name = '&1' and partition_name is null\r\nnew 4: where table_name = 'CUSTOMERS_OBE' and partition_name is null\r\nno rows selected\r\nSQL><\/pre>\n <\/p>\n
Resetting Statistics and Preferences<\/span><\/strong><\/p>\ndelete_table_stats.sql<\/p>\n
exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');<\/pre>\n <\/p>\n
dbms_stats.delete_table_stats \ub97c \uc0ac\uc6a9\ud574\uc11c \uae30\uc874\uc758 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc0ad\uc81c \ud569\ub2c8\ub2e4.<\/p>\n
SQL> @delete_table_stats\r\nSQL> exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');\r\nPL\/SQL procedure successfully completed.\r\nSQL><\/pre>\n <\/p>\n
set_global_publish.sql<\/p>\n
select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;<\/pre>\n <\/p>\n
\uae30\uc874\uc758 \ud1b5\uacc4\uc815\ubcf4\ub294 \uc0ad\uc81c\ud558\uace0, \uc790\ub3d9 publish \uac00 \ub418\ub3c4\ub85d \uc0c8\ub86d\uac8c \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud569\ub2c8\ub2e4.<\/p>\n
SQL> @delete_table_stats\r\nSQL> exec dbms_stats.delete_table_stats('SH', 'CUSTOMERS_OBE');\r\nPL\/SQL procedure successfully completed.\r\nSQL>\r\nSQL> @set_global_publish\r\nSQL> select dbms_stats.get_prefs('PUBLISH', 'SH', 'CUSTOMERS_OBE') publish from dual;\r\nPUBLISH\r\n--------------------------------------------------------------------------------------------------------------------------------------------\r\nFALSE\r\nSQL><\/pre>\n <\/p>\n
\uc758<\/span><\/strong><\/span>\uacac<\/strong><\/span><\/p>\n <\/p>\n
Oracle 11g \uc774\uc804\uc5d0\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uad00\ub9ac\uc790\uac00 \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud558\uba74 \ubc14\ub85c Plan \uc5d0 \ubc18\uc601\ub418\uc5b4 \uc608\uae30\uce58 \ubabb\ud55c \uacb0\uacfc\ub97c \ucd08\ub798 \ud560 \uc218 \uac00 \uc788\uc5c8\uc2b5\ub2c8\ub2e4. <\/strong><\/span><\/p>\n\uc774\uc81c Oracle 11g \uc5d0\uc11c\ub294 \ud1b5\uacc4\uc815\ubcf4\uc758 \uc218\uc9d1 ( Gathering ) \uacfc \ubc18\uc601 ( Publish ) \ub97c \ubd84\ub9ac<\/span>\ud560 \uc218 \uc788\uac8c \ub428\uc73c\ub85c\uc368 \uae30\uc874\uc758 \ud1b5\uacc4\uc218\uc9d1\uc758 \ubd88\uc548\ud568\uc744 \uadf9\ubcf5\ud558\uace0 <\/strong><\/span><\/p>\n\uc2e4 \uc6b4\uc601\ud658\uacbd\uc5d0\uc11c \ud14c\uc2a4\ud2b8\ub97c \ud1b5\ud558\uc5ec \uac80\uc99d\ub41c \ud1b5\uacc4\uc815\ubcf4\ub9cc\uc774 Publish \ub418\ub3c4\ub85d \ud558\uc5ec \uc560\ud50c\ub9ac\ucf00\uc774\uc158\uc758 \uc131\ub2a5\uc744 \uadf9\ub300\ud654 \ud560 \uc218 \uc788\uac8c \ub418\uc5c8\uc2b5\ub2c8\ub2e4.<\/strong><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"\uacfc\uac70\uc758 version\uc5d0\uc11c\ub294 \ud1b5\uacc4\uc815\ubcf4\uac00 \uc218\uc9d1\uc774 \ub418\uba74 \ubc14\ub85c SQL Plan\uc774 \ubc14\ub014 \uc218\uac00 \uc788\uc5c8\ub2e4. \uc77c\ubc18\uc801\uc73c\ub85c\ub294 \uac19\uc740 \uc870\uac74\uc5d0\uc11c \uac19\uc740 \ubc29\uc2dd\uc73c\ub85c \ud1b5\uacc4\uc815\ubcf4\ub97c \uc218\uc9d1\ud558\ub294 \uacbd\uc6b0\uc5d4 \uc0c8\ub85c\uc774 \uc0dd\uc131\ub41c SQL Plan\uc740 \uae30\uc874\uc758 \uac83\uacfc \ub3d9\uc77c\ud560 \uac83\uc774\ub2e4. \ud558\uc9c0\ub9cc, \uacbd\uc6b0\uc5d0 \ub530\ub77c\uc11c\ub294(\uc608: \ud1b5\uacc4\uc815\ubcf4\uc758 \uc218\uc9d1\ubc29\uc2dd \ub2e4\ub978 \uacbd\uc6b0, Index\uac00 \ucd94\uac00\ub41c \uacbd\uc6b0 \ub4f1) \ubc18\ub4dc\uc2dc \ub354 \uc88b\uc740 SQL Plan\uc774 \uc0dd\uc131\ub41c\ub2e4\uace0 \ubcf4\uc7a5\ub420 \uc218 \uc5c6\uc5c8\uae30 \ub54c\ubb38\uc5d0 \ud14c\uc2a4\ud2b8 \uc11c\ubc84\uc5d0\uc11c \uba3c\uc800 \uc218\ud589\ud558\uc5ec SQL Plan\uc744 […]<\/p>\n","protected":false},"author":1,"featured_media":2857,"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":[992,162,993,991],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/oracle-business-intelligence.jpg?fit=973%2C615","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3492"}],"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=3492"}],"version-history":[{"count":5,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3492\/revisions"}],"predecessor-version":[{"id":3497,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3492\/revisions\/3497"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2857"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3492"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3492"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3492"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"image\"](\"https:\/\/i0.wp.com\/oracledba.zapto.org\/wordpress\/wp-content\/uploads\/2018\/02\/image-12.png\" "\\"image\\"")
<\/a><\/p>\n