Techdata VINA 지식 공유

$ORACLE_HOME/rdbms/admin/utlsyxsz.sql

간단한 인터뷰를 통해 SYSAUX의 사이즈를 예상 할 수 있다(보관주기, interval 등)

전문

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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 - Linux x86 64-bit 1 12:23:21 (01/02) NO

~~~~~~~~~~~~~~~~~~~~

Current SYSAUX usage

~~~~~~~~~~~~~~~~~~~~

| Total SYSAUX size: 516.8 MB

|

| Total size of SM/AWR 40.6 MB ( 7.8% of SYSAUX )

| Total size of SM/OPTSTAT 10.6 MB ( 2.1% of SYSAUX )

| Total size of XDB 157.5 MB ( 30.5% of SYSAUX )

| Total size of SDO 65.8 MB ( 12.7% of SYSAUX )

| Total size of EM 45.3 MB ( 8.8% of SYSAUX )

| Total size of AO 36.3 MB ( 7.0% of SYSAUX )

| Total size of XSOQHIST 36.3 MB ( 7.0% of SYSAUX )

| Total size of ORDIM/ORDDATA 13.6 MB ( 2.6% of SYSAUX )

| Total size of LOGMNR 12.3 MB ( 2.4% of SYSAUX )

| Total size of XSAMD 8.8 MB ( 1.7% of SYSAUX )

| Total size of SM/ADVISOR 7.8 MB ( 1.5% of SYSAUX )

| Total size of SM/OTHER 6.1 MB ( 1.2% of SYSAUX )

| Total size of TEXT 3.8 MB ( 0.7% of SYSAUX )

| Total size of EXPRESSION_FILTER 3.6 MB ( 0.7% of SYSAUX )

| Total size of WM 3.5 MB ( 0.7% of SYSAUX )

| Total size of SMON_SCN_TIME 2.1 MB ( 0.4% of SYSAUX )

| Total size of SQL_MANAGEMENT_BASE 1.7 MB ( 0.3% of SYSAUX )

| Total size of PL/SCOPE 1.6 MB ( 0.3% of SYSAUX )

| Total size of LOGSTDBY 1.4 MB ( 0.3% of SYSAUX )

| Total size of STREAMS 1.0 MB ( 0.2% of SYSAUX )

| Total size of EM_MONITORING_USER 0.5 MB ( 0.1% of SYSAUX )

| Total size of ORDIM 0.4 MB ( 0.1% of SYSAUX )

| Total size of JOB_SCHEDULER 0.4 MB ( 0.1% of SYSAUX )

| Total size of AUTO_TASK 0.3 MB ( 0.1% of SYSAUX )

| Total size of Others 55.7 MB ( 10.8% of SYSAUX )

|

~~~~~~~~~~~~~~~~~~~~

AWR Space Estimation

~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Automatic Workload Repository (AWR)

| in SYSAUX, we need the following values:

|

| - Interval Setting (minutes)

| - Retention Setting (days)

| - Number of Instances

| - Average Number of Active Sessions

| - Number of Datafiles

|

| For 'Interval Setting',

| Press <return> to use the current value: 60.0 minutes

| otherwise enter an alternative

|

Enter value for interval: 1

** Value for 'Interval Setting': 1

|

| For 'Retention Setting',

| Press <return> to use the current value: 8.00 days

| otherwise enter an alternative

|

Enter value for retention:

** Value for 'Retention Setting': 8

|

| For 'Number of Instances',

| Press <return> to use the current value: 1.00

| otherwise enter an alternative

|

Enter value for num_instances:

** Value for 'Number of Instances': 1

|

| For 'Average Number of Active Sessions',

| Press <return> to use the current value: 12.0

| otherwise enter an alternative

|

Enter value for active_sessions: 30

** Value for 'Average Number of Active Sessions': 30

| ***************************************************

| Estimated size of AWR: 6,026.5 MB

|

| The AWR estimate was computed using

