COMMIT_WAIT, COMMIT_LOGGING & COMMIT_WRITE

For good explanation see : http://antognini.ch/2012/04/commit_wait-and-commit_logging/
Recommanded values : COMMIT_WAIT=WAIT; COMMIT_LOGGING=BATCH

How to see hidden parameters in Oracle

To see hidden parameters in Oracle :

set echo off lines 149 pages 9999 feed off
clear col
clear break
clear compute
ttitle off
btitle off

COLUMN Param FORMAT a42 wrap head 'Underscore Parameter'
COLUMN Descr FORMAT a75 wrap head 'Description'
COLUMN SessionVal FORMAT a7 head 'Value|Session'
COLUMN InstanceVal FORMAT a7 head 'Value|Instnc'
ttitle skip 1 center 'All Underscore Parameters' skip 2

SELECT
a.ksppinm Param ,
b.ksppstvl SessionVal ,
c.ksppstvl InstanceVal,
a.ksppdesc Descr
FROM
x$ksppi a ,
x$ksppcv b ,
x$ksppsv c
WHERE
a.indx = b.indx AND
a.indx = c.indx AND
a.ksppinm LIKE '/_%' escape '/'
ORDER BY 1;

How to calibarte disk io using the RESOURCE_MANAGER package

@see :
resource manager io calibration in 11g
I/O Calibration Inside the Database

How to disable AUTO TASKS under Oracle 11g

Do the following commands :

set lines 180 pages 1000
 
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/
 
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/
 
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL);
END;
/

To see the auto task status, use the following command :

 
col client_name for a40
col attributes for a60

select client_name, status,attributes,service_name from dba_autotask_client;

CLIENT_NAME                              STATUS   ATTRIBUTES                                                   SERVICE_NAME
---------------------------------------- -------- ------------------------------------------------------------ ----------------------------------------------------------------
auto optimizer stats collection          DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor                       DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor                       DISABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL

How to find number of lines stored in a table's blocks

To find the number of lines per block for a specific table :

SQL> SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),COUNT(*) NUMBER_ROWS_PER_BLOCK
        FROM <my_table_name>
        GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)
        Order by COUNT(*) desc;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) NUMBER_ROWS_PER_BLOCK
------------------------------------ ---------------------
                                 355                   127
                                2142                   127
                                 363                   127
                                5550                   127
                                4934                   127
                                5614                   127
                                5552                   127
                                 785                   127
                                 789                   127

And to find the file id :

SQL> select
     dbms_rowid.rowid_relative_fno(rowid) FILE#,
     dbms_rowid.rowid_block_number(rowid) BLOCK#,
     count(1)                             NUMBER_ROWS_PER_BLOCK
  from <my_table_name>
  group by
     dbms_rowid.rowid_relative_fno(rowid),
     dbms_rowid.rowid_block_number(rowid)
  order by 1,2
SQL> /

     FILE#     BLOCK#  NUMBER_ROWS_PER_BLOCK
---------- ---------- ----------------------
         5       2024        100
         5       2025        115
         5       2026        115

To dump the content of a block to the trace file, use the following command :

SQL> alter system dump datafile 5 block min 2024 block max 2024;

Now, to find the trace file you need to find the process id of your current session :

SQL> select p.spid from v$session s, v$process p
        where s.sid = (select distinct sid from v$mystat)
              and p.addr = s.paddr;

Dead lock detected

Just a note to don't forget how to solve a dead lock deleted with TM lock.

Having this trace :

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-0000cfcc-00000000       248     668    SX   SSX      258    1512    SX   SSX
TM-0000cfcc-00000000       258    1512    SX   SSX      248     668    SX   SSX

session 668: DID 0001-00F8-00028FD4     session 1512: DID 0001-0102-00011BA0
session 1512: DID 0001-0102-00011BA0    session 668: DID 0001-00F8-00028FD4

The lock is a TM lock where two sessions having a SX (subexclusive) lock try to acquire a SSX (share-subexclusive) higher lock on a table.
A TM lock ensure that the structure of a table could not be altered whilst the session holding the lock is currently updating one or more rows in the table.

