CHANGE ASM DISKGROUP REDUNDANCY FROM NORMAL TO EXTERNAL

1.1      Verify the existing

When the disk groups are created with some redundancy say External, Normal or High then its redundancy cannot be changed. Need to change redundancy can arise if:

- DBA's want to switch from Normal/High Redundancy to External Redundancy due to disk space constraints or due to plans of using External methods of maintaining redundancy (like RAID 10 , etc) .

- Switch to ASM based Redundancy i.e converting from External redundancy to Normal/High Redundancy

• This note is for RAC Grid infrastructure 11.2.0.2 

• 2 nodes : node1_name & node2_name

• Linux 64nits

oragrid:

SQL>  select state,name,type from v$asm_diskgroup;

STATE       NAME                           TYPE

----------- ------------------------------ ------

MOUNTED     DATA_ASM                       NORMAL

SQL> col gnum format 99

SQL> col filnum format 999999999999

SQL> col path format a60

SQL> SELECT gnum, filnum, concat('+'||gname,sys_connect_by_path(aname, '/')) path

  2  FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,

  3  a.reference_index rindex, a.group_number gnum,a.file_number filnum

  4  FROM v$asm_alias a,v$asm_diskgroup g

  5  WHERE a.group_number = g.group_number)

  6  START WITH (mod(pindex, power(2, 24))) = 0

  7  CONNECT BY PRIOR rindex = pindex;

GNUM        FILNUM PATH

---- ------------- ------------------------------------------------------------

   1    4294967295 +DATA_ASM/scan-cluster

   1    4294967295 +DATA_ASM/scan-cluster/ASMPARAMETERFILE

   1           253 +DATA_ASM/scan-cluster/ASMPARAMETERFILE/REGISTRY.253.8073

                   78527

   1    4294967295 +DATA_ASM/scan-cluster/OCRFILE

   1           255 +DATA_ASM/scan-cluster/OCRFILE/REGISTRY.255.807379617

   1    4294967295 +DATA_ASM/MY_DB

   1    4294967295 +DATA_ASM/MY_DB/DATAFILE

   1           271 +DATA_ASM/MY_DB/DATAFILE/INDX.271.809516379

   1           270 +DATA_ASM/MY_DB/DATAFILE/DATAX.270.807802575

GNUM        FILNUM PATH

---- ------------- ------------------------------------------------------------

   1           268 +DATA_ASM/MY_DB/DATAFILE/DATAX.268.807796427

   1           264 +DATA_ASM/MY_DB/DATAFILE/UNDOTBS2.264.807705403

   1           259 +DATA_ASM/MY_DB/DATAFILE/USERS.259.807705229

   1           258 +DATA_ASM/MY_DB/DATAFILE/UNDOTBS1.258.807705227

   1           257 +DATA_ASM/MY_DB/DATAFILE/SYSAUX.257.807705225

   1           256 +DATA_ASM/MY_DB/DATAFILE/SYSTEM.256.807705225

   1           269 +DATA_ASM/MY_DB/DATAFILE/DATAX.269.807802377

   1    4294967295 +DATA_ASM/MY_DB/CONTROLFILE

   1           260 +DATA_ASM/MY_DB/CONTROLFILE/Current.260.807705377

   1    4294967295 +DATA_ASM/MY_DB/ONLINELOG

   1           265 +DATA_ASM/MY_DB/ONLINELOG/group_3.265.807705465

GNUM        FILNUM PATH

---- ------------- ------------------------------------------------------------

   1           266 +DATA_ASM/MY_DB/ONLINELOG/group_4.266.807705465

   1           261 +DATA_ASM/MY_DB/ONLINELOG/group_1.261.807705379

   1           262 +DATA_ASM/MY_DB/ONLINELOG/group_2.262.807705381

   1    4294967295 +DATA_ASM/MY_DB/TEMPFILE

   1           263 +DATA_ASM/MY_DB/TEMPFILE/TEMP.263.807705385

   1    4294967295 +DATA_ASM/MY_DB/PARAMETERFILE

   1           267 +DATA_ASM/MY_DB/PARAMETERFILE/spfile.267.807705467

   1           267 +DATA_ASM/MY_DB/spfileMY_DB.ora

