Mar 17, 2016

More about Indexes

In B tree index single column indexes do not include entries for null values ; but bitmap and function based index can store all null entries .
Multi column indexes will store null values but only if at least one column is not null


1) If all columns contain null values , the entry will not be stored in the index

2) Adding a frequently null valued column to an index can impact performance by allowing the index to filter the nulls

3) If range scanning a column ie: < or >  , put those columns at the end of the composite index

Oracle Trace file





Mar 11, 2016

New Features in Oracle 11g

Oracle Lock

Oracle locking policy consists of the following


•Oracle locks data at the row level on modification only. There is no lock escalation to a block or table level, ever.
•Oracle never locks data just to read it. There are no locks placed on rows of data by simple reads.
•A writer of data does not block a reader of data.
•A writer of data is blocked only when another writer of data has already locked the row it was going after.


There are two mechanisms for locking data in a database


Pesimistic Locking

In pessimistic locking a record or page is locked immediately when the lock is requested,

Optimistic Locking

In an optimistic lock the record or page is only locked when the changes made to that record are updated.


Blocked Inserts

The only time an INSERT will block is when you have a table with a primary key or unique constraint placed on it and two sessions simultaneously attempt to insert a row with the same value, it is most avoided via the use of Oracle sequences in the generation of primary keys as they are highly concurrent method of generating unique keys in a multi-user environment.


Blocked Updates and Deletes

To avoid update and delete blocking use either one of the two locking methods Pessimistic or Optimistic.


Deadlocks

Deadlocks occur when two people hold a resource that the other wants. Oracle records all deadlocks in a trace file. The number one cause of deadlocks is un-indexed foreign keys
•if you update the parent table's primary key the child table will be locked in the absence of an index
•if I delete a parent table, the entire child table will be locked, again in the absence of an index.

Lock Escalation

In other RDBMS when a users locks 100 rows (this may vary) the lock is escalated to a table lock, however Oracle will never escalates a lock


Type of Locks

There a number of different types of locks as listed below:
•DML Locks - DML (data manipulation language), in general SELECT, INSERT, UPDATE and DELETE. DML locks will be locks on a specific row of data, or a lock at the table level, which locks every row in the table.
•DDL locks - DDL (data definition language), in general CREATE, ALTER and so on. DDL locks protect the definition of the structure of objects.
•Internal locks and latches - These are locks that Oracle uses to protect its internal data structure.
•Distributed Locks - These are used by OPS to ensure that different nodes are consistent with each other.
•Deadlocks - Occurs when two sessions block each other while each waits for a resource that the other session is holding.
•PCM - PCM (Parallel Cache Management) These are locks that protect one or more cached data blocks in the buffer cache across multiple instances


DML Locks

There are two main types of DML locks TX (Transaction) and TM (DML Enqueue).


A TX lock is acquired when a transaction initiates its first change and is held until the transaction performs a COMMIT or ROLLBACK. It is used as a queuing mechanism so that other sessions can wait for the transaction to complete.


 A TM lock is used to ensure that the structure of the table is not altered while you are modifying its contents.


The complete set of DML locks are
Row Share  ,Row Exclusive ,Share,Share Row Exclusive  ,Exclusive


The type of locked used when using DML are
RS (table) and RX (row)   select  ... for update;  lock table ... in row share mode 
RX (table) and RX (row)   any insert, update or delete



DDL Locks

DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions.


There are three types of DDL locks


Exclusive DDL Locks - these prevent other sessions from gaining a DDL lock or TM lock themselves. You can query a table but not modify it. Exclusive locks will normally lock the object until the statement has finished.


Share DDL Locks - This protect the structure of the referenced object against modification by other sessions, but allows modification to the data. Shared DDL locks allow you to modify the contents of a table but not their structure.


•Breakable Parse Locks - This allows an object, such as a query plan cached in the shared pool to register its reliance on some objects. If you perform a DDL against that object, Oracle will review the list of objects that have registered their dependence, and invalidate them. Hence these locks are breakable, they do not prevent the DDL from occurring. Breakable parse locks are used when a session parses a statement, a parse lock is taken against every object referenced by that statement. These locks are taken in order to allow the parsed, cached statement to be invalidated (flushed) in the shared pool if a reference object is dropped or alter in some way. Use the below SQL to identify any parse locks on views, procedures,grants, etc

Latches and Internal Locks (Enqueues)

Latches are locks that are held for short period of time, for example the time it takes to modify an in-memory data structure like buffer cache or the library cache in the shared pool. They are lightweight  . They do not protect database objects such as tables or data files.


Enqueues are another more sophisticated serialized device used when update rows in a database table. The requestor will queue up and wait for the resource to become available, hence these are not as fast as a latch. It is possible to use manual locking using the FOR UPDATE statement or LOCK TABLE statement, or you can create your own locks by using the DBMS_LOCK package.

Mar 4, 2016

Bulk Processing in Oracle

 Bulk Processing with BULK COLLECT and FORALL


  • BULK COLLECT: SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval


  • FORALL: INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly


  • BULK COLLECT

    declare
      type t_plsql_type is table of employee%rowtype index by pls_integer;
      vt_plsql_type t_plsql_type;                      
    begin

       select * bulk collect into vt_plsql_type from  employee ;

       for v_typ_idx in 1..vt_plsql_type.count loop
           dbms_output.put_line(vt_plsql_type(v_typ_idx).emp_id);
       end loop;       

    end ;  

    FORALL