Oracle Database Commands and Practical Examples: January 2021

Tuesday 12 January 2021

PT - Oracle Compression Basic and advanced table example

PT - Oracle Compression Basic and advanced

Query to check table compression

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES;

TABLE_NAME COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

ADDRESSES DISABLED

PRODUCT_INFORMATI DISABLED

LOGON DISABLED

ORDERS DISABLED

CARD_DETAILS DISABLED

INVENTORIES DISABLED

WAREHOUSES DISABLED

JOBS DISABLED

CUSTOMERS DISABLED

TABLE_NAME COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

PRODUCT_DESCRIPTI DISABLED

ONS

ORDER_ITEMS DISABLED

V DISABLED

JOB_HISTORY DISABLED

EMP DISABLED

DEPT DISABLED

ORDERENTRY_METADA DISABLED

TABLE_NAME COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

CUST_SOURCE DISABLED

CUST_BCOMPRESSED ENABLED BASIC

CUST_ACOMPRESSED ENABLED ADVANCED

19 rows selected.

SQL>

Create table with no compression basic and advanced.

No compression : CUST_SOURCE

Basic Compression : CUST_BCOMPRESSED

Advanced Compression : CUST_ACOMPRESSED

SQL> conn soe/soe

Connected.

SQL> CREATE TABLE CUST_SOURCE

2 ( CUSTOMER_NO NUMBER(6),

3 FIRST_NAME VARCHAR2(20),

4 LAST_NAME VARCHAR2(20),

5 NOTE1 VARCHAR2(100),

6 NOTE2 VARCHAR2(100)

7 ) NOLOGGING PCTFREE 0 TABLESPACE SOETBS;

Table created.

SQL> CREATE TABLE CUST_BCOMPRESSED

2 ( CUSTOMER_NO NUMBER(6),

3 FIRST_NAME VARCHAR2(20),

4 LAST_NAME VARCHAR2(20),

5 NOTE1 VARCHAR2(100),

6 NOTE2 VARCHAR2(100)

7 ) ROW STORE COMPRESS BASIC NOLOGGING TABLESPACE SOETBS;

Table created.

SQL> CREATE TABLE CUST_ACOMPRESSED

2 ( CUSTOMER_NO NUMBER(6),

3 FIRST_NAME VARCHAR2(20),

4 LAST_NAME VARCHAR2(20),

5 NOTE1 VARCHAR2(100),

6 NOTE2 VARCHAR2(100)

7 ) ROW STORE COMPRESS ADVANCED NOLOGGING PCTFREE 0 TABLESPACE SOETBS;

Table created.

Insert value on all tables.

SQL> INSERT INTO CUST_SOURCE

2 SELECT LEVEL AS CUSTOMER_NO,

3 DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(10,20))) FIRST_NAME,

4 DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(10,20))) LAST_NAME,

5 DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(0,100))) NOTE1,

6 DBMS_RANDOM.STRING('A', TRUNC(DBMS_RANDOM.value(0,100))) NOTE2

7 FROM DUAL

8 CONNECT BY LEVEL <= 100000;

100000 rows created.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO CUST_BCOMPRESSED SELECT * FROM CUST_SOURCE;

100000 rows created.

SQL> COMMIT;

Commit complete.

Gather stats on the all tables.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_SOURCE');

PL/SQL procedure successfully completed.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_BCOMPRESSED');

PL/SQL procedure successfully completed.

SQL>

Check block size compression on the all tables

SQL> col TABLE_NAME format a17

SQL> SELECT TABLE_NAME, BLOCKS*8 SIZE_KB, COMPRESSION, PCT_FREE

2 FROM USER_TABLES

3 WHERE TABLE_NAME IN ('CUST_SOURCE','CUST_BCOMPRESSED','CUST_ACOMPRESSED');

TABLE_NAME SIZE_KB COMPRESS PCT_FREE

----------------- ---------- -------- ----------

CUST_ACOMPRESSED ENABLED 0

CUST_BCOMPRESSED 15056 ENABLED 0

CUST_SOURCE 15056 DISABLED 0

Truncate and insert value on compressed table using direct path loading hints . As compare to basic compression there is small difference in advanced. check the status below.

SQL> TRUNCATE TABLE CUST_ACOMPRESSED;

Table truncated.

SQL> INSERT /*+ APPEND */ INTO CUST_ACOMPRESSED SELECT * FROM CUST_SOURCE;

100000 rows created.

SQL> COMMIT;

Commit complete.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'CUST_ACOMPRESSED');

PL/SQL procedure successfully completed.

SQL> col TABLE_NAME format a17

SQL> SELECT TABLE_NAME, BLOCKS*8 SIZE_KB, COMPRESSION, PCT_FREE

2 FROM USER_TABLES

3 WHERE TABLE_NAME IN ('CUST_SOURCE','CUST_BCOMPRESSED','CUST_ACOMPRESSED');

TABLE_NAME SIZE_KB COMPRESS PCT_FREE

----------------- ---------- -------- ----------

CUST_ACOMPRESSED 4360 ENABLED 0

CUST_BCOMPRESSED 4360 ENABLED 0

CUST_SOURCE 5984 DISABLED 0

Create Basic level of compression from no compressed table

SQL> CREATE TABLE CUST_BCOMPRESSED1 ROW STORE COMPRESS BASIC as select * from CUST_SOURCE ;

Table created.

Change existing table to compression

SQL> ALTER TABLE CUST_SOURCE ROW STORE COMPRESS BASIC;

Table altered.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES where table_name='CUST_SOURCE';

TABLE_NAME COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

CUST_SOURCE ENABLED BASIC

Change existing table from basic to compression

SQL> ALTER TABLE CUST_SOURCE ROW STORE COMPRESS advanced;

Table altered.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES where table_name='CUST_SOURCE';

TABLE_NAME COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

CUST_SOURCE ENABLED ADVANCED

SQL>

SQL> ALTER TABLE CUST_SOURCE noCOMPRESS;

Table altered.

SQL> SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES where table_name='CUST_SOURCE';

TABLE_NAME COMPRESS COMPRESS_FOR

----------------- -------- ------------------------------

CUST_SOURCE DISABLED

Create tablespace with advanced level of compression

SQL> create tablespace adv_comp datafile 'C:\APP\VARUNYADAV\ORADATA\ORCL2\advcomp01.DMP' size 10m DEFAULT ROW STORE COMPRESS ADVANCED;

Tablespace created.

Determining Which Rows Are Compressed

SELECT DECODE(DBMS_COMPRESSION.GET_COMPRESSION_TYPE(OWNNAME => 'SOE',TABNAME => 'SALES_HISTORY',SUBOBJNAME => '',ROW_ID => 'AAAKEIEEGBBADBTDEDD'),

1, 'No Compression',

2, 'Advanced Row Compression',

4, 'Hybrid Columnar Compression for Query High',

8, 'Hybrid Columnar Compression for Query Low',

16, 'Hybrid Columnar Compression for Archive High',

32, 'Hybrid Columnar Compression for Archive Low',

4096, 'Basic Table Compression',

'Unknown Compression Type') COMPRESSION_TYPE

FROM DUAL;

Compression Related Statistics

SQL> SELECT N.NAME, S.VALUE FROM V$MYSTAT S, V$STATNAME N WHERE S.STATISTIC# = N.STATISTIC# AND N.NAME LIKE '%HSC%';

NAME VALUE

---------------------------------------------------------------- ----------

HSC OLTP Space Saving 1177689

HSC OLTP Compressed Blocks 510

HSC IDL Compressed Blocks 3379

HSC Compressed Segment Block Changes 6691

HSC Heap Segment Block Changes 11782

HSC OLTP Non Compressible Blocks 508

HSC OLTP positive compression 1018

HSC OLTP negative compression 0

HSC OLTP recursive compression 0

HSC OLTP inline compression 1018

HSC OLTP partial compression 0

NAME VALUE

---------------------------------------------------------------- ----------

HSC OLTP Drop Column 0

HSC OLTP Compression skipped rows 0

HSC OLTP compression block checked 0

HSC OLTP Compression wide compressed row pieces 0

15 rows selected.

Monday 11 January 2021

AWR - Delete AWR Snapshots ID

AWR - Delete AWR Snapshots ID

Query to display the existing AWR snapshots period

SQL> col BEGIN_INTERVAL_TIME format a28

SQL> col END_INTERVAL_TIME format a28

SQL> SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME, SNAP_LEVEL

2 FROM DBA_HIST_SNAPSHOT

3 ORDER BY SNAP_ID;

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL

---------- ---------------------------- ---------------------------- ----------

