oracle main top active session and sql causing slowness

  

prompt** /* Current Active sql   */


-- active 

col sql_id format a20
SELECT
    s.sql_id,
    ss.elapsed_time / 1000000 AS elapsed_seconds,
    ss.cpu_time / 1000000 AS cpu_seconds,
    ss.disk_reads,
    ss.buffer_gets,
    ss.executions,
    ROUND(ss.elapsed_time / 1000000 / NULLIF(ss.executions, 0), 4) AS avg_elapsed_seconds,
    ROUND(ss.cpu_time / 1000000 / NULLIF(ss.executions, 0), 4) AS avg_cpu_seconds
FROM
    v$sql s 
    JOIN v$sqlstats ss ON s.sql_id = ss.sql_id
WHERE
    ss.elapsed_time > 1000000 -- Adjust the threshold as needed (microseconds)
  and s.sql_id in ( select sql_id from v$session where status='ACTIVE') 
ORDER BY
    ss.elapsed_time DESC;


set line 200
col username for a20
select * from ( 
 select a.sid , a.username  , round ( ( b.consistent_gets+b.block_gets-b.physical_reads) / ( b.consistent_gets+b.block_gets) *100,2) HitRatio , 
 b.block_gets , b.consistent_gets , b.physical_reads ,  b.block_changes , b.consistent_changes 
 from v$session a , v$sess_io b 
 where a.sid=b.sid  and ( b.consistent_gets+b.block_gets) > 0 
 and username is not null   and a.status='ACTIVE'
 order by HitRatio  ,a.sid , a.username   ) 
 where rownum < 20 
  order by HitRatio ;


col DTS format a22
col inst_sid heading "INST_ID|:SID" format a7
col username format a15
col machine format a12
col sql_exec_start   heading "SQL|START|D HH:MM:SS" format a11
col sql_id format a13
col module format a16
col event format a33
col SEQ# format 99990
col wait_sec heading "WAIT|(SEC)" format 99999

