Oracle Architectural Components

1. Oracle Architectural Components

2. Objectives • Listing the structures involved in connecting a user to an Oracle server • Listing the stages in processing a query • Listing the stages in processing a DML statement • Listing the stages in processing COMMITS

3. Oracle server The Oracle Server Application/networkserver Server Users

4. Oracle server Connecting to a Database Serverprocess Client Server Userprocess User

5. User Process • Runs on the client machine • Is spawned when a tool or an application is invoked • Runs the tool or application (SQL*Plus, Server Manager, Oracle Enterprise Manager, Developer/2000) • Includes the User Program Interface (UPI) • Generates calls to the Oracle server

6. Server Process • Runs on the server machine (host) • Services a single user process in the dedicated server configuration • Uses an exclusive PGA • Includes the Oracle Program Interface (OPI) • Processes calls generated by the client • Returns results to the client

7. Instance SGA Oracle Instance An Oracle instance: • Is a means to access an Oracle database • Always opens one and only one database Background processes

8. Control files Data files Redo logfiles Oracle Database Parameterfile Archivedlog files Passwordfile Database

9. Parameterfile Passwordfile Archivedlog files Other Key Physical Structures Database

10. Processing a Query • Parse: • - Search for identical statement • Check syntax, object names, privileges • Lock objects used during parse • Create and store execution plan • Execute: identify rows selected • Fetch: return rows to user process

11. Statement Handle Handle Results OK OK • Parse • Execute • Fetch Processing a Query (cont.) SELECT * FROM emp ORDER BY ename; Userprocess Serverprocess

12. Shared pool Librarycache Datadictionarycache The Shared Pool • Size defined by SHARED_POOL_SIZE • Library cache contains statement text, parsed code, and an execution plan • Data dictionary cache contains table and column definitions and privileges

13. Database Buffer Cache • Number of buffers defined by DB_BLOCK_BUFFERS • Size of a buffer based on DB_BLOCK_SIZE • Stores the most recently used blocks

14. Program Global Area (PGA) PGA Serverprocess • Not shared and not writable • Contains • Sort area • Session information • Cursor state • Stack space

15. Processing a DML Statement • If data blocks not in the buffer, server process reads them into the buffer • Server process places locks on rows to be modified • The redo log buffer is modified with the changed values • The data blocks are changed • Before image is recorded in the rollback block

16. 3 Instance SGA 4 Shared pool Librarycache 5 Database buffercache Redo logbuffer Datadictionarycache 2 Control files Data files Redo logfiles 1 Database Processing a DML Statement (cont.) UPDATE emp SET sal=sal*1.1 WHERE empno=7369 Serverprocess

17. Rollback Segment Old image New image Table Rollback segment DML statement

18. Redo Log Buffer • Size defined by LOG_BUFFER • Records changes made through the instance • Used sequentially • Circular buffer

19. Other Instance Processes • Other required processes • - Database Write (DBW0) • - Log Writer (LGWR) • - Process Monitor (PMON) • - System Monitor (SMON) • - Checkpoint (CKPT) • Archive process (ARC0) is optional; is used in production database

20. Instance SGA Shared pool Database buffercache DBWR Data files Control files Redo logfiles Database Writer (DBWR) • DBW0 writes when: • There are many dirty buffers • There are few free buffers • Timeout occurs • Checkpoint occurs

21. Instance SGA Shared pool Redo logbuffer LGWR Data files Control files Redo logfiles Log Writer (LGWR) • LGWR writes when: • There is a commit • The redo log buffer is one-third full • There is more than 1MB of redo • Before DBW0 writes

22. COMMIT Processing • Server process places a commit record and system change number (SCN) in redo log buffer. • LGWR writes redo log to redo log files. • User is informed COMMIT is complete • Server process records trans. Is complete and locks can be released.

23. 1 4 3 2 Control files Data files Redo logfiles Database COMMIT Processing (cont.) Instance SGA Shared pool Serverprocess Database buffercache Redo logbuffer LGWR Userprocess

24. SMON: System Monitor • Automatically recovers the instance • Rolls forward changes in redo logs • Opens the database for user access • Rolls back uncommitted transactions • Coalesces free spaces • Deallocates temporary segments

25. PMON: Process Monitor • Cleans up after failed processes by: • Rolling back the transaction • Releasing locks • Releasing other resources

26. Archiving • Database archive mode • - NOARCHIVELOG for databases that do not require recovery after disk failure • - ARCHIVELOG mode for production • ARC0 process • Automatically archives online redo log files • Preserves the record of all changes made to the database

27. Instance SGA Shared pool DBWR LGWR Control files Parameterfile Archivedlog files Redo logfiles Data files Passwordfile Summary Serverprocess PGA Userprocess Database

28. Summary (cont.) • You should have learned: • Explain the SGA, Instance, memory structures • Explain the database files • Explain the primary background processes (DBW0, LGWR, CKPT, PMON, SMON, ARC0) • Explain SQL processing steps • Explain the COMMIT process