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$
Wednesday, 2 February 2011
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;
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;
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;
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
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;
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;
Subscribe to:
Posts (Atom)
