To see Temp Space:
-------------------
SELECT a.tablespace_name,ROUND((c.total_blocks*b.block_size)/1024/1024/1024,2)
"Total Size [GB]",ROUND((a.used_blocks*b.block_size)/1024/1024/1024,2) "Used_size[GB]",
ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024/1024,2) "Free_size[GB]",
ROUND((a.max_blocks*b.block_size)/1024/1024/1024,2) "Max_Size_Ever_Used[GB]",
ROUND((a.max_used_blocks*b.block_size)/1024/1024/1024,2) "MaxSize_ever_Used_by_Sorts[GB]" ,
ROUND((a.used_blocks/c.total_blocks)*100,2) "Used Percentage"
FROM V$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)
total_blocks FROM dba_temp_files GROUP by tablespace_name) c
WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;
==============================================================================================
To see top 10 consuming process :
---------------------------------
select * from
(SELECT d.tablespace_name,a.sid,a.serial#,a.program,a.module,a.action,a.username "DB Username",a.osuser,ROUND((b.blocks*d.block_size)/1024/1024,2) "Used MB",c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,dba_tablespaces d
WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value AND d.tablespace_name=b.tablespace ORDER BY b.tablespace, b.blocks DESC)
where rownum <=10
Query to check TEMP USAGE :
col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';
=================================================================================================
To Check Percentage Usage of Temp Tablespace:
---------------------------------------------
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;
=================================================================================================
To find Sort Segment Usage by a particular User:
------------------------------------------------
SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
order by u.blocks desc;
To find Total Free space in Temp Tablespace :
---------------------------------------------
select tablespace_name , (free_blocks*8)/1024/1024 FreeSpaceInGB,
(used_blocks*8)/1024/1024 UsedSpaceInGB,
(total_blocks*8)/1024/1024 TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%'
=====================================================================================================
------------------------------------------------
SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
order by u.blocks desc;
To find Total Free space in Temp Tablespace :
---------------------------------------------
select tablespace_name , (free_blocks*8)/1024/1024 FreeSpaceInGB,
(used_blocks*8)/1024/1024 UsedSpaceInGB,
(total_blocks*8)/1024/1024 TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%'
=====================================================================================================
Get 10 sessions with largest temp usage :
-----------------------------------------
cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;
==============================
-----------------------------------------
cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;
==============================
Identifying WHO is currently using TEMP Segments :
---------------------------------------------------
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND (b.blocks*d.block_size)/1048576 > 1024
ORDER BY b.tablespace, 6 desc;
=============================================================================================
---------------------------------------------------
SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND (b.blocks*d.block_size)/1048576 > 1024
ORDER BY b.tablespace, 6 desc;
=============================================================================================
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 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.
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';
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 ?
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;
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.
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
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.
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>';
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;
No comments:
Post a Comment