28 rows selected.

As oracle:

SQL> select name from v$controlfile

union

select name from v$datafile

union

select name from v$tempfile

union

select member from v$logfile

union

select filename from v$block_change_tracking ;

NAME

--------------------------------------------------------------------------------

+DATA_ASM/MY_DB/controlfile/current.260.807705377

+DATA_ASM/MY_DB/datafile/datax.268.807796427

+DATA_ASM/MY_DB/datafile/datax.269.807802377

+DATA_ASM/MY_DB/datafile/datax.270.807802575

+DATA_ASM/MY_DB/datafile/indx.271.809516379

+DATA_ASM/MY_DB/datafile/sysaux.257.807705225

+DATA_ASM/MY_DB/datafile/system.256.807705225

+DATA_ASM/MY_DB/datafile/undotbs1.258.807705227

+DATA_ASM/MY_DB/datafile/undotbs2.264.807705403

+DATA_ASM/MY_DB/datafile/users.259.807705229

+DATA_ASM/MY_DB/onlinelog/group_1.261.807705379

NAME

--------------------------------------------------------------------------------

+DATA_ASM/MY_DB/onlinelog/group_2.262.807705381

+DATA_ASM/MY_DB/onlinelog/group_3.265.807705465

+DATA_ASM/MY_DB/onlinelog/group_4.266.807705465

+DATA_ASM/MY_DB/tempfile/temp.263.807705385

SQL> show parameter pfile;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

spfile                               string      +DATA_ASM/MY_DB/spfileMY_DB.ora

SQL> select A.group# as "Group", A.thread#, B.member, round(A.bytes/1024/1024,0) as "Size (Mo)"

from v$log a, v$logfile b

where a.group# = b.group#

order by A.group#, B.member;  2    3    4

Group THREAD#

----- -------

MEMBER

--------------------------------------------------------------------------------

 Size (Mo)

----------

    1       1

+DATA_ASM/MY_DB/onlinelog/group_1.261.807705379

        50

    2       1

+DATA_ASM/MY_DB/onlinelog/group_2.262.807705381

        50

Group THREAD#

----- -------

MEMBER

--------------------------------------------------------------------------------

 Size (Mo)

----------

    3       2

+DATA_ASM/MY_DB/onlinelog/group_3.265.807705465

        50

    4       2

+DATA_ASM/MY_DB/onlinelog/group_4.266.807705465

Group THREAD#

----- -------

MEMBER

--------------------------------------------------------------------------------

 Size (Mo)

----------

        50

1.2      Drop the existing disk group after database backup and create a new disk group with External redundancy

1. Shutdown (immediate) the database and then start up mount.  Take a valid RMAN backup of existing database as:

Method1:

On each node, and as oracle user:

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Startup mount:

SQL> startup mount;

Method2:

You can also, use the following method, as oracle user:

srvctl stop database -d MY_DB -o immediate

Startup mount:

srvctl start database -d MY_DB -o mount

On the first node:

mkdir –p /livraison/backup/

rman target /

RMAN> run {

    allocate channel chn1 device type DISK format '/livraison/backup/%U';

    backup full database spfile plus archivelog delete all input;

}

2. Make copy of spfile to accessible location:

On the first node, and as oracle user:

SQL> create pfile='/livraison/backup/initMY_DB.ora' from spfile;
SQL> alter database backup controlfile to '/livraison/backup/control.ctl';

3. Shutdown the database

srvctl stop database -d MY_DB -o immediate

4. Connect to ASM Instance and Drop the existing Diskgroups

4.1 Stop asm on the other nodes (except the first one).

 As oargrid user:   

 Method1:              

