oracle sql performance tuning and optimization pdf

Oracle Sql Performance Tuning And Optimization Pdf

File Name: oracle sql performance tuning and optimization .zip
Size: 1421Kb
Published: 25.05.2021

Before you start fidgeting with individual SQL statements, it is important to note that hints are probably the last thing you should consider adding when attempting to optimize your code. There are several levels of optimization and it is recommended that you start with the server, then the database instance, and finally go down through the database objects to individual statements.

Tuning of SQL code is generally cheaper than changing the data model.

How to Do Performance Tuning in Oracle

This chapter discusses Oracle automatic SQL tuning features. Using the newly enhanced query optimizer to tune SQL statements, the automatic process replaces manual SQL tuning, which is a complex, repetitive, and time-consuming function.

In normal mode, the optimizer compiles the SQL and generates an execution plan. 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. In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be further improved.

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 called under the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer. Under tuning mode, the optimizer can take several minutes to tune a single statement.

It is both time and resource intensive to invoke the Automatic Tuning Optimizer every time a query has to be hard-parsed. The Automatic Tuning Optimizer is meant to be used for complex and high-load SQL statements that have non-trivial impact on the entire system.

See Chapter 6, "Automatic Performance Diagnostics". Automatic SQL Tuning includes four types of tuning analysis:. The query optimizer relies on object statistics to generate execution plans. 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:.

Because optimizer statistics are automatically collected and refreshed, this problem may be encountered only when automatic optimizer statistics collection has been turned off. See "Automatic Statistics Gathering". Auxiliary information in the form of statistics for objects with no statistics, and statistic adjustment factor for objects with stale statistics.

The query optimizer can sometimes produce inaccurate estimates about an attribute of a statement due to lack of information, leading to poor execution plans. Traditionally, users have corrected this problem by manually adding hints to the application code to guide the optimizer into making correct decisions. For packaged applications, changing application code is not an option and the only alternative available is to log a bug with the application vendor and wait for a fix.

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. SQL Profile addresses this problem by collecting additional information using sampling and partial execution techniques to verify and, if necessary, adjust these estimates. 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.

The availability of the additional information makes it possible to produce well-tuned plans for corresponding SQL statement without requiring any change to the application code. This attribute determines which user sessions can apply the profile. By altering the category of a SQL profile, you can determine which sessions are affected by the creation of a profile. This technique enables you to test a SQL Profile in a restricted environment before making it available to other user sessions. As tables grow or indexes are created or dropped, the execution plan can change with the same SQL Profile.

The information stored in it continues to be relevant even as the data distribution or access path of the corresponding statement change. However, over a long period of time, its content can become outdated and would have to be regenerated. A complete set of functions are provided for management of SQL Profiles. See "SQL Profiles". Indexes can tremendously enhance performance of a SQL statement by reducing the need for full table scans on large tables.

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. The Automatic Tuning Optimizer identifies common problems with structure of SQL statements than can lead to poor performance. 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 SQL Profile.

A user can choose to accept the recommendation to complete the tuning of the SQL statements. These input sources include:. See "Automatic Database Diagnostic Monitor". By default, the AWR retains data for the last seven days. This means that any high-load SQL that ran within the retention period of the AWR can be located and tuned using this feature.

See "Overview of the Automatic Workload Repository". The third likely source of input is the cursor cache. The cursor cache and AWR together provide the capability to identify and tune high-load SQL statements from the current time going as far back as the AWR retention allows, which by default is at least 7 days.

An STS can include SQL statements that are yet to be deployed, with the goal of measuring their individual performance, or identifying the ones whose performance falls short of expectation.

SQL Tuning Advisor provides options to manage the scope and duration of a tuning task. The scope of a tuning task can be set to limited or comprehensive. If the limited option is chosen, the SQL Tuning Advisor produces recommendations based on statistics checks, access path analysis, and SQL structure analysis.

SQL Profile recommendations are not generated. With the comprehensive option you can also specify a time limit for the tuning task, which by default is 30 minutes. After analyzing the SQL statements, the SQL Tuning Advisor provides advice on optimizing the execution plan, the rationale for the proposed optimization, the estimated performance benefit, and the command to implement the advice. You simply have to choose whether or not to accept the recommendations to optimize the SQL statements.

To use the APIs, the user must be granted specific privileges. In addition, values for task name and description are provided. You can use the task name to specify this task when using other APIs. To view the task names associated with a specific owner, you can run the following:.

After you have created a tuning task, you need to execute the task and start the tuning process. For example:. For each proposed recommendation, the rationale and benefit is provided along with the SQL commands needed to implement the recommendation. Additional information about tuning tasks and results can be found in DBA views.

An STS includes:. Associated execution context, such as user schema, application module name and action, list of bind values, and the cursor compilation environment. Associated basic execution statistics, such as elapsed time, CPU time, buffer gets, disk reads, rows processed, cursor fetches, the number of executions, the number of complete executions, optimizer cost, and the command type.

SQL statements can be filtered using the application module name and action, or any of the execution statistics.

