Dump ASH Data.

These are few ways you can look at the ASH data.

1) Generating ASH Report from Enterprise Manager
2) Querying  v$active_session_history
3) Generating ASH Report from sql ( ashrpt.sql or ashrpti.sql  in ORACLE_HOME\admin )
4) Dump ASH to a file .


The first three steps are pretty straightforward.
I would opt for first method , as it is available right there in OEM and then I can navigate to appropriate SQLs.

But , for others , we do have some options . ...

Let us see how method 4 works.

The following example dumps the ASH data for the past 5 minutes.


C:>sqlplus / as sysdba
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump ashdump 5
Statement processed.

You can use the file to load into the table via SQL Loader or External Table and analyze the ASH data.

Contents of the trace file
--------------------------

Trace file D:\APP\ORACLE\diag\rdbms\dbdev\dbdev\trace\dbdev_ora_5592.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 
CPU                 : 2 - type 8664, 2 Physical Cores
Process Affinity    : 0x0x0000000000000000
Memory (Avail/Total): Ph:1835M/3839M, Ph+PgF:5607M/7677M
Instance name: dbdev
Redo thread mounted by this instance: 1
Oracle process number: 28
Windows thread id: 5592, image: ORACLE.EXE (SHAD)


*** 2011-12-02 09:53:33.615
*** SESSION ID:(24.6023) 2011-12-02 09:53:33.615
*** CLIENT ID:() 2011-12-02 09:53:33.615
*** SERVICE NAME:(dbdev.test.edu) 2011-12-02 09:53:33.615
*** MODULE NAME:(sqlplus.exe) 2011-12-02 09:53:33.615
*** ACTION NAME:() 2011-12-02 09:53:33.615
 
Processing Oradebug command 'setmypid'

*** 2011-12-02 09:53:33.615
Oradebug command 'setmypid' console output:

