Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Performance Tuning’ Category

Script to list events in Oracle Database

Posted by Pavan DBA on August 23, 2012


Friends, many a times we will be setting events traces in our databases for various reasons. The below script will give advantage to know what are all the events turned on in our database

Listing All Events

Most events are numbered in the range 10000 to 10999. To dump all event messages in this range use

    SET SERVEROUTPUT ON
   
    DECLARE
      err_msg VARCHAR2(120);
    BEGIN
      dbms_output.enable (1000000);
      FOR err_num IN 10000..10999
      LOOP
        err_msg := SQLERRM (-err_num);
        IF err_msg NOT LIKE ‘%Message ‘||err_num||’ not found%’ THEN
          dbms_output.put_line (err_msg);
        END IF;
      END LOOP;
    END;
    /

On Unix systems event messages are in the formatted text file

    $ORACLE_HOME/rdbms/mesg/oraus.msg

To print detailed event messages (Unix only) use the following script

    event=10000
    while [ $event -ne 10999 ]
    do
        event=`expr $event + 1`
        oerr ora $event
    done

Listing Enabled Events

To check which events are enabled in the current session

    SET SERVEROUTPUT ON
    DECLARE
        l_level NUMBER;
    BEGIN
        FOR l_event IN 10000..10999
        LOOP
            dbms_system.read_ev (l_event,l_level);
            IF l_level > 0 THEN
                dbms_output.put_line (‘Event ‘||TO_CHAR (l_event)||
                ‘ is set at level ‘||TO_CHAR (l_level));
            END IF;
        END LOOP;
    END;
    /

Advertisements

Posted in Performance Tuning, Scripts | Tagged: , , | Leave a Comment »

script to analyze Disk IO’s

Posted by Pavan DBA on July 1, 2012


To Analyze the DISK I/o’s

==========================

 

prompt SESSIONS PERFORMING HIGH I/O > 50000

select p.spid, s.sid,s.process cli_process, s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,lpad(t.sql_text,30) “Last SQL”
from v$session s, v$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
t.disk_reads > 10000
order by t.disk_reads desc;

Posted in Performance Tuning, Scripts | Tagged: | Leave a Comment »

script to check INACTIVE sessions with HIGH DISK IO

Posted by Pavan DBA on July 1, 2012


To check INACTIVE sessions with HIGH DISK IO

=============================================

select p.spid,s.username, s.sid,s.status,t.disk_reads, s.last_call_et/3600 last_call_et_Hrs,
s.action,s.program,s.machine cli_mach,s.process cli_process,lpad(t.sql_text,30) “Last SQL”
from gv$session s, gv$sqlarea t,v$process p
where s.sql_address =t.address and
s.sql_hash_value =t.hash_value and
p.addr=s.paddr and
t.disk_reads > 5000
and s.status=’INACTIVE’
and s.process=’1234′
order by S.PROGRAM;

Posted in Performance Tuning, Scripts | Tagged: , | Leave a Comment »

script to find redo generated by current sessions

Posted by Pavan DBA on March 30, 2012


#################################################

redo generated by current sessions

#################################################

 

select v$session.sid, username, value redo_size

from v$sesstat, v$statname, v$session

where v$sesstat.STATISTIC# = v$statname.STATISTIC#

and v$session.sid = v$sesstat.sid

and name = ‘redo size’

and value > 0

and username is not null

order by value

/

Posted in Performance Tuning, Scripts | Tagged: , , | 12 Comments »

script to find sql text with sid of a session

Posted by Pavan DBA on March 30, 2012


#######################

TO FIND SQL TEXT FROM SID

#######################

 

select a.sid,a.program,b.sql_text

from v$session a, v$sqltext b

where a.sql_hash_value = b.hash_value

and a.sid=429

order by a.sid,hash_value,piece;

Posted in Performance Tuning, Scripts | Tagged: , , , , , , , , | Leave a Comment »

 
%d bloggers like this: