This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Mar 11, 2013 6:38 AM by Mark D Powell RSS

How Oracle DML/Select exactly Happen?? What process / Buffer use ?

Mohanad Awad Newbie
Currently Being Moderated
Dears,

Really i read many post on internet but not not clear and dose not have full detail so i have some Q :

1- For DML : after commit dose the data write directly to database files ? what is the criteria and which process ?
2- if the Server process fetch the necessary and needed block to be updated , so When DBWR fetch data from datafile?
3- Dose parsing information saved on Library cache ? dose the Dictionary cache use for DML and SELECT ?
4- If i update huge data on same time for example : 1 Melon record , Where it save on log buffer or database buffer ? and if not enough dose it save in temporary table ? or what the procedure for that ?

5 and this very confuse : !!! dose commit happen after checkpoint ?

so explain to be below please ... i make many insert then commit and check below query for long time

select checkpoint_change# from v$database --14026091 -- from control file
select dbms_flashback.get_system_change_number from dual ;--14027831 ( just this change but not change in control file and datafile
select name , checkpoint_change# from v$datafile ;--14026091
select name , checkpoint_change# from v$datafile_header ; -- --14026091

so as you see above check point dose not change and reflected on control , data file .. is there different type of check point in oracle ! and when each one happen and why ?

6 - When DBWR work exactly ? what the criteria ?
7 - When LGWR work exactly ? what the criteria ?
8 - when you commit data then select before checkpoint or anything , from you exactly read the data ? from database buffer ? or log buffer ? or online redo log file ?

Best Regards,
Mohanad Awad
  • 2. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    Mohanad Awad Newbie
    Currently Being Moderated
    Dears,

    It dose not answer my above Q ! and the link dose not have mention process / Buffer which use, my Q is so clear .

    i think my issue in DML not select also .. i know exactly how select work :

    1. Server process checks Library Cache to see if same statement has been issued previously. If so, acquire the existing execution plan
    2. If no, then server process stores the submitted select in the library cache. Server process now parses it and develops an execution plan, storing the final execution plan with the SQL statement in the Library Cache
    3. Server process visits Buffer Cache and checks to see if required data is already in cache. If so, read the existing buffers to obtain the data.
    4. If not, the server process fetches the necessary blocks from disk and loads them into the buffer cache. Once in the cache, the server process is able to obtain the necessary row data.
    5. Server process returns data to the user that requested it.
    my Q about DML ...
    Thanks

    Edited by: Mohanad Awad on Mar 9, 2013 5:44 AM
  • 3. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    TSharma-Oracle Guru
    Currently Being Moderated
    http://dbaspot.com/oracle-server/54577-update-statement-flow.html
  • 4. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    Mark D Powell Guru
    Currently Being Moderated
    Mophand, the complete answers to your questions would require a volume be written such as the Concpets manual. Using version 11.2 as a guide in specific Chapters 7, 9, and 10 (SQL, Data Concurrency and Consistency, and Transactions) plus ch 15 on Process Architecture which discusses the database background processes.

    Commits cause entries to be written to the online redo log by lgwr. Once lgwr writes the data then dbwr is signaled that it can write the changed data to disk. Uncommitted changed data can be witten to disk however this happends after the undo data for the change has been logged. This happens when the total amount of data being changed is too large to hold in the buffer cache till the commit is issued.

    Log writes has depending on your version and how you want to count them 3 to 5 conditions that make it write: on commit, when the log buffer is 2/3 full, every 3 seconds ... The triggers are listed in the manual for your version of Oracle.

    The data file headers and control file headers are updated on a database checkpoint. When a database checkpoint occurs depends on configuration and RAC vs non-RAC etc ... but basically when the database switches to a new online redo log you get a checkpoint and Oracle has dbwr flush dirty buffers to disk (committed or not), check point writer prepares the file headers, and the datafile headers and control file get updated.

    HTH -- Mark D Powell --
  • 5. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    Mohanad Awad Newbie
    Currently Being Moderated
    can you answer my above Q from your link ?

    Edited by: Mohanad Awad on Mar 9, 2013 6:55 AM
  • 6. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    Mohanad Awad Newbie
    Currently Being Moderated
    The DOC include every process , buffer alone ... but it dose not discus that with DML ..

    Can any one answer above Q without links please ?
  • 7. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    sb92075 Guru
    Currently Being Moderated
    Mohanad Awad wrote:
    The DOC include every process , buffer alone ... but it dose not discus that with DML ..

    Can any one answer above Q without links please ?
    not me.

    why should I write here when explanation exists already below

    http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#BABEAGIA

    BTW - MUCH of what you wrote is NOT correct; too many to enumerate.
    Realize that Oracle does the right thing; even when you do not understand what actually occurs.
  • 8. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    Mohanad Awad Newbie
    Currently Being Moderated
    it seem this link what i need .. thanks ,

    Just one Q please : if i execute below insert :


    insert into scott.dept values (99,'Database',null);

    commit;

    - before write dirty buffer and before checkpoint : i execute below query :

    select * from scott.dept where deptno=99;

    >> dose the data exist on data file on physical hard disk or just exist on database cache buffer ? i know sure oracle does the right thing; even when you do not understand what actually occurs. i ask just to understand !
  • 9. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    sb92075 Guru
    Currently Being Moderated
    Mohanad Awad wrote:
    it seem this link what i need .. thanks ,

    Just one Q please : if i execute below insert :


    insert into scott.dept values (99,'Database',null);

    commit;

    - before write dirty buffer and before checkpoint : i execute below query :

    select * from scott.dept where deptno=99;
    dose the data exist on data file on physical hard disk or just exist on database cache buffer ? i know sure oracle does the right thing; even when you do not understand what actually occurs. i ask just to understand !
    you will get the new record for SELECT that starts after the COMMIT is issued.
  • 10. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    Mohanad Awad Newbie
    Currently Being Moderated
    I know that but on above case (After commit) And (Before write dirty data and before checkpoint) ,

    the select statement will be from database buffer or database file ? because it DBWR dose not run until now ????
  • 11. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    sb92075 Guru
    Currently Being Moderated
    Mohanad Awad wrote:
    I know that but on above case (After commit) And (Before write dirty data and before checkpoint) ,

    the select statement will be from database buffer or database file ? because it DBWR dose not run until now ????
    what difference does it make?
    how will you behave differently depending upon the answer?
  • 12. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    Mohanad Awad Newbie
    Currently Being Moderated
    So no different ? i told you before to know what the step just ! for me as user dose not different ...

    But as you DBA i want to answer that ? this mean if dose not different if shutdown happen on that time we will lose the data .. coz it's still in buffer ... and if logfile corrupted ...
  • 13. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    sb92075 Guru
    Currently Being Moderated
    Mohanad Awad wrote:
    So no different ? i told you before to know what the step just ! for me as user dose not different ...

    But as you DBA i want to answer that ? this mean if dose not different if shutdown happen on that time we will lose the data .. coz it's still in buffer ... and if logfile corrupted ...
    COMMIT GUARANTEES data is recoverable!
    Period & End of Discussion!
  • 14. Re: How Oracle DML/Select exactly Happen?? What process / Buffer use ?
    Mark D Powell Guru
    Currently Being Moderated
    Yes, the documentation I referenced does discuss how Oracle processes DML. There is a section that discusses how Oracle parses DML and sets up the query plan. Then there is the chapter on how Oracle keeps track of the changed data, and there is a chapter on how the background processes work.

    database writers uses lazy writes to write data to disk. If the system crashes after the commit was issued but before dbwr got around to writing the changed it Oracle will still contain the changes after being restarted because crash recovery will rapply the changes to the database data files. The basic mechanisms used are discussed in adequate detail in the Concepts manaul.

    If you are an Oracle DBA there is no better place to start than reading the Concepts manual from cover to cover. A developer on the other hand can probably start with the Application Developers Guide - Fundamentals.

    IMHO -- Mark D Powell --
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points