*** 2011-12-02 09:53:57.881
Processing Oradebug command 'dump ashdump 5'
ASH dump
<<>>
****************
SCRIPT TO IMPORT
****************
------------------------------------------
Step 1: Create destination table
------------------------------------------
CREATE TABLE ashdump AS
SELECT * FROM SYS.WRH$_ACTIVE_SESSION_HISTORY WHERE rownum < 0
----------------------------------------------------------------
Step 2: Create the SQL*Loader control file as below
----------------------------------------------------------------
load data
infile * "str '\n####\n'"
append
into table ashdump
fields terminated by ',' optionally enclosed by '"'
(                              
SNAP_ID  CONSTANT 0           ,
DBID                          ,
INSTANCE_NUMBER               ,
SAMPLE_ID                     ,
SAMPLE_TIME TIMESTAMP ENCLOSED BY '"' AND '"' "TO_TIMESTAMP(:SAMPLE_TIME   ,'MM-DD-YYYY HH24:MI:SSXFF')"   ,
SESSION_ID                    ,
SESSION_SERIAL#               ,
SESSION_TYPE                  ,
USER_ID                       ,
SQL_ID                        ,
SQL_CHILD_NUMBER              ,
SQL_OPCODE                    ,
FORCE_MATCHING_SIGNATURE      ,
TOP_LEVEL_SQL_ID              ,
TOP_LEVEL_SQL_OPCODE          ,
SQL_PLAN_HASH_VALUE           ,
SQL_PLAN_LINE_ID              ,
SQL_PLAN_OPERATION#           ,
SQL_PLAN_OPTIONS#             ,
SQL_EXEC_ID                   ,
SQL_EXEC_START DATE 'MM/DD/YYYY HH24:MI:SS' ENCLOSED BY '"' AND '"' ":SQL_EXEC_START"   ,
PLSQL_ENTRY_OBJECT_ID         ,
PLSQL_ENTRY_SUBPROGRAM_ID     ,
PLSQL_OBJECT_ID               ,
PLSQL_SUBPROGRAM_ID           ,
QC_INSTANCE_ID                ,
QC_SESSION_ID                 ,
QC_SESSION_SERIAL#            ,
EVENT_ID                      ,
SEQ#                          ,
P1                            ,
P2                            ,
P3                            ,
WAIT_TIME                     ,
TIME_WAITED                   ,
BLOCKING_SESSION              ,
BLOCKING_SESSION_SERIAL#      ,
BLOCKING_INST_ID              ,
CURRENT_OBJ#                  ,
CURRENT_FILE#                 ,
CURRENT_BLOCK#                ,
CURRENT_ROW#                  ,
TOP_LEVEL_CALL#               ,
CONSUMER_GROUP_ID             ,
XID                           ,
REMOTE_INSTANCE#              ,
TIME_MODEL                    ,
SERVICE_HASH                  ,
PROGRAM                       ,
MODULE                        ,
ACTION                        ,
CLIENT_ID                     ,
MACHINE                       ,
PORT                          ,
ECID                           
)                              
---------------------------------------------------
Step 3: Load the ash rows dumped in this trace file
---------------------------------------------------
sqlldr userid/password control=ashldr.ctl data= errors=1000000
---------------------------------------------------
<<>>
<<>>
####
2627103816,1,27387994,"12-02-2011 09:53:47.800000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59482,100,0,0,998719,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387993,"12-02-2011 09:53:46.801000000",6,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3213517201,58536,0,1,1,0,12920,4294967295,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (CKPT)","","","","SERVER-DB02",0,""
####
2627103816,1,27387990,"12-02-2011 09:53:43.802000000",6,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,4078387448,58530,2,3,2,0,651,4294967295,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (CKPT)","","","","SERVER-DB02",0,""
####
2627103816,1,27387977,"12-02-2011 09:53:30.771000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59466,100,0,0,999755,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387959,"12-02-2011 09:53:12.731000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59447,100,0,0,999253,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387943,"12-02-2011 09:52:56.717000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59431,100,0,0,1000095,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387942,"12-02-2011 09:52:55.716000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59430,100,0,0,998594,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387926,"12-02-2011 09:52:39.717000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59414,100,0,0,997838,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387909,"12-02-2011 09:52:22.719000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59393,100,0,0,999836,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387892,"12-02-2011 09:52:05.705000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59376,100,0,0,1001355,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387875,"12-02-2011 09:51:48.707000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59359,100,0,0,1000171,0,4294967291,0,3937105842,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387858,"12-02-2011 09:51:31.708000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59343,100,0,0,999867,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387841,"12-02-2011 09:51:14.697000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59325,100,0,0,999688,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387825,"12-02-2011 09:50:58.698000000",4,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3176176482,54366,5,1,1000,999949,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (DIA0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387824,"12-02-2011 09:50:57.698000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59308,100,0,0,1000193,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387814,"12-02-2011 09:50:47.683000000",4,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3176176482,54355,5,1,1000,999911,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (DIA0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387812,"12-02-2011 09:50:45.683000000",6,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3213517201,58069,0,1,1,0,11257,4294967295,0,3306949592,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (CKPT)","","","","SERVER-DB02",0,""
####
2627103816,1,27387809,"12-02-2011 09:50:42.683000000",6,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3213517201,58065,0,15,1,0,428,4294967295,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (CKPT)","","","","SERVER-DB02",0,""
####
2627103816,1,27387807,"12-02-2011 09:50:40.683000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59292,100,0,0,1000681,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387790,"12-02-2011 09:50:23.685000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59274,100,0,0,999428,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387773,"12-02-2011 09:50:06.686000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59258,100,0,0,999501,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387756,"12-02-2011 09:49:49.672000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59241,100,0,0,999596,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387750,"12-02-2011 09:49:43.672000000",4,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,3176176482,54291,5,1,1000,1001875,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (DIA0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387739,"12-02-2011 09:49:32.659000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59225,100,0,0,1000266,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387722,"12-02-2011 09:49:15.641000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59207,100,0,0,999939,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
2627103816,1,27387706,"12-02-2011 09:48:59.624000000",126,1,2,0,"",0,0,0,"",0,0,0,0,0,0,"",0,0,0,0,0,0,0,866018717,59189,100,0,0,999878,0,4294967291,0,0,4294967295,0,0,0,0,0,,0,0,165959219,"ORACLE.EXE (PSP0)","","","","SERVER-DB02",0,""
####
<<>>

*** 2011-12-02 09:53:57.896
Oradebug command 'dump ashdump 5' console output:

MERGE statement in SQL Server

In the current releases of Oracle and MS SQL server ,we can use MERGE to ease the ETL process .

The implementation of MERGE statement in SQL Server 2008 seems little more advanced than Oracle's implementation.

Here is our use case.

a) If the record in the source table does not exist in the target table  , then add the record.
b) If the record in the source table matches with the record in the target table and there is another condition ( in our example , col3 in target table is 'N' ) ,then update the target record.
c) If the record in the target table does not exists in the source table , then delete it from the target table.