SQL> shutdown immediate;

ASM diskgroups volume disabled

ASM diskgroups dismounted

ASM instance shutdown

 Method2:

             crsctl stop resource ora.asm -n node2_name -f

4.2 On the first node, and as oragrid user:

sqlplus '/as sysasm'

SQL> drop diskgroup DATA_ASM including contents;

Diskgroup dropped.

If you have such errors:

ERROR at line 1:

ORA-15039: diskgroup not dropped

ORA-15027: active use of diskgroup "DATA_ASM" precludes its dismount

Then, ensure that asm are stopped on each node (except the current one) and do:

   SQL> alter diskgroup DATA_ASM dismount force;

   SQL> drop diskgroup DATA_ASM force including contents;

5. Shutdown ASM Instance on the first node.

On the first node, and as oragrid user:

sqlplus '/as sysasm'

SQL> shutdown immediate;

6. Startup the ASM instance in nomount state and Create the new ASM diskgroup

On each node, as oragrid user:

Two methods to do:

Method 1:

sqlplus '/as sysasm'

SQL> startup nomount;

ORA-00099: warning: no parameter file specified for ASM instance

ASM instance started

Total System Global Area  283930624 bytes

Fixed Size                  2225792 bytes

Variable Size             256539008 bytes

ASM Cache                  25165824 bytes

Method 2:

       srvctl start asm -o nomount

Check:

       crsctl stat res -t

The new diskgroups name should be same as of previous diskgroup, it will facilitate the RMAN restore process.

Locate the asmca binary (for example /logiciels/oracle/grid/bin/)

cd /logiciels/oracle/grid/bin/

export DISPLAY=XXXX

./asmca

7. Connect to the RDBMS instance and startup in no mount state using pfile

On the first node,  as oragrid user:

srvctl start instance -d MY_DB -n node1_name -o nomount;

On the first node, and as oracle user:

cd /livraison/backup

SQL> startup nomount pfile='initMY_DB.ora';

sql> create spfile='+DATA_ASM/MY_DB/spfileMY_DB.ora' from pfile='/livraison/backup/initMY_DB.ora';

Check:

    ASMCMD> ls -l DATA_ASM/MY_DB/

    Type           Redund  Striped  Time             Sys  Name

                                                     Y    PARAMETERFILE/

                                                     N    spfileMY_DB.ora => +DATA_ASM/MY_DB/PARAMETERFILE/spfile.256.811956759

    ASMCMD> ls -l DATA_ASM/MY_DB/PARAMETERFILE/

    Type           Redund  Striped  Time             Sys  Name

    PARAMETERFILE  UNPROT  COARSE   APR 05 15:00:00  Y    spfile.256.811956759

At this moment, shutdown and restart the database on no mount.

SQL> Shutdown immediate;

SQL> Startup nomount;

8. Now restore the controlfile and backup's using RMAN

On the first node:

rman target/

RMAN> restore controlfile from '/livraison/backup/control.ctl';

Starting restore at 04-APR-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=16 instance=MY_DB1 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+DATA_ASM/MY_DB/controlfile/current.256.811867053

Finished restore at 04-APR-13

RMAN> alter database mount;
RMAN> restore database;

Starting restore at 04-APR-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=16 instance=MY_DB1 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to +DATA_ASM/MY_DB/datafile/system.256.807705225

channel ORA_DISK_1: restoring datafile 00002 to +DATA_ASM/MY_DB/datafile/sysaux.257.807705225

channel ORA_DISK_1: restoring datafile 00003 to +DATA_ASM/MY_DB/datafile/undotbs1.258.807705227

channel ORA_DISK_1: restoring datafile 00004 to +DATA_ASM/MY_DB/datafile/users.259.807705229

channel ORA_DISK_1: restoring datafile 00005 to +DATA_ASM/MY_DB/datafile/undotbs2.264.807705403

