Running SQL / Long Running SQL / Last executed SQL / SQLID from SID

Last/Latest Running SQL

-----------------------
set pages 50000 lines 32767
col "Last SQL" for 100
SELECT t.inst_id,s.username, s.sid, s.serial#,t.sql_id,t.sql_text "Last SQL"
FROM gv$session s, gv$sqlarea t
WHERE s.sql_address =t.address AND
s.sql_hash_value =t.hash_value
/




Current Running SQLs

--------------------
set pages 50000 lines 32767
col HOST_NAME for a20
col EVENT for a40
col MACHINE for a30
col SQL_TEXT for a50
col USERNAME for a15

select sid,serial#,a.sql_id,a.SQL_TEXT,S.USERNAME,i.host_name,machine,S.event,S.seconds_in_wait sec_wait,
to_char(logon_time,'DD-MON-RR HH24:MI') login
from gv$session S,gV$SQLAREA A,gv$instance i
where S.username is not null
-- and S.status='ACTIVE'
AND S.sql_address=A.address
and s.inst_id=a.inst_id and i.inst_id = a.inst_id
and sql_text not like 'select S.USERNAME,S.seconds_in_wait%'
/



Current Running SQLs

--------------------
set pages 50000 lines 32767
col program format a20
col sql_text format a50

select b.sid,b.status,b.last_call_et,b.program,c.sql_id,c.sql_text
from v$session b,v$sqlarea c
where b.sql_id=c.sql_id
/

Last/Latest Running SQL

-----------------------
set pages 50000 lines 32767
select inst_id,sample_time,session_id,session_serial#,sql_id from gv$active_session_history
where sql_id is not null
order by 1 desc
/

SQLs Running from longtime

--------------------------
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi';
set pages 50000 lines 32767
col target format a25
col opname format a40
select sid
,opname
,target
,round(sofar/totalwork*100,2) as percent_done
,start_time
,last_update_time
,time_remaining
from
v$session_longops
/

Active Sessions running for more than 1 hour

---------------------------------------------
set pages 50000 lines 32767
col USERNAME for a10
col MACHINE for a15
col PROGRAM for a40

SELECT USERNAME,machine,inst_id,sid,serial#,PROGRAM,
to_char(logon_time,'dd-mm-yy hh:mi:ss AM')"Logon Time",
ROUND((SYSDATE-LOGON_TIME)*(24*60),1) as MINUTES_LOGGED_ON,
ROUND(LAST_CALL_ET/60,1) as Minutes_FOR_CURRENT_SQL
From gv$session
WHERE STATUS='ACTIVE'
AND USERNAME IS NOT NULL and ROUND((SYSDATE-LOGON_TIME)*(24*60),1) > 60
ORDER BY MINUTES_LOGGED_ON DESC;

Session details associated with SID and Event waiting for

---------------------------------------------------------
set pages 50000 lines 32767
col EVENT for a40

select a.sid, a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy hh24:mi') LOGON_TIME,to_char(Sysdate, 'dd-mon-yy-hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs connected" from v$session a,v$session_wait b where a.sid in(&SIDs) and a.sid=b.sid order by 8;

Session details associated with Oracle SID

-------------------------------------------
set head off
set verify off
set echo off
set pages 1500
set linesize 100
set lines 120
prompt
prompt Details of SID / SPID / Client PID
prompt ==================================
select /*+ CHOOSE*/
'Session Id.............................................: '||s.sid,
'Serial Num..............................................: '||s.serial#,
'User Name ..............................................: '||s.username,
'Session Status .........................................: '||s.status,
'Client Process Id on Client Machine ....................: '||'*'||s.process||'*' Client,
'Server Process ID ......................................: '||p.spid Server,
'Sql_Address ............................................: '||s.sql_address,
'Sql_hash_value .........................................: '||s.sql_hash_value,
'Schema Name ..... ......................................: '||s.SCHEMANAME,
'Program ...............................................: '||s.program,
'Module .................................................: '|| s.module,
'Action .................................................: '||s.action,
'Terminal ...............................................: '||s.terminal,
'Client Machine .........................................: '||s.machine,
'LAST_CALL_ET ...........................................: '||s.last_call_et,
'S.LAST_CALL_ET/3600 ....................................: '||s.last_call_et/3600
from v$session s, v$process p
where p.addr=s.paddr and
s.sid=nvl('&sid',s.sid)
/
set head on

