Tìm các câu lệnh SQL chạy lâu, chiếm IO, chiếm tài nguyên trong Oracle Database

Last updated on May 27th, 2022 at 11:34 am

SQLs with elapsed time more then 1 hour

>>>Đọc thêm:

Khoá học SQL – Truy vấn cơ sở dữ liệu từ cơ bản đến nâng cao

Lộ trình trở thành Data Engineer (Kỹ sư dữ liệu)

SELECT *
    FROM dba_hist_snapshot where end_interval_time>=to_date('03/08/2017 00:00:00','dd/mm/yyyy hh24:mi:ss')
    and end_interval_time <=to_date('04/08/2017 01:00:00','dd/mm/yyyy hh24:mi:ss')
    order by end_interval_time;
    
    SELECT min(snap_id), max(snap_id)
    FROM dba_hist_snapshot where end_interval_time>=to_date('03/08/2017 01:00:00','dd/mm/yyyy hh24:mi:ss')
    and end_interval_time <=to_date('04/08/2017 01:00:00','dd/mm/yyyy hh24:mi:ss')
    order by end_interval_time;

    SELECT sql_id,
    text,
    elapsed_time,
    CPU_TIME,
    EXECUTIONS,
    PX_SERVERS,
    DISK_READ_BYTES,
    DISK_WRITE_BYTES,
    IO_INTERCONNECT_BYTES,
    OFFLOAD_ELIGIBLE_BYTES,
    CELL_SMART_SCAN_ONLY_BYTES,
    FLASH_CACHE_READS,
    ROWS_PROCESSED
    --AVG_PX_SERVER
    FROM (SELECT x.sql_id,
    SUBSTR ( dhst.sql_text, 1, 4000) text,
    ROUND ( x.elapsed_time / 1000000,0)  elapsed_time,
    ROUND ( x.cpu_time / 1000000,0)  CPU_TIME,
    --ROUND ( x.elapsed_time / 1000000, 3) elapsed_time,
    --ROUND ( x.cpu_time / 1000000, 3) cpu_time_sec,
    x.executions_delta       EXECUTIONS,
    ROUND (X.DISK_READ_BYTES/1048576,0)        DISK_READ_BYTES,
    ROUND (X.DISK_WRITE_BYTES/1048576,0)       DISK_WRITE_BYTES,
    ROUND (X.IO_INTERCONNECT_BYTES/1048576,0)  IO_INTERCONNECT_BYTES,
    ROUND (X.OFFLOAD_ELIGIBLE_BYTES/1048576,0) OFFLOAD_ELIGIBLE_BYTES,
    X.FLASH_CACHE_READS                        FLASH_CACHE_READS,
    ROUND (X.cell_smart_scan_only_BYTES/1048576,0)  CELL_SMART_SCAN_ONLY_BYTES,
    (x.ROWS_PROCESSED) ROWS_PROCESSED,
    (X.PX_SERVERS) PX_SERVERS,
    --ROUND(X.PX_SERVERS/X.executions_delta,0) AVG_PX_SERVER,
    row_number () OVER (PARTITION BY x.sql_id ORDER BY 0) rn
    FROM dba_hist_sqltext dhst,
    (SELECT dhss.sql_id                       sql_id,
    SUM (dhss.cpu_time_delta)                 cpu_time,
    SUM (dhss.elapsed_time_delta)             elapsed_time,
    SUM (dhss.executions_delta)               executions_delta,
    SUM (dhss.PHYSICAL_READ_BYTES_DELTA)      DISK_READ_BYTES,
    SUM (dhss.PHYSICAL_WRITE_BYTES_DELTA)     DISK_WRITE_BYTES,
    SUM (dhss.IO_INTERCONNECT_BYTES_DELTA)    IO_INTERCONNECT_BYTES,
    SUM (dhss.IO_OFFLOAD_ELIG_BYTES_DELTA)    OFFLOAD_ELIGIBLE_BYTES,
    SUM (dhss.OPTIMIZED_PHYSICAL_READS_DELTA) FLASH_CACHE_READS,
    SUM (dhss.IO_OFFLOAD_RETURN_BYTES_DELTA)  cell_smart_scan_only_BYTES,
    SUM (dhss.ROWS_PROCESSED_DELTA)      ROWS_PROCESSED,
    SUM (dhss.PX_SERVERS_EXECS_DELTA) PX_SERVERS
    FROM dba_hist_sqlstat dhss
    WHERE dhss.snap_id IN
                        (SELECT distinct snap_id
                        FROM dba_hist_snapshot    
                        WHERE SNAP_ID > 90796 AND SNAP_ID<= 90820)
    --comment BELOW line if want to include current executions.
    --AND dhss.executions_delta > 0    
    and dhss.instance_number=1
    GROUP BY dhss.sql_id) x
    WHERE x.sql_id = dhst.sql_id
    AND ROUND ( x.elapsed_time / 1000000, 3) > 3600    
    )    
    WHERE rn = 1 ORDER BY ELAPSED_TIME DESC;
    
    --WAIT_CLASS AND COUNTS / NOTE " NULL VALUE IS CPU"
    select wait_class, count(*) cnt from dba_hist_active_sess_history
    WHERE SNAP_ID > 90796 AND SNAP_ID<= 90820 and instance_number=1
    group by wait_class_id, wait_class
    order by 2 desc;

