Oracle UTLSYXSZ_UTIL
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose This is one of only two packages 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 source file describes this package as follows:
"This script will estimate the amount of space required for the SYSAUX tablespace. We will estimate based on the number of active sessions, files, tables, indexes, etc."

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.

Remember, with 12c and above, with the new container architecture, database has one SYSAUX tablespace per container.
AUTHID DEFINER
Dependencies
COL$ FILE$ TABSUBPART$
DBMS_OUTPUT GV$INSTANCE USER$
DBMS_STANDARD OBJ$ V$OSSTAT
DBMS_STATS PARTOBJ$ WRH$_ACTIVE_SESSION_HISTORY
DBMS_WORKLOAD_REPOSITORY REGISTRY$ WRM$_SNAPSHOT
DUAL TABPART$ WRM$_WR_CONTROL
First Available 10.1
Security Model Owned by SYS with no granted privileges.
Source {ORACLE_HOME}/rdbms/admin/utlsyxsz.sql

see also {ORACLE_HOME}/rdbms/admin/utlsyxszd.sql
.. comment out drop statement at end of file

SQL> @?/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
-- before logging into SQL*Plus, edit the source script $ORACLE_HOME/rdbms/admin/utlsyxsz.sql, line 1684,
-- and comment out the "DROP PACKAGE" line.
-- This demo was run on a Windows laptop used at Oracle conferences for live demos so the number of tables and user
-- is quite low and some of the defaults optimized using the DBMS_WORKLOAD_REPOSITORY package.

conn / as sysdba

set define on

SQL> set define on
SQL> @?/rdbms/admin/utlsyxsz.sql

This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/utlsyxsz.out

Using the report name /home/oracle/utlsyxsz.out

SP2-0808: Package created with compilation warnings


SP2-0810: Package Body created with compilation warnings

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Estimated at
03:40:47 on Jul 01, 2021 ( Thursday ) in Timezone +00:00


DB_NAME             HOST_PLATFORM                             INST STARTUP_TIME      PAR
-----------------   ---------------------------------------- ----- ----------------- ---
* TEST21DB test21 - Linux x86 64-bit                             1  02:22:01 (01/17) NO

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size: 1,923.9 MB
|
| Total size of SM/AWR 548.8 MB ( 28.5% of SYSAUX )
| Total size of SM/OPTSTAT 387.5 MB ( 20.1% of SYSAUX )
| Total size of SDO 169.9 MB ( 8.8% of SYSAUX )
| Total size of SM/OTHER 136.2 MB ( 7.1% of SYSAUX )
| Total size of XDB 102.4 MB ( 5.3% of SYSAUX )
| Total size of AUDSYS 85.6 MB ( 4.5% of SYSAUX )
| Total size of SM/ADVISOR 62.1 MB ( 3.2% of SYSAUX )
| Total size of AO 48.4 MB ( 2.5% of SYSAUX )
| Total size of LOGMNR 11.0 MB ( 0.6% of SYSAUX )
| Total size of JOB_SCHEDULER 7.8 MB ( 0.4% of SYSAUX )
| Total size of WM 7.3 MB ( 0.4% of SYSAUX )
| Total size of SQL_MANAGEMENT_BASE 3.8 MB ( 0.2% of SYSAUX )
| Total size of SMON_SCN_TIME 3.3 MB ( 0.2% of SYSAUX )
| Total size of PL/SCOPE 3.0 MB ( 0.2% of SYSAUX )
| Total size of TEXT 2.8 MB ( 0.1% of SYSAUX )
| Total size of STREAMS 1.9 MB ( 0.1% of SYSAUX )
| Total size of LOGSTDBY 1.8 MB ( 0.1% of SYSAUX )
| Total size of EM_MONITORING_USER 0.9 MB ( 0.0% of SYSAUX )
| Total size of AUTO_TASK 0.7 MB ( 0.0% of SYSAUX )
| Total size of Others 338.7 MB ( 17.6% 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.07
| otherwise enter an alternative
|
Enter value for active_sessions:

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

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

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

** Value for 'Number of Tables': 182

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

** Value for 'Number of Partitions': 48

|
| 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 68.9 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 182
| Indexes - 221
| Columns - 1,441
| Partitions - 48
| Indexes on Partitions - 144
| Columns in Partitions - 5,232
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR: 92.8 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 8.00 days
| Num Instances - 1
| Active Sessions - 0.07
| Datafiles - 4
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 68.9 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 182
| Indexes - 221
| Columns - 1,441
| Partitions - 48
| Indexes on Partitions - 144
| Columns in Partitions - 5,232
| 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 SDO 169.9 MB
| Est size of SM/OTHER 136.2 MB
| Est size of XDB 102.4 MB
| Est size of AUDSYS 85.6 MB
| Est size of SM/ADVISOR 62.1 MB
| Est size of AO 48.4 MB
| Est size of LOGMNR 11.0 MB
| Est size of JOB_SCHEDULER 7.8 MB
| Est size of WM 7.3 MB
| Est size of SQL_MANAGEMENT_BASE 3.8 MB
| Est size of SMON_SCN_TIME 3.3 MB
| Est size of PL/SCOPE 3.0 MB
| Est size of TEXT 2.8 MB
| Est size of STREAMS 1.9 MB
| Est size of LOGSTDBY 1.8 MB
| Est size of EM_MONITORING_USER 0.9 MB
| Est size of AUTO_TASK 0.7 MB
| Est size of Others 338.7 MB

| Est size of SM/AWR 92.8 MB
| Est size of SM/OPTSTAT 68.9 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 1,149.3 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report

Related Topics
AWRINFO_UTIL
Built-in Functions
Built-in Packages
DBMS_AWR_REPORT_LAYOUT
DBMS_WORKLOAD_REPOSITORY
Tablespaces
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx