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

A common classic problem, but also a difficult industry problem, Oracle-based programs are slow and fast, many experienced DBAs face this problem is helpless, there is no optimal solution. If your database is experiencing various performance issues, you may wish to contact ZYT to see how we can resolve your database concerns.

---Zhongyi Science and Technology Tomcat (Chen Hongyi)

question is coming

A new fund client, first meeting, estimated that there will be some technical problems test, I arrived, the development team leader went straight to the topic, I have a problem here, the previous manufacturer provided a solution, we are difficult to accept, I do not know what you have a better way.

Note: The code below is the processed data.

Question statement:

The execution plan for the problem, this diagram is best zoomed in to take a closer look

If the data distribution in the status field is seriously skewed, for example, a school has a serious imbalance between boys and girls, with 99990 boys and 10 girls. If the data is distributed according to gender, the data is skewed. If the filtering condition is gender field, SQL will be faster and slower. This is exactly what customers are facing right now.

Tilting means "crooked."

Simply put, in the case of using binding variables, if the data distribution on the key conditions is severely skewed, when generating the execution plan, the binding variables peek into a very small value of data, cardinality is 1, and the execution speed will generally be very fast. When this execution plan is executed to a value with 3 million rows of data, the performance will be severely degraded. This is "crooked." The reason why it is "skewed" is that if you use bound variables, you avoid repeated analysis. Only the substitution value when generating the execution plan for the first time can be peeped, that is, there is only one execution plan. In this case, the data is unevenly distributed, with STATUS='INVALID', index appropriate, and STATUS=' VALID', full table appropriate. There's always one execution plan that's bad, and that's the classic time-fast-time-slow performance puzzle.

Cardinality is 100000*(10/100000) for all girls and 100000 *(99990/10000) for all boys.

The third filter condition is "T1". The difference between E-ROWS and A-ROWS for "STATUS"=:B1 is so large that CBO chooses to connect using nested loops. Then why did this phenomenon occur? The reason is simple. The binding variable B1 that generates the execution plan has a value of 'INVALID', and the data distribution on this column is very skewed. STATUS has a value of 'INVALID' for only 16 rows, and VALID data accounts for the vast majority of 1132K. Therefore, we generally recommend that developers do not use bound variables on such conditions.

Many experienced DBAs have their own ways of dealing with this kind of problem, but is there an optimal solution? Think about this problem, what do you do?

Think about it for yourself. Here's the thinking time...

....

....

...

...

..

..

.

.

solutions

So far so good, the problem becomes a regular problem, regular solution:

1. Because NDV (Number of distinct values) is very small, so, you can not bind variables, directly use literal values, batch programs, you can ignore the consumption of parsing.

2. Removing histograms allows CBO to assume that data is evenly distributed, which may sacrifice performance advantages for small data volumes.

3. SQL Profile binding execution plan. This approach is similar to the second option. In a different way.

4. ACS (Adaptive Cursor Sharing), this feature is generally not recommended to enable, if opened for this problem, can not be said to be a good solution. Not recommended for now.

At this time my heart is not happy, if it is this kind of conventional problem, by a database service company DBA will not understand, carefully asked the customer,"What language is your code written in?" Procedure or Java?』

Customer answers: "procedure."

Battle of Chrysanthemum

Hearing the customer's answer, my chrysanthemum tightened, the first solution did not work, PL/SQL using dynamic SQL, is a method that all developers can not accept, I was born, I do not accept.

The customer stared like a bean bag and said,"Yes, we just don't want to accept this plan." I've written this before, and I've had a lot of problems.』

He was secretly delighted. Fortunately, he did not recommend this plan directly. Chrysanthemum was temporarily saved.

"Can you accept small amounts of data a little slower than now, but large amounts of data will become faster?"』

"Can you accept small amounts of data a little slower than now, but large amounts of data will become faster?"』

Customer: "It's better not to do this, is there a better solution?" I also understand some SQL optimization principles, we sometimes bind values that don't exist.』

Chrysanthemum worry.

Sometimes, when I feel a little stressed, I have a flash of inspiration. At this time, a fifth plan pops into my mind:

SQL Plan Baseline

The reason why this time just jumped out is also a reason, really not a last resort, using SQL PLan Baseline my heart is also resistant, I really don't like to use sql plan baseline this thing, it seems to be born for EM design, use trouble, but for customer underwear also have to use ah...

SQL Plan Baseline Configuration

Capture Plan:

accept：

SQL_PLAN_gd8s9vjf5z9t89e2752cc This plan, so we love two plans, that is to say, each time SQL is executed, CBO will recover and decide which plan to use to execute the current SQL.

test verifies

Test statement:

Implementation plan:

The execution plan uses the SQL plan baseline SQL_PLAN_gd8s9vjf5z9t89e2752cc.

B1:='VALID';

Implementation plan:

CBO selects another baseline SQL_PLAN_gd8s9vjf5z9t8cea8bf8c

Tomcat said,

In GCS, there are a lot of complex, bug-related problem solving every day, as if the theme of life is just solving problems. After coming to Zhongyi Antu, the atmosphere of this team is the same as GCS, and they like to fight with some difficult problems. This is the best tradition of our DBA team. If your system has a problem, let's try "dead knock."

summary

This is a very simple case. What I want to say is that we, as DBAs, always combine the situation on site and use Oracle's powerful functions to provide customers with the most convenient and cost-effective solutions under the most granular problem analysis. We don't create technology, we're technology porters.

If your database is still facing this kind of headache, let us try to follow our public number and contact us to try it.

This article is reproduced in Zhongyi Antu

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.