Useful Oracle SQL's

--Run time Session logon and all logon since last reboot.
-------------------------------------------------------------
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


---Another running Sql
set lin 10000
col sid for 999999999
col serial# for  9999999999
col osuer for a20
col SQL_TEXT for a70 wrap
col osuser for a10
col status for a10
--select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a,  v$sqlarea b
--where a.SQL_ADDRESS=b.ADDRESS
--and b.HASH_VALUE = a.SQL_HASH_VALUE
select sid,serial#, status, osuser,b.EXECUTIONS,b.DISK_READS, b.SQL_TEXT from v$session a,  v$sql b
where a.SQL_ADDRESS=b.ADDRESS
and b.HASH_VALUE = a.SQL_HASH_VALUE
order by 6 desc,5 desc
/


COLUMN name FORMAT A20;
col value for 999999999999
set lin 10000

--CLEAR SCREEN;
PROMPT DATABASE BUFFER CACHE STATISTICS:
SELECT
    SUM(DECODE(name, 'physical reads',value,0)) AS misses,
    SUM(DECODE(name, 'db block gets',value,0)) +
    SUM(DECODE(name, 'consistent gets',value,0)) AS hits,
    ROUND(100*(1-SUM(DECODE(name, 'physical reads',value,0))/
     (SUM(DECODE(name, 'db block gets',value,0)) +
    SUM(DECODE(name, 'consistent gets',value,0))) ),2) AS "HIT RATIO"
  FROM v$sysstat
 WHERE name IN
 ('db block gets','consistent gets','physical reads');
PROMPT  DICTIONARY CACHE STATISTICS:
SELECT SUM(getmisses) AS misses,
   SUM(gets) AS "HITS (EXECUTIONS)",
   ROUND(100*(1-(SUM(getmisses)/SUM(gets))),2) AS hit_ratio
  FROM v$rowcache;
PROMPT  LIBRARY CACHE STATISTICS:
SELECT SUM(reloads) AS misses,
   SUM(pins) AS "HITS (EXECUTIONS)",
   ROUND(100*(1-(SUM(reloads)/SUM(pins))),2) AS hit_ratio
  FROM v$librarycache;
PROMPT SORTING STATISTICS:
col VALUE for 9999999999999999
SELECT name, value
  FROM v$sysstat
 WHERE name IN ('sorts (memory)', 'sorts (disk)');




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


--- Sql cost with sql_id
set lin 1000
col c1 heading SQL|ID
col c2 heading Cost format 9,999,999
col c3 heading 'SQL Text' format a200 wrap
select p.sql_id c1,
       p.COST  c2,
       p.cpu_cost ,
       p.time,
       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
and p.sql_id = trim('&sql_id')
order by p.cost desc
/


col c1 heading SQL|ID
col c2 heading Cost format 9,999,999
col c3 heading 'SQL Text' format a200

set lin 10000
select sql_id , round((elapsed_time/1000000)/decode(nvl(executions,0),0,1,executions),5) avg_etime,  
   child_number, executions execs, CPU_TIME,ELAPSED_TIME, to_char(s.sql_text),
   buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio,
   sql_text, LAST_ACTIVE_TIME,SQL_PROFILE,PARSING_SCHEMA_NAME ,ROWS_PROCESSED , LAST_LOAD_TIME
   , disk_reads
from v$sql s
where sql_id = trim('&sql_id');
set lin 100

SET LINESIZE 145
SET PAGESIZE 9999

COLUMN sid                     FORMAT 999            HEADING 'SID'
COLUMN oracle_username         FORMAT a12            HEADING 'Oracle User'     JUSTIFY right
COLUMN os_username             FORMAT a9             HEADING 'O/S User'        JUSTIFY right
COLUMN session_program         FORMAT a18            HEADING 'Session Program' TRUNC
COLUMN session_machine         FORMAT a18             HEADING 'Machine'         JUSTIFY right TRUNC
COLUMN session_pga_memory      FORMAT 9,999,999,999  HEADING 'PGA Memory'
COLUMN session_pga_memory_max  FORMAT 9,999,999,999  HEADING 'PGA Memory Max'
COLUMN session_uga_memory      FORMAT 9,999,999,999  HEADING 'UGA Memory'
COLUMN session_uga_memory_max  FORMAT 9,999,999,999  HEADING 'UGA Memory MAX'

