Oracle에서 시간이 많이 걸리는 상위 5개의 SQL 쿼리

Oracle에서 시간이 많이 걸리는 상위 5개의 SQL 쿼리

Oracle에서 성능이 떨어지는 SQL 쿼리를 찾으려면 어떻게 해야 합니까?

Oracle은 공유 SQL 영역에 대한 통계를 유지하며 SQL 문자열(v$sqlarea)당 하나의 행을 포함합니다.하지만 그 중 어떤 것이 제대로 작동하지 않는지 어떻게 확인할 수 있을까요?

이 SQL 문이 유용한 시작점임을 알게 되었습니다(원작자의 탓으로 돌릴 수 없습니다.인터넷 어딘가에서 찾았습니다).

SELECT * FROM
(SELECT
    sql_fulltext,
    sql_id,
    elapsed_time,
    child_number,
    disk_reads,
    executions,
    first_load_time,
    last_load_time
FROM    v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10
/

이렇게 하면 현재 SQL 캐시에 저장되어 있는 상위 SQL 문이 경과 시간별로 순서대로 검색됩니다.스테이트먼트는 시간이 지남에 따라 캐시에서 사라지기 때문에 정오에 출근할 때 어젯밤 배치 작업을 진단하는 것은 좋지 않을 수 있습니다.

disk_reads 및 실행별로 순서를 지정할 수도 있습니다.일부 불량 응용 프로그램은 동일한 SQL 문을 너무 많이 전송하므로 실행이 유용합니다.이 SQL에서는 바인드 변수를 올바르게 사용하고 있다고 가정합니다.

그리고 나서, 당신은sql_id그리고.child_number이 아기에게 주입하는 방법:-

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child));

SQL 캐시의 실제 계획과 SQL 전문이 표시됩니다.

디스크 부하가 높은 풀테이블 스캔은 다음과 같습니다.

SELECT Disk_Reads DiskReads, Executions, SQL_ID, SQL_Text SQLText, 
   SQL_FullText SQLFullText 
FROM
(
   SELECT Disk_Reads, Executions, SQL_ID, LTRIM(SQL_Text) SQL_Text, 
      SQL_FullText, Operation, Options, 
      Row_Number() OVER 
         (Partition By sql_text ORDER BY Disk_Reads * Executions DESC) 
         KeepHighSQL
   FROM
   (
       SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads, 
          Max(Executions) OVER (Partition By sql_text) Executions, 
          t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options
       FROM v$sql t, v$sql_plan p
       WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS' 
       AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')
       AND t.Executions > 1
   ) 
   ORDER BY DISK_READS * EXECUTIONS DESC
)
WHERE KeepHighSQL = 1
AND rownum <=5;

인스턴스의 액티비티 기간 동안 실행당 평균 버퍼 get을 얻을 수 있습니다.

SELECT username,
       buffer_gets,
       disk_reads,
       executions,
       buffer_get_per_exec,
       parse_calls,
       sorts,
       rows_processed,
       hit_ratio,
       module,
       sql_text
       -- elapsed_time, cpu_time, user_io_wait_time, ,
  FROM (SELECT sql_text,
               b.username,
               a.disk_reads,
               a.buffer_gets,
               trunc(a.buffer_gets / a.executions) buffer_get_per_exec,
               a.parse_calls,
               a.sorts,
               a.executions,
               a.rows_processed,
               100 - ROUND (100 * a.disk_reads / a.buffer_gets, 2) hit_ratio,
               module
               -- cpu_time, elapsed_time, user_io_wait_time
          FROM v$sqlarea a, dba_users b
         WHERE a.parsing_user_id = b.user_id
           AND b.username NOT IN ('SYS', 'SYSTEM', 'RMAN','SYSMAN')
           AND a.buffer_gets > 10000
         ORDER BY buffer_get_per_exec DESC)
 WHERE ROWNUM <= 20

사용하는 Oracle 버전에 따라 다르지만 Statspack 이하가 10g 이상이면 상위 SQL 및 기타 많은 기능을 제공합니다.

톰-오라클에게 들은 완전한 정보입니다도움이 됐으면 좋겠다

select * 
from v$sql 
where buffer_gets > 1000000 
or disk_reads > 100000 
or executions > 50000 

다음 쿼리는 대량의 디스크 읽기를 수행하는 SQL 문을 반환합니다(해당 사용자 및 쿼리가 실행된 횟수 포함).

SELECT t2.username, t1.disk_reads, t1.executions,
    t1.disk_reads / DECODE(t1.executions, 0, 1, t1.executions) as exec_ratio,
    t1.command_type, t1.sql_text
  FROM v$sqlarea t1, dba_users t2
  WHERE t1.parsing_user_id = t2.user_id
    AND t1.disk_reads > 100000
  ORDER BY t1.disk_reads DESC

쿼리를 SYS로 실행하고 과도한 것으로 간주되는 항목에 따라 디스크 읽기 수를 조정합니다(100,000개면 충분합니다).

최근 이 쿼리를 사용하여 이용 거부 사용자를 추적하고 있습니다.Explain Plans그들의 진술을 이행하기 전에.

이 쿼리는 오래된 Oracle SQL 튜닝북(유감스럽게도 더 이상 가지고 있지 않음)에서 발견되었으므로 죄송합니다.

여러 가지 방법이 있지만 tkprof를 위한 구글을 사용하세요.

GUI는 없습니다.커맨드 라인 뿐이고, Oracle의 초보자에게는 위협적인 조작이지만, 매우 파워풀합니다.

이 링크는 좋은 시작처럼 보입니다.

http://www.oracleutilities.com/OSUtil/tkprof.html

검색하는 동안 하나의 가정으로 작업을 수행하는 다음과 같은 쿼리를 받았습니다(쿼리 실행 시간 > 6초).

---

사용자 이름, sql_text, 지금까지, 총 작업, 단위 선택

시작 v$sql,v$session_longops

여기서 sql_address = address AND sql_sql_value = hash_value

ORDER BY 주소, hash_value, child_number;

---

위 쿼리는 현재 사용자의 세부사항을 나열할 것이라고 생각합니다.

댓글은 대환영!!

언급URL : https://stackoverflow.com/questions/316812/top-5-time-consuming-sql-queries-in-oracle