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.

Advanced Sql Tuning Pdf

Language:English, Japanese, French
Country:Czech Republic
Genre:Science & Research
Published (Last):26.11.2015
ePub File Size:25.80 MB
PDF File Size:16.47 MB
Distribution:Free* [*Registration Required]
Uploaded by: RANDY

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.

The purpose for tuning is to reduce resource consumption or to reduce the elapsed time for an operation to complete. Either way, the goal is to improve the effective use of a particular resource. If we use services with our database, then AWR tracks metrics at the service level. The Automatic Database Diagnostic Monitor ADDM on the other hand is responsible for analyzing the AWR data on a regular basis, then locates the root causes of performance problems, provides recommendations for correcting any problems and identifies non-problem areas of the system.

However, it is important to realize that tuning is an iterative process, and fixing one problem can cause the bottleneck to shift to another part of the system.

Even with the benefit of ADDM analysis, it can take multiple tuning cycles to reach acceptable system performance. ADDM benefits apply beyond production systems; on development and test systems, ADDM can provide an early warning of performance issues. In 11g: Oracle 11g utilizes an automatic mechanism for performance tuning. This reduces the continuous intervention of the DBA that was seen in the earlier versions.

Tuning in the case of 11g is generally of two main types [4]: 1. Proactive Monitoring: Proactive monitoring usually occurs on a regularly scheduled interval, where several performance statistics are examined to identify whether the system behavior and resource usage has changed.

Usually, monitoring does not result in configuration changes to the system, unless the monitoring exposes a serious problem that is developing.

Bottleneck Elimination Tuning usually implies fixing a performance problem. However, tuning should be part of the life cycle of an applicationthrough the analysis, design, coding, production, and maintenance stages. Often, the tuning phase is left until the database is in production.

At this time, tuning becomes a reactive process, where the most important bottleneck is identified and fixed. A statement's execution plan is the sequence of operations Oracle performs to run the statement.


The row source tree is the core of the execution plan. It also helps you to understand the optimizer decisions, such as why the optimizer chose a nested loop join instead of a hash join, and lets you understand the performance of a query.

All statistics, except the number of output rows, are cumulative. We can enable the SQL Trace facility for a session or for an instance.

When the SQL Trace facility is enabled, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files. The additional overhead of running the SQL Trace facility against an application with performance problems is normally insignificant compared with the inherent overhead caused by the application's inefficiency.

The query optimizer operates in two modes: a normal mode and a tuning mode.

The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan. The output of the query optimizer is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan.

When running in the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer. The Automatic Tuning Optimizer performs four types of tuning analysis [4]: i.

ISSN (Print): 2279-0063

Statistics Analysis iii. Access Path Analysis iv.

If these statistics are stale or missing, the optimizer does not have the necessary information it needs and can generate poor execution plans.

The Automatic Tuning Optimizer checks each query object for missing or stale statistics, and produces two types of output: Recommendations to gather relevant statistics for objects with stale or no statistics.

Because optimizer statistics are automatically collected and refreshed, this problem may be encountered only when automatic optimizer statistics collection has been turned off. 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. How to prevent this from recurring?

How do you prevent bad plans based on choice of bind variables? Optimizer statistics issues a. Incomplete statistics c. Improper optimizer configuration d. Changing statistics f. Rapidly changing data 2. Application Issues a. Missing Mi i access structures t t b. Poorly written SQL statements 3.

Advanced Oracle SQL Tuning

Cursor sharing issues a. Bind-sensitive SQL with bind peeking p g b. Literal usage 4. Resource and contention issues a. Hardware resource crunch b b. C t ti row llock Contention k contention, t ti bl block k update d t contention t ti c.


Data fragmentation 5. Parallelism issues a. Not p parallelized no scaling g to large g data b. Upgraded database: Changing g g statistics f. Application Issues 3.

Cursor sharing issues 4. Resource and contention issues 5. Optimizer statistics issues 2. Missing access structures a b.Resource and contention issues 5.

We would first of all like to extend our gratitude the entire management at CDAC Noida for supporting and providing us with the opportunity to utilize such a platform. Either way, the goal is to improve the effective use of a particular resource. VIEW An operation that performs a view's query and then returns the resulting rows to another operation.

Anthony Mokwena. Each table is first sorted on the column values used to join the tables and then merges the two sorted result set into one.