Oracle Learning Starter Series five memory structures, database structures, processes

The Oracle Learning Primer series Five

Memory structure, database structure, process

The previous Toad briefly described some of the features of Oracle's database products, other products, and Oracle software. Although the dry goods have but the sincerity is relatively few, this beginning is to dry-based, which is mixed with some of the matter of nonsense. Toad has been thinking how to vividly through this dry text to describe the world-class software, too theory we can not see, too practice and lack of depth. Think of yourself in college that will, the teacher's lectures, all dull and boring ah, full of interest is exhausted. So we can't emulate it, we have to go our own way. Describe esoteric computer knowledge in the most understandable form, metaphor, and other means. Walk up ~

The opening question is how the memory structure, database structure, and process in Oracle are in 3 of the following small topics.

I e-mail:[email protected]

Public Number: Hoptoad

Welcome to communicate with all

1 Oracle Database structure

On this topic, the online search is absolutely a lot, not to mention the book appeared, there are many masters of the speeches. But we do not care about those, according to our own rhythm to write down characteristics, remember the wonderful.

First insert the first picture of this series, figure one (absolutely from the official website), see how careful Toad for the picture use, if everyone has their own unique understanding of this figure, then you can skip this note directly, of course, it is also possible to know the new shame, and then Yong .... and come shame?

1.1 General structure

This Figure 1 generally describes the approximate Oracle database, including the memory structure, database files, processes and clients. Although not very detailed, Toad thought the diagram was a good illustration of getting started with the Oracle database.

So let's watch what the picture says.

Service process, where the deployment is the service process is a dedicated service process or sharing, follow-up will slowly come.

Memory structure, the memory structure mainly includes the PGA and SGA,PGA is for the client Access Time service, the SGA is the database operation service, these after the machine power down disappeared.

There are also the main 6 process PMON,SMON,DBWR,LGWR,CHPT,ARCN, which are only available when the database is started.

The bottom is stored in the persistent media files, have archived logs, data files, parameter files, password files, etc., not with the machine under the power and disappear.

On the way there are 2 concepts, examples and databases that are very important in Oracle:

1.1.1 Instances

An instance is a combination of the processes and memory structures created by Oracle.

1.1.2 Database

A database is made up of a set of files, and all operations on the database need to be done through an instance. Typically the database and instance are one by one corresponding. The exception is RAC, where the RAC next database corresponds to multiple instances.

Examples and database relationships are a metaphor for the relationship between the engine and the car. The car is the database, right there, but can't move, only the engine started, the car can move up. In general, a car is a single-cylinder engine, but it can also be multi-cylinder, such as a 6-cylinder engine or something.

Then look at the logical database structure.

1.2 Logical database structure

into the second chapter diagram, as shown in Figure 2, as from the Internet

Here need to mention some concepts, are the most basic in Oracle database and need to always grasp the concept, is always, death can not forget, if you worry about forgetting to write down. The concepts mentioned here are follow, many of which can be applied to other databases, and remember that we will be able to do more with less later.

Let's start from the bottom, boring but not boring ha ~

1.2.1 Data block

The data block, which is the storage base of the Oracle data block, is composed of several bytes. What is the number of bytes? Commonly used is 8KB,4KB, can be specified when creating a database. Once specified, it cannot be changed, but even in a 4KB database, we can specify to create a 8KB tablespace as long as there is 8KB cache in our memory structure.

Data blocks are similar to block sizes in an operating system, and the Oracle database block size is usually an integer multiple of the size of the operating system block, with the benefit of not having to say much.

Toad's own work experience, in the OLTP model, 4KB, 8KB and 16KB performance difference is not particularly obvious.

However, the principle is that the block size is the smallest unit of processing Oracle's updates, selection, or insertion of data transactions, and the access is random, then the smaller block size is selected, if the rows are smaller and the access is mostly contiguous, or if there are large rows, a larger block size is used.

1.2.2 Data range

Area, extent, is a two or more contiguous Oracle data block, which is a unit of space allocation. The concept is always so clear, 3 key points.

A) 2 or more than 2

b) contiguous blocks of data

c) Space allocation unit

A data block is a storage unit, and an area is an allocation unit. Looking at the concept of weight unimportant, from the concept we know that Oracke allocates space when at least 2 blocks of data start. If you apply for a block of data, then I'm sorry, just don't give.

As a child to mom and dad to ask for pocket money,

US: "Mom, give me 5 cents, buy a turnip to eat"

Mom: "! @#￥% ..., no 5 points "

US: "A dime is fine"

Mom: "OK, buy two more bags of shredded radish?" , give you 1 cents. "

1.2.3 Data Segment

A segment is a set of extents assigned to a logical structure.

2 Key points

A) Assign to a logical structure

b) a set of areas

1.2.4 Object

The Oracle database objects are basically the following 10.

1) Table

2) Constraints: ensure data integrity.

3) View: Virtual table, named query statement.