237 03-JAN-21 11.11.04.000 AM 03-JAN-21 11.21.29.133 AM 1

238 03-JAN-21 11.21.29.133 AM 03-JAN-21 12.30.52.205 PM 1

239 03-JAN-21 12.30.52.205 PM 03-JAN-21 01.30.06.140 PM 1

240 03-JAN-21 01.30.06.140 PM 03-JAN-21 02.30.41.034 PM 1

241 03-JAN-21 02.30.41.034 PM 03-JAN-21 04.43.52.074 PM 1

242 03-JAN-21 04.43.52.074 PM 04-JAN-21 10.53.30.874 AM 1

243 04-JAN-21 10.53.30.874 AM 04-JAN-21 01.21.24.107 PM 1

244 04-JAN-21 01.21.24.107 PM 04-JAN-21 06.52.48.561 PM 1

245 04-JAN-21 06.52.48.561 PM 04-JAN-21 07.30.44.011 PM 1

246 04-JAN-21 07.30.44.011 PM 04-JAN-21 08.30.48.957 PM 1

247 04-JAN-21 08.30.48.957 PM 04-JAN-21 09.30.35.943 PM 1

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL

---------- ---------------------------- ---------------------------- ----------

248 04-JAN-21 09.30.35.943 PM 04-JAN-21 10.41.13.096 PM 1

249 04-JAN-21 10.41.13.096 PM 04-JAN-21 11.30.08.754 PM 1

250 05-JAN-21 08.52.50.000 PM 05-JAN-21 09.03.53.714 PM 1

251 05-JAN-21 09.03.53.714 PM 05-JAN-21 11.03.33.863 PM 1

252 06-JAN-21 04.29.38.000 PM 06-JAN-21 04.39.51.949 PM 1

253 06-JAN-21 04.39.51.949 PM 06-JAN-21 05.30.14.405 PM 1

254 06-JAN-21 06.40.07.000 PM 06-JAN-21 06.51.12.663 PM 1

255 06-JAN-21 06.51.12.663 PM 06-JAN-21 07.30.42.067 PM 1

256 07-JAN-21 01.40.05.000 PM 07-JAN-21 01.50.44.257 PM 1

257 07-JAN-21 01.50.44.257 PM 07-JAN-21 02.30.02.151 PM 1

258 07-JAN-21 02.30.02.151 PM 07-JAN-21 03.30.09.955 PM 1

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL

---------- ---------------------------- ---------------------------- ----------

259 07-JAN-21 03.30.09.955 PM 07-JAN-21 04.30.15.553 PM 1

260 07-JAN-21 04.30.15.553 PM 07-JAN-21 05.30.20.993 PM 1

261 07-JAN-21 05.30.20.993 PM 07-JAN-21 06.30.25.701 PM 1

262 07-JAN-21 06.30.25.701 PM 07-JAN-21 07.30.31.904 PM 1

263 07-JAN-21 07.30.31.904 PM 07-JAN-21 08.30.38.215 PM 1

264 07-JAN-21 08.30.38.215 PM 07-JAN-21 10.48.52.857 PM 1

265 07-JAN-21 10.48.52.857 PM 07-JAN-21 11.30.55.648 PM 1

266 07-JAN-21 11.30.55.648 PM 08-JAN-21 11.17.14.791 AM 1

267 08-JAN-21 11.17.14.791 AM 08-JAN-21 12.30.30.901 PM 1

268 08-JAN-21 12.30.30.901 PM 08-JAN-21 03.13.41.727 PM 1

269 08-JAN-21 03.13.41.727 PM 08-JAN-21 04.30.46.203 PM 1

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL

---------- ---------------------------- ---------------------------- ----------

270 08-JAN-21 04.30.46.203 PM 08-JAN-21 05.30.52.773 PM 1

271 08-JAN-21 05.30.52.773 PM 08-JAN-21 06.30.55.994 PM 1

272 08-JAN-21 06.30.55.994 PM 08-JAN-21 07.30.03.886 PM 1

273 08-JAN-21 07.30.03.886 PM 09-JAN-21 04.45.43.649 PM 1

274 09-JAN-21 04.45.43.649 PM 09-JAN-21 05.30.38.500 PM 1

275 09-JAN-21 05.30.38.500 PM 09-JAN-21 06.30.53.204 PM 1

276 11-JAN-21 01.27.38.000 PM 11-JAN-21 01.38.49.671 PM 1

277 11-JAN-21 01.38.49.671 PM 11-JAN-21 02.30.59.139 PM 1

278 11-JAN-21 02.30.59.139 PM 11-JAN-21 03.30.47.602 PM 1

279 11-JAN-21 03.30.47.602 PM 11-JAN-21 04.30.52.254 PM 1

43 rows selected.

Flush all AWR snapshots and check snapshots period details.

If FLUSH_LEVEL is not specified, it defaults to TYPICAL for a non-CDB database.

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(FLUSH_LEVEL=>'ALL');

PL/SQL procedure successfully completed.

SQL> SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME, SNAP_LEVEL

2 FROM DBA_HIST_SNAPSHOT

3 ORDER BY SNAP_ID DESC FETCH FIRST 1 ROWS ONLY;

SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME SNAP_LEVEL

---------- ---------------------------- ---------------------------- ----------

279 11-JAN-21 03.30.47.602 PM 11-JAN-21 04.30.52.254 PM 1

Delete based on SNAP ID

SQL> DEFINE v_snapID = &Enter_SnapID

Enter value for enter_snapid: 279

SQL> begin

2 DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(

3 LOW_SNAP_ID => &v_snapID,

4 HIGH_SNAP_ID => &v_snapID);

5 end;

6 /

old 3: LOW_SNAP_ID => &v_snapID,

new 3: LOW_SNAP_ID => 279,

old 4: HIGH_SNAP_ID => &v_snapID);

new 4: HIGH_SNAP_ID => 279);

PL/SQL procedure successfully completed.

AWR - Script execution's For non and RAC Database AWR report

Script execution's For non and RAC Database AWR report

Example Below for 2 node RAC .

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------

SMRRAC smrrac1 smrrac READ WRITE PRIMARY YES 0

Execute statement example awrgrpti.sql

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

Specify the Report Type

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

AWR reports can be generated in the following formats. Please enter the

name of the format at the prompt. Default value is 'html'.

'html' HTML format (default)

'text' Text format

'active-html' Includes Performance Hub active report

Enter value for report_type:

Type Specified: html

Instances in this Workload Repository schema

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

DB Id INSTT_NUM DB Name INSTT_NAME Host

------------ ---------- ------------ ---------------- ------------

* 4255065810 2 SMRRAC smrrac2 s01

* 4255065810 1 SMRRAC smrrac1 s02

Enter value for dbid: 4255065810

Header Details:

AWR - Set AWR Retention period from 1hr to 30 minutes

Enabling and Disabling AWR Snapshots Capture

enabled when STATISTICS_LEVEL = TYPICAL or ALL

disabled when STATISTICS_LEVEL = BASIC

Initial settings: RETENTION = 8 days, INTERVAL = 60 minutes and 31 days

Execute command to Check for snapshot intervals period:

SQL> SELECT SNAP_INTERVAL, RETENTION FROM DBA_HIST_WR_CONTROL;

SNAP_INTERVAL

---------------------------------------------------------------------------

RETENTION

---------------------------------------------------------------------------

+00000 01:00:00.0

+00008 00:00:00.0

+40150 00:01:00.0

+00008 00:00:00.0

Execute statement to set AWR STATISTICS_LEVEL to ALL to change interval time.

C:\Users\varun>sqlplus sys/system123@pdb as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 11 16:48:31 2021

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show parameter STATISTICS_LEVEL

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

client_statistics_level string TYPICAL

statistics_level string TYPICAL

SQL> ALTER SYSTEM SET STATISTICS_LEVEL=ALL SCOPE=BOTH;

SQL> col SNAP_INTERVAL format a20

SQL> col RETENTION format a20

SQL> col TOPNSQL format a10

SQL> SELECT SNAP_INTERVAL, RETENTION, TOPNSQL FROM DBA_HIST_WR_CONTROL;

SNAP_INTERVAL RETENTION TOPNSQL

-------------------- -------------------- ----------

+40150 00:01:00.0 +00008 00:00:00.0 DEFAULT

+00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

Change snapshot interval from 1 hour to 30 minute

SQL> BEGIN

2 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 30);

3 END;

4 /

PL/SQL procedure successfully completed.

Statement to check Snapshot interval period

SQL> col SNAP_INTERVAL format a20

SQL> col RETENTION format a20

SQL> col TOPNSQL format a10

