Concepts and terminology of Oracle (2)

§ 2. 7 Oracle Memory Structure

As mentioned above, Oracle database instances are composed of a group of background processes and memory structures. The memory structure includes:

System Global Area)

Program Global Area)

§ 2. 7.1 system global Zone

The Oracle System is a storage area used to store system information. Both user processes and Oracle background processes can use SGA. SGA contains many components (different parts ).

Data buffer cache)

Data blocks recently used by the Oracle system are stored in the data high-speed buffer zone (that is, the user's high-speed buffer zone). When writing data to the database, it reads and writes data blocks, when the data high-speed buffer is filled up, the system automatically removes some infrequently accessed data. If the data you want to query is not in the Data High-speed buffer, Oracle automatically reads data from the disk. The data high-speed buffer zone includes three types:

1) Dirty partition (dirty buffers): contains data blocks that have been changed and need to be written back to the data file.

2) Free Zone (free buffers): it does not contain any data and can be written again. Oracle can read data blocks from data files.

3) Retention zone (pinned buffers): This zone contains a zone that is being processed or explicitly reserved for future use.

After Oracle8i, the buffer pool will be divided into three zones (when multiple buffer pool features are used ):

1) Keep Buffer Pool: data blocks are retained in the memory and will not be squeezed out from the memory;

2) The recycle buffer pool removes data from the unwanted memory;

3) The default buffer pool contains allocated blocks.

Redo log buffer (RaDO log buffer)

Any transaction must be put in the redo log buffer before it is recorded to the redo log (online redo log is required for recovery. The log is then written to the process (lgwr) to regularly write the content in the buffer to the redo log.

Shared Pool)

A shared pool is a reserved area of SGA and is used to store stored procedures and packages such as SQL and PL/SQL, data dictionaries, locks, character set information, and security attributes. The shared pool includes:

1) Library cache );

2) dictionary high-speed buffer (Dictionary cache ).

Library Cache)

The zone includes:

1) shared SQL zone (shared pool area );

2) Private SQL area (Private SQL area );

3) PL/SQL stored procedures and packages (PL/SQL procedure and Package );

4) control structure ).

That is to say, this area contains SQL statements that have been analyzed by syntax and are ready to be executed at any time.

Dictionary high-speed buffer (data dictionary cache)

It is used to store all the information required by the Oracle System Management. This information is the username used to log on to Oracle. These users have database objects and their locations.

Svrmgr> show SGA

Total system global area 35544188 bytes

Fixed size 22208 bytes

Variable Size 3376332 bytes

Database buffer 122880 bytes

Redo buffer 32768 bytes

The last four items are added to the previous one.

You can also use select * from V $ SGA; to query the SGA of the current instance.

Large pool)

A large pool in SGA is an optional buffer. It can be configured with management right as needed. It provides a large zone for backup and recovery operations such as databases.

2) program global zone (PGA)

PGA is the memory area used by Oracle. It can only be stored and controlled by a single process at a time, used to store session variables and internal arrays.

SGA

Oracle memory structure (SGA diagram) § 2. 7.2 Global Program Zone

PGA is the memory area used by Oracle. It can only be stored and controlled by a single process at a time, used to store session variables and internal arrays.

§ 2. 8 Oracle instances

An Oracle instance is composed of SGA, background processes, and data files. Each database has its own SGA and an independent Oracle process set. :

Oracle instance and background process (instance diagram)

In the case of distribution, Oracle uses a SID (system identifer) to identify the names of each Oracle server so that the names of different database systems are not confused, in UNIX environments, the oracle_sid variable is used to distinguish between them.

§ 2. 9 multi-threaded server (MTS)

Multi-threaded ORACLE Server (multithreader server) allows multiple connections to the database to fully share memory and resources, which allows a small amount of memory to support more users.

All processes connected to the Oracle database occupy a certain amount of memory. Therefore, if too many processes connect to Oracle, a performance bottleneck occurs.

Oracle8 allows more than 10 thousand users to connect to Oracle at the same time, but not all users use MTS. Currently, some 4gl tools do not support MTS, such as VB and Pb, and VC/C ++ does not support MTS. The Oracle multi-thread server has its own connection pool (that is, the Shared Server process ). Because users share open connections, this is much faster than the original dedicated method (eliminating bottlenecks ).

Multithreading is very suitable for some specialized application systems, such as the order registration system, where the customer submits the order, and the data of the recorded employee's order; another recorder is negotiating with the customer, not all data is being input (dedicated server processes are idle ). These terminals are forced to connect to the system and occupy the resources of other users.

Multi-threaded servers eliminate these shortcomings. A multi-threaded server only maintains one connection pool. When a terminal needs to talk to the system, it can be allocated a connection. You can remove it if you do not need it. In this way, the system resources are shared by multiple users.

