"Oracle" Physical architecture

I. ORACLE Physical Architecture

1. Principle Structure diagram

Each part explains:

PGA: Private memory area for use by the current initiating user only.

Three effects

1. The session information after the user logs in will be saved in the PGA.

2. run the sort . Assuming there is not enough memory, Oracle will finish in the temporary tablespace
3. Save User Rights information

SGA: Includes shared pools, data buffers, log buffers , and some related processes.

Database: Where the data is finally stored, one area is the log storage area. Data and log logs are written to the database and redo log groups by DBWR and LGWR, and when a redo log group is full, switch to the next one, and once the loop is over, the log that was originally sorted into the first group is written to the archive log by the arch process.

2 structure in depth2.1 parsing a structure from a query SQL statement

If there is a sql:select * from user where user_id= ' 20 '

1. After SQL is parsed. (Zone 1) PGA save their login and permission information . It is important to note that if you save this information, you do not need to check again the next time you call SQL. Just play the PGA.

2. The SQL then matches a unique hash value and reaches the shared pool in Zone 2 . The shared pool infers whether this hash value existed before, assuming that it is OK to run directly, assuming that it does not exist, it is necessary to check the syntax and semantics of SQL to verify and parse it.

   Parse what? is to put the select * from user where user_id= ' 20 ' this statement, in the case of USER_ID existence index, Oracle to choose the optimal cost ( whether it is an index scan or a full table scan ), After making a selection, Oracle will put the change plan and the previous hash value together.

3. With the condition, SQL next looks into the data buffer to see if there is a record of user_id 20. Assume that the buffer has a return result immediately. If none is assumed, it is to be found in the 3 zone (database) . Returns results regardless of whether they are found.

2.2 Parsing the structure from an updated SQL statement

If there is a sql:update user set user_name= ' Xiao Hong '

1. As in step 1.2.
2. Write the operation record of the data to the redo log
3. write to the database . persisted data. Change the User_name field of the Database user table to Xiao Hong.

3 Commit and rollback

1. When a user commits a commit, the data is not immediately brushed into the database.

   When will data be brushed into the database?

   This is related to the CKPT process. When the amount of data in the buffer reaches a certain level, the CKPT process is triggered and the data is then batched into the database at this time.

   What if I lose power before I swipe into the database? The redu mechanism once again runs the log recovery data.

2. ROLLBACK is running undo

"Oracle" Physical architecture