How to check Oracle database for long running queries

Question

My application  which uses an Oracle database  is going slow or appears to have stopped completely   How can find out which queries are most expensive  so I can investigate further

User · Answer

select sq PARSING SCHEMA NAME  sq LAST LOAD TIME  sq ELAPSED TIME  sq ROWS PROCESSED  ltrim sq sql text   sq SQL FULLTEXT   from v sql sq  v session se  order by sq ELAPSED TIME desc  sq LAST LOAD TIME desc

User · Answer

Step 1 Execute the query  column username format  a10  column osuser format  a10  column module format  a16  column program name format  a20  column program format  a20  column machine format  a20  column action format  a20  column sid format  9999  column serial  format  99999  column spid format  99999  set linesize 200 set pagesize 30 select a sid a serial  a username a osuser c start time  b spid a status a machine  a action a module a program from v session a  v process b  v transaction c  v sqlarea s Where a paddr   b addr and a saddr   c ses addr and a sql address   s address     and to date c start time  mm dd yy hh24 mi ss    lt   sysdate -  15 1440  -- running for 15 minutes order by c start time       Step 2  desc v session  Step 3 select sid  serial  SQL ADDRESS  status PREV SQL ADDR from v session where sid  xxxx     enter the sid value   Step 4  select sql text from v sqltext where address  XXXXXXXX    Step 5  select piece  sql text from v sqltext where address  XXXXXX  order by piece

User · Answer

v session longops  If you look for sofar    totalwork you ll see ones that haven t completed  but the entries aren t removed when the operation completes so you can see a lot of history there too

User · Answer

You can use the v sql monitor view to find queries that are running longer than 5 seconds   This may only be available in Enterprise versions of Oracle   For example this query will identify slow running queries from my TEST APP service  select to char sql exec start   dd-Mon hh24 mi     elapsed time   1000000  run time         cpu time  sql id  sql text  from   v sql monitor where  service name    TEST APP  order  by 1 desc   Note elapsed time is in microseconds so   1000000 to get something more readable

User · Answer

This one shows SQL that is currently  ACTIVE  -  select S USERNAME  s sid  s osuser  t sql id  sql text 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  lt  gt   SYSTEM  order by s sid t piece     This shows locks   Sometimes things are going slow  but it s because it is blocked waiting for a lock   select   object name     object type     session id     type          -- Type or system user lock   lmode         -- lock mode in which session holds lock   request     block     ctime         -- Time since current mode was granted from   v locked object  all objects  v lock where   v locked object object id   all objects object id AND   v lock id1   all objects object id AND   v lock sid   v locked object session id order by   session id  ctime desc  object name     This is a good one for finding long operations  e g  full table scans    If it is because of lots of short operations  nothing will show up   COLUMN percent FORMAT 999 99   SELECT sid  to char start time  hh24 mi ss   stime   message   sofar totalwork   100 percent  FROM v session longops WHERE sofar totalwork  lt  1

User · Answer

Try this  it will give you queries currently running for more than 60 seconds  Note that it prints multiple lines per running query if the SQL has multiple lines  Look at the sid serial  to see what belongs together   select s username s sid s serial  s last call et 60 mins running q sql text from v session s  join v sqltext with newlines q on s sql address   q address  where status  ACTIVE  and type  lt  gt  BACKGROUND  and last call et gt  60 order by sid serial  q piece

User · Answer

You can generate an AWR  automatic workload repository  report from the database    Run from the SQL Plus command line   SQL gt    ORACLE HOME rdbms admin awrrpt sql   Read the document related to how to generate  amp  understand an AWR report  It will give a complete view of database performance and resource issues  Once we are familiar with the AWR report it will be helpful to find Top SQL which is consuming resources   Also  in the 12C EM Express UI we can generate an AWR

User · Answer

You can check the long-running queries details like   completed and remaining time using the below query    SELECT SID  SERIAL   OPNAME  CONTEXT  SOFAR    TOTALWORK ROUND SOFAR TOTALWORK 100 2     COMPLETE    FROM V SESSION LONGOPS   WHERE OPNAME NOT LIKE   aggregate           AND TOTALWORK    0         AND SOFAR  lt  gt  TOTALWORK    For the complete list of troubleshooting steps  you can check here Troubleshooting long running sessions

[oracle] How to check Oracle database for long running queries

Examples related to oracle