SELECT
    s.sid                sid
  , lpad(s.username,12)  oracle_username
  , lpad(s.osuser,9)     os_username
  , s.program            session_program
  , lpad(s.machine,8)    session_machine
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and 
           sn.statistic# = ss.statistic# and
           sn.name = 'session pga memory')        session_pga_memory
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and 
           sn.statistic# = ss.statistic# and
           sn.name = 'session pga memory max')    session_pga_memory_max
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and 
           sn.statistic# = ss.statistic# and
           sn.name = 'session uga memory')        session_uga_memory
  , (select ss.value from v$sesstat ss, v$statname sn
     where ss.sid = s.sid and 
           sn.statistic# = ss.statistic# and
           sn.name = 'session uga memory max')    session_uga_memory_max
FROM 
    v$session  s
ORDER BY session_pga_memory DESC
/

PRO Object list
break on report
compute sum of NO_OF_SEGS on report
compute sum of MB on report
col TEMP_TBS for a7
col DEFAULT_TABLESPACE for a10
col profile for a15
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
/
set line 80

Few more useful SQL

---Remove all varchar on column
to_number(translate(mb.msisdn,'#+= <=<IDEAXXXXXXXX.?"-*%@!$%^&*$abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',' '),'999999999999')
 between om.msisdn_start and om.msisdn_end


create materialized view dilip.DILIP_user_mv
tablespace DILIP
refresh complete
start with sysdate
next  trunc(sysdate)+1+2/24+35/(24*60) 
----enable query rewrite
as 
SELECT  USER_INFO_ID,POINTS_BALANCE,LOGIN_ID,rnk FROM
(
SELECT user_info_id, points_balance, login_id, RANK() OVER ( ORDER BY points_balance DESC) rnk
FROM dilip.DILIP_user, dilip.DILIP_user123
WHERE user_info_id=id
)
WHERE rownum<=10



CREATE MATERIALIZED VIEW DILIP_LOG_MV   
    TABLESPACE TEST BUILD IMMEDIATE   REFRESH force  
    START WITH sysdate
    NEXT trunc(sysdate)+1+2/24+35/(24*60)   
  AS 
    SELECT OPERATOR_ID,CIRCLE_ID,KEYWORD,COUNT(MSISDN) 
    TOTAL_DOWNLOAD_COUNT     ,COUNT(DISTINCT MSISDN) 
    UNIQUE_USER_COUNT,trunc(RECVAT) recvat     
    FROM DILIP_LOG_report     
    WHERE trunc(RECVAT) between trunc(sysdate-60)     
    and trunc(sysdate-1)     GROUP BY OPERATOR_ID,CIRCLE_ID,
    KEYWORD,trunc(RECVAT)   


----Duplicate rows calculation based on combination of columns
--- Good performance most of time if we have index created on ------ columns inside where clause. select count(*) from hw_content_master t1
where exists (select 'x' from dilip_master t2
                 where t1.cont_id = t2.cont_id
                 and t1.rowid > t2.rowid
                -- and t1.active =1
              )   


----Rank Query
select mb.* from
(
SELECT user_info_id, points_balance, login_id, RANK() OVER ( ORDER BY points_test DESC) rnk
FROM dilip_user a, user_info b
WHERE a.user_info_id=b.id
) mb
mb.rownum < 11


---- All Foreign key reference on specific table

select
        a.tt,
        a.owner,
        b.table_name,
        a.constraint_name,
        b.column_name,
        b.position,
        a.r_constraint_name,
        c.column_name,
        c.position,
        c.table_name r_table_name,
        a.r_owner
from
        (select
                owner,
                constraint_name,
                r_constraint_name,
                r_owner,1 tt
        from
                dba_constraints
        where
                owner=upper('&&owner')
                and table_name=upper('&&table_name')
                and constraint_type!='C'
        union
        select
                owner,
                constraint_name,
                r_constraint_name,
                r_owner,2
        from
                dba_constraints
        where
                (r_constraint_name,r_owner) in
                (select
                        constraint_name,
                        owner
                from
                        dba_constraints
                where
                        owner=upper('&owner')
                        and table_name=upper('&table_name'))
        ) a,
        dba_cons_columns b,
        dba_cons_columns c
