1. No category

HOW TO MANAGE AND ENHANCE PERFORMANCE
DURING MIGRATIONS TO ASE 15.0
CLAUDIA FERNANDEZ
SR. PRODUCT MANAGER, QUEST SOFTWARE
AGENDA
Query processing introduction
ASE 15 Query Processing Enhancements
Overview
Performance Challenge
Techniques to enhance performance during
migrations




Analyze SQL performance variations
Abstract Plans
SQL Tuning
Database Changes
Sybase Database Expert
ASE QUERY PROCESSOR
Minimize the costs of running queries by determining
and analyzing these costs.
Examines parsed and normalized queries, and uses
statistics on database objects to determine the best
possible query plan.
The output from the query processor is a query plan.
The query plan contains the ordered steps that carry
out the query, including the methods to access each
table.
HOW DOES ASE PROCESS SQL?
Server Receives SQL
SQL
Parse, Normalize &
Pre-Process
ASE Optimizer
Determines the Query Plan
Data
Compile
Execute
HOW THE ASE OPTIMIZER WORKS*
SQL
Internally
Rewrites &
Generates
Multiple
Query
plans
Plan 1
Plan 2
Cost Estimation
Plan 3
Plan 1 cost=1000
Plan 2 cost=3000
Plan 3 cost=500
* Simplified version
THE PERFECT OPTIMIZER?
“For any real-life optimizer, no matter how
sophisticated, there will always be a query, a
database state and a system state where the
optimizer makes the wrong decision.”
Mihnea Andrei (Sybase France) &
Patrick Valduriez (Laboratoire d’Informatique de Paris)
User-Optimizer Communication, Abstract Plans Tech paper
2 DIFFERENT SQL’S – SAME QUERY
PLAN
Elapsed Time:
0.080 s
Elapsed Time: 0.080
s
2 DIFFERENT SQL’S – 2 DIFFERENT
PLANS
Elapsed Time:
0.080 s
Elapsed Time: 0.110
s
QUERY PROCESSING
For a SQL statement there are many possible
query plans to use
A SQL statement can be coded in many different
ways to produce the same result set and different
query plans
Other factors that affect SQL performance:




Indexes
Statistics
Configuration Changes
ASE query processing internals
 Vary between major ASE releases
ASE 15.0 QUERY PROCESSING
CHANGES
ASE 15.0 query processor now provides speed and efficiency
for both OLTP and DSS environments.
You can now choose an optimization strategy that best suits
your query environment, whether it
supports primarily OLTP, operational DSS, or a mixture of both.
ASE 15 provides several enhancements to query processing
and more efficient algorithms
ASE 15.0 QUERY PROCESSING
ENHANCEMENTS
•Improved query plan costing
•Improved query plan selection that enhances performance
through:
 New index union and index intersection strategies for queries with
and/or predicates on different indexes
 On-the-fly grouping and ordering using in-memory sorting and hashing
for queries with group by and order by clauses
•Cost-based pruning and timeout mechanisms that use
permutation search strategies for large, multi-way joins, and
for star and snowflake schema joins
•Improved problem diagnosis and resolution using:
 Searchable XML format trace outputs
 Diagnostic output from new set commands
 Joins involving a large number of tables
OPTIMIZATION GOALS
Three optimization goals that can be set at server,
session or query level.
allrows_mix – the default goal, the most useful
goal in a mixed-query environment. It balances the
needs of OLTP and DSS query environments.
 allrows_oltp – the most useful goal for purely
OLTP queries.
 allrows_dss – the most useful goal for
operational DSS queries of medium-to-high
complexity.
OPTIMIZATION GOALS
sp_configure "optimization goal", 0, "allrows_mix"
set plan optgoal allrows_oltp
select * from A order by A.a plan "(use optgoal
allrows_dss)“
How to determine the best optimization goal for the
database, application and query?
OPTIMIZATION CRITERIA
Represents specific algorithms or relational
techniques that may or may not be considered
when ASE creates a query plan.
Recommended only for tuning:
 hash_join
