User and the SQL’s they are running

set linesize 138
set pagesize 200
col c1 for a9
col c1 heading “OSUser”
col c2 for a9 trunc
col c2 heading “OraUser”
col b1 for a6
col b1 heading “OSPID”
col b2 for 9999 justify left
col b2 heading “OraSID”
col b3 for 999999 justify left
col b3 heading “Serial#”
col b5 for a3 trunc
col b5 heading STS
col sql_text for a65
set space 1
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.status b5,b.username c2, b.sid b2, b.serial# b3,to_char(b.logon_time,’DDMonYY HH24:MI:SS’)
LOGIN_TIME,a.sql_text from V$SQLTEXT a, V$SESSION b,V$PROCESS c
where a.address = b.sql_address
— and b.status = ‘ACTIVE’ /* CHOOSE THIS OPTION ONLY TO SEE ACTVE TRANSACTION ON THAT MOMENT */
and b.paddr = c.addr
and a.hash_value = b.sql_hash_value order by c.spid,a.hash_value,a.piece;

Progress of RMAN backup

SELECT SID, SERIAL#, 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

Rman backup Details

select to_char(min(a),’mm-dd-yyyy hh24:mi:ss’) Start_Time,
to_char(max(b),’mm-dd-yyyy hh24:mi:ss’) End_Time,
(max(b)-min(a))*1440 Minutes
from (
select bs.start_time a
,bs.completion_time b
FROM
v$backup_set bs
, (select distinct
set_stamp
, set_count
, tag
, device_type
from v$backup_piece
where status in (‘A’, ‘X’)) bp
, (select distinct
set_stamp
, set_count
, ‘YES’ spfile_included
from v$backup_spfile) sp
WHERE
bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bs.set_stamp = sp.set_stamp (+)
AND bs.set_count = sp.set_count (+)
AND bs.start_time > SYSDATE -1
order by 1
)

SELECT
bs.recid bs_key
, bp.tag tag
, DECODE(backup_type
, ‘L’, ‘Archived Logs’
, ‘D’, ‘Datafile Full’
, ‘I’, ‘Incremental’) backup_type
, device_type device_type
, DECODE( bs.controlfile_included
, ‘NO’, null
, bs.controlfile_included) controlfile_included
, sp.spfile_included spfile_included
, bs.incremental_level incremental_level
, bs.pieces pieces
, TO_CHAR(bs.start_time, ‘mm/dd/yy HH24:MI:SS’) start_time
, TO_CHAR(bs.completion_time, ‘mm/dd/yy HH24:MI:SS’) completion_time
, (bs.elapsed_seconds/60) elapsed_minutes
FROM
v$backup_set bs
, (select distinct
set_stamp
, set_count
, tag
, device_type
from v$backup_piece
where status in (‘A’, ‘X’)) bp
, (select distinct
set_stamp
, set_count
, ‘YES’ spfile_included
from v$backup_spfile) sp
WHERE
bs.set_stamp = bp.set_stamp
AND bs.set_count = bp.set_count
AND bs.set_stamp = sp.set_stamp (+)
AND bs.set_count = sp.set_count (+)
AND bs.start_time > SYSDATE -1
ORDER BY
bs.recid

Oracle Statistics – Find the sampling percentage for a table

SELECT A.table_name, A.num_rows, B.bytes / 1024 / 1024 mb,A.sample_size,
100 * (A.sample_size / A.num_rows) sample_pct
FROM dba_tables A, dba_segments B
WHERE A.table_name IN (‘S_PARTY’)
AND B.segment_type = ‘TABLE’
AND B.segment_name = A.table_name
ORDER BY sample_pct;

Top user of Memory , IO and CPU

