In Pre SQL Tuning stage performing the following is not only essential, but also the fundamentals of Performance Tuning that supplement in improving the SQL Statement performance
-
- Deciding on AMM – Automatic Memory Management or MMM – Manual Memory Management is crucial.
-
- Tuning SGA and PGA components individually not only in MMM but also in AMM is helpful to maintain good performance of the SQL’s.
- Application Tuning | Schema Design
- Using the right type of object such as a suitable type of Table, Index with appropriate storage parameters and placing them in relevant tablespaces holds key for Performance.
- Object Tuning
- The object those used in the database comes under lot of bottleneck issues and even some of them can become hot objects.
- Tuning such objects is a must before getting into the next stage.
- Gather Optimizer Statistics
- Rather than considering Gathering Statistics of optimizer as a Pre SQL Tuning Stage activity, it best suits to be the first step of SQL Tuning.
- However, as it needs to be decided for entire database it is suitable to consider it as a Pre SQL Tuning activity.
The above approach that is explained in DataSphere’s Performance Tuning training program in detail that helps in understanding many areas in SQL Tuning before starting SQL Tuning.
SQL Tuning is the stage in which DBA can contribute immensely to improve the SQL Statement performance.
Most of the DBAs using the SQL Tuning facilities available, try doing things that may not contribute towards improving performance.
Instead, following the fundamental steps takes DBAs towards the goal.
The fundamental steps of SQL Tuning stage are
- Use Pareto Rule also known as 80:20 Rule
- In any given environment all the statements of the Application cannot be tuned, in fact it is not required.
- Focus on 20% SQLs those consume 80% of resource to tune.
- Choose Top SQL
- Even with 20% of the SQLs, it is highly difficult to tune all statements in a short duration.
- Instead choose Top SQL based on either one of the following
- Most resource consuming statements
- Most executed statements
- Set Performance Benchmark
- Once the Top SQLs are chosen, then get the current performance of the statement before beginning tuning and set them as the benchmark so that performance of the statements can be compared after tuning is done.
- Use SQL Tuning facilities
- SQL Tracing
- To get the statement executed, amount of resource consumed for various phases of statement processing, the execution plan that helps to understand the statement behaviour.
- TKPROF – Trace Kernel Profiler
- An utility to convert SQL Trace file to a readable format.
- By using options, can get customized and additional information in the output file about the statement.
- Explain Plan | SQL Plan
- With Explain Plan, it’s possible to know the Execution Plan that the statement may take when it executed.
- With SQL Plan, the plan is given after execution.
- AutoTrace
- Is used to understand that the Execution Plan given is for the desired output of the statement itself as it provides the output along with the Execution Plan.
- Will also give the statistics such as Physical Reads, Logical Reads, Sorting done for the individual statement similar to 1st Stage that is consolidated for all the statements.
SQL Tuning stage is the stage that can improve the SQL Statement performance exponentially but following the Fundamental Steps in a Systematic way and analyzing the output of each facility used depending the Database environment such as OLTP and DSS.
DataSphere’s PT Premium course will give you the concepts and practical demonstrations, to make you comfortable and use the facilities with ease to achieve the desired performance improvement goal.
Post SQL Tuning Stage is for the SQL Statements
- Fine Tuning
- Continual Performance improvement
Fine Tuning is done by Influencing the Optimizer in various ways
Some of the very important ways of influencing Optimizer are
- Set Optimizer Mode
- Use Access Path by using type of object
- Using Hints
- Cursor Sharing options
- Using appropriate join
Continual Performance improvement can be achieved by configuring AWR components
- Set AWR Baseline
- Acts as a benchmark for AWR to collect the performance statistics and store it as snapshot.
- Create Baseline Template
- Helps to build a baseline in future point of time that can be a peak period of Database usage and peak of Optimal Performance of the database.
The Post SQL Tuning though not mandatory and in some cases not required, will help in improvising SQL Statement performance further and also set the stage for Continual Performance improvement which is well explained in DataSphere PT Premium program.
DBAs gets into limelight if they could improve the performance of the Statement by following a Systematic approach instead of doing the tuning in bits and pieces.
It’s Your turn to get the limelight, are You ready!!!
DataSphere’s PT Premium will help You with Your approach towards SQL Tuning and Performance Goal that You set for Your environment’s SQL Statements.
- Introduction
- Memory Tuning
- Application Tuning Overview
- Object Tuning
- Gather Statistics
- SQL Tuning
- SQL Fine Tuning
- Tools & utilities for Tuning
- AWR
- ADDM
- SQL Tuning Advisor
+ Oracle12c DBA Architecture as a Value Add
- Introduction
- Memory Tuning
- Application Tuning Overview
- Object Tuning
- Gather Statistics
- SQL Tuning
- SQL Fine Tuning
- Tools & utilities for Tuning
- AWR
- ADDM
- SQL Tuning Advisor
- Value Add : Oracle12c DBA Architecture
+ 4 Live Online Interactive Sessions with DBA Guru, Mohan Kumar S.
+ SQL Tuning Workshop on 5 different statements
+ 10 ways of Influencing Optimizer Workshop
+ 100% Cashback can avail on
Complete DBA or RAC Package (RAC+ASM+DG)
Specialities of PT Premium : Learn more
Video Sessions
+ 2 decades of experience explained in a Traditional way
+ Mohan Kumar S. has presented his 2 decades of DBA Experience in the form of concepts, theory and Practical Demo
+ Explained in Traditional method that gives you a feeling of Live classroom session by Mohan Kumar S.
+ Concepts are explained for clear understanding of subject in detail and in depth
+ The Practical Demos in line with Real Time Use Cases are given for each topic that stamps the learning with you
Modulewise Tests
+ Ensures your learning is Perfect”
+ A short & crisp objective type questionnaire helps you to ensure that Your learning & understanding of the subjects is perfect
Modulewise eMaterial
+ Read and Imagine the subject Yourself
+ A detailed and descriptive theory inline with the Video Sessions helps you to imagine the subject Yourself
Assignments
+ To Boost your confidence on PT
+ Based on Use Cases, the assignments are designed to equip you to practice and prepare for real time requirements
+ Gives you profound confidence to take up Performance Tuning for the Databases You manage
Exam
+ Helps you to SYNC with the Performance Tuning
+ The Objective type Questions includes the Question on Conceptual understanding, Practicals performed and Theoretical aspects of PT