Checking for Active Transactions SID

------------------------------------
select username,t.used_ublk,t.used_urec from v$transaction t,v$session s where t.addr=s.taddr;

Session details from Session longops

-------------------------------------
select inst_id,SID,SERIAL#,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME, username from gv$session_longops;


Session details with SPID

-------------------------
select sid, serial#, USERNAME, STATUS, OSUSER, PROCESS,
MACHINE, MODULE, ACTION, to_char(LOGON_TIME,'yyyy-mm-dd hh24:mi:ss')
from v$session where paddr in (select addr from v$process where spid = '&spid')
/
To find Undo Generated For a given session

---------------------------------------


select username,
t.used_ublk ,t.used_urec
from gv$transaction t,gv$session s
where t.addr=s.taddr and
s.sid='&sid';

To list count of connections from other machines

---------------------------------


select count(1),machine from gv$session where inst_id='&inst_id' group by machine;

To get total count of sessions and processes

-------------------------------------


select count(*) from v$session;

select count(*) from v$process;

select (select count(*) from v$session) sessions, (select count(*) from v$process) processes from dual;

To find sqltext thru sqladdress

-------------------------------
select sql_address from v$session where sid=1999;

select sql_text from v$sqltext where ADDRESS='C00000027FF00AF0' order by PIECE;

To find sqltext for different sql hashvalue

--------------------------------------


select hash_value,sql_text from v$sql where hash_value in (1937378691,1564286875,
248741712,2235840973,2787402785)

To list long running forms user sessions

----------------------------------------
select s.sid,s.process,p.spid,s.status ,s.action,s.module, (s.last_call_et/3600) from
v$session s, v$process p where round(last_call_et/3600) >4 and action like '%FRM%' and
p.addr=s.paddr ;

To list inactive Sessions respective username------------------------------------


SELECT username,count(*) num_inv_sess
FROM v$session
where last_call_et > 3600
and username is not null
AND STATUS='INACTIVE'
group by username
order by num_inv_sess DESC;

SELECT count(*) FROM v$session where last_call_et > 43200 and username is not null AND
STATUS='INACTIVE';
SELECT count(*) FROM v$session where last_call_et > 3600 and username is not null AND
STATUS='INACTIVE';

To find session id with set of SPIDs

------------------------------------
select sid from v$session, v$process where addr=paddr and spid in ('11555','26265','11533');

To find Sql Text given SQLHASH & SQLADDR

----------------------------------------
select piece,sql_text from v$sqltext where HASH_VALUE = &hash and ADDRESS ='&addr' order by piece;
select piece,sql_text from v$sqltext where ADDRESS ='&addr' order by piece;


To find SQL ID / SQL TEXT and SID of running SQLs

----------------------------------------

set lines 300

set pages 3000

set long 50000

COL USERNAME FOR A15

COL SQL_FULLTEXT FOR A100

SELECT A.INST_ID, A.SID, A.SERIAL#, A.USERNAME, A.SQL_ID, A.STATUS , B.SQL_FULLTEXT , B.PLAN_HASH_VALUE FROM GV$SESSION A , GV$SQLAREA B




WHERE A.SQL_ID=B.SQL_ID;

View SQL that run more than one hour from AWR views

select  sql_text 

from    dba_hist_sqltext 
where   sql_id in 
  (select   sql_id
   from     dba_hist_sqlstat 
   where    snap_id between &Start_SnapID and &End_SnapID
   and      elapsed_time_total > 7200000000 -- number for 1 hour
)
/

Identify hot blocks using AWR

First set the start and end snapshot ID as variables. 

To get a general idea of where the majority of WAITS's occur,
run the following SQL statement to view the counts of wait classes
in descending order;
---------------------------------------------------------

select   d.wait_class_id                as Wait_Class_ID
        ,d.wait_class                   as Wait_Class
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
where    d.nap_id between :p_Start_Snap_ID and :p_End_Snap_ID
group by d.wait_class_id
        ,d.wait_class
order by 3 desc;


Next, List a breakdown of Events per Wait class identified
in the previous result set;
---------------------------------------------------------
select   d.wait_class_id                as Wait_Class_id
        ,d.wait_class                   as Wait_Class_Name
        ,e.Name                         as Event_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,v$Event_Name                   e
where    d.snap_id between :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID                     = e.Event_ID
group by d.wait_class_id
        ,d.wait_class
        ,e.Name