opportunistic_distinct_view
group-sorted
 hash_union_distinct
parallel_query
distinct_sorting
 merge_join
store_index
group_hashing
 merge_union_all
append_union_all
index_intersection
 merge_union_distinct
 multi_table_store_ind
 nl_join
bushy_space_search
distinct_hashing
distinct_sorted
How to tune queries with the best
optimization criteria?
OPTIMIZATION TIMEOUT LIMIT
The optimizer triggers a timeout inside the optimizer at
each optimization block, when both these circumstances are
met:
 At least one complete plan has been retained as the best plan.
 The user configured timeout percentage limit has been exceeded.
Set a server, sesion and query level
sp_configure "optimization timeout limit", 10
sp_configure "optimization timeout limit", 10
select * from <table> plan "(use opttimeoutlimit <n>)"
Values between 0 and 100
What’s the best optimization timeout limit to use for the server,
application and queries?
HOW TO TAKE MAXIMIZE
PERFORMANCE IN ASE 15.
1. Collect SQL statements
2. Compare query plans before migration and in
ASE 15
3. If performance degradation is identified,
considered:
1.
2.
3.
4.
Abstract plans
SQL tuning
Indexing
Configuration parameter changes
COLLECTING SQL STATEMENTS FOR
ANALYSIS
SQL from important stored procedures and views
From MDA tables
From Query Processing (QP) Metrics
 SQL statements
 CPU execution time
 Elapsed time
 Two-part physical IO (PIO) reads
 Number of executions
 Minimum, maximum, and average
COMPARE QUERY PLAN CHANGES
DATABASE A
DATABASE B
MIGRATION
WHAT ARE ABSTRACT PLANS?
select title, pub_name from titles,publishers where
titles.pub_id=publishers.pub_id
Abstract Plan
Show Plan
( nl_g_join
( t_scan
publishers )
( t_scan
titles )
)
( prop titles
( parallel
( prefetch
( lru )
)
( prop publishers
( parallel
( prefetch
( lru )
)
STEP 1
The type of query is SELECT.
FROM TABLE
publishers
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for
data pages.
FROM TABLE
titles
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for
data pages.

1 )
2 )
1 )
2 )
USES OF ABSTRACT PLANS
Tuning
 Specific Queries
 Applications
galaxy
12.5 dev
Migration
 From one release of ASE to another
 Same version of ASE but different instances
12.5 test
12.5
production
Using Abstract Plans for Tuning
Forcing plans – tune query
 Change query in application or stored procedure
 Specify a plan in the query
