Core technical sentiment of Oracle ACS senior consultant Luo minoluo: Niu! 11g Automatic Optimization and

Author :? SHOUG Member-Luo Min, ORACLEACS Senior Consultant, remembers that he encountered a very difficult SQL statement on a 10g Data Warehouse project many years ago, at that time, even if I collected statistics and even added hints to statements, the execution plans produced by Oracle were not satisfactory. Finally, I had to seek the old man through SR

Author :? Mr. Luo Min, oracle acs senior consultant from SHOUG many years ago, remembered that he had encountered a very difficult SQL statement on a 10g Data Warehouse project many years ago, at that time, even if I collected statistics and even added hints to statements, the execution plans produced by Oracle were not satisfactory. Finally, I had to seek the old man through SR

Author :?

SHOUG Member-oracle acs senior consultant Luo Min

1. Past Events many years ago

I remember that I encountered a very difficult SQL statement on a 10g Data Warehouse project many years ago. Even if I collected statistics and even added hints to the statements, the execution plans generated by Oracle are not satisfactory. Finally, I had to seek advice from a foreigner through SR. He suggested that I use a new 10g new technology, that is, let me generate SQL Profile information for the statement, and then execute the statement. Everything is OK!

I also remember when I asked a foreigner if I had encountered very complicated and difficult SQL statements and threw them to Oracle, especially when I generated an SQL Profile to assist the optimizer? The devil answered with Pride: "That's right !"

2. I once again lamented the SQL Profile!

Several years later, in 2014, when optimizing a SQL statement with nearly 200 rows, it was found that the statement execution plan had basically no obvious problems, for example, there was no full table scan, there is no full index scan, and even the Cost of the statement is very low (of course, the Cost is not very accurate ). However, the statement execution efficiency is not high. When it reaches 30 seconds, the resource consumption is also very high. For example, the Buffer Gets reaches 1,246,155 times. Of course, the customer is not satisfied. How can we further optimize it?

When the mountains are exhausted, I think of the past many years ago and the amazing SQL Profile technology. As a result, after searching for the latest 11g document "Automatic SQL Tuning and SQL Profiles (Doc ID 271196.1)", I practiced it in a simple way. What is the effect? The following is the comparison before and after optimization:

This is an indicator before optimization:

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (MS)	30,273	30,272.96	17.76
CPU Time (MS)	29,968	29,968.19	17.79
Executions	1
Buffer Gets	1,246,155	1,246,155.00	14.68
Disk Reads	5,437	5,437.00	0.80

This is the optimized indicator:

Stat Name	Statement Total	Per Execution	% Snap Total
Elapsed Time (MS)	4,653	4,652.71	3.00
CPU Time (MS)	4,470	4,470.23	2.90
Executions	1
Buffer Gets	303,480	303,480.00	2.32
Disk Reads	9,740	9,740.00	1.39

It can be seen that the statement response speed drops from 30 seconds to more than 4 seconds, and the Buffer Gets drops from 1,246,155 to 303,480! I have not made any changes to the statement, nor created a new index. The execution plan is better, and the actual effect is even better! SQL Profile!

3. Implementation Details

The following is a detailed process of comprehensive application of the 11g automatic optimization tool and SQL Profile technology:

• Generate an automatic optimization task

Declare

My_task_name VARCHAR2 (30 );

My_sqltext CLOB;

Begin

My_sqltext: =' <欲调优的sql语句文本> ';

My_task_name: = DBMS_SQLTUNE.CREATE_TUNING_TASK (

SQL _text => my_sqltext,

User_name =>' <用户名> ',

Scope => 'computing ',

Time_limit => 60,

Task_name => 'test1 ′,

Description => 'Task to tune a query on a specified table ');

End;

/

• Execute an automatic optimization task

Begin

DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name => 'test1 ′);

End;

/

• Query the automatic optimization report generated by Oracle

Sets long 10000

Set long chunksize 1000

Set linesize 100

Set heading off

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK ('test1') from DUAL;

Set heading on