SQL> SELECT SNAP_INTERVAL, RETENTION, TOPNSQL FROM DBA_HIST_WR_CONTROL;

SNAP_INTERVAL RETENTION TOPNSQL

-------------------- -------------------- ----------

+00000 01:00:00.0 +00008 00:00:00.0 DEFAULT

+00000 00:30:00.0 +00008 00:00:00.0 DEFAULT

AWR- Managing Space Consumed by AWR Snapshots

Space consumed by AWR snapshots

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------

RAC rac1 rac READ WRITE PRIMARY YES 0

Execute command to check Space consumed by AWR snapshots

SQL> SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES/1024 MB, MOVE_PROCEDURE FROM V$SYSAUX_OCCUPANTS WHERE OCCUPANT_NAME = 'SM/AWR';

OCCUPANT_NAME MB MOVE_PROCEDURE

---------------------------------------------------------------- ---------- ----------------------------------------------------------------

SM/AWR 814.75

This script will report general AWR information

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

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

Specify the Report File Name

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

The default report file name is awrinfo.txt. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: test.txt

Using the report name test.txt

No errors.

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

AWR INFO Report

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

Report generated at

12:06:14 on Jan 11, 2021 ( Monday ) in Timezone +01:00

Warning: Non Default AWR Setting!

--------------------------------------------------------------------------------

Snapshot interval is 60 minutes and Retention is 8 days

DB_ID DB_NAME HOST_PLATFORM INST STARTUP_TIME LAST_ASH_SID PAR

------------ --------- ---------------------------------------- ----- ----------------- ------------ ---

* 4255065810 SMRRAC sgdcplm02 - Linux x86 64-bit 1 07:34:38 (01/09) 39017973 YES

4255065810 SMRRAC sgdcplm01 - Linux x86 64-bit 2 07:34:07 (01/09) 39653620 YES

########################################################

(I) AWR Snapshots Information

########################################################

*****************************************************

(1a) SYSAUX usage - Schema breakdown (dba_segments)

*****************************************************

| Total SYSAUX size 2,175.6 MB ( 7% of 32,768.0 MB MAX with AUTOEXTEND ON )

| Schema SYS occupies 1,711.9 MB ( 78.7% )

| Schema APEX_040200 occupies 248.4 MB ( 11.4% )

| Schema MDSYS occupies 93.8 MB ( 4.3% )

| Schema XDB occupies 68.6 MB ( 3.2% )

| Schema ORDDATA occupies 16.1 MB ( 0.7% )

| Schema SYSTEM occupies 15.8 MB ( 0.7% )

| Schema WMSYS occupies 7.2 MB ( 0.3% )

| Schema DVSYS occupies 4.4 MB ( 0.2% )

| Schema CTXSYS occupies 3.8 MB ( 0.2% )

| Schema AUDSYS occupies 2.6 MB ( 0.1% )

| Schema GSMADMIN_INT occupies 1.4 MB ( 0.1% )

| Schema DBSNMP occupies 1.3 MB ( 0.1% )

| Schema ORDSYS occupies 0.4 MB ( 0.0% )

********************************************************

(1b) SYSAUX occupants space usage (v$sysaux_occupants)

********************************************************

| Occupant Name Schema Name Space Usage

| -------------------- -------------------- ----------------

| SM/AWR SYS 814.8 MB

| SM/OPTSTAT SYS 477.5 MB

| SDO MDSYS 93.8 MB

| SM/ADVISOR SYS 89.1 MB

| XDB XDB 68.6 MB

| SM/OTHER SYS 66.9 MB

| AO SYS 38.9 MB

| XSOQHIST SYS 38.9 MB

| JOB_SCHEDULER SYS 16.6 MB

| ORDIM/ORDDATA ORDDATA 16.1 MB

| LOGMNR SYSTEM 13.9 MB

| WM WMSYS 7.2 MB

| TEXT CTXSYS 3.8 MB

| SMON_SCN_TIME SYS 3.3 MB

| AUDSYS AUDSYS 2.6 MB

| SQL_MANAGEMENT_BASE SYS 2.4 MB

| PL/SCOPE SYS 1.6 MB

| LOGSTDBY SYSTEM 1.5 MB

| EM_MONITORING_USER DBSNMP 1.3 MB

| STREAMS SYS 1.0 MB

| ORDIM ORDSYS 0.4 MB

| AUTO_TASK SYS 0.4 MB

| AUDIT_TABLES SYS 0.0 MB

| EM SYSMAN 0.0 MB

| EXPRESSION_FILTER EXFSYS 0.0 MB

| ORDIM/ORDPLUGINS ORDPLUGINS 0.0 MB

| ORDIM/SI_INFORMTN_SC SI_INFORMTN_SCHEMA 0.0 MB

| STATSPACK PERFSTAT 0.0 MB

| TSM TSMSYS 0.0 MB

| ULTRASEARCH WKSYS 0.0 MB

| ULTRASEARCH_DEMO_USE WK_TEST 0.0 MB

| XSAMD OLAPSYS 0.0 MB

| Others (Unaccounted space) 415.1 MB

******************************************

(1c) SYSAUX usage - Unregistered Schemas

******************************************

| This section displays schemas that are not registered

| in V$SYSAUX_OCCUPANTS

| Schema APEX_040200 occupies 248.4 MB

| Schema DVSYS occupies 4.4 MB

| Schema GSMADMIN_INT occupies 1.4 MB

| Total space 254.3 MB

*************************************************************

(1d) SYSAUX usage - Unaccounted space in registered schemas

*************************************************************

| This section displays unaccounted space in the registered

| schemas of V$SYSAUX_OCCUPANTS.

| Unaccounted space in SYS/SYSTEM 160.9 MB

| Total space 160.9 MB

*************************************

(2) Size estimates for AWR snapshots

*************************************

| Estimates based on 60 mins snapshot INTERVAL:

| AWR size/day 46.3 MB (1,977 K/snap * 24 snaps/day)

| AWR size/wk 324.4 MB (size_per_day * 7) per instance

| AWR size/wk 648.7 MB (size_per_day * 7) per database

| Estimates based on 24 snaps in past 24 hours:

| AWR size/day 46.3 MB (1,977 K/snap and 24 snaps in past 24 hours)

| AWR size/wk 324.4 MB (size_per_day * 7) per instance

| AWR size/wk 648.7 MB (size_per_day * 7) per database

**********************************

(3a) Space usage by AWR components (per database)

**********************************

COMPONENT MB % AWR KB_PER_SNAP MB_PER_DAY MB_PER_WEEK TABLE% : INDEX%

--------- --------- ------ ------------ ---------- ----------- ----------------

FIXED 407.1 50.0 988 23.2 162.1 51% : 49%

EVENTS 123.7 15.2 300 7.0 49.2 46% : 54%

SQLPLAN 67.0 8.2 163 3.8 26.7 67% : 33%

SQL 32.2 4.0 78 1.8 12.8 75% : 25%

SPACE 22.0 2.7 53 1.3 8.8 63% : 37%

ASH 10.0 1.2 24 0.6 4.0 78% : 23%

SQLBIND 10.0 1.2 24 0.6 4.0 40% : 60%

RAC 8.8 1.1 21 0.5 3.5 52% : 48%

SQLTEXT 5.4 0.7 13 0.3 2.1 93% : 7%

**********************************

(3b) Space usage within AWR Components (> 500K)

**********************************

COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE

--------- --------- --------------------------------------------------------------------- ---------------

FIXED 57.0 WRH$_SYSMETRIC_HISTORY_INDEX - 60% INDEX

FIXED 55.0 WRH$_SYSMETRIC_HISTORY - 66% TABLE

FIXED 11.0 WRH$_SYSMETRIC_SUMMARY - 57% TABLE

FIXED 7.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11226 - 79% INDEX PARTITION

FIXED 6.0 WRH$_LATCH_PK.WRH$_LATCH_4255065810_11226 - 64% INDEX PARTITION

FIXED 6.0 WRH$_SYSSTAT.WRH$_SYSSTA_4255065810_11226 - 66% TABLE PARTITION

FIXED 6.0 WRH$_LATCH.WRH$_LATCH_4255065810_11226 - 71% TABLE PARTITION

FIXED 5.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11202 - 78% INDEX PARTITION

FIXED 5.0 WRH$_SYSMETRIC_SUMMARY_INDEX - 61% INDEX

FIXED 4.0 WRH$_SYSSTAT.WRH$_SYSSTA_4255065810_11082 - 52% TABLE PARTITION

FIXED 4.0 WRH$_RSRC_CONSUMER_GROUP - 44% TABLE

FIXED 4.0 WRH$_PARAMETER.WRH$_PARAME_4255065810_11106 - 25% TABLE PARTITION