In Oracle's implementation of MERGE , the first two conditions can be easily implemented.
Here's where SQL Server's implementation of MERGE comes handy for the third condition .

Let us create tables ( t_source - for source table and t_dest - for destination table ) .


1> CREATE TABLE t_source
2>   (
3>     col1 INTEGER NOT NULL PRIMARY KEY ,
4>     col2 CHAR(30) NOT NULL ,
5>     col3 CHAR(1) NOT NULL
6>   ) ;
7> go

1>  CREATE TABLE t_dest
2>   (
3>     col1 INTEGER NOT NULL PRIMARY KEY ,
4>     col2 CHAR(30) NULL ,
5>     col3 CHAR(1) NULL
6>   ) ;
7> go



1> insert into t_source values ( 1 , 'Zahir Mohideen' , 'Y') ;
2> insert into t_source values ( 2 , 'Zahir Farook' , 'N') ;
3> insert into t_source values ( 3 , 'Mohideen Farook' , 'N') ;
4> go
(1 rows affected)



1> insert into t_dest values ( 2 , 'Zahir Farook' , 'N') ;
2> insert into t_dest values ( 3 , 'Mohideen Z Farook' , 'Y') ;
3> insert into t_dest values ( 4 , 'Kilakarai' , 'N') ;
4> go
(1 rows affected)


Here are the contents of the tables.

1> select * from t_source
2> go

col1 col2 col3
---- ---- ----
1 Zahir Mohideen Y
2 Zahir Farook N
3 Mohideen Farook N

(3 rows affected)

1> select * from t_dest;
2> go
col1 col2 col3
---- ---- ----
2 Zahir Farook N
3 Mohideen Z Farook Y
4 Kilakarai N

(3 rows affected)

Here is our implentaion of MERGE statement .

1> Merge t_dest d USING
2> ( SELECT col1 scol1 , col2 scol2 , col3 scol3 FROM t_source
3> ) s ON ( s.scol1 = d.col1 )
4> WHEN NOT matched THEN
5>   INSERT
6>     (
7>       col1 ,
8>       col2 ,
9>       col3
10>     )
11>     VALUES
12>     (
13>       s.scol1 ,
14>       s.scol2 ,
15>       s.scol3
16>     )
17> WHEN matched  AND col3 = 'N' THEN
18>   UPDATE SET col2 = s.scol2 , col3 = s.scol3
19> WHEN NOT matched BY source THEN
20>   DELETE ;
21> go

(3 rows affected)


The code in line # 4 is related to first condition
The code in line # 17 is related to second condition
The code in line # 19 is related to third condition


Here is the result set after running MERGE statement.

1> select * from t_dest
2> go

col1 col2 col3
---- ---- ----
1 Zahir Mohideen Y
2 Zahir Farook N
3 Mohideen Z Farook Y

(3 rows affected)

Comments Welcome.

How to estimate the size of the index in oracle

In Oracle , there are few ways to estimate the size of the index , that you are planning to create.
Let us see an example .


Method 1:
SQL> drop table t purge ;
Table dropped.

SQL> create table t as select object_id , object_name , object_type from all_objects ;
Table created.

SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.

