Oracle log performance and Principle Analysis, oracle log

Oracle log performance and Principle Analysis, oracle log

Introduction to Oracle log performance and Analysis of Its principles

1) Consistency and Performance

Logs are a core component of all databases.

It is related to database data consistency.

Currently, we can see several databases

Oracle, sqlserver, mysql, db2, and sybase

The log principles of all these databases are almost the same.

The basic content of the databases currently used is basically the same.

Are composed of the most basic relational data tables.

Use Basic SQL syntax to operate various database Entity Data

Basic differences arise from the execution of these basic functions.

You have your own implementation methods and code.

Different advanced functions are mainly produced in consistency protection and performance improvement.

Other advanced functions are also generated when you combine basic functions or add some additional functions.

In fact, the principles of these functions are basically the same.

The difference is only in the implementation method and code

In terms of database volume

Small databases will be deleted in terms of advanced functions

Large Databases have more advanced functions of their own.

Another obvious difference is that the environment generates

The more operating system types the database can use, the more adaptable it is.

Look at all functional software

Similar functions

Once used and familiar with it, you will find that the biggest difference is in appearance.

However, they are competitive in the market, competing with users' usage and capital

Log and performance have little to do

However, the consistency between logs and database data is very large.

When other aspects of a software are not changed

When improving its performance

Some stability will be lost.

This is true for both hardware and software.

If you blindly overclock the cpu, the system will crash easily.

In order to improve the writing speed of the hard disk, some people adopt the method of reducing data validation.

For example, audio and video files in some formats do not perform data verification to make the transmission faster.

Most of these files are damaged after multiple network transmissions.

To make online video playback faster on the Internet

Video resolution is constantly reduced, and the video is blurred.

To make the database process data faster, you can only protect data correctness in the same environment.

However, few people in oracle mention how to improve its performance, but only about how to optimize it.

I thought for a long time and did not expect how to directly improve its performance inside it.

Probably because oralce is a mature Software

The combination of various internal aspects has achieved the greatest balance.

Improving the performance of one aspect will often affect the performance of other aspects.

So it cannot be said to improve performance

All you can do is to make the most balanced optimization of the current software and hardware configurations.

There are some ways to improve the performance of oracle on the network

If you directly improve the performance of the hardware, the performance of the same hardware must be at the cost of loss of stability.

In addition, the general performance can only be improved within a certain range, but more improvement is counterproductive.

An obvious example

You can allocate all the memory to oracle when allocating memory.

But now the operating system has no memory, and the operating system crashes and oralce cannot run.

The performance improvement of oracle software is concentrated on the compilation of the executed customer program.

It mainly enables the customer to better adapt to the oracle software environment during programming.

Faster and safer Client Program Execution

The performance of an optimized program is much higher than that of a program that does not conform to the oracle operating environment.

This requires programmers to be familiar with the basic structure and functions of oracle.

Best Choice of oracle components and functions

It does not change the database, but optimizes the use of the oracle database by the customer program.

Again, it is risky to directly improve oracle performance.

Once an error occurs, data will be lost,

In addition, user data using this software is often very important.

Even if one byte is lost, it may cause huge losses.

Therefore, consistency is important during selection.

We know that for oracle databases or any databases

The focus of our learning is nothing more than two

The first is database data consistency.

We need to ensure this, especially for DBAs.

So we need to study it and it is a subject of our in-depth research.

After listening to the teacher's entire system

Everyone should have a deep understanding of Database Consistency.

No data consistency issues caused by misoperations

This teacher believes there is no problem

In addition, we make oracle run faster.

This is our second goal.

If you are familiar with both goals

If someone else mentions Database Consistency, you have a set of theoretical systems to support you.

There is also a set of theoretical systems and related practices to support your optimization.

This shows that oralce has learned a good job.

Therefore, learning oracle should not be based on the content we have learned so much.

Finally, we are entangled in two major parts.

First, consistency

The second is the database performance.

Oracle logs are only related to consistency and performance.

I would like to emphasize this point for you. Next I will focus on learning logs.

In a sense, oracle Database Consistency is more important than oracle performance.

The database can be slower, but data consistency must not be a problem.

This is a matter of principle.

From the beginning, we talked about the last lesson.

There will be some questions about Database Consistency in the middle

Oracle will be involved in any knowledge process.

Particularly since the beginning of the log

In the future, Database Consistency will continue to be instilled in the discussion.

Ii) oracle log principles

After talking about the log principle, we will know why oracle needs logs.

After the oracle database is started

A bunch of dbf Files correspond to buffercache in the memory.

The memory also contains the redolog log in the disk corresponding to the logbuffer.

The customer's SQL statement will be executed after it enters the database

For oracle databases, there are many connections.

Each connection has a large number of SQL statements.

That is, the database will execute a large number of SQL statements at the same time point.

SQL statements with queries, addition, deletion, and modification

This is what we often call addition, deletion, modification, and query.

We don't care about this first.

1) Historical Records explanation

Buffercache contains a bunch of buffer

Let's take a look at the process of oracle log generation.

Assume that a person is standing on buffercache.

It can see all the buffer in buffercache

And this person's eyes are very fast.

Let's look at the buffercache status.

