Oracle 11g R2 Architecture

Install Oracle software----Create a database----create tables in the database----table stores information---these table files need to be stored on the physical hard disk.

If a user (the user can be a remote user, or a local or an application) is connected to the Oracle database, the user is not directly connected to the data file stored on the hard disk, so in Oracle to run an instance, the instance is not saved on the hard disk, Instance is a part of memory, and the library and table files are stored on the hard disk, the user can not directly access the data inside the library, only access to the instance, the instance is a special block of memory, only the instance can communicate directly with the database, in our experimental environment, the instance and database are on the same machine, we call it a single instance environment For the user all operations are done in the instance, Oracle divides a block of memory for the instance, which we call the SGA, in fact, when the Oracle instance starts, it allocates an SGA system global zone and starts some Oracle daemon processes (daemons).

The SGA Core component describes:

Memory =SGA+PGA in Oracle

An instance can have only one SGA, and all server processes and background processes share an SGA

Pga:

Let's take a look at how a SELECT statement is executed.

If the HR user has written a select * from Scott.emp;

The first step: compile, compile contains the syntax of the check and semantic check.

Step Two: If the compilation does not have a problem, it enters the shared pool, which is composed of the library cache and the data dictionary cache. The SQL statement enters Librarycache the first time, that is, the library cache holds the correct SQL statement that was compiled, just the SQL statement. SQL statements that are not cached in the library cache require hard parsing (that is, the i/0 resource that consumes the device reads from the hard disk). Assuming that the SQL statement is in the library cache, no hard parsing is required and the data dictionary cache is then checked. Permissions and object data and attributes are cached in the data dictionary cache, all of which are dictionary The cache checks whether the HR user has access to the Scott.emp table, and if there is permission to continue execution, returns a result directly to the HR user if there is no permission. If a statement has never been executed, the statement is first cached in the library cache in the share pool, and the next time it is executed, the SQL statement is taken directly from the share pool. Storing SQL statements also requires space, share pool space is also limited, in order to prevent the share pool space is not enough, the first-in-one rule that executes after the SQL statement will overwrite the first executed SQL statement to free up space, of course, the larger the share pool the better.

We use the Enterprise Manager to view information about the share pool


In SQL Plus you can view it in the following way, "0" in the figure indicates automated management

You can view the shared pool size from the V$sgainfo

Sql> Select Name,bytes/1024/1024| | ' MB ' from V$sgainfo;

Shared Pool Size

256MB

Large Pool Size

16MB

Java Pool Size

16MB

Step three: The data buffer cache now has an HR user access to the Scott.emp table, and the role of the DataBuffer cache is to transfer the block into memory from disk or storage and buffer cache.

Example:

The first time a user accesses scott.emp content

Sql> Set Timing on

Sql> select * from scott.emp; Execute SQL statement for the first time

14 rows have been selected.

Time used: 00:00:00.05

Sql>/Time to execute the previous command again is 00:00:00.00

14 rows have been selected.

Time used: 00:00:00.00

Summary: Buffer cache Caches the data itself, and the library cache caches the SQL statement itself.

The size of the buffer cache can be viewed through show parameter db_cache_size

Fourth step: Redo Log buffer< Redo the logging buffer > when the user performs insert,update,delete,create,alter and other operations, the data changes, these changed data are written to the data buffer (buffer Cache), the redo log buffer is written before the changed data is placed in the redo log cache to ensure that Oracle knows which transactions need to be committed and which needs to be recalled when the data is restored.

If you have any questions about this article, please add the following discussion

Oracle 11g R2 Architecture