SQL> explain plan for create index idx1_t on t(object_id);
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
------------------------------
Plan hash value: 2847741357
---------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | CREATE INDEX STATEMENT |        | 77233 |   377K|   170   (1)| 00:00:01 |
|   1 |  INDEX BUILD NON UNIQUE| IDX1_T |       |       |            |          |
|   2 |   SORT CREATE INDEX    |        | 77233 |   377K|            |          |
|   3 |    TABLE ACCESS FULL   | T      | 77233 |   377K|   130   (1)| 00:00:01 |
---------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
------------------------------
Note
-----
   - automatic DOP: skipped because of IO calibrate statistics are missing
   - estimated index size: 2097K bytes
15 rows selected.

Method 2: 

SQL> variable u_byte number;
SQL> variable a_byte number;
SQL> exec dbms_space.create_index_cost( 'create index idx1_t on t(object_id)', :u_byte, :a_byte );
PL/SQL procedure successfully completed.

SQL> print :u_byte;
    U_BYTE
----------
    386165

SQL> print :a_byte;
    A_BYTE
----------
   2097152


As you can see , with the second method , the estimated size are not rounded . Being this is an estimate , this shouldn't matter.

How to estimate the size of SYSAUX tablespace.

Starting with Oracle 10g , Oracle introduced SYSAUX  tablespace, where it maintains the information about components that are not MUST for a database to be up and running .

If SYSAUX is not available ,  core functionality is not impacted , but AWR  , ADDM , SQL plan management , Diagnostic Pack , ... .( to name a few )   will be impacted.

Oracle provides a script to estimate the size of the SYSAUX tablespace. This script is located under ORACLE_HOME/admin.

The script interview you with series of questions and comes up with the report.

Here is an example.
Comments welcome...!

c:\app\oracle\product\11.2.0\dbhome_2\RDBMS\ADMIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 22 16:41:23 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @utlsyxsz
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 to continue, otherwise enter an alternative.

Enter value for report_name:

...

....


c:\app\oracle\product\11.2.0\dbhome_2\RDBMS\ADMIN>type utlsyxsz.txt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
16:29:00 on Sep 22, 2011 ( Thursday ) in Timezone -04:00


DB_NAME     HOST_PLATFORM                             INST STARTUP_TIME      PAR
----------- ---------------------------------------- ----- ----------------- ---
* DBDEV     COR-DB02 - Microsoft Windows x86 64-bi     1 12:14:51 (09/16)  NO
            t


