How does a DML statement works in oracle

How does a DML statement works in oracle

Listener verifies the connection and a server process gets generated for the client request.And thus request gets processed through PGA and SGA as described below.

PGA-- holds information of  serer process/ plus SQL work area /and session memory /And  Cursor bound

     
SGA --

 1.)Parsing
    The shared pool is a data structure.
people (processes, things, like dbwr, lgwr, etc) allocate memory from it to create data structures they need and use.

they then allocate memory from it, use a latch

   cache

 Data dictionary cache - Stores recently data definitions.

 Library Cache  -  Stores recently executed query.
   Latch
 Shared pool latch is used to protect memory allocations
 Library cache is used to protect modifications to these structures after allocation.

-- Oracle accesses the data dictionary (sytax check ,semantic check , privilege check)  during SQL statement parsing.

-- Shared Pool Latch is acquired to check for the parsed version of this SQL.

-- If the Parsed Version is available then Library Cache Latch is acquired to protect the parsed version of that SQL for any modification. (SOFT Parse). - library cache lock wait event

--A pin is applied to the library cache object for a PL/SQL program unit or SQL statement while it is being compiled, parsed, or executed(no fetch) - library cache pin/ mutex wait event

 It would be the library cache latch that gets hit on the parse.

2.)Optimisation - multiple plan using CBO

  -- If the parsed version is not available, even then the Library Cache latch is acquired to parse the query and generate the execution plan (HARD Parse) .

3.)Raw source generation - optimised good plan generate of hash value

4.) Fetch - fetch the output in buffer cache.

-- Acquire the lock on table. Bring the data from the data files into the memory i.e buffer cache

   

-- Keep the old value in undo (buffer cache) and make a record in log buffer

-- On commit, A system change numner is generated and entrie content of log buffer is off loaded to log files and lock is realed.

-- Undo is now eligible for being overwritten

11g reused cache- new feature

Every SQL statement needs a set of blocks - those blocks are either

a) in the cache

b) not in the cache

if (a) is true, no physical IO. If (b) is true, then we do a physical IO.

insert sort of needs to figure out where to put stuff. We do not create a new block for every row. When you insert, we need to read things like freelists (manual segment space management) or bitmap blocks (automatic segment space management). When we read those blocks - they will tell us where to put the data.

And when we figure out that the block in file 5, block #42 is the block we should insert into - we need to get that into the cache. It probably already has some data on it, we need to add to it.

we might have to physical IO the freelist data, the bitmap data and the target block.

then again, we might not.

That is for HEAP data of course, now YOU need to think about what would happen with an index organized table, a b*tree cluster, a hash cluster, a partitioned table, etc.........