Tracking Oracle database growth


To calculate the Datafile Growth on a yearly basis:
===================================================
select to_char(CREATION_TIME,'RRRR') year,to_char(CREATION_TIME,'MM') month,round(sum(bytes)/1024/1024/1024) gb
from v$datafile group by to_char(CREATION_TIME,'RRRR'),to_char(CREATION_TIME,'MM') order by 1,2;

YEAR MO         GB
---- -- ----------
2000 04          9
2004 06          5
2004 11          5
2005 01          2
2005 02          4
2005 06          4
2005 09         20
2005 10          2
2005 11         12
2006 01          8
2007 01          1
2007 06          8
2007 08       1490
2008 01          8
2008 02          9
2008 03         44
2008 04         22
2008 07         16
2008 09         10
2008 10         57
2009 01         14
2009 02         36
2009 04         37
2009 05         26
2009 06         23
2009 07         82
2009 08          0
2009 09         18
2009 11         36
2009 12         16
2010 01         39
2010 02         80
2010 03          6
2010 04         56
2010 05         80
2010 06        471
2010 07         36
2010 08         75
2010 09        160
2010 10          4
2010 12         38
2011 02         59
2011 04         52
2011 05         16
2011 07         78
2011 08         40
2011 09          9
2011 10         78
2011 11         57
2011 12          8
2012 01        110
2012 02        134
2012 06          6
2012 08         14


Through  OEM :
===========

Here are the steps to know Database growth pattern for last one month/year using OEM


1) Login to OEM and Click on the Reports Tab
2) Navigate to Reports–>Storage–>Oracle Database Space Usage path and Click on Oracle Database Space Usage link.
3) Select the Target database and here we are getting Oracle Database space usage for last one Month.
4) Also we can get one year Database growth by setting Set Time Period Button.
5) Also we can find Oracle Database Tablespace Monthly Space Usage by Navigating Reports–>Storage–>Oracle Database Space Usage path and click on Oracle Database Tablespace Monthly Space Usage link.


AWR Reports


AWR Features

The AWR is used to collect performance statistics including:
  • Wait events used to identify performance problems.
  • Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  • Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
  • Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
  • Object usage statistics.

Workload Repository Reports

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report file name. The resulting report can be opened in a browser or text editor accordingly.


Enterprise Manager

The automated workload repository administration tasks have been included in Enterprise Manager. The "Automatic Workload Repository" page is accessed from the main page by clicking on the "Administration" link, then the "Workload Repository" link under the "Workload" section. The page allows you to modify AWR settings or manage snapshots without using the PL/SQL APIs.

Long running Concurrent Requests

1.How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';

2.Concurrent manager status for a given sid?

col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE,
s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';


3. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';

4.To find sid,serial# for a given concurrent request id?

set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';


5.To find concurrent program name,phase code,status code for a given request id?

SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code)
phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-
yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'),
completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;

6.To find the sql query for a given concurrent request sid?

select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&oracle_sid'
/

7. To find child requests

set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE
(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',
'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =
'&parent_concurrent_request_id';


8. Cancelling Concurrent request :

update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;

9. Kill sessions program wise

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where
MODULE like '';


10 .Concurrent Request running by SID

SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;

11. Find out request id from Oracle_Process Id:


select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';


12. Oracle Concurrent Request Error Script (requests which were error ed out)

SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;


13. Request submitted by User

SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;



14.Concurrent Program enable with trace

col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID






















How to monitor RMAN Backups ?


RMAN Backups Monitoring

select count(*) from  v$backup_async_io where status='IN PROGRESS';

Session information on backups

SELECT s.sid, username AS "User", program, module, action, logon_time "Logon", l.*
FROM v$session s, v$enqueue_lock l
WHERE l.sid = s.sid and l.type = 'CF' AND l.id1 = 0 and l.id2 = 2;

RMAN backup overall progress status

SELECT decode(context,1,'This Task:',2,'Agregate:','?') Context, sofar, totalwork, round(sofar/totalwork*100,2) "% Complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork
UNION
SELECT decode(context,1,'This Task:',2,'Agregate:','?') Context, sofar, totalwork, round(sofar/totalwork*100,2) "% Complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork;

The simplest query for checking what’s happening in a database


When someone asks you to take a quick look into database performance and for whatever reason you can’t run your usual scripts or performance tools on there, ), then what query would you run first?
Yeah sometimes I’ve been not allowed to run custom scripts nor even touch the keyboard due security policies in effect.
Whenever you’re in such situation you want to be the command both short and effective for showing the database state.
The simplest query for determining database state performance wise would be this:
SQL> select event, state, count(*) from v$session_wait group by event, state order by 3 desc;

