How to detect locks in SQL sessions

To find out v$session holding lock:
SQL> select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not null;

To kill a locked session, first need to find sid, serial and use
SQL> alter system kill session 'sid, serial#';
*** you need have dba priviledge to kill sessions

 To find which SQL has lock wait:
SQL> select sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value, piece;

If #3 is a parameterized SQL, use V$SQL_BIND_CAPTURE to display information on bind variables used by SQL cursors. Each row in the view contains information for one bind variable defined in a cursor.
SQL> select * from V$SQL_BIND_CAPTURE where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value;

SQL to check deadlocks:
SQL> select    c.owner,    c.object_name,    c.object_type,    b.sid,    b.serial#,    b.status,    b.osuser,    b.machine from    v$locked_object a ,    v$session b,    dba_objects c where    b.sid = a.session_id and    a.object_id = c.object_id;

Oracle - SQL diagnostic reports

AWR (Automatic Workload Repository) report: Oracle through snapshots collects, process and maintains performance statistics that can be accessed via AWR reports.

Generating AWR report:
SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql

Also see: these related AWR reports under the same location:
— awrrpt.sql
Displays various statistics for a range of snapshots Ids.
— awrrpti.sql
Displays statistics for a range of snapshot Ids on a specified database and instance.
— awrsqrpt.sql
Displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a particular SQL statement.
— awrsqrpi.sql
Displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL.
— awrddrpt.sql
Compares detailed performance attributes and configuration settings between two selected time periods.
— awrddrpi.sql
Compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

ASH (Active Session History) report: displays top session activities during AWR snapshots.

Generating ASH report: SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql
SQL> @$ORACLE_HOME/rdbms/admin/ashrpti.sql

ADDM (Automatic Database Diagnostic Monitor) report: shows most significant performance issues between AWR snapshots.

ADDM reports include :
Top SQL Activities
CPU bottlenecks
Undersized memory allocations
Excessive parsing
I/O usage
Concurrency issues
Object contention

Generating ADDM report:
SQL> @$ORACLE_HOME/rdbms/admin/addmrpt.sql

Generating SQL trace

TURN on SQL tracing:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

TURN off SQL tracing:

ALTER SESSION SET EVENTS '10046 trace name context off';


Location of trace dump file:

Trace output is written to the database's UDUMP directory.

UDUMP is the database's USER DUMP DIRECTORY, you can find the same by using:
SQL> SHOW PARAMETERS user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /xx/xx/xx/xx/udump

To change this value:
SQL> ALTER SYSTEM SET user_dump_dest = '/xx/xx/xx/xx/udump' SCOPE=both;

System altered.


The default name for a trace files is INSTANCE_PID_ora_TRACEID.trc where:
INSTANCE is the name of the Oracle instance,
PID is the operating system process ID (select SPID from V$PROCESS); and
TRACEID is a character string of your choosing.

 select vp.spid
 from v$session vs,
      v$process vp
 where sid IN (select distinct sid from v$mystat )
   and vs.paddr = vp.addr;

Adding debug statements in a PL/SQL block/procedure/function

The most common strategy used for adding debug statement is to create a temp table for debug purpose and log the data into it via autonomous transaction.

Creating autonomous transaction is very important as your transaction with business logic may get rollback due to any unexpected error.

The below script can be run for adding the temp table and autonomous procedure for adding the logs.

enable_debug.sql :

CREATE TABLE DBUGTAB(ERROR_CODE NUMBER, MODULE_NAME VARCHAR2(4000), ERROR_MSG VARCHAR2(4000));  CREATE or REPLACE PACKAGE dbug as PROCEDURE debug(p_module_name VARCHAR2,p_error_msg in VARCHAR2);  END dbug;  /  CREATE OR REPLACE PACKAGE BODY dbug AS  PROCEDURE debug(p_module_name VARCHAR2, p_error_msg in VARCHAR2) is  PRAGMA AUTONOMOUS_TRANSACTION;     l_errornum    NUMBER;  BEGIN     SELECT nvl(max(ERROR_CODE),0) into l_errornum from dbugtab;     l_errornum := l_errornum+1;     INSERT into dbugtab(error_code, error_msg ,module_name)     values         (l_errornum, p_error_msg, p_module_name);     COMMIT;  END;  END dbug;  /

For adding the debug statements, the below sample can be followed:
dbug.debug('anbcdcd','adadad');

To query the debug statements in the order of their insertion:
SELECT * FROM dbugtab ORDER BY error_code;