PL/SQL optimization case for a hundredfold performance (R11 note 13th day)

I believe you are attracted by the words of performance, but I want to focus on the optimization of the idea, this is more important than the optimization skills, and the result, I do not want to say that it is a hundredfold ascension, "from the Black" "a bit."

There is a real idea to discuss with you, is a SQL statement if the original run 20 seconds, optimized to 1 seconds, the performance of the increase is 20 times times or increased by 95%. Of course also saw a statement, a SQL statement run 20 seconds each, run 100 times a day, optimize each run 1 seconds, run or 100 times, then the performance improvement is said to optimize the cumulative time of 100*20-100=1990 seconds?

OK, let's take a look at the optimization of PL/SQL, analyze some information in the early stage, can refer to the performance problem analysis caused by the alarm of Flash back zone (R11 note 11th day)

In general, the flash-back area at the database level is exploding and quickly approaching the alarm threshold.

One of the important factors is that an update operation takes an extremely long time, about 4 hours, and the resource consumption is huge.

Sql_fulltext
----------------------------------------------------------------------------------------------------
UPDATE cardinfo A SET a.max_level = NVL ((SELECT user_class from role_class_info B WHERE a.groupid =
b.group_id and B.cn_guid = A.role_guid), a.max_level) where drawed = ' Y ' and the data analysis found that this is a regular change that is triggered once a week on two. It is confirmed that this is a scheduler job run caused. The key is to call the stored procedure.

Well, the point is the stored procedure, of course the logic inside is still some complex. I'll simplify.

To explain briefly, database A's table Card_new stores some of the packet card data, and a record is inserted after the user activates the card information. While database B is a statistical database, the data is extracted from database a based on the rule table tasklist, and then in the statistical terminal based on the business requirements to do information change calibration, information is in the cardinfo this table. Rules table tasklist simply add, as if our mobile phone card number, such as 152xxxx001-152xxxx999 is a number segment, which is defined in this information, from the source library according to this rule extraction.

Sql> Select COUNT (*) from card_new where cardid between ' j23450010000 ' and ' j23500009999 ';
COUNT (*)
----------
5000

The information for the stored procedure is generally as follows

CREATE or replace PROCEDURE "Proc_update_cardinfo"
As
BEGIN
For cur in (SELECT * from tasklist where is_droped = ' N ') loop
MERGE into Cardinfo a
USING (SELECT *
from [email Protected]_link t
WHERE T.cardid >= Cur.t_start
and T.cardid <= Cur.t_end
) b
On (A.cardid = B.cardid)
When matched then
UPDATE
SET a.groupid = B.groupid,
A.role_guid = B.role_guid,
a.drawed = b.drawed,
A.max_level = B.max_level
When isn't matched then
Insert
(Cardid, GroupID, Role_guid, drawed, Max_level)
Values
(B.cardid, B.groupid, B.role_guid, b.drawed, b.max_level);
COMMIT;
End Loop;

/** do a mapping change for field 1 */
UPDATE Cardinfo A
SET A.used_jewel = (SELECT jewel_total
From Role_costs_info b
WHERE b.group_id = A.groupid
and b.cn_guid = A.role_guid)
WHERE drawed = ' Y ' and Cardid in (select Cardid from Tmp_cardinfo);
COMMIT;

/** make a mapping change to field 2 **/
UPDATE Cardinfo A
SET A.max_level = NVL ((SELECT user_class
From Role_class_info b
WHERE a.groupid = b.group_id
and B.cn_guid = A.role_guid),
A.max_level)
WHERE drawed = ' Y ' and Cardid in (select Cardid from Tmp_cardinfo);
COMMIT;
END;
/

The above table, in addition to the rules table tasklist is less than 10,000 database (similar number of data), the amount of other data are in billions of levels, so the optimization space is very large, optimization is difficult.

and development students simply understand the requirements, my preliminary conclusion is that the update part needs to be improved, because the update part of the change is the full table update, the impact surface is large, can not determine the incremental data, basically according to 1 weeks of frequency, the incremental data should be within the million. Looking at the parts of the following update, we found that the amount of data changed was tens, and the performance was very poor.

However, in the process of optimization, it feels like I am straying from the direction, because the target side in accordance with existing conditions and supplemental conditions to find that the amount of data always changed is too large, is tens, and the expectation is far from, simply speaking, according to the current conditions of the data is not incremental data, so my focus on the source of data extraction.