channel ORA_DISK_1: restoring datafile 00006 to +DATA_ASM/MY_DB/datafile/datax.271.811862519

channel ORA_DISK_1: restoring datafile 00007 to +DATA_ASM/MY_DB/datafile/indx.270.811862579

channel ORA_DISK_1: reading from backup piece /livraison/backup/04o68323_1_1

channel ORA_DISK_1: piece handle=/livraison/backup/04o68323_1_1 tag=TAG20130404T133147

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:15

Finished restore at 04-APR-13

RMAN> alter database open resetlogs;

9. Restart the other nodes

On the first node, and as oracle user:

srvctl start instance -d MY_DB -n node2_name;

1.3      Control

As oragrid:

SQL>  select state,name,type from v$asm_diskgroup;

STATE       NAME                           TYPE

----------- ------------------------------ ------

MOUNTED     DATA_ASM                       EXTERN

/home/dba/oragrid> crsctl status resource ora.DATA_ASM.dg

NAME=ora.DATA_ASM.dg

TYPE=ora.diskgroup.type

TARGET=ONLINE               , ONLINE

STATE=ONLINE on node1_name, ONLINE on node2_name

High Performance Tuning Scripts

Here are some performance tuning scripts, available specially for 10g and 11g.

1. Read Errors from Alert.log : 11g (last 20 days)

select substr(MESSAGE_TEXT, 1, 150) message_text,to_char(cast(ORIGINATING_TIMESTAMP as DATE), 'YYYY-MM-DD') err_timestamp,  count(*) cnt
from X$DBGALERTEXT
where (upper(MESSAGE_TEXT) like '%ORA-%' or upper(MESSAGE_TEXT) like '%ERROR%')and cast(ORIGINATING_TIMESTAMP as DATE) > sysdate - 20                       
group by substr(MESSAGE_TEXT, 1, 150), to_char(cast(ORIGINATING_TIMESTAMP as DATE), 'YYYY-MM-DD')
order by to_char(cast(ORIGINATING_TIMESTAMP as DATE), 'YYYY-MM-DD');

2. SQL statistics (Top 10, last 5 days):

SQL Ordered by Elapsed Time:

select * from (
       select distinct                       
                              round((sum(sql_hist.ELAPSED_TIME_delta)/1000000) ,3) c10,
                              sql_hist.sql_id               c2,   
                              sum(sql_hist.executions_delta)     c3,
                              sum(sql_hist.buffer_gets_delta)    c4,
                              sum(sql_hist.disk_reads_delta )    c5,
                              round( (sum(sql_hist.CPU_TIME_DELTA)/1000000) ,3)     c6,                                                           
                              decode(sum(sql_hist.executions_delta), 0,
                                                             null, round((sum(sql_hist.ELAPSED_TIME_delta)/1000000)/sum(sql_hist.executions_delta),3)
                                    ) c7,                             
                              sql_hist.module             c8    ,
                             to_char( substr(text.sql_text,1,600))  c9,                            
                             decode (sum(sql_hist.ELAPSED_TIME_delta), 0,null,
                                        round((sum(sql_hist.CPU_TIME_DELTA)/sum(sql_hist.ELAPSED_TIME_delta))*100 ,3)
                                    ) c11                            
                            from
                               dba_hist_sqlstat        sql_hist,
                               dba_hist_snapshot         s,
                               dba_hist_sqltext        text                              
                            where
                               s.snap_id = sql_hist.snap_id and
                               s.begin_interval_time >= sysdate -5  and                      
                               text.sql_id =sql_hist.sql_id and
                               text.dbid=sql_hist.dbid 
                               group by  sql_hist.sql_id , sql_hist.module  , to_char( substr
                                (text.sql_text,1,600))   
                               order by c10 desc--, c1 desc
                             )  where rownum <= 10;

