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
       )   ;

Script for Pga memory for each process

Hi,
you can find here a script that displays the pga memory for each process, and specifies the apply name, capture name and propogation name, in the case you use Oracle Streams.

SELECT se.sid,n.name , SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, MAX(se.value) pga_memory, 
       DECODE( r.APPLY_NAME, NULL,  DECODE(coor.APPLY_NAME,NULL, ser.APPLY_NAME, coor.APPLY_NAME),  r.APPLY_NAME ) APPLY_NAM,
       cap.CAPTURE_NAME, q.QNAME, s.USERNAME
FROM v$sesstat se, v$statname n, V$SESSION s, 
V$STREAMS_APPLY_READER r, V$STREAMS_APPLY_coordinator coor, V$STREAMS_APPLY_SERVER ser,
GV$STREAMS_CAPTURE c, dba_capture cap ,
dba_queue_schedules q
WHERE n.statistic# = se.statistic#
AND   s.sid= se.sid
AND n.name IN ('session pga memory')
AND s.SID = r.SID(+) 
AND s.SERIAL# = r.SERIAL#(+)
AND s.SID = coor.SID(+) 
AND s.SERIAL# = coor.SERIAL#(+)
AND s.SID = ser.SID(+) 
AND s.SERIAL# = ser.SERIAL#(+)
AND c.CAPTURE_NAME = cap.CAPTURE_NAME(+)  
AND s.SID            = c.SID(+)
AND s.SERIAL# = c.SERIAL# (+)
AND s.SID            = TO_NUMBER(SUBSTR (q.session_id(+), 1,  INSTR(q.session_id(+), ',')- 1 )) 
AND s.SERIAL# = TO_NUMBER(SUBSTR (q.session_id(+), INSTR(q.session_id(+), ',') +1 ,  LENGTH(q.session_id(+))  - INSTR(q.session_id(+), ',')))
GROUP BY n.name,se.sid, SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4),DECODE( r.APPLY_NAME, NULL,  DECODE(coor.APPLY_NAME,NULL, ser.APPLY_NAME, coor.APPLY_NAME),  r.APPLY_NAME ), cap.CAPTURE_NAME, q.qname, s.USERNAME
ORDER BY 4 DESC ;

How to send emails using oracle ?

How to send emails using oracle ?
A. First create the package :

CREATE OR REPLACE PACKAGE TOTO."PAC_MANAGE_MAIL"  AS        
                      TYPE ARRAY IS TABLE OF VARCHAR2(255);        
                      G_MAIL_CONN utl_smtp.connection;        
                     G_MAILHOST VARCHAR2(64) := 'xxxxxx';        ------------------->email server IP
                     G_CRLF CHAR(2) DEFAULT CHR(13)||CHR(10);
  PROCEDURE PRO_SEND_MAIL (PC_SENDER VARCHAR2,                        
                                                                    PC_FROM VARCHAR2,                        
                                                                    PC_TO ARRAY DEFAULT ARRAY(),                        
                                                                    PC_CC ARRAY DEFAULT ARRAY(),                        
                                                                   PC_BCC ARRAY DEFAULT ARRAY(),                        
                                                                   PC_SUBJECT VARCHAR2,                        
                                                                  PC_TEXT VARCHAR2);
PROCEDURE PRO_WRITE_DATA (PC_TEXT VARCHAR2);
FUNCTION FUN_ADDRESS_EMAIL ( PC_CHAINE VARCHAR2,   PC_RECIPIENTS ARRAY)                
                                                                  RETURN VARCHAR2 ;
END;
/