FIXED 4.0 WRH$_LATCH_PK.WRH$_LATCH_4255065810_11202 - 70% INDEX PARTITION

FIXED 4.0 WRH$_LATCH.WRH$_LATCH_4255065810_11082 - 56% TABLE PARTITION

FIXED 4.0 WRH$_SYSSTAT.WRH$_SYSSTA_4255065810_11154 - 52% TABLE PARTITION

FIXED 4.0 WRH$_SYSSTAT.WRH$_SYSSTA_4255065810_11272 - 60% TABLE PARTITION

FIXED 4.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11082 - 78% INDEX PARTITION

FIXED 4.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11106 - 79% INDEX PARTITION

FIXED 4.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11130 - 79% INDEX PARTITION

FIXED 4.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11154 - 79% INDEX PARTITION

FIXED 4.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11178 - 79% INDEX PARTITION

FIXED 4.0 WRH$_SYSSTAT_PK.WRH$_SYSSTA_4255065810_11272 - 77% INDEX PARTITION

FIXED 4.0 WRM$_SNAPSHOT_DETAILS - 56% TABLE

FIXED 4.0 WRH$_SYSSTAT.WRH$_SYSSTA_4255065810_11202 - 52% TABLE PARTITION

FIXED 3.0 WRH$_LATCH.WRH$_LATCH_4255065810_11130 - 74% TABLE PARTITION

FIXED 3.0 WRH$_LATCH.WRH$_LATCH_4255065810_11154 - 74% TABLE PARTITION

FIXED 3.0 WRH$_LATCH.WRH$_LATCH_4255065810_11178 - 74% TABLE PARTITION

FIXED 3.0 WRH$_LATCH.WRH$_LATCH_4255065810_11202 - 74% TABLE PARTITION

FIXED 3.0 WRH$_LATCH.WRH$_LATCH_4255065810_11272 - 86% TABLE PARTITION

FIXED 3.0 WRH$_LATCH.WRH$_LATCH_4255065810_11106 - 74% TABLE PARTITION

EVENTS 11.0 WRH$_BG_EVENT_SUMMARY - 42% TABLE

EVENTS 8.0 WRH$_BG_EVENT_SUMMARY_PK - 56% INDEX

EVENTS 7.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11226 - 72% INDEX PARTITION

EVENTS 6.0 WRH$_ENQUEUE_STAT - 51% TABLE

EVENTS 6.0 WRH$_ENQUEUE_STAT_PK - 49% INDEX

EVENTS 6.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11202 - 65% INDEX PARTITION

EVENTS 5.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11226 - 68% TABLE PARTITION

EVENTS 5.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11154 - 66% INDEX PARTITION

EVENTS 5.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11178 - 61% INDEX PARTITION

EVENTS 5.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11272 - 68% INDEX PARTITION

EVENTS 4.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11130 - 66% INDEX PARTITION

EVENTS 4.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11106 - 48% TABLE PARTITION

EVENTS 4.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11178 - 52% TABLE PARTITION

EVENTS 4.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11272 - 53% TABLE PARTITION

EVENTS 4.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11082 - 67% INDEX PARTITION

EVENTS 4.0 WRH$_EVENT_HISTOGRAM_PK.WRH$_EVENT__4255065810_11106 - 78% INDEX PARTITION

EVENTS 3.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11154 - 67% TABLE PARTITION

COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE

--------- --------- --------------------------------------------------------------------- ---------------

EVENTS 3.0 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_4255065810_11226 - 78% INDEX PARTITION

EVENTS 3.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11202 - 69% TABLE PARTITION

EVENTS 3.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11130 - 65% TABLE PARTITION

EVENTS 3.0 WRH$_EVENT_HISTOGRAM.WRH$_EVENT__4255065810_11082 - 61% TABLE PARTITION

EVENTS 2.0 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_4255065810_11226 - 64% TABLE PARTITION

EVENTS 2.0 WRH$_LIBRARYCACHE - 48% TABLE

EVENTS 2.0 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_4255065810_11272 - 51% INDEX PARTITION

EVENTS 2.0 WRH$_LIBRARYCACHE_PK - 37% INDEX

EVENTS 1.0 WRH$_SYSTEM_EVENT.WRH$_SYSTEM_4255065810_11272 - 77% TABLE PARTITION

EVENTS 0.9 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_4255065810_11130 - 66% INDEX PARTITION

EVENTS 0.9 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_4255065810_11106 - 67% INDEX PARTITION

EVENTS 0.9 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_4255065810_11082 - 66% INDEX PARTITION

EVENTS 0.9 WRH$_SYSTEM_EVENT_PK.WRH$_SYSTEM_4255065810_11154 - 67% INDEX PARTITION

SQLPLAN 45.0 WRH$_SQL_PLAN - 40% TABLE

SQLPLAN 22.0 WRH$_SQL_PLAN_PK - 28% INDEX

SQL 4.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11202 - 36% TABLE PARTITION

SQL 4.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11082 - 33% TABLE PARTITION

SQL 3.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11130 - 45% TABLE PARTITION

SQL 3.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11178 - 47% TABLE PARTITION

SQL 3.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11226 - 83% TABLE PARTITION

SQL 2.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11154 - 68% TABLE PARTITION

SQL 2.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11106 - 61% TABLE PARTITION

SQL 2.0 WRH$_SQLSTAT.WRH$_SQLSTA_4255065810_11272 - 68% TABLE PARTITION

SQL 0.8 WRH$_SQLSTAT_PK.WRH$_SQLSTA_4255065810_11226 - 61% INDEX PARTITION

SQL 0.6 WRH$_SQLSTAT_INDEX.WRH$_SQLSTA_4255065810_11226 - 70% INDEX PARTITION

SQL 0.5 WRH$_SQL_WORKAREA_HIST_PK - 50% INDEX

SQL 0.5 WRH$_SQLSTAT_PK.WRH$_SQLSTA_4255065810_11154 - 61% INDEX PARTITION

SQL 0.5 WRH$_SQLSTAT_PK.WRH$_SQLSTA_4255065810_11178 - 63% INDEX PARTITION

SQL 0.5 WRH$_SQLSTAT_PK.WRH$_SQLSTA_4255065810_11202 - 63% INDEX PARTITION

SPACE 2.0 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11226 - 62% TABLE PARTITION

SPACE 1.0 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11272 - 78% TABLE PARTITION

SPACE 0.9 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11082 - 78% TABLE PARTITION

SPACE 0.9 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11106 - 76% TABLE PARTITION

SPACE 0.9 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11130 - 78% TABLE PARTITION

SPACE 0.9 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11154 - 74% TABLE PARTITION

SPACE 0.9 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11178 - 78% TABLE PARTITION

SPACE 0.9 WRH$_SEG_STAT.WRH$_SEG_ST_4255065810_11202 - 78% TABLE PARTITION

SPACE 0.8 WRH$_SEG_STAT_PK.WRH$_SEG_ST_4255065810_11226 - 64% INDEX PARTITION

SPACE 0.8 WRH$_IOSTAT_FILETYPE - 64% TABLE

SPACE 0.6 WRH$_SEG_STAT_PK.WRH$_SEG_ST_4255065810_11130 - 65% INDEX PARTITION

SPACE 0.6 WRH$_SEG_STAT_PK.WRH$_SEG_ST_4255065810_11272 - 65% INDEX PARTITION

SPACE 0.5 WRH$_SEG_STAT_OBJ_PK - 24% INDEX

SPACE 0.5 WRH$_SEG_STAT_PK.WRH$_SEG_ST_4255065810_11082 - 65% INDEX PARTITION

SPACE 0.5 WRH$_SEG_STAT_PK.WRH$_SEG_ST_4255065810_11178 - 66% INDEX PARTITION

SPACE 0.5 WRH$_SEG_STAT_PK.WRH$_SEG_ST_4255065810_11202 - 64% INDEX PARTITION

ASH 2.0 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11226 - 57% TABLE PARTITION

ASH 0.9 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11272 - 69% TABLE PARTITION

COMPONENT MB SEGMENT_NAME - % SPACE_USED SEGMENT_TYPE

--------- --------- --------------------------------------------------------------------- ---------------

ASH 0.9 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11154 - 74% TABLE PARTITION

ASH 0.9 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11202 - 76% TABLE PARTITION

ASH 0.8 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11130 - 76% TABLE PARTITION

ASH 0.8 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11178 - 82% TABLE PARTITION

ASH 0.7 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11082 - 71% TABLE PARTITION

ASH 0.7 WRH$_ACTIVE_SESSION_HISTORY.WRH$_ACTIVE_4255065810_11106 - 72% TABLE PARTITION