order by 4 desc;


Now attempt to identify which users are responsible for the
waits (broken down per event type).

select   d.wait_class_id                as Wait_Class_ID
        ,d.wait_class                   as Wait_Class_Name
        ,u.Username                     as User_Name
        ,e.Name                         as Event_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,v$Event_Name                   e
        ,all_users                      u
where    d.snap_id between :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID         = e.Event_ID
and      d.User_id          = u.User_ID
group by u.Username
        ,d.wait_class_id
        ,d.wait_class
        ,e.Name
order by 4, 5 desc;


You may want to exclude WAITS's for SYS and focus only on the
application specific schemas, in which case, add
the additional predicate "u.Username != 'SYS'"

Also, you would probably want to exclude SQL*Net related WAIT's,
therefore add "e.Name not like 'SQL*Net%'" as a predicate.

select   d.wait_class                   as Wait_Class_Name
        ,u.Username                     as User_Name
        ,e.Name                         as Event_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,v$Event_Name                   e
        ,all_users                      u
where    d.snap_id between  :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID         =     e.Event_ID
and      d.User_id          =     u.User_ID
and      u.Username         !=    'SYS'
and      e.Name not         like  'SQL*Net%'
group by u.Username
        ,d.wait_class
        ,e.Name
order by 4, 5 desc;


To drill down on hot blocks, the WAIT class to target would be;
"User I/O".
Therefore add an additional predicate;
      "d.Wait_Class       like  'User I/O'".

select   d.wait_class                   as Wait_Class_Name
        ,u.Username                     as User_Name
        ,e.Name                         as Event_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,v$Event_Name                   e
        ,all_users                      u
where    d.snap_id between  :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID         =     e.Event_ID
and      d.User_id          =     u.User_ID
and      u.Username         !=    'SYS'
and      e.Name not         like  'SQL*Net%'
and      d.Wait_Class       like  'User I/O'
group by u.Username
        ,d.wait_class
        ,e.Name
order by 1, 4 desc;


To drill down on which Objects the hot blocks occur in,
join to the all_Objects dictionary view.

Remove the Event Name from the grouping and select list
since we know longer want to focus on individual reasons
for the general "User I/O" (of which there are several).

select   d.wait_class                   as Wait_Class_Name
        ,u.Username                     as User_Name
        ,a.Object_Name                  as Object_Name
        ,count(*)                       as Cnt
from     dba_hist_active_sess_history   d
        ,all_users                      u
        ,all_objects                    a
        ,v$Event_Name                   e
where    d.snap_id between  :p_Start_Snap_ID and :p_End_Snap_ID
and      d.Event_ID         =     e.Event_ID
and      d.User_id          =     u.User_ID
and      u.Username         !=    'SYS'
and      e.Name not         like  'SQL*Net%'
and      d.Wait_Class       like  'User I/O'
and      d.Current_Obj#     =     a.Object_ID
and      a.Object_Type      =     'TABLE'
group by u.Username
        ,d.wait_class
        ,a.Object_Name
order by 4 desc,  2, 3;


And finally, to identify the most read ROWs relative to a Top-N
number passed in as a parameter.

select User_Name
      ,Object_Name
      ,Hot_Row_ID
      ,Cnt
