1 2 Previous Next 18 Replies Latest reply: Mar 11, 2013 8:38 AM by Mark D Powell RSS

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

    Mohanad Awad
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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