UNDO SIZE INFORMATION

set serverout on size 1000000
set feedback off
set heading off
set lines 132
declare
cursor get_undo_stat is
select d.undo_size/(1024*1024) "C1",
substr(e.value,1,25) "C2",
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "C3",
round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "C4"
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';
begin
dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) || 'To optimize UNDO you have two choices :'); dbms_output.put_line('====================================================' || chr(10));
for rec1 in get_undo_stat loop
dbms_output.put_line('A) Adjust UNDO tablespace size according to UNDO_RETENTION :' || chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',60,'.')|| ' : ' || TO_CHAR(rec1.c1,'999999') || ' MB');
dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (' || ltrim(TO_CHAR(rec1.c2/60,'999999')) || ' MINUTES) ',60,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MB');
dbms_output.put_line(chr(10));
dbms_output.put_line(chr(10));
dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :' || chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',60,'.') || ' : ' || TO_CHAR(rec1.c2/60,'999999') || ' MINUTES');
dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (' || ltrim(TO_CHAR(rec1.c1,'999999'))
|| ' MEGS) ',60,'.') || ' : ' || TO_CHAR(rec1.c4/60,'999999') || ' MINUTES');
end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/



===


-- ************* ROLLBACK SEGMENTS ************** --
prompt

clear breaks
clear computes
clear columns
set heading on
column tablespace_name heading 'Tablespace' justify left format a15 truncated
column segment_name heading 'Seg|Name' justify center format a7
column status heading 'Status' justify center format a8
column initial_extent heading 'Initial|(in M)' justify center format 99990.9
column next_extent heading 'Next|(in M)' justify center format 99990.9
column min_extents heading 'Min|Ext' justify center format 99990
column max_extents heading 'Max|Ext' justify center format 999999999990
column pct_increase heading 'Pct|Inc' justify center format 99990
column rbsize heading 'Curr Size|(in M)' justify left format 9,99990
break on tablespace_name skip 1 on report skip 2
select
r.tablespace_name,
r.segment_name, r.status,
r.initial_extent/1024/1024 "initial_extent",
r.next_extent/1024/1024 "next_extent",
r.min_extents,r.max_extents,
r.pct_increase,
sum(e.bytes)/1024/1024 "rbsize"
from dba_rollback_segs r, dba_extents e
where e.segment_name = r.segment_name
group by r.tablespace_name, r.segment_name, r.status,
r.initial_extent/1024, r.next_extent/1024,
r.min_extents, r.max_extents, r.pct_increase;




Seg Initial Next Min Max Pct Curr Size
Tablespace Name Status (in M) (in M) Ext Ext Inc (in M)
--------------- ------- -------- -------- -------- ------ ------------- ------ ---------
UNDOTBS1 _SYSSMU ONLINE 0.1 0.1 2 32765 1
6_24433
81498$

_SYSSMU ONLINE 0.1 0.1 2 32765 1
9_14243
41975$

_SYSSMU ONLINE 0.1 0.1 2 32765 0
10_3550
978943$

_SYSSMU ONLINE 0.1 0.1 2 32765 1
3_20976
77531$

_SYSSMU ONLINE 0.1 0.1 2 32765 1
7_32866
10060$


SYSTEM SYSTEM ONLINE 0.1 0.1 1 32765 0

UNDOTBS1 _SYSSMU ONLINE 0.1 0.1 2 32765 2
4_11520
05954$

_SYSSMU ONLINE 0.1 0.1 2 32765 0
2_22325
71081$

_SYSSMU ONLINE 0.1 0.1 2 32765 1
8_20123
82730$

_SYSSMU ONLINE 0.1 0.1 2 32765 1
5_15274
69038$

_SYSSMU ONLINE 0.1 0.1 2 32765 0
1_37803
97527$

Oracle Session in Shared Pool

select sid,
username,
round(total_user_mem/1024/1024,2) mem_used_in_mb,
round(100 * total_user_mem/total_mem,2) mem_percent
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_mem
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.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)),
(select
sum(value) total_mem
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.STATISTIC# and
c.name in ('session pga memory','session uga memory'))
order by 3 desc;

Oracle which session are hogging the database

select
sid,
serial,
username,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
serial# serial,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.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 writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by b.sid,serial#, nvl(b.username,p.name)),
(select sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by 3 desc;


=======


from Web ---

set serveroutput on
declare
cursor c1 is
select version from v$instance;

cursor c2 is
select
host_name
,  instance_name
,  to_char(sysdate, 'DD-MON-YY HH24:MI:SS') currtime
,  to_char(startup_time, 'DD-MON-YY HH24:MI:SS') starttime
from v$instance;

cursor c4 is
select * from (SELECT count(*) cnt, substr(event,1,50) event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message',
'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC) where rownum <6;

cursor c5 is
select round(sum(value)/1048576) as sgasize from v$sga;

cursor c6 is select round(sum(bytes)/1048576) as dbsize
from v$datafile;

cursor c7 is
select 'top physical i/o process' category, sid,
username, total_user_io amt_used,
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)')
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
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,
total_user_io amt_used,
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')
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
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,
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'))
where rownum < 2
union all
select 'top memory process', sid,
username, total_user_mem,
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')
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
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,
total_user_cpu,
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'
and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
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,
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')
where rownum < 2;

cursor c8 is select username, sum(VALUE/100) cpu_usage_sec
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and name like '%CPU used by this session%'
and se.sid = ss.sid
and username is not null
and username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by username
order by 2 desc;
begin
dbms_output.put_line ('Database Version');
dbms_output.put_line ('=================');
for rec in c1
loop
dbms_output.put_line(rec.version);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Hostname');
dbms_output.put_line ('========================');
for rec in c2
loop
dbms_output.put_line(rec.host_name);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('SGA Size (MB)');
dbms_output.put_line ('=======================');
for rec in c5
loop
dbms_output.put_line(rec.sgasize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Database Size (MB)');
dbms_output.put_line ('=======================');
for rec in c6
loop
dbms_output.put_line(rec.dbsize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Instance start-up time');
dbms_output.put_line ('=======================');
for rec in c2 loop
dbms_output.put_line( rec.starttime );
end loop;
dbms_output.put_line( chr(13) );
for b in
(select total, active, inactive, system, killed
from
(select count(*) total from v$session)
, (select count(*) system from v$session where username is null)
, (select count(*) active from v$session where status = 'ACTIVE' and username is not null)
, (select count(*) inactive from v$session where status = 'INACTIVE')
, (select count(*) killed from v$session where status = 'KILLED')) loop
dbms_output.put_line('Active Sessions');
dbms_output.put_line ('=======================');
dbms_output.put_line(b.total || ' sessions: ' || b.inactive || ' inactive,' || b.active || ' active, ' || b.system || ' system, ' || b.killed || ' killed ');
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line( 'Sessions Waiting' );
dbms_output.put_line( chr(13) );
dbms_output.put_line('Count Event Name');
dbms_output.put_line('=======================');
for rec in c4
loop
dbms_output.put_line(rec.cnt||' '||rec.event);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('=======================');
dbms_output.put_line('TOP Physical i/o, logical i/o, memory and CPU processes');
dbms_output.put_line ('=======================');
for rec in c7
loop
dbms_output.put_line (rec.category||': SID '||rec.sid||' User : '||rec.username||': Amount used : '||rec.amt_used||': Percent used: '||rec.pct_used);
end loop;
dbms_output.put_line('=======================');
dbms_output.put_line('TOP CPU users by usage');
dbms_output.put_line ('=======================');
for rec in c8
loop
dbms_output.put_line (rec.username||'-'||rec.cpu_usage_sec);
dbms_output.put_line ('=======================');
end loop;
end;
/

Oracle Top 10 most resource intensive queries

Oracle most resource intensive queries


set pagesize 200
select sql_text,
username,
disk_reads_per_exec,
buffer_gets,
disk_reads,
parse_calls,
sorts,
executions,
rows_processed,
hit_ratio,
first_load_time,
sharable_mem,
persistent_mem,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value,
sql_id
from
(select sql_text ,
b.username ,
round((a.disk_reads/decode(a.executions,0,1,a.executions)),2)
disk_reads_per_exec,
a.disk_reads ,
a.buffer_gets ,
a.parse_calls ,
a.sorts ,
a.executions ,
a.rows_processed ,
100 - round(100 * a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio,
a.first_load_time ,
sharable_mem ,
persistent_mem ,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value,
a.sql_id
from
sys.v_$sqlarea a,
sys.all_users b
where
a.parsing_user_id=b.user_id and
b.username not in ('SYS','SYSTEM','SYSMAN','ORACLE_OCM','DBSNMP','MDSYS','XDB','EXFSYS')
order by 3 desc)
where rownum < 11


-- SYSMAN
-- The SYSMAN user represents the Enterprise Manager super admin account.
-- This EM admin can create and modify other EM admin accounts as well as admin the database
-- -- instance itself.

-- DBSNMP
-- The DBSNMP user is used by EM to monitor the database. EM uses this account to access
-- performance stats about the database.
-- The DBSNMP credentials sometimes referred to as the monitoring credentials.
/



default user list

http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_26.shtml

dbms_sqltune with example

First find out the sqlid from


select sid,serial#,sql_id from v$session where username='user name ';

or

find the top sql in particulate schema

SELECT * FROM
(SELECT
sql_fulltext,
sql_id,
child_number,
disk_reads,
executions,
first_load_time,
last_load_time
FROM v$sql
where PARSING_SCHEMA_NAME='ANUJ'
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10
/




First create the tuning task:


set serveroutput on
set long 10000 longchunksize 5000

DECLARE

my_task_name VARCHAR2(30);

BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => 'c6u1g74vugzxz',
scope => 'COMPREHENSIVE',
time_limit => 3000,
task_name => 'c6u1g74vugzxz_tune',
description => 'Task to tune a query');

END;
/

Execute the task

SYS AS SYSDBA>exec dbms_sqltune.execute_tuning_task('c6u1g74vugzxz_tune');

PL/SQL procedure successfully completed.



Now run the report for task


SYS AS SYSDBA>set long 20000

set longchunksize 20000

set linesize 400

select dbms_sqltune.report_tuning_task('c6u1g74vugzxz_tune') from dual;

Oracle Date's Maths with Anuj

http://anuj-singh.blogspot.co.uk/2012/05/oracle-date-calculations.html