OS level statistic and TPS

Below are SQL used to give report like Linux Sar.
========================================

DBA_HIST_OSSTAT
  select BEGIN_INTERVAL_TIME, os.* from
  (select
  SNAP_ID,
   cpus1,round(idle1/(idle1+user1+sys1+iowait1+nice1),2)*100 idle,
  round(sys1/(idle1+user1+sys1+iowait1+nice1),2)*100 sys,
  round(user1/(idle1+user1+sys1+iowait1+nice1),2)*100 user1,
  round(nice1/(idle1+user1+sys1+iowait1+nice1),2)*100 nice
  from
  (SELECT SNAP_ID,
  sum(decode(stat_name,'NUM_CPUS',value,0))    cpus1,
  sum(decode(stat_name,'IDLE_TIME',value,0))   idle1,
  sum(decode(stat_name,'USER_TIME',value,0))   user1,
  sum(decode(stat_name,'SYS_TIME',value,0))    sys1,
  sum(decode(stat_name,'IOWAIT_TIME',value,0)) iowait1,
  sum(decode(stat_name,'NICE_TIME',value,0))   nice1
  FROM DBA_HIST_OSSTAT group by SNAP_ID))os , DBA_HIST_snapshot sn
  where os.SNAP_ID=sn.SNAP_ID
order by 1;


col BEGIN_INTERVAL_TIME for a30
set lin 1000 pages 1000
select count(1), to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi')date1, idle,sys,user1,nice from
(
select
SNAP_ID,
 cpus1,round(idle1/(idle1+user1+sys1+iowait1+nice1),2)*100 idle,
 round(sys1/(idle1+user1+sys1+iowait1+nice1),2)*100 sys,
 round(user1/(idle1+user1+sys1+iowait1+nice1),2)*100 user1,
 round(nice1/(idle1+user1+sys1+iowait1+nice1),2)*100 nice
from
(SELECT SNAP_ID,
sum(decode(stat_name,'NUM_CPUS',value,0))    cpus1,
sum(decode(stat_name,'IDLE_TIME',value,0))   idle1,
sum(decode(stat_name,'USER_TIME',value,0))   user1,
sum(decode(stat_name,'SYS_TIME',value,0))    sys1,
sum(decode(stat_name,'IOWAIT_TIME',value,0)) iowait1,
sum(decode(stat_name,'NICE_TIME',value,0))   nice1
FROM   DBA_HIST_OSSTAT group by SNAP_ID))os
     , DBA_HIST_snapshot sn
     , dba_hist_active_sess_history ash
where os.SNAP_ID=sn.SNAP_ID
and sn.SNAP_ID=ash.SNAP_ID
and ash.snap_id=os.snap_id
group by to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi'), idle,sys,user1,nice
order by 1;


col BEGIN_INTERVAL_TIME for a22
col idle for 999
col sys for 999
col user1 for 999
col nice for 999
set lin 1000 pages 1000
col db_name for a22
col cpus1 for 9999
select (select instance_name||'@'||substr(host_name,-(length(host_name))) from v$instance)db_name ,cpus1,sess,date1, idle,sys,user1,nice,sql.cnt ,sql.CPU,sql.ELAPSED,sql.iowait,sql.exec
from
(
select count(1)sess,os.snap_ID ,cpus1, to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi')date1, idle,sys,user1,nice
 from
(
select
SNAP_ID,
 cpus1,round(idle1/(idle1+user1+sys1+iowait1+nice1),2)*100 idle,
 round(sys1/(idle1+user1+sys1+iowait1+nice1),2)*100 sys,
 round(user1/(idle1+user1+sys1+iowait1+nice1),2)*100 user1,
 round(nice1/(idle1+user1+sys1+iowait1+nice1),2)*100 nice
from
(SELECT SNAP_ID,
sum(decode(stat_name,'NUM_CPUS',value,0))    cpus1,
sum(decode(stat_name,'IDLE_TIME',value,0))   idle1,
sum(decode(stat_name,'USER_TIME',value,0))   user1,
sum(decode(stat_name,'SYS_TIME',value,0))    sys1,
sum(decode(stat_name,'IOWAIT_TIME',value,0)) iowait1,
sum(decode(stat_name,'NICE_TIME',value,0))   nice1
FROM   DBA_HIST_OSSTAT group by SNAP_ID))os
     , DBA_HIST_snapshot sn
     , (select * from dba_hist_active_sess_history where user_id in (select user_id from dba_users where username in ('SYS','SYSTEMS'))) ash  
where os.SNAP_ID=sn.SNAP_ID
and sn.SNAP_ID=ash.SNAP_ID
and ash.snap_id=os.snap_id
group by os.SNAP_ID,cpus1,to_char(BEGIN_INTERVAL_TIME,'dd-mon-yyyy hh24:mi'), idle,sys,user1,nice
)aa, (select snap_id,count(1)cnt,round(sum(CPU_TIME_TOTAL/1000000),0)CPU,round(sum(ELAPSED_TIME_TOTAL/1000000),0)ELAPSED
,round(sum(IOWAIT_TOTAL/1000000),0)IOWAIT,round(sum(EXECUTIONS_TOTAL),0)exec from DBA_HIST_SQLSTAT group by snap_id) sql
where sql.snap_id=aa.snap_id order by 3;