EVENT                                                            STATE                 COUNT(*)
---------------------------------------------------------------- ------------------- ----------
rdbms ipc message                                                WAITING                      9
SQL*Net message from client                                      WAITING                      8
log file sync                                                    WAITING                      6
gcs remote message                                               WAITING                      2
PL/SQL lock timer                                                WAITING                      2
PL/SQL lock timer                                                WAITED KNOWN TIME            2
Streams AQ: qmn coordinator idle wait                            WAITING                      1
smon timer                                                       WAITING                      1
log file parallel write                                          WAITING                      1
ges remote message                                               WAITING                      1
SQL*Net message to client                                        WAITED SHORT TIME            1
DIAG idle wait                                                   WAITING                      1
pmon timer                                                       WAITING                      1
db file sequential read                                          WAITING                      1
Streams AQ: waiting for messages in the queue                    WAITING                      1
rdbms ipc message                                                WAITED KNOWN TIME            1
jobq slave wait                                                  WAITING                      1
Streams AQ: qmn slave idle wait                                  WAITING                      1
Streams AQ: waiting for time management or cleanup tasks         WAITING                      1

19 rows selected.

It uses the Oracle wait interface to report what all database sessions are currently doing wait/CPU usage wise. Whenever there’s a systemic issue (like extremely slow log file writes) this query will give good hint towards the cause of problem. Of course just running couple of queries against wait interface doesn’t give you the full picture (as these kinds of database wide “healthchecks” can be misleading as we should be really measuring end user response time breakdown at session level and asking questions like what throughput/response time do you normally get) but nevertheless, if you want to see an instance sessions state overview, this is the simplest query I know.
Interpreting this query output should be combined with reading some OS performance tool output (like vmstat or perfmon), in order to determine whether the problem is induced by CPU overload. For example, if someone is running a parallel backup compression job on the server which is eating all CPU time, some of these waits may be just a side-effect of CPU overload).
Below is a cosmetically enhanced version of this command, as one thing I decode the “WAITED FOR xyz TIME” wait states to “WORKING” and “On CPU / runqueue” as event name as otherwise it’s easy to miss by accident that some sessions are not actually waiting on previous event anymore:
SQL> select
  2     count(*),
  3     CASE WHEN state != 'WAITING' THEN 'WORKING'
  4          ELSE 'WAITING'
  5     END AS state,
  6     CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
  7          ELSE event
  8     END AS sw_event
  9  FROM
 10     v$session_wait
 11  GROUP BY
 12     CASE WHEN state != 'WAITING' THEN 'WORKING'
 13          ELSE 'WAITING'
 14     END,
 15     CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
 16          ELSE event
 17     END
 18  ORDER BY
 19     1 DESC, 2 DESC
 20  /

  COUNT(*) STATE   EVENT
---------- ------- ----------------------------------------
        11 WAITING log file sync
         9 WAITING rdbms ipc message
         4 WAITING SQL*Net message from client
         3 WAITING PL/SQL lock timer
         2 WORKING On CPU / runqueue
         2 WAITING gcs remote message
         1 WAITING ges remote message
         1 WAITING pmon timer
         1 WAITING smon timer
         1 WAITING jobq slave wait
         1 WAITING Streams AQ: waiting for messages in the
         1 WAITING DIAG idle wait
         1 WAITING Streams AQ: qmn slave idle wait
         1 WAITING Streams AQ: waiting for time management
         1 WAITING db file sequential read
         1 WAITING log file parallel write
         1 WAITING Streams AQ: qmn coordinator idle wait

