Translate

Search This Blog

direct path write temp wait events in oracle

This wait event is similar to direct path write event with only difference that I/O performed are made to temporary files. This wait event signifies sort segment, hashes or bitmap opearation are not fitting in the PGA. This is the case of one pass scan or multi pass scan. This wait event may also occur for creation of global  temporary tables.

So Direct path write temp is an direct access path in which many Oracle blocks are written directly
to the temporary files by the Oracle Server process.  To know which tempfiles are affected query p1 column from v$session.

Note: since temp file numbers are found by adding 200(default value of db_files or whatever the value of db_files has been set, related this P1 information to v$tempfile accordingly.
causes:
  • sorting(order by)
  • sort megre joi
  • hash join
  • union
  • distinct
  • create index
  • creating global temporary tables
action:
  • use order by on indexes column
  • when use hash join make sure hash table can fit into memory(statistics must be gathered accurately else oracle can use bigger table for hashing)
  • avoid sort merge join by using hint no_use_merge
  • if column data is already sorted then use no sort in creating indexes
  • use UNION ALL in place of UNIION
This wait event implies work area size operations are not fitting in PGA so increase PGA_AGGREGATE_TARGET. Other reason for not using the PGA inspite of setting it large is Oracle generally uses 5% to 25% of PGA_AGGREGATE_TARGET value as upper bound in 11g. Other criteria of upper bound is oracle does not use PGA above _PGA_MAX_SIZE  and _shm_max_size hidden parameters which defaults to 200M. So increase _PGA_MAX_SIZE ,_SHM_MAX_SIZE and PGA_AGGREGATE_TARGET.


I increased both PGA parameters in my dataware house databases in 10gR2 and processing time reduced significantltly

direct path write wait events in Oracle

This wait event belongs to "User I/O" wait class and signifies that Oracle Server Process is writing buffers ino datafiles bypassing the SGA. This occurs when direct path append insert operation is performed. This also occirs for CTAS done in parallel and parallel DML. The number of waits is not the same as number of write calls issued if the I/O subsystem supports asynchronous writes.

 Cases: It occurs in the following situations:
  • Parallel DML are issued to create objects (CREATE TABLE hr.admin_emp_dept
    PARALLEL COMPRESS
    AS SELECT * FROM hr.employees
    WHERE department_id = 10;)
  • Parallel DMLS is issued to populate objejcts
  • Direct path loads  ( Insert /*+ append / into table2 select * from table1)
To see what object is undergoing above wait event query V$SESSION or V$SESSION_WAIT parameter columns:

Wait event parameters are explained as below: 
P1: File_id for the write call
P2: Start block_id for the write call
P3: Number of blocks in the write call

select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name like 'direct path write';
NAME                 PARAMETER1           PARAMETER2           PARAMETER3           WAIT_CLASS
-------------------- -------------------- -------------------- -------------------- -------------
direct path write    file number          first dba            block cnt            User I/O
value of P1 and p2 can be passed to DBA_EXTENTS to find the object undergoing direct path write event.

Action Plan: Make sure I/O distribution is balanced and I/O subsystem is adequate to meet parallel I/O demand on data files, Ideally parallel degree should not exceed the actual parallelization data files on differnt devices.


 

direct path read temp wait events

Direct read temp wait event is similar to direct path read wait event except difference
that data which is read from disk into the PGA bypassing the SGA comes from the temp data
files instead of permanent data files. So sort segments, hashes and bit maps etc from
temp files are read into PGA

SELECT p1 “file#”, p2 “block#”, p3 “class#”
 FROM v$session
 WHERE event = ‘direct path read temp’;

Note: since temp file numbers are found by adding 200(default value of db_files or whatever the value of db_files has been set, related this P1 information to v$tempfile accordingly.


SELECT relative_fno, owner, segment_name, segment_type
 FROM dba_extents
 WHERE file_id = &file
 AND &block BETWEEN block_id AND block_id + &blocks – 1;

ASH Query to find direct path read temp waits:

SELECT dba_objects.object_name,
dba_objects.object_type,
ash.event,
p1,p2,p3,
round(SUM(active_session_history.wait_time + active_session_history.time_waited) / 1000000, 2) ttl_wait_time
FROM v$active_session_history ash, dba_objects
WHERE sample_time BETWEEN sysdate-60/1440 and sysdate
AND active_session_history.current_obj# = dba_objects.object_id
AND event LIKE 'direct path read temp%'
GROUP BY dba_objects.object_name, dba_objects.object_type, ash.event,p1,p2,p3
ORDER BY 7 DESC;

How to tune:

1. Individual sessions are not alloted PGA greater than least (5% of PGA_AGGREGATE_TARGET, _PGA_MAX_SIZE)  So set  _pga_max_size to value higher than 200M to large value and increase
PGA_AGGREGATE_PARAMETER

If using shared servers then increase sort_area_size and hash_area_size

2. Query v$PGA_TARGET_ADVICE and see PGA_CACHE_HIT_PERCENTAGE should be 100% also PGA_OVERALLOCATION must be 0.

If you query V$PGASTAT you should PGA should not be overallocated than target.

3. use indexes for sorting

4. Aoid sort operations

5. try use UNION ALL instead UNION

6. try avoid use sort merge join by using hint NO_USE_MERGE
 

Buffer Busy Waits wait events

Buffer busy waits occur when another session is reading the block into the buffer OR Another session holds the buffer in an incompatible mode to our request. This wait event was known as buffer busy wait event before oracle 10.

These waits indicate read/read, read/write, or write/write contention. The Oracle session is waiting to pin a buffer. A buffer must be pinned before it can be read or modified. Only one process can pin a buffer at any one time.

This wait can be intensified by a large block size as more rows can be contained within
the block This wait happens when a session wants to access a database block in the buffer cache
but it cannot as the buffer is "busy It is also often due to several processes repeatedly reading the same blocks (eg: if lots of people scan the same index or data block)

These waits are common in an I/O bound system. These wait events may indicates presence of hot blocks even in tuned queries and presence of un-selective or right hand indexes.

Queries in 10g to find the segments whose data blocks have read contention:

1. SELECT p1 "file#", p2 "block#", p3 "class#",sql_id
FROM v$session
WHERE event = 'buffer busy waits' and username='&USER_NAME';

Parameters P1 and P2 represents the file# and block# while parameter p3 represents the wait_class id. In 9i Parameter P3 represented reason code


2. Pass above selected file# and block# to below query to find to the affected segment

SELECT relative_fno, owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &FILE
AND &BLOCK BETWEEN block_id
AND block_id + blocks - 1;

This block of contention can belong to data block,segement header or undo block.

The main way to reduce buffer busy waits is to reduce the total I/O on the system by tuning the query Depending on the block type, the actions will differ

Data Blocks:
-Eliminate HOT blocks from the application.

-Reduce the number of rows per block( by moving table to tablespace with smaller block size or by below techniques)

-Try rebuilding the object with a higher PCTFREE so that you reduce the number of rows per block.

-Increase INITRANS and MAXTRANS and reduce PCTUSED This will make the table less dense .

-Check for repeatedly scanned /unselective indexes.

-Check for 'right- hand-indexes' (indexes that get inserted into at the same point by many processes).

Segemnt Header:
Use Automate segment management that is bit maps or increase of number of FREELISTs and FREELIST GROUPs

Undo Header:
Increase the number of Rollback Segments

block contention wait events are also recorded in specific view V$WAITSTAT and since V$SESSION has all the wait events data integrated with it from 10g and it also have the row wait information, below query can also be used to find the sql statements.

SELECT
s.p1 file_id, s.p2 block_id,o.object_name obj,
o.object_type otype,
s.SQL_ID,
w.CLASS,event
FROM v$session s,
( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
all_objects o
WHERE
event IN ('buffer busy waits'')
AND
w.CLASS#(+)=s.p3
AND o.object_id (+)= s.row_wait_OBJ#
ORDER BY 1;

SELECT SQL_FULLTEXT from V$SQL WHERE sql_id=&amp

SQL_FULLTEXT is CLOB column which displays full query

ASH samples the active sessions every one second and so we can query v$active_session_history also to get buffer busy waits or read by other session.

SELECT
p1 file_id , p2 block_id ,o.object_name obj,
o.object_type otype,
ash.SQL_ID,
w.CLASS
FROM v$active_session_history ash,
( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
all_objects o
WHERE event='buffer busy waits'
   AND w.CLASS#(+)=ash.p3
AND o.object_id (+)= ash.CURRENT_OBJ#
AND ash.sample_time > SYSDATE - &MIN/(60*24)
ORDER BY 1;

downgrading oracle database to earlier release , 11.2.0.3 to 11.2.0.1 , oracle 11g to 10g

Below are the steps for downgrading oracle databases from 11.2.0.3 to 11.2.0.1 and from 11g to 10g

This assumes a) you do not have oracle valult installed. 2) You do not have oracle application express 3) you do not have objects created from fixed tables 4) database is not configured for Label Security 5) this is single instance database

Step 1 : Timzone data types consideration:

A). If you previously had upgraded the database and then used the DBMS_DST PL/SQL package to update the database time zone version, then you must apply the patch for the same time zone file version into the earlier release's Oracle home before downgrading.

As an example scenario, assume that a release 10.2.0.4 database on Linux x64 using DSTv4 had been upgraded to release 11.2.0.2, and DBMS_DST was then run to update this database to DSTv14. Then, before downgrading from release 11.2.0.3 to 10.2.0.4, you need to apply on the release 10.2.0.4 side the DSTv14 patch for 10.2.0.4 for Linux x64. This ensures that your TIMESTAMP WITH TIME ZONE data is not logically corrupted during retrieval.

To find which time zone file version your database is using, run:

SELECT value$ FROM sys.props$ WHERE NAME = 'DST_PRIMARY_TT_VERSION';
B). If you had set the ORA_TZFILE environment variable to the full path name of the timezone.dat file when you upgraded to Oracle Database 11g Release 2 (11.2), then you must unset it if you subsequently downgrade your database.

Two time zone files are included in the Oracle home directory:

◦The default time zone file at
$ORACLE_HOME/oracore/zoneinfo/timezonelrg.dat
◦A smaller time zone file at
$ORACLE_HOME/oracore/zoneinfo/timezone.dat
If you do not unset the ORA_TZFILE variable, then connecting to the database using the smaller time zone file might produce the following errors:
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

Step 3:  Connect to sys user from higher ORACLE_HOME and run downgrade scripts

sqlplus / as sysdba
 
SQL> STARTUP DOWNGRADE
 
SQL> drop user sysman cascade [ if you have existing sysman user]
 
SQL> spool downgrade.log
 
SQL> @?/rdbms/admin/catdwgrd.sql
  
SQL>spool off

Step 4:  Start Oracle database service from lower ORACLE_HOME and reload old dictionary

A) Now copy init files and password files from higher ORACLE_HOME to lower ORACLE_HOME
 
B) If you are on windows then delete existing Oracle database service by
 
 oradim -delete -sid SID_NAME from Higher ORACLE_HOME\bin

and create oracle service in lower ORACLE_HOME by
 
oradim -new -sid ORCL -startmode auto -srvcstart system
 
C) set ORACLE_HOME=LOWER_ORACLE_HOME or export ORACLE_HOME=LOWER_ORACLE_HOME
 
D) sqlplus / as sysdba [ using binaries of OLD_ORACLE_HOME]
 
SQL>spool reload.log

SQL> startup upgrade
 
SQL> @?/rdbms/admin/catrelod.sql
 
Additional steps:
 
If you are downgrading to Oracle Database 11g Release 1 (11.1.0.6), run the xsrelod.sql script:
SQL> @xsrelod.sql
If you are downgrading to Oracle Database 10g Release 1 (10.1.0.5) and you have XDB in your database, then run the dbmsxdbt.sql script:
@dbmsxdbt.sql
SQL>spool off
 

Step 6: Open the database in normal mode

sqlplus / as sysdba
SQL>shutdown immediate
 
SQL> startup
 
SQL>@?/rdbms/admin/utlrp.sql
 
SQL> col comp_name form a50
SQL> select comp_name,version,status from dba_registry ;

-- done--
 
Query from DBA_REGISTRY should show all components to earlier ORACLE_HOME versions.

Below is output from downgrading 11.2.0.3 to 11.2.0.1
Before upgrade i.e before running catdwgrd in higher ORACLE_HOME:

COMP_NAME                                          VERSION                        STATUS  
-------------------------------------------------- ------------------------------ -----------                                                                                                                                                                                                              
OWB                                                11.2.0.1.0                     VALID                  
Oracle Application Express                         3.2.1.00.10                    VALID   
Oracle Enterprise Manager                          11.2.0.3.0                     VALID   
LAP Catalog                                       11.2.0.3.0                    VALID                                                                                                                                                                                                                    
Spatial                                            11.2.0.3.0                     VALID      
Oracle Multimedia                                  11.2.0.3.0                     VALID
Oracle XML Database                                11.2.0.3.0                     VALID
Oracle Text                                        11.2.0.3.0                     VALID  
Oracle Expression Filter                           11.2.0.3.0                     VALID 
Oracle Rules Manager                               11.2.0.3.0                     VALID  
Oracle Workspace Manager                           11.2.0.3.0                     VALID 
Oracle Database Catalog Views                      11.2.0.3.0                     VALID
Oracle Database Packages and Types                 11.2.0.3.0                     VALID    
JServer JAVA Virtual Machine                       11.2.0.3.0                     VALID 
Oracle XDK                                         11.2.0.3.0                     VALID
Oracle Database Java Packages                      11.2.0.3.0                     VALID   
OLAP Analytic Workspace                            11.2.0.3.0                     VALID                                                                                                                                                                                                                    
Oracle OLAP API                                    11.2.0.3.0                     VALID   


After upgrade:

COMP_NAME                           STATUS      VERSION
----------------------------------- ----------- ----------
Oracle Database Packages and Types  VALID       11.2.0.1.0
Oracle Database Catalog Views       VALID       11.2.0.1.0
JServer JAVA Virtual Machine        VALID       11.2.0.1.0
Oracle XDK                          VALID       11.2.0.1.0
Oracle Database Java Packages       VALID       11.2.0.1.0
Oracle Text                         INVALID     11.2.0.1.0
Oracle XML Database                 VALID       11.2.0.1.0
Oracle Workspace Manager            VALID       11.2.0.1.0
OLAP Analytic Workspace             VALID       11.2.0.1.0
OLAP Catalog                        VALID       11.2.0.1.0
Oracle OLAP API                     VALID       11.2.0.1.0
Oracle Multimedia                   INVALID     11.2.0.1.0
Spatial                             INVALID     11.2.0.1.0
Oracle Expression Filter            VALID       11.2.0.1.0
Oracle Rules Manager                VALID       11.2.0.1.0
Oracle Application Express          VALID       3.2.1.00.1
OWB                                 VALID       11.2.0.1.0

1. Oracle MultiMedia component became VALID after running utlrp.sql
2. For making Oracle Text Valid perform below step in Lower Oracle home:

sqlplus / as sysdba
SQL>shutdown immediate
SQL>startup upgrade
SQL> drop public synonym ctx_filter_cache_statistics;
SQL> drop view ctx_filter_cache_statistics;
SQL>@?/rdbms/admin/catrelod.sql
SQL>shutdown immediate
SQL>startup
SQL>@utlrp.sql

maintaining oracle database control(not actually oracle enterprize manager)

case 0) creating DB control if you already do not have it.

emca -config dbcontrol db -repos create

It will prompt for listener port/home, passwords for sys,dbsnmp and sysman. It will also prompt if you want to use smtp to send email alerts so it needs smtp host name and your email id from which alerts are sent

case 1) If you already have  Oracle DB control existing in your database(which is mistakenly called Oracle Enterprise manager in dba community) and you change your hostname then service of oracle enterprize manager(I will also use same wrong terminology) does not start because of hostname mismatch in configuration. In this case you do not need to drop DB control and repository and create Oracle DB control from scratch. You only need to configure Oracle Enterprize manager again.

So you only need to run below two commands followed by instructions given by oracle.

emca -deconfig dbcontrol db
emca -config dbcontrol db

case 2) If you want to drop oracle enterprize manager before upgrade or downgrade of database then it is good idea you con drop Oracle enterprize manager dbcontrol alongwith repository(sysman) from following command.

emca -deconfig dbcontrol db -repos drop

Some times oracle is able to drop repositort successfully but it can not deconfigure dbcontrol , in this case delete directory my-PC_orcl here my_PC is host name and orcl is dbname for which oem was configured. This case is also required when you want to create enterprize manager again,after dropping it as in this case it will report dbconfig already exists

case 3) if for some reason "emca -deconfig dbcontrol db" does not work then you can try command given in case 2)

case 4) if you think you have corrupted DB contrrol (Oracle enterprize manager) repository then you can run

emca -deconfig dbcontrol db -repose recreate


or
emca -deconfig dbcontrol db -repose create


case 5) Oracle enterprize manager can not be starting due changed time zone of the host, in this case you can set environment variable TZ to point to the original time zone which is recorded in enterprize manager repository

case 6)  getting rid of https for oracle enterprize manager
it is very easy, this comes handy when browsers does not support oem over https or when want fast response

emctl unsecure dbconsole

This will unsecure oracle enterprize manager and you get rid of https

Note: dropping Oracle enterprize manager puts database in quiesce mode so all new operations and connections are stalled. So be prepared for this outage

C:\Users\testuser>emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Aug 22, 2013 8:37:46 PM
EM Configuration Assistant, Version 11.2.0.1.0 Production
Copyright (c) 2003, 2011, Oracle.  All rights reserved.
Enter the following information:
Database SID: ORCL
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.----------------------------------------------------------------------

read only materialized views in oracle

Materialized views(snapshots) are used for reporting as well as read write replication(advanced replication). This post deals with read only materialized view creation for reporting.

Materialized view can be refreshed in three mode:
  1. complete refresh - whole materialized view is truncated and populated from source query using all records. This is slowest refresh but it does not require materialized view logs to be created.
  2. fast refresh  -  MV is updated with changed records. This materialized view requires materialized view logs be created on all base tables or source tables 
  3. force refresh - if MV can not be refreshed from fast mode then mv is refreshed using complete mode. 
Creating materialiez view example: Requires CREATE MATERIALIZED VIEW privilege

1. create materialized view mv_name
  tablespace mv_data
   as select * from base_table;

By default materialized view refresh type is complete. This mv should be refreshed manually and using complete mode of refresh. Base_table is called master table or source table

Example of fast refreshing materialized view: 
2. create materialized view mv1
tablespace mv_data
build immediate
refresh fast
start with sysdate
next sysdate+1
as select t1.rowdid,t2.rowid, t1.c1,t2.c2
   from t1,t2
   where t1.c1=t2.c1;

This is fast refreshing materialized view based on join query and refreshes automatically at 24 hours interval
Build immediate indicates materialized view will actually be instantiated at first refresh.
  • materialized view with join query and fast refresh needs materilaized view log based on primary key as well as rowid  
  • materialized view with join query and fast refresh requires rowid from all source table be included in select list of query of materialized view definition 
so for above mv,  mv log should be created as:
create materialized view log on t1 with primary key,rowid;
create materialized view log on t1 with primary key,rowid;

Creates MLOG$_T1 and MLOG$_T2 tables which contains the change vectors to be used for fast refresh.

Example of fast refreshing materialized view using aggregate query: 
3. create materialized view mv1
refresh fast
start with sysdate
next sysdate+1
build immediate
as select t1.rowid,t2.rowid, t1.c1,t2.c2,sum(t1.c3)
   from t1,t2
   where t1.c1=t2.c1
   group by t1.rowid,t2.rowid,t1.c1,t2.c2;
  • materialized view with with aggregate function needs materialized view log be created based on new values
so for above mv log should be created as
create materialized view log on t1 with primary key,rowid including new values;
create materialized view log on t1 with primary key,rowid including new values;

Alter the materialized vew refresh schedule: 
alter materialized view mv1
refresh start with sysdate next sysdate+/10/1440

Manually refreshing a materialized view:
  • complete refreshing mv:  exec dbms_mview.refresh('materialized_view_name','C')
  • fast refreshing mv:  exec dbms_mview.refresh('materialized_view_name','F')
  • force refreshing mv:  exec dbms_mview.refresh('materialized_view_name','?')
v$mvrefresh view is used to know the currently refreshing materialized view.

Refresh on commit:
  • IN order to refresh materialized view on commit on refresh commit object privilege needs to be granted to user creating materialized view or on commit refresh system privilege needs to be granted to user creating materialized view.
create materialized view mv1
refresh fast on committ
as select t1.c1,t2.c2
   from t1,t2
   where t1.c1=t2.c1

Query rewrite:

create materialized view mv1
refresh fast on demand
enable query rewrite
as select t1.c1,t2.c2
   from t1,t2
   where t1.c1=t2.c1

  • This mv will be refreshed manually only and query rewrite has been enabled. This mv requires user be granted global query rewrite system privilege be granted or query rewrite object privilege be granted on source tables.


note:Materialized view can be indexed like any oracle table.
 
Some cases:
 
1. create materialized view mv1
refresh fast
start with sysdate next sysdate+1/144
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2 from t1,t2  where t1.c1=t2.c1
Error at line 1
ORA-12032: cannot use rowid column from materialized view log on "SCOTT"."T2"
 
Remedy:
 
alter materialized view log on t2 add rowid
 
2.  create materialized view mv1
refresh fast
start with sysdate next sysdate+1/144
as select  t1.c1 c1, t2.c1 c2 from t1,t2  where t1.c1=t2.c1
Error at line 1
ORA-12052: cannot fast refresh materialized view SCOTT.MV1

Remedy:
 
Create materialized view log on tables t1 and t2 and include rowid of both tables in query
 
3.  create materialized view mv1
refresh fast
start with sysdate next sysdate+1/144
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2, sum(t1.c1) from t1,t2  where t1.c1=t2.c1 group by t1.c1,t2.c1 ,t1.rowid,t2.rowid

ORA-32401: materialized view log on "SCOTT"."T2" does not have new values


 



Remedy: 
 
Since this materialized view uses aggregate function so include new values clause in create materialized view log statement for both tables t1 and t2
 
drop materialized view log on t1
create  materialized view log on t1 with primary key,rowid including new values
 
drop materialized view log on t2
create  materialized view log on t2 with primary key,rowid including new values
 
create materialized view mv1
refresh fast
start with sysdate next sysdate+1/144
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2, sum(t1.c1) from t1,t2  where t1.c1=t2.c1 group by t1.c1,t2.c1 ,t1.rowid,t2.rowid
4.  create materialized view mv1
  refresh Fast
   start with sysdate next sysdate+1/144
  as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2 from t1@"u2",t2@"u2"  where t1.c1=t2.c1
  /
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2 from t1@"u2",t2@"u2"  where t1.c1=t2.c1
                                                                                                     *
ERROR at line 4:
ORA-12028: materialized view type is not supported by master site @"U2"
 
Remedy :
 
Master site and materialized view site should have same version of oracle database

Orace DBA scripts: wait event scripts

-- sessions with highest CPU consumption
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 3600 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours

-- sessions with the highest time for a certain wait
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;

-- sessions with highest DB Time usage
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND  sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0

--session statistics for a particular session :
 select  s.sid,s.username,st.name,se.value
from v$session s, v$sesstat se, v$statname st
where s.sid=se.SID and se.STATISTIC#=st.STATISTIC#
--and st.name ='CPU used by this session'
and s.username='&USERNAME'
order by s.sid,se.value desc

If non DBA user want to see its own statistics then it should grants as below:
grant select on sys.V_$SESSION to username;
grant select on sys.V_$STATNAME to username;
grant select on sys.V_$MYSTAT to username;

and replace view v$sesstat by v$mystat

another useful grants :
grant select on sys.V_$PROCESS to username;
grant select on sys.DBA_2PC_PENDING to username;
grant select on sys.v_$SQLTEXT to username;
grant select on sys.v_$SQL to username;

Oracle DBA scripts: DBA_ACTIVE _SESSION _HISTORY queries

-- top users, program,modules,machine,sql_id
SELECT * FROM (
 SELECT count(*) AS count,USERNAME program, module, machine, sql_id
 FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY, DBA_USERS
 WHERE  DBA_HIST_ACTIVE_SESS_HISTORY.USER_ID=DBA_USERS.USER_ID
 AND session_type='FOREGROUND'
 AND  sample_time > sysdate-60/1440
 AND sample_time < sysdate
 --AND event = 'library cache: mutex X'
 GROUP BY USERNAME, program, module, machine, sql_id
 ORDER BY count(*) DESC
)
WHERE rownum <= 20

-- top temp segments given than a threshold
SELECT * FROM (
  SELECT count(*) AS count,username, program, module, machine, sql_id,sum(temp_space_allocated)
  FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY,DBA_USERS
  WHERE DBA_HIST_ACTIVE_SESS_HISTORY.USER_ID=DBA_USERS.USER_ID
  AND sample_time > sysdate-60/1440
  AND sample_time < sysdate
  AND temp_space_allocated > 100*1024*1024
  GROUP BY USERNAME, program, module, machine, sql_id
  ORDER BY count(*) DESC
)
WHERE rownum <= 20
/
--temp space usage and pattern as time grow taken by particular query whose sql_id is listed above
SELECT    sql_id,
       TO_CHAR(sample_time,'DD-MON-YYYY HH24:MI:SS') AS sample_time,
       temp_space_allocated/1024/1024
FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY
WHERE sample_time > sysdate-1
AND sample_time < sysdate
AND sql_id = '6x2bkbryfk69s'
ORDER BY sample_time

SELECT * FROM sys.dba_hist_sqltext WHERE sql_id = '&SQL_ID'
SELECT user_id, username FROM sys.dba_users WHERE user_id = '&USER_ID'

--sessions and SQL to which top allocated pga
SELECT * FROM (
  SELECT
         count(*) AS count,
         user_id, program, module, machine, sql_id
  FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY
  WHERE sample_time > sysdate-1
  AND sample_time < sysdate
  AND pga_allocated > 10*1024*1024
  GROUP BY user_id, program, module, machine, sql_id
  ORDER BY count(*) DESC
)
WHERE rownum <= 20

SELECT    sql_id,
       TO_CHAR(sample_time,'DD-MON-YYYY HH24:MI:SS') AS sample_time,
       pga_allocated
FROM SYS.DBA_HIST_ACTIVE_SESS_HISTORY
WHERE sample_time > sysdate-1
AND sample_time < sysdate
AND sql_id = '&SQL_ID'
ORDER BY sample_time;

Oracle DBA scripts: Active Session History Queries

-- TOP events
select event,
sum(wait_time +time_waited) ttl_wait_time
from v$active_session_history
where sample_time between sysdate - 60/2880 and sysdate
group by event
order by 2

-- Top sessions
select sesion.sid,
sesion.username,
sum(ash.wait_time + ash.time_waited)/1000000/60 ttl_wait_time_in_minutes
from v$active_session_history ash, v$session sesion
where sample_time between sysdate - 60/2880 and sysdate
and ash.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3 desc

--Top queries
SELECT active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
SUM(active_session_history.wait_time +
active_session_history.time_waited)/1000000 ttl_wait_time_in_seconds
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
and dba_users.username <>'SYS'
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4 DESC

-- Top segments
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
FROM v$active_session_history active_session_history,
dba_objects
WHERE active_session_history.sample_time BETWEEN SYSDATE - 1 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 4 DESC

-- Most IO
SELECT sql_id, COUNT(*)
FROM gv$active_session_history ash, gv$event_name evt
WHERE ash.sample_time > SYSDATE - 1/24
AND ash.session_state = 'WAITING'
AND ash.event_id = evt.event_id
AND evt.wait_class = 'User I/O'
GROUP BY sql_id
ORDER BY COUNT(*) DESC;

SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQL_ID));

-- Top 10 CPU consumers in last 60 minutes
select * from
(
select session_id, session_serial#, count(*)
from v$active_session_history
where session_state= 'ON CPU' and
 sample_time > sysdate - interval '60' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;

-- Top 10 waiting sessions in last 60 minutes
select * from
(
select session_id, session_serial#,count(*)
from v$active_session_history
where session_state='WAITING'  and
 sample_time >  sysdate - interval '60' minute
group by session_id, session_serial#
order by count(*) desc
)
where rownum <= 10;

-- Find session detail of top sid by passing sid
select  serial#,
 username,
 osuser,
 machine,
 program,
 resource_consumer_group,
 client_info
from v$session where sid=&sid;


-- Find different sql_ids of queries executed in above  top session by-passing sid
select distinct sql_id, session_serial# from v$active_session_history
where sample_time >  sysdate - interval '60' minute
and session_id=&sid

--Find full sqltext (CLOB) of above sql
select sql_fulltext from v$sql where sql_id='&sql_id'

--find session wait history of above found top sessionselect * from v$session_wait_history where sid=&sid

--find all wait events for above top session
select event, total_waits, time_waited/100/60 time_waited_minutes,
       average_wait*10 aw_ms, max_wait/100 max_wait_seconds
from v$session_event
where sid=&sid
order by 5 desc

--session statistics for above particular top session :
select s.sid,s.username,st.name,se.value
from v$session s, v$sesstat se, v$statname st
where s.sid=se.SID and se.STATISTIC#=st.STATISTIC#
--and st.name ='CPU used by this session'
--and s.username='&USERNAME'
and s.sid='&SID'
order by s.sid,se.value desc
 

find blocking locks in oracle

It has been easier to find blocking locks from 10g onwards as v$session has blocker session id and blocker instance number.

select sid waiter_sid ,sql_id waiting_sql_id ,  blocking_session,blocking_instance
from v$session
where blocking_session is not null;

Before 10g you would query DBA_WAITERS and DBA_BLOCKERS to fnd waiter and blocker sessions.

Other way before 10g when you do not have above views or do not want to run script utllockt.sql then you would need to rely on V$LOCK

select
a.sid ,
(select serial# from v$session c where a.sid = c.sid) srl,
(select substr(machine,1,8) from v$session c where a.sid = c.sid) machine,
(select substr(program,1,11) from v$session c where a.sid = c.sid) program,
' is blocking ',
b.sid,
(select serial# from v$session c where b.sid = c.sid) srl,
(select substr(machine,1,8) from v$session c where b.sid = c.sid) machine,
(select substr(program,1,11) from v$session c where b.sid = c.sid) program
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
       SID        SRL MACHINE  PROGRAM     'ISBLOCKING'         SID        SRL MACHINE  PROGRAM
---------- ---------- -------- ----------- ------------- ---------- ---------- -------- -----------
       442         17 UAT-DB   JDBC Thin C  is blocking         394      21980 UAT-DB   JDBC Thin C
       442         17 UAT-DB   JDBC Thin C  is blocking         479         20 UAT-DB   JDBC Thin C

Alternate :

SELECTvh.SID locking_sid, vs.status status, vs.program program_holding, vw
.SID waiter_sid, vsw.program program_waiting
FROM v$lock vh, v$lock vw, v$session vs, v$session vsw
WHERE (vh.id1, vh.id2) IN (SELECT id1, id2
FROM v$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM v$lock
WHERE lmode = 0)
AND vh.id1 = vw.id1
AND vh.id2 = vw.id2
AND vh.request = 0
AND vw.lmode = 0
AND vh.SID = vs.SID
AND vw.SID = vsw.SID;

Additionally you can add criteria block=1 for blocker row from v$lock

Find the particular row for which there is row level lock contention:

select *
from &1..&2
where rowid =
        dbms_rowid.rowid_create (
                rowid_type      =>  1,
                object_number   => &3,
                relative_fno    => &4,
                block_number    => &5,
                row_number      => &6
        )
/

Pass it schema name, Object name,Object number, relative_fno , block# and row# . You can find these parameter values from v$session for sid which is blocked.

get Advisors, ADDM and AWR stored queries


set linesize 200
col BEGIN_INTERVAL_TIME format a70
select * from (select snap_id,begin_interval_time from dba_hist_snapshot order by begin_interval_time desc) where rownum < 3;
---------------------------------------------
Set pages 1000
Set lines 75
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message           : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message    : '||c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,
Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%' and a.status='COMPLETED'
and a.description like '%4782%'
Order by b.impact, d.rank;

----------------------------------------------------------- cat

get_addm_report.sql which gets each task from the last snapshot from dba_advisor_tasks


set long  10000000
set pagesize 50000
column get_clob format a80

select dbms_advisor.get_task_report (task_name) as ADDM_report
from dba_advisor_tasks
where task_id = (
        select max(t. task_id)
        from dba_advisor_tasks t, dba_advisor_log l
        where t.task_id = l.task_id
        and t.advisor_name = 'ADDM'
        and l.status = 'COMPLETED');


---------------------------------------------------------------
export ORACLE_SID=$1
export ORAENV_ASK=NO
. oraenv
sqlplus -S / as sysdba <set echo off
set lines 100
set pages 200
set trimspool on
set termout off
set feedback off

column dcol new_value mydate noprint
select to_char(sysdate,'YYMMDD') dcol from dual;

spool /home/oraprd/scripts/dbreport_$1_&mydate..txt
ttitle 'Average Active Sessions in the last week: Instance $1'
column sample_hour format a16
select
   to_char(round(sub1.sample_time, 'HH24'), 'YYYY-MM-DD HH24:MI') as sample_hour
,    round(avg(sub1.on_cpu),1) as cpu_avg
,    round(avg(sub1.waiting),1) as wait_avg
,    round(avg(sub1.active_sessions),1) as act_avg
,    round( (variance(sub1.active_sessions)/avg(sub1.active_sessions)),1) as act_var_mean
from
   ( -- sub1: one row per second, the resolution of SAMPLE_TIME
     select
        sample_id,
        sample_time,
        sum(decode(session_state, 'ON CPU', 1, 0))  as on_cpu,
        sum(decode(session_state, 'WAITING', 1, 0)) as waiting,
        count(*) as active_sessions
     from
        dba_hist_active_sess_history
     where
        sample_time > sysdate - 7
     group by
        sample_id,
        sample_time
   ) sub1
group by
   round(sub1.sample_time, 'HH24')
order by
   round(sub1.sample_time, 'HH24');


ttitle 'Most expensive queries in the last week: Instance $1'
-- gets most expensive queries
-- (by time spent, change "order by" to use another metric)
-- after a specific date
select
   sub.sql_id,
   sub.seconds_used,
   sub.executions,
   sub.gets
from
   ( -- sub to sort before rownum
     select
        sql_id,
        round(sum(elapsed_time_delta)/1000000) as seconds_used,
        sum(executions_delta) as executions,
        sum(buffer_gets_delta) as gets
     from
        dba_hist_snapshot natural join dba_hist_sqlstat
     where
        begin_interval_time > sysdate - 7
     group by
        sql_id
     order by
        2 desc
   ) sub
where
   rownum < 30
;


set long 32768
ttitle 'Text for most expensive SQL in the last week: Instance $1'
select sql_text
from dba_hist_sqltext
where sql_id =
(
select sub.sql_id
from
   ( -- sub to sort before rownum
     select
        sql_id,
        round(sum(elapsed_time_delta)/1000000) as seconds_since_date,
        sum(executions_delta) as execs_since_date,
        sum(buffer_gets_delta) as gets_since_date
     from
        dba_hist_snapshot natural join dba_hist_sqlstat
     where
        begin_interval_time > sysdate - 7
     group by
        sql_id
     order by
        2 desc
   ) sub
where
   rownum = 1
);

spool off;
exit

top query from awr(revised)

Previous query was blogged in http://orababy.blogspot.in/2013/07/simplification-of-use-of-oracle-10g11g.html  and http://orababy.blogspot.in/2013/08/find-top-resource-intensive-queries-and.html

Here is revised top query, this time presented with sqlplus formatting:

set feedback off
set pagesize 50000
set linesize 8000
set trimspool on
set long 65535
set verify off
set verify off
set serveroutput on size 1000000
alter session set "_optimizer_cartesian_enabled"=false;
alter session set optimizer_mode=RULE;
alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS';
Set Heading On
Set Feedback On
PROMPT
PROMPT **Top 50 Sql last 7 Days(s)**
PROMPT
set pages 10000 linesize 500
set trimspool on
COLUMN parsing_schema_name JUSTIFY C FORMAT a15 HEADING 'Parsing|Schema'
COLUMN '%TOT%' JUSTIFY C FORMAT a6 HEADING '%Tot%'
COLUMN 'SEC/EXE' JUSTIFY C FORMAT 99999.999 HEADING 'Sec|Exec'
COLUMN sql_text JUSTIFY C FORMAT a95 word_wrap HEADING 'SQLText'
COLUMN EXECUTIONS JUSTIFY C format 9,999,999,999 HEADING Executions
COLUMN DISK_READS JUSTIFY C format 9,999,999,999 HEADING DiskReads
COLUMN BUFFER_GETS JUSTIFY C format 9,999,999,999 HEADING BufferGets
COLUMN ELAPSED_TIME JUSTIFY C format 9,999,999,999 HEADING ElapsedTime
COLUMN CPU_TIME JUSTIFY C format 9,999,999,999 HEADING CpuTime
COLUMN ROWS_PROCESSED JUSTIFY C format 9,999,999,999 HEADING
RowsProcessed
COLUMN RANK noprint


SELECT
DISTINCT sub.parsing_schema_name,
sub.sql_id,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
DBMS_LOB.SUBSTR(DHST.sql_text,4000,1),CHR(10),' '),CHR(9),' '),' ',' '),'FROM',CHR(10)||'FROM'),
'from',CHR(10)||'from'),'AND ',CHR(10)||'AND '),'and ',CHR(10)||'and '),'WHERE ',CHR(10)||'WHERE '),
'where ',CHR(10)||'where ') SQL_TEXT,
sub.EXECUTIONS,ROUND((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)||'%' "%TOT%",
sub.DISK_READS,ROUND((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)||'%' "%TOT%",
sub.BUFFER_GETS,ROUND((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)||'%' "%TOT%",
sub.ELAPSED_TIME,ROUND((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100)||'%' "%TOT%",
sub.CPU_TIME,ROUND((sub.CPU_TIME/sub2.CPU_TIME_TOTAL)*100)||'%' "%TOT%",
ROWS_PROCESSED,
sub.SEC_PER_EXEC "SEC/EXE",
ROUND((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)+
ROUND((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)+
ROUND((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)+
ROUND((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100) RANK
FROM DBA_HIST_SQLTEXT DHST,
(
SELECT DISTINCT
SQL_ID,
PARSING_SCHEMA_NAME,
ROUND(SUM(EXECUTIONS_DELTA)) AS EXECUTIONS,
ROUND(SUM(PARSE_CALLS_DELTA)) AS PARSE_CALLS,
ROUND(SUM(DISK_READS_DELTA)) AS DISK_READS,
ROUND(SUM(BUFFER_GETS_DELTA)) AS BUFFER_GETS,
ROUND(SUM(ROWS_PROCESSED_DELTA)) AS ROWS_PROCESSED,
ROUND(SUM(CPU_TIME_DELTA/1000000)) AS CPU_TIME,
ROUND(SUM(ELAPSED_TIME_DELTA/1000000)) ELAPSED_TIME,
ROUND(SUM(IOWAIT_DELTA)/1000000) AS IOWAIT,
SUM(ELAPSED_TIME_DELTA/1000000)/DECODE(SUM(EXECUTIONS_DELTA),0,1,SUM(EXECUTIONS_DELTA)) SEC_PER_EXEC
FROM
dba_hist_snapshot
NATURAL join
dba_hist_sqlstat DHS
NATURAL join
dba_hist_sql_plan DHSP
WHERE
BEGIN_INTERVAL_TIME >= SYSDATE-7
AND
parsing_schema_name NOT IN ('SYS','SYSTEM')
AND
object_owner NOT IN ('SYS','SYSTEM')
GROUP BY
SQL_ID,PARSING_SCHEMA_NAME
) sub,
(
SELECT DECODE(ROUND(SUM(EXECUTIONS_DELTA)),0,1,ROUND(SUM(EXECUTIONS_DELTA))) AS EXECUTIONS_TOTAL,
DECODE(ROUND(SUM(DISK_READS_DELTA)),0,1,ROUND(SUM(DISK_READS_DELTA))) AS DISK_READS_TOTAL,
DECODE(ROUND(SUM(BUFFER_GETS_DELTA)),0,1,ROUND(SUM(BUFFER_GETS_DELTA))) AS BUFFER_GETS_TOTAL,
DECODE(ROUND(SUM(ELAPSED_TIME_DELTA/1000000)),0,1,ROUND(SUM(ELAPSED_TIME_DELTA/1000000))) AS ELAPSED_TIME_TOTAL,
DECODE(ROUND(SUM(CPU_TIME_DELTA/1000000)),0,1,ROUND(SUM(CPU_TIME_DELTA/1000000))) AS CPU_TIME_TOTAL
FROM
dba_hist_snapshot
NATURAL join
dba_hist_sqlstat DHS
NATURAL join
dba_hist_sql_plan DHSP
WHERE
BEGIN_INTERVAL_TIME >= SYSDATE-7
AND
parsing_schema_name NOT IN ('SYS','SYSTEM')
AND
object_owner NOT IN ('SYS','SYSTEM')
) sub2
WHERE DHST.sql_id = sub.sql_id
AND ROUND((sub.EXECUTIONS/sub2.EXECUTIONS_TOTAL)*100)+
ROUND((sub.DISK_READS/sub2.DISK_READS_TOTAL)*100)+
ROUND((sub.BUFFER_GETS/sub2.BUFFER_GETS_TOTAL)*100)+
ROUND((sub.ELAPSED_TIME/sub2.ELAPSED_TIME_TOTAL)*100) > 4
AND ROWNUM < 51
AND sub.SEC_PER_EXEC >= .001
ORDER BY RANK DESC

direct path read waits

Direct path read waits are associated with direct read operations. This wait event falls under the User I/O wait class. An Oracle direct read operation reads data directly into the session's PGA , bypassing the SGA. The data in PGA is not shared with other sessions.

Direct reads may be performed in synchronous or asynchronous mode depending on the platform , and the value of the DISK_ASYNC_IO parameter

A significant number of direct path read waits is most likely an application issue

Common causes, Diagnosis and Actions:

Till Oracle 9i this wait events occured for SQL statements with functions that require sorts such as ORDER BY, GROUP BY, UNION,DISTINCT and ROLLUP, HASH PARTITIONS that do not fit into SQL Work Area.

From Oracle 10g this wait event has been divided in two wait events 1. direct path read and 2. direct path read temp

Direct path read waits  in 10g onward occurs for read operations by parallel slaves used in parallel query. While direct path read temp waits occur for sort operations, hashes,index creation etc

This wait event has three parameters: FILE#, FIRST BLOCK#, and BLOCK COUNT.

If level 8 or level 12 10046 trace is enabled for session performing direct path read then number of blocks counts is shown by parameter p3 .

If session performing direct path read is traced with strace , truss, tusc or trace then size of block chunk of direct read can be seen

Note: There is a separate directr read wait event for LOB segments: direct path read(lob). This wait event applies for LOBs that are stored as NOCACHE. When LOBs are stored as CACHE reads and writes go through the SGA and show up as db file sequential reads.

Avoid sorting or hashing large tables else Increase PGA_AGGREGATE_TARGET or MEMORY_MAX_TARGET  after querying V$PGASTAT and V$PGA_TARGET_ADVICE or V$MEMORY_TARGET_ADVICE



read by other session wait events

Read by other session or buffer busy waits occur a when another session is reading the block into the  buffer  OR Another session holds the buffer in an  incompatible mode to our request.   This wait event was known as buffer busy wait event before oracle 10.

These waits indicate read/read, read/write, or write/write contention. The Oracle session is waiting to pin a buffer. A buffer must be pinned before it can be read or modified. Only one process can pin a buffer at any one time.

This wait can be intensified by a large block  size as more rows can be contained within
the block  This wait happens when a session wants to  access a database block in the buffer cache
but it cannot as the buffer is "busy  It is also often due to several processes  repeatedly reading the same blocks (eg: if lots of people scan the same index or data  block)

These waits are common in an I/O bound system.  These wait events may indicates presence of  hot blocks even in tuned queries and presence of un-selective or right hand indexes.

Queries in 10g to find the segments whose data blocks have read contention:

1. SELECT p1 "file#", p2 "block#", p3 "class#",sql_id
FROM v$session
WHERE event = 'read by other session' and username='&USER_NAME';

Parameters P1 and P2 represents the file# and block# while parameter p3 represents the wait_class id. In 9i Parameter P3 represented reason code


2. Pass above selected file# and block# to below query

SELECT relative_fno, owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &FILE
AND &BLOCK BETWEEN block_id
AND block_id + blocks - 1;


This block of contention can belong to data block,segement header or undo block.


The main way to reduce buffer busy waits is to reduce the total I/O on the system by tuning the query  Depending on the block type, the actions will differ

Data Blocks:
-Eliminate HOT blocks from the application.

-Reduce the number of rows per block( by moving table to tablespace with smaller block size or by below techniques)

-Try rebuilding the object with a higher PCTFREE so that you reduce the number of rows per block.

-Increase INITRANS and MAXTRANS and reduce PCTUSED This will make the table less dense .

-Check for repeatedly scanned /unselective indexes.

-Check for 'right- hand-indexes' (indexes that get inserted into at the same point by many processes).

Segemnt Header:
Use Automate segment management that is bit maps or  increase of number of FREELISTs and FREELIST GROUPs

Undo Header:
Increase the number of Rollback Segments


block contention wait events are also recorded in specific view V$WAITSTAT and since V$SESSION has all the wait events data integrated with it from 10g and it also have the row wait information, below query can also be used to find the sql statements.


SELECT
      s.p1 file_id, s.p2 block_id,o.object_name obj,
       o.object_type otype,
       s.SQL_ID,
       w.CLASS,event
FROM v$session s,
     ( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
     all_objects o
WHERE
 event IN ('read by other session')
AND
    w.CLASS#(+)=s.p3
   AND o.object_id (+)= s.row_wait_OBJ#
ORDER BY 1;

SELECT SQL_FULLTEXT from V$SQL WHERE sql_id=&amp

SQL_FULLTEXT is CLOB column which displays full query

ASH samples the active sessions every one second and so we can query v$active_session_history also to get buffer busy waits or read by other session.

SELECT
     p1 file_id ,  p2  block_id ,o.object_name obj,
       o.object_type otype,
       ash.SQL_ID,
       w.CLASS
FROM v$active_session_history ash,
     ( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
      all_objects o
WHERE event='read by other session'
   AND w.CLASS#(+)=ash.p3
   AND o.object_id (+)= ash.CURRENT_OBJ#
      AND ash.sample_time > SYSDATE - &MIN/(60*24)
ORDER BY 1;

Follow by Email