ORACLE11G Architecture Detailed

1. Oracle memory is composed of SGA+PGA

2. Oracle Database Architecture
Database architecture refers to the composition, working process and principle of database, and the organization and management mechanism of data in database.

How Oracle Works:

1), start the Oracle instance on the database server;
2), the application runs in the client's user process, enabling the Oracle Network Service drive to establish a connection with the server;
3), the server runs the Oracle Network Service drive and establishes a dedicated server process to execute the user process;
4), the client commits the transaction;
5), the server process gets the SQL statement and checks if there are similar SQL statements in the shared pool, and if so, the server process checks the user's access rights, otherwise allocates a new SQL share to parse and execute the SQL statement;
6), the server obtains the required data from the actual data file or the SGA;
7), the server process updates the data in the SGA, process Dbwn writes the updated block of data back to the disk under certain conditions, and the process LGWR records the transaction in the Redo log file;
8), if the transaction succeeds, the server process sends the message to the application.

3. Oracle Server

I, Oracle server consists of Oracle database and Oracle instance.
II, an Oracle database is a collection of data stored in a data file that is treated as a logical unit.
III. Oracle instances are composed of background processes and memory structures that manage the database.

4. Oracle Instance
I, Oracle instance is a collection of memory structures and background processes
II. The process of starting an Oracle instance: allocating memory, starting a background process
III. The memory of the Oracle instance includes the SGA and PGA;
Allocate SGA when launching an instance;
The PGA is allocated when the server process is established;
In general, Sga:pga=8:1 allocates memory.

Background processes for IIII, Oracle instances include Smon, Pmon, DBWR, LGWR, ARC, Ckpt, and so on.

5. SGA (System global Area):
I0, database information is stored in the SGA and is shared by multiple database processes.
I1, SGA includes: Shared pool, data buffer, log buffer, large pool, Java pool, stream pool.
I2, Shared Pool
1), shared pool is an area of memory for parsing, compiling, and executing SQL, PL/s programs.
When executing a SELECT * from EMP statement, the SQL statement parsing, compile-build execution plan, run execution plans, and so on, are done in the shared pool, and if you execute the SELECT * FROM EMP statement again, Looks for the same SQL in the shared pool, saves compilation, generates the Execute plan action step, and runs the execution plan directly if it exists, so developing good coding habits can be very helpful in improving Oracle execution efficiency.
2), the shared pool consists of a library cache and a data dictionary cache.
3), the size of the shared pool directly affects the performance of the database.

i3, data buffers

1), used to store data read from the disk data file, all users share.
2), the server process will read the data stored in the data buffer, when the subsequent requests need this data can be found in memory, no need to read from the disk, improve the read speed.
3), the size of the data buffer has a direct impact on the reading speed of the database.
4), the data in the data file on the hard disk, how to return to the user?
I, removed from the hard disk, directly back to the user, making it very inefficient to obtain data.
II, remove from the hard disk, put in the data buffer, from the data buffer to return data to the user. The next time the same data is read, it is fetched directly from the data buffer and does not need to be fetched from the hard disk.