17 rows selected.

SQL>

Also, sometimes you might want to exclude the background processes and idle sessions from the picture:
SQL> select
  2     count(*),
  3     CASE WHEN state != 'WAITING' THEN 'WORKING'
  4          ELSE 'WAITING'
  5     END AS state,
  6     CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
  7          ELSE event
  8     END AS sw_event
  9  FROM
 10     v$session
 11  WHERE
 12      type = 'USER'
 13  AND status = 'ACTIVE'
 14  GROUP BY
 15     CASE WHEN state != 'WAITING' THEN 'WORKING'
 16          ELSE 'WAITING'
 17     END,
 18     CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
 19          ELSE event
 20     END
 21  ORDER BY
 22     1 DESC, 2 DESC
 23  /

  COUNT(*) STATE   EVENT
---------- ------- ----------------------------------------
         6 WAITING PL/SQL lock timer
         4 WORKING On CPU / runqueue
         3 WAITING db file sequential read
         1 WAITING read by other session
         1 WAITING Streams AQ: waiting for messages in the
         1 WAITING jobq slave wait

6 rows selected.

By the way, the above scripts report quite similar data what ASH is actually using (especially the instance performance graph which shows you the instance wait summary). ASH nicely puts the CPU count of server into the graph as well (that you would be able to put the number of “On CPU” sessions into perspective), so another useful command to run after this script is “show parameter cpu_count” or better yet, check at OS level to be sure :)
Note that you can use similar technique for easily viewing the instance activity from other perspectives/dimensions, like which SQL is being executed:
SQL> select sql_hash_value, count(*) from v$session
  2  where status = 'ACTIVE' group by sql_hash_value order by 2 desc;

SQL_HASH_VALUE   COUNT(*)
-------------- ----------
             0         20
     966758382          8
    2346103937          2
    3393152264          1
    3349907142          1
    2863564559          1
    4030344732          1
    1631089791          1

8 rows selected.

SQL> select sql_text,users_executing from v$sql where hash_value = 966758382;

SQL_TEXT                                                     USERS_EXECUTING
------------------------------------------------------------ ---------------
BEGIN :1 := orderentry.neworder(:2,:3,:4); END;                           10

How to recover Applications context file if it is corrupted or deleted accidentally?


The Applications context file can be retrieved by running the adclonectx.pl script.
To retrieve the Applications tier context file,
  • perl /clone/bin/adclonectx.pl retrieve
  • On being prompted for the context file to be retrieved, select the option of retrieving the
          Applications tier context file that has been lost and retrieve it to the default location specified
          by the script.
The above command can be used only when INST_TOP the is still intact. In case that has also been lost
accidentally, the Applications tier context file may be retrieved as follows:
  • Execute the following command on the Database tier:perl /appsutil/clone/bin/adclonectx.pl retrieve
  • On being prompted for the context file to be retrieved, select the option of retrieving the
          Applications tier context file that has been lost.
  • While confirming the location for the context file, set it to any existing directory with write permission.
  • Once the context file has been generated in the specified location, move it to the location specified
          for the context file in the context variable 's_contextfile'.
To retrieve the Database tier context file,
  • Execute the following command on the Database tier:perl /appsutil/clone/bin/adclonectx.pl retrieve
  • On being prompted for the context file to be retrieved, select the Database tier context file and
          retrieve it to the default location specified by the script.

How to compile invalid objects in an APPS Environment



Applying Patches can create invalid objects. To get a quick count of the number of existing invalids (if any), use the following select statement :

SELECT COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID';

For a more detailed query, use the following script :

SELECT OWNER, OBJECT_TYPE, COUNT(*)
FROM DBA_OBJECTS
WHERE STATUS = 'INVALID'
GROUP BY OWNER, OBJECT_TYPE;

To recompile an individual object, connect to SQL*PLUS as the owner of the object (generally apps) and use one of the following depending on the object type :

alter package <package_name> compile; (package specification)
alter package <package_name> compile body; (package body)
alter view <view_name> compile; (view)

If the object compiles with warnings, use either of the following to see the errors that caused the warnings :

