DBA's New Best Friend: Advanced SQL Tuning. Features of Oracle Database 11g. Peter Belknap, Sergey Koltakov, Jack Raitto. Oracle Database SQL Tuning Guide, 12c Release 2 () AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro. SQL Tuning for Smarties, Dummies and Everyone in Between. Novices SQL Tuning Solutions – New Feature Overview. • Problem .. Advanced SQL Tuning.

Author:BERNIE SCISCO
Language:English, Spanish, French
Country:Iraq
Genre:Environment
Pages:138
Published (Last):23.08.2016
ISBN:714-4-74004-619-3
Distribution:Free* [*Registration Required]
Uploaded by: VIRGINA

61541 downloads 184292 Views 15.70MB PDF Size Report


Advanced Sql Tuning Pdf

Jul 14, Oracle Database SQL Tuning Guide, 12c Release 1 () AMD Opteron logo are trademarks or registered trademarks of Advanced Micro. Sep 21, about optimization of SQL queries and PL/SQL scripts, however, you have to understand the basics of SQL. advanced optimization techniques. .. Note: You can automatically generate documentation (HTML, PDF, CHM. more efficient forms and the use of advanced techniques such as devoted to the nuances of Oracle SQL tuning, most notably the Kimberly Floss book.

Because each input is sorted, the merge join operator gets a row from each input and compares them. For example, for inner join operations, the rows are returned if they are equal. If they are not equal, whichever row has the lower value is discarded and another row is obtained from that input. This process repeats until all rows have been processed. Sometimes the index may not be used if queries are framed as: Queries involving! Avoid index merges especially if using rule based optimizer Minimize table lookups in queries Join Techniques Sort merge join does not require indexes. Each table is first sorted on the column values used to join the tables and then merges the two sorted result set into one. Nested loops join Nested loop involves an index on at least one of the table. In this case a full table scan is done on one table.

The understanding of how SQL plan execution physically works will become a strong and required foundation for any systematic SQL tuning and troubleshooting task.

We will look into the full range of techniques such as query rewriting, full hinting, stored outlines, SQL profiles, adjusting row count estimates and passing optimizer parameters into individual statements. We will start from identifying important prerequisites to any systematic SQL tuning, such as understanding the amount and shape of data accessed and what is the SQL statement supposed to do.

This allows you to understand what would be the ideal execution order and hierarchy which you can then achieve with the techniques covered next in this section.

10g-11g-SQL.Tuning.pdf

As no SQL tuning technique is appropriate in all situations, we will also look into how to pick the right technique for fixing the right problem and common SQL optimization pitfalls.

We will look into how to systematically detect inefficient data access even if nobody is complaining yet about performance. Auxiliary information in the form of statistics for objects with no statistics, and statistic adjustment factor for objects with stale statistics. This auxiliary information is stored in an object called a SQL Profile. Traditionally, users have corrected this problem by manually adding hints to the application code to guide the optimizer into making correct decisions.

The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans. The output of this type of analysis is a recommendation to accept the SQL Profile. A SQL Profile, once accepted, is stored persistently in the data dictionary. Note that the SQL Profile is specific to a particular query.

If accepted, the optimizer under normal mode uses the information in the SQL Profile in conjunction with regular database statistics when generating an execution plan. Effective indexing is a common tuning technique. The Automatic Tuning Optimizer also explores whether a new index can significantly enhance the performance of a query.

Advanced SQL Tuning Features of Oracle Database 11g

If such an index is identified, it recommends its creation. These could be syntactic, semantic, or design problems with the statement. The alternative suggested is similar, but not equivalent, to the original statement.

An application developer can then determine if the advice is applicable to their situation or not. These changes require a good understanding of the data properties and should be implemented only after careful consideration. The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile.

You can choose to accept the recommendation to complete the tuning of the SQL statements. Attributes can be customized for the maintenance of windows, including start and end time, frequency, and days of the week.

It made many performance issues much easier to fix, but was lacking an automation framework: users still had to run the advisor manually when performance problems happened and accept the advisors recommendations.

However, with the release of the Oracle 11g was introduced Automatic Query Optimizer.

Query optimization is the refining process in database administration and it helps to bring down speed of execution. This tool attempts to determine the most efficient way to execute the given query by considering the possible query plans.

This tool hence provides an edge over the other tools as it leads to faster processing of the query, helps in reduction of the cost per query, results in high performance of the system, reduces stress on the database thus leading to efficient usage of the database engine due to which lesser memory is consumed.

A fully self-tuning database capable of fixing or preventing every performance problem is a long way away to be sure. Thus, it can be said that the automatic query optimizer leads to better performance factors which were missing in the earlier versions of Oracle databases. This auxiliary information is stored in an object called a SQL Profile.

(PDF) S sql tuning for everyone | Anthony Mokwena - terney.info

Traditionally, users have corrected this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. The query optimizer under normal mode makes estimates about cardinality, selectivity, and cost that can sometimes be off by a significant amount resulting in poor execution plans.

The output of this type of analysis is a recommendation to accept the SQL Profile. A SQL Profile, once accepted, is stored persistently in the data dictionary. Note that the SQL Profile is specific to a particular query. If accepted, the optimizer under normal mode uses the information in the SQL Profile in conjunction with regular database statistics when generating an execution plan.

Effective indexing is a common tuning technique. The Automatic Tuning Optimizer also explores whether a new index can significantly enhance the performance of a query.

If such an index is identified, it recommends its creation. These could be syntactic, semantic, or design problems with the statement. The alternative suggested is similar, but not equivalent, to the original statement.

10g-11g-SQL.Tuning.pdf

An application developer can then determine if the advice is applicable to their situation or not. These changes require a good understanding of the data properties and should be implemented only after careful consideration. The output of the SQL Tuning Advisor is in the form of an advice or recommendations, along with a rationale for each recommendation and its expected benefit. The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile.

You can choose to accept the recommendation to complete the tuning of the SQL statements. Attributes can be customized for the maintenance of windows, including start and end time, frequency, and days of the week.

It made many performance issues much easier to fix, but was lacking an automation framework: users still had to run the advisor manually when performance problems happened and accept the advisors recommendations. However, with the release of the Oracle 11g was introduced Automatic Query Optimizer. Query optimization is the refining process in database administration and it helps to bring down speed of execution. This tool attempts to determine the most efficient way to execute the given query by considering the possible query plans.

This tool hence provides an edge over the other tools as it leads to faster processing of the query, helps in reduction of the cost per query, results in high performance of the system, reduces stress on the database thus leading to efficient usage of the database engine due to which lesser memory is consumed.

A fully self-tuning database capable of fixing or preventing every performance problem is a long way away to be sure. Thus, it can be said that the automatic query optimizer leads to better performance factors which were missing in the earlier versions of Oracle databases.

It is always a collective task that is rewarded.