Oracle database application Performance optimization experience (training handout)

This is my colleague to the company to do internal training ppt of the handout, for everyone to share. This is the training outline, PPT in Find place to upload, and so found will send the link here.

Put it on the csdn for the time being, earn points download points: download.csdn.net/download/kingstarer/10655069

Cut a few ppt page you can preview it first

Hello, everyone, welcome to participate in today's technology exchange, and today we are going to share with you is the Oracle database application optimization related experience. This is the outline.

What we are going to talk about today is divided into five parts. 1 to 3 is what we're talking about today, and then four and five are possible, a little faster.

Let me give you a brief introduction to each part of the content.

First part of the first section will introduce you to the Oracle database architecture and some internal implementation details. This is in order to introduce the database optimization knowledge to do some foreshadowing.

Next, I'd like to show you some experience in writing high-performance SQL.

Then in the third part, we will share the knowledge of the implementation plan of the database. When we find that SQL is running slowly, we can look at the execution plan and find out the unreasonable links in it to optimize.

Part IV is an introduction to some of the optimizations for database wait events and AWR reports. The AWR report is equivalent to a physical examination report of the database, and waiting events are some medical indicators. We do the whole database application optimization often use it.

Part Five is recommended for some study materials.

Let's start with the first part of the introduction. This is the schema diagram for an Oracle database.

Here user process is the meaning of client processes, that is, we usually write their own programs, or Sqlplus sqldevelor these tools.

We usually say that the database is a relatively vague concept, in fact it is comprised of two parts. Part of the database file, including data files, log files, and so on. In this part, this is the professional terminology database. The other part is the database instance, a set of processes that run around the real database.

Here to illustrate, is not the older, I copied from the Internet 11g architecture diagram. Now that the database has gone to 18c, there may be some changes. In addition, there are some database processes that are not too important in this diagram, which are not sent here. Well, we still look at this picture, this figure is estimated to be oracle8i when the figure, although more than the picture is missing some of the content, but I think the picture is better, so the later will be around this picture to introduce.

As you can see from this diagram, the Oracle database is divided into two main parts, a database instance, which consists of a database background process and associated shared memory. The other part, is the database this piece, mainly is the data base file. We often say that the database is the two pieces of content collectively.

This picture is still very important, we look at a few more eyes. Next we will make a detailed introduction to the nouns appearing in this picture.

Next, let's introduce some important components of the database, one by one.

The first thing to introduce is the database instance this block. This piece is made up of two big parts, one of which is the huge chunk of shared memory, which we call the SGA. The other is the Oracle background process.

The SGA is a very important concept. It is primarily a cache and buffer for Oracle Library table data and redo logs. On the next page we will detail the details.

This page focuses on the Oracle database background process. These background processes are mainly responsible for database monitoring, data and log reading and writing, failure recovery and other functions.

Here we list a few more common process introductions. Pmon and Smon, these two processes are used to recover from user process failures and system failures, respectively. Our development generally does not need to care about how it works, and DBAs need to be concerned.

DBWN This process is responsible for writing the data inside the SGA back to disk. We just said that the SGA caches Oracle database table data. Some of the data in the user has been modified by SQL, need to write back to disk. Because database write disk is time-consuming, data modification is always first modified in the SGA, reaching a certain amount or the database is idle when the DBWN is written back to the disk uniformly.

LGWR is also a relatively important process. It is responsible for writing the redo log from memory to disk for database recovery. A transaction is completed only if the redo log is written to disk. LGWR Primary operating point is when the user initiates a commit command, or when the log buffer is more than 1M long.

The Redo log size is limited and will be transferred to the archive log through the archive log process when it is full.

CHKP is the process of coordinating LGWR and DBWN. Detailed coordination principle everyone is interested in the Internet to find, I mainly want to introduce their coordination between the need to use a concept, called the system change number, that is, this SCN. This system change number can be simply understood as the number of the Oracle transaction, which grows as the transaction commits and changes in time. Each Oracle data block records the system change number that modifies the data block. When a database is queried, it is possible to determine whether a record has been recently modified based on the system change number.

The composition of the SGA is described in detail below.

Database block buffer cache, which is primarily the data of the cached database. including read and write data, will be placed in this buffer, and then write back to disk. General trading System This buffer has a high hit rate of over 90%.

The Library cache is a cache, but it caches not data, but SQL and execution plans. Before executing SQL, the Oracle database generates an execution plan for SQL, which is the detailed execution step of SQL. This step generation is relatively expensive for database resources. So Oracle will save the generated execution plan, and if you run into exactly the same SQL, use the ready-made execution plan without rebuilding it. So we usually write SQL often said to use bound variables, one of the main reasons is to reduce the database execution plan generation time.

The redo log mentioned a little earlier that it was a record of database changes. For example, we issue an UPDATE statement that changes a field from 0 to a 1,redo log to record the data after the field becomes 1. Instead of writing back to the disk immediately after modifying a record, the database writes to the cache and registers the redo log. When a transaction commits, the record is not lost due to power loss as long as the redo log is output to disk. Because in general, the random IO when writing data, the Write log is sequential io, the sequential IO speed is much faster than the random IO.