CREATE OR REPLACE PACKAGE BODY TOTO.PAC_MANAGE_MAIL" AS
       PROCEDURE PRO_SEND_MAIL (PC_SENDER   VARCHAR2,
                            PC_FROM     VARCHAR2,
                            PC_TO       ARRAY DEFAULT ARRAY(),
                            PC_CC       ARRAY DEFAULT ARRAY(),
                            PC_BCC      ARRAY DEFAULT ARRAY(),
                            PC_SUBJECT  VARCHAR2,
                            PC_TEXT     VARCHAR2)IS
    WC_TO_LIST   LONG;
    WC_CC_LIST   LONG;
    WC_BCC_LIST  LONG;
    BEGIN
        G_MAIL_CONN := utl_smtp.open_connection(G_MAILHOST, 25);
        utl_smtp.helo(G_MAIL_CONN, G_MAILHOST);
        utl_smtp.mail(G_MAIL_CONN, PC_SENDER);        WC_TO_LIST  := FUN_ADDRESS_EMAIL('To:', PC_TO);
        WC_CC_LIST  := FUN_ADDRESS_EMAIL('Cc:', PC_CC);
        WC_BCC_LIST := FUN_ADDRESS_EMAIL('Bcc:', PC_BCC);        utl_smtp.open_data(G_MAIL_CONN);
        PRO_WRITE_DATA ('Date : '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ));
        PRO_WRITE_DATA ('From : '||NVL( PC_FROM, PC_SENDER ));
        PRO_WRITE_DATA ('Subject : '||NVL( PC_SUBJECT, 'No subject' ));
        PRO_WRITE_DATA (WC_TO_LIST);
        PRO_WRITE_DATA (WC_CC_LIST);        utl_smtp.write_data(G_MAIL_CONN, ' '||G_CRLF);
        utl_smtp.write_data(G_MAIL_CONN, PC_TEXT);
        utl_smtp.close_data(G_MAIL_CONN);
        utl_smtp.quit(G_MAIL_CONN);
    END;    -- ***************************************************************************************
    -- ***************************************************************************************
    PROCEDURE  PRO_WRITE_DATA (PC_TEXT     VARCHAR2)IS
    BEGIN
      IF (PC_TEXT IS NOT NULL)THEN
          utl_smtp.write_data(G_MAIL_CONN, PC_TEXT||G_CRLF);
      END IF;
    END;    -- ***************************************************************************************
    -- ***************************************************************************************
    FUNCTION FUN_ADDRESS_EMAIL (  PC_CHAINE  VARCHAR2,
                                  PC_RECIPIENTS ARRAY)  RETURN VARCHAR2 IS
      WN_RECIPIENT  LONG;
    BEGIN      FOR i IN 1 .. PC_RECIPIENTS.COUNT LOOP
          utl_smtp.rcpt(G_MAIL_CONN, PC_RECIPIENTS(i) );
          IF(WN_RECIPIENT IS NULL)THEN
              WN_RECIPIENT := PC_CHAINE || PC_RECIPIENTS(i);
          ELSE
              WN_RECIPIENT := WN_RECIPIENT || ', '||PC_RECIPIENTS(i);
          END IF;
      END LOOP;      RETURN WN_RECIPIENT;
    END;END;
/

B. If you use 10g or 11g add some rigths to the user TOTO :

conn sys/...begin
dbms_network_acl_admin.create_acl (
acl => 'utl_mail.xml',
description => 'Allow mail to be send',
principal => 'TOTO',
is_grant => TRUE,
privilege => 'connect'
);
commit;
end;

begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_mail.xml',
principal => 'TOTO',
is_grant => TRUE,
privilege => 'resolve'
);
commit;
end;

begin
dbms_network_acl_admin.assign_acl(
acl => 'utl_mail.xml',
host => 'xxxxx' ------------------->email server IP
);
commit;
end;

C. How to send emails ?

PAC_MANAGE_MAIL.PRO_SEND_MAIL (
PC_SENDER =>'xx@xx',                                
PC_FROM =>'xx@xx',                                
PC_TO   =>('aa@bb','cc@dd'),                                
PC_SUBJECT =>'The subject',                                
PC_TEXT    =>'The text') ;