show errors
OR
select * from user_errors where name = '<OBJECT_NAME>';

Another way to correct invalid objects is to run the adadmin utility as follows:

UNIX OPERATING PLATFORM

1. Log in as APPS User : <applmgr username>/<applmgr password>

2. Start the adadmin-Utility from the Unix prompt with this command :

adadmin

The utility will then ask you a series of questions.

3. Under the Maintain Applications Database Objects Menu, select Compile APPS schema(s)

This task spawns parallel workers to compile invalid database objects in your APPS schema(s). It uses the same parallel phases as AutoInstall.

Also try running $ORACLE_HOME/rdbms/admin/utlrp.sql ( as sysdba )

Within Applications, there is a script to compile INVALID objects - called ADCOMPSC.pls

Arguments for ADCOMPSC.pls :

1 - Schema to run in
2 - Password for schema
3 - Check errors for objects starting with #3

NOTE: The order in which to compile Invalid Objects in schemas is SYS, SYSTEM, APPS and then all others. APPS_DDL and APPS_ARRAY_DDL should exist in all schema's. In case of an ORA-1555 error while running adcompsc.pls, restart the script.

The script can be run as followed :

cd $AD_TOP/sql
sqlplus @adcompsc.pls SCHEMA_NAME SCHEMA_PASSWORD %

Example : SQL> @adcompsc.pls apps apps %

After the script completes, check for invalid objects again. If the number has decreased, but invalid objects still exist, run adcompsc.pls again. Keep running adcompsc.pls until number of invalid objects stops decreasing.

If there are any objects still left INVALID, verify them by using the script 'aderrchk.sql' to record the remaining INVALID objects. 'Aderrchk.sql' uses the same syntax as 'adcompsc.pls'. This script is also supplied with the Applications. Send the aderrchk.sql to a file using the spool <file> command in sqlplus.

e.g. sqlplus x/y @aderrchk.sql SCHEMA_NAME SCHEMA_PASSWORD %

For objects which will not compile, try the following :

select text
from user_source
where name = 'OBJECTNAME'
and text like '%Header%';

This script will provide the script that creates the packages/recreates the packages.
SQL>@packageheader
SQL>@packagebody
If recreating the package does not make the package valid, analyze the user_errors table to determine the cause of the invalid package :

select text
from user_errors
where name = '<PACKAGENAME>';

Undo Related Queries


To check retention guarantee for undo tablespace

select tablespace_name,status,contents,logging,retention from dba_tablespaces where tablespace_name like '%UNDO%';

To show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace

select     tablespace_name,   
status,
count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",         
sum(blocks)*8/(1024*1024) total_space
from     dba_undo_extents
group by    tablespace_name, status;

Extent Count and Total Blocks

set linesize 152  
col tablespace_name for a20
col status for a10

select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from   dba_undo_extents
where  tablespace_name in ('&undotbsp')
group by  tablespace_name,status;

To show UndoRetention Value

Show parameter undo_retention;


Undo retention in hours

col "Retention" for a30
col name for a30
col value for a50
select name "Retention",value/60/60 "Hours" from v$parameter where name like '%undo_retention%';

To check space related statistics of  UndoTablespace from stats$undostat of 90 days

select UNDOBLKS,BEGIN_TIME,MAXQUERYLEN,UNXPSTEALCNT,EXPSTEALCNT,NOSPACEERRCNT from stats$undostat where BEGIN_TIME between sysdate-90 and sysdate and UNXPSTEALCNT > 0;

To check space related statistics of  UndoTablespace from v$undostat

select
sum(ssolderrcnt) "Total ORA-1555s",
round(max(maxquerylen)/60/60) "Max Query HRS",
sum(unxpstealcnt) "UNExpired STEALS",
sum(expstealcnt) "Expired STEALS"
from v$undostat
order by begin_time;
Date wise occurrence of ORA-1555

select to_char(begin_time, 'mm/dd/yyyy hh24:mi') "Int. Start",
ssolderrcnt "ORA-1555s", maxquerylen "Max Query",
unxpstealcnt "UNExp SCnt",UNXPBLKRELCNT "UnEXPblks", expstealcnt "Exp SCnt",EXPBLKRELCNT "ExpBlks",
NOSPACEERRCNT nospace
from v$undostat where ssolderrcnt>0
order by begin_time;

