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

First of all, I don't know how to Oracle. I think I can understand it. Haha, because I don't specifically talk about too detailed things in oracle. The content of this part is more general and can be used for reference.

I will mix these ideas in my platform, in short, after there are goods and materials, plus time and energy, it is like sunshine, air and water.

Part of ppt is a simple sharing I made at a conference on InfoQ, and today I reinterpreted it on the basis of the original ppt.

This is some of the problems in my eyes, some Oracle has been done, for a mature commercial software, although the functionality is satisfied, there are still some areas worthy of improvement, or they are not good enough.

This also reflects the several stages of dealing with the problem, some people have a headache to relieve the pain, some people can find the problem in advance, and some people can evade the problem earlier. If you go from this realm to a higher level, it will actually be more embarrassing, because the problem is strangled in the cradle without happening at all, it is difficult to reflect the value, and it will be more embarrassing.

There is a story about Bian Que, I did not verify the source, but can explain the problem.

In Bian Que's words, "my eldest brother is so skilled in medicine that he can prevent it before it happens. When a person is ill, he can tell at a glance, and then recuperate it with medicine, so people all over the world think that he will not cure the disease." he's not famous at all. My second brother's ability is to prevent other people from becoming seriously ill at the beginning of a disease. As soon as the patient began to catch a cold and cough, he cured him with medicine, so my second brother's fame was only in the countryside, and he was regarded as a doctor for minor ailments, just because he was the worst. Therefore, we must wait until this person is terminally ill and dying, and then give the medicine of tiger and wolf to bring the dead back to life. In this way, the whole world will think that I am a magic doctor. Come to think of it, if you treat a disease like my eldest brother, your vitality will not be hurt at all. When my second brother treats a disease, he will make up for it if his vitality is slightly damaged. If he treats a disease like me, his life will be saved, but his vitality will be greatly hurt. You say, who is the best doctor in our family?

Therefore, for the operation and maintenance staff, to say a word of selfishness, sometimes they even hope that some problems can occur so that they can be taken seriously by everyone, and some people may have a deep understanding of this. Originally, I hope that you can encounter more problems and solve more problems. The three values are definitely positive.

Having said so many questions, let's take a look at how Oracle optimization tool customization has to do with this.

Let's first take a look at Oracle's optimization tool. If you haven't heard of it, it doesn't matter. You can imagine a scenario where there is a database where the cpu load suddenly rises at a point in the wee hours of the morning, causing business blocking and a series of problems. If you are a DBA, how to think and deal with it.

Suppose you arrive at the office at 10:00 in the morning and the problem occurs at 2am. How should the problem be diagnosed and analyzed?

Because for the database, that fault state has passed, how to capture the problem of that point in time, this word will be often mentioned in the early years, that is, diagnosis.

We have to analyze this problem, and in the version of oracle9i, it would be a nightmare. I know that the early Ali DBA was pained by this kind of problem. If there is a problem at 2: 00 in the morning, how to solve it? That is to stay in front of the computer at 2 o'clock.

Some students said that this way is really too low. Oracle has statspack. It is true that there is this tool, but the problem is likely to be averaged. For example, the load of the database is 1% and 100% respectively within an hour. If the average is 50%, the problem will be averaged, then too many problems will be shielded. Blocking doesn't mean solving.

So, the most basic tool I'm talking about is AWR, which monitors the overall load of the database. Cycles can range from half an hour to an hour. This disadvantage is obvious, and it takes a while for you to find that it belongs to hindsight.

Based on this improvement, oracle has made a considerable improvement, that is, it is far more than one of MySQL's tools in this area, ASH.

ASH is simply called an artifact, it will collect information in the background, how often, 1 second. So we can diagnose the problem in seconds. Does this have a big impact on performance? I'm sure there is, but it's very low.

One drawback of ASH is that it cannot be associated with some detailed information, because it only grabs active sessions and some information is not available. So it cannot be said to be a panacea, but from another point of view, most of the problems caused are active conversations. So this coverage is basically sufficient.

