I/O tuning in Oracle : test the I/O throughput and latency:

Part 2: This is part 2 of series I/O tuning

To test the I/O throughput and latency:

Step 1:  Verify if asynchronous I/O is enabled by query

set linesize 130
col name format a70
select
       name
        , asynch_io
    from
        v$datafile d
       inner join v$iostat_file f on d.file# = f.file_no;

NAME                                                                   ASYNCH_IO
---------------------------------------------------------------------- ---------
/u02/oradata/TESTDB/system01.dbf                                       ASYNC_OFF
/u02/oradata/TESTDB/system01.dbf                                       ASYNC_OFF
/u02/oradata/TESTDB/sysaux01.dbf                                       ASYNC_OFF
/u02/oradata/TESTDB/undotbs01.dbf                                      ASYNC_OFF
/u02/oradata/TESTDB/users01.dbf                                        ASYNC_OFF
Step 2:  set the direct and asynchronous I/O:

sqlplus / as sysdba
SQL>alter system set filesystemio_options=setall scope=spfile;
SQL>shutdown immediate
SQL>startup

Step 3: verify asynch I/O

NAME                                                                   ASYNCH_IO
---------------------------------------------------------------------- ---------
/u02/oradata/TESTDB/system01.dbf                                       ASYNC_ON
/u02/oradata/TESTDB/system01.dbf                                       ASYNC_ON
/u02/oradata/TESTDB/sysaux01.dbf                                       ASYNC_ON
/u02/oradata/TESTDB/undotbs01.dbf                                      ASYNC_ON
/u02/oradata/TESTDB/users01.dbf                                        ASYNC_ON

Step 4: run the I/O calibration

