"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. perform a sort , and if memory is not enough, Oracle will do it in a temporary tablespace
3. Save User Rights information

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

Database: Where the data is eventually stored, one of the areas 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

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

1. After SQL is parsed, the(Zone 1) PGA holds its login and permissions information . It is important to note that if you save this information, you do not need to re-verify the next time you call SQL, just play the PGA Fetch.
2. The SQL then matches a unique hash value and reaches the shared pool in Zone 2 , and the shared pool determines if the hash value existed before, and if it does, it is OK to do so, and if not, check SQL syntax, semantics For verification and resolution. 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 execution 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. If there is a buffer, the result is returned immediately, and if none is found in the 3 zone (database) , the result is returned regardless of whether it is located.

2.2 Parsing the structure from an updated SQL statement

Suppose 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, persist the data, and change the User_name field of the Database user table to Xiao Hong.

3 Commit and rollback

1. When a user submits 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, where the CKPT process is triggered when the amount of data in the buffer reaches a certain level, and the data is only batched into the database at this time. What if I lose power before I swipe into the database? The redu mechanism re-executes the log recovery data.

2. ROLLBACK is performing the undo

For more information, see Harvest, more than Oracle, this article is for summary use only, please correct if there are errors.