Because the source library configuration is good, the use of PCIE-SSD, query million-meter big table also pretty give force, I in the Reserve Library query the situation of the data.

Sql> SELECT count (t.cardid)
2 from Card_new T, tasklist cur
3 WHERE t.cardid >= Cur.t_start
4 and T.cardid <= cur.t_end;
COUNT (T.cardid)
---------------
599845975

Look at the results there are more than 500 million data, of course, we look closely, in fact, the statement itself is problematic.

In fact, the data extracted by logic is 200 million, that is, all the data in the Source Library table.

As a result, downstream data changes are directly affected, leading to the current situation.

So the bottleneck is obvious, in two places ,
1. When extracting the performance pressure on the online business, it is a full-scale extraction
2. Update is a full-volume update, field matching data range is too large

The idea of improvement is relatively simple.

1. Data that is explicitly incremental

2. Incremental data changes using temporary tables or tagging incremental data in Cardinfo

3. Perform complete data testing to ensure that performance improvements are true and effective.

Let's say it one by one.

1. Incremental data, I looked at the field of the source table, which has a time-based field, and the name of the field should be the activation time of the gift card. and development colleagues confirmed that the place was clear.

We follow the idea that the incremental data is about 70,000.

Sql> Select COUNT (*) from card_new where drawdate>sysdate-10;
COUNT (*)
----------
78174

As a result, the nature of the problem is captured, and later updates can limit the conditions to avoid full-scale updates. I created a temporary table to handle. Gets the incremental data extracted from the source library.

2. Incremental Data Change optimization

The original update is such a logic,

UPDATE Cardinfo A
SET A.used_jewel = (SELECT jewel_total
From Role_costs_info b
WHERE b.group_id = A.groupid
and b.cn_guid = A.role_guid)
WHERE drawed = ' Y ';

After the improvement, the condition is limited, the following form

UPDATE Cardinfo A
SET A.used_jewel = (SELECT jewel_total
From Role_costs_info b
WHERE b.group_id = A.groupid
and b.cn_guid = A.role_guid)
WHERE drawed = ' Y ' and Cardid in (select Cardid from Tmp_cardinfo);

Of course, there are some small details of the improvement, again not to repeat the first.

3. Performance Testing

Next is the performance test, how to real simulation test this problem, 11g to make full use of sapshot standby benefits.

How to switch to snapshot standby in a standby library
Set the current standby to disable in Dgbroker
Then use Sqlplus in the standby operation:

Recover managed standby database cancel; --Cancel Log application
ALTER DATABASE convert to snapshot standby; --Switch to Snapshot Standby
ALTER DATABASE open; --Open the database after switching
Select Database_role,open_mode from V$database; --Check whether the change takes effect

Then I started the performance test, I pointed the data source to the repository corresponding to the source library, so there is no direct pressure on the line. Modify the stored procedure in the target database and run the test.

sql> exec proc_update_cardinfo1;
PL/SQL procedure successfully completed.
elapsed:00:01:04.38

Stored procedures that were executed for at least 4 hours are now available in 1 minutes.

Complete the test and start the recovery repository for physical Standby:
Sqlplus Library: Shutdown immediate
Startup Mount
ALTER DATABASE convert to physical standby; --Switch database to physical standby
Shutdown immediate--modified database to Nomount, reboot
Startup Mount
Select Database_role,open_mode from V$database;
ALTER DATABASE open;
Then use DG Broker in the main library to enable the original repository.

Summary

The whole process came down, let me to the problem of cognition, from the original flash back area of the alarm gradually discovered, extended to PL/SQL storage process implementation, of course, this part still took some time to get familiar with the next business, in order to better meet the optimization needs, optimization in particular need to firmly grasp the performance bottleneck, grasp the essence, Then break it one by one. The Snapshot standby is a good addition to the test of performance issues. The evaluation run time will be more realistic and effective.

The last performance increase from 4 hours to 1 minutes.

--------------------------

A week later, I followed the issue again, confirming that it had been repaired. The utilization rate before the flashback was greatly reduced.

The actual SQL execution is better than expected, the original UPDATE statement takes a few hours, and the current execution takes only 1 seconds.

PL/SQL optimization case for a hundredfold performance (R11 note 13th day)