Just to remember : From Oracle documentation : @http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502

The types of row and table locks are summarized here.
For a more complete discussion of the types of row and table locks, see Oracle Database Concepts.

Row Locks (TX) :
----------------
A row lock, also called a TX lock, is a lock on a single row of a table.
A transaction acquires a row lock for each row modified by one of the following statements:
 INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE.

The row lock exists until the transaction commits or rolls back.

When a transaction obtains a row lock for a row, the transaction also acquires a table lock
for the table in which the row resides. The table lock prevents conflicting DDL operations that
would override data changes in a current transaction.


Table Locks (TM):
-----------------
A transaction automatically acquires a table lock (TM lock) when a table is modified with the
following statements:
 INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE.

These DML operations require table locks to reserve DML access to the table on behalf of a transaction
and to prevent DDL operations that would conflict with the transaction.
You can explicitly obtain a table lock using the LOCK TABLE statement, as described in "Manual Data Locking".


A table lock can be held in any of the following modes:

    * A row share lock (RS), also called a subshare table lock (SS), indicates that the transaction
    holding the lock on the table has locked rows in the table and intends to update them.
    An SS lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.

    * A row exclusive lock (RX), also called a subexclusive table lock (SX), indicates that
      the transaction holding the lock has updated table rows or issued SELECT ... FOR UPDATE.
      An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently
      in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and SS locks
      for the same table.

    * A share table lock (S) held by one transaction allows other transactions to query the table
     (without using SELECT ... FOR UPDATE) but allows updates only if a single transaction holds the share table lock.
      Multiple transactions may hold a share table lock concurrently, so holding this lock is not sufficient to ensure
      that a transaction can modify the table.

    * A share row exclusive table lock (SRX), also called a share-subexclusive table lock (SSX),
      is more restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on
      a given table.
      An SSX lock held by a transaction allows other transactions to query the table (except for SELECT ... FOR UPDATE)
      but not to update the table.

    * An exclusive table lock (X) is the most restrictive mode of table lock, allowing the transaction that holds
      the lock exclusive write access to the table. Only one transaction can obtain an X lock for a table.

From Tom Kyte :

=> Row Locks (TX)
Row-level locks are primarily used to prevent two transactions from modifying the same row.
When a transaction needs to modify a row, a row lock is acquired.

=> Table Locks (TM)
Table-level locks are primarily used to do concurrency control with concurrent DDL operations,
such as preventing a table from being dropped in the middle of a DML operation

From another Oracle doc but more confusing:

The type of lock definition.
The values are:
    ROWS_S (SS)  : row share lock
    ROW-X (SX)   : row exclusive lock
    SHARE (S)    : share lock
    S/ROW-X (SSX): exclusive lock
    NONE         : lock requested but not yet obtained

From Jonathan Lewis @http://jonathanlewis.wordpress.com/2010/06/21/locks/ :

 Lock Modes :
 Filed under: Infrastructure,Locks — Jonathan Lewis @ 7:05 pm GMT Jun 21,2010

I usually think about locks (and the meaning of the lmode and request columns in v$lock) in terms of numbers,
and never seem to remember which number goes with which name – apart from mode 6 = exclusive.
So I’ve finally put up a little list somewhere I’ll always be able to find it if I need it.
Value   Name(s)                    Table method (TM lock)
    0   No lock                    n/a
 
    1   Null lock (NL)             Used during some parallel DML operations (e.g. update) by
                                   the pX slaves while the QC is holding an exclusive lock.
 
    2   Sub-share (SS)             Until 9.2.0.5/6 "select for update"
        Row-share (RS)             Since 9.2.0.1/2 used at opposite end of RI during DML
                                   Lock table in row share mode
                                   Lock table in share update mode
 
    3   Sub-exclusive(SX)          Update (also "select for update" from 9.2.0.5/6)
        Row-exclusive(RX)          Lock table in row exclusive mode
                                   Since 11.1 used at opposite end of RI during DML
 
    4   Share (S)                  Lock table in share mode
                                   Can appear during parallel DML with id2 = 1, in the PX slave sessions
                                   Common symptom of "foreign key locking" (missing index) problem
                                   Note that bitmap indexes on the child DON'T address the locking problem
 
    5   share sub exclusive (SSX)  Lock table in share row exclusive mode
        share row exclusive (SRX)  Less common symptom of "foreign key locking" but likely to be more
                                   frequent if the FK constraint is defined with "on delete cascade."
 
    6   Exclusive (X)              Lock table in exclusive mode
                                   create index    -- duration and timing depend on options used
                                   insert /*+ append */