Change the relevant parameters in the parameter file to make the system a multi-threaded server configuration (effective after restart ). In addition, the database instance must provide the same number of users as the number of users placed.

§ 2. 10 Oracle transaction processing process

Bank withdrawal process:

1. Issue the SQL statement for querying the remaining amount, such:

Select account_balance from banktable

Where account_number = '123'

And account_type = 'savings ';

The SQL statement uses SGA to obtain the server process;

The server process checks whether the statement exists in the Shared Pool. If no statement exists, it is placed in the Shared Pool and ready to run;

Execute the SQL statement to read the high-speed data buffer of SGA from the data file of the remaining data blocks;

Display results. For example, the remaining amount is $325.

2. Withdrawal $25: The SQL statement is:

Update bank_table set account_balanct = 300

Where account_number = '123'

And account_type = 'savings ';

The customer process transmits the SQL statement to the server process through SGA;

The server process queries whether the statement exists and executes (;

Analyze SQL statements and store them in the shared pool;

Execute SQL statements;

Is the data to be processed in the Data High-speed buffer? Yes to 7;

Reads data blocks from data files to high-speed data buffer;

Record the original value ($325) in the rollback segment );

Generate a copy of the transaction in the redo log;

Change the balance in the Data High-speed buffer to $300;

Bank teller machines send work completion signals through SGA (submit ):

Record completed transactions in the redo log;

Clear the Undo information in the rollback segment );

The customer has obtained the money. § 2. 11 sys and system modes of the Oracle System

Sys and system are two accounts installed by default on each Oracle database system. Sys is the owner of all internal database tables, structures, process packages, and so on. It also has V $ and data dictionary views, and creates all encapsulated database roles (dBA, connect, resource ). Sys is the only user who can access a specific internal data dictionary. System is also created when oracle is installed for DBA task management.

The default password after sys is installed is change_on_install, and the default password for system is Manager. For security, after installation is complete. Use the alter user SYS identified by PASSWORD command to modify the passwords of these two privileged accounts.

§ 2. 12 Oracle system tracking File

All Oracle databases have at least one file used to record system information, errors, and major events. this file is called alertsid. log (the Sid here is the System ID of Oracle), where the storage location is initsid. the background_dump_dest parameter of the ora file is provided.

Both the background process and the user process can create their own tracking files. The background process tracking file location is given by the background_dump_dest parameter, and the user tracking file location is given by the user_dump_dest parameter. For example, the parameter file initora8.ora provides:

# Define directories to store trace and alert files

Background_dump_dest = D:/Oracle/admin/ora8/bdump

User_dump_dest = D:/Oracle/admin/ora8/udump

The background trace file is named sidproc. TRC.

§ 2. 13 ORACLE System Data Dictionary

Data dictionary is the knowledge base for all objects stored in the database. The Oracle database system uses the data dictionary to obtain object information and security information, users and DBAs use it to view database information. The data dictionary stores information about data objects and segments. Such as tables, views, indexes, packages, processes, and information about users, permissions, roles, and audits. A data dictionary is a read-only object and cannot be modified by anyone.

§ 2. 14 other data objects

In addition to the preceding data objects, Oracle also includes views, sequences, synonyms, triggers, database chains, packages, processes, and functions. The following is a description.

§ 2. 14.1 View

View is the SQL statement stored in the database for query. It is mainly for two reasons:

Security reasons: the view can hide some data, such as the Social Insurance Fund table. You can use the view to show only the name and address, but not the social insurance number and wage number, another reason is that complex queries are easy to understand and use.

§ 2. Sequence 14.2

A sequence is a database object used to generate unique digital data. A sequence is created with an initial value, an increment value, a maximum value, and a maximum of 38 integers.

§ 2. 14.3 trigger

A trigger is a special stored procedure. Its execution is not called by a program, nor is it manually started, but triggered by an event, for example, when you perform operations (insert, delete, update) on a table, it is activated for execution. Triggers are often used to enhance data integrity constraints and business rules. The trigger can be found in the dba_triggers and user_triggers data dictionary.

§ 2. 14.4 Synonyms

Synonym is a database pointer to other database tables. There are two types of synonyms: private and public ). Private synonyms are created in the specified mode and accessed only in the mode used by the creator. Public synonyms are accessed in the mode specified by public, and can be accessed by all database modes (users.

§ 2. 14.5 database chain

Database link is a storage definition for connecting to a remote database. It is used to query remote users in a distributed database environment. Because they are stored in the dba_db_links data dictionary, they can be considered as a database object type.

This article from: http://oracle.chinaitlab.com/serial/754900_6.html