from
  (
  select   u.Username                     as User_Name
          ,a.Object_Name                  as Object_Name
          ,dbms_rowid.rowid_create(1, d.Current_Obj#
                                     ,d.Current_File#
                                     ,d.Current_Block#
                                     ,d.Current_Row#) as Hot_Row_ID
          ,count(*)                       as Cnt
  from     dba_hist_active_sess_history   d
          ,all_users                      u
          ,all_objects                    a
          ,v$Event_Name                   e
  where    d.snap_id between  :p_Start_Snap_ID and :p_End_Snap_ID
  and      d.Event_ID         =     e.Event_ID
  and      d.User_id          =     u.User_ID
  and      u.Username         !=    'SYS'
  and      e.Name not         like  'SQL*Net%'
  and      d.Wait_Class       like  'User I/O'
  and      d.Current_Obj#     =     a.Object_ID
  and      a.Object_Type      =     'TABLE'
  group by u.Username
          ,a.Object_Name
          ,dbms_rowid.rowid_create(1, d.Current_Obj#
                                     ,d.Current_File#
                                     ,d.Current_Block#
                                     ,d.Current_Row#)
  order by 4 desc,  2
  )
where rownum < &top_n;

Identify most scanned tables

Identify the top 20 most scanned tables. 

set linesize 400
set pagesize 3000
col Owner format a15
col Object_Name format a30

select /*+ all_rows */
       *
from
    (select Inst_ID
           ,owner
           ,object_name
           ,value
     from   gv$segment_statistics
     where  statistic_name ='logical  reads'
     and    object_type='TABLE'
     order by 3 desc)
where rownum < 21
/

Identify SQL in blocking and waiting sessions

col Event format a25

col DML_BLOCKING format a45
col DML_In_Waiting format a45
set linesize 400
set pagesize 3000

-- LABEL: STATEMENT A
select  distinct
        a.sid                     as Waiting_SID
       ,d.sql_text                as DML_In_Waiting
       ,o.Owner                   as Object_Owner
       ,o.Object_Name             as Locked_Object
       ,a.Blocking_Session        as Blocking_SID
       ,c.sql_text                as DML_Blocking
from
        v$session                 a
       ,v$active_session_history  b
       ,v$sql                     c
       ,v$sql                     d
       ,all_objects               o
where
        a.event                   = 'enq: TX - row lock contention'
and     a.sql_id                  = d.sql_id
and     a.blocking_session        = b.session_id
and     c.sql_id                  = b.sql_id
and     a.Row_Wait_Obj#           = o.Object_ID
and     b.Current_Obj#            = a.Row_Wait_Obj#
and     b.Current_File#           = a.Row_Wait_File#
and     b.Current_Block#          = a.Row_Wait_Block#

-- LABEL: STATEMENT B
select  distinct
        a.sid                     as Waiting_SID
       ,a.event                   as Event
       ,c.sql_text                as DML_Blocking
       ,b.sid                     as Blocking_SID
       ,b.event                   as Event
       ,b.sql_id                  as Blocking_SQL_ID
       ,b.prev_sql_id             as Blocking_Prev_SQL_ID
       ,d.sql_text                as DML_Blocking
from
        v$session                 a
       ,v$session                 b
       ,v$sql                     c
       ,v$sql                     d
where
        a.event                   = 'enq: TX - row lock contention'
and     a.blocking_session        = b.sid
and     c.sql_id                  = a.sql_id
and     d.sql_id                  = nvl(b.sql_id,b.prev_sql_id);

Script to find session caused the most load (foreground sessions)

The following statement indicates the sessions that have caused the most load as a percentage of all foreground sessions;

select ah.session_id
      ,ah.session_type
      ,nvl(ah.sql_id,'xx')                     as SQL_ID
      ,count(*)                                as Session_Cnt
      ,round(count(*)/sum(count(*)) over(), 2) as Percent_Load
from
       v$active_session_history ah
where
       ah.sample_time >to_date('31-OCT-11 11:40','dd-MON-yy hh24:mi')
and    ah.session_type='FOREGROUND'
group by
       ah.session_id
      ,ah.session_type
      ,ah.sql_id
order by count(*) desc

/

WARNING: Heavy swapping observed on system in last 5 mins

while importing / Exporting Data Oracle Goes to High Memory Utilization this cause SWAP alert in Alert Log file,

hence resolution is simple,

increaase TEMP file size in your Oracle Database and this alert will be gone.


it generally goes to SWAP when TEMP space is not enough to process the data .

How to Export and Import Statistics

procedure with a scenerio
Case Definition
A critical application suddenly seems to hang, wait events show long table scans running on the OLTP environment. It comes out that the DBA in charge of this system did run statistics on the tables of the user that owns the application. The gather statistics got stuck and the process was killed. Since this moment the application started to perform extremely slowly.
The production database has several clones; we decide to export back statistics from one of these clones, to the production database.

Steps in Brief
1) Create a table to hold statistics on the source database
2) Generate a script that export table statistics on the clone database
3) Generate a script that import statistics on the clone database
4) Export statistics on clone database
5) Export table containing the exported statistics from clone database
6) Ftp export file with clone statistics table, and the script to import statistics from clone server to production server
7) Import table containing clone statistics into production database
8) Import statistics on production server using the script to import statisctics generated on the clone server

1. Create tables to hold statistics on the clone database
— On Cloned Database

SQL> execute BMS_STATS.create_stat_table(‘OM’,’OLD_STATS’);
PL/SQL procedure successfully completed.
SQL> grant SELECT,INSERT,UPDATE,DELETE on OM.OLD_STATS to public;
Grant succeeded.

