How to check Oracle database for long queries

Question

My application using the Oracle database is slow or seems to have stopped completely.

How do I know which queries are the most expensive, so I can continue to research?

Answer 1

Here's the 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 <> 'SYSTEM' order by s.sid,t.piece / 

Locks are displayed here. Sometimes everything goes slowly, but this is because it is locked waiting for the 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 well suited for searching for long operations (for example, a full table scan). If this is due to the large number of short operations, nothing will appear.

 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 < 1 / 

Answer 2

Try this, it will give you queries now more than 60 seconds. Note that it prints multiple lines for each query if SQL has multiple lines. Look at 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 <>'BACKGROUND' and last_call_et> 60 order by sid,serial#,q.piece 

Answer 3

v $ SESSION_LONGOPS

If you are looking for sofar! = Totalwork, you will see those that were not completed, but the records are not deleted after the operation is completed, so you can also see a lot of history there.

Answer 4

 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') <= 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; 

Answer 5

You can create an AWR report (automatic workload repository) from a database.

Run SQL * Plus from the command line:

 SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql 

Read the document on how to create and understand an AWR report. This will give a complete picture of database performance and resource issues. Once we review the AWR report, it will be helpful to find Top SQL that consumes resources.

In addition, we can generate AWRs in the 12C EM Express user interface.

Answer 6

You can check the details of long running queries, such as% completed and remaining time, using the following 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 <> TOTALWORK; 

You can check the full list of troubleshooting steps here: Troubleshoot long sessions