In addition, the SQL statements can be ranked based on any combination of execution statistics. SQL Tuning Sets are transportable across databases and can be exported from one system to another, allowing for the transfer of SQL workloads between databases for remote performance diagnostics and tuning. When poorly performing SQL statements are encountered on a production system, it may not be desirable for developers to perform their investigation and tuning activities on the production system directly.

This feature allows the DBA to transport the offending SQL statements to a test system where the developers can safely analyze and tune them. Typically you would use the STS operations in the following sequence:. For both the workload repository and STS, there are predefined table functions that can be used to select columns from the source to populate a new STS.

The data has been filtered to select only the top 30 SQL statements ordered by elapsed time. First a ref cursor is opened to select from the specified baseline. Next the statements and their statistics are loaded from the baseline into the STS.

Move the staging table to the system where the SQL Tuning Sets will be imported using the mechanism of choice such as datapump or database link. The function returns the identifier of the added reference. This creates and stores a SQL Profile in the database.

This hash function normalizes the SQL statement for case changes the entire SQL statement to upper case and white spaces removes all extra whites spaces before generating the signature. The same SQL Profile thus will work for all SQL statements that are essentially the same, where the only difference is in case usage and white spaces.

This may be useful for applications that use literal values rather than bind variables, since this will allow SQL with text differing only in its literal values to share a SQL Profile. If both literal values and bind variables are used in the SQL text, or if this parameter is set to false the default value , literal values will not be normalized. You can also specify whether to ignore errors raised if the name does not exist. This section summarizes the views that you can display to review information that has been gathered for tuning the SQL statements.

You need DBA privileges to access these views. Skip Headers. 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. This auxiliary information is stored in an object called a SQL Profile. Cursor cache The third likely source of input is the cursor cache.

Expert Oracle SQL

Skip to main content Skip to table of contents. Advertisement Hide. This service is more advanced with JavaScript available. Download book PDF. Front Matter Pages i-xxi.

In every interview you might ask the basic questions related to SQL Performace. Because SQL Performance improvement is very essential factor for any project. I want to cover the basic as well as advanced interview questions that might asked in Interview related to Performance improvement of data. The Performance of the SQL is related to lot of factors like which are different indexes used,What hardware is using,what is system hardware and all. In this article i will cover 20 most important SQL Optimization interview questions that might ask in interview. What are different parameters to consider the database performance of Application? What size of images we are using in application.

Database Performance Tuning and Optimization

This chapter discusses Oracle automatic SQL tuning features. Using the newly enhanced query optimizer to tune SQL statements, the automatic process replaces manual SQL tuning, which is a complex, repetitive, and time-consuming function. In normal mode, the optimizer compiles the SQL and generates an execution plan. 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.

Oracle performance tuning is a crucial step in ensuring speedy application function and data retrieval. All database administrators DBAs are familiar with the onerous task of increasing database performance. To accelerate application function, DBAs have to expedite query response time, which means DBAs must have a clear understanding of how their database is organized and how it serves its purpose. That is to say, DBAs must understand not just the database itself but the dedicated computer language that accesses the database to retrieve, manipulate, or delete information. Wondering how to do performance tuning in Oracle, specifically?

Database Performance Tuning and Query Optimization

No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage and retrieval system, without the prior written permission of the copyright owner. However, subject to all other legal limitations in their respective locales and limited to any rights others may have like trademark holders etc. One example of this is Amazon s Look Inside feature.

Oracle Performance Tuning for 10gR2

It seems that you're in Germany. We have a dedicated site for Germany. This Oracle-specific book begins by assuming you have already identified a particular SQL statement and are considering taking steps to improve its performance. The book describes a systematic process by which to diagnose a problem statement, identify a fix, and to implement that fix safely in a production system. You'll learn not only to improve performance when it is too slow, but also to stabilize performance when it is too variable.

To browse Academia. Skip to main content. By using our site, you agree to our collection of information through the use of cookies.

Search this site. Access PDF. Al Buen Entendedor American Addresses Of Joseph H.

Стратмор встал и начал расхаживать по кабинету, не спуская при этом глаз с двери. - Несколько недель назад, когда я прослышал о том, что Танкадо предложил выставить Цифровую крепость на аукцион, я вынужден был признать, что он настроен весьма серьезно. Я понимал, что если он продаст свой алгоритм японской компании, производящей программное обеспечение, мы погибли, поэтому мне нужно было придумать, как его остановить. Я подумал о том, чтобы его ликвидировать, но со всей этой шумихой вокруг кода и его заявлений о ТРАНСТЕКСТЕ мы тут же стали бы первыми подозреваемыми.


Теперь пользователь мог посылать конфиденциальные сообщения: ведь если даже его послание перехватывалось, расшифровать его могли лишь те, кто знал ключ-пароль. АНБ сразу же осознало, что возникла кризисная ситуация.


Thomas A.

Oracle SQL Performance Tuning and Optimization PDF. Download PDF. Written by a Senior Database Administrator who has worked with the Oracle RDBMS for​.


Leave a comment

it’s easy to post a comment

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>