View Sidebar

Welcome Oracle DBA!

This site is intended for helping Oracle DBA’s to Administer Databases and make them running faster. We will try our best to publish useful scripts ans methods. The views expressed on this blog are ours and do not reflect the views of Oracle Corporation. The views and opinions expressed by visitors on this blog are theirs, not ours. These programs/scripts are for informational purposes only and we disclaim liability for any damages caused by such use of the Programs.  
Vincent FENOLL Cyrille MODIANO Yann COUSIN
View Vincent Fenoll's profile on LinkedIn View Cyrille Modiano's profile on LinkedIn View Yann Cousin's profile on LinkedIn
 
Latch contention

Latch contention

Library cache latch contention is typically caused by NOT using bind variables. It is due to excessive parsing of statements.

Cache buffers chain latches contention is typically caused by concurrent access to a very “hot” block and the most common type of such a hot block is an index root or branch block (since any index based query must access the root block).

Redo copy/redo allocation latches contention are rarely encountered today. They protect the redolog buffer

A useful and very complete article/scripts on latch contention by Tanel Poder (Oracle 9i to 12c):

http://tech.e2sn.com/oracle/troubleshooting/latch-contention-troubleshooting

The scripts:



Vincent Fenoll DBA Oracle Montreal

2014/05/13Read More
Database Health Check (DBMS_HM)

Database Health Check (DBMS_HM)

The Oracle DBA wants to run Health Checks with Health Monitor upon his database.

With Oracle 11g, these checks can be done:
- DB Structure Integrity Check
- CF Block Integrity Check
- Data Block Integrity Check
- Redo Integrity Check
- Transaction Integrity Check
- Undo Segment Integrity Check
- Dictionary Integrity Check
- ASM Allocation Check

Viewing the list of checks that can be done on your database:

 SELECT name
  FROM v$hm_check
 WHERE internal_check = 'N';

Health checks accept input parameters, some are mandatory while others are optional.

Displaying parameter information for all health checks:

  SELECT c.name check_name,
         p.name parameter_name,
         p.TYPE,
         p.DEFAULT_VALUE,
         p.description
    FROM v$hm_check_param p, v$hm_check c
   WHERE p.check_id = c.id AND c.internal_check = 'N'
  ORDER BY c.name;

Running a check:

BEGIN
   DBMS_HM.run_check ('Dictionary Integrity Check', 'report_dictionary_integrity');
END;
/

or

BEGIN
   DBMS_HM.RUN_CHECK (check_name     => 'Transaction Integrity Check',
                      run_name       => 'my_transaction_run',
                      input_params   => 'TXN_ID=22.87.1');
END;

Viewing the first report in text format with DBMS_HM (HTML & XML format are also available):

SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.get_run_report ('report_dictionary_integrity') FROM DUAL;

Listing all the Health Check executed (Health Monitor View):

SELECT run_id,
       name,
       check_name,
       run_mode,
       status,
       src_incident,
       num_incident,
       error_number
  FROM v$hm_run;
2014/01/09Read More
Kill Session Oracle

Kill Session Oracle

The Oracle DBA wants to kill a session that is consuming abnormally too many resources.

alter system kill session SID, SERIAL# [MyRACInstance]' [immediate];

SID, SERIAL#are provided by V$SESSION
The third and optional parameter is for RAC environment


You can also kill the process directly with an operating system command:
$ kill -9 myPID
2014/01/08Read More
SQL Informations

SQL Informations

The Oracle DBA knows the pid of an Oracle resource-intensive process. Now we want to gather some informations on the SQL Statement (SQL_ID, username, program, terminal…) before running an explain plan.

To find the cpu intensive queries:
http://www.oracle-scripts.net/cpu-issues/

SELECT    'USERNAME : ' || s.username  || CHR (10)   
		|| 'SCHEMA : '  || s.schemaname || CHR (10) 
		|| 'OSUSER : '  || s.osuser    || CHR (10)  
		|| 'PROGRAM : ' || s.program   || CHR (10)  
		|| 'MACHINE : ' || s.machine   || CHR (10)  
		|| 'TERMINAL : ' || s.terminal  || CHR (10)  
		|| 'SPID : '    || p.spid      || CHR (10)  
		|| 'SID : '     || s.sid       || CHR (10)  
		|| 'SERIAL# : ' || s.serial#   || CHR (10)  
		|| 'TYPE : '    || s.TYPE      || CHR (10)  
		|| 'SQL ID : '  || q.sql_id    || CHR (10)  
		|| 'CHILD_NUMBER : '  || q.child_number    || CHR (10)  
		|| 'SQL TEXT : ' || q.sql_text 
          RESULT
  FROM v$session s, v$process p, v$sql q
 WHERE s.paddr = p.addr AND s.sql_id = q.sql_id(+) AND p.spid = '&&MY_PID';
 

To display the Oracle explain plan:
http://www.oracle-scripts.net/display-cursor/

I/O Bottlenecks

I/O Bottlenecks

Databases are experiencing performance problems and the Oracle DBA wants to know if there are I/O Bottlenecks.

$  iostat -xd 5

r/s:  Reads/second
w/s:  Writes/second
kr/s: kilobytes read per second
kw/s: kilobytes written per second
wait: average number of transactions waiting for service (queue length)
actv: average number of transactions actively being serviced
svc_t:average response time  of  transactions,  in  milliseconds
%w:   percent of time there are transactions waiting for service
%b:  percent of time the disk is busy

                  extended device statistics
device      r/s    w/s   kr/s   kw/s wait actv  svc_t  %w  %b
1/md1      56.9    0.0 2104.5    0.0  0.0  0.4    7.9   0  32
1/md0      56.9    0.0 2104.5    0.0  0.0  0.4    7.9   0  32
1/md83     56.9    0.0 2104.5    0.0  0.0  0.4    7.9   0  32
2/md1       0.0   10.8    0.0   86.6  0.0  0.2   16.3   0  18
2/md0       0.0   10.8    0.0   86.6  0.0  0.3   28.1   0  18
2/md210     0.0    0.2    0.0    1.6  0.0  0.0    4.2   0   0

If working with a zone (a virtual operating system abstraction), these commands will not be very helpful as 
you will have difficulties to find which disks are experiencing high I/O activity.

But hopefully, you can have some help with SQL*Plus:

(1) Look for sessions that are currently waiting for I/O resources:
SELECT username,
       program,
       machine,
       sql_id
  FROM v$session
 WHERE event LIKE 'db file%read';

(2) which SQL statements are using a lots of disks:

 col schema format a20
 SELECT *
  FROM (  SELECT parsing_schema_name Schema, SQL_ID,
                 SUBSTR (sql_text, 1, 75) SQL,
                 disk_reads
            FROM v$sql
        ORDER BY disk_reads DESC)
 WHERE ROWNUM < 20;

 And with the result set of (1) or (2):
 set long 1000
 select SQL_FULLTEXT from v$sql where sql_id='xxxxxxxxxxx'

--> Tune your SQL Statement or execute it less times.

After that you can also check the global Oracle wait events:
http://www.oracle-scripts.net/resources-waits/

2014/01/06Read More

Switch to our mobile site