SQLBIND 6.0 WRH$_SQL_BIND_METADATA_PK - 27% INDEX

SQLBIND 4.0 WRH$_SQL_BIND_METADATA - 28% TABLE

RAC 0.8 WRH$_DLM_MISC.WRH$_DLM_MI_4255065810_11226 - 87% TABLE PARTITION

RAC 0.8 WRH$_DLM_MISC_PK.WRH$_DLM_MI_4255065810_11226 - 58% INDEX PARTITION

RAC 0.6 WRH$_DLM_MISC.WRH$_DLM_MI_4255065810_11272 - 78% TABLE PARTITION

RAC 0.6 WRH$_DLM_MISC_PK.WRH$_DLM_MI_4255065810_11272 - 61% INDEX PARTITION

RAC 0.5 WRH$_DLM_MISC.WRH$_DLM_MI_4255065810_11154 - 76% TABLE PARTITION

SQLTEXT 5.0 WRH$_SQLTEXT - 35% TABLE

**********************************

(4) Space usage by non-AWR components (> 500K)

**********************************

COMPONENT MB SEGMENT_NAME SEGMENT_TYPE

--------- --------- --------------------------------------------------------------------- ---------------

NON_AWR 168.0 SYS.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX

NON_AWR 96.0 SYS.I_WRI$_OPTSTAT_H_ST INDEX

NON_AWR 94.1 SYS.SYS_LOB0000008312C00038$$ LOBSEGMENT

NON_AWR 51.2 MDSYS.SYS_LOB0000083702C00006$$ LOBSEGMENT

NON_AWR 42.2 SYS.SYS_LOB0000006421C00005$$ LOBSEGMENT

NON_AWR 42.1 SYS.SYS_LOB0000010986C00001$$ LOBSEGMENT

NON_AWR 34.2 SYS.SYS_LOB0000008304C00004$$ LOBSEGMENT

NON_AWR 24.0 SYS.HEATMAP SYSTEM STATISTI

NON_AWR 23.2 APEX_040200.SYS_LOB0000088338C00007$$ LOBSEGMENT

NON_AWR 19.0 APEX_040200.WWV_FLOW_PAGE_PLUGS TABLE

NON_AWR 17.2 SYS.SYS_LOB0000085557C00004$$ LOBSEGMENT

NON_AWR 15.0 SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX

NON_AWR 14.1 SYS.SYS_LOB0000007777C00040$$ LOBSEGMENT

NON_AWR 11.0 APEX_040200.WWV_FLOW_STEP_ITEMS TABLE

NON_AWR 10.0 SYS.WRI$_ADV_SQLT_PLANS TABLE

NON_AWR 10.0 APEX_040200.WWV_FLOW_TEMPLATES TABLE

NON_AWR 9.2 SYS.SYS_LOB0000008106C00008$$ LOBSEGMENT

NON_AWR 9.2 APEX_040200.SYS_LOB0000088569C00009$$ LOBSEGMENT

NON_AWR 9.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10618 TABLE PARTITION

NON_AWR 9.0 MDSYS.SDO_CS_SRS TABLE

NON_AWR 9.0 SYS.WRI$_ADV_PARAMETERS_PK INDEX

NON_AWR 9.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10181 TABLE PARTITION

NON_AWR 8.1 APEX_040200.SYS_LOB0000087756C00016$$ LOBSEGMENT

NON_AWR 8.0 SYS.WRI$_OPTSTAT_OPR_TASKS TABLE

NON_AWR 8.0 SYS.I_WRI$_OPTSTAT_HH_ST INDEX

NON_AWR 8.0 SYS.WRI$_ADV_MESSAGE_GROUPS_PK INDEX

NON_AWR 8.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10433 TABLE PARTITION

NON_AWR 8.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10526 TABLE PARTITION

NON_AWR 8.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10261 TABLE PARTITION

NON_AWR 8.0 SYS.SCHEDULER$_EVENT_LOG TABLE

NON_AWR 7.2 SYS.SYS_LOB0000085542C00004$$ LOBSEGMENT

NON_AWR 7.0 SYS.WRI$_ADV_PARAMETERS TABLE

NON_AWR 7.0 SYS.I_SCHEDULER_JOB_RUN_DETAILS INDEX

NON_AWR 6.0 SYS.WRI$_ADV_MESSAGE_GROUPS TABLE

NON_AWR 6.0 APEX_040200.WWV_FLOW_STEP_PROCESSING TABLE

NON_AWR 6.0 SYS.SCHEDULER$_INSTANCE_PK INDEX

NON_AWR 6.0 SYS.WRI$_ADV_MSG_GRPS_IDX_01 INDEX

NON_AWR 6.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10365 TABLE PARTITION

NON_AWR 6.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10167 TABLE PARTITION

NON_AWR 6.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10287 TABLE PARTITION

NON_AWR 5.2 APEX_040200.SYS_LOB0000087810C00010$$ LOBSEGMENT

NON_AWR 5.1 SYS.SYS_LOB0000006421C00004$$ LOBSEGMENT

NON_AWR 5.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10505 TABLE PARTITION

NON_AWR 5.0 SYS.I_WRI$_OPTSTAT_OPR_TASKS_TGST INDEX

NON_AWR 4.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10613 TABLE PARTITION

COMPONENT MB SEGMENT_NAME SEGMENT_TYPE

--------- --------- --------------------------------------------------------------------- ---------------

NON_AWR 4.0 APEX_040200.WWV_FLOW_REGION_REPORT_COLUMN TABLE

NON_AWR 4.0 APEX_040200.WWV_FLOW_STEPS TABLE

NON_AWR 4.0 SYSTEM.SYS_LOB0000001340C00009$$ LOBSEGMENT

NON_AWR 4.0 SYS.I_WRI$_OPTSTAT_OPR_TASKS_STIME INDEX

NON_AWR 3.2 SYS.SYS_LOB0000085537C00004$$ LOBSEGMENT

NON_AWR 3.2 APEX_040200.SYS_LOB0000088421C00019$$ LOBSEGMENT

NON_AWR 3.2 SYS.SYS_LOB0000085547C00004$$ LOBSEGMENT

NON_AWR 3.1 APEX_040200.SYS_LOB0000087667C00011$$ LOBSEGMENT

NON_AWR 3.0 SYS.WRI$_OPTSTAT_TAB_HISTORY TABLE

NON_AWR 3.0 APEX_040200.WWV_FLOW_WORKSHEET_COLUMNS TABLE

NON_AWR 3.0 APEX_040200.WWV_FLOW_INSTALL_SCRIPTS TABLE

NON_AWR 3.0 APEX_040200.WWV_FLOW_PAGE_PLUG_TEMPLATES TABLE

NON_AWR 3.0 APEX_040200.WWV_FLOW_STEP_BUTTONS TABLE

NON_AWR 3.0 SYS.I_WRI$_OPTSTAT_OPR_TASKS_OPOBJ INDEX

NON_AWR 3.0 SYS.I_WRI$_OPTSTAT_OPR_TASKS_OPID INDEX

NON_AWR 3.0 SYS.SMON_SCN_TO_TIME_AUX CLUSTER

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY.SYS_P10615 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10577 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10357 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10173 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10573 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10373 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10454 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10198 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10253 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10482 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10185 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10493 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10213 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10501 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10278 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10487 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10535 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10295 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10509 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10333 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10544 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_OPTSTAT_HISTGRM_HISTORY.SYS_P10313 TABLE PARTITION

NON_AWR 3.0 SYS.WRI$_ADV_SQLT_PLAN_STATS TABLE

NON_AWR 3.0 SYS.WRI$_ADV_RATIONALE TABLE

NON_AWR 2.2 SYS.SYS_LOB0000085552C00004$$ LOBSEGMENT

NON_AWR 2.2 APEX_040200.SYS_LOB0000088585C00007$$ LOBSEGMENT

NON_AWR 2.2 APEX_040200.SYS_LOB0000087738C00028$$ LOBSEGMENT

NON_AWR 2.2 APEX_040200.SYS_LOB0000087738C00022$$ LOBSEGMENT

NON_AWR 2.2 SYS.SYS_LOB0000085562C00004$$ LOBSEGMENT

NON_AWR 2.1 SYS.SYS_LOB0000007741C00014$$ LOBSEGMENT

NON_AWR 2.1 AUDSYS.SYS_LOB0000091784C00014$$.SYS_LOB_P186 LOB PARTITION

COMPONENT MB SEGMENT_NAME SEGMENT_TYPE

--------- --------- --------------------------------------------------------------------- ---------------

NON_AWR 2.1 SYS.SYS_LOB0000006450C00009$$.SYS_LOB_P10621 LOB PARTITION

