Arvind Toorpu

Lets create a trigger that will log all the errors in the database:It is better to create this trigger under SYS or DBA user’s so that you don’t have to grant all permission if you have to grant permissions separately. ——————————————————–—  DDL for Trigger AFTER_ERROR——————————————————–   CREATE OR REPLACE TRIGGER “AFTER_ERROR”     AFTER SERVERERROR ON DATABASEDECLARE    pragma autonomous_transaction;    id…

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…

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…