Oracle 데이터베이스에서 장시간 실행 중인 쿼리를 확인하는 방법

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$sq_longops

지금까지 != total work를 검색하면 완료되지 않은 작업이 표시되지만 작업이 완료될 때 항목이 제거되지 않으므로 많은 기록도 볼 수 있습니다.

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;

v$sql_monitor 보기를 사용하여 5초 이상 실행 중인 쿼리를 찾을 수 있습니다.이 기능은 Oracle Enterprise 버전에서만 사용할 수 있습니다.예를 들어 이 쿼리는 TEST_APP 서비스에서 실행 속도가 느린 쿼리를 식별합니다.

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;

주의: 경과_시간은 마이크로초 단위이므로 /1000000에서 읽기 쉬운 것을 얻을 수 있습니다.

데이터베이스에서 AWR(Automatic Workload Repository) 보고서를 생성할 수 있습니다.

SQL*Plus 명령줄에서 실행:

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

AWR 보고서를 생성하고 이해하는 방법에 관한 문서를 읽습니다.데이터베이스 성능 및 리소스 문제를 전체적으로 볼 수 있습니다.AWR 보고서를 숙지하면 리소스를 소비하는 상위 SQL을 찾는 데 도움이 됩니다.

또한 12C EM Express UI에서는 AWR을 생성할 수 있습니다.

다음 쿼리를 사용하여 완료율 및 남은 시간 등의 장기 실행 쿼리에 대한 자세한 내용을 확인할 수 있습니다.

 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;

트러블 슈팅 순서의 완전한 리스트에 대해서는, 여기를 참조해 주세요.장시간 실행 중인 세션 문제 슈팅

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;

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