Oracle UTLSYXSZ_UTIL
Version 11.2.0.3

General Information
Purpose This is the only package I have found in the Oracle database that is non-persistent. By that I mean Oracle builds it on the fly, uses it to create a single report, and then drops it.

The script, utilizing this package, estimates the amount of space required for the SYSAUX tablespace. It estimates based on the number of active sessions, files, tables, indexes, and other factors including ASH and AWR data.

If you edit the script and remove the DROP PACKAGE you will have it in your database for your own use whenever you wish.
AUTHID DEFINER
Dependencies This package does not exist in the database except temporarily while the script is being run.
First Available 10gR1
Security Model Owned by SYS with no granted privileges.
Source {ORACLE_HOME}/rdbms/admin/utlsyxsz.sql
Subprograms
AWR_DISPLAY_VARIABLES OPTSTATS_DISPLAY_VARS SHOW_DEFAULT
AWR_SET_VARIABLES OPTSTATS_GET_VARS SHOW_DEFAULT2
AWR_SPACE_ESTIMATE OPTSTATS_SPACE_EST SYSAUX Tablespace Report
 
AWR_DISPLAY_VARIABLES
This routine will return the number of active sessions, files, interval, retention, and number of instances currently in the system utlsyxsz_util.awr_display_variables(
active_sessions IN NUMBER,
files           IN NUMBER,
interval        IN NUMBER,
retention       IN NUMBER,
num_inst        IN NUMBER,
awr_est         IN NUMBER);
set severoutput on

DECLARE
 vAvgActive     PLS_INTEGER := 300;
 vDB_Files      PLS_INTEGER;
 vInterval      PLS_INTEGER;
 vRetention     PLS_INTEGER;
 vNumInst       PLS_INTEGER;
 vAWR_Size      NUMBER;
 vAWR_SpaceEst  PLS_INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO vDB_Files
  FROM sys.file$
  WHERE status$ = 2;

  SELECT snapint_num, retention_num
  INTO vInterval, vRetention
  FROM sys.wrm$_wr_control
  WHERE dbid = (SELECT dbid FROM v$database);

  SELECT COUNT(*)
  INTO vNumInst
  FROM gv$instance;

  SELECT SUM(space_usage_kbytes)/1024
  INTO vAWR_Size
  FROM v$sysaux_occupants;

  vAWR_SpaceEst := utlsyxsz_util.awr_space_estimate(vAvgActive, vDB_Files, vInterval, vRetention, vNumInst, vAWR_Size);

  utlsyxsz_util.awr_display_variables(vAvgActive, vDB_Files, vInterval, vRetention, vNumInst, vAWR_SpaceEst);
END;
/
 
AWR_SET_VARIABLES
This routine will return the number of active sessions, files, interval, retention, and number of instances currently in the system utlsyxsz_util.awr_set_variables(
active_sessions OUT NUMBER,
files           OUT NUMBER,
interval        OUT NUMBER,
retention       OUT NUMBER,
num_inst        OUT NUMBER);
set severoutput on

DECLARE
 vAvgActive     PLS_INTEGER := 300;
 vDB_Files      PLS_INTEGER;
 vInterval      PLS_INTEGER;
 vRetention     PLS_INTEGER;
 vNumInst       PLS_INTEGER;
 vAWR_SpaceEst  PLS_INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO vDB_Files
  FROM sys.file$
  WHERE status$ = 2;

  SELECT snapint_num, retention_num
  INTO vInterval, vRetention
  FROM sys.wrm$_wr_control
  WHERE dbid = (SELECT dbid FROM v$database);

  SELECT COUNT(*)
  INTO vNumInst
  FROM gv$instance;

  utlsyxsz_util.awr_set_variables(vAvgActive, vDB_Files, vInterval, vRetention, vNumInst);
END;
/
 
AWR_SPACE_ESTIMATE
This routine will return in the estimated usage for the AWR in megabytes (MB) utlsyxsz_util.awr_space_estimate(
active_sessions IN NUMBER,
files           IN NUMBER,
interval        IN NUMBER,
retention       IN NUMBER,
num_inst        IN NUMBER,
cur_awr_size    IN NUMBER)
RETURN NUMBER;
See AWR_DISPLAY_VARIABLES Demo Above
 
OPTSTATS_DISPLAY_VARS
This routine will display the optstats variables utlsyxsz_util.optstats_display_vars(
num_user_tabs      IN NUMBER,
num_user_parts     IN NUMBER,
num_user_inds      IN NUMBER,
num_user_part_inds IN NUMBER,
num_user_cols      IN NUMBER,
num_user_part_cols IN NUMBER,
stats_retention    IN NUMBER,
dml_activity       IN NUMBER,
optstats_est       IN NUMBER);
See OPTSTATS_GET_VARS Demo Below
 
