{"id":488,"date":"2015-10-02T08:42:31","date_gmt":"2015-10-02T08:42:31","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=488"},"modified":"2015-10-02T08:42:31","modified_gmt":"2015-10-02T08:42:31","slug":"sysaux-tablespace%ec%97%90%ec%84%9c-table-%ec%9e%ac%ec%83%9d%ec%84%b1-%ed%95%98%eb%8a%94-%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=488","title":{"rendered":"SYSAUX tablespace\uc5d0\uc11c table \uc7ac\uc0dd\uc131 \ud558\ub294 \ubc29\ubc95"},"content":{"rendered":"

AWR \ud1b5\uacc4\uc815\ubcf4 \uc218\uc9d1\uc744 \uc704\ud574 <\/span>statistics_level\uc744 ALL\ub85c \ubcc0\uacbd\ud558\uc600\ub2e4\uac00 \uc774\ud6c4 LEVEL\uc744 \ub0ae\ucdc4\uc74c\uc5d0\ub3c4 <\/span><\/p>\n

SYSAUX tablespace\uc5d0 \uc800\uc7a5\ub41c \uc815\ubcf4\ub4e4\ub85c\uc778\ud574 \ub514\uc2a4\ud06c full \uc0c1\ud0dc\uac00 \ubc1c\uc0dd\ud55c \uc774\uc288\uac00 \uc788\uc5c8\ub2e4. <\/span><\/p>\n

SYSAUX <\/span>tablespace\uc5d0 \uc788\ub294 <\/span>WRI$_ \ub85c \uc2dc\uc791\ud558\ub294 \ud14c\uc774\ube14\ub4e4\uc5d0 AWR \uc218\uc9d1 \ub370\uc774\ud130 \uac12\uc774 \ub0a8\uc544\uc788\uc5b4 \ubc1c\uc0dd\ud55c \uc774\uc288\ub85c <\/span><\/p>\n

\ud574\ub2f9 \ud14c\uc774\ube14\uc744 <\/span>$ORACLE_HOME\/rdbms\/admin \uc5d0 \uc788\ub294 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc218\ud589\ud558\uc5ec \uc0ad\uc81c \ud6c4 <\/span>\uc7ac\uc0dd\uc131\ud560 \uc218 \uc788\ub2e4.<\/span><\/p>\n

<\/p>\n

1. <\/span>AWR \ud1b5\uacc4\uc815\ubcf4 \uc218\uc9d1 \uae30\ub2a5\uc744 \ube44\ud65c\uc131\ud654\ub97c \uc704\ud574\uc11c <\/span>statistics<\/span> level\uc744 Basic\uc73c\ub85c \ubcc0\uacbd\ud55c\ub2e4.<\/span><\/p>\n

SQL> show parameter statistics_level<\/p>\n

<\/p>\n

NAME\t\t\t\t<\/span>     TYPE\t<\/span>                 VALUE<\/p>\n

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

statistics_level<\/span>\t\t<\/span> <\/span>          string<\/span>\t<\/span>                 <\/span>TYPICAL<\/span><\/p>\n


<\/span><\/p>\n

alter system set statistics_level=basic scope=spfile;<\/span><\/p>\n<\/p>\n

System altered.<\/span><\/p>\n

<\/p>\n

2. AWR repository\ub97c \uc0ad\uc81c\ud558\ub294 \ub3d9\uc548 \ud2b8\ub79c\uc7ad\uc158\uc774 \uc77c\uc5b4\ub098\ub294 \uac83\uc744 \ubc29\uc9c0\ud558\uae30 \uc704\ud558\uc5ec DB\ub97c shutdown \ud6c4 restrict \ubaa8\ub4dc\ub85c \uc7ac\uae30\ub3d9\ud55c\ub2e4.<\/span><\/p>\n

SQL> shutdown immediate<\/font><\/p>\n

Database closed.<\/font><\/p>\n

Database dismounted.<\/font><\/p>\n

ORACLE instance shut down.<\/font><\/p>\n


<\/font><\/p>\n

SQL> startup restrict<\/font><\/p>\n

ORACLE instance started.<\/font><\/p>\n


<\/font><\/p>\n

Total System Global Area  542871552 bytes<\/font><\/p>\n

Fixed Size\t\t<\/span>    2254904 bytes<\/font><\/p>\n

Variable Size\t\t<\/span>  218105800 bytes<\/font><\/p>\n

Database Buffers\t<\/span>  314572800 bytes<\/font><\/p>\n

Redo Buffers\t\t<\/span>    7938048 bytes<\/font><\/p>\n

Database mounted.<\/font><\/p>\n

Database opened.<\/font><\/p>\n


<\/font><\/p>\n

SQL> show parameter statistics<\/font><\/p>\n


<\/font><\/p>\n

NAME\t\t\t\t<\/span>     TYPE\t<\/span> VALUE<\/font><\/p>\n

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

optimizer_use_pending_statistics     boolean\t<\/span> FALSE<\/font><\/p>\n

statistics_level\t\t<\/span>               string\t<\/span> BASIC<\/font><\/p>\n

timed_os_statistics\t\t<\/span>        integer\t<\/span> 0<\/font><\/p>\n<\/p>\n

timed_statistics\t\t<\/span>               boolean\t<\/span> FALSE<\/font><\/p>\n


<\/font><\/p>\n

3. AWR \ud14c\uc774\ube14 \uc0ad\uc81c \ubc0f \uc7ac\uc0dd\uc131\uc744 \uc704\ud574 \uc544\ub798\uc758 \uc2a4\ud06c\ub9bd\ud2b8 \ucc28\ub840\ub85c \uc218\ud589\ud55c\ub2e4.<\/font><\/p>\n

SQL> @catnoawr<\/font><\/p>\n

SQL> @catnomwn<\/font><\/p>\n

SQL> @catnomtr<\/font><\/p>\n

SQL> @catnoalr<\/font><\/p>\n

SQL> @catnofus<\/font><\/p>\n


<\/font><\/p>\n

SQL> alter system flush shared_pool;<\/font><\/p>\n

System altered.<\/font><\/p>\n

SQL> <\/font>alter system flush shared_pool;<\/span><\/p>\n

System altered.<\/font><\/p>\n

SQL> <\/font>alter system flush shared_pool;<\/span><\/p>\n

System altered.<\/font><\/p>\n


<\/font><\/p>\n<\/p>\n

11g\uc758 \uacbd\uc6b0 \ucd94\uac00\uc801\uc73c\ub85c \uc544\ub798\uc758 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc218\ud589\ud55c\ub2e4.<\/font><\/p>\n

SQL> @dbmsslrt<\/font><\/p>\n

SQL> @catawrtb<\/font><\/p>\n

SQL> @execsvrm<\/font><\/p>\n


<\/font><\/p>\n

4. \ubaa8\ub450 \uc0ad\uc81c\ub410\ub2e4\uba74 \uc544\ub798\uc758 \ucffc\ub9ac\ub97c \ud1b5\ud574 \uc815\uc0c1\uc801\uc73c\ub85c \uc0ad\uc81c\ub418\uc5c8\ub294\uc9c0 \ud655\uc778\ud55c\ub2e4. <\/font><\/p>\n

   \uc5ec\uc804\ud788 \uc874\uc7ac\ud55c\ub2e4\uba74 \uc218\ub3d9\uc73c\ub85c \uc0ad\uc81c\ud574\uc900\ub2e4.<\/font><\/p>\n

SQL> select table_name from dba_tables where table_name like ‘WRM$_%’ or table_name like ‘WRH$_%’;<\/font> <\/span><\/p>\n


<\/span><\/p>\n

SQL> drop type AWR_OBJECT_INFO_TABLE_TYPE;<\/font><\/p>\n

drop type AWR_OBJECT_INFO_TYPE;<\/font><\/p>\n

drop table WRH$_PLAN_OPERATION_NAME;<\/font><\/p>\n

drop table WRH$_PLAN_OPTION_NAME;<\/font><\/p>\n

drop table WRH$_MV_PARAMETER;<\/font><\/p>\n

drop table WRH$_MV_PARAMETER_BL;<\/font><\/p>\n

drop table WRH$_DYN_REMASTER_STATS;<\/font><\/p>\n

drop table WRH$_PERSISTENT_QMN_CACHE;<\/font><\/p>\n

drop table WRH$_DISPATCHER;<\/font><\/p>\n

drop table WRH$_SHARED_SERVER_SUMMARY;<\/font><\/p>\n

drop table WRM$_WR_USAGE<\/font><\/p>\n

drop table WRM$_SNAPSHOT_DETAILS<\/font><\/p>\n

<\/p>\n

5. \ubaa8\ub450 \uc0ad\uc81c\ub418\uc5c8\ub2e4\uba74 statistics level \uae30\uc874 \uac12\uc73c\ub85c \uc218\uc815\ud55c \ud6c4 \uc778\uc2a4\ud134\uc2a4\ub97c \uc7ac\uae30\ub3d9\ud55c\ub2e4.<\/span><\/p>\n

SQL> alter system set statistics_level=TYPICAL scope=spfile;<\/p>\n

System altered.<\/p>\n

<\/p>\n

SQL> shutdown immediate<\/p>\n

Database closed.<\/p>\n

Database dismounted.<\/p>\n

ORACLE instance shut down.<\/p>\n

<\/p>\n

SQL> startup<\/p>\n

ORACLE instance started.<\/p>\n

<\/p>\n

Total System Global Area  542871552 bytes<\/p>\n

Fixed Size\t\t<\/span>    2254904 bytes<\/p>\n

Variable Size\t\t<\/span>  218105800 bytes<\/p>\n

Database Buffers\t<\/span>  314572800 bytes<\/p>\n

Redo Buffers\t\t<\/span>    7938048 bytes<\/p>\n

Database mounted.<\/p>\n

Database opened.<\/p>\n

<\/p>\n

SQL> show parameter statistics_level<\/p>\n

<\/p>\n

NAME\t\t\t\t<\/span>     TYPE\t<\/span> VALUE<\/p>\n

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

statistics_level\t\t<\/span>     string\t<\/span> TYPICAL<\/p>\n

<\/div>\n


<\/font><\/p>\n


<\/font><\/p>\n


<\/font><\/p>\n


<\/font><\/p>\n


<\/font><\/p>\n


<\/font><\/p>\n


<\/span><\/p>\n

\n

<\/p>\n<\/div>\n

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

AWR \ud1b5\uacc4\uc815\ubcf4 \uc218\uc9d1\uc744 \uc704\ud574 statistics_level\uc744 ALL\ub85c \ubcc0\uacbd\ud558\uc600\ub2e4\uac00 \uc774\ud6c4 LEVEL\uc744 \ub0ae\ucdc4\uc74c\uc5d0\ub3c4  SYSAUX tablespace\uc5d0 \uc800\uc7a5\ub41c \uc815\ubcf4\ub4e4\ub85c\uc778\ud574 \ub514\uc2a4\ud06c full \uc0c1\ud0dc\uac00 \ubc1c\uc0dd\ud55c \uc774\uc288\uac00 \uc788\uc5c8\ub2e4.  SYSAUX tablespace\uc5d0 \uc788\ub294 WRI$_ \ub85c \uc2dc\uc791\ud558\ub294 \ud14c\uc774\ube14\ub4e4\uc5d0 AWR \uc218\uc9d1 \ub370\uc774\ud130 \uac12\uc774 \ub0a8\uc544\uc788\uc5b4 \ubc1c\uc0dd\ud55c \uc774\uc288\ub85c  \ud574\ub2f9 \ud14c\uc774\ube14\uc744 $ORACLE_HOME\/rdbms\/admin \uc5d0 \uc788\ub294 \uc2a4\ud06c\ub9bd\ud2b8\ub97c \uc218\ud589\ud558\uc5ec \uc0ad\uc81c \ud6c4 \uc7ac\uc0dd\uc131\ud560 \uc218 \uc788\ub2e4. 1. AWR \ud1b5\uacc4\uc815\ubcf4 \uc218\uc9d1 \uae30\ub2a5\uc744 \ube44\ud65c\uc131\ud654\ub97c \uc704\ud574\uc11c statistics level\uc744 Basic\uc73c\ub85c \ubcc0\uacbd\ud55c\ub2e4. SQL> show parameter statistics_level NAME      TYPE                  VALUE ———————————— […]<\/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":[14],"tags":[235,236,237],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/488"}],"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=488"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/488\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=488"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=488"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=488"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}