The undo data corresponds to the redo log. It is an undo table space. Undo is undo, and Redo is the opposite process. So we've changed the field from 0 to 1 o'clock, and here we're going to record that the field is still 0 before the change.

Note that the data for the undo tablespace is stored in the database file, so the database is cached in the DB buffer cache when the rollback segment is manipulated.

The undo data has a very useful function, which is consistent reading. Oracle typically does not lock the table when executing a SELECT statement, but it also guarantees that the returned data is definitely the data at the start point of the query, even though the data has been modified during the query. The implementation of this feature is the need to rollback the data through the query, to obtain the state of the data before the modification.

This is the principle of checkpoint, the checkpoint is used for database power-down recovery. Everyone is interested in a closer look.

After the database instance is introduced, let's start by introducing this block. Database is a generic term for a collection of databases, and a database can correspond to multiple DB instances. That's what we always call RAC. Multiple database servers operate the same database file.

The most important files in a database are data files and online log files. Just now we have introduced the redo log cache, which is written to disk when the user commits the transaction, which is the online log.

This page describes the knowledge of the Oracle database service process. That is, the process of processing SQL requests from client threads that we normally write.

A database can have a number of service processes that share an SGA. But each process has its own piece of private memory space, which we call the PGA. The PGA default is relatively small, if we need to make a large table connection, may not be enough memory, then we can apply to increase the PGA. Do the bulk task of the students can pay attention to.

But there are two parts of SB-ah, that is this piece of memory, then? He's got another part of this. Some of these related background processes are more important, and this is a huge chunk of shared memory, then? Its main function is to make a enrichment so that these data to make us happy these will be in the store, and then this data is these background processes, later process, including these several, this is each process specific layout of things monitoring this is not too important, and this this fart? This is the user process monitoring this is mainly used to hang the words there is no normal disconnection, he will help you to put these original important, and then this thing, let this this function of the word, as we have said, there are several pieces of the database to write back some data, and then what. This night. I just. Drive into the pond. The log write process is followed by our database on a regular basis. Write to the log file. This is converse. Archive archive process, this is the application of this is actually more important, is to be concerned about the two processes, not the relevant knowledge of the process. Over here. Here are some of the principles of the Oracle database. This is the system to change the number, on this matter, log process him. He sometimes. Next we introduce this piece of database related knowledge.

Let's go through the introduction of an UPDATE statement to chuanjiang the knowledge we just introduced. First, the client initiates a SQL execution request, and the database looks inside the buffer pool to see if the SQL execution plan exists. If there is a ready-made execution plan, this process is called soft parsing. If the execution plan does not exist, it needs to be rebuilt, and this process is called hard parsing. The database then parses the data that SQL needs to access to see if it is in the block cache. If you are using cached data directly, if not, you need to read it from the database file and put it into memory.

The database then modifies the data block in memory and registers the redo log to the log buffer at the same time.

When the database commits, the contents of the redo log are brushed into the disk. The contents of the data buffer are written into the disk by dbwn over time.

The main architecture about the database is introduced here. These pages are a detailed knowledge of the database block, because of the time relationship is not spoken. It is important to know that Oracle hard disk operations are in the database block, not in a single row of records or a field unit. A database block will have n rows of records.

Below to enter the key communication content, about the database application optimization knowledge.

First of all, we should pay attention to the type of the system in the database optimization, the different system optimization target is not the same. In general, we divide the system into two main categories: OLTP and OLAP, also called DSS.

OLTP is the full name of an online trading system, such as our receiving online system. This type of system is characterized by a very frequent transaction, but with very little data involved in each transaction. The database response time requirements are relatively high, and typically require a few seconds or even a few milliseconds to return.

OLAP full name is an online analysis system, we usually do the batch system belongs to this category. This type of system features: trading is relatively small, but each transaction involves more data volume. Database access time requires a relatively relaxed point.

The common resource bottlenecks in OLTP systems are CPU and memory, while OLTP system FAQs are on Io.

This table describes some of the experiences of two types of system development.

OLTP because of the high frequency of SQL operations, it is generally required to use bound variables to reduce the consumption of database generation execution plans. In addition, OLTP typically minimizes the number of times a database disk is accessed and tries to maximize memory hit rates.

On-line analysis system is generally read and write the larger system, in order to facilitate data access will do a lot of redundancy. And, for the database to be able to get the best execution plan, the use of binding variables will be less.

This is the general two types of systems in the database when the parameter configuration differences, but also to follow the system business characteristics and configuration.

The general principles of system optimization are described in the previous section, and some optimization details are described here.

The first is about indexing.

The index tells the database what it is, not what it has. So we usually use not equal to the query condition is not indexed.

In addition, we also need to be aware of the data types of indexed columns. In this case, the Tel phone number column uses a string type, but the query is made with tel= numbers. Causes the database index to be invalid. And, like this, we seem to be mathematically equivalent, but there is a lot of difference in efficiency due to index problems.