OPTSTATS_GET_VARS
This routine will return the number of system and user tables, indexes, and column currently in the system. It also returns the statistics retention period. utlsyxsz_util.optstats_get_vars(
num_sys_tabs       OUT NUMBER,
num_user_tabs      OUT NUMBER,
num_sys_parts      OUT NUMBER,
num_user_parts     OUT NUMBER,
num_sys_inds       OUT NUMBER,
num_user_inds      OUT NUMBER,
num_sys_part_inds  OUT NUMBER,
num_user_part_inds OUT NUMBER,
num_sys_cols       OUT NUMBER,
num_user_cols      OUT NUMBER,
num_sys_part_cols  OUT NUMBER,
num_user_part_cols OUT NUMBER,
stats_retention    OUT NUMBER);
set serveroutput on

DECLARE
 vNumSysTabs     NUMBER;
 vNumUsrTabs     NUMBER;
 vNumSysParts    NUMBER;
 vNumUsrParts    NUMBER;
 vNumSysInds     NUMBER;
 vNumUsrInds     NUMBER;
 vNumSysPartInds NUMBER;
 vNumUsrPartInds NUMBER;
 vNumSysCols     NUMBER;
 vNumUsrCols     NUMBER;
 vNumSysPartCols NUMBER;
 vNumUsrPartCols NUMBER;
 vStatsRetention NUMBER;
 vSpaceEst       NUMBER;
 vPctVolatile    NUMBER := 0.5;
 vPctSkewedCols  NUMBER := 0.11;
 vPctCandParts   NUMBER := 0.2;
 vDaysToStaleLow NUMBER := 15;
 vDaysToStale    NUMBER := 6;
BEGIN
  utlsyxsz_util.optstats_get_vars(vNumSysTabs, vNumUsrTabs, vNumSysParts, vNumUsrParts,
  vNumSysInds, vNumUsrInds, vNumSysPartInds, vNumUsrPartInds, vNumSysCols, vNumUsrCols,
  vNumSysPartCols, vNumUsrPartCols, vStatsRetention);

  vSpaceEst := utlsyxsz_util.optstats_space_est(vNumSysTabs, vNumUsrTabs, vNumSysParts,
  vNumUsrParts, vNumSysInds, vNumUsrInds, vNumSysPartInds, vNumUsrPartInds, vNumSysCols,
  vNumUsrCols, vNumSysPartCols, vNumUsrPartCols, vPctVolatile, vPctSkewedCols,
  vPctCandParts, vDaysToStale, vDaysToStaleLow, vStatsRetention);

  utlsyxsz_util.optstats_display_vars(vNumSysTabs, vNumUsrTabs, vNumUsrParts, vNumUsrInds,
  vNumUsrPartInds, vNumUsrCols, vNumUsrPartCols, 2, vSpaceEst);
END;
/
 
OPTSTATS_SPACE_EST
Returns the estimated usage for Optimizer Stats versions in megabytes (MB) utlsyxsz_util.optstats_space_est(
num_sys_tabs       IN NUMBER,
num_user_tabs      IN NUMBER,
num_sys_parts      IN NUMBER,
num_user_parts     IN NUMBER,
num_sys_inds       IN NUMBER,
num_user_inds      IN NUMBER,
num_sys_part_inds  IN NUMBER,
num_user_part_inds IN NUMBER,
num_sys_cols       IN NUMBER,
num_user_cols      IN NUMBER,
num_sys_part_cols  IN NUMBER,
num_user_part_cols IN NUMBER,
pct_volatile       IN NUMBER,
pct_skewed_cols    IN NUMBER,
pct_cand_parts     IN NUMBER,
days_to_stale      IN NUMBER,
days_to_stale_low  IN NUMBER,
stats_retention    IN NUMBER)
RETURN NUMBER;
See OPTSTATS_GET_VARS Demo Above
 
SHOW_DEFAULT
Display the default value for a variable utlsyxsz_util.show_default(
name  IN VARCHAR2,
value IN NUMBER,
unit  IN VARCHAR2 DEFAULT NULL,
list  IN VARCHAR2 DEFAULT NULL);
set serveroutput on

DECLARE
 vInterval  PLS_INTEGER;
 vRetention PLS_INTEGER;
BEGIN
  SELECT snapint_num, retention_num
  INTO vInterval, vRetention
  FROM sys.wrm$_wr_control
  WHERE dbid = (SELECT dbid FROM v$database);

  utlsyxsz_util.show_default('Interval Setting', (vInterval / 60), 'minutes');
  utlsyxsz_util.show_default('Retention Setting', (vRetention / 86400), 'days');
END;
/
 
SHOW_DEFAULT2
Another routine to display the default value of a variable. Apparently the developer never heard of overloading. utlsyxsz_util.show_default2(
name  IN VARCHAR2,
value IN VARCHAR2,
list  IN VARCHAR2 DEFAULT NULL);
set severoutput on