~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size:                       1,173.8 MB
|
| Total size of SM/AWR                       254.9 MB (  21.7% of SYSAUX )
| Total size of SM/OPTSTAT                   264.4 MB (  22.5% of SYSAUX )
| Total size of XDB                          127.8 MB (  10.9% of SYSAUX )
| Total size of SM/ADVISOR                   106.6 MB (   9.1% of SYSAUX )
| Total size of EM                            96.6 MB (   8.2% of SYSAUX )
| Total size of SDO                           45.3 MB (   3.9% of SYSAUX )
| Total size of AO                            41.9 MB (   3.6% of SYSAUX )
| Total size of XSOQHIST                      41.9 MB (   3.6% of SYSAUX )
| Total size of SQL_MANAGEMENT_BASE           28.6 MB (   2.4% of SYSAUX )
| Total size of ORDIM/ORDDATA                 13.6 MB (   1.2% of SYSAUX )
| Total size of LOGMNR                        12.3 MB (   1.0% of SYSAUX )
| Total size of SM/OTHER                       6.4 MB (   0.5% of SYSAUX )
| Total size of SMON_SCN_TIME                  6.3 MB (   0.5% of SYSAUX )
| Total size of JOB_SCHEDULER                  6.2 MB (   0.5% of SYSAUX )
| Total size of PL/SCOPE                       5.9 MB (   0.5% of SYSAUX )
| Total size of XSAMD                          5.1 MB (   0.4% of SYSAUX )
| Total size of EXPRESSION_FILTER              3.6 MB (   0.3% of SYSAUX )
| Total size of TEXT                           3.6 MB (   0.3% of SYSAUX )
| Total size of WM                             2.0 MB (   0.2% of SYSAUX )
| Total size of EM_MONITORING_USER             1.7 MB (   0.1% of SYSAUX )
| Total size of LOGSTDBY                       1.4 MB (   0.1% of SYSAUX )
| Total size of STREAMS                        1.0 MB (   0.1% of SYSAUX )
| Total size of ORDIM                          0.4 MB (   0.0% of SYSAUX )
| Total size of AUTO_TASK                      0.3 MB (   0.0% of SYSAUX )
| Total size of Others                        95.9 MB (   8.2% 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 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 to use the current value:   8.00 days
|   otherwise enter an alternative
|
Enter value for retention: 30

**   Value for 'Retention Setting': 30

|
| For 'Number of Instances',
|   Press to use the current value:   1.00
|   otherwise enter an alternative
|
Enter value for num_instances: 1

**   Value for 'Number of Instances': 1

|
| For 'Average Number of Active Sessions',
|   Press to use the current value:   0.09
|   otherwise enter an alternative
|
Enter value for active_sessions: 50

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

| ***************************************************
| Estimated size of AWR:                   3,558.0 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval -        60 minutes
|           Retention -     30.00 days
|       Num Instances -         1
|     Active Sessions -     50.00
|           Datafiles -         6
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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 to use the current value:  1,114.0
|   otherwise enter an alternative
|
Enter value for number_of_tables: 2000

**   Value for 'Number of Tables': 2000

|
| For 'Number of Partitions',
|   Press to use the current value:  1,233.0
|   otherwise enter an alternative
|
Enter value for number_of_partitions: 2500

**   Value for 'Number of Partitions': 2500

|
| For 'Statistics Retention',
|   Press to use the current value:     31.0 days
|   otherwise enter an alternative
|
Enter value for stats_retention:

**   Value for 'Statistics Retention': 31
|
| For 'DML Activity',
|   Press to use the current value:        2
|   otherwise enter an alternative <1=low, 2=medium, 3=high>
|
Enter value for dml_activity: 2

**   Value for 'DML Activity': 2

| ***************************************************
| Estimated size of Stats history            146.7 MB
|
|   The space for Optimizer Statistics history was
|   estimated using the following values:
|
|                         Tables -   2,000
|                        Indexes -   2,758
|                        Columns -  32,097
|                     Partitions -   2,500
|          Indexes on Partitions -     684
|          Columns in Partitions -  65,142
|        Stats Retention in Days -      31
|          Level of DML Activity -  Medium
| ***************************************************

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

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR:                   3,558.0 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval -        60 minutes
|           Retention -     30.00 days
|       Num Instances -         1
|     Active Sessions -     50.00
|           Datafiles -         6
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history            146.7 MB
|
|   The space for Optimizer Statistics history was
|   estimated using the following values:
|
|                         Tables -   2,000
|                        Indexes -   2,758
|                        Columns -  32,097
|                     Partitions -   2,500
|          Indexes on Partitions -     684
|          Columns in Partitions -  65,142
|        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                            127.8 MB
| Est size of SM/ADVISOR                     106.6 MB
| Est size of EM                              96.6 MB
| Est size of SDO                             45.3 MB
| Est size of AO                              41.9 MB
| Est size of XSOQHIST                        41.9 MB
| Est size of SQL_MANAGEMENT_BASE             28.6 MB
| Est size of ORDIM/ORDDATA                   13.6 MB
| Est size of LOGMNR                          12.3 MB
| Est size of SM/OTHER                         6.4 MB
| Est size of SMON_SCN_TIME                    6.3 MB
| Est size of JOB_SCHEDULER                    6.2 MB
| Est size of PL/SCOPE                         5.9 MB
| Est size of XSAMD                            5.1 MB
| Est size of EXPRESSION_FILTER                3.6 MB
| Est size of TEXT                             3.6 MB
| Est size of WM                               2.0 MB
| Est size of EM_MONITORING_USER               1.7 MB
| Est size of LOGSTDBY                         1.4 MB
| Est size of STREAMS                          1.0 MB
| Est size of ORDIM                            0.4 MB
| Est size of AUTO_TASK                        0.3 MB
| Est size of Others                          95.9 MB

| Est size of SM/AWR                       3,558.0 MB
| Est size of SM/OPTSTAT                     146.7 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size:             4,359.1 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report

c:\app\oracle\product\11.2.0\dbhome_2\RDBMS\ADMIN>