I4, log buffers
1), all the modification information of the logging database, the log information is first generated in the log buffer.
2) log data is written to the log file by the background process when the logging data reaches a certain number of records.
3), relatively speaking, the log buffer has less impact on the performance of the database.
eg, UPDATE emp SET empno=7935 where empno=7934; first recorded in the journal Buffer, some time (three cases; one, the log buffer record reaches 1M, every 3 seconds, the log buffer has used One-third, Any of these can be used to write these records in the log buffer to an online log.

i5, Large pond
The memory space allocated for large background process operations mainly refers to backup recovery, large IO operations, parallel queries, and so on.

I6, Stream Pool
An advanced replication technology is part of the memory space allocated for stream applications.

i7, Java Pool
The memory space allocated for the Java application.

7. PGA (Process Global area): Program
The I0, the program Global Zone (PGA) contains the data and control information required for a single server process, such as the SQL binding variable passed in each session, and control information that controls the session. If the session is sorted or hashed, the memory in the PGA is also used.
I1, PGA is automatically assigned when a user process connects to a database and creates a session, and the information required to save each user process connected to the Oracle database is saved
I2, shared connection mode, and dedicated connection mode, the PGA is managed differently.
In most cases, private connection mode is used. The resulting user process and server process are described later

8. Oracle Process: User process, server process (shadow process, relative user process), background process
I, the user process is a program that needs to interact with the Oracle server. User processes are created when a user runs an application ready to send a request to the database server. The user process is located on the client side, the server process, and the background process on the server.
II. The server process is used to process requests for user processes that connect to the instance. The server process is generated when the user connects to the Oracle DB instance creation session.
III. Background processes are set by the Oracle database to maintain optimal system performance and reconcile multiple user requests. A sequence of background processes is created when an Oracle instance is started.

10. User processes and Server processes
I, for a private connection, the user initiates an application on the client, such as Sql*plus, which initiates a user process on the client, and when the server-side connection with Oracle succeeds, a server process is generated on the server, which acts as the agent process for the user process. Executes various commands instead of the client and returns the results to the client. 9i, 10g, 11g use a dedicated connection.
II, once the user process is aborted, the server process is immediately aborted.
III. The SGA is shared with all users and the PGA is not shared.

11. Session
I0, a session is a single connection between a user and an Oracle server
I1, creating a session when a user establishes a connection to the server
I2, closing a session when the user is disconnected from the server
i3, query the session information from the V$session, SELECT * from V$session;

12. Background Process
I0, Pmon process monitoring process, monitoring the user process is normal.
1), clean up the failed process.
2), releases all currently pending locks.
3), release the resources used by the failed process.
I1, Smon system monitoring process, real-time monitoring of the entire Oracle state.
1), after the instance fails, automatically resumes the instance when the database is reopened.
2), tidy up the free space of the data files, combine the adjacent areas.
3), release the temporary segment that is no longer in use.
I2, DBWR data write process
1), manage the data buffers, and keep the most recently used blocks in memory.
2), write the modified buffer data to the data file.
i3, LGWR Log write process
1), responsible for writing log data in the log buffer to the log file.
2), the system has more than one log file, the process in a circular way to write data to the file.
I4, Ckpt checkpoint process
1), in order to prevent instances from crashing, in order to carry out the process of instance recovery as soon as possible.
2), Checkpoint includes two kinds: full checkpoint, incremental checkpoint.
3), the full checkpoint is triggered only in the following two cases:
1, the user issues the alter system checkpoint;
2, the database is closed normally except for shutdown abort.
3), incremental checkpoint trigger situation:
1, every three seconds.
2, online log switching.
How do DBWR, LGWR, and ckpt work in harmony?
i5, viewing process information
1), query the background process information initiated from v$process
2), view started several DBWR processes
SELECT * from v$process where program like '%dbw% ';
See the number of DBWR processes: Show parameter the number of DB_WR,DBWR processes has a certain impact on the generation of dirty data.
To modify the number of DBWR processes:
Alter system set DB_WRITER_PROCESSES=3 scope=memory; --"Unable to modify specified initialization parameters" error
Alter system set DB_WRITER_PROCESSES=3 scope=spfile;--modified successfully and will take effect the next time you start
3), view started several arc processes
SELECT * from v$process where program like '%arc% ';

13. Automatic PGA Management in 9i
I, the premise is: must set the initialization parameter Workarea_size_policy to auto.
II, automatic PGA management, the DBA according to the load situation of the database, estimates that all sessions will probably need to consume the total size of the PGA, and then set the value to initialize the parameter pga_aggregate_target, Oracle automatically adjusts the size of the PGA for each session.

14. Automatic SGA Management in 10g
I, automatic SGA management, namely Asmm,automatic Shared Memory Management. It is only possible to allocate a total size for the SGA, so it is not necessary to allocate the size of each part of the SGA as much as 9i.
II, you can start ASMM if you set the initialization parameter statistics_level to typical or all. You can then use a new initialization parameter, Sga_target, provided by 10g to define the size of the entire SGA.
III, for this reason, 10g has a new background process Mman, memory Manager, for automatic SGA management.
IIII, note Another background process Mmon, memory monitor, for automatic statistical information collection.

15. Automatic memory management in 11g
I1, automatic memory management, that is amm,automatic memories Management. Allocating a total memory size for Oracle's use as a whole can be done without allocating the size of the SGA and PGA as much as 10g.
The i2, initialization parameter statistics_level is typical or all before amm can be started.
Show parameter statistics_level; --View AMM
Show parameter memory; --See how much memory the entire Oracle occupies
Alter system set memory_max_target=900m;--sets the maximum memory size can be reached
i3, the new initialization parameter Memory_target, defines the overall size of the memory, which is the total size of the SGA plus the PGA.
alter system set memory_target=500m; The value of--memory_target setting is either greater than memory_max_target
I4, the new initialization parameter Memory_max_target defines the maximum achievable value for memory_target.
Alter system set memory_max_target=900m Scope=spfile; --The next launch takes effect, Scope=spfile can't omit or error
i5, note: If you use AMM, the values for Sga_target and pga_aggregate_target should be set to 0.
Show parameter sga_target;
Show parameter page_aggregate_target;
alter system set sga_target=0m;
alter system set pag_aggregate_target=0;
Background process Mman in I6, 11g for automatic memory management.