select inst_id||chr(58)||sid as inst_sid
   ,username
   ,case
      when machine like '%\%' then substr(machine,(instr(machine,'\',1)+1),length(machine)) -- WINDOWS HOST NAME FORMANT'
      when machine like '%.%' then substr(machine,1,(instr(machine,'.',1)-1))               -- UNIX HOST NAME FORMANT
      else machine
   end machine
   ,(sysdate - sql_exec_start) day(1) to second(0) as sql_exec_start
   ,sql_id
   ,substr(module,1,15) module
   ,substr
      (case time_since_last_wait_micro
         when 0 then (case wait_class when 'Idle' then '*IDLE* '||event else event end)
         else 'ON CPU'
         end
      ,1,33) event
   ,seq#
   ,(case time_since_last_wait_micro
      when 0 then wait_time_micro
      else time_since_last_wait_micro
      end) /1000000 wait_sec
from gv$session
where inst_id||chr(58)||sid <> sys_context ('USERENV','INSTANCE')||chr(58)||sys_context ('USERENV','SID')
   and username is not null
   and status='ACTIVE'
order by sql_exec_start,
   username,
   sql_id,
   machine,
   sid;



-- Active  Transactions used blocks  
 
col name format a10
col username format a8
col osuser format a8
col start_time format a17
col status format a12
tti 'Active transactions'
select s.sid,username,t.start_time, r.name, t.used_ublk "USED BLKS",
decode(t.space, 'YES', 'SPACE TX',
decode(t.recursive, 'YES', 'RECURSIVE TX',
decode(t.noundo, 'YES', 'NO UNDO TX', t.status)
)) status
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s
where t.xidusn = r.usn
and t.ses_addr = s.saddr
/



Top Query with high elapsed time
--- Queries in last 1 hour ( Run from Toad, for proper view)
Select
module,parsing_schema_name,inst_id,sql_id,CHILD_NUMBER,sql_plan_baseline,sql_profile,plan_hash_value,sql_fulltext,
to_char(last_active_time,'DD/MM/YY HH24:MI:SS' ),executions, elapsed_time/executions/1000/1000,
rows_processed,sql_plan_baseline from gv$sql where last_active_time>sysdate-1/24
and executions <> 0 order by elapsed_time/executions desc ;






-- in memory 
set line 200 pages 200
col module format a20 
col sql_id format a15 
col sql_text format a50
select s.module,sql_text , s.sid , s.sql_id
from v$sqltext_with_newlines t,V$SESSION s
where t.address = s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username is not null
order by s.sid,t.piece;

 

set line 200 pages 200
col username for a20
select s.sid, s.username,optimizer_mode,hash_value  ,  s.sql_id
address,cpu_time,elapsed_time,sql_text
from v$sqlarea q, v$session s
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.username is not null;


set line 200 pages 200
col username for a20
select s.inst_id, s.sid, s.serial#, s.sql_id , s.username ,  trunc( cpu_time/1000000)/60,2) cpu_time , 
trunc( elapsed_time /1000000)/60,2) minutes  , sql_text  from  v$sqlarea a,  v$session s 
where a.sql_id=s.sql_id and  s.status='ACTIVE'  order by trunc( elapsed_time /1000000)/60,2)  desc ; 



select s.sid,s.serial#,s.username,s.sql_id,q.sql_text  
from v$sqlarea q ,v$session s
where s.sql_hash_value = q.hash_value
and s.sql_address = q.address
and s.username is not null;



prompt** /*get top sql causing slowness  */

col sql_id format a20
SELECT
    s.sql_id,
    ss.elapsed_time / 1000000 AS elapsed_seconds,
    ss.cpu_time / 1000000 AS cpu_seconds,
    ss.disk_reads,
    ss.buffer_gets,
    ss.executions,
    ROUND(ss.elapsed_time / 1000000 / NULLIF(ss.executions, 0), 4) AS avg_elapsed_seconds,
    ROUND(ss.cpu_time / 1000000 / NULLIF(ss.executions, 0), 4) AS avg_cpu_seconds
FROM
    v$sql s
    JOIN v$sqlstats ss ON s.sql_id = ss.sql_id
WHERE
    ss.elapsed_time > 1000000 -- Adjust the threshold as needed (microseconds)
ORDER BY
    ss.elapsed_time DESC;




col sql_id format a20
SELECT
    s.sql_id,
    ss.elapsed_time / 1000000 AS elapsed_seconds,
    ss.cpu_time / 1000000 AS cpu_seconds,
    ss.disk_reads,
    ss.buffer_gets,
    ss.executions,
    ROUND(ss.elapsed_time / 1000000 / NULLIF(ss.executions, 0), 4) AS avg_elapsed_seconds,
    ROUND(ss.cpu_time / 1000000 / NULLIF(ss.executions, 0), 4) AS avg_cpu_seconds
FROM
    v$sql s
    JOIN v$sqlstats ss ON s.sql_id = ss.sql_id
WHERE
    ss.elapsed_time > 1000000 -- Adjust the threshold as needed (microseconds)
group by  s.sql_id,ss.elapsed_time ,  ss.cpu_time, ss.disk_reads, ss.buffer_gets,ss.executions,ss.cpu_time
ORDER BY
    ss.elapsed_time DESC;



-- Identify top SQL statements by CPU and IO time
SELECT sql_id, sql_text, executions, elapsed_time / 1000000 AS elapsed_sec, cpu_time / 1000000 AS cpu_sec,
       buffer_gets, disk_reads, sql_fulltext
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC;


################

prompt** /*get top  long running sessions */


Checking sql monitoring report

 


exec dbms_output.put_line('========== Session Details ==========');
set feedback on
set lines 300
set pages 1000
column TERMINAL format a30
column USERNAME format a15
column OSUSER format a20
column MACHINE format a20
column PROGRAM format a30
select inst_id,sid, serial#  ,  last_call_et   ,username,osuser,machine,terminal,program,status,to_char(logon_time, 'dd-mon-yy hh24:mi:ss') , event, blocking_session , sql_id 
from gv$session 
where TYPE='USER'  and username not like 'SYS%'  and status='ACTIVE'
order by last_call_et desc  , LOGON_TIME, username;

set line 200 
col username format a20
col machine format a20
col program format a20
col event format a20
SELECT s.sid,
       s.serial#,
       s.username,
       s.machine,
       s.program,
       w.event,
       w.seconds_in_wait
  FROM v$session s
       JOIN v$session_wait w ON s.sid = w.sid
 WHERE w.event NOT LIKE 'SQL*Net%'
   AND w.event NOT LIKE 'rdbms%'
   AND w.event NOT LIKE 'Streams%'
   AND w.event NOT LIKE 'PL/SQL%'
   AND w.event NOT LIKE 'JOX%'
   AND w.event NOT LIKE 'Streams AQ%'
 ORDER BY w.seconds_in_wait DESC;




Check progress and status of alter Shrink Space command


-- Following command will search alter table command and give you output how much it covered according to table size in first command.
-- GB_read give you how much it covered yet.
select a.event, a.WAIT_TIME, c.SQL_TEXT,
c.PHYSICAL_READ_BYTES / 1024 / 1024 / 1024 "GB_READ",
c.PHYSICAL_WRITE_BYTES / 1024 / 1024 / 1024 "GB_WRITE"
from v$session_wait a , v$session b , v$sql c
where UPPER(c.SQL_TEXT) like UPPER('%ALTER TABLE%')
and a.sid = b.sid
and b.SQL_ID = c.SQL_ID;

--If you have session id of session from which command running then use following command:
select a.event, a.WAIT_TIME, c.SQL_TEXT,
c.PHYSICAL_READ_BYTES / 1024 / 1024 / 1024 "GB_READ",
c.PHYSICAL_WRITE_BYTES / 1024 / 1024 / 1024 "GB_WRITE"
from v$session_wait a , v$session b , v$sql c
where a.SID =
and a.sid = b.sid
and b.SQL_ID = c.SQL_ID;


################

prompt** /*get  waits  */


col evet format a30
select event , sum(seconds_in_wait)  from v$session_wait group by event order by 2 ; 


col evet format a30
select event , sum(seconds_in_wait)  from v$session  group by event order by 2 ; 



select event , sum(time_waited) ,   sum(total_waits) ,   sum(total_timeouts) from v$system_event group by event order by 2 desc fetch first 30 rows only ;


-- Check for waits
SELECT event, total_waits, time_waited
FROM v$session_event
WHERE event NOT LIKE '%SQL*Net%'
ORDER BY time_waited DESC;





/* Which Database Objects Experienced the Most Number of Waits in the Past One Hour */

set line 200 
set linesize 120
col event format a40
col object_name format a40

select * from 
(
  select dba_objects.object_name,
 dba_objects.object_type,
active_session_history.event,
 sum(active_session_history.wait_time +
  active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
    dba_objects
 where 
active_session_history.sample_time between sysdate - 1/24 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
 group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
 order by 4 desc)
where rownum < 6;




################

prompt** /*get  longopps  */


SELECT OPNAME,TARGET,SID,SOFAR,TOTALWORK,TIME_REMAINING, message  FROM V$SESSION_LONGOPS where time_remaining>0

/




################

prompt** /*get  locks */


set linesize 110
col BLOCKING_STATUS format a110
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE
|| ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING '
|| S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID=L1.SID AND S2.SID=L2.SID
AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID
AND L1.BLOCK > 0 AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2;


select inst_id , sid , blocking_session , final_blocking_session , seconds_in_wait from gv$session where  blocking_session is not null  and seconds_in_wait > 5 ;

select sysdate, s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;





exec dbms_output.put_line('========== Session Details ==========');
set feedback on
set lines 300
set pages 1000
column TERMINAL format a30
column USERNAME format a15
column OSUSER format a20
column MACHINE format a20
column PROGRAM format a30
select inst_id,sid, serial#  ,  last_call_et   ,username,osuser,machine,terminal,program,status,to_char(logon_time, 'dd-mon-yy hh24:mi:ss') , event, blocking_session , sql_id   , seconds_in_wait
from gv$session 
where TYPE='USER'  and username not like 'SYS%'
and event like '%row lock%' 
order by last_call_et desc  , LOGON_TIME, username;



exec dbms_output.put_line('========== blocking sessions  ==========');
set feedback on
set lines 300
set pages 1000
column TERMINAL format a30
column USERNAME format a15
column OSUSER format a20
column MACHINE format a20
column PROGRAM format a30
select inst_id,sid, serial#  ,  last_call_et   ,username,osuser,machine,terminal,program,status,to_char(logon_time, 'dd-mon-yy hh24:mi:ss') , event, blocking_session , sql_id   , seconds_in_wait
from gv$session 
where  sid in  ( select  distinct final_blocking_session from gv$session  where final_blocking_session is not  null ) 
order by last_call_et desc  , LOGON_TIME, username;





exec dbms_output.put_line('========== Session Details ==========');
set feedback on
set lines 300
set pages 1000
column TERMINAL format a30
column USERNAME format a15
column OSUSER format a20
column MACHINE format a20
column PROGRAM format a30
select inst_id,sid, serial#  ,  last_call_et   ,username,osuser,to_char(logon_time, 'dd-mon-yy hh24:mi:ss') , event, blocking_session , sql_id   , seconds_in_wait
from gv$session 
where TYPE='USER'  and username not like 'SYS%'
and event like '%row lock%' 
order by last_call_et desc  , LOGON_TIME, username;




alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
SELECT  vp.spid,c.inst_id,b.session_id sid,c.serial#,a.object_name,
       a.owner object_owner,
       Decode(b.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) locked_mode,
        b.oracle_username,
        c.program,
        d.event,
        c.status,
        c.last_call_et,
        c.sql_address,
        e.sql_text, c.logon_time
FROM   all_objects a,
       gv$locked_object b,
       gv$session c,
  gv$session_wait d,
gv$sqlarea e,
gv$process vp
WHERE  a.object_id = b.object_id
and b.session_id=c.sid
and c.sid=d.sid and c.paddr=vp.addr
and e.address=c.sql_address and lower(d.event) like '%enq%'
ORDER BY 1;


alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
SELECT  vp.spid,c.inst_id,b.session_id sid,c.serial#,a.object_name,
a.owner object_owner,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.oracle_username,
c.program,
d.event,
c.status,
c.last_call_et,
c.sql_address,
e.sql_text, c.logon_time
FROM   all_objects a,
gv$locked_object b,
gv$session c,
gv$session_wait d,
gv$sqlarea e,
gv$process vp
WHERE  a.object_id = b.object_id
and b.session_id=c.sid
and c.sid=d.sid and c.paddr=vp.addr
and e.address=c.sql_address and lower(d.event) like '%enq%'
ORDER BY 1;

set serveroutput on


BEGIN
dbms_output.enable(1000000);
for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner, 
                b.object_name object_name, b.object_type object_type
                FROM v$locked_object a, dba_objects b
    WHERE xidsqn != 0
and b.object_id = a.object_id)
loop
    dbms_output.put_line('.');
    dbms_output.put_line('Blocking Session   : '||do_loop.session_id);
dbms_output.put_line('Object (Owner/Name): '||do_loop.owner||'.'||do_loop.object_name);
    dbms_output.put_line('Object Type        : '||do_loop.object_type);
for next_loop in (select sid from v$lock
                where id2 = do_loop.xidsqn
and sid != do_loop.session_id)
LOOP
     dbms_output.put_line('Sessions being blocked   :  '||next_loop.sid);
    end loop;  
end loop;

END;
/




prompt** /*   Get active sid of a pl/sql object   */

select sid, sql_id,serial#, status, username, program
from v$session
where PLSQL_ENTRY_OBJECT_ID in (select object_id
from dba_objects
where object_name in ('&PROCEDURE_NAME'));





prompt** /*  Check if  backup  are running    */



prompt** /* session generating high  undo  and  temp   */




-- Top SQLs consuming most DB cache (based on logical + physical reads) in last 15 minutes
SELECT
    ash.sql_id,
    COUNT(*) AS ash_samples,
    ROUND(SUM(sql.buffer_gets) / 1024 / 1024, 2) AS buffer_gets_million,
    ROUND(SUM(sql.disk_reads) / 1024 / 1024, 2) AS disk_reads_million,
    ROUND(SUM(sql.buffer_gets + sql.disk_reads) / 1024 / 1024, 2) AS total_reads_million
FROM
    v$active_session_history ash
    JOIN v$sql sql ON ash.sql_id = sql.sql_id
WHERE
    ash.sample_time > SYSDATE - (15 / (24 * 60))  -- last 15 minutes
    AND ash.sql_id IS NOT NULL
    AND ash.session_type = 'FOREGROUND'
GROUP BY
    ash.sql_id
ORDER BY
    total_reads_million DESC
FETCH FIRST 10 ROWS ONLY;


-- Top SQLs consuming shared pool memory in the last 15 minutes
SELECT
    s.sql_id,
    s.sharable_mem / 1024 / 1024 AS sharable_mem_mb,
    s.persistent_mem / 1024 / 1024 AS persistent_mem_mb,
    s.runtime_mem / 1024 / 1024 AS runtime_mem_mb,
    s.loaded_versions,
    s.executions,
    s.last_active_time
FROM
    v$sql s
WHERE
    s.last_active_time > SYSDATE - (15 / (24 * 60))  -- last 15 minutes
ORDER BY
    s.sharable_mem DESC
FETCH FIRST 20 ROWS ONLY;



Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more