| the following values:

|

| Interval - 1 minutes

| Retention - 8.00 days

| Num Instances - 1

| Active Sessions - 30.00

| Datafiles - 12

| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Optimizer Stat History Space Estimation

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Optimizer Statistics History

| we need the following values:

|

| - Number of Tables in the Database

| - Number of Partitions in the Database

| - Statistics Retention Period (days)

| - DML Activity in the Database (level)

|

| For 'Number of Tables',

| Press <return> to use the current value: 164.0

| otherwise enter an alternative <a positive integer>

|

Enter value for number_of_tables:

** Value for 'Number of Tables': 164

|

| For 'Number of Partitions',

| Press <return> to use the current value: 56.0

| otherwise enter an alternative <a positive integer>

|

Enter value for number_of_partitions:

** Value for 'Number of Partitions': 56

|

| For 'Statistics Retention',

| Press <return> to use the current value: 31.0 days

| otherwise enter an alternative <a positive integer>

|

Enter value for stats_retention:

** Value for 'Statistics Retention': 31

|

| For 'DML Activity',

| Press <return> to use the current value: 2 <medium>

| otherwise enter an alternative <1=low, 2=medium, 3=high>

|

Enter value for dml_activity:

** Value for 'DML Activity': 2

| ***************************************************

| Estimated size of Stats history 44.2 MB

|

| The space for Optimizer Statistics history was

| estimated using the following values:

|

| Tables - 164

| Indexes - 297

| Columns - 1,506

| Partitions - 56

| Indexes on Partitions - 196

| Columns in Partitions - 364

| Stats Retention in Days - 31

| Level of DML Activity - Medium

| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~

Estimated SYSAUX usage

~~~~~~~~~~~~~~~~~~~~~~

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| Estimated size of AWR: 6,026.5 MB

|

| The AWR estimate was computed using

| the following values:

|

| Interval - 1 minutes

| Retention - 8.00 days

| Num Instances - 1

| Active Sessions - 30.00

| Datafiles - 12

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| Estimated size of Stats history 44.2 MB

|

| The space for Optimizer Statistics history was

| estimated using the following values:

|

| Tables - 164

| Indexes - 297

| Columns - 1,506

| Partitions - 56

| Indexes on Partitions - 196

| Columns in Partitions - 364

| Stats Retention in Days - 31

| Level of DML Activity - Medium

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| For all the other components, the estimate

| is equal to the current space usage of

| the component.

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

|

|

| ***************************************************

| Summary of SYSAUX Space Estimation

| ***************************************************

| Est size of XDB 157.5 MB

| Est size of SDO 65.8 MB

| Est size of EM 45.3 MB

| Est size of AO 36.3 MB

| Est size of XSOQHIST 36.3 MB

| Est size of ORDIM/ORDDATA 13.6 MB

| Est size of LOGMNR 12.3 MB

| Est size of XSAMD 8.8 MB

| Est size of SM/ADVISOR 7.8 MB

| Est size of SM/OTHER 6.1 MB

| Est size of TEXT 3.8 MB

| Est size of EXPRESSION_FILTER 3.6 MB

| Est size of WM 3.5 MB

| Est size of SMON_SCN_TIME 2.1 MB

| Est size of SQL_MANAGEMENT_BASE 1.7 MB

| Est size of PL/SCOPE 1.6 MB

| Est size of LOGSTDBY 1.4 MB

| Est size of STREAMS 1.0 MB

| Est size of EM_MONITORING_USER 0.5 MB

| Est size of ORDIM 0.4 MB

| Est size of JOB_SCHEDULER 0.4 MB

| Est size of AUTO_TASK 0.3 MB

| Est size of Others 55.7 MB

| Est size of SM/AWR 6,026.5 MB

| Est size of SM/OPTSTAT 44.2 MB

|

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| Total Estimated SYSAUX size: 6,536.3 MB

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| ***************************************************

End of Report