2. Generate a script that export table statistics on the clone database
The purpose of this script is to generate one export statistics command per table, the export is directed into the table created on step 1.
Variables:
&tabname = the table created on the previous step to hold the statistics
&usrname = The name of the owner of &tabname
—- script to generate export table stats start here ———-
set linesize 130 pagesize 0
spool exportstats.sql
select ‘exec dbms_stats.export_table_stats(‘||chr(39)||owner||chr(39)||’,’||chr(39)||table_name||chr(39)||’,null,’||chr(39)||’&tabname’
||chr(39)||’,null,true,’||chr(39)||’INV’||chr(39)||’)’
from dba_tables where owner =’&usrname’
/
spool off

—- script to generate export table stats end here ———-
Note: you may also use instead of the script this command:
exec DBMS_STATS.export_schema_stats(‘&usrname’,’&tabname’)
This syntax will run in 10g. It may fail on 8i – 9i databases with some objects. That’s why I prefer the script on these versions.

3. Generate a script that import statistics on the clone database
The purpose of this script is to generate one import statistics command per table, the source is the table created on step 1.
&tabname = the table created on the previous step to hold the statistics
&usrname = The name of the owner of &tabname

—- script to generate import table stats start here ———-
set linesize 130 pagesize 0
spool importstats.sql
select ‘exec dbms_stats.import_table_stats(‘||chr(39)||owner||chr(39)||’,’||chr(39)||table_name||chr(39)||’,null,’||chr(
39)||’&tabname’||chr(39)||’,null,true,’||chr(39)||’&usrname’||chr(39)||’)’
from dba_tables where owner =’&usrname’
/
spool off
—- script to generate import table stats end here ———-
Execute this script to generate impstats.sql that will import the statistics on the production database.

4. Export statistics on clone database
Using the script expstat.sql; generated on step 2, export statistics into the statistics table created on step 1.

5. Export table containing the exported statistics from clone database
vmractest:/oradisk/av/expstats>exp avargas file=exp_stats_from_clone tables=avr.old_stats feedback=1000
Export: Release 9.2.0.5.0 – Production on Tue Feb 20 11:57:02 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 – Production
Export done in IW8ISO8859P8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path …
Current user changed to AVR
. . exporting table OLD_STATS
….
4115 rows exported
Export terminated successfully without warnings.

6. Ftp export file with clone statistics table from clone server to production server and script to import statistics from clone server to production server
Execute FTP session from target server, get both the table that contains the exported statistics and the script to import them, generated on step :proddb > ftp vmractest
Connected to vmractest
220 vmractest FTP server (SunOS 5.8) ready.
Name (vmractest:oracle): oracle
331 Password required for oracle.
Password:
230 User oracle logged in.
ftp> cd /oradisk/av/expstats
250 CWD command successful.
ftp> get exp_stats_from_clone.dmp
200 PORT command successful.
150 ASCII data connection for exp_stats_from_clone.dmp (10.5.180.72,64082) (473088 bytes).
226 ASCII Transfer complete.
local: exp_stats_from_clone.dmp remote: exp_stats_from_clone.dmp
478390 bytes received in 0.17 seconds (2680.69 Kbytes/s)
ftp> get impstats.sql
200 PORT command successful.
150 ASCII data connection for impstats.sql (10.5.180.72,64776) (31461 bytes).
226 ASCII Transfer complete.
local: impstats.sql remote: impstats.sql
31704 bytes received in 0.033 seconds (947.63 Kbytes/s)
ftp> bye
221 Goodbye.

7. Import table containing clone statistics into production database
On the production database import the table that contains the exported statistics.
proddb >imp avargas file= exp_stats_from_clone.dmp full =y
Import: Release 9.2.0.5.0 – Production on Tue Feb 20 12:19:11 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 – Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and UTF8 NCHAR character set
export client uses IW8ISO8859P8 character set (possible charset conversion)
export server uses AL16UTF16 NCHAR character set (possible ncharset conversion)
. importing AVARGAS’s objects into AVARGAS
. importing AVR’s objects into AVR
. . importing table “OLD_STATS” 4115 rows imported
Import terminated successfully without warnings.

8. Import statistics on production server using the script to import statistics generated on the clone server
Using the script impstats.sql; generated on step 3, import statistics into the production database.