When you're done, AWR,ASH, let's take a look at ADDM.

As we all know, the current version of Oracle is 12cMagne12cr1, which was released about 6 years ago. 12cr2 is recognized by DBA as a stable version, and we have been waiting for this version for about 6 years. This is an awkward situation for Oracle and many DBA to describe.

So Oracle must also be aware of this problem. I guess it is similar to the way SQL Server plays, that is, one version a year, which will weaken people's sensitivity to the version, so this year will launch 18c, that is, the autonomous database, and then there will be 19cMagne20c (this is real).

Some students say that Oracle is automatically optimized, is there nothing wrong with Oracle DBA, but it is not, too simple.

It is impossible for Oracle to launch something that does not exist at once. 12c with cdb is equivalent to moving the foundation and creating an autonomous database on a building that has been built for more than 30 years. Does it change a lot? in fact, if we take a closer look, we will find that these will become some key components of the autonomous database. And these Oracle already have some automation solutions.

So automatic diagnosis (ADDM) and SQL automatic optimization recommendation (SQL Tuning,SQL Monitor,SQL profile) are all done iteratively, and more dynamic processing methods are introduced to improve them.

In spite of this, these optimization tools are still semi-finished to me because they are still not very good to use. So I thought of a way to make some improvements, even if the original step needs to be manually operated 3 times, simplified to 2 times, I think it is also optimization.

What we need to pay attention to here is that when customizing, you need to make clear a yardstick, that is, if you solve a problem, do you solve more problems, or bring more problems? Do one thing, can achieve common ideas, I think you do Oracle or MySQL, or other databases, will be of great help.

Let's take a look at the steps to generate an AWR report, just like when you go to the hospital, first draw blood (here it can be called sampling), generate a blood test report (AWR report), and then the doctor to see which index is high and which is low, very similar.

At this point, I would like to mention one person, that is Zhang Xiaoming, he is really a doctor, and later transferred to oracle.

The steps to generate an awr report are as follows:

I listed five steps, which means that all five steps require manual intervention. You can imagine how painful it will be if you have 50 databases. It's like you are a head teacher and a child in a class. You can't chat one by one. We just need to grasp the overall state. Then help us solve more problems (database with performance failure).

I used to deal with performance problems, but I had to generate a lot of reports every day, but I couldn't stand it any more. To understand this optimized customization, you have to understand how it works, and I decided to improve it.

AWR script invocation relationship is as follows, in fact, understand the invocation relationship, we can have a targeted look at what can be improved.

Awrinput.sql is responsible for inputting the parameters, awrinpunm is the name of the parameters, and the question mark is the key. His implementation is the package dbms_workload_repository listed below.

So when we get here, let's take a look at the implementation principle of AWR. With this diagram, ADDM,AWR,ASH can get a general idea.

To put it simply, Oracle grabs some database changes from the memory level, and then collaborates through MMON background processes to write data to snapshots. The performance difference at snapshot level is AWR report, while ADDM analyzes snapshot-level databases, and ASH is slightly different.

Understand this, let's get to work, we have a clear direction of customization. Not eating a fat man in one breath, nor writing a masterpiece, as long as it can meet the demand.

Then the question arises, we actually have some things to do when we customize AWR, we quickly get what the meaning of AWR is and why we want to read this AWR report. It's much better to figure out this question than to customize it in your busy time.

Mainly because of this, DB time, I think it is one of the most important indicators to look at AWR, there is no one, without this reference, other databases are meaningless.

If you want to see a formal interpretation, you can take a look at this explanation. It doesn't matter if you don't understand. Skip it.

Once our direction is clear, customization is easy, and we can customize the input parameters, which can be generated in advance. For example, if you get such a list, you can clearly see which point in time the performance is high. I don't have to generate all the snapshot reports, so the state I reach later is to go to work to take a look at the db time value, and if it is high, generate an awr report, otherwise you don't have to pay too much attention and do what you have to do.