16. Oracle Database structure
I, the Oracle database consists of operating system files that provide the actual physical storage for database information.
II, Oracle database includes logical structure and physical structure;
The physical structure contains a set of operating system files in the database;
Logical structure refers to the relationship between logical concepts formed after database creation.

17. Physical Structure
The physical component is the operating system physical file used by the Oracle database. We can see in the D:\DEV\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL directory, different files in the production environment are placed in different directories, according to the frequent situation of user access to make decisions.
1), the main physical files can be divided into three categories:
I, data files: Data files are used to store database data, such as tables, index data, and so on.
II. Control file: The control file is a binary file that records the physical structure of the database.
III. Online log files: Records all modification information for the database for failback.
Note: If you lose these files, the database cannot continue to run and media recovery is required.

2), non-primary physical files include:
parameter files, password files, alarm and trace files, archive log files, backup files.
The parameter file is divided into the Pfile file and the SPFile file, which is located in the Oracle_home\database directory, and the default 9i does not use sfile files, such as Spfileorcl.ora files.
Password file: Sys user password, located in the Oracle_home\database directory, such as the Pwdorcl.ora file.
To see if there is an archive log: Archive log list.
Oracle_home:d:\dev\oracle\product\10.2.0\db_1
Note: If you lose these files, the database can continue to run without the need for media recovery.

18. Logical Structure
The logical structure of a database is to analyze the composition of a database from a logical point of view.
The logical components of Oracle include: Database--------------------

19. Table Space
I1, the tablespace is the largest logical unit in the database, and an Oracle database contains at least one table space, which is the system tablespace named Systems.
I2, each tablespace is made up of one or more data files, and a data file can be associated with only one table space.
The size of the i3 and tablespace is equal to the sum of all the data file sizes that make up the tablespace.
The syntax for I4, creating tablespaces is:
CREATE tablespace Tablespacename
datafile ' filename ' [SIZE integer [k| M]]
[Autoextend [off| On]];
i5, default table space
1), default contains the following tablespace (viewed from v$tablespace): SYSTEM, Sysaux, TEMP, UNDOTBS1, USERS, EXAMPLE.
2) system is the most basic information of the systems table space, and if the system table space is broken, Oracle will not start.
3), Sysaux, introduced from 10g, as the auxiliary table space of system, to reduce the load of system table space. Some of the components in the previous table space are now placed in the Sysaux table space. For example, previous system tablespace Logminer, logical Standby in previous system tablespace, spatial, Oracle text in previous Drsys table space, and so on.
I6, temp, is a temporary tablespace that is used when sorting cannot be done in the allocated space, that is, in the temporal tablespace in the Oracle instance.
UNDOTBS1 is the undo table space, which is the undo type of tablespace that holds the data before the user makes a DML operation.
Users are the default persistent tablespace for the database.
Example is the table space to which the table involved in the database test case belongs.
i7, select * from dba_data_files;--view table space Specific information
SELECT * from v$tablespace;--view table space

20, section, District, database, mode
I, Duan, ASSM automatic segment storage Management
1), segment is the logical storage structure of the table space, the segment consists of a set of zones.
2), according to the characteristics of the data stored in the paragraph, divides the segment into several types, including data segment, index segment, fallback segment and temporary segment.
II, District
1), the area allocated space for the segment, it consists of continuous data blocks.
2), when all the space in the paragraph is fully used, the system automatically assigns a new zone to the segment.
3), the zone cannot exist across data files, only in one data file.
III. Database
1), the data block is the smallest storage unit that the Oracle server can allocate, read, or write.
2), the Oracle server manages the storage space of data files in blocks.
3), show parameter db_block_size; --View data block size
IIII, mode, equivalent to user
1), mode is the generic name of the database object created by the user.
2), schema objects include tables, views, indexes, synonyms, sequences, procedures, and packages

From: http://www.cnblogs.com/linjiqin/archive/2012/03/02/2376647.html

ORACLE11G Architecture Detailed