(See Lock Horror for notes on the mode 3 lock during RI in 11g).

It’s a minor irritant, of course, that some of the lock modes have two names and two abbreviations – and
some documents use one name and the other abbrevation (e.g. talking about row-exclusive locks, then using the SX abbreviation). The various lock trace files seem to favour SS, SX, and SSX over RS, RX and SRX.n

The change to mode 3 for “select for update” is described in Metalink (MOS) in note 3646162.8 – which
references a bug of the same number describing a false deadlock issue in RAC; and it may still be possible to
reverse the change in Oracle 11 (according to bug note 4969880) by setting the “_fix_control” hidden
parameter to ’4969880:ON’.

The requirement for the ‘mode 2 at opposite end of RI’ may relate to problems of checking referential
integrity while executing parallel DML (unpublished bug 2435787 may apply, but I have a note to myself
about parallel DML resulting in deadlocks although I can’t find anything about this on Metalink).

I also have a note about a session in 9.2 getting two TM locks on the same object during parallel dml,
one in mode 1 with id1 = 0 and one in mode 4 with id1 = 1. I haven’t looked at this for a long time, though.

Seen from different discussion on internet :

Lock type and lock lode :
* TYPE=TX, LMODE=6, REQUEST=4 = session 2 is attempting to insert a row with the same primary key value or
                                unique index value as a row just inserted by session 1
* TYPE=TX, LMODE=6, REQUEST=4 = ITL Problem or bitmap index fragment problem
* TYPE=TX, LMODE=6, REQUEST=6 = session 2 is attempting to update a row that is locked by session 1′s transaction (likely
                                session 1 already updated the row)
* TYPE=TM, LMODE=3, REQUEST=5 = possible missing index on a foreign key column

From Charles Hooper — June 22, 2010

and

Here is the way I remember them (the ‘Sub’ is explained in dbmslock.sql comments):

S and X are Shared and eXclusive locks concerning the whole table (if talking about TM locks)
SS/RS and SX/RX are Shared and eXclusive locks concerning only a Subset of the table (just some Rows)
SSX/SRX is a Shared lock for whole table + eXclusive lock concerning only a Subset of the table (just some Rows)

From Franck Pachot — June 22, 2010

What to keep in mind :
A share row exclusive table lock (TM-SX or subexclusive) generally indicates that the transaction holding the lock
has made one or more updates to rows in the table.
A share row subexclusive table lock (TM-SSX) is more restrictive than a subexclusive (SX) table lock.
Only one transaction at a time can acquire a SSX table lock on a given table.
A share row exclusive table lock held by a transaction allows other transactions to query or lock specific rows
using SELECT with the FOR UPDATE clause, but not to update the table.
A share row exclusive table lock held by a transaction prevents other transactions from obtaining row exclusive
table locks and modifying the same table.
How to know which object is locked :
Look at TM-0000cfcc-00000000.
TM indicate a table lock and 0000cfcc the ressource locked.
To find the ressource :

SQL> select to_number( '0000cfcc', 'xxxxxxxxxx') from dual;

TO_NUMBER('0000CFCC','XXXXXXXXXX')
----------------------------------
                             53196

SQL> select * from dba_objects where object_id=53196;

OWNER OBJECT_NAME           SUBOBJECT_NAME   OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED   LAST_DDL_ TIMESTAMP STATUS  T G S
----- -------------------- ----------------- --------- -------------- ----------- --------- --------- --------- ------- - - -
TEST   MY_TABLE_NAME                         53196     54978          TABLE       17-OCT-13 04-DEC-13 2013-10-17 VALID  N N N