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…

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…

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,…

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…

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 ‘ || ”” ||…

Db file sequential read                                                 Summary The db file sequential read wait event means that Oracle is waiting while doing a single-block I/O read. This is the case when reading an index. Like all wait events the columns P1, P2, P3 give us the information needed to diagnose the waiting. Tip:…

 Failed Jobs can be verified by using the following sql: sql > select owner_name,job_name,operation,job_mode,state,attached_sessions from dba_datapump_jobs;   Check the state (status ) of the Jobs all the failed Jobs,they will show as not running for failed once. DROP MASTER TABLE Since  the  above  jobs  are  orphaned or  not running  won’t…

Nice and easiest way to to extract the DDL for all tablepaces..  Generate the DDL using the below query and you can re create the table spaces in any environment with any changes you want to make. set heading off;set echo off;Set pages 999;set long 90000; spool ddl_list.sql select dbms_metadata.get_ddl(‘TABLESPACE’,tb.tablespace_name)…

Upgrading Opatch to Latest Version : It is so simple to upgrade th Opatch version for your database. I will check my current version of Opatch [oracle@linux5 OPatch]$ opatch version Invoking OPatch 10.2.0.4.2 OPatch Version: 10.2.0.4.2 OPatch succeeded. Now download the latest Opatch version from oracle support site.in this case…