Total number of ORA-1555s since instance startup

select 'TOTAL # OF ORA-01555 SINCE INSTANCE STARTUP : '|| to_char(startup_time,'DD-MON-YY HH24:MI:SS')
from v$instance;

To check for Active Transactions

set head on
select usn,extents,round(rssize/1048576)
rssize,hwmsize,xacts,waits,optsize/1048576 optsize,shrinks,wraps
from v$rollstat where xacts>0
order by rssize;

Undo Space Utilization by each Sessions

set lines 200
col sid for 99999
col username for a10
col name for a15
select  s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *8)/1024/1024 SizeGB
from    v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where   t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;

List of long running queries since instance startup

set head off
select 'LIST OF LONG RUNNING - QUERY SINCE INSTANCE STARTUP' from dual;
set head on
select     *
from
(select to_char(begin_time, 'DD-MON-YY hh24:mi:ss') BEGIN_TIME ,
round((maxquerylen/3600),1) Hours
from v$undostat
order by maxquerylen desc)
where    rownum < 11;

Undo Space used by all transactions

set lines 200
col sid for 99999
col username for a10
col name for a15
select  s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *8)/1024/1024 SizeGB
from    v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where   t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;

List of All active Transactions

select  sid,username,
t.used_ublk ,t.used_urec
from    v$transaction t,v$session s
where   t.addr=s.taddr;

To list all Datafile of UndoTablespace

select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDO%'
or tablespace_name like '%RBS%'
order by tablespace_name,file_name;

select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDOTBS2%'
order by tablespace_name,file_name;

col file_name for a40
set pagesize 100
select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%APPS_UNDOTS1%'
order by tablespace_name,file_name;

select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files where file_name like '%undo%' order by file_name;

To check when a table is last analysed

select OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from dba_tables where TABLE_NAME like 'MLC_PICK_LOCKS_DETAIL';

select OWNER,TABLE_NAME,TABLESPACE_NAME,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from dba_tables where TABLE_NAME like 'APPS.XLA_AEL_GL_V';

To list all Undo datafiles with status and size

show parameter undo
show parameter db_block_size
col tablespace_name form a20
col file_name form a60
set lines 120
select tablespace_name, file_name, status, bytes/1024/1024 from dba_data_files
where tablespace_name=(select tablespace_name from dba_tablespaces where contents='UNDO');

Total undo space

select    sum(bytes)/1024/1024/1024 GB from dba_data_files  where tablespace_name='&Undo_TB_Name';

Undo Tablespace

select tablespace_name from dba_tablespaces where tablespace_name like '%UNDO%';

To find MaxQueryLength from stats$undostat

Select Max(MAXQUERYLEN) from stats$undostat;

*select max(maxquerylen) from v$undostat;

*select begin_date,u.maxquerylen from
(select to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date,maxquerylen
from v$undostat order by maxquerylen desc) u  where rownum<11;

*select begin_date,u.maxquerylen from
(select maxquerylen,to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date from
v$undostat order by maxquerylen DESC) u  where rownum<26 order by begin_date ASC, maxquerylen DESC;

*select begin_date,u.maxquerylen from
(select maxquerylen,to_char(begin_time,'DD-MON-YYYY:HH24-MI-SS') begin_date from
v$undostat order by maxquerylen DESC) u  where rownum<26 order by  maxquerylen DESC;

*select sum(u.maxquerylen)/25 AvgUndoRetTime
from (select maxquerylen from v$undostat order by maxquerylen desc) u  where rownum<26;
*select sum(u.maxquerylen)
from (select maxquerylen from v$undostat order by maxquerylen desc) u  where rownum<26;

DBA_UNDO_EXTENTS

set linesize 152  
col tablespace_name for a20
col status for a10
select tablespace_name,status,count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB
from   dba_undo_extents
group by  tablespace_name, status
order by tablespace_name;

Mapping Undo Segments to usernames

