col c1 heading 'Average Waits for|Full scan Read I/O' format 9999.99
col c2 heading 'Average Waits for|Index Read I/O' format 9999.99
col c3 heading 'Percent of| I/O Waits |for Full scan Read I/O' format 9999.99
col c4 heading 'Average Waits |for Full scan Read I/O' format 9999.99
col c4 heading 'starting values for optimize_index_cost_adj' format 9999.99
select
a.AVERAGE_WAIT,
b.AVERAGE_WAIT,
a.TOTAL_WAITS/(a.TOTAL_WAITS+b.TOTAL_WAITS),
b.TOTAL_WAITS/(a.TOTAL_WAITS+b.TOTAL_WAITS),
(a.AVERAGE_WAIT/b.AVERAGE_WAIT)*100
from v$system_event a,
v$system_event b
where a.EVENT = 'db file scattered read'
and a.EVENT = 'db file sequential read'
-- space
/* SQL Analyze(110,1) */ select profile, USERNAME, count(*)NO_OF_SEGS ,sum(b.bytes)/1024/1024 MB,
DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE TEMP_TBS ,CREATED
,ACCOUNT_STATUS from dba_users a, dba_segments b
where a.username=b.owner(+)
group by USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,CREATED,ACCOUNT_STATUS,profile
order by created,NO_OF_SEGS desc,mb desc
--- Sql cost with sql_id--------------
col c1 heading SQL|ID
col c2 heading Cost format 9,999,999
col c3 heading 'SQL Text' format a200
select p.sql_id c1,
p.cost c2,
/*to_char(*/s.sql_text/*) c3*/
from
dba_hist_sql_plan p,
dba_hist_sqltext s
where p.id=0
and p.sql_id = s.sql_id
and p.cost is not null
order by p.cost desc;
sql history with r. to. date
select
to_char(s.begin_interval_time,'mm-dd hh24') c1,
p.sql_id ,
p.executions_delta ,
p.buffer_gets_delta ,
p.disk_reads_delta ,
p.iowait_delta,
p.apwait_delta,
p.ccwait_delta
from
dba_hist_sqlstat p,
dba_hist_snapshot s
where p.snap_id = s.snap_id;
Cumulative Usage od database indexes
select
trunc(s.begin_interval_time),
p.object_name,
sum(t.disk_reads_total),
sum(t.rows_processed_total)
from
dba_hist_sql_plan p,
dba_hist_sqlstat t,
dba_hist_snapshot s
where p.sql_id = t.sql_id
and t.snap_id = s.snap_id
and p.object_type like '%INDEX%'
group by trunc(s.begin_interval_time),
p.object_name;
-- top sql in last 10 min
-------------------------
select
sql_id,
count(*),
round(count(*)/sum(count(*)) over (),2) "% load" from
v$active_session_history a
where a.SAMPLE_TIME > sysdate - 10/24/60
and a.SESSION_TYPE <> 'BACKGROUND'
group by sql_id
--- USER wose CPU usage----
SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage"
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC;
--- same sql in library cache
select
b.sql_id,
count(*),
round(count(*)/sum(count(*)) over (),2) from
v$sql a,
dba_hist_active_sess_history b
where a.SQL_ID = b.sql_id
and a.SQL_FULLTEXT like '%orders%'
group by b.sql_id
order by 2 desc
--- wait on data file useful for striping object
select
f.file_name,
count(*),
sum(h.TIME_WAITED)
from
v$active_session_history h,
dba_data_files f
where h.CURRENT_FILE#=f.file_id
group by f.file_name
order by 3 desc
--- list of resource on high demand in last 1 hour
select
e.NAME,
sum(h.WAIT_TIME + h.TIME_WAITED)
from
v$active_session_history h,
v$event_name e
where h.EVENT_ID=e.EVENT_ID
and e.WAIT_CLASS<>'Idle'
group by e.NAME
order by 2 desc
;
SELECT C.SQL_TEXT,
B.NAME,
COUNT(*),
SUM(TIME_WAITED)
FROM v$ACTIVE_SESSION_HISTORY A,
v$EVENT_NAME B,
v$SQLAREA C
WHERE A.SAMPLE_TIME BETWEEN '10-JUL-04 09:57:00 PM' AND
'10-JUL-04 09:59:00 PM' AND
A.EVENT# = B.EVENT# AND
A.SESSION_ID= 123 AND
A.SQL_ID = C.SQL_ID
GROUP BY C.SQL_TEXT, B.NAME
-- object and there waits
select
o.owner,
o.object_name,
o.object_type,
sum(h.WAIT_TIME + h.TIME_WAITED)
from
v$active_session_history h,
v$event_name e,
dba_objects o
where h.EVENT_ID=e.EVENT_ID
and h.CURRENT_OBJ#=o.object_id
and e.WAIT_CLASS<>'Idle'
group by o.owner,
o.object_name,
o.object_type
order by 4 desc
-- space managements object adviced for shrink orginaiosed
create or replace function bfsfun (so in varchar2,
stype in varchar2 default null )
return bfsset pipelined
is
o bfs := bfs(null,null,null,null,null,null,null,null);
fs1_b number;
fs2_b number;
fs3_b number;
fs4_b number;
fs1_b1 number;
fs2_b1 number;
fs3_b1 number;
fs4_b1 number;
fulb number;
fulb1 number;
u_b number;
u_b1 number;
begin
/*create type bfs as object
(
so varchar2(30),
st varchar2(30),
sn varchar2(100),
fs1 number,
fs2 number,
fs3 number,
fs4 number,
fb number
);
create type bfsset as table of bfs;
*/
for rec in (
select s.owner,s.segment_name,s.segment_type from dba_segments s
where owner = so and s.segment_type = nvl(stype,s.segment_type ))
loop
dbms_space.space_usage
(segment_owner => rec.owner,
segment_name => rec.segment_name,
segment_type => rec.segment_type,
fs1_bytes => fs1_b,
fs1_blocks => fs1_b1,
fs2_bytes => fs2_b,
fs2_blocks => fs2_b1,
fs3_bytes => fs3_b,
fs3_blocks => fs3_b1,
fs4_bytes => fs4_b,
fs4_blocks => fs4_b1,
full_bytes => fulb,
full_blocks => fulb1,
unformatted_blocks => u_b1,
unformatted_bytes => u_b
);
o.so:=rec.owner;
o.st:= rec.segment_type;
o.sn:=rec.segment_name;
o.fs1:=fs1_b1;
o.fs2:=fs2_b1;
o.fs3:=fs3_b1;
o.fs4:=fs4_b1;
o.fb:=fulb1;
pipe row(o);
end loop;
return;
end ;
0-25 25-50 50-75 75-100% free space Full Blocks
select * from
table(bfsfun('&un','TABLE'))
order by fs4 desc
--
alter table t enable row movement;
alter table t shrink space compact;
cascade if index also used