SET SERVEROUTPUT ON

 DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
  DBMS_RESOURCE_MANAGER.CALIBRATE_IO(
      1 /* # of disks */
      , 10 /* maximum tolerable latency in milliseconds */
      , iops /* I/O rate per second */
      , mbps /* throughput, MB per second */
      , lat  /* actual latency in milliseconds */
  );
  DBMS_OUTPUT.PUT_LINE('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE('latency  = ' || lat);
  DBMS_OUTPUT.PUT_LINE('max_mbps = ' || mbps);
END;
/

max_iops = 161 [I/O rate per secomd]
latency  = 11 [ I/O latency in miliseconds]
max_mbps = 91 [ maximum throughput read]

Step 5:  check the performed I/O size:

SQL>  select
  2      d.name
  3      , f.file_no
  4      , f.small_read_megabytes
  5      , f.small_read_reqs
  6      , f.large_read_megabytes
  7      , f.large_read_reqs
  8   from
  9      v$iostat_file f
 10      inner join v$datafile d on f.file_no = d.file#
 11   ;
NAME                                   FILE_NO SMALL_READ_MEGABYTES SMALL_READ_REQS LARGE_READ_MEGABYTES LARGE_READ_REQS
----------------------------------- ---------- -------------------- --------------- -------------------- ---------------
/u02/oradata/TESTDB/system01.dbf             1                  277           34308                 2866            3033
/u02/oradata/TESTDB/system01.dbf             1                    0              17                    0               0
/u02/oradata/TESTDB/sysaux01.dbf             2                  191           24235                 2541            2541
/u02/oradata/TESTDB/undotbs01.dbf            3                  269           34381                 3893            3893
/u02/oradata/TESTDB/users01.dbf              4                  302           34763                  579             579
 

I/O tuning in Oracle: filesystemio_options and disk_asynch_io

Part1: This is part one of series I/O tuning.

Most of the performance tuning issues can be related to I/O in any database. Oracle provides only two main parameters to control I/O behaviour these are  filesystemio_options and disk_asynch_io

filesystemio_options alllows you to specify synchronous and asynchronous read/write and direct and indirect read write. By default it is none it means operating systen's default I/O mode is selected which is synchronous read/write and indirect read write that is file system cached read write operations. But since disk_asynch_io parameter defaults to true Oracle supports asynchronous read write by default.

Oracle recommends to set parameter filesystemio_options  to value 'setall' but it is not always good practise especially when SGA is small. setting it to setall lets your Oracle DB perform I/O operations without going to file system cache and it saves overhead of double caching but if SGA is smaller and DB host machine  has large free memory then it is not good to set this parameter to value setall. In this case you should increase DB_CACHE_SIZE and only then set filesystemio_options to setall.

Five years ago I was setting up Oracle database to handle large I/O of new Dataware house project. I set filesystemio_options to setall. This speeded up load operations aproximately by 5% whose caching in file system was not useful as these were insert statements and not repeated. At the same time increased SGA to quite high to cache I/O as much as possible so that repetitive select queries can benefit from SGA as in this case when filesystemio_options was set to setall then file system cache was not available.

So summary is be prudent when setting filesystemio_options to setall to enable direct read/write and asynchronous operations.

Other parameters to affect write (as well as read) is dbwriter_processes. When asynchronous I/O operations are slower in operating system in comparison to synchronous I/O then turn off asynchronous I/O by setting disk_asynch_io to false and set multiple db writer processes by increasing dbwriter_processes values from 1 to 2,3 or 4  suitable value to your system. Alternate is  incrase  dbwr_io_slaves from 0 to 2,3,4 suitable value.

You would be keen to disable asynchronous I/O  when you see high average_wait on event db_file_parallel_wait. Other reason for turning it off will be synchronous I/O is more reliable.

SQL> select event,average_wait from v$system_event where event like 'db file parallel write';
EVENT                                                            AVERAGE_WAIT
---------------------------------------------------------------- ------------
db file parallel write                                                    28.2  [ centi seconds]

This is not a very good ASYNCH I/O. Try Synchronous I/O

Note 1: Asynchronous I/O operations are more prone to block corruptions than synchronous operations so many DBAs disable it and follow practice as mentioned in above paragraph. So if you do not have standby databases and oracle 11g then which autoamatically recovers corrupted block on primary then you would not want asynchronous I/O
Note 2: For 11g R2 for tuning purpose, the “db file async I/O submit” should be treated as   “db file parallel write” in previous releases.

RMAN salient feature introduced from 10g incrementally updated image copy backup-- which is mostly ignored

Incrementally updated image copy backup:

It is faster to restore from RMAN image (copy) backup still   image copy backups were not considered very good practice because it takes longer to take rman copy(image)
backup as they backup whole database block to block backup and also incremental image copy backup was  not possible.

Oracle 10g has introduced incrementally updated image copy backup feature. In this feature first
copy backup was taken and then incremntal backup set is taken and image copy backup is updated with  this incremental backupset. So basically this is three steps task.

first step: update(recover) the image copy backup
second step : take image copy backup if this does not exist before
and then take incremental level 1 backupset(changed block from image copy backup)
RUN {
  RECOVER COPY OF DATABASE WITH TAG 'incr_backup' ;
  BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
}

Note the backuo tags must be same in both rman commands.
Note 1: When above commands are executed for first time. RMAN gives message
"no copy of datafile #n found to recover" as there are no existing image copy backup with given tag
and then take the image copy backup of database.
It does not take the incremental backupset.
Note 2: when above commands are executed for second time, then RMAN again gives message
"no copy of datafile #n found to recover" as there are no existing incremental backupset to apply(recover)
the image copy backup of database with the given tag. and takes the incremental level 1 backupset.

Note3: It performs  two steps: First recover the image copy backup of database with incremental backupset.
and then it takes incremental level 1 backup set(backup of changed blocks since image copy backup of database
was taken with given tag
First run:
RMAN> RUN {
2>   RECOVER COPY OF DATABASE WITH TAG 'incr_backup' ;
3>   BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
4> }
Starting recover at 05-FEB-13
using channel ORA_DISK_1
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
Finished recover at 05-FEB-13
Starting backup at 05-FEB-13
using channel ORA_DISK_1
no parent backup or copy of datafile 1 found
no parent backup or copy of datafile 3 found
no parent backup or copy of datafile 5 found
no parent backup or copy of datafile 2 found
no parent backup or copy of datafile 4 found
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
output filename=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-SYSTEM_FNO-1_5DO179AR tag=INCR_BACKUP recid=85 stamp=806593907
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
output filename=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-SYSAUX_FNO-3_5EO179BK tag=INCR_BACKUP recid=86 stamp=806593922
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
output filename=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-EXAMPLE_FNO-5_5FO179C3 tag=INCR_BACKUP recid=87 stamp=806593929
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
output filename=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-UNDOTBS1_FNO-2_5GO179CB tag=INCR_BACKUP recid=88 stamp=806593934
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
output filename=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-USERS_FNO-4_5HO179CI tag=INCR_BACKUP recid=89 stamp=806593938
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 05-FEB-13
Starting Control File and SPFILE Autobackup at 05-FEB-13
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2013_02_05\O1_MF_S_806593939_8K1J9WD4_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 05-FEB-13
Second run:
RMAN> RUN {
2>   RECOVER COPY OF DATABASE WITH TAG 'incr_backup' ;
3>   BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
4> }
Starting recover at 05-FEB-13
using channel ORA_DISK_1
no copy of datafile 1 found to recover
no copy of datafile 2 found to recover
no copy of datafile 3 found to recover
no copy of datafile 4 found to recover
no copy of datafile 5 found to recover
Finished recover at 05-FEB-13
Starting backup at 05-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 05-FEB-13
channel ORA_DISK_1: finished piece 1 at 05-FEB-13
piece handle=D:\ORATEMP\DEF_LOC_5JO179D2_1_1 tag=TAG20130205T135234 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 05-FEB-13
Starting Control File and SPFILE Autobackup at 05-FEB-13
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2013_02_05\O1_MF_S_806593979_8K1JC4OG_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 05-FEB-13
Third run:
RMAN> RUN {
2>   RECOVER COPY OF DATABASE WITH TAG 'incr_backup' ;
3>   BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
4> }
Starting recover at 05-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile copies to recover
recovering datafile copy fno=00001 name=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-SYSTEM_FNO-1_5DO179AR
recovering datafile copy fno=00002 name=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-UNDOTBS1_FNO-2_5GO179CB
recovering datafile copy fno=00003 name=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-SYSAUX_FNO-3_5EO179BK
recovering datafile copy fno=00004 name=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-USERS_FNO-4_5HO179CI
recovering datafile copy fno=00005 name=D:\ORATEMP\DEF_LOC_DATA_D-ORCL_I-1333581083_TS-EXAMPLE_FNO-5_5FO179C3
channel ORA_DISK_1: reading from backup piece D:\ORATEMP\DEF_LOC_5JO179D2_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ORATEMP\DEF_LOC_5JO179D2_1_1 tag=TAG20130205T135234
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished recover at 05-FEB-13
Starting backup at 05-FEB-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 05-FEB-13
channel ORA_DISK_1: finished piece 1 at 05-FEB-13
piece handle=D:\ORATEMP\DEF_LOC_5LO179EJ_1_1 tag=TAG20130205T135323 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 05-FEB-13
Starting Control File and SPFILE Autobackup at 05-FEB-13
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2013_02_05\O1_MF_S_806594029_8K1JDP83_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 05-FEB-13


Recoverability Window:

If you want to recover to three days ago you can not do from using above backup strategy
so you can alter RMAN commands to as below:

RMAN> RUN {
2> RECOVER COPY OF DATABASE WITH TAG 'incr_backup'  until time 'sysdate-3';
3> BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'incr_backup' DATABASE;
4> }

Now if you want to recover to 27 hours ago you can recover as below:

RMAN { set until time "to_date('03-feb-2013 03:00:00','dd-mon-yy hh:mi:ss am')";
restore database;
recover database;
}
 

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.