SQL Top wait

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