SQL ordered by CPU Time

 select * from (

                            select distinct

                              sql_hist.sql_id               c2,   

                              sum(sql_hist.executions_delta )    c3,

                              sum(sql_hist.buffer_gets_delta )   c4,

                              sum(sql_hist.disk_reads_delta  )   c5,

                              round((sum(sql_hist.CPU_TIME_DELTA)/1000000),3)       c6,

                              round((sum(sql_hist.ELAPSED_TIME_delta)/1000000),3) c7,

                              sql_hist.module             c8    ,

                             to_char( substr(text.sql_text,1,600))  c9,

                             decode(sum(sql_hist.executions_delta),0,null, round((sum(sql_hist.CPU_TIME_DELTA)/1000000)/sum(sql_hist.executions_delta),3)) c10    ,

                             decode (sum(sql_hist.ELAPSED_TIME_delta), 0,null,

                                        round((sum(sql_hist.CPU_TIME_DELTA)/sum(sql_hist.ELAPSED_TIME_delta))*100 ,3)

                                    ) c11                                

                            from

                               dba_hist_sqlstat        sql_hist,

                               dba_hist_snapshot         s,

                               dba_hist_sqltext        text

                            where

                               s.snap_id = sql_hist.snap_id and

                               s.begin_interval_time >= sysdate -5  and                      

                               text.sql_id =sql_hist.sql_id and

                               text.dbid=sql_hist.dbid

                               group by  sql_hist.sql_id , sql_hist.module  , to_char( substr(text.sql_text,1,600))   

                             order by c6 desc--, c1 desc

                        ) where rownum <= 10;

SQL Ordered by Gets

 select * from (

                            select distinct                       

                              sql_hist.sql_id               c2,   

                              sum(sql_hist.executions_delta)     c3,

                              sum(sql_hist.buffer_gets_delta )   c4,

                              sum(sql_hist.disk_reads_delta )    c5,

                              round((sum(sql_hist.CPU_TIME_DELTA)/1000000),3)       c6,

                              round((sum(sql_hist.ELAPSED_TIME_delta)/1000000),3) c7,

                              sql_hist.module             c8    ,

                             to_char( substr(text.sql_text,1,600))  c9,

                             decode(sum(sql_hist.executions_delta),0,null, round((sum(sql_hist.buffer_gets_delta))/sum(sql_hist.executions_delta),3)) c10    ,

                             decode (sum(sql_hist.ELAPSED_TIME_delta), 0,null,

                                        round((sum(sql_hist.CPU_TIME_DELTA)/sum(sql_hist.ELAPSED_TIME_delta))*100 ,3)

                                    ) c11                                

                            from

                               dba_hist_sqlstat        sql_hist,

                               dba_hist_snapshot         s,

                               dba_hist_sqltext        text

                            where

                               s.snap_id = sql_hist.snap_id and

                               s.begin_interval_time >= sysdate -5  and                      

                               text.sql_id =sql_hist.sql_id and

                               text.dbid=sql_hist.dbid 

                               group by  sql_hist.sql_id , sql_hist.module  , to_char( substr(text.sql_text,1,600))   

                             order by c4 desc--, c1 desc

                             ) where rownum <= 10;        

3. Optimization:

Current Execution Plan (last execution) for the top query (last 5 days)

   var l_sql_id varchar2(13);
   begin
     select  sql_id--, sql_text
              into    :l_sql_id    
    from
    (
    select distinct                       
                              round((sum(sql_hist.ELAPSED_TIME_delta)/1000000) ,3) c10,
                              sql_hist.sql_id             sql_id
                            from
                               dba_hist_sqlstat        sql_hist,
                               dba_hist_snapshot         s,
                               dba_hist_sqltext        text                              
                            where
                               s.snap_id = sql_hist.snap_id and
                               s.begin_interval_time >= sysdate -5  and                      
                               text.sql_id =sql_hist.sql_id and
                               text.dbid=sql_hist.dbid group by  sql_hist.sql_id , sql_hist.module  , to_char( substr(text.sql_text,1,600))   
                               order by c10 desc--, c1 desc
    ) where rownum <= 1;   
   end;
   /


    SELECT tf.*
    FROM dba_hist_sqltext ht,
    TABLE(dbms_xplan.display_awr(ht.sql_id,NULL,NULL, 'ALL')) tf
     WHERE ht.sql_id = :l_sql_id ;
   

