OCP Chapter Fourth: storage structure +SGA

=============================================================================================================== ========================

Storage structure--Video 12

1. Data file

2. Control file

3. Redo log File

4. parameter file

5. passowrd file

Query database name statement:

Show parameter Db_name

Select name from V$database;

Query Instance Name statement:

Show parameter instance_name

Select instance_name from V$instance;

Query data File location:

Select name from V$datafile;

Query control File Location:

Select name from V$controlfile;

Query log file location:

Select member from V$logfile;

Query Archive file Location:

Select name from V$archived_log;

Query parameter file location:

Select value from v$parameter where name = ' SPFile ';

=============================================================================================================== ========================

Server process--video 13

Bootstrap: After a user process makes a request, the server process receives

PGA role: Save user processes to send requests to the server side of private information, such as: Session information, sorting information, etc.

In the PGA space also includes a very important space area, called: UGA (User Global Zone), session information, sorting information, etc. are stored in the UGA space.

PGA Memory: When connected to a user process, the PGA is allocated when the session ends and the PGA is released

PGA Size: 10M to 4gb-1byte

PGA Management method: Workarea_size_policy parameter Manual "Manual Management" | Auto "Automatic Management"

View PGA Space statement: show parameter PGA;

View the PGA management style statement: show parameter work; =============================================================================================================== ========================

Memory structure (SGA)--Video 14

Boot: Data additions and deletions to the operation, will be completed in the SGA memory, the memory is divided into 6 large areas, the operation of the data in these 6 areas are shared, with each other to complete the operation,

When the instance starts, the 6 large memory areas are allocated

When the instance is closed, the 6 large memory areas are recycled

1. Shared Pool

2, database high-speed buffer

3. Redo the log buffer

4. Large Pool

5. Java Pool

6, Streams Pool

The SGA is a readable, writable area of memory where all users connected to the Oracle instance can read or change data in the SGA memory, and 90% of the work is done in memory

Query the size of the large area of the SGA: the size of these memory areas is set in the parameter file

Show SGA;

Allocation of SGA

The total size of the 8I:SGA is determined by the sum of all memory build sizes, cannot directly define the size of the SGA, changes to the size of the memory build must not take effect until after the database restarts, so the total size of the 9I:SGA is called the SGA Static management is determined by the initialization parameters sga_max_size, each memory build large The small sum cannot exceed this parameter, under this size, the SGA each memory organization can change the size directly without restarting the database, therefore called the SGA dynamic management

10G:SGA The total size can be as dynamic as 9i management, can also implement the SGA automatic management, only need to set the initialization parameters SGA_TARGET,SGA each memory build can be automatically set by the database size, based on the automatic collection of statistics from the system

Query the sga_max_size parameter size statement:

Show Parameter Sga_max_size

To modify the sga_max_size parameter size statement:

Alter system set SGA_MAX_SIZE=1025M Scope=spfile;

=============================================================================================================== ======================== memory Structure (SGA)--Video 15

Shared Pool role:

(1) Storage of recently executed SQL statements

(2) Save the most recently used data definition

Composition: Database buffers

Data dictionary Caching

Size: determined by shared_pool_size size

Database buffers: Stores the most recently used SQL and PL/SQL statement information

Share the most commonly used statements

Manage compliance with LRU rules

Consists of two parts:

1. Shared SQL Zone

2. Shared PL/SQL area

Data dictionary caching: the most recently used definition stored in the database

Data files, tables, indexes, columns, users, permissions, and other database objects are saved

During the analysis phase, the server process looks up the data dictionary buffer to verify the name of the object and whether it is a legitimate access

For queries and DML statements, if the data dictionary information is in the cache, the response time can be increased

Size is determined by the shared pool parameter

Analysis of an SQL statement by the SGA

If executed previously, follow a previously executed plan, usually soft analysis "soft parse" or fast soft analysis

Soft ANALYSIS: Submit SQL statements via PGA---parse statement----soft parse----execute statement--via PGA output

If not, Oracle begins parsing the syntax and semantics of the statement, obtains the best execution plan according to the optimizer rules, and saves the statement with its corresponding execution plan in memory.