select  s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,(rs.rssize)/1024/1024 MB,rn.name
from    v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where   t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;



Total Undo Statistics
alter session set nls_date_format='dd-mon-yy hh24:mi';
set lines 120
set pages 2000
select BEGIN_TIME,  END_TIME, UNDOBLKS, TXNCOUNT , MAXQUERYLEN  , UNXPSTEALCNT ,
EXPSTEALCNT , SSOLDERRCNT , NOSPACEERRCNT
from v$undostat;

Total Undo Statistics since specified year

select 'TOTAL STATISTICS SINCE Jan 01, 2005 - STATSPACK' from dual;
set head on
set lines 152
column undotsn format 999 heading 'Undo|TS#';
column undob format 9,999,999,999 heading 'Undo|Blocks';
column txcnt format 9,999,999,999,999 heading 'Num|Trans';
column maxq format 999,999 heading 'Max Qry|Len (s)';
column maxc format 9,999,999 heading 'Max Tx|Concurcy';
column snol format 9,999 heading 'Snapshot|Too Old';
column nosp format 9,999 heading 'Out of|Space';
column blkst format a13 heading 'uS/uR/uU/|eS/eR/eU' wrap;
column unst format 9,999 heading 'Unexp|Stolen' newline;
column unrl format 9,999 heading 'Unexp|Relesd';
column unru format 9,999 heading 'Unexp|Reused';
column exst format 9,999 heading 'Exp|Stolen';
column exrl format 9,999 heading 'Exp|Releas';
column exru format 9,999 heading 'Exp|Reused';
select undotsn
, sum(undoblks) undob
, sum(txncount) txcnt
, max(maxquerylen) maxq
, max(maxconcurrency) maxc
, sum(ssolderrcnt) snol
, sum(nospaceerrcnt) nosp
, sum(unxpstealcnt)
||'/'|| sum(unxpblkrelcnt)
||'/'|| sum(unxpblkreucnt)
||'/'|| sum(expstealcnt)
||'/'|| sum(expblkrelcnt)
||'/'|| sum(expblkreucnt) blkst
from stats$undostat
where dbid in (select dbid from v$database)
and instance_number in (select instance_number from v$instance)
and end_time > to_date('01012005 00:00:00', 'DDMMYYYY HH24:MI:SS')
and begin_time < (select sysdate from dual)
group by undotsn;

*SELECT (SUM(undoblks))/ SUM ((end_time - begin_time) * 86400) FROM v$undostat;

Checking for Recent ORA-1555

show parameter background

cd <background dump destination>
ls -ltr|tail

view <alert log file name>

shift + G ---> to get the tail end...

?ORA-1555 ---- to search of the error...

shift + N ---- to step for next reported error...

Rollback segment queries

Wraps

select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,extends,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn  where  rn.usn=rs.usn
order by wraps;

Wraps column as high values for the all segments size of rollback segments are small for long running queries and transactions by increasing the  rollback segments size we can avoid  the  ORA-01555 errors

Undo Contention

Rollback Segment Contention

prompt   If any ratio is > .01 then more rollback segments are needed

column "total_waits" format 999,999,999
column "total_timeouts" format 999,999,999
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;

Sample Output:
REM NAME                              WAITS             GETS     Ratio
REM ------------------------------ ----------         ---------- ---------
REM SYSTEM                                  0                269    .00000
REM R01                                     0                304    .00000
REM R02                                     0               2820    .00000
REM R03                                     0                629    .00000
REM R04                                     1                511    .00196
REM R05                                     0                513    .00000
REM R06                                     1                503    .00199
REM R07                                     0                301    .00000
REM R08                                     0                299    .00000

Looking at the tcl script to see what sql gets performed to determine rollback
segment contention

select count from v$waitstat where class = 'system undo header';
select count from v$waitstat where class = 'system undo block';
select count from v$waitstat where class = 'undo header';
select count from v$waitstat where class = 'undo block';               

Rollback Segment Information

set lines 152
col segment_type  for a10
col tablespace_name for a20
select owner,tablespace_name,extents,next_extent/1024 next_extnentKB,max_extents,pct_increase 
from dba_segments
where segment_type='ROLLBACK';

