Home

Which SQL are doing a lot of disk I/O                                                 Which SQL are doing a lot of disk I/O SELECT * FROM (SELECT SUBSTR(sql_text,1,500) SQL, ELAPSED_TIME, CPU_TIME, disk_reads, executions, disk_reads/executions “Reads/Exec”, hash_value,address FROM V$SQLAREA WHERE ( hash_value, address ) IN ( SELECT DISTINCT HASH_VALUE, address FROM v$sql_plan WHERE DISTRIBUTION IS NOT NULL ) AND disk_reads >…

Disk I/O Script Datafiles Disk I/O Tablespace Disk I/O Which segments have top Logical I/O & Physical I/O Which SQL are doing a lot of disk I/O

Which segments have top Logical I/O & Physical I/O                                                 Summary Do you know which segments in your Oracle Database have the largest amount of I/O, physical and logical? This SQL helps to find out which segments are heavily accessed and helps to target tuning efforts on these segments: SELECT ROWNUM AS Rank, Seg_Lio.* FROM (SELECT…

Tablespace Disk I/O                                                Summary The Physical design of the database reassures optimal performance for DISK I/O. Storing the datafiles in different filesystems (Disks) is a good technique to minimize disk contention for I/O. How I/O is spread per Tablespace SELECT T.NAME, SUM(Physical_READS) Physical_READS, ROUND((RATIO_TO_REPORT(SUM(Physical_READS)) OVER ())*100, 2) || ‘%’ PERC_READS, SUM(Physical_WRITES) Physical_WRITES, ROUND((RATIO_TO_REPORT(SUM(Physical_WRITES)) OVER ())*100,…

Datafiles Disk I/O                                             Summary The Physical design of the database reassures optimal performance for DISK I/O. Storing the datafiles in different filesystems (Disks) is a good technique to minimize disk contention for I/O. How I/O is spread per datafile SELECT NAME, phyrds Physical_READS, ROUND((RATIO_TO_REPORT(phyrds) OVER ())*100, 2)|| ‘%’ PERC_READS, phywrts Physical_WRITES, ROUND((RATIO_TO_REPORT(phywrts) OVER ())*100, 2)||…