NON_AWR 2.1 SYS.SYS_LOB0000006450C00009$$.SYS_LOB_P10558 LOB PARTITION

NON_AWR 2.1 SYS.SYS_LOB0000006450C00009$$.SYS_LOB_P10548 LOB PARTITION

NON_AWR 2.1 SYS.SYS_LOB0000006450C00009$$.SYS_LOB_P10553 LOB PARTITION

NON_AWR 2.0 SYS.WRI$_OPTSTAT_IND_HISTORY TABLE

NON_AWR 2.0 SYS.LTXID_TRANS.LTXID_TRANS_2 TABLE PARTITION

NON_AWR 2.0 SYS.SCHEDULER$_JOB_RUN_DETAILS TABLE

NON_AWR 2.0 SYS.WRI$_SEGADV_OBJLIST TABLE

**********************************

(5a) AWR snapshots - last 50

**********************************

Total snapshots in DB 4255065810 Instance 2 = 211

Total snapshots in DB 4255065810 Instance 1 = 211

DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT

---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------

4255065810 11250 1 +00000 00:00:01.1 11:00:19 (01/09) 07:34:38 (01/09) 0 0

4255065810 11250 2 +00000 00:00:00.6 11:00:20 (01/09) 07:34:07 (01/09) 0 0

4255065810 11251 1 +00000 00:00:00.6 12:00:23 (01/09) 07:34:38 (01/09) 0 0

4255065810 11251 2 +00000 00:00:00.2 12:00:23 (01/09) 07:34:07 (01/09) 0 0

4255065810 11252 1 +00000 00:00:00.6 13:00:08 (01/09) 07:34:38 (01/09) 0 0

4255065810 11252 2 +00000 00:00:01.1 13:00:08 (01/09) 07:34:07 (01/09) 0 0

4255065810 11253 1 +00000 00:00:00.2 14:00:11 (01/09) 07:34:38 (01/09) 0 0

4255065810 11253 2 +00000 00:00:00.8 14:00:12 (01/09) 07:34:07 (01/09) 0 0

4255065810 11254 1 +00000 00:00:00.6 15:00:36 (01/09) 07:34:38 (01/09) 0 0

4255065810 11254 2 +00000 00:00:00.1 15:00:36 (01/09) 07:34:07 (01/09) 0 0

4255065810 11255 1 +00000 00:00:00.8 16:00:21 (01/09) 07:34:38 (01/09) 0 0

4255065810 11255 2 +00000 00:00:01.1 16:00:21 (01/09) 07:34:07 (01/09) 0 0

4255065810 11256 1 +00000 00:00:01.1 17:00:23 (01/09) 07:34:38 (01/09) 0 0

4255065810 11256 2 +00000 00:00:00.5 17:00:24 (01/09) 07:34:07 (01/09) 0 0

4255065810 11257 1 +00000 00:00:00.5 18:00:08 (01/09) 07:34:38 (01/09) 0 0

4255065810 11257 2 +00000 00:00:00.8 18:00:09 (01/09) 07:34:07 (01/09) 0 0

4255065810 11258 1 +00000 00:00:00.3 19:00:11 (01/09) 07:34:38 (01/09) 0 0

4255065810 11258 2 +00000 00:00:00.6 19:00:12 (01/09) 07:34:07 (01/09) 0 0

4255065810 11259 1 +00000 00:00:00.6 20:00:36 (01/09) 07:34:38 (01/09) 0 0

4255065810 11259 2 +00000 00:00:00.9 20:00:37 (01/09) 07:34:07 (01/09) 0 0

4255065810 11260 1 +00000 00:00:00.6 21:00:21 (01/09) 07:34:38 (01/09) 0 0

4255065810 11260 2 +00000 00:00:00.8 21:00:22 (01/09) 07:34:07 (01/09) 0 0

4255065810 11261 1 +00000 00:00:00.2 22:00:24 (01/09) 07:34:38 (01/09) 0 0

4255065810 11261 2 +00000 00:00:00.4 22:00:25 (01/09) 07:34:07 (01/09) 0 0

4255065810 11262 1 +00000 00:00:00.1 23:00:09 (01/09) 07:34:38 (01/09) 0 0

4255065810 11262 2 +00000 00:00:00.2 23:00:10 (01/09) 07:34:07 (01/09) 0 0

4255065810 11263 1 +00000 00:00:00.1 00:00:12 (01/10) 07:34:38 (01/09) 0 0

4255065810 11263 2 +00000 00:00:00.1 00:00:13 (01/10) 07:34:07 (01/09) 0 0

4255065810 11264 1 +00000 00:00:01.0 01:00:38 (01/10) 07:34:38 (01/09) 0 0

4255065810 11264 2 +00000 00:00:00.1 01:00:37 (01/10) 07:34:07 (01/09) 0 0

4255065810 11265 1 +00000 00:00:00.7 02:00:01 (01/10) 07:34:38 (01/09) 0 0

4255065810 11265 2 +00000 00:00:00.7 02:00:01 (01/10) 07:34:07 (01/09) 0 0

4255065810 11266 1 +00000 00:00:00.8 03:00:26 (01/10) 07:34:38 (01/09) 0 0

4255065810 11266 2 +00000 00:00:00.7 03:00:26 (01/10) 07:34:07 (01/09) 0 0

4255065810 11267 1 +00000 00:00:00.7 04:00:11 (01/10) 07:34:38 (01/09) 0 0

4255065810 11267 2 +00000 00:00:00.4 04:00:11 (01/10) 07:34:07 (01/09) 0 0

4255065810 11268 1 +00000 00:00:00.3 05:00:14 (01/10) 07:34:38 (01/09) 0 0

4255065810 11268 2 +00000 00:00:00.3 05:00:14 (01/10) 07:34:07 (01/09) 0 0

4255065810 11269 1 +00000 00:00:01.0 06:00:38 (01/10) 07:34:38 (01/09) 0 0

4255065810 11269 2 +00000 00:00:00.9 06:00:39 (01/10) 07:34:07 (01/09) 0 0

4255065810 11270 1 +00000 00:00:00.9 07:00:02 (01/10) 07:34:38 (01/09) 0 0

4255065810 11270 2 +00000 00:00:00.8 07:00:02 (01/10) 07:34:07 (01/09) 0 0

4255065810 11271 1 +00000 00:00:00.1 08:00:26 (01/10) 07:34:38 (01/09) 0 0

4255065810 11271 2 +00000 00:00:00.9 08:00:27 (01/10) 07:34:07 (01/09) 0 0

4255065810 11272 1 +00000 00:00:01.1 09:00:11 (01/10) 07:34:38 (01/09) 0 0

4255065810 11272 2 +00000 00:00:00.8 09:00:12 (01/10) 07:34:07 (01/09) 0 0

4255065810 11273 1 +00000 00:00:00.6 10:00:15 (01/10) 07:34:38 (01/09) 0 0

DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT

---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------

4255065810 11273 2 +00000 00:00:00.4 10:00:15 (01/10) 07:34:07 (01/09) 0 0

4255065810 11274 1 +00000 00:00:00.6 11:00:00 (01/10) 07:34:38 (01/09) 0 0

4255065810 11274 2 +00000 00:00:01.2 11:00:00 (01/10) 07:34:07 (01/09) 0 0

4255065810 11275 1 +00000 00:00:00.3 12:00:03 (01/10) 07:34:38 (01/09) 0 0

4255065810 11275 2 +00000 00:00:00.9 12:00:04 (01/10) 07:34:07 (01/09) 0 0

4255065810 11276 1 +00000 00:00:00.6 13:00:27 (01/10) 07:34:38 (01/09) 0 0

4255065810 11276 2 +00000 00:00:01.2 13:00:27 (01/10) 07:34:07 (01/09) 0 0

4255065810 11277 1 +00000 00:00:00.7 14:00:12 (01/10) 07:34:38 (01/09) 0 0

4255065810 11277 2 +00000 00:00:01.2 14:00:12 (01/10) 07:34:07 (01/09) 0 0

4255065810 11278 1 +00000 00:00:00.4 15:00:15 (01/10) 07:34:38 (01/09) 0 0

4255065810 11278 2 +00000 00:00:01.0 15:00:16 (01/10) 07:34:07 (01/09) 0 0

4255065810 11279 1 +00000 00:00:00.5 16:00:40 (01/10) 07:34:38 (01/09) 0 0

4255065810 11279 2 +00000 00:00:01.0 16:00:40 (01/10) 07:34:07 (01/09) 0 0

4255065810 11280 1 +00000 00:00:01.1 17:00:03 (01/10) 07:34:38 (01/09) 0 0