• SQL Profile generated by the Oracle automatic optimization task

DECLARE

My_sqlprofile_name VARCHAR2 (30 );

Begin

My_sqlprofile_name: = DBMS_SQLTUNE.ACCEPT_ SQL _PROFILE (

Task_name => 'test1 ′,

Name => 'test1 ′);

End;

/

OK. You can run the statement that requires optimization and observe the optimization effect. The result is the exciting group of data shown above, and the SQL Profile is clearly displayed in the new execution plan generated by Oracle:

"SQL profile" test1 "used for this statement"

Now, you only need to fill in the statements and usernames you need to optimize in the above script.

4. SQL ProfileWhat is it?

The SQL Profile information is stored in the Oracle Data Dictionary. Apart from the limited information displayed in the dba_ SQL _profiles view, it is indeed invisible and obscure. What is SQL Profile? In fact, SQL Profiling can be analogous to the relationship between tables and statistical information. SQL Profile is the statistical information of an SQL statement. For example, when we encounter a complex SQL statement that consumes a lot of resources, Oracle can use some sampled data or execute a segment of this statement, analyze the statement execution history to assess whether the overall execution plan is optimized. The auxiliary information is the SQL Profile information, which is stored in the data dictionary.

The working principle of SQL Profiling is as follows:

That is, the upper part displays the SQL Tuning Advisor, the 11g automatic optimization tool. After generating the SQL Profile information for an SQL statement, the lower part of SQL Tuning Advisor. When Oracle officially needs to execute this SQL statement, the optimizer not only uses the statistical information of the object accessed by the statement, but also uses the SQL Profile information to generate a better overall execution plan.

5. When should I use the automatic optimization tool and SQL Profile?

Oracle's 11g Automatic Tuning tool and SQL Profile are indeed as magical as Pandora box. Continuing with the above optimization case, although the statement was optimized by Oracle, I carefully compared the execution plan of more than 50 steps before and after the optimization, it's amazing to know which steps have been optimized by Oracle so well!

Does it rely on automatic optimization tools and SQL Profile to optimize complex statements? Slow down. First of all, although there are many application performance problems, the main problems are some traditional and fundamental problems. For example, there is a lack of suitable indexes, the design of compound indexes is unreasonable, especially the incorrect index order, resulting in low indexing efficiency, and the incorrect use of functions in SQL statements leads to the unavailability of indexes. To address these problems, the rational use of 20% of basic technologies, especially the indexing technology, can actually solve 80% of the problems. These technologies are also basic skills and basic design and development specifications for DBAs and application developers. Over-reliance on automated tools will degrade our basic skills. Second, the automatic tuning tool and SQL Profile are not a panacea for all kinds of diseases. How can Oracle automatic tools better understand your data model and data distribution and provide more accurate optimization policies? Third, Oracle automatic tools are not easy to use, and require the close cooperation of DBAs and developers. For most basic problems, experienced DBAs and developers can see problems at a glance, why should I use a cool knife to kill chickens?

So when will I use the automatic optimization tool and SQL Profile for optimization? My experience: when traditional and manual analysis methods are difficult to work with complex SQL statements, we suggest you try these new technologies.

In any case, the Oracle 11g Automatic Tuning tool and SQL Profile are good! Do not accept it!

Related posts:

1. Utilize SQL Tuning Advisor from Script
2. Oracle Acs senior consultant Luo minoluo core technical sentiment: Table sharding or partition?
3. How can I verify the performance of SQL PROFILE?
4. [New Features of Oracle Database 12c] automatic job of SYS_AUTO_SPM_EVOLVE_TASK
5. SQL Performance Analyzer Summary of common SPA scripts
6. More About SYS_AUTO_ SQL _TUNING_TASK
7. Know about Oracle High Water Mark
8. 11g new dynamic performance view V $ SQL _MONITOR, V $ SQL _PLAN_MONITOR

Original article address: Oracle Acs senior consultant Luo minoluo core technical sentiment: Niu! 11g Automatic Optimization and SQL Profile, thanks to the original author for sharing.