developer tip

장기 실행 쿼리에 대해 Oracle 데이터베이스를 확인하는 방법

copycodes 2020. 8. 30. 08:50
반응형

장기 실행 쿼리에 대해 Oracle 데이터베이스를 확인하는 방법


Oracle 데이터베이스를 사용하는 내 애플리케이션이 느려지거나 완전히 중지 된 것 같습니다.

가장 비용이 많이 드는 쿼리를 찾으려면 추가로 조사 할 수 있습니까?


이것은 현재 "ACTIVE"인 SQL을 보여줍니다.

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
/

이것은 자물쇠를 보여줍니다. 때때로 상황이 느리게 진행되지만 잠금을 기다리는 것이 차단 되었기 때문입니다.

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
/

이것은 긴 작업 (예 : 전체 테이블 스캔)을 찾는 데 적합합니다. 짧은 작업이 많기 때문에 아무 것도 표시되지 않습니다.

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
/

이것을 시도하면 현재 60 초 이상 실행중인 쿼리가 제공됩니다. SQL에 여러 줄이있는 경우 실행중인 쿼리 당 여러 줄을 인쇄합니다. 무엇이 함께 속하는지 보려면 sid, serial #을보십시오.

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

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.


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;

You can generate an AWR (automatic workload repository) report from the database.

Run from the SQL*Plus command line:

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

Read the document related to how to generate & 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.


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 <> TOTALWORK;

For the complete list of troubleshooting steps, you can check here:Troubleshooting long running sessions

참고URL : https://stackoverflow.com/questions/622289/how-to-check-oracle-database-for-long-running-queries

반응형