1
Following a Tuning Methodology
Objectives
After completing this lesson, you should be able to do
the following:
• State the procedural steps in managing
performance
• Describe the causes of performance problems
• Identify the main system areas that you can
address by using the tuning process
• Describe the tuning methodology
• Explain the advantage of following the steps of the
tuning methodology in their proper sequence
• List the tuning steps that are the responsibility of
the application developer
• Describe an overview of SQL Tuning
1-
Managing Performance
• Start early.
• Set objectives.
• Tune and monitor compliance.
• Work together.
• Handle exceptions and changes.
1-
Factors to Be Managed
• Schema
– Data design
– Indexes
• Application
– SQL statements
– Procedural code
• Instance
• Database
• User expectations
• Hardware/network tuning
1-
Performance Problems
• Inadequate consumable resources
– CPU
– I/O
– Memory (may be detected as an I/O problem)
– Data communications resources
• Design limitations
• Locking
1-
Critical Resource
• Performance depends on the following:
– How many clients need the resource
– How long they must wait for it
– How long they hold it
• Consider limiting demand to maintain acceptable
response times.
Response time
Time to
service Acceptable
completion response time
Demand rate
1-
Excessive Demand
• Greatly increases response time and reduces
throughput
• Should be prevented as much as possible by
limiting demand to a level that still allows
reasonable throughput
1-
Scalability: Resource Exhaustion
Resource exhaustion can result from:
• Hardware exhaustion
• Table scans in high-volume transactions, causing
inevitable disk I/O shortages
• Excessive network requests, resulting in network
and scheduling bottlenecks
• Memory allocation, causing paging and swapping
• Excessive process and thread allocation, causing
operating system thrashing
1-
Scalability with Application Design,
Implementation, and Configuration
Applications have a large impact on scalability.
• Poor schema design can cause expensive SQL
that does not scale.
• Poor transaction design can cause locking and
serialization problems.
• Poor connection management can cause poor
response times and unreliable systems.
1-
Tuning Methodology
1. Tune business function
2. Tune data design
3. Tune process design
4. Tune SQL statements
5. Tune physical structure
6. Tune memory allocation
7. Tune I/O
8. Tune memory contention
9. Tune operating system
10. Networking issues
1-
Tuning Roles
Business analyst 1. Tune business function
Designer 2. Tune data design
3. Tune process design
Application developer 4. Tune SQL statements
5. Tune physical structure
Database administrator 6. Tune memory allocation
7. Tune I/O
8. Tune memory contention
Operating system administrator 1. Tune operating system
Network administrator 2. Networking issues
1-
Overview of SQL Statement Tuning
• Identify problematic SQL.
• Verify optimizer statistics.
• Review execution plans.
• Restructure SQL statements.
• Restructure indexes.
• Maintain execution plans.
1-
Identify Problematic SQL
• Identify slow programs.
• Look at SQL in the program.
• Use SQL_Trace and TKPROF.
1-
Verifying Optimizer Statistics
• Gather statistics for all tables.
• Gather new statistics when existing statistics
become stale.
1-
Reviewing the Execution Plan
• Driving table has the best filter.
• Fewest number of rows are being returned to the
next step.
• The join method is appropriate for the number of
rows being returned.
• Views are used efficiently.
• There are no unintentional Cartesian products.
• Each table is being accessed efficiently.
• Examine predicates in the SQL statement and the
number of rows in the table.
• A full table scan does not mean inefficiency.
1-
Restructuring the SQL Statements
• Compose predicates using AND and = .
• Avoid transformed columns in the WHERE clause.
• Avoid mixed-mode expressions and beware of
implicit type conversions.
• Write separate SQL statements for specific tasks.
• Use EXISTS rather than IN for subqueries.
• Control the access path and join order with hints.
1-
Restructuring the Indexes
• Remove nonselective indexes to speed the
DML.
• Index performance-critical access paths.
• Reorder columns in existing concatenated
indexes.
• Add columns to the index to improve
selectivity.
• Consider index-organized tables.
1-
Maintaining Execution Plans over Time
• Stored outlines
• Stored statistics
1-
Summary
In this lesson, you should have learned how to:
• Manage performance
– Start early; be proactive.
– Set measurable objectives.
– Monitor requirements compliance.
– Handle exceptions and changes.
• Identify performance problems
– Inadequate consumable resources
– Inadequate design resources
– Critical resources
– Excessive demand
1-
Summary
• Tune SQL statements
– Manage user expectations.
– 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.
1-