Multiple plan hash values for the top queries ? (Top 10, Last 5 days)

select * from (

      with vue as ( select sql_id from

                                                    (

                                                            select distinct

                                                           --   s.snap_id, to_char(s.begin_interval_time,'mm-dd-yyyy hh24:mi:ss')  c1,

                                                              sql_hist.sql_id               ,                                 

                                                              sql_hist.buffer_gets_delta    c4                                                            

                                                            from

                                                               dba_hist_sqlstat        sql_hist,

                                                          --     dba_hist_snapshot         s,

                                                               dba_hist_sqltext        text,

                                                               (    select max(sql_hist.buffer_gets_delta) buffer_gets_delta, sql_hist.sql_id

                                                                    from   dba_hist_sqlstat        sql_hist, dba_hist_snapshot    s

                                                                    where s.snap_id = sql_hist.snap_id and

                                                                    s.begin_interval_time >= sysdate -5

                                                                    group by  sql_hist.sql_id                                    

                                                               )max_condition

                                                            where

                                                              sql_hist.sql_id =  max_condition.sql_id and

                                                              max_condition.buffer_gets_delta = sql_hist.buffer_gets_delta and     

                                                              -- s.begin_interval_time >= sysdate -5 and

                                                               text.sql_id =sql_hist.sql_id

                                                             --order by c4 desc--, c1 desc

                                                     ) where rownum <= 10

          )

      select

                          vue.SQL_ID

                        , PLAN_HASH_VALUE

                        , sum(EXECUTIONS_DELTA) EXECUTIONS

                        , sum(ROWS_PROCESSED_DELTA) CROWS

                        , trunc(sum(CPU_TIME_DELTA)/1000000/60) CPU_MINS

                        , trunc(sum(ELAPSED_TIME_DELTA)/1000000/60)  ELA_MINS

                        from DBA_HIST_SQLSTAT , VUE

                        where DBA_HIST_SQLSTAT .SQL_ID = vue.sql_id

                        group by vue.SQL_ID , PLAN_HASH_VALUE

                        order by SQL_ID, CPU_MINS

      ) ;

 

4. Events (Last 5 days):

Top 5 Timed Foreground Events:

