samareshoracle

 Profile Answers by samareshoracle 

• Aug 4th, 2012

 

Following a Tuning Methodology

Objectives::
• Determine performance problems
• Manage performance
• Describe tuning methodologies
• Identify goals for tuning
• Describe automatic SQL tuning features
• List manual SQL tuning steps

Performance Problems::
Inadequate consumable resources
– CPU
– Data communications resources
Example, if you want a function to complete in less than one second, a network with a message turnaround time of two seconds will never meet the target.

High-load SQL ::
– A single SQL statement may take up a large amount of resources, thereby affecting the performance of other statements.

Contention ::
– If many users are trying to update the same set of tables, contention due to locking .

Factors to Be Managed ::::::
Schema :
– Data design
– Indexes

Application :
– SQL statements
– Procedural code

Instance (memory utilization)
Database (physical arrangement of data on the disk)
Hardware and network tuning

Tuning Goals ::::

Reduce the response time
Reduce resource usage

Example : Reduce the workload :
It is possible to change the execution plan of the statement without altering the functionality to reduce the resource consumption.
1. If a query needs to access a small percentage of data in the table, by creating an index, you reduce the amount of resources used.
2. If a user is looking at the first 20 rows of the 10,000 rows returned in a specific sort order, and if the query (and sort order) can be satisfied by an index, then the user does not need to access and sort the 10,000 rows to see the first 20 rows.

::::: Overview of SQL Tuning :::::

1. Identify causes of poor performance.
– The volume of data being accessed.
– Poorly written application code
– OPTIMIZER_MODE setting
–Optimizer statistics.

2. Identify problematic SQL.
– Automatic: ADDM, Top SQL
– Manual: V$ views, statspack

3. Apply a tuning method.
– Manual tuning
– Automatic SQL tuning

4. Implement changes to:
– SQL statement constructs
– Access structures such as indexes

5.Manual Tuning

Gather information about the referenced objects.
Gather optimizer statistics.
Review execution plans.
Restructure SQL statements.
Restructure indexes and create materialized views.
6. Maintain execution plans.

Gather Information About
Referenced Objects
SQL text from V$SQLTEXT Structure of tables and indexes and whether the
indexes are unique or non-unique
Optimizer statistics
– Number of rows in each table and the selectivity of the index columns, including the date when the segments were last analyzed

Optimizer plan - EXPLAIN PLAN
– Create Optimizer plan for the SQL statement
Gathering Optimizer Statistics
Gather statistics for all tables.
Gather new statistics when existing statistics become stale.
– This monitoring is enabled by default when STATISTICS_LEVEL is set to TYPICAL or ALL.
– The information about changes of tables can be viewed in the USER_TAB_MODIFICATIONS view.
– In Oracle Database 10g, the statistics gathering is done automatically by default.

Reviewing the Execution Plan

The join method is appropriate for the number of rows being returned.
Fewest number of rows are being returned to the next step.
There are no unintentional Cartesian products.
Each table is being accessed efficiently.
Examine the predicates in the SQL statement and the number of rows in the table.
A full table scan does not mean inefficiency.

Restructuring the Indexes

The tools for monitoring usage of indexes :

– SQL Tuning Advisor.

Remove unnecessary indexes to speed the DML.
Reorder columns in existing concatenated
indexes frequently used in WHERE clause.
Create appropriate indexes based on usage type:

B*tree
Bitmap
Bitmap join
Concatenated
Consider index-organized tables.


– Storing optimizer statistics for tables applies to all SQL statements that refer to those tables.
– Storing an execution plan (that is, plan stability) maintains the plan for a single SQL statement.

Summary : Tune SQL statements
Analyze the results at each step
Tune the physical schema
Choose when to use SQL
Reuse SQL statements when possible
Design and tune the SQL statement
Get maximum performance with the optimizer



Hope this help you all.

robindas

 Profile Answers by robindas Questions by robindas

• Jan 11th, 2013

 

Few steps for query tuning.

1) Check for if any sub query or exists or not exists is there if yes then try to change it in the joins

2) Try to check the way joins had been written try to put it in format to follow one to many relation ship and joins are in primary to foreign keys.

3) if dead locks are happening due try to implement the serialization or try to use hint with (nolock) with select statement and with (rowlock) with insert update and delete statement.

There are many more will keep writing later on.
robin das (tech mahindra)