* set lines 152
col name for a15
select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn  where  rn.usn=rs.usn and rs.xacts>0;

* select name,extents,rssize/1048576 rssizeMB ,xacts,writes/1024/1024,optsize/1048576 optsize,
shrinks,wraps,extends,aveshrink/1048576,waits,rs.status,rs.curext
from v$rollstat rs, v$rollname rn  where  rn.usn=rs.usn
order by wraps;

* select name,extents,optsize/1048576 optsize,
shrinks,wraps,aveshrink/1048576,aveactive,rs.status,rs.curext
from v$rollstat rs, v$rollname rn  where  rn.usn=rs.usn;

* select  sum(rssize)/1024/1024/1024 sizeGB from v$rollstat;

* select sum(xacts) from v$rollstat;
select  sum(rssize)/1024/1024/1024 sizeGB from v$rollstat where xacts=0;
select  sum(rssize)/1024/1024/1024 sizeGB from v$rollstat where xacts>0;
select sum(xacts) from v$rollstat;

* select tablespace_name,segment_name,initial_extent,next_extent,min_extents,max_extents,status
from dba_rollback_segs
where status='ONLINE';

* select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible
from dba_data_files where file_name like '%&filename%';

* select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='&tbs';

Optimize Oracle UNDO Parameters

Actual Undo Size
SELECT SUM(a.bytes/1024/1024/1024) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

UNDO_SIZE
----------
209715200

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;

UNDO_BLOCK_PER_SEC
------------------
3.12166667

Undo Segment Summary for DB

Undo Segment Summary for DB: S901  Instance: S901  Snaps: 2 -3
-> Undo segment block stats:
-> uS - unexpired Stolen,   uR - unexpired Released,   uU - unexpired reUsed
-> eS - expired   Stolen,   eR - expired   Released,   eU - expired   reUsed

Undo           Undo        Num  Max Qry     Max Tx Snapshot Out of uS/uR/uU/
TS#         Blocks      Trans  Len (s)   Concurcy  Too Old  Space eS/eR/eU
---- -------------- ---------- -------- ---------- -------- ------ -------------
1         20,284      1,964        8         12        0      0 0/0/0/0/0/0

Undo Segment Stats for DB

Undo Segment Stats for DB: S901  Instance: S901  Snaps: 2 -3
-> ordered by Time desc

Undo      Num Max Qry   Max Tx  Snap   Out of uS/uR/uU/
End Time           Blocks    Trans Len (s)    Concy Too Old  Space eS/eR/eU
------------ ------------ -------- ------- -------- ------- ------ -------------
12-Mar 16:11       18,723    1,756       8       12       0      0 0/0/0/0/0/0
12-Mar 16:01        1,561      208       3       12       0      0 0/0/0/0/0/0

Undo Segment Space Required = (undo_retention_time * undo_blocks_per_seconds)

As an example, an UNDO_RETENTION of 5 minutes (default) with 50 undo blocks/second (8k blocksize)
will generate:
Undo Segment Space Required = (300 seconds * 50 blocks/ seconds * 8K/block) = 120 M


select tablespace_name,file_name,file_id,autoextensible,bytes/1048576
Mbytes, maxbytes/1048576 maxMbytes
from dba_data_files
where tablespace_name like '%UNDO%'
or tablespace_name like '%RBS%'
or tablespace_name like '%ROLLBACK%'
order by tablespace_name,file_name;

select a.owner,a.tablespace_name,b.status, a.extents,a.next_extent/1024 next_extnentKB,a.max_extents,a.pct_increase from dba_segments a,dba_tablespaces b
where segment_type='ROLLBACK' and a.tablespace_name=b.tablespace_name;

select tablespace_name,status from dba_tablespaces where tablespace_name='ROLLBACK';

Actual Undo Size

SELECT SUM(a.bytes/1024/1024) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

UNDO_SIZE
----------
209715200

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;

UNDO_BLOCK_PER_SEC
------------------
3.12166667

DB Block Size

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
FROM v$parameter
WHERE name = 'db_block_size';