The script is actually very simple, clear pain points, the script is also very short, in fact, it will be converted into whether the DB time is high or not, and if the awr report is generated at a high level. Otherwise, it will not be generated.

The script can be downloaded here, https://github.com/jeanron100/dbm_lite

Also mention awr format, if a DBA front-end development capability is very strong, then the combat effectiveness is very amazing, awr format is a DBA development, can make the awr report into a layer of refinement.

The awr part actually costs 80% of the pen and ink. If you customize the ash,addm, it's natural to go to the driving school to get a driver's license. The first eight classes are all about feeling, and the next two lessons can be learned very quickly.

The customization of ash is a similar idea, which is simpler than awr. Enter two timestamps.

Customizing ADDM requires some basic knowledge of pl/sql, which invokes several processes in pl/sql to create optimization tasks and generate optimization reports. We can do it by dynamically binding a few parameters.

Supplement the schematic diagram of ASH. This is very helpful for you to understand ASH.

This data is divided into two parts, one is down, the other is in the cache. So if a database goes down, it is likely that the cache-level ASH will be lost because there is no disk down.

This kind of problem is more difficult to find. In this regard, we need to do more work on the details of monitoring.

SQL optimized part of the content is even more, said to be bitter tears, limited time, let's share it here.

Let me continue to add some attempts to customize SQL, which can be regarded as throwing a brick to attract jade.

If subdivided, it can be divided into these four categories, how to understand it? ADDM will analyze the potential SQL problems, which is based on the snapshot level.

He will only analyze and tell you that a SQL implementation took more time and may be problematic, but can't tell you exactly how to optimize it.

And SQL Tuning is an expert in this field, he will tell you which SQL has a potential problem, it's time to index, it's time to adjust statistics, and so on.

But at present, this SQL Tuning Advisor is more criticized, because according to our practice, in the vast majority of cases, his analysis is not very reliable. I may not like it when I say so.

Why, because some table designs are business-based, I also know that adding an index will help this SQL, but other SQL, from a design point of view, this tool can not drill down, if it can be done, then the position of DBA is in jeopardy.

So my preferred way of working is to see what oracle suggests if there is a performance problem and there is no idea for a SQL optimization.

Although I don't take its advice in most cases, there are times when it gives advice that I'm not aware of. So this is the advantage of tools, relying entirely on experience, there will still be omissions, many years ago strategy to now, has been integrated into the product, the life of the old DBA is actually not easy. In the old RBO era, SQL optimization was so sour that DBA said what it said.

Let's take a look at SQL profile. If you don't have the experience of SQL Profile in your optimization experience, you need to lose points. This is definitely not bluffing or self-supporting flag.

This kind of scenario is when the value of DBA is seriously overvalued, and it is usually hot when you encounter this kind of problem.

It's impossible to change the application code, even if you can change it, redeploy and restart the service, that's definitely not reliable, so don't change the code, don't change SQL, don't index, and optimize SQL, this operation will give you a lot of points in your career.

I have encountered many times in my career. In most cases, it is very cool, and there are two awkward times. The first time is that after a SQL optimization, the problem broke out a year later. The simple understanding is that this table is originally 100000 data, and the effect of the bound execution plan is very good, but the amount of data is 10 million a year later, then the original execution plan will not work. If you do some concurrency, do some load up. The problem will be magnified. So the correct posture for SQL profile is that it works as a temporary solution, but it is definitely not recommended as a permanent solution. If there are a large number of execution plan bundles in a database, it is like making more than n patches. Not to mention elegance, it looks very shabby.

Another embarrassing situation is excessive optimization. How to understand this? I have achieved efficient processing and feedback for the development and optimization of SQL. Basically, when he told me that the application was stuttered, I optimized it in less than 5 minutes. The false impression to the application students was that the job was very easy. The supervisor of the development came to me and said, let me give you permission, and they can optimize it themselves. The reality is that it is not yet mature.