---@dsession
set lin 180
select
   rpad(c.name||':',11)||rpad(' current logons='||
   (to_number(b.sessions_current)),20)||'cumulative logons='||
   rpad(substr(a.value,1,10),10)||'highwater mark= '||
   b.sessions_highwater ||'   avg logon per_day= ' ||round(to_number(rpad(substr(a.value,1,10),10))/(sysdate-d.STARTUP_TIME))
   ||' Startup_date= '||d.STARTUP_TIME Information
from
   v$sysstat a,
   v$license b,
   v$database c,
   v$instance d
where
   a.name = 'logons cumulative'
/
set lin 180

WITH hist_snaps

     AS (SELECT instance_number,

                snap_id,

                round(begin_interval_time,'MI') datetime,

                  (  begin_interval_time

                   + 0

                   - LAG (

                        begin_interval_time + 0)

                     OVER (PARTITION BY dbid, instance_number

                           ORDER BY snap_id))

                * 86400      -- seconds per day

                   diff_time -- calculate difference in time from 

                             -- previous snapshot

                   FROM dba_hist_snapshot,

     hist_stats

     AS (SELECT dbid,

                instance_number,

                snap_id,

                stat_name,

                  VALUE

                - LAG (

                     VALUE)

                  OVER (PARTITION BY dbid,instance_number,stat_name

                        ORDER BY snap_id)

                  delta_value -- difference in value from previous 

                              -- snapshot for each stat_name

           FROM dba_hist_sysstat

          WHERE stat_name IN ('user commits', 'user rollbacks'))

  SELECT datetime,

         ROUND (SUM (delta_value) / 3600, 2) "Transactions/s"

     FROM hist_snaps sn, hist_stats st

   WHERE     st.instance_number = sn.instance_number

         AND st.snap_id = sn.snap_id

         AND diff_time IS NOT NULL

GROUP BY datetime

ORDER BY 1 desc;

TPS Related SQL:
=====================================

set pages 9999; 

column c1 heading "start|time" format a20; 

column c2 heading "end|time" format a20; 

column c3 heading "total|undo|blocks|used" format 9,999,999; 

column c4 heading "total|number of|transactions|executed" format 999,999; 

column c5 heading "longest|query|(sec)" format 999,999; 

column c6 heading "highest|concurrent|transaction|count" format 9,999; 

break on report 

compute sum of c3 on report 

compute sum of c4 on report 

set linesize 120 

select 

to_char(begin_time,'dd-mon-yy hh:mi pm') c1, 

to_char(end_time ,'dd-mon-yy hh:mi pm') c2, 

sum(undoblks) c3, 

sum(txncount) c4, 

max(maxquerylen) c5, 

max(maxconcurrency) c6 

from v$undostat 

group by to_char(begin_time,'dd-mon-yy hh:mi pm'), to_char(end_time ,'dd-mon-yy hh:mi pm') 

order by 1 asc; 

select min(begin_time), max(end_time),

sum(case metric_name when 'User Commits Per Sec' then average end) User_Commits_Per_Sec,

sum(case metric_name when 'User Rollbacks Per Sec' then average end) User_Rollbacks_Per_Sec,

sum(case metric_name when 'User Transaction Per Sec' then average end) User_Transactions_Per_Sec,

snap_id

from dba_hist_sysmetric_summary

where trunc(begin_time) > sysdate-7

group by snap_id

order by snap_id;

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

SQL Tune Query

1) To tune particular sql_id.

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => '&&1',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 4000,

                          task_name   => '&&1'||'_manual_tuning_task',

                          description => 'Manual Tuning on 18 march 10');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&&1'||'_manual_tuning_task');

SELECT task_name, status FROM dba_advisor_log WHERE owner = 'METASEA';

SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 1000

SELECT DBMS_SQLTUNE.report_tuning_task('&&1'||'_manual_tuning_task') AS recommendations FROM dual;

SET PAGESIZE 24

undef 1

2) One can use also for tuning Top SQL.

@?/rdbms/admin/sqltrpt.sql 

or

ORACLE_HOME/rdbms/admin/sqltrpt.sql

3) Top SQL finder.

set lin 1000

col "Command Type" for a20

select CTYP "Command Type", OBJ "Name", 0 - EXEM   "Number of Executions" , GETS "Buffer Gets" ,

ROWP "Rows Processed" from (select distinct EXEM, CTYP, OBJ, GETS, ROWP

from ( select decode (S.COMMAND_TYPE ,  2, 'Insert into ' ,  3,'Select from ',  6, 'Update  of  ' ,  7, 'Delete from ' ,

26,'Lock    of  ') CTYP , O.OWNER || '.' || O.NAME    OBJ , sum(0 - S.EXECUTIONS)

EXEM  , sum(S.BUFFER_GETS) GETS  , sum(S.ROWS_PROCESSED) ROWP from V$SQL  S ,

V$OBJECT_DEPENDENCY D , V$DB_OBJECT_CACHE   O

where S.COMMAND_TYPE in (2,3,6,7,26)

and D.FROM_ADDRESS = S.ADDRESS and D.TO_OWNER = O.OWNER

and D.TO_NAME= O.NAME   and O.TYPE = 'TABLE'

--and O.OWNER NOT IN ('SYS','SYSTEM')

and O.OWNER = upper('&&user_name')

group by S.COMMAND_TYPE , O.OWNER  , O.NAME )  )

where ROWNUM <= 25

;

set lin 80