Top oracle sql s

TOP CPU SQL Statements

You can find TOP CPU SQL for last 24 hours with following script.

select * from (
select ss.sql_text,
    a.SQL_ID, 
    sum(CPU_TIME_DELTA), 
    sum(DISK_READS_DELTA),
    count(*)
from 
    DBA_HIST_SQLSTAT a, dba_hist_snapshot s,v$sql ss
where
 s.snap_id = a.snap_id and a.sql_id=ss.sql_id
 and s.begin_interval_time > sysdate -1
    group by 
    ss.sql_text,a.SQL_ID
order by 
    sum(CPU_TIME_DELTA) desc)
where rownum<20;

TOP IO SQL

You can find TOP IO SQL for last 24 hours with following script.

select * from 
(
SELECT /*+LEADING(x h) USE_NL(h)*/ 
       h.sql_id
,      SUM(10) ash_secs
FROM   dba_hist_snapshot x
,      dba_hist_active_sess_history h
WHERE   x.begin_interval_time > sysdate -1
AND    h.SNAP_id = X.SNAP_id
AND    h.dbid = x.dbid
AND    h.instance_number = x.instance_number
AND    h.event in  ('db file sequential read','db file scattered read')
GROUP BY h.sql_id
ORDER BY ash_secs desc )
where rownum<10;

You can find TOP 10 SQL for last 1 hour with following script.

select * from (
select active_session_history.sql_id,
 dba_users.username,
 sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
 dba_users
where 
active_session_history.sample_time between sysdate -  1/24  and sysdate
  and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
 group by active_session_history.sql_id,sqlarea.sql_text, dba_users.username
 order by 4 desc )
where rownum <11;

You can check Top Oracle database wait events in Cache which is v$ queries with below script.

select  wait_class,
       sum(total_waits), sum(time_waited)
    from gv$session_wait_class
    where wait_class !='Idle'
    group by wait_class
    order by 3 desc;

You can check Top Oracle database wait events from Active session history which is v$active_session_history queries with below script.

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from gv$active_session_history active_session_history
where active_session_history.event is not null
group by active_session_history.event
order by 2 desc)
where rownum <= 10;

You can check Top Oracle database wait events at specific intervals. You should type date and SNAP_ID

select snap_id,begin_interval_time,end_interval_time
from dba_hist_snapshot
where to_char(begin_interval_time,'DD-MON-YYYY')='20-FEB-2019'
and EXTRACT(HOUR FROM begin_interval_time) between 8 and 10;

select * from (
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from dba_hist_active_sess_history active_session_history
where event is not null
and SNAP_ID between 34411 and 34431
group by active_session_history.event
order by 2 desc)
where rownum<10;

To find any SQL’s SQL_ID and other SQL informations in the cache, use following SQL. Following query will find if related SQL exists in the cache, if not exists it will not find.

select * from gv$sql where sql_text like '%DEVECI%';

To search any SQL historical in Oracle database you may use following SQL. Just change begin Interval time column and SQL_TEXT column.

SELECT STAT.SQL_ID, SQL_TEXT, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, ELAPSED_TIME_DELTA, 
STAT.SNAP_ID, SS.END_INTERVAL_TIME, EXECUTIONS_DELTA FROM DBA_HIST_SQLSTAT STAT, 
DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS WHERE STAT.SQL_ID = TXT.SQL_ID AND
STAT.DBID = TXT.DBID AND SS.DBID = STAT.DBID AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER 
AND STAT.SNAP_ID = SS.SNAP_ID AND
SS.BEGIN_INTERVAL_TIME >= sysdate-31 AND
UPPER(SQL_TEXT) LIKE '%DEVECI%' ORDER BY ELAPSED_TIME_DELTA DESC;

You can find average Active session of database with following script.

SELECT 'Load',
CASE
WHEN ( ( CAST (end_time.sample_time AS DATE)
- CAST (start_time.sample_time AS DATE))
* 24
* 60
* 60) = 0
THEN
0
ELSE
ROUND (
( COUNT (ash.sample_id)
/ ( ( CAST (end_time.sample_time AS DATE)
- CAST (start_time.sample_time AS DATE))
* 24
* 60
* 60)),
2)
END
AS Average_Active_Session
FROM (SELECT MIN (sample_time) sample_time
FROM v$active_session_history ash
WHERE sample_time BETWEEN SYSDATE - 1 / 1440 AND SYSDATE) start_time,
(SELECT MAX (sample_time) sample_time
FROM gv$active_session_history
WHERE sample_time BETWEEN SYSDATE - 1 / 1440 AND SYSDATE) end_time,
gv$active_session_history ash
WHERE ash.sample_time BETWEEN start_time.sample_time
AND end_time.sample_time
GROUP BY end_time.sample_time, start_time.sample_time;

To list all User Sessions not Background, use following scripts. This script will list you just only User type sessions and their detais.

select * FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+)
and s.TYPE ='USER' and s.username!='SYS';

You can list how many Active and Inactive User sessions are in the Oracle database with following script.

select count(*) FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+)
and s.TYPE ='USER' and s.username!='SYS';

You can list only Active User sessions without sys user with following script

select count(*) FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+)
and s.TYPE ='USER' and s.username!='SYS' and status='ACTIVE';

You can list only Inactive User sessions without sys user with following script

select count(*) FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+)
and s.TYPE ='USER' and s.username!='SYS' and status='INACTIVE';

You can list all user sessions which are ACTIVE state more than 600 Second with following script.

select count(*) FROM gv$session s, gv$process p
WHERE s.paddr = p.addr(+)
and s.TYPE ='USER' and s.username!='SYS' and status='ACTIVE' and last_call_et > 600

If you don’t use bind variables in the SQL queries then Oracle will know similar sqls differently like following.

select * from customer where id=63;

select * from customer where id=34;

Above queries are almost same, just id variable is different. But Oracle optimizer will evaluate these SQLs like different SQL.

If you use bind variable instead of literal like following then Oracle will evaluate as same SQL and will use same execution plan and won’t be hard parse in Oracle.

variable SYS_B_0 number;
exec :SYS_B_0:= 63

select * from customer where id= :SYS_B_0;

You can find out all queries which are not using bind variables with following script. You can see more queries with changing row nums of script.

Select * 
from (
With subs as
(SELECT /*+ materialize */
m.sql_id, k.*, m.SQL_TEXT, m.SQL_FULLTEXT
FROM (SELECT inst_id,
parsing_schema_name AS user_name,
module,
plan_hash_value,
COUNT(0) copies,
SUM(executions) executions,
SUM(round(sharable_mem / (1024 * 1024), 2)) sharable_mem_mb
FROM gv$sqlarea
WHERE executions < 5
AND kept_versions = 0
GROUP BY inst_id, parsing_schema_name, module, plan_hash_value
HAVING COUNT(0) > 10
ORDER BY COUNT(0) DESC) k
LEFT JOIN gv$sqlarea m
ON k.plan_hash_value = m.plan_hash_value
WHERE k.plan_hash_value > 0)
select distinct ki.inst_id,
t.sql_id,
ki.sql_text,
t.plsql_procedure,
ki.user_name,
sum(ki.copies) copies,
sum(ki.executions) executions,
sum(ki.sharable_mem_mb) sharable_mem_mb
from (select sql_id,
program_id,
program_line#,
action,
module,
service,
parsing_schema_name,
round(buffer_gets / decode(executions, 0, 1, executions)) buffer_per_Exec,
row_number() over(partition by sql_id order by program_id desc, program_line#) sira,
decode(program_id,
0,
null,
owner || '.' || object_name || '(' || program_line# || ')') plsql_procedure
from gv$sql a, dba_objects b
where a.program_id = b.object_id(+)) t,
subs ki
where ki.sql_id = t.sql_id
and t.sira = 1
group by ki.inst_id,
t.sql_id,
ki.sql_text,
t.plsql_procedure,
ki.user_name
order by sum(ki.executions) desc
)
where rownum < 51;   

You will find object detail of queries which is not using bind variables.

With subs as
(SELECT /*+ materialize */
   m.sql_id, k.*, m.SQL_TEXT, m.SQL_FULLTEXT
    FROM (SELECT inst_id,
                 parsing_schema_name AS user_name,
                 module,
                 plan_hash_value,
                 COUNT(0) copies,
                 SUM(executions) executions,
                 SUM(round(sharable_mem / (1024 * 1024), 2)) sharable_mem_mb
            FROM gv$sqlarea
           WHERE executions < 5
             AND kept_versions = 0
           GROUP BY inst_id, parsing_schema_name, module, plan_hash_value
          HAVING COUNT(0) > 10
           ORDER BY COUNT(0) DESC) k
    LEFT JOIN gv$sqlarea m
      ON k.plan_hash_value = m.plan_hash_value
   WHERE k.plan_hash_value > 0)
select *
  from (select sql_id,
               program_id,
               program_line#,
               action,
               module,
               service,
               parsing_schema_name,
               round(buffer_gets / decode(executions, 0, 1, executions)) buffer_per_Exec,
               row_number() over(partition by sql_id order by program_id desc, program_line#) lines,
               decode(program_id,
                      0,
                      null,
                      owner || '.' || object_name || '(' || program_line# || ')') plsql_procedure
          from gv$sql a, dba_objects b
         where a.program_id = b.object_id(+)) t,
       subs ki
where ki.sql_id = t.sql_id
   and lines = 1;

You can access the third Oracle DBA scripts related to Blocking Sessions and Lock Kill Scripts with following link.

Find Blocking Sessions and Lock Kill Scripts & Locked Objects | Oracle DBA Scripts All in One -3