Shulou(Shulou.com)05/31 Report--

This article mainly explains "how is SQL Profile". Interested friends might as well take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to SQL Profile!

What is SQL Profile?

SQL Profile is a technology that I like very much, through which you can bind any needed hint for SQL without modifying the SQL statement, which is separate from the SQL code, and compared with Baseline and outline, the operation is simple, if you are proficient in using it, you can modify the execution plan of a query statement in a very short time. I have used SQL Profile in many occasions to solve the database performance problem. SQL Profile is a function of ORACLE 10G. SQL Profile is described in Oracle documents as part of SQL Tuning Advisor and can only be used through SQL Tuning Advisor. Generally, you can view the optimization suggestions given by SQL Tuning Advisor after running JOB,JOB running SQL Tuning Advisor. These suggestions may include letting DBA adopt SQL Profile (a collection of hint). As a new feature provided after ORACLE 10G, DBA can hand over SQL tuning to SQL Tuning Advisor. Normally, after a SQL statement is given to the SQL optimizer, the optimizer needs to give the parsing result in a very short time, but SQL Tuning Advisor is different, it may take a long time to produce an efficient execution plan. Furthermore, it uses very time-consuming techniques such as Wath-If analysis and enhanced use of dynamic sampling techniques to verify optimizer estimates. The task of SQL Tuning Advisor is to analyze SQL statements and recommend ways to improve statement performance, including collecting missing or outdated object statistics, creating new indexes, changing SQL statements, or adopting SQL Profile. Officially, SQL Profile can only be used through SQL Tuning Advisor, but the later chapters of this section will use manual SQL Profile to tell readers how to use and create SQL Profile more quickly, but manual SQL Profile is not supported by ORACLE technical support.

Simply put, a SQL Profile is an object that contains a series of hint that contain additional information that can help the optimizer find an efficient execution plan for a particular SQL statement. These hint contain execution environment, object statistics, and corrections to the evaluation of the query optimizer. For example, the widely circulated OPT_ESTIMATE is the hint introduced by SQL Profile. Its main function is to set object statistics, enlarge or reduce the evaluation cardinality of the optimizer, and correct the cardinality of table joins. For example, the true return cardinality of select * from a where status='Inactive', is 10000, but due to the outdated statistics. Causes the optimizer to think that only 100 is returned, so this information is corrected by OPT_ESTIMATE, for example, by telling the optimizer through OPT_ESTIMATE (@ "SEL$1", TABLE, "A" @ "SEL$1", SCALE_ROWS=100) that the cardinality returned after predicate filtering for table An is: the optimizer's evaluation cardinality is magnified by 100x, that is, the optimizer's evaluation cardinality is 100, multiplied by the magnification factor 100. The final cardinality is 10000 (note that corrective information for hint such as OPT_ESTIMATE is not stored and updated in the statistics of objects such as tables, indexes, and so on). From this we can also see that SQL Profile does not really lock the execution plan, but just tells the optimizer some more real information, so that the optimizer can get a more reasonable execution plan based on the real information. Therefore, even if a SQL uses SQL Profile, the optimizer has a lot of flexibility in choosing the execution plan, and these correction factors may no longer be accurate as the data changes and time goes by, so it may appear in some SQL that uses SQL Profile. These SQL Profile work well at first, but some problems will arise soon. However, like outline and Baseline, SQL Profile works based on storing some hint. Although SQL Profile does not use common hint such as index and full by default, these hint can still play a role in SQL Profile.

SQL Profile is turned on by default in 10G and 11G, and you can turn off SQL Profile by setting the parameter SQLTUNE_CATEGORY to false. Each SQL Profile is placed in a specific category, and you can specify the value of the category when you create the SQL Profile, and if not, it will be placed in the category where the SQLTUNE_CATEGORY is defualt. If the value of SQLTUNE_CATEGORY is set to a value other than default, only if the value of category of SQL Profile is set to the parameter SQLTUNE_CATEGORY will take effect.

N Note:SQL Profile can add any hint to the SQL statement, for example: bind_aware this hint does not work through SQL Baseline, but it can work through SQL Profile. Because SQL Profile, as a mechanism to modify the SQL execution plan based on hint, is rather foolish, it only applies hint to the SQL of a specific signature and will not do any other verification. SQL Baseline not only applies hint, but also needs to calculate and verify the plan_hash_ value, so hint such as bind_aware does not work on SQL Baseline, because this hint will lead to instability of the execution plan. While Baseline is created with a specific plan_hash_value, it cannot be linked to this unstable hint.

N SQL Profile can be used in DataGuard, that is, after the main library creates SQL Profile, the standby library can automatically use the SQL Profile created on the main library, but Baseline cannot be used in DataGuard.

At this point, I believe that everyone has a deeper understanding of "how is SQL Profile", might as well come to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.