Posts

Find index skewed, rebuild

Find index skewed, rebuild                                                  Summary It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt. When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance. The key column to decide index skewed is blevel . You must estimate statistics for the index or analyze index validate structure . If the BLEVEL were to be more than 4, it is recommended to rebuild the index. SELECT OWNER, INDEX_NAME, TABLE_NAME, LAST_ANALYZED, BLEVEL FROM DBA_INDEXES WHERE OWNER NOT IN ('SYS', 'SYSTEM') AND BLEVEL >= 4 ORDER BY BLEVEL DESC;
Post a Comment
Read more

Which SQL are doing a lot of disk I/O

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 > 100 AND executions > 0 ORDER BY ELAPSED_TIME DESC) WHERE ROWNUM <=30;
Post a Comment
Read more

Disk I/O

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
Post a Comment
Read more

Which segments have top Logical I/O & Physical 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 St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'LIO' AS Unit FROM V$segment_Statistics St WHERE St.Statistic_Name = 'logical reads' ORDER BY St.VALUE DESC) Seg_Lio WHERE ROWNUM <= 10 UNION ALL SELECT ROWNUM AS Rank, Seq_Pio_r.* FROM (SELECT St.Owner, St.Obj#, St.Object_Type, St.Object_Name, St.VALUE, 'PIO Rea...
Post a Comment
Read more

Tablespace Disk I/O

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, 2) || '%' PERC_WRITES, SUM(total) total, ROUND((RATIO_TO_REPORT(SUM(total)) OVER ())*100, 2) || '%' PERC_TOTAL FROM (SELECT ts#, NAME, phyrds Physical_READS, phywrts Physical_WRITES, phyrds + phywrts total FROM v$datafile df, v$file...
Post a Comment
Read more

Datafiles Disk I/O

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)|| '%' PERC_WRITES, phyrds + phywrts total FROM v$datafile df, v$filestat fs WHERE df.FILE# = fs.FILE# ORDER BY phyrds DESC; Tip : ORDER BY phyrds, order by physical reads descending. ORDER BY phywrts, order by physical writes descending. How I/O is spread per filesystem SELECT filesystem, ROUND((RATIO_TO_REP...
Post a Comment
Read more

Current waiting events Summary

Current waiting events   Summary The first and most important script about OWI, is where current sessions waiting SELECT a.SID, b.serial#, b.status, p.spid, b.logon_time, a.event, l.NAME latch_name, a.SECONDS_IN_WAIT SEC, b.sql_hash_value, b.osuser, b.username, b.module, b.action, b.program, a.p1,a.p1raw, a.p2, a.p3, --, b.row_wait_obj#, b.row_wait_file#, b.row_wait_block#, b.row_wait_row#, 'alter system kill session ' || '''' || a.SID || ', '|| b.serial# || '''' || ' immediate;' kill_session_sql FROM v$session_wait a, v$session b, v$latchname l, v$process p WHERE a.SID = b.SID AND b.username IS NOT NULL AND b.TYPE <> 'BACKGROUND' AND a.event NOT IN (SELECT NAME FROM v$event_name WHERE wait_class = 'Idle') AND (l.latch#(+) = a.p2) AND b.paddr = p.addr --AND a.sid = 559 --AND module IN ('JDBC Thin Client') --AND p.spid = 13317 --AND b.sql_hash_value = '4119097924' --AND event like 'libra...
Post a Comment
Read more