Go to: Understand Oracle database structure components from the inside

Transferred from:

Http://database.51cto.com/art/200707/51422.htm

[Bring togetherBlog of Oracle: instances and databases

Instances are composed of the following: SGA, sharedpool, databasebuffercache, and redologbuffercache. A database consists of physical files. The required files include data files, control files, and redo logs. There are also parameter files, password files, and archived log files (these three are not mandatory ).

After a connection is established, the user starts a server process to complete sqlcommand in place of the user process in the future, and then changes the database-related files (data is read or modified) through the Oracle instance ).

User processes cannot directly operate on the database, but must be completed by establishing a connection through the server process.

Oracleserver consists of two parts,

1. instance: consists of the memory structure and background process.
2. Database: consists of data files, log files, and control files <these three files are required>.

Controlfile is used to connect instances and databases:

SQL> shutdownimmediate SQL> startupnomount SQL> alterdatabasemount

The above three processes are connected to the instance and database through controlfile.

SQL> alterdatabaseopen: one-time verification of database data files and redo log files during the open process to verify their statuses.

Oracleinstance: a means of database access.

The relationship between a database and an instance is 1: n. a single instance can only operate on one database. The memory structure (shared pool, buffercache, redologbuffercache) and the corresponding process structure (pmon <ProgramMonitoring process>, SMON <system monitoring process>, ckpt <Checkpoint Process> ).

SQL> showsga --- display database memory structure information SQL> setwrapoff SQL> setlinesize200

The preceding two values indicate the row width.

SQL> select * fromv $ bgprocess;

We can see all the processes that may be used in this system. paddr does not assign a valid address to every process, that is, not every process is required.

SQL> select * fromv $ bgprocesswherepaddr <> '00'

All required processes are displayed.

Establishingaconnectionandcreatingasession

Connect to an oracle instance

This includes creating a user connection and creating a session.

SQL> select * fromv $ controlfiles; -- displays several control files in the current system. SQL> select * fromv $ datafile; -- display consists of several data files. SQL> select * fromv $ logfile; -- display consists of several log files.

Oracle memory structure (memory structure)

It consists of two parts:

1. SGA is dynamic, and its maximum value is specified by sga_max_size. The SGA memory is dynamically adjusted by sgacomponents.

2. PGA is not shared, that is, it contains different information. There are two available memories that can be configured by SGA:

(1) largepool (2) javapool SQL> showparametershared SQL> showparameterdb_cache SQL> showparameterlog

The preceding three commands are used to view memory information.

SQL> altersystemsetdb_cache_size = 20 m;

The total memory size cannot exceed the value of sga_max_size? It is caused by system language problems.

You can use altersessionsetnls_language = 'American 'or altersessionsetnls_language = "simple Chinese ".

Sharedpool)

(1) librarycache library cache;

(2) datadictionarycache: Specifies the data dictionary cache, which is also called row cache. shared_pool_size specifies the size.

SQL> altersystemsetshared_pool_size = 64 m;

Librarycache is mainly used to improveCodeStorage of recently used SQL and PL/SQL code.

(1) least recently used (LRU)Algorithm;

(2) includes two structures: 1: Sharing SQL code; 2: Sharing PL/SQL code;

(3) It cannot be directly defined, but determined by the sharedpoolsize.

Datadictonarycache.

For example, SQL> select * fromauthors;

The process of executing this command is: first confirm whether there is authors, then confirm that the field does not exist, then check the syntax, and finally verify the permission, and the information is the content of datadictionarycache. Its information includes databasefiles, tables, indexes, fields, users, permissions, and other database objects.

(1) It is mainly used to change the system's sensing time and performance.

(2) by changing the sharedpool size, datadictionarycache cannot be set separately.