{"id":1067,"date":"2016-01-02T04:30:15","date_gmt":"2016-01-02T04:30:15","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1067"},"modified":"2016-01-02T04:30:15","modified_gmt":"2016-01-02T04:30:15","slug":"sysaux-tablespace-%ec%82%ac%ec%9d%b4%ec%a6%88-%ec%98%88%ec%83%81%ed%95%98%ea%b8%b0","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1067","title":{"rendered":"SYSAUX Tablespace \uc0ac\uc774\uc988 \uc608\uc0c1\ud558\uae30"},"content":{"rendered":"

$ORACLE_HOME\/rdbms\/admin\/utlsyxsz.sql <\/SPAN><\/P>
\n

\uac04\ub2e8\ud55c \uc778\ud130\ubdf0\ub97c \ud1b5\ud574 SYSAUX\uc758 \uc0ac\uc774\uc988\ub97c \uc608\uc0c1 \ud560 \uc218 \uc788\ub2e4(\ubcf4\uad00\uc8fc\uae30, interval \ub4f1) <\/SPAN><\/P>
\n

   <\/P>
\n

<\/SPAN><\/P>
\n

   <\/P>
\n

\uc804\ubb38 <\/SPAN><\/P>
\n

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

SYSAUX Size Estimation Report <\/SPAN><\/P>
\n

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

   <\/P>
\n

Estimated at <\/SPAN><\/P>
\n

13:16:38 on Jan 02, 2016 ( Saturday ) in Timezone +09:00 <\/SPAN><\/P>
\n

   <\/P>
\n

   <\/P>
\n

DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR <\/SPAN><\/P>
\n

———– —————————————- —– —————– — <\/SPAN><\/P>
\n

* ORCL1 oracle.com – Linux x86 64-bit 1 12:23:21 (01\/02) NO <\/SPAN><\/P>
\n

   <\/P>
\n

~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

Current SYSAUX usage <\/SPAN><\/P>
\n

~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

| Total SYSAUX size: 516.8 MB <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| Total size of SM\/AWR 40.6 MB ( 7.8% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of SM\/OPTSTAT 10.6 MB ( 2.1% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of XDB 157.5 MB ( 30.5% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of SDO 65.8 MB ( 12.7% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of EM 45.3 MB ( 8.8% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of AO 36.3 MB ( 7.0% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of XSOQHIST 36.3 MB ( 7.0% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of ORDIM\/ORDDATA 13.6 MB ( 2.6% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of LOGMNR 12.3 MB ( 2.4% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of XSAMD 8.8 MB ( 1.7% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of SM\/ADVISOR 7.8 MB ( 1.5% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of SM\/OTHER 6.1 MB ( 1.2% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of TEXT 3.8 MB ( 0.7% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of EXPRESSION_FILTER 3.6 MB ( 0.7% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of WM 3.5 MB ( 0.7% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of SMON_SCN_TIME 2.1 MB ( 0.4% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of SQL_MANAGEMENT_BASE 1.7 MB ( 0.3% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of PL\/SCOPE 1.6 MB ( 0.3% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of LOGSTDBY 1.4 MB ( 0.3% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of STREAMS 1.0 MB ( 0.2% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of EM_MONITORING_USER 0.5 MB ( 0.1% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of ORDIM 0.4 MB ( 0.1% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of JOB_SCHEDULER 0.4 MB ( 0.1% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of AUTO_TASK 0.3 MB ( 0.1% of SYSAUX ) <\/SPAN><\/P>
\n

| Total size of Others 55.7 MB ( 10.8% of SYSAUX ) <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

   <\/P>
\n

~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

AWR Space Estimation <\/SPAN><\/P>
\n

~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

   <\/P>
\n

| To estimate the size of the Automatic Workload Repository (AWR) <\/SPAN><\/P>
\n

| in SYSAUX, we need the following values: <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| – Interval Setting (minutes) <\/SPAN><\/P>
\n

| – Retention Setting (days) <\/SPAN><\/P>
\n

| – Number of Instances <\/SPAN><\/P>
\n

| – Average Number of Active Sessions <\/SPAN><\/P>
\n

| – Number of Datafiles <\/SPAN><\/P>
\n

   <\/P>
\n

| <\/SPAN><\/P>
\n

| For ‘Interval Setting’, <\/SPAN><\/P>
\n

| Press <return> to use the current value: 60.0 minutes <\/SPAN><\/P>
\n

| otherwise enter an alternative <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

Enter value for interval: 1 <\/SPAN><\/P>
\n

   <\/P>
\n

** Value for ‘Interval Setting’: 1 <\/SPAN><\/P>
\n

   <\/P>
\n

| <\/SPAN><\/P>
\n

| For ‘Retention Setting’, <\/SPAN><\/P>
\n

| Press <return> to use the current value: 8.00 days <\/SPAN><\/P>
\n

| otherwise enter an alternative <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

Enter value for retention: <\/SPAN><\/P>
\n

   <\/P>
\n

** Value for ‘Retention Setting’: 8 <\/SPAN><\/P>
\n

   <\/P>
\n

| <\/SPAN><\/P>
\n

| For ‘Number of Instances’, <\/SPAN><\/P>
\n

| Press <return> to use the current value: 1.00 <\/SPAN><\/P>
\n

| otherwise enter an alternative <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

Enter value for num_instances: <\/SPAN><\/P>
\n

   <\/P>
\n

** Value for ‘Number of Instances’: 1 <\/SPAN><\/P>
\n

   <\/P>
\n

| <\/SPAN><\/P>
\n

| For ‘Average Number of Active Sessions’, <\/SPAN><\/P>
\n

| Press <return> to use the current value: 12.0 <\/SPAN><\/P>
\n

| otherwise enter an alternative <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

Enter value for active_sessions: 30 <\/SPAN><\/P>
\n

   <\/P>
\n

** Value for ‘Average Number of Active Sessions’: 30 <\/SPAN><\/P>
\n

   <\/P>
\n

| *************************************************** <\/SPAN><\/P>
\n

| Estimated size of AWR: 6,026.5 MB <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| The AWR estimate was computed using <\/SPAN><\/P>
\n

| the following values: <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| Interval – 1 minutes <\/SPAN><\/P>
\n

| Retention – 8.00 days <\/SPAN><\/P>
\n

| Num Instances – 1 <\/SPAN><\/P>
\n

| Active Sessions – 30.00 <\/SPAN><\/P>
\n

| Datafiles – 12 <\/SPAN><\/P>
\n

| *************************************************** <\/SPAN><\/P>
\n

   <\/P>
\n

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

Optimizer Stat History Space Estimation <\/SPAN><\/P>
\n

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

   <\/P>
\n

| To estimate the size of the Optimizer Statistics History <\/SPAN><\/P>
\n

| we need the following values: <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| – Number of Tables in the Database <\/SPAN><\/P>
\n

| – Number of Partitions in the Database <\/SPAN><\/P>
\n

| – Statistics Retention Period (days) <\/SPAN><\/P>
\n

| – DML Activity in the Database (level) <\/SPAN><\/P>
\n

   <\/P>
\n

| <\/SPAN><\/P>
\n

| For ‘Number of Tables’, <\/SPAN><\/P>
\n

| Press <return> to use the current value: 164.0 <\/SPAN><\/P>
\n

| otherwise enter an alternative <a positive integer> <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

Enter value for number_of_tables: <\/SPAN><\/P>
\n

   <\/P>
\n

** Value for ‘Number of Tables’: 164 <\/SPAN><\/P>
\n

   <\/P>
\n

| <\/SPAN><\/P>
\n

| For ‘Number of Partitions’, <\/SPAN><\/P>
\n

| Press <return> to use the current value: 56.0 <\/SPAN><\/P>
\n

| otherwise enter an alternative <a positive integer> <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

Enter value for number_of_partitions: <\/SPAN><\/P>
\n

   <\/P>
\n

** Value for ‘Number of Partitions’: 56 <\/SPAN><\/P>
\n

   <\/P>
\n

| <\/SPAN><\/P>
\n

| For ‘Statistics Retention’, <\/SPAN><\/P>
\n

| Press <return> to use the current value: 31.0 days <\/SPAN><\/P>
\n

| otherwise enter an alternative <a positive integer> <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

Enter value for stats_retention: <\/SPAN><\/P>
\n

   <\/P>
\n

** Value for ‘Statistics Retention’: 31 <\/SPAN><\/P>
\n

   <\/P>
\n

| <\/SPAN><\/P>
\n

| For ‘DML Activity’, <\/SPAN><\/P>
\n

| Press <return> to use the current value: 2 <medium> <\/SPAN><\/P>
\n

| otherwise enter an alternative <1=low, 2=medium, 3=high> <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

Enter value for dml_activity: <\/SPAN><\/P>
\n

   <\/P>
\n

** Value for ‘DML Activity’: 2 <\/SPAN><\/P>
\n

   <\/P>
\n

| *************************************************** <\/SPAN><\/P>
\n

| Estimated size of Stats history 44.2 MB <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| The space for Optimizer Statistics history was <\/SPAN><\/P>
\n

| estimated using the following values: <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| Tables – 164 <\/SPAN><\/P>
\n

| Indexes – 297 <\/SPAN><\/P>
\n

| Columns – 1,506 <\/SPAN><\/P>
\n

| Partitions – 56 <\/SPAN><\/P>
\n

| Indexes on Partitions – 196 <\/SPAN><\/P>
\n

| Columns in Partitions – 364 <\/SPAN><\/P>
\n

| Stats Retention in Days – 31 <\/SPAN><\/P>
\n

| Level of DML Activity – Medium <\/SPAN><\/P>
\n

| *************************************************** <\/SPAN><\/P>
\n

   <\/P>
\n

~~~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

Estimated SYSAUX usage <\/SPAN><\/P>
\n

~~~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

   <\/P>
\n

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

| Estimated size of AWR: 6,026.5 MB <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| The AWR estimate was computed using <\/SPAN><\/P>
\n

| the following values: <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| Interval – 1 minutes <\/SPAN><\/P>
\n

| Retention – 8.00 days <\/SPAN><\/P>
\n

| Num Instances – 1 <\/SPAN><\/P>
\n

| Active Sessions – 30.00 <\/SPAN><\/P>
\n

| Datafiles – 12 <\/SPAN><\/P>
\n

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

| Estimated size of Stats history 44.2 MB <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| The space for Optimizer Statistics history was <\/SPAN><\/P>
\n

| estimated using the following values: <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| Tables – 164 <\/SPAN><\/P>
\n

| Indexes – 297 <\/SPAN><\/P>
\n

| Columns – 1,506 <\/SPAN><\/P>
\n

| Partitions – 56 <\/SPAN><\/P>
\n

| Indexes on Partitions – 196 <\/SPAN><\/P>
\n

| Columns in Partitions – 364 <\/SPAN><\/P>
\n

| Stats Retention in Days – 31 <\/SPAN><\/P>
\n

| Level of DML Activity – Medium <\/SPAN><\/P>
\n

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

| For all the other components, the estimate <\/SPAN><\/P>
\n

| is equal to the current space usage of <\/SPAN><\/P>
\n

| the component. <\/SPAN><\/P>
\n

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| *************************************************** <\/SPAN><\/P>
\n

| Summary of SYSAUX Space Estimation <\/SPAN><\/P>
\n

| *************************************************** <\/SPAN><\/P>
\n

   <\/P>
\n

| Est size of XDB 157.5 MB <\/SPAN><\/P>
\n

| Est size of SDO 65.8 MB <\/SPAN><\/P>
\n

| Est size of EM 45.3 MB <\/SPAN><\/P>
\n

| Est size of AO 36.3 MB <\/SPAN><\/P>
\n

| Est size of XSOQHIST 36.3 MB <\/SPAN><\/P>
\n

| Est size of ORDIM\/ORDDATA 13.6 MB <\/SPAN><\/P>
\n

| Est size of LOGMNR 12.3 MB <\/SPAN><\/P>
\n

| Est size of XSAMD 8.8 MB <\/SPAN><\/P>
\n

| Est size of SM\/ADVISOR 7.8 MB <\/SPAN><\/P>
\n

| Est size of SM\/OTHER 6.1 MB <\/SPAN><\/P>
\n

| Est size of TEXT 3.8 MB <\/SPAN><\/P>
\n

| Est size of EXPRESSION_FILTER 3.6 MB <\/SPAN><\/P>
\n

| Est size of WM 3.5 MB <\/SPAN><\/P>
\n

| Est size of SMON_SCN_TIME 2.1 MB <\/SPAN><\/P>
\n

| Est size of SQL_MANAGEMENT_BASE 1.7 MB <\/SPAN><\/P>
\n

| Est size of PL\/SCOPE 1.6 MB <\/SPAN><\/P>
\n

| Est size of LOGSTDBY 1.4 MB <\/SPAN><\/P>
\n

| Est size of STREAMS 1.0 MB <\/SPAN><\/P>
\n

| Est size of EM_MONITORING_USER 0.5 MB <\/SPAN><\/P>
\n

| Est size of ORDIM 0.4 MB <\/SPAN><\/P>
\n

| Est size of JOB_SCHEDULER 0.4 MB <\/SPAN><\/P>
\n

| Est size of AUTO_TASK 0.3 MB <\/SPAN><\/P>
\n

| Est size of Others 55.7 MB <\/SPAN><\/P>
\n

   <\/P>
\n

| Est size of SM\/AWR 6,026.5 MB <\/SPAN><\/P>
\n

| Est size of SM\/OPTSTAT 44.2 MB <\/SPAN><\/P>
\n

| <\/SPAN><\/P>
\n

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

| Total Estimated SYSAUX size: 6,536.3 MB <\/SPAN><\/P>
\n

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ <\/SPAN><\/P>
\n

| *************************************************** <\/SPAN><\/P>
\n

   <\/P>
\n

End of Report <\/SPAN><\/P>
\n

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

$ORACLE_HOME\/rdbms\/admin\/utlsyxsz.sql \uac04\ub2e8\ud55c \uc778\ud130\ubdf0\ub97c \ud1b5\ud574 SYSAUX\uc758 \uc0ac\uc774\uc988\ub97c \uc608\uc0c1 \ud560 \uc218 \uc788\ub2e4(\ubcf4\uad00\uc8fc\uae30, interval \ub4f1)         \uc804\ubb38 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SYSAUX Size Estimation Report ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~     Estimated at 13:16:38 on Jan 02, 2016 ( Saturday ) in Timezone +09:00         DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR ———– —————————————- —– —————– — * ORCL1 oracle.com […]<\/p>\n","protected":false},"author":1,"featured_media":1068,"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":[3],"tags":[566],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/1\/cfile26.uf.274FB84056875256159F5E.png?fit=586%2C396","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1067"}],"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=1067"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1067\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/1068"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1067"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1067"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1067"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}