DECLARE
 dml_act PLS_INTEGER := 2;
BEGIN
  utlsyxsz_util.show_default2('DML Activity', dml_act, '<1=low, 2=medium, 3=high>');
END;
/
 
SYSAUX Tablespace Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
00:14:00 on Oct 27, 2010 ( Wednesday ) in Timezone -07:00


DB_NAME     HOST_PLATFORM                                     INST   STARTUP_TIME     PAR
----------- ----------------------------------------          ----- ----------------- ---
*           ORABASE2 PERRITO3 - Microsoft Windows IA (32-bit) 1     14:40:58 (10/21)  NO

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size: 594.3 MB
|
| Total size of SM/AWR 95.2 MB ( 16.0% of SYSAUX )
| Total size of SM/OPTSTAT 67.5 MB ( 11.4% of SYSAUX )
| Total size of XDB 128.1 MB ( 21.5% of SYSAUX )
| Total size of EM 59.0 MB ( 9.9% of SYSAUX )
| Total size of SDO 44.9 MB ( 7.6% of SYSAUX )
| Total size of XSOQHIST 35.7 MB ( 6.0% of SYSAUX )
| Total size of AO 35.7 MB ( 6.0% of SYSAUX )
| Total size of SM/ADVISOR 17.4 MB ( 2.9% of SYSAUX )
| Total size of ORDIM/ORDDATA 11.3 MB ( 1.9% of SYSAUX )
| Total size of LOGMNR 7.9 MB ( 1.3% of SYSAUX )
| Total size of XSAMD 7.3 MB ( 1.2% of SYSAUX )
| Total size of SM/OTHER 6.0 MB ( 1.0% of SYSAUX )
| Total size of EXPRESSION_FILTER 3.6 MB ( 0.6% of SYSAUX )
| Total size of TEXT 3.5 MB ( 0.6% of SYSAUX )
| Total size of SMON_SCN_TIME 3.3 MB ( 0.5% of SYSAUX )
| Total size of WM 3.1 MB ( 0.5% 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.2% of SYSAUX )
| Total size of JOB_SCHEDULER 1.3 MB ( 0.2% of SYSAUX )
| Total size of STREAMS 1.0 MB ( 0.2% of SYSAUX )
| Total size of EM_MONITORING_USER 0.6 MB ( 0.1% of SYSAUX )
| Total size of ORDIM 0.4 MB ( 0.1% of SYSAUX )
| Total size of AUTO_TASK 0.3 MB ( 0.1% of SYSAUX )
| Total size of Others 56.8 MB ( 9.5% 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:

** Value for 'Interval Setting': 60

|
| 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: 0.01
| otherwise enter an alternative
|
Enter value for active_sessions:

** Value for 'Average Number of Active Sessions': .01

| ***************************************************
| Estimated size of AWR: 92.1 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 8.00 days
| Num Instances - 1
| Active Sessions - 0.01
| Datafiles - 11
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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: 303.0
| otherwise enter an alternative <a positive integer>
|
Enter value for number_of_tables:

** Value for 'Number of Tables': 303

|
| For 'Number of Partitions',
| Press <return> to use the current value: 74.0
| otherwise enter an alternative <a positive integer>
|
Enter value for number_of_partitions:

** Value for 'Number of Partitions': 74

|
| 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 46.1 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 303
| Indexes - 491
| Columns - 2,664
| Partitions - 74
| Indexes on Partitions - 214
| Columns in Partitions - 364
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR: 92.1 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 8.00 days
| Num Instances - 1
| Active Sessions - 0.01
| Datafiles - 11
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 46.1 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 303
| Indexes - 491
| Columns - 2,664
| Partitions - 74
| Indexes on Partitions - 214
| 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 128.1 MB
| Est size of EM 59.0 MB
| Est size of SDO 44.9 MB
| Est size of XSOQHIST 35.7 MB
| Est size of AO 35.7 MB
| Est size of SM/ADVISOR 17.4 MB
| Est size of ORDIM/ORDDATA 11.3 MB
| Est size of LOGMNR 7.9 MB
| Est size of XSAMD 7.3 MB
| Est size of SM/OTHER 6.0 MB
| Est size of EXPRESSION_FILTER 3.6 MB
| Est size of TEXT 3.5 MB
| Est size of SMON_SCN_TIME 3.3 MB
| Est size of WM 3.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 JOB_SCHEDULER 1.3 MB
| Est size of STREAMS 1.0 MB
| Est size of EM_MONITORING_USER 0.6 MB
| Est size of ORDIM 0.4 MB
| Est size of AUTO_TASK 0.3 MB
| Est size of Others 56.8 MB

| Est size of SM/AWR 92.1 MB
| Est size of SM/OPTSTAT 46.1 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 569.7 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report

Related Topics
Packages
Tablespaces

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved