SYSAUX Tablespace Sizing

 SYSAUX Tablespace Sizing

The focus of this article on an undocumented script, UTLSYXSZ.SQL, located in the $ORACLE_HOME/rdbms/admin directory.

When the UTLSYXSZ.SQL script is run it creates a PL/SQL package named UTLSYXSZ_UTIL. The script then runs an anonymous block that collects user information and outputs a multi-section report. The final action the script performs is to drop the built-in package and undefine the variables it created.

SQL> @?/rdbms/admin/utlsyxsz.sql

Run the above script to get the current sysaux tablespace current/future utilization and AWR space estimation for future growth. A very useful script to plan the size of sysaux tablespace.

Helpful script  $ORACLE_HOME/rdbms/admin/utlsyxsz.sql.  It provides a prediction of the space needed when creating the SYSAUX tablespace to begin with.

The report depends on many input values such as

|   - Interval Setting (minutes)
|   - Retention Setting (days)
|   - Number of Instances
|   - Average Number of Active Sessions
|   - Number of Datafiles

Parameter Collection

After the script defines a number of session variables the package it creates is then called to gather

metadata and produced the following reports.

 SYSAUX Size Estimation Report

 CURRENT SYSAUX Usage Report

 AWR Space Estimation Report

 Optimizer Stat History Space Estimation Report

 Estimated SYSAUX Usage Report

 Summary of SYSAUX Space Estimation

 Finally reporting out the Total Estimated SYSAUX Size

Some of the information in the reports is collected by querying data dictionary tables and dynamic performance views. Other information it solicits from the user by making calls to UTLSYXSZ_UTIL which require the user to input a value (or accept the default), and in a few cases, for example the variable ash_hist which defines how much ASH (Active Session History) data will be examined as part of the report's calculation, the value is partially hard-coded into the package and a script edit is required to alter the value as you can see in the listing below.

Estimated size of Stats history

Note prompted for the number of Indexes or Columns. Generation of these numbers, again, is hard-coded into the package and can be changed before the script is run to produce a value more in line with your actual usage.

Conclusion of the report we see that Oracle is estimating that a system that corresponds to the parameters entered should result in a SYSAUX tablespace sized at about 8.5GB. 

The current SYSAUX, as the result of self-sizing is about 3.9GB so an additional 4.6GB is a good estimate of the space that will be required for expansion should the system suddenly experience an increase in tables, users, and DML activity as anticipated. 

Check temporary tablespace of CDB or PDB databases

 

Check temporary tablespace of CDB or PDB databases

CDB or PDB temporary tablespace files details:

col db_name for a10 col tablespace_name for a10 col file_name for a25 SELECT vc2.name "db_name",tf.file_name, tf.tablespace_name, autoextensible, maxbytes/1024/1024 "Max_MB", SUM(tf.bytes)/1024/1024 "MB_SIZE" FROM v$containers vc2, cdb_temp_files tf WHERE vc2.con_id = tf.con_id GROUP BY vc2.name,tf.file_name, tf.tablespace_name, autoextensible, maxbytes ORDER BY 1, 2;

Check the aggregated size of temporary tablespace for CDB or PDB databases

Col name for a10col tablespace_name for a15
SELECT  vc2.name, tf.tablespace_name, sum(decode(autoextensible,'NO',bytes,'YES',maxbytes))/1024/1024 "Max Bytes", SUM(tf.bytes)/1024/1024
FROM v$containers vc2, cdb_temp_files tf
WHERE vc2.con_id = tf.con_id
GROUP BY vc2.name, tf.tablespace_name
ORDER BY 1, 2;

Oracle database tablespace report

 

Oracle database tablespace report – SQL script

Amazing script written by Kirill Loifman

SQL will show Oracle database tablespace information.. The SQL script will list Oracle database tablespaces including tablespace status and type, counts of files and segments per tablespace and the most important – display proper tablespace 

– New [2.0]: Tablespace (TS) type is extended to display UNIFORM / System Extend Management and ASSM
– New [2.0]: Display actual used space in UNDO and TEMP tablespaces (not HWM as before)
– New [2.0]: Runtime is considerably reduced
– New [2.0]: Compatible with OEM CC 12c/13c output
– Tested on Oracle database 10g, 11g, 12c
– Column: “Max Size” – maximum possible size of a tablespace as a result of Autoextention of database files
– Column: “TS Type” (Tablespace type):
-> LM/DM – Local/Dictionary Managed
-> SYS/UNI – SYStem/UNIform Extent Management (LM only)
-> ASSM/MSSM – Automatic/Manual Segment Space Management (ASSM -> LM only)

set pagesize 10000 linesize 300 tab off   col tablespace_name format A22              heading "Tablespace" col ts_type         format A13              heading "TS Type" col segments        format 999999           heading "Segments" col files           format 9999 col allocated_mb    format 9,999,990.000    heading "Allocated Size|(Mb)" col used_mb         format 9,999,990.000    heading "Used Space|(Mb)" col Free_mb         format 999,990.000      heading "Free Space|(Mb)" col used_pct        format 999              heading "Used|%" col max_ext_mb      format 99,999,990.000   heading "Max Size|(Mb)" col max_free_mb     format 9,999,990.000    heading "Max Free|(Mb)" col max_used_pct    format 999              heading "Max Used|(%)"   BREAK ON REPORT COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE   ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb ON REPORT   WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name),      tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name),      tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics),      ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name) SELECT d.tablespace_name,        d.status,        DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,        a.cnt files,         NVL(s.segcnt,0) segments,        ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,        ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB,        ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB,        ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct,        ROUND(a.maxbytes / 1024 / 1024, 3)  max_ext_mb,        ROUND(NVL(m.used_percent,0), 2) Max_used_pct   FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f  WHERE d.tablespace_name = a.tablespace_name(+)    AND d.tablespace_name = f.tablespace_name(+)    AND d.tablespace_name = m.tablespace_name(+)    AND d.tablespace_name = s.tablespace_name(+)    AND NOT d.contents = 'UNDO'    AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' ) UNION ALL -- TEMP TS SELECT d.tablespace_name,        d.status,        DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,        a.cnt,        0,        ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,        ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB,        ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB,        ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,        ROUND(a.maxbytes / 1024 / 1024, 3)  max_size_mb,        ROUND(NVL(m.used_percent,0), 2) Max_used_pct   FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t  WHERE d.tablespace_name = a.tablespace_name(+)    AND d.tablespace_name = t.tablespace_name(+)    AND d.tablespace_name = m.tablespace_name(+)    AND d.extent_management = 'LOCAL'    AND d.contents = 'TEMPORARY'   UNION ALL -- UNDO TS SELECT d.tablespace_name,        d.status,        DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,        a.cnt,        NVL(s.segcnt,0) segments,        ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,        ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB,        ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB,        ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct,        ROUND(a.maxbytes / 1024 / 1024, 3)  max_size_mb,        ROUND(NVL(m.used_percent,0), 2) Max_used_pct FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = u.tablespace_name(+) AND d.tablespace_name = m.tablespace_name(+) AND d.tablespace_name = s.tablespace_name(+) AND d.contents = 'UNDO' ORDER BY 1 /   prompt * Tablespace (TS) types: prompt .  - LM/DM     - Local/Dictionary Managed prompt .  - SYS/UNI   - SYStem/UNIform Extent Management (LM only) prompt .  - ASSM/MSSM - Automatic/Manual Segment Space Management (ASSM -> LM only) Reference - Script Ref