4255065810 11280 2 +00000 00:00:00.5 17:00:04 (01/10) 07:34:07 (01/09) 0 0

4255065810 11281 1 +00000 00:00:00.2 18:00:28 (01/10) 07:34:38 (01/09) 0 0

4255065810 11281 2 +00000 00:00:00.6 18:00:29 (01/10) 07:34:07 (01/09) 0 0

4255065810 11282 1 +00000 00:00:00.3 19:00:13 (01/10) 07:34:38 (01/09) 0 0

4255065810 11282 2 +00000 00:00:00.7 19:00:14 (01/10) 07:34:07 (01/09) 0 0

4255065810 11283 1 +00000 00:00:00.9 20:00:17 (01/10) 07:34:38 (01/09) 0 0

4255065810 11283 2 +00000 00:00:00.3 20:00:17 (01/10) 07:34:07 (01/09) 0 0

4255065810 11284 1 +00000 00:00:00.1 21:00:02 (01/10) 07:34:38 (01/09) 0 0

4255065810 11284 2 +00000 00:00:01.1 21:00:02 (01/10) 07:34:07 (01/09) 0 0

4255065810 11285 1 +00000 00:00:00.6 22:00:05 (01/10) 07:34:38 (01/09) 0 0

4255065810 11285 2 +00000 00:00:00.9 22:00:06 (01/10) 07:34:07 (01/09) 0 0

4255065810 11286 1 +00000 00:00:00.7 23:00:30 (01/10) 07:34:38 (01/09) 0 0

4255065810 11286 2 +00000 00:00:00.9 23:00:31 (01/10) 07:34:07 (01/09) 0 0

4255065810 11287 1 +00000 00:00:00.1 00:00:16 (01/11) 07:34:38 (01/09) 0 0

4255065810 11287 2 +00000 00:00:00.8 00:00:16 (01/11) 07:34:07 (01/09) 0 0

4255065810 11288 1 +00000 00:00:00.8 01:00:19 (01/11) 07:34:38 (01/09) 0 0

4255065810 11288 2 +00000 00:00:00.8 01:00:19 (01/11) 07:34:07 (01/09) 0 0

4255065810 11289 1 +00000 00:00:00.1 02:00:04 (01/11) 07:34:38 (01/09) 0 0

4255065810 11289 2 +00000 00:00:00.8 02:00:04 (01/11) 07:34:07 (01/09) 0 0

4255065810 11290 1 +00000 00:00:00.7 03:00:07 (01/11) 07:34:38 (01/09) 0 0

4255065810 11290 2 +00000 00:00:00.7 03:00:07 (01/11) 07:34:07 (01/09) 0 0

4255065810 11291 1 +00000 00:00:00.8 04:00:32 (01/11) 07:34:38 (01/09) 0 0

4255065810 11291 2 +00000 00:00:00.7 04:00:32 (01/11) 07:34:07 (01/09) 0 0

4255065810 11292 1 +00000 00:00:00.7 05:00:17 (01/11) 07:34:38 (01/09) 0 0

4255065810 11292 2 +00000 00:00:00.6 05:00:17 (01/11) 07:34:07 (01/09) 0 0

4255065810 11293 1 +00000 00:00:01.0 06:00:20 (01/11) 07:34:38 (01/09) 0 0

4255065810 11293 2 +00000 00:00:00.8 06:00:20 (01/11) 07:34:07 (01/09) 0 0

4255065810 11294 1 +00000 00:00:00.5 07:00:04 (01/11) 07:34:38 (01/09) 0 0

4255065810 11294 2 +00000 00:00:00.3 07:00:04 (01/11) 07:34:07 (01/09) 0 0

4255065810 11295 1 +00000 00:00:00.1 08:00:06 (01/11) 07:34:38 (01/09) 0 0

4255065810 11295 2 +00000 00:00:00.9 08:00:07 (01/11) 07:34:07 (01/09) 0 0

4255065810 11296 1 +00000 00:00:00.9 09:00:31 (01/11) 07:34:38 (01/09) 0 0

4255065810 11296 2 +00000 00:00:00.6 09:00:31 (01/11) 07:34:07 (01/09) 0 0

DBID SNAP_ID INST FLUSH_ELAPSED ENDTM STARTUP_TIME STATUS ERRCNT

---------- ---------- ----- -------------------- ----------------- ----------------- ------ ------

4255065810 11297 1 +00000 00:00:00.4 10:00:16 (01/11) 07:34:38 (01/09) 0 0

4255065810 11297 2 +00000 00:00:01.1 10:00:16 (01/11) 07:34:07 (01/09) 0 0

4255065810 11298 1 +00000 00:00:00.4 11:00:13 (01/11) 07:34:38 (01/09) 0 0

4255065810 11298 2 +00000 00:00:01.0 11:00:13 (01/11) 07:34:07 (01/09) 0 0

4255065810 11299 1 +00000 00:00:00.1 12:00:05 (01/11) 07:34:38 (01/09) 0 0

4255065810 11299 2 +00000 00:00:00.5 12:00:05 (01/11) 07:34:07 (01/09) 0 0

**********************************

(5b) AWR snapshots with errors or invalid

**********************************

no rows selected

**********************************

(5c) AWR snapshots -- OLDEST Non-Baselined snapshots

**********************************

DBID INST SNAP_ID ENDTM STATUS ERROR_COUNT

---------- ----- ---------- ----------------- ------ -----------

4255065810 1 11089 09:00:12 (01/02) 0 0

**********************************

(6) AWR Control Settings - interval, retention

**********************************

DBID LSNAPID LSPLITID LSNAPTIME LPURGETIME FLAG INTERVAL RETENTION VRSN

----------- -------- -------- -------------- -------------- ----- ----------------- ----------------- ----

4255065810 11299 11272 01/11 12:00:06 01/10 08:05:00 2 +00000 01:00:00.0 +00008 00:00:00.0 11

**********************************

(7a) AWR Contents - row counts for each snapshots

**********************************

SNAP_ID INST ASH SQL SQBND FILES SEGST SYSEVT

---------- ----- ---------- ---------- ---------- ---------- ---------- ----------

11275 1 32 70 277 8 101 242

11275 2 43 90 221 8 104 267

11276 1 37 72 544 8 99 242

11276 2 66 102 547 8 104 267

11277 1 40 75 240 8 100 242

11277 2 37 67 182 8 105 267

11278 1 32 71 250 8 99 242

11278 2 44 81 751 8 102 267

11279 1 36 69 245 8 97 242

11279 2 35 72 488 8 114 268

11280 1 41 68 238 8 90 242

11280 2 38 75 194 8 110 268

11281 1 33 69 243 8 96 242

11281 2 47 94 168 8 105 268

11282 1 34 66 521 8 101 242

11282 2 61 99 1344 8 102 268

11283 1 37 72 258 8 101 242

11283 2 33 70 499 8 112 269

11284 1 33 69 247 8 103 243

11284 2 42 69 247 8 110 269

11285 1 36 74 274 8 99 244

11285 2 37 79 527 8 111 269

11286 1 30 70 237 8 98 244

11286 2 32 89 950 8 102 269

11287 1 33 66 242 8 103 244

11287 2 41 96 151 8 109 269

11288 1 27 69 565 8 98 244

11288 2 63 100 540 8 113 269

11289 1 35 72 255 8 103 244

11289 2 38 86 362 8 111 269

11290 1 24 64 250 8 99 244

11290 2 38 83 489 8 112 269

11291 1 26 65 240 8 98 244

11291 2 40 79 144 8 113 269

11292 1 40 64 237 8 102 244

11292 2 44 77 431 8 110 269

11293 1 36 68 263 8 96 244

11293 2 36 100 207 8 109 269

11294 1 115 75 960 8 100 244

11294 2 78 103 548 8 108 269

11295 1 87 77 672 8 103 244

11295 2 44 85 605 8 109 269

11296 1 47 70 283 8 95 244

11296 2 53 81 781 8 105 269

11297 1 26 64 238 8 100 244

11297 2 54 83 525 8 110 269

11298 1 32 70 261 8 98 244

SNAP_ID INST ASH SQL SQBND FILES SEGST SYSEVT

---------- ----- ---------- ---------- ---------- ---------- ---------- ----------

11298 2 70 80 453 8 111 269

11299 1 35 67 244 8 104 244

11299 2 58 103 1074 8 115 269

**********************************

(7b) AWR Contents - average row counts per snapshot

**********************************

SNAP_COUNT INST ASH SQLSTAT SQLBIND FILES SEGSTAT SYSEVENT

---------- ----- ---------- ---------- ---------- ---------- ---------- ----------