Top 40 Objects by Physical Read

SELECT * FROM (
        SELECT do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']' AS OBJECTS,
        DHSS.INSTANCE_NUMBER AS INST,
        SUM(DHSS.LOGICAL_READS_DELTA) LOGICAL_READ,
        SUM(DHSS.PHYSICAL_READS_DELTA) PHY_READ,
        SUM(DHSS.PHYSICAL_WRITES_DELTA) PHY_WRIT,
        SUM(DHSS.ITL_WAITS_DELTA) ITL_WT,
        SUM(DHSS.ROW_LOCK_WAITS_DELTA) ROW_LCK_WT
        from dba_hist_seg_stat DHSS, DBA_OBJECTS DO    
        WHERE DHSS.SNAP_ID > 90797 AND DHSS.SNAP_ID<= 90820
        AND DHSS.OBJ#=DO.OBJECT_ID
        and DHSS.INSTANCE_NUMBER=1
        group by do.OWNER||'.'||do.OBJECT_NAME||'..['||do.OBJECT_TYPE||']',DHSS.INSTANCE_NUMBER
        order BY PHY_READ DESC
    ) WHERE ROWNUM <=40;
    
end; 

Check IO

SELECT host_name,
         db_name,
         instance_name,
         ROUND (SUM (last_15_mins) / 1024 / 1024) IO_MB_LAST_15_MINS,
         SYSDATE
    FROM (  SELECT inst.host_name,
                   db.name AS db_name,
                   inst.instance_name,
                   sm.metric_name,
                   ROUND (AVG (sm.VALUE), 0) last_15_mins
              FROM GV$SYSMETRIC_HISTORY sm,
                   gv$instance inst,
                   (SELECT name FROM v$database) db
             WHERE     sm.inst_id = inst.inst_id
                   AND sm.metric_name IN ('Physical Read Total Bytes Per Sec',
                                          'Physical Write Bytes Per Sec',
                                          'Redo Generated Per Sec')
                   AND sm.begin_time >= SYSDATE - 15 / (24 * 60)
          GROUP BY inst.host_name,
                   db.name,
                   inst.instance_name,
                   sm.inst_id,
                   sm.metric_name)
GROUP BY host_name, db_name, instance_name
ORDER BY 1;

select sql_id,sql_fulltext from gv$sql where  sql_id in ('67bm8d2ah3xhk');

check order by elaped time

select * from
(SELECT parsing_schema_name "USER",X.sql_id,dbms_lob.substr (sql_text,100,1)||' ...' "SQL_TEXT" --dbms_lob.substr(SQL_TEXT,4000,1) "SQL_TEXT" 
,ROUND(X.ELAPSED_TIME/1000000,0) ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000,0) CPU_TIME_SEC
,ROUND(X.BUFFER_GETS /1000000,0) BUFFER_GETS_NUMBER
, EXECUTIONS_DELTA
, ROUND(X.ELAPSED_TIME/1000000/EXECUTIONS_DELTA,2) AVG_ELAPSED_SEC
FROM DBA_HIST_SQLTEXT DHST,
(SELECT dhss.parsing_schema_name,DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME,
SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME, SUM (DHSS.BUFFER_GETS_DELTA) BUFFER_GETS
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID in (select snap_id from dba_hist_snapshot where begin_interval_time >= sysdate - 2/24 and begin_interval_time <= sysdate) and EXECUTIONS_DELTA>0
--and DHSS.parsing_schema_name like '%SUP%'
GROUP BY dhss.parsing_schema_name,DHSS.SQL_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY ELAPSED_TIME_SEC DESC
)
where rownum < 10;

check cpu time 

select * from
(SELECT parsing_schema_name "USER",X.sql_id,dbms_lob.substr (sql_text,100,1)||' ...' "SQL_TEXT" --dbms_lob.substr(SQL_TEXT,4000,1) "SQL_TEXT" 
,ROUND(X.ELAPSED_TIME/1000000,0) ELAPSED_TIME_SEC
,ROUND(X.CPU_TIME /1000000,0) CPU_TIME_SEC
,ROUND(X.BUFFER_GETS /1000000,0) BUFFER_GETS_NUMBER
, EXECUTIONS_DELTA
, ROUND(X.CPU_TIME/1000000/EXECUTIONS_DELTA,2) AVG_ELAPSED_SEC
FROM DBA_HIST_SQLTEXT DHST,
(SELECT dhss.parsing_schema_name,DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME,
SUM (DHSS.ELAPSED_TIME_DELTA) ELAPSED_TIME, SUM (DHSS.BUFFER_GETS_DELTA) BUFFER_GETS
, SUM(DHSS.EXECUTIONS_DELTA) EXECUTIONS_DELTA
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID in (select snap_id from dba_hist_snapshot where begin_interval_time >= sysdate - 2/24 and begin_interval_time <= sysdate) and EXECUTIONS_DELTA>0
--and DHSS.parsing_schema_name like '%SUP%'
GROUP BY dhss.parsing_schema_name,DHSS.SQL_ID) X
WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY CPU_TIME_SEC DESC
)
where rownum < 10;

>>>Đọc thêm:

Khoá học SQL – Truy vấn cơ sở dữ liệu từ cơ bản đến nâng cao

Lộ trình trở thành Data Engineer (Kỹ sư dữ liệu)