Example – forcing an index
select title, pub_name from titles,publishers
where titles.pub_id = publishers.pub_id
plan
"(nl_g_join
( t_scan publishers )
( i_scan titles_960003422 titles )
)
( prop titles
( parallel 1 )
( prefetch 2 )
( lru )
)
( prop publishers
USING ABSTRACT PLANS FOR
TUNING
Forcing plans – no change to applications
Create plans for specific queries
create plan "select title, pub_name from
titles,publishers where titles.pub_id =
publishers.pub_id" " (nl_g_join
( t_scan publishers )
( i_scan titles_960003422 titles )
)
( prop titles
( parallel 1 )
( prefetch 2 )
( lru )
)
( prop publishers
( parallel 1 )
( prefetch 2 )
( lru )
)"
•Plans stored in sysqueryplans
•Delete old plan if re-trying “create plan”
sp_drop_qplan <id>
Use stored plans
set plan load on
USING ABSTRACT PLANS IN ISQL
FOR MIGRATION
Save plans
1
Migrate
2
If needed use pre-migrate plans
3
USING APS IN ISQL FOR MIGRATION –
SAVING PLANS
Analyze
1
• Create a plan group:
• sp_add_qpgroup pre_migrate
• Instruct server to capture plans:
• set plan dump pre_migrate on
2
3
• sp_configure “abstract planPerformance
dump”, 1
• default group is ap_stdout Optimization
• Run queries:
• Plans captured automatically
• Ensure plan capture of all or most
important queries
Predict
• Stop capturing of plans:
• Session:
•set plan dump off
• Server:
•sp_configure “abstract plan dump”, 0
USING APS IN ISQL FOR MIGRATION –
MIGRATE
• Upgrade to new
version of ASE
1
2
3
•Migrate to different
environment
• dev
• prod
• Follow guidelines
USING APS IN ISQL FOR MIGRATION –
POST MIGRATE
• Verify migrated server
1
2
• Instruct
ASE to use stored
plans
• Only if all other options exhausted
• Session:
•set plan load pre_migrate on
• Server:
•sp_copy_all_qplans ap_stdout, ap_stdin
•sp_configure “abstract plan load”, 1
• Run your applications
• Pre-migrate behavior established
3
USING APS IN ISQL FOR MIGRATION –
POST MIGRATE FINE TUNING
• Evaluate
• Retain benefits of possible new plans
• Identify queries that are problematic
1 a plan group 2
• Create
• sp_add_qpgroup post_migrate
• Copy plans for problematic queries
into this plan group
• sp_copy_qplan <id>, post_migrate
• Instruct server to use saved plans
• set plan load post_migrate on
•Run your applications
• Pre-migrate behavior established for
specific queries
3
WHAT IS SYBASE DATABASE EXPERT
Analyze
Performance
Diagnosis
Performance
Assurance
Preempt
Performance
Optimization
Predict
Performance Management
WHAT IS SYBASE DATABASE EXPERT
Analyze
• Performance Monitor:
Monitors ASE performance activity and
diagnoses performance bottlenecks with
visual analysis of performance metrics
Performance
Diagnosis
Preempt
Performance
Optimization
Performance
• SQL Inspector:
Assurance
Collects SQL performance metrics
Predict
• SQL Scanner:
Identifies problematic SQL from
application source codes
Performance Management
WHAT IS SYBASE DATABASE EXPERT
Analyze
• Index Advisor:
Proposes new index scenarios to improve
SQL performance metrics
Performance
• Syntactical SQL Optimizer:
Diagnosis
Performance
Transforms SQL statements to locate the
Assurance
most-efficient alternative SQL
code
Preempt
Performance
Optimization
Predict
Performance Management
WHAT IS SYBASE DATABASE EXPERT
Analyze
• Index Impact Analyzer:
Analyzes the impact that indexes may have on SQL performance
• Migration Analyzer:
Compares query plan changes between ASE environments
preempting performance changes during migrations
Performance
• Configuration Analyzer:
Diagnosis
Performance
Performance
Optimization
Evaluates the effect on SQL performance when changing ASE
Assurance
configuration parameters.
• Unused Index Analyzer:
Predict
AnalyzesPreempt
query plans to identify unused indexes
Performance Management
DATABASE EXPERT ENHANCEMENTS
FOR ASE 15
Identifies top SQL from QP Metrics with SQL
Inspector
Determine optimization goal and timeout for
multiple SQL with Configuration Analyzer
Optimizes SQL using SQL rewrites, forces,
optimization goals and optimization criteria
Support for function-based indexes
Enhanced Abstract Plan usage to manage
performance changes, Migration Analyzer
XML query plans
DATABASE EXPERT’S MIGRATION
ANALYZER
Compares SQL performance changes between
different database environment
Allows users to preempt performance degradation
when performing database migrations, database
upgrades and application rollouts
Integrates Abstract Plan Management to stabilize
SQL performance
THE MIGRATION ANALYZER
Abstract Plans
SQL Repository
Abstract Plans
ASE 12.0
ASE 12.5
ASE 15
Master Plan
Snapshot
Scenario1
Scenario2
If performance degradation is identified,
performance can be managed
through Abstract Plans
QUESTIONS AND ANSWERS
More information:
 ASE Performance and Tuning Guides
 www.sybase.com/databaseexpert