Hard analysis: Submit SQL statements via the PGA--parse statement------and analyze and get optimized scheme "CBO or RBO"

--execution plan based on optimization scheme--execute statement--via PGA output

LRU Management rules: Because the memory is very scarce resources, for the SQL program needs to pay more attention to optimization, the SQL statement submitted to the memory needs to be managed by LRU, the LRU algorithm determines the duration of the shared object, long-term SQL statement saving information will be released in the LRU, if If you do not want to be released, call the Dbms_shared_pool.keep stored procedure to host the process in a shared pool

=============================================================================================================== ======================== memory Structure (SGA)--Video 16

Database Cache Area: (1) The data image of the data block obtained from the data file is staged, because the memory reads and writes fast, in order to reduce the physical IO, the data to be read is saved in memory operation, that is saved in the cache area

(2) also follow the LRU algorithm to manage memory

(3) When you need to manipulate the data again, you can get and update it directly from memory, and the efficiency of statement execution can be greatly improved.

(4) initialization parameter db_block_size determines the size of the data block, which is the smallest unit of database I/O, the default size is 8K

(5) The database buffer zone is composed of multiple independent sub-cache pools, which can be set independently of the size

Default Pool db_cache_size: If you do not specify which buffer pool the data blocks are saved in, the data blocks will be saved in the defaults pool

KEEP Pool db_keep_cache_size: Keep The object's data block in memory, the data stored in the KEEP pool will not be released by LRU and will remain in memory

Recycle pool db_recycle_cache_size: When some of the data in the pool is no longer important, it clears the data and makes room for new data

"Only the default pool can be set to be automatically managed by the SGA"

Extension: Regardless of which pool of stored data is kept in the buffer zone, the modified data will have two states if the data is modified, that is, the clean state and dirty state are known in Oracle as: Writable list, The list retains the data that has been modified, which is called dirty data dirty state in Oracle is called: ARU list, the list is not modified data, this part of the data is called clean data dirty data will be DBWR background process written to the physical disk, The other data will not be written.

Database Buffer Size:

The Db_cache_size parameter determines

Query the size statement for the default pool:

Show Parameter Db_cache_size

Query the size statement for the Keep pool:

Show Parameter Db_keep_cache_size

Query the size statement for the Recycle pool:

Show Parameter Db_recycle_cache_size

=============================================================================================================== ======================== memory Structure (SGA)--video 17

Redo log Buffers (1) changes in all blocks of data in the staging database are recorded in the redo log buffer when changes are made to data in some chunks

(2) memory management is FIFO, first-out, in this way will ensure that the database log in order to loop the redo log buffer to write

(3) Redo log is used to provide database recovery, if not recovery, basic use of log

(4) In the database, the content that remains in the log buffer will be physically written to the log file in the disk by LGWR the background process, periodically or after the condition is met.

(5) Redo log buffer size is determined by the parameter log_buffer, but this memory area can not be dynamically resized, generally not set too large, 1M to 2M can

(6) The log buffer cannot be automatically managed by the SGA, the size of the buffer should be set manually

To view the log buffer size statement:

Show Parameter Log_buf

=============================================================================================================== ======================== memory Structure (SGA)--video 18: Large pool part skipped ... Memory structure (SGA)--video 19:java pool part skipped ...

=============================================================================================================== ======================== memory Structure (SGA)--Video 20

Process section 1, User process: Client connection to the database program, when the client makes a connection request, the user process starts, when disconnected, the user process shuts down

2, service process: Response and Accept user Action request program, connection is started, service process is assigned, connection is disconnected, service process shutdown

3. Background process: DBWR, LGWR, Pmon, Smon, CKPT

To view the background process statements that are being executed in the current system:

Select name from v$bgprocess where paddr <> ' 00 ';

=============================================================================================================== ======================== memory Structure (SGA)--video 21: The installation section of the database is skipped ... Memory structure (SGA)--video 22: The installation part of the database is skipped ...

OCP Chapter Fourth: storage structure +SGA