4) Index: Speed up the query (speed up the query).

5) Sequence: A series of consecutive increments or decrements of the number, the same step, (surrogate key).

6) Synonyms: Another name for an object (an alias for an object).

7) Stored procedure: for operation

8) Function: Used as a complex operation. Used for calculations.

9) Trigger: The stored procedure triggered by the event.

10) package

The operation of the database can be basically attributed to the operation of the data object, and the object Toad does not know how to define it. An object is also a logical structure, a gravitated structure built on top of a segment.

1.2.5 Table Space

A tablespace is a set of data files, usually made up of related segments. A table space is a logical entity that contains physical data files. The tablespace stores all available data for the database.

The corresponding relationship between the tablespace and the physical data file can be seen in the next section. The tablespace is also a logical structure, which is the largest logical structure under the database.

After the database is created, you will have the following table space

A) system table space

b) Sysaux table space

c) Undo Undo table Space

d) Temporary temporary table space

1.3 Physical database structure

The logical structure of the Oracle database is described in the previous section, so it is important to see how the logical and physical storage structures correspond.

First, in general, we can understand that the ORAC le Database is made up of table spaces, so it starts with the table space associated with the physical database structure.

First look at Figure 3below:

It can be seen that the logical structure is somewhat more complex than the physical structure.

Physical structure on two things operating system blocks and files. The database block mentioned in the previous section is an integer multiple of the operating system block and can also reflect one or two.

Here the Toad focuses on the files in the Oracle database.

1.4 Data files

A data file is typically the largest physical storage part of a database, and a data file can belong to only one database. One or more data files form an entity that becomes a tablespace.

When we create a table space, let him have a 2G size data file, then the table space will be able to store 2G size of data, one day to run out of 2G, then add a data file to the table space, of course, can also enlarge the size of the original data file.

Note: the previous article mentions objects such as indexes, tables, and so on, although it is also in the data file that the table space belongs to, but the objects themselves are not specified to be stored on that data file, and the data file is only associated with the tablespace .

The database performs the following view of the data file where

Sql>selectname from V$datafile;

1.5 Control files

A look at this file, Toad can not help but sigh: so small, it is so important ah. Small stature, big effect.

This control file, which manages the state of the database, is very important, and the general administrator will make at least 3 copies of the backup.

The control file contains the details of the data file and the redo log file name, location, log sequence number, backup, and all important SCN (System change number). The database is constantly updating the control file during the operation.

The checkpoint information in the control file enables Oracle to determine how much to return from the online redo log file in order to recover the data. In addition, when you start an Oracle instance, through the control file, you determine all the data files and redo log files that the database operation must open.

Do you think it's important?

See where the control files are located

Sql>selectname from V$controlfile

1.6 Log files

The log file is actually a redo log file that records all changes made to the database and helps to restore the database. If the log file is full and then archived, the archived log file is called the archive redo log, and the log file being logged is called an online redo log file.

Oracle requires a database of at least two redo log groups, each at least one separate log file. The current log group is not available at the time of filing, so a second log group is required to connect to it. This cycle, endless.

Considering the role of log file recovery database, it is generally recommended that multiplexing redo logs be stored on different disks to ensure that they are not easily lost.

See where the Redo log files are

Sql>select member from V$logfile;

1.7 Other documents

There are some documents, toad first simple take a moment, follow-up we go further.

Spfile,pfile, password files, alarm log files, trace files, backup files, etc.

SPFILE is an initialization parameter for an Oracle instance, which is a binary file that cannot be edited manually, and we can generate pfile,pfile from SPFILE by command to support manual editing. The size of the components in the memory structure in the following section can be set in SPFile or Pfile.

The password file is the name of the database user granted SYSDBA or sysoper administrative rights, and the database security related, the subsequent toad will and everyone together to mash drum.

Alarm log file , Toad likes to call him alert log. Captures major changes and events during the operation of the Oracle instance. Contains log switching, errors, alarms, and other information, but also the database wrong time, Toad first time to check the place, we can go inside to see also. Alert Log file path:

$ORACLE _base/diag/rdbms/[sid]/trace/alert_[sid].log.

Trace Files , the information dump (dump) contained within Oracle's various internal structures to trace files so that users can resolve various failures based on the contents of the file.

backup Files , as the name implies, because the backup data operation gets the files.

2 Memory structure

The memory structure is also part of the Oracle DB instance. This memory structure allows you to share executable code between users, write database changes to memory areas, and so on, to improve database performance by several orders of magnitude.

Let's look at Figure 4 .

A picture is better than thousands of words, one can see the database memory structure including the PGA and the SGA, then the question comes, specifically?

2.1 SGA

The SGA is the most important memory component in an instance, especially in an OLTP database system, much larger than the PGA. In a data warehouse environment, the PGA may be the more important area of Oracle memory.

2.1.1 Database buffers

Saves a copy of a block of data read from a file, improving read performance.