But to say the least, this kind of thing will eventually be replaced, you do not improve, then oracle improvement, this is not 18c, this part is bound to be changed and optimized.

Give me a simple example.

If a SQL has a good execution plan and low consumption, but is inefficient in execution, there must be something wrong.

It's like a person's resume looks bright, but the ability to work is mediocre. Where is the bottleneck of the problem? how to drill down? from the perspective of hr, the recruitment work has been completed, but as far as the employment department is concerned, the impact is great. So to find this bottleneck, we need to drill down. Get the implementation plan of the whole SQL according to the actual implementation situation.

As shown in the figure above, you can clearly see that when doing an index scan, the estimate is more than 2000 records, but in fact, 4G records can be divided into several branches to consider, such as improper use of indexes, unreasonable statistical information, unreasonable query conditions, and so on. Check and drill down one by one, and you will soon be able to locate the problem.

I know that some experts do optimization in the opposite direction, that is, first look at the implementation plan, and then deduce what SQL is. If you can reach this level, you are running a race with the optimizer.

To get a html report for SQL, it's easy to call the package directly.

If you use SQL monitor well, your career happiness will be greatly improved. Of course, I spent a lot of time thinking about this thing, so you can take a look at the previous summary. Http://dbaplus.cn/news-10-705-1.html

SQL Monitor's report is divided into several levels, such as simplified text, slightly purple html, and rich active versions, just like Xiaomi phones, standard version, high-end version, premium version

When talking about the advantages of SQL Monitor, I would like to talk about his disadvantages.

The disadvantage is also obvious, so far, this part needs to be improved, although the content of the report is much richer, but if I talk about a performance problem a few days ago, I would like to see the SQL monitor report. I'm sorry I can't get this, but the only thing that can barely meet the requirements is an SQL report extracted from AWR.

So mysql does this better, with slow logs, and it's all written down. Oracle also has a similar implementation of slow logging. In oracle, some businesses may not be a problem for 3 seconds. So SQL Monitor defaults to 5 seconds, of course this can be adjusted.

I am a person who likes to toss around, so I am going to make some improvements. My improvement is more elegant than slow logs, that is, periodic scanning. If you find SQL performance problems, generate a SQL monitor report of the corresponding SQL, and if it exists repeatedly, generate a SQL monitor report. In fact, this detail can be optimized.

One of the goals achieved in the later stage is as follows. I have generated a large number of sql reports, and I am really close to going to work to drink tea and read the report.

When there is a problem, optimize the SQL, and then send it to the developer for confirmation, so I will fight the friendship with the developer next, because their problems are basically inextricable from my eyes.

One more word in sql profile's place.

If you want to optimize efficiently, sqlt needs to be able to use, which is used by the oracle coe department.

To take a brief look, I think this prospect of many years ago still applies today. Some students said that I do not have the same mass as Ali, optimization work is difficult to have motivation, there are no difficulties, you have to create difficulties for yourself, I am not talking about you stopping the database and creating failures.

But you do fine operation and maintenance, if you optimize the system of a key business to the extreme, when the performance is high, when the performance is low, what the reason is high, then you are already at the highest point.

So in this respect, I do not need to envy those high-end company brothers, at least in the volume of oracle, not too large scale, mainly to do key business, centralized management. The idea is different from that of MySQL management.

In addition, semi-automation, now too much automation, some key areas must be semi-automatic, not afraid of slow, afraid of mistakes. This place must be discreet.

I don't want my main library to be changed at will, even if its setting is unreasonable and weird, if I can correct it during maintenance time, but other than that, the main library is the main library.

For everyone's optimization, this is just the beginning. the current era poses great challenges to everyone, do not underestimate these challenges, do not be inexplicably excluded, and now the development and changes have far exceeded my expectations. so I can think of more challenges and many possible problems. I don't want the story of warm-boiled frogs to happen around us.

That's all for my sharing. Thank you.

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.