where
        b.constraint_name=a.constraint_name
        and b.owner=a.owner
        and c.constraint_name=a.r_constraint_name
        and c.owner=a.r_owner
        and b.position=c.position
order   by 1,2,3,4,5

---Traverse from child through parent

select * from (
SELECT rpad('*',2*level,'*') || username ,sys_connect_by_path( userid, '/' )rownum1 ,level rank,userid,username,user_level_higher 
from dilip_users  --where user
start with userid=10001  ---10001
CONNECT BY  userid=  prior user_level_higher
)  --where userid = 10002

where rank = 2

Parameter need to Change for Performance Tuning

Below setting can Improve overall performance of whole DB's

Default values 

optimizer_index_cost_adj=100  --> Low value means index scan is less costly (Low value force index use)
optimizer_index_caching=0     --> High cache means more chance for nested loop first in below loop style

• Nested loop joins
• Hash join access
• Full-index scans
• Full-table scan access 

I have a query that performs bad (did not return after more than one minute). 
After I issued the following if worked well.
alter session set optimizer_index_cost_adj=5
alter session set optimizer_index_caching=90

Can use hint in sql statement.

select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . . 

select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .
 

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 Wise CPU Usage

SQL Wise CPU Usage

select
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds
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   ss.status='ACTIVE'
and   ss.username is not null
order by VALUE desc;

col type for a10
select * from (
select
ash.SQL_ID , ash.SQL_PLAN_HASH_VALUE Plan_hash, aud.name type,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) -
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) "WAIT" ,
sum(decode(ash.session_state,'WAITING', decode(wait_class, 'User I/O',1,0),0)) "IO" ,
sum(decode(ash.session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash,
audit_actions aud
where SQL_ID is not NULL
and ash.sql_opcode=aud.action
and ash.sample_time > sysdate - &minutes /( 60*24)
group by sql_id, SQL_PLAN_HASH_VALUE , aud.name
order by sum(decode(session_state,'ON CPU',1,1)) desc
) where rownum < 10
/

SQL to check Stale statistics

Many times Oracle SQL plan get change which will cause abnormal behavior, most probable reason could be stale statistics marked by Oracle. Below is useful queries to identify the Objects and gather stats for those Objects. Oracle do have auto jobs which takes care stats gather requirement in defined Windows. One need to calibrate window based on requirement as stats gather as this is resource Intensive process, and should not conflict Business transaction. Oracle include this nightly maintenance Jobs by default scheduled in night time. Stats gather is vast topic, one need carefully touch stats gather on PROD environment.

---Using Anonymous PL/SQL block
SQL> SET SERVEROUTPUT ON
  DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_database_stats(objlist=>ObjList, options=>’LIST STALE’);
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || ‘.’ || ObjList(i).ObjName || ‘ ‘ || ObjList(i).ObjType || ‘ ‘ || ObjList(i).partname);
END LOOP;
END;
/

---Oracle 10g Onwards.

col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';

Query to find to Soft Parse and Hard Parse Ratio

Query to find to Soft Parse and Hard Parse ratio

select 'Soft Parses ' "Ratio"

,round(

((select sum(value) from v$sysstat where name = 'parse count (total)')

- (select sum(value) from v$sysstat where name = 'parse count (hard)'))

/(select sum(value) from v$sysstat where name = 'execute count')

*100,2)||'%' "percentage"

from dual

union

select 'Hard Parses ' "Ratio"

,round(

(select sum(value) from v$sysstat where name = 'parse count (hard)')

/(select sum(value) from v$sysstat where name = 'execute count')

*100,2)||'%' "percentage"

from dual

union

select 'Parse Failure ' "Ratio"

,round(

(select sum(value) from v$sysstat where name = 'parse count (failures)')

/(select sum(value) from v$sysstat where name = 'parse count (total)')

*100,2)||'%' "percentage"

from dual

/

select sql_text from v$sqlarea where version_count > 1;

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