Of course the database buffers themselves can also be refined into: free buffers, dirty buffers, pinning buffers.

The primary purpose of the buffer is to minimize the data misses and disk IO operations.

You can divide a buffer into multiple buffers for use by different objects, or you can set up buffers for multiple database block sizes (for example, 4kb,8kb,16kb).

2.1.2 Shared Pool

A shared pool is information that maintains executable PL/SQL code, and a data dictionary table. It can be divided into library cache and dictionary cache.

Note: A data dictionary is a set of key tables maintained by Oracle that contains important metadata such as data tables, users, permissions, and so on.

The pool can reduce the compilation of the same code, reducing hard parsing and thus reducing memory and CPU resources.

Toad knows. The solution for low hit ratios for data dictionaries and library caches is the same, which is to increase the size of the shared pool.

2.1.3 Redo Log Buffers

We'll be back. Writes the log to the process, because the log writes the process the refresh frequency, the area size does not need to be too big, the basic few M is done.

2.1.4 Java Pool

Reserved for any Java-based application of the JVM.

2.1.5 Large Pool

You need to use a large pool when using parallel queries. Also, it is recommended that you configure large pools with Rman and shared server configurations.

2.1.6 Flow Pool

Enables the sharing of data between different databases and between different environments.

2.2 PGA

A program global zone is created for each user when a user initiates a session. The PGA holds data and control information for dedicated server processes created by Oracle for users. The PGA is exclusive and cannot be shared unless a shared server configuration is used.

The PGA can hold the user's cursor, is not shared with other users, and also involves some sort of memory intensive operation of the class.

Concept Introduction guys look at the joy of a good, now do not need to be too serious.

3 process

Process is part of an Oracle DB instance, and we said that an Oracle DB instance consists of memory structures and processes.

Oracle processes can be divided into two types: User processes for connecting users and databases, and Oracle processes for performing all the actual operations of the database (read-write data files, write logs, etc.). Here Toad to talk about the most important background process, other processes we have the opportunity to say.

What is the process? This toad won't say a word, huh?

Let's go straight to the point where the following processes can be executed in the system where Oracle data is installed

#ps –ef | grep Ora_

To view.

3.1 Data write process (DBWR)

The purpose of this process is to write the data in memory to disk, making the modification of the database persistent. Because of the performance problem, not all changes to the database will be written directly to the disk, but in memory, and so on when the time is ripe to batch brush into the disk, this performance is greatly improved.

The timing is three things like:

A) Database publishing checkpoints

b) Memory buffers do not have free space

c) 3 seconds

Above 3 points, any point can be satisfied.

Given that some systems have a large amount of data being brushed each time, Oracle supports multiple processes to write together. By setting the db_wirter_processes parameter in Pfile, if the system supports asynchronous IO, it is better to use only one write process.

3.2 Log Write process (LGWR)

Similar to the data write process, writes the redo logs in-memory cache to disk. This write feature is 100% sequential write. If the log file cannot be written, the database will stop working, of course, this is a very small occurrence. After all, how could it be that all of a sudden could not be written?

Log write is also a time-like data write process, this process has four chance points

A) 3 seconds

b) Buffer to One-third

c) Data write process trigger (complete the log write process before the data write process, for this there is a pre-write protocol)

d) Transaction commit, write commit record to redo log

Note: The Redo log file may contain the commit and uncommitted transaction records.

3.3 System Monitoring process (Smon)

This is the system monitoring process, not always working, periodic inspection. Main processing

A) If the instance crashes after a restart, is it consistent

b) Merge the availability zones (we said that the area is an adjacent block of data)

c) Removal of unnecessary temporary segments

3.4 Process monitoring process (Pmon)

This process is used to clear the failed user process and to ensure that the database frees the resources that the dead process consumes. This process and Smon, is generally inactive, but will be periodic inspection, similar to the head of World of Warcraft supervision, do not work, periodic walk, to see who is lazy, lazy it on the hands.

If can toad also want to become Pmon,smon, their two brothers work real Easy Ah, a look is leadership.

3.5 Checkpoint Process (CKPT)

Notifies the database write process, which also triggers the log write process. The role of this process is to synchronize buffer cache information with the information on the database disk.

The process is specifically done with the following 4 things:

A) log buffers are written to the log file

b) checkpoint record written to log file

c) Swipe data cache to disk

d) Update the header of the data file and control file

3.6 Archive Process (ARCN)

This process is only possible when the system is archived, and is responsible for archiving the filled log files. is to generate an archive log document for the contents of the online log file. If required, you can set up multiple simultaneous archiving operations.

This process starts to work when the log is switched.

If we run in a non-archive mode, then there is no such process, but the production system must be in the archive mode of operation, or the consequences of unimaginable, Toad himself playing the system crash is ok haha.

Other processes in this will not be wordy, the follow-up will naturally involve. This is the end of this article-if you are interested in reading the next note.

Oracle Learning Starter Series five memory structures, database structures, processes