And this is a common misconception. Not using indexes is not necessarily faster than not using indexes. According to our usual experience, less than 100 records in the database, or the index column selection is not high, for example, the gender field is only male or female, using the index is slower. Because the database uses an index query, you need to do several disk operations in the index, and then find the record location and then return to the table data access record content.

This experience is also more important, if we have a database inside the use of foreign keys to ensure data consistency, it should be noted that the foreign key is built on the column index. Because Oracle modifies the parent table record, it is time-consuming not to build indexes until the child table is checked for changes that violate foreign key constraints.

This page is about Oracle's features, which are unique to Oracle. With these features, we can greatly optimize our programs in certain specific scenarios.

This article is about Oracle's rownum and ROWID usage. We usually write scripts such as this scenario: if the library table has records, then perform a operation, otherwise perform B operation. It is natural for some students to use SELECT COUNT (*) to determine if a library table has a record. In fact, we can add a limit of where rownum < 2, which tells the database to end the query as long as it finds a qualifying record, without having to count all the data.

ROWID concept in front of the PPT page has, using ROWID access records is the fastest way for Oracle to access data, because ROWID explicitly express the data on the disk which file, which data block, which line record. Peso access to fast, using the index to access the need to first identify rowid and then according to rowID to check the corresponding data.

Also have this technique, believe that do batch of schoolmate also often use. is to use some special methods to keep the database from logging redo logs. As we have described earlier, you need to log redo logs for database operations to recover data when the database is abnormal, but if we determine that the data is not too important and the database exception hangs and does not need to be recovered, you can use these tips to reduce the redo log to speed up.

There are also some other fragmentation techniques, which are described here.

This is well known, using bound variables, you can reuse the SQL execution plan, reduce the database CPU consumption.

This technique should also be known, that is, when deleting the entire table data, consider using truncate as much as possible. Because truncate is directly modifying the database dictionary, the Library table occupies the data space state to unused. This operation is fast. However, it is important to note that truncate is not like delete and can be rolled back after being deleted by mistake. The transaction is automatically committed after the truncate is executed, and the query cannot be flashed back.

The next big chapter wants to talk to you about some of the knowledge about database execution plans. Some of the optimization experiences described in the previous chapter are a general approach to optimization: improving the efficiency of SQL operations by adjusting the execution plan.

First, describe the definition of the execution plan. The execution plan is the step plan for the database to execute the SQL statement, and Oracle will disassemble the complex SQL into simple steps and execute it sequentially before executing the SQL.

There can be many different execution plans for the same SQL, and the efficiency of these execution plans can vary considerably.

How do we look at the SQL execution plan, here are a few methods:

One method is to execute set autotrace on in Sqlplus and then execute SQL, and Sqlplus will print the SQL execution plan and some statistics after execution.

Also, there is no need to execute SQL, just execute the explain command, then you can use the SQL statement to find out its execution plan.

There is also a method that I use more often. You can also display an execution plan by running SQL in the Execution Plan window of Plsql Develor. Just like this picture here.

Knowing how to look at the execution plan, we still have to know how to analyze it.

We look at this simple SQL execution plan, it can be seen that the execution plan is a tree structure, there are a number of steps, each step may have sub-nodes or sibling nodes.

We look at the implementation of the plan need to look from the top down, met with sub-nodes have sub-nodes, if there are no sub-nodes, then the same level of the sibling node in the comparison of one, read and look at another.

Or we can use tools to help us determine the order in which execution plans are executed. This is plsql. Look at the Execution Plan window, there is a set of arrows, click it will follow the order of execution to select the execution steps.

Let's take a specific case to explain:

This SQL function is to find the work number less than 103 from the employee table, the associated job table to take out job information, related department table to take out department information.

It corresponds to the execution plan is this

We look down from the top, 0 1 2 have sub-nodes, so look at their sub-nodes first. Always see 3, 3 No child nodes, so do 3 first. Extract the data from the employee table.

3 after execution needs to execute its sibling node 4, 4 has a sub-node, so first execute its sub-node 5, with job_id to index JOB_ID_PK inside to find data rowid. 5 after execution, according to the data found rowID back to the job table to find job information, that is, 4 of this child node operation.

3 and 4 will go back to 2 after the execution, and connect the data together. Then execute 6 and 7, take the data from the Department table, then execute 1 and then merge the department data with the previous employee and position table connection results, and finally return to 0, back to the client.

So the order of execution of this execution plan is 3 5 4 2 7 6 1 0

Like this is a more complicated implementation plan, everyone is interested to go back to see its execution order. The answer is in this column.

As we said earlier, there are many different execution plans for the same SQL. How does the database determine which execution plan to use? This involves the concept of optimizer, which is to be introduced in this chapter.

The optimizer is a core component of the database and is responsible for converting SQL to the optimal execution plan based on a variety of judging criteria.

The optimizer we use now is the CBO, which is based on the cost calculation optimizer. It calculates the cost of each SQL execution step and selects the execution plan with the lowest total cost.