SELECT * FROM (  SELECT event, waits, TIME, round(100*pct,2) pct , waitclass
        FROM (SELECT   e.event_name event,
           e.total_waits - NVL (b.total_waits, 0) waits,
             (e.time_waited_micro - NVL (b.time_waited_micro, 0)
             )
           / 1000000 TIME,
             (e.time_waited_micro - NVL (b.time_waited_micro, 0)
             )
           / (SELECT SUM (  e1.time_waited_micro
              - NVL (b1.time_waited_micro, 0)
                )
             FROM dba_hist_system_event b1,
               dba_hist_system_event e1
            WHERE b1.snap_id(+) = b.snap_id
              AND e1.snap_id = e.snap_id
              AND b1.dbid(+) = b.dbid
              AND e1.dbid = e.dbid
              AND b1.instance_number(+) = b.instance_number
              AND e1.instance_number = e.instance_number
              AND b1.event_id(+) = e1.event_id
              AND e1.total_waits > NVL (b1.total_waits, 0)
              AND e1.wait_class <> 'Idle') pct,
              e.wait_class waitclass
          FROM dba_hist_system_event b, dba_hist_system_event e,  (select max(snap_id) max_snap_id, min (snap_id) min_snap_id
                             from dba_hist_snapshot
                             where begin_interval_time >= sysdate -5) s                            
          WHERE         b.snap_id = s.min_snap_id
           AND e.snap_id = s.max_snap_id                               
           AND b.event_id(+) = e.event_id
           AND e.total_waits > NVL (b.total_waits, 0)
           AND e.wait_class <> 'Idle'           
          ORDER BY waits DESC
       )
       WHERE ROWNUM < 6;

Five first Database Objects Experienced the Most Number of Waits:

select * FROM (  WITH ORDERED AS
         (
         SELECT
            dba_objects.object_name,
                  dba_objects.object_type,
                 active_session_history.event,
                  sum(active_session_history.wait_time +
                   active_session_history.time_waited) ttl_wait_time
         ,   ROW_NUMBER() OVER ( ORDER BY sum(active_session_history.wait_time +
                           active_session_history.time_waited) desc
                ) AS rn
         FROM
          v$active_session_history active_session_history,
                  dba_objects
          where
                 active_session_history.sample_time between sysdate - 5 and sysdate
                 and active_session_history.current_obj# = dba_objects.object_id
                  group by dba_objects.object_name, dba_objects.object_type, active_session_history.event 
                  order by  ttl_wait_time  desc                              
         )
         SELECT
           object_name,
                  object_type,
                 event,
                   ttl_wait_time
         FROM
          ORDERED
         WHERE
          rn <=6         
      );

 

5. Sessions Activities (Top 10, Last 5 days):

select * from
       (
         select username, module, session_id, sample_time, session_state, event, wait_time, dba_hist_sqltext.sql_id, SQL_TEXT       
         from v$active_session_history,  dba_users, dba_hist_sqltext
         where  dba_users.user_id = v$active_session_history.user_id
         and username not in ('SYS','SYSTEM','CTXSYS','DBSNMP','OUTLN','SYSAUX', 'ORDSYS','ORDPLUGINS','MDSYS','DMSYS','APPQOSSYS', 'WMSYS','WKSYS','OLAPSYS','SYSMAN','XDB','EXFSYS','TSMSYS','MGMT_VIEW','ORACLE_OCM','DIP','SI_INFORMTN_SCHEMA','ANONYMOUS')
         and sample_time between sysdate -5 and sysdate
         and dba_hist_sqltext.sql_id = v$active_session_history.sql_id
         and dba_hist_sqltext.sql_id is not null
         order by wait_time desc ) where rownum <= 10 ;

 

6. Disk I/O

Segments ordered by Physical Reads (Top 10):

 select * from
       (       
         select segment_name,object_type,total_physical_reads
          from ( select owner||'.'||object_name as segment_name,object_type,
           value as total_physical_reads
           from v$segment_statistics
           where statistic_name in ('physical reads')
          order by total_physical_reads desc
          )       
       ) where rownum <= 10   ;

SQL with the highest I/O (Top 10, Last 5 days):

select * from
       (       
         WITH ORDERED AS
                                    (
                                        SELECT
                                               h.sql_id
                                        ,      SUM(10) ash_secs
                                        ,ROW_NUMBER() OVER ( ORDER BY SUM(10) desc
                                                              ) AS rn
                                        FROM   dba_hist_snapshot x
                                        ,      dba_hist_active_sess_history h
                                        WHERE   x.begin_interval_time between sysdate -5 and sysdate
                                        AND    h.SNAP_id = X.SNAP_id
                                        AND    h.dbid = x.dbid
                                        AND    h.instance_number = x.instance_number
                                        and sql_id is not null
                                        GROUP BY h.sql_id
                                        ORDER BY ash_secs desc                                                                    
         )
         SELECT
          ORDERED.rn,
          dba_hist_sqltext.sql_id,
                                     to_char( substr(dba_hist_sqltext.sql_text,1,600)) text
         FROM
          ORDERED, dba_hist_sqltext                                  
         WHERE
                                        ORDERED.sql_id= dba_hist_sqltext.sql_id
          and rn <=10  
                                     ORDER BY rn
       )   ;