211 1 35.73 73.31 360.45 8 98.32 249.05

211 2 53.12 97.62 581.24 8 108.15 273.06

**********************************

(7c) AWR total item counts - names, text, plans

**********************************

SQLTEXT SQLPLAN SQLBMETA SEGOBJ DATAFILE TEMPFILE

---------- ---------- ---------- ---------- ---------- ----------

1355 64775 9153 1765 8 1

########################################################

(II) Advisor Framework Info

########################################################

**********************************

(1) Advisor Tasks - Last 50

**********************************

OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS

-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------

SYS/ADDM 34834/ADDM:4255065810_2_11283 20:00:18 (01/10) 0 0 AUTO COMPLETED

SYS/ADDM 34825/ADDM:4255065810_1_11283 20:00:18 (01/10) 0 0 AUTO COMPLETED

SYS/ADDM 34835/ADDM:4255065810_11283 20:00:18 (01/10) 0 0 AUTO COMPLETED

SYS/ADDM 34827/ADDM:4255065810_11284 21:00:03 (01/10) 0 0 AUTO COMPLETED

SYS/ADDM 34826/ADDM:4255065810_1_11284 21:00:03 (01/10) 0 0 AUTO COMPLETED

SYS/ADDM 34836/ADDM:4255065810_2_11284 21:00:04 (01/10) 0 0 AUTO COMPLETED

SYS/ADDM 34838/ADDM:4255065810_1_11285 22:00:06 (01/10) 0 0 AUTO COMPLETED

SYS/ADDM 34837/ADDM:4255065810_2_11285 22:00:07 (01/10) 0 0 AUTO COMPLETED

SYS/ADDM 34848/ADDM:4255065810_11285 22:00:07 (01/10) 0 0 AUTO COMPLETED

SYS/Segment Ad 34849/SYS_AUTO_SPCADV_0412221001 22:12:04 (01/10) 1 1 CMD COMPLETED

2021

SYS/ADDM 34839/ADDM:4255065810_1_11286 23:00:31 (01/10) 0 0 AUTO COMPLETED

SYS/ADDM 34851/ADDM:4255065810_11286 23:00:32 (01/10) 0 0 AUTO COMPLETED

SYS/ADDM 34850/ADDM:4255065810_2_11286 23:00:32 (01/10) 0 0 AUTO COMPLETED

SYS/ADDM 34840/ADDM:4255065810_1_11287 00:00:17 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34852/ADDM:4255065810_2_11287 00:00:17 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34841/ADDM:4255065810_11287 00:00:17 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34853/ADDM:4255065810_2_11288 01:00:20 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34842/ADDM:4255065810_1_11288 01:00:20 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34854/ADDM:4255065810_11288 01:00:20 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34855/ADDM:4255065810_2_11289 02:00:05 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34844/ADDM:4255065810_11289 02:00:05 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34843/ADDM:4255065810_1_11289 02:00:05 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34857/ADDM:4255065810_11290 03:00:08 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34845/ADDM:4255065810_1_11290 03:00:08 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34856/ADDM:4255065810_2_11290 03:00:08 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34858/ADDM:4255065810_2_11291 04:00:33 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34846/ADDM:4255065810_1_11291 04:00:33 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34859/ADDM:4255065810_11291 04:00:33 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34860/ADDM:4255065810_2_11292 05:00:18 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34847/ADDM:4255065810_1_11292 05:00:18 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34868/ADDM:4255065810_11292 05:00:18 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34861/ADDM:4255065810_2_11293 06:00:21 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34862/ADDM:4255065810_11293 06:00:21 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34869/ADDM:4255065810_1_11293 06:00:21 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34870/ADDM:4255065810_1_11294 07:00:05 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34863/ADDM:4255065810_2_11294 07:00:05 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34871/ADDM:4255065810_11294 07:00:05 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34865/ADDM:4255065810_11295 08:00:08 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34872/ADDM:4255065810_1_11295 08:00:08 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34864/ADDM:4255065810_2_11295 08:00:08 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34866/ADDM:4255065810_2_11296 09:00:32 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34873/ADDM:4255065810_1_11296 09:00:32 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34867/ADDM:4255065810_11296 09:00:32 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34874/ADDM:4255065810_1_11297 10:00:17 (01/11) 1 1 AUTO COMPLETED

SYS/ADDM 34875/ADDM:4255065810_11297 10:00:18 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34878/ADDM:4255065810_2_11297 10:00:18 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34876/ADDM:4255065810_1_11298 11:00:14 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34880/ADDM:4255065810_11298 11:00:15 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34879/ADDM:4255065810_2_11298 11:00:15 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34888/ADDM:4255065810_11299 12:00:06 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34881/ADDM:4255065810_2_11299 12:00:06 (01/11) 0 0 AUTO COMPLETED

SYS/ADDM 34877/ADDM:4255065810_1_11299 12:00:06 (01/11) 0 0 AUTO COMPLETED

**********************************

(2) Advisor Task - Oldest 5

**********************************

OWNER/ADVISOR TASK_ID/NAME CREATED EXE_DURATN EXE_CREATN HOW_C STATUS

-------------- -------------------------------- ---------------- ---------- ---------- ----- ------------

SYS/ADDM 32586/ADDM:4255065810_1_10561 09:00:15 (12/11) 0 0 AUTO COMPLETED

SYS/ADDM 32593/ADDM:4255065810_10561 09:00:15 (12/11) 0 0 AUTO COMPLETED

SYS/ADDM 32592/ADDM:4255065810_2_10561 09:00:15 (12/11) 0 0 AUTO COMPLETED

SYS/ADDM 32594/ADDM:4255065810_2_10562 10:00:06 (12/11) 0 0 AUTO COMPLETED

SYS/ADDM 32587/ADDM:4255065810_1_10562 10:00:07 (12/11) 0 0 AUTO COMPLETED

SYS/ADDM 32598/ADDM:4255065810_10562 10:00:07 (12/11) 0 0 AUTO COMPLETED

**********************************

(3) Advisor Tasks With Errors - Last 50

**********************************

no rows selected

########################################################

(III) ASH Usage Info

########################################################

**********************************

(1a) ASH histogram (past 3 days)

**********************************

NUM_ACTIVE_SESSIONS NUM_SAMPLES

-------------------- ------------

0000 - 0004 4,240

0005 - 0009 14

0010 - 0014 1

**********************************

(1b) ASH histogram (past 1 day)

**********************************

NUM_ACTIVE_SESSIONS NUM_SAMPLES

-------------------- ------------

0000 - 0004 1,573

0005 - 0009 6

**********************************

(2a) ASH details (past 3 days)

**********************************

INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE

---- ---------------- ---------------- ------------ ------------ ----------

2 12:59:57 (01/08) 11:55:51 (01/11) 21,559 2,992 0.14

1 12:59:55 (01/08) 11:56:27 (01/11) 21,562 2,304 0.11

**********************************

(2b) ASH details (past 1 day)

**********************************

INST MIN_TIME MAX_TIME NUM_SAMPLES NUM_ROWS AVG_ACTIVE

---- ---------------- ---------------- ------------ ------------ ----------

2 13:08:15 (01/10) 11:55:51 (01/11) 8,009 1,063 0.13

1 13:01:40 (01/10) 11:56:27 (01/11) 8,051 915 0.11

**********************************

(2c) ASH sessions (Fg Vs Bg) (past 1 day across all instances in RAC)

**********************************

Foreground % 7.7

Background % 92.3

MMNL % 0.0

End of Report

Report written to test.txt

Space consumed by the Optimizer backup statistics

SQL> SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES/1024 MB, MOVE_PROCEDURE

2 FROM V$SYSAUX_OCCUPANTS

3 WHERE OCCUPANT_NAME = 'SM/OPTSTAT';

OCCUPANT_NAME MB

---------------------------------------------------------------- ----------

MOVE_PROCEDURE

----------------------------------------------------------------

SM/OPTSTAT 12.625

Execute statement to get information about retention period of snapshots

SQL> SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;

GET_STATS_HISTORY_RETENTION

---------------------------

Set Snapshot Retention period to 7 days

SQL> exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);

PL/SQL procedure successfully completed.

It will purge old stats then current retention period

SQL> exec DBMS_STATS.PURGE_STATS(NULL);

PL/SQL procedure successfully completed.

HOME

Tuesday 12 January 2021

PT - Oracle Compression Basic and advanced table example

Monday 11 January 2021

AWR - Delete AWR Snapshots ID

AWR - Script execution's For non and RAC Database AWR report

AWR - Set AWR Retention period from 1hr to 30 minutes

AWR- Managing Space Consumed by AWR Snapshots