For buffercache, a large number of SQL statements are executed.

The buffer block in one buffercache in a time point is changed.

Another buffercache block is modified at the next time.

The difference between the two time points is several milliseconds or several microseconds.

At another time point, other buffer blocks were modified.

That is to say, a large number of buffer is modified in buffercache within a period of time.

Then this person is recording a book.

Strictly record a change process of the buffer according to the time.

For example

A buffer is changed, and it will be immediately seen. After it is seen, it will be recorded in the notebook immediately.

Let's take a note of the changes in the buffer at that time.

After the record is complete, another buffer is changed.

In the log, immediately record the change in the buffer at this time point.

This person

Record a change history of the buffer in strict chronological order at extremely fast speeds

These logs are recorded in the logbuffer.

Finally, the logbuffer will be written to the redolog on the disk through the LGWR process.

First write the first redolog

When the first redolog is full, write the second redolog and then write the third redolog.

Let's review it.

The process of log recording and the process of log generation

One person on buffercache

Holding a small book

This person's eyes are very fast and his records are very fast.

It can see all the buffer in buffercache

This person records a change process of all the buffers in buffercache in strict chronological order.

That is to say

Our logs record changes in the buffer and are recorded in chronological order.

This person is doing this thing.

When the buffer changes

It does not care which session comes up and which SQL statement is modified

It only cares about which buffer has changed

So what is recorded in the log is the entire change process of the buffer recorded strictly in chronological order in the buffer.

This person can be called a historian.

We know that history has a role called historian.

It follows the Emperor's side every day

The words and deeds of the emperor and the decisions made by every action will be recorded.

This historian is used to constrain the Emperor.

He strictly and realistically remembers

It does not carry any personal feelings.

Everything done by the Emperor is recorded

The whole life of the Emperor is recorded by time.

This is an example from the historian.

Log Generation Process

After the lecture, We will summarize it in one sentence.

Oracle logs are recorded strictly according to the buffer change time in buffercache.

First, the log records the change of buffer, and second, it records data strictly by time.

It may be better if we use two sentences.

The first log records buffer, and logs are recorded in buffer units.

The second is recorded according to the time sequence in which the buffer changes.

That is, this historian keeps watching which buffer has changed to the next one.

Let's think so first.

This is the log

As we will talk about later, we will gradually understand some ways to record oracle logs.

This is very helpful for future study.

This is explained through the historical records Explanation Method.

2) log recording method

We know that the logging method is recorded by buffer.

How is it recorded?

For example, a buffer in buffercache has changed

There are many ways to Record buffer changes

Oralce wants to record the change of this buffer

First, record the buffer address, that is, the address of the data block in the disk.

Second, the time point, which time changes

Third, what changes have been made to this data block?

For example, if the data block is changed by insert or delete

What is the data inserted by insert?

What is the deleted data?

The change to data blocks is nothing more than adding and deleting data.

In addition, for the buffer, the modification is to delete a row of data and then insert a row of data.

Is to delete this row first and then insert and then form an update

For example, we run a delete SQL statement.

It deletes 10 thousand rows.

These 10 thousand rows are in one thousand blocks.

In this case, we can roughly think that oracle has generated one thousand logs.

Because a log is generated for each block.

This is how logs are generated.

The log contains the address of the data block, the time of the change, and how it is changed.

Add or delete

Also, only one row of data is changed, but dozens of k logs are generated.

This is very likely

Therefore, oracle logs are generated in large quantities.

In the future, we will study:

What is the amount of oracle logs generated?

Will oracle logs generate performance problems?

Impact of logs on Performance

The wait event wait events in oracle will involve

Logs generally have no impact on performance.

Modifications to the oracle database will generate a large number of logs

This log needs to be transferred from logbuffer to redolog

IO is involved in redolog.

I/O may cause performance problems.

This is a log record Method

It also tells us that oracle logs will generate a large number of logs.

3) actual log generation process

The actual log generation process has some performance problems.

The teacher drew a lot of pictures in the course.

His diagram is casual, but it should be said that it has a meaningful meaning.

The student himself did not draw his picture because he only wanted to use words to describe the course.

The memory has a sharedpool,

Buffercache and logbuffer correspond to the dbf and redolog on the disk.

Oracle instances connect to many customer sessions

Each session is allocated with a small memory, which is called PGA.

Customer sessions are processed by server process in oracle. Each serverprocess generates a PGA

It is possible to modify a buffer in buffercache.

Server process reads the disk block into the buffer.

Reads data from the memory buffer to the cpu.

The buffer data is also modified.

All these accesses are implemented by serverprocess.

Of course, when dirty data blocks are written back, the DBWR process is used.

Serverprocess generates its own logs every time it modifies the buffer.

Write it to your PGA first

When it is written to a certain extent, it is written from PGA to logbuffer.

The same is true for the second serverprocess.

When it modifies a buffer, it also generates logs.

It will also be written from PGA to logbuffer

Actual log generation process

Serverprocess modifies the buffer to generate logs and writes them to its own PGA.

Under certain trigger conditions

Logs are written to the logbuffer.

Logbuffer is finally written to the redolog of the disk through the LGWR process.

This is the actual log generation process.