DB_BLOCK_SIZE [Byte]
--------------------
4096

Optimal Undo Retention

209'715'200 / (3.12166667 * 4'096) = 16'401 [Sec]

Using Inline Views, you can do all in one query!

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/

ACTUAL UNDO SIZE [MByte]
------------------------
200

UNDO RETENTION [Sec]
--------------------
10800

OPTIMAL UNDO RETENTION [Sec]
----------------------------
16401

Calculate Needed UNDO Size for given Database Activity

If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Again, all in one query:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/

ACTUAL UNDO SIZE [MByte]
------------------------
200
UNDO RETENTION [Sec]
--------------------
10800
NEEDED UNDO SIZE [MByte]
------------------------
131.695313

Checking when tables are last analyzed
select
OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,PARTITIONED,DEPENDENCIES,DROPPED from
dba_tables where TABLE_NAME like 'MLC_END_USER_REGISTRATION';

DECLARE
v_table_space_name      VARCHAR2(30);
v_table_space_size_in_MB        NUMBER(9);
v_auto_extend      BOOLEAN;
v_undo_retention      NUMBER(9);
v_retention_guarantee    BOOLEAN;
v_undo_info_return    BOOLEAN;
BEGIN
v_undo_info_return := dbms_undo_adv.undo_info(v_table_space_name, v_table_space_size_in_MB, v_auto_extend, v_undo_retention, v_retention_guarantee);
dbms_output.put_line(’UNDO Tablespace Name: ‘ || v_table_space_name);
dbms_output.put_line(’UNDO Tablespace size (MB) : ‘ || TO_CHAR(v_table_space_size_in_MB));
dbms_output.put_line(’If UNDO tablespace is auto extensible above size indicates max possible size of the undo tablespace’);
dbms_output.put_line(’UNDO tablespace auto extensiable is : ‘|| CASE WHEN v_auto_extend THEN  ‘ON’ ELSE ‘OFF’ END);
dbms_output.put_line(’Undo Retention (Sec): ‘ || v_undo_retention);
dbms_output.put_line(’Retention : ‘||CASE WHEN v_retention_guarantee THEN ‘Guaranteed ‘ ELSE ‘NOT Guaranteed’ END);
END;

undo_autotune

This function is used to find auto tuning of undo retention is ENABLED or NOT.

Set serverout on
declare
v_autotune_return Boolean := null;
v_autotune_enabled boolean := null;
begin
v_autotune_return:= dbms_undo_adv.undo_autotune(v_autotune_enabled);
dbms_output.put_line(CASE WHEN v_autotune_return THEN 'Information is available :' ELSE 'Information is NOT available :' END||
CASE WHEN v_autotune_enabled THEN 'Auto tuning of undo retention is ENABLED' ELSE 'Auto tuning of undo retention is NOT enabled' END);
end;
/

select dbms_undo_adv.longest_query from dual

select dbms_undo_adv.required_retention from dual


select dbms_undo_adv.best_possible_retention from dual

select  dbms_undo_adv.required_undo_size(1800) from dual

DECLARE
v_undo_health_return number;
v_retention number;
v_utbsize number;
v_problem VARCHAR2(1024);
v_recommendation VARCHAR2(1024);
v_rationale VARCHAR2(1024);
BEGIN
v_undo_health_return :=  dbms_undo_adv.undo_health(problem => v_problem,
recommendation => v_recommendation,
rationale => v_rationale,
retention => v_retention,
utbsize => v_utbsize);
dbms_output.put_line(’Problem : ‘||v_problem);
dbms_output.put_line(’Recommendation= : ‘||v_recommendation);
dbms_output.put_line(’Rationale : ‘||v_retention);
dbms_output.put_line(’Retention : ‘||v_retention);
dbms_output.put_line(’UNDO tablespace size : ‘||v_utbsize);
END;

undo_advisor

It uses oracle’s advisor framework to find out problem and provide recommendations.

DECLARE
v_undo_advisor_return VARCHAR2(100);
BEGIN
v_undo_advisor_return := dbms_undo_adv.undo_advisor(instance => 1);
dbms_output.put_line(v_undo_advisor_return);
END;