How insert statement works internally in oracle

May 27, 2018
SQL INSERT INTO statement is used to insert new records into a table. How insert statement in Oracle  executes internally in Oracle architecture is explained below. Also we will discuss about the flow and sequence of steps which oracle follow for the execution of insert statement.

INSERT INTO Syntax
INSERT INTO table_name (column1, column2,…..column_n )

VALUES (value1, value2…..value_n);

How does the insert query execution occur ?

    1. SQL*PLUS checks the syntax on client side.
    1. If syntax is correct the query is stamped as a valid sql statement and encrypted into oci (oracle call interface) packets andsent via lan using tcp to the server.
    1. Once the packets reach the server the server process will rebuild the query and again perform a syntax check on server side.
    1. Then if syntax is correct server process will continue execution of the query.
    1. The server process will go to the library cache. The library cache keeps the recently executed sql statements along with their execution plan.
    1. In the library cache the server process will search from the mru (most recently used) end to the lru (least recently used) end for a match for the sql statement. It does this by using a hash algorithm that returns a hash value. If the hash value of the query we have written matches with that of the query in library cache then server process need not generate an execution plan (soft parsing) but if no match is found then server process has to proceed with the generation of execution plan (hard parsing).
    1. Parsing is the process undertaken by oracle to generate an execution plan.
    1. The first step in parsing involves performing a symantic check. This is nothing but check for the existence of the obj and its structure in the database.
    1. This check is done by server process in the data dictionary cache. Here server process will ask for the definition of the object, if already available within the data dictionary cache, server process will process the check. If not available then server process will retrieve the required information from the system tablespace.
    1. After this in case of hard parsing the server process will approach the optimizer, who will read the sql statement and generate the execution plan of the query. The optimizer generates multiple execution plans during parsing.
    1. After generation of the e-plan’s by the optimizer the server process will pick the best possible and cost effective e-plan and go to the library cache.
    1. In the library cache the server process will keep the e-plan in the library cache along with the original sql text.
    1. At this point in time the parsing ends and the execution of the sql statement will begin.
    1. After generation of e-plan server process will keep the plan in the library cache on the mru end.
    1. Thereafter the plan is picked up and execution of the insert operation will begin.
    1. Server process will bring empty blocks from the specific datafile of the tablespace in which the table will exist , into which rows must be inserted.
    1. The blocks will be brought into database block buffers(or database buffer cache).
    1. The blocks will be containing no data.
    1. Then server process will bring equal no of empty blocks from the rollback/undo tablespace. they will also be brought into the database block buffers.
    1. Server process will copy the address of the original data blocks of the userdata datafiles into the empty rollback/undo blocks.
    1. Then server process will bring a set of userdata blocks into the pga and the data will be added from the insert sql statement into user data blocks.
  1. After the insert operation is complete in the database buffer cache then dbwriter will write the data back to the respective datafiles after a certain time gap.

Must Read: