Shulou(Shulou.com)06/03 Report--

Two connection methods of Oracle database: dedicated server connection and shared server connection

1. Composition of Oracle database:

Oracle server is made up of instances and databases

Oracle database consists of: data files, redo log files, temporary control files, parameters files, password files, etc. (the most important of which are data files and redo log files)

The oracle instance is composed of a shared memory block of the system global area SGA and a large number of background processes.

SGA includes: database buffer cache, log buffer, shared pool, and (large pool, Java pool, stream pool)

Background processes: DBWR (database writing process); LGWR (log writing process); CKPT (checkpoint); SMON (system monitoring process); PMON (process monitoring); ARCH (archiving process); RECO recovery; LCKN blocking; etc.

PGA: the user session also requires server-side memory, which is not shared, also known as the program global area PGA, and each session has its own PGA.

-starting from Oracle12C, the undo generated by global temporary tables can be stored in temporary tablespaces.

"Control file"

The control file is a fairly small file (which can grow to 64MB in extreme cases) that stores the location of some files needed by the database, as well as other information, such as information about the checkpoint, database name, database creation timestamp, history of archiving redo logs, and RMAN information.

"redo log"

Redo logs are divided into online redo logs redo and archived redo logs.

Online Redo log files-- online redo log, also known as online redo log, means that Oracle records database data updates in real time in the form of SQL scripts, in other words, real-time saving executed SQL scripts to online log files (in a specific format).

Note: the most important goal of the online redo log is to be able to recover committed transactions after the instance fails

Archive Redo log files-- Archive redo Log, or Archive Log for short, means that Oracle saves the online redo log as a file to the hard disk (persistence) when the conditions are met.

SGA and PGA

PGA is the user process area, and it can also be understood that if the concurrency is 500m, then 500m of PGA needs to be allocated.

PGA is a process-specific memory area

Redo buffer

-in fact, the LGWR process flushes buffer data to disk when any of the following occurs:

1. Every 3 seconds

2. When a commit or rollback request occurs

3. When LGWR is required to switch log files

4. When the redo buffer is full of 1/3, or when the cache redo log data reaches 1MB

When data needs to be written to the online redo log, it needs to be temporarily cached in the redo buffer before it is written to disk.

The default size of the redo log buffer is controlled by the log_buffer parameter

Block buffer cache block buffer cache

Oracle stores database blocks in the block buffer cache before they are written to disk or after they are read from disk

The block buffer cache must not be too small, or our query will never run out. It can't be too big, because it will seize the resources of other processes.

There is only one block size in the database: one of the 2KB 4KB 8KB 16KB 32KB; if you want to modify the database block, specify: db_block_size=16384 in the init.ora file when you install the database. 16384 is 16K.

"shared pool shared pool"

A shared pool is where Oracle caches some program data, and when a query is parsed, the parsed results are cached there. Before completing the task of parsing the entire query, Oracle searches the shared pool to see if the work is done

Shared pools are characterized by a large number of small blocks of memory, typically 4KB or smaller. Our goal is to use small chunks of memory to avoid fragmentation problems, which are likely to occur if they vary in size.

"Daichi large pool"

Large pools are used to allocate large chunks of memory, while shared pools cannot handle such large chunks of memory.

"JAVA Pool"

The JAVA pool is designed to support running JAVA in the database, and if you write a stored procedure in JAVA, Oracle will use the memory of the Java pool when processing the code.

"flow pool"

Flow pools are used to buffer queue messages, and flow pools are important for goldengate streams, etc. These scenarios must have flow pools

-when SGA manages automatically, the statistics_level parameter must be set to typical or all, because if statistics collection is not enabled, the database does not have a history to determine the amount of memory required by each component.

II. Oracle database process

There are three main types of processes in an Oracle instance: server process, background process, slave process.

-v$bgprocess view allows you to view all possible background processes in the database

Select paddr,name,description from v$bgprocess order by paddr desc

Check the running background processes in the system by querying the records in v$process where pname is not empty.

Note: background processes are also divided into two categories: one is processes with specific tasks, and the other is processes that can perform a variety of other tasks (such as tool processes)

-View the background process started in 12C:

Select paddr,name,description from v$bgprocess where paddr '00' order by paddr desc

(about 22 background processes will be started in 12C, compared with 17 in 11GR2)

1. PMON process Monitor

The process Monitor (process monitor) is responsible for cleaning up after an abnormal termination of the connection. (for example, if a dedicated server fails or is terminated for some reason, the PMON process is responsible for cleaning up and releasing resources. PMON rolls back uncommitted work, releases locks, and releases SGA resources previously allocated to failed processes)

Note: before Oracle12C, PMON was also responsible for handling the task of registering listeners. Starting from Oracle12C, a special listener registration background process was used to register instances and services to listeners.

2. LREG listens to the registration process

Starting with Oracle12C, the LREG process is responsible for registering database instances and services with listeners

3. SMON system monitor

The SMON process is used to do all system-level tasks. The work done by SMON includes the following:

①: clean up temporary tablespaces: (not that you don't need to clean up manually at all)

②: merge free tablespaces: (applicable only for tablespaces that use data dictionaries)

③: a transaction that resumes activity for files that were originally unavailable:

④: perform instance recovery of failed nodes in RAC:

⑤: clean up OBJ$: (OBJ$ is an underlying data dictionary in which almost every object in the database (table index trigger view, etc.) corresponds to an entry. In many cases, some entries may represent objects that have been deleted, or objects that indicate where the not there is not).

⑥: manage revocation segments: SMON will be responsible for implementing automatic loading and unloading of revocation segments and shrinking revocation segments.

⑦: rollback segment offline:

4. RECO distributed database recovery

Reco has a very core task: some transactions may remain ready due to crashes or lost links during two-phase commit (2pc). This process is to recover these transactions.

5. CKPT checkpoint process

The implementation of checkpoints is mainly the work of the DBWn process. CKPT is only to assist the process that actually runs the checkpoints to update the headers of the data files.

6. DBWn database block writer

The database block writer is the background process responsible for writing dirty blocks to disk

The performance of DBWn is very important. If it does not write blocks fast enough to release the buffer quickly, we will see an increase in the number of waiting events and waiting events such as free buffer waits and write complete waits. In fact, we can configure multiple DBWn processes. In fact, we can configure 36 in Oracle11G and as many as 100in Oracle12C.

Select name,description from v$bgprocess where description like'db writer process%';-check how many DBWn processes there are in the database

7. LGWR log writer:

The LGWR process is responsible for flushing the contents of the redo log buffer in SGA to disk.

8. ARCn archiving process

The task of the process is: when LGWR fills up an online redo log file, it copies it to another location, after which these archived redo log files can be used to complete media recovery

9. DIAG Diagnostic process

In the previous version of the technical term RAC environment, the process uses ADR (Advanced Diagnostic Library) from Oracle11G, which is responsible for monitoring the overall status of the real column and capturing the information needed to deal with the real column failure.

10. FBDA flashback data archiving process

11. DBRM Database Resource Manager process

The DBRM process implements resource plans that are configured for a database instance

twelve。 There are other processes:

LMON Lock Monitor

LMD lock manager daemon

LMSn Lock Manager Server

LCK0 lock process

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.