SELECT
‘Top Physical I/O Process’ CATEGORY,
SID,
USERNAME,
ROUND(100 * TOTAL_USER_IO/TOTAL_IO,2) PCT_USED
FROM
(SELECT b.SID SID,
nvl(b.USERNAME,p.NAME) USERNAME,
sum(VALUE) TOTAL_USER_IO
FROM V$STATNAME c,
V$SESSTAT a,
V$SESSION b,
V$BGPROCESS p
WHERE a.STATISTIC#=c.STATISTIC# and
p.paddr (+) = b.paddr and
b.SID=a.SID AND
c.NAME in (‘physical reads’,’physical writes’,
‘physical reads direct’,’physical reads direct (lob)’,
‘physical writes direct’,’physical writes direct (lob)’)
GROUP BY b.SID, nvl(b.USERNAME,p.name)
ORDER BY 3 DESC),
(select sum(value) TOTAL_IO
from V$STATNAME c,
V$SESSTAT a
WHERE a.STATISTIC#=c.STATISTIC# and
c.NAME in (‘physical reads’,’physical writes’,
‘physical reads direct’,’physical reads direct (lob)’,
‘physical writes direct’,’physical writes direct (lob)’))
WHERE ROWNUM < 2
UNION ALL
SELECT "Top Logical I/O Process",
SID,
USERNAME,
ROUND(100 * TOTAL_USER_IO/TOTAL_IO,2) PCT_USED
FROM
(SELECT b.SID SID,
nvl(b.USERNAME,p.NAME) USERNAME,
sum(VALUE) TOTAL_USER_IO
FROM V$STATNAME c,
V$SESSTAT a,
V$SESSION b,
V$BGPROCESS p
WHERE a.STATISTIC#=c.STATISTIC# and
p.paddr (+) = b.paddr and
b.SID=a.SID AND
c.NAME in ("consistent gets&","db block gets")
GROUP BY b.SID, nvl(b.USERNAME,p.name)
ORDER BY 3 DESC),
(select sum(value) TOTAL_IO
from V$STATNAME c,
V$SESSTAT a
WHERE a.STATISTIC#=c.STATISTIC# and
c.NAME in ("consistent gets&","db block gets"))
WHERE ROWNUM < 2
UNION ALL
SELECT "Top Memory Process",
SID,
USERNAME,
ROUND(100 * TOTAL_USER_MEM/TOTAL_MEM,2)
FROM
(SELECT b.SID SID,
nvl(b.USERNAME,p.NAME) USERNAME,
sum(VALUE) TOTAL_USER_MEM
FROM V$STATNAME c,
V$SESSTAT a,
V$SESSION b,
V$BGPROCESS p
WHERE a.STATISTIC#=c.STATISTIC# and
p.paddr (+) = b.paddr and
b.SID=a.SID AND
c.NAME in ("session pga memory","session uga memory")
GROUP BY b.SID, nvl(b.USERNAME,p.name)
ORDER BY 3 DESC),
(select sum(value) TOTAL_MEM
from V$STATNAME c,
V$SESSTAT a
WHERE a.STATISTIC#=c.STATISTIC# and
c.NAME in ("session pga memory","session uga memory") )
WHERE ROWNUM < 2
UNION ALL
SELECT 'Top CPU Process',
SID,
USERNAME,
ROUND(100 * TOTAL_USER_CPU/GREATEST(TOTAL_CPU,1),2)
FROM
(SELECT b.SID SID,
nvl(b.USERNAME,p.NAME) USERNAME,
sum(VALUE) TOTAL_USER_CPU
FROM V$STATNAME c,
V$SESSTAT a,
V$SESSION b,
V$BGPROCESS p
WHERE a.STATISTIC#=c.STATISTIC# and
p.paddr (+) = b.paddr and
b.SID=a.SID AND
c.NAME = "CPU used by this session"
GROUP BY b.SID, nvl(b.USERNAME,p.name)
ORDER BY 3 DESC),
(select sum(value) TOTAL_CPU
from V$STATNAME c,
V$SESSTAT a
WHERE a.STATISTIC#=c.STATISTIC# and
c.NAME = "CPU used by this session")
WHERE ROWNUM < 2;

Temp Space usage per statement and session

– Temp segment usage per session.

SQL> SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;

2. Identify temp segment usages per statement

– Temp segment usage per statement.

SQL> SELECT S.sid || ‘,’ || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY mb_used;

Smallest size for datafile to RESIZE

— following script will help you to determine smallest size for datafile.

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading “Smallest|Size|Poss.”
column currsize format 999,990 heading “Current|Size”
column savings format 999,990 heading “Poss.|Savings”
break on report
compute sum of savings on report

column value new_val blksize
select value from v$parameter where name = ‘db_block_size’
/

select file_name,
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) –
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
/