SQL tuning and the Dynamic Statement Cache
What Will We Talk About?
› Some SQL Tuning Fundamentals
› Dynamic SQL in More Detail
› Introduction to DB2 Statement Caching
› Mining the Dynamic Statement Cache
› What’s on the Horizon for Statement Caching in DB2 9
Performance Tuning Methodologies
General Thoughts
› High-level Monitoring is the starting point
– High level performance workload metric
–
–
–
–
System-wide monitoring approaches
Ongoing Monitoring for defined exceptions
Quick Access to a current view of the DB2 subsystem
Quick Access to Accounting and Statistics data
› Drilldown using two approaches
– First approach
• Collect performance data on an ongoing basis
• Analyze data for exception conditions
• Higher cost for data collection
– Second approach
• Collect performance data only when indicated by high-level monitoring tool and
for a set period of time
• Analyze results
• Less data for identifying trends over time
DB2 Tuning
Where Should You Spend Your Time
› What Can I tune in DB2
SQL
70%
Subsystem
10%
Object
Analysis
20%
› Where are the biggest problems
– Purely an estimate and your experience may vary
– Many tuning efforts combine multiple areas
• Especially true of SQL and Object Analysis
Solving the Problem
SQL Analysis Across the Application Life Cycle
› Focus on individual SQL statements
– Do they meet “best practice” coding
standards
– Do they use expected/accepted DB2
access paths
– Do they deliver desired result set in
acceptable time with acceptable
resource consumption
› Developed and tested in controlled
environment
› More predictive in nature
› Focus on workload dynamics
– How does concurrent execution affect
response time/resource consumption
– Does this SQL statement/program
collide with other transactions
– Same application
• Other applications in a shared
subsystem
› Real world unpredictability comes into
play
› More focus on measuring the workload
and rapidly reacting
Tuning SQL Across the Lifecycle
› Early and Often
› Definitely not a one-shot deal
In Development
Focus on Atomic SQL
Minimal Concern for
workload metrics
In Test
More
focus on workload
metrics
Continued focus on atomic
SQL
In Production
Major
emphasis on workload
metrics including I/O analysis
Reduced focus on Atomic SQL
SQL Tuning Fundamentals
DB2 Optimizer Determines SQL Performance
SQL Statement
Text
DB2
Configuration
Access Path
To the Data
Hardware
Configuration
TABLE
Schema Definitions
Catalog
Statistics
Tablespace
DB04.TS1
Index
App1.Index1
SQL Tuning Fundamentals
DB2 Access Paths
Access Paths
To the Data
•Direct Row access using ROWID
•One Fetch Index Scan using Min, MAX
•Unique Matching Index Scan using a
predicate value
•Matching Index Scan Only
•Non-Matching Index Scan Only
•Matching Index Cluster Scan
•Matching Random Index Scan
•Multiple Matching Index Scan using
AND and OR
In order from minimum
to maximum resource
usage
•Non Matching Cluster Index Scan
•Segmented Table Space Scan
•Non Segmented Table Space Scan
(in parallel or sequential)
•Non-matching Random Index Scan
SQL Tuning Fundamentals
Access Path Selection
For Static SQL
› Access Path Selection occurs
during bind step of compile/linkedit application deployment cycle
› Options exist to make static run
more like dynamic SQL
– REOPT (VARS)
• Access path determined at run time
for those statements with host
variables or parameter markers
For Dynamic SQL
› Access Path Selection occurs at
run-time
› Options exist to make dynamic
SQL run more like static
– KEEPDYNAMIC bind option
• Holds prepared statements across
commits to avoid cost of re-preparing
statement
– Global Dynamic Statement Cache
• Maintains Skeleton of prepared
statement in virtual storage
SQL Tuning Considerations
More on Static SQL
› Static – Application data access requirements are known and
traditional procedural languages are being used
– Used in most early DB2 applications
–
–
–
–
–
Host variable defined
In working storage
Simpler programming techniques especially for retrieval
Access path determined at bind time – better performance
Authorization for execution at the plan (package later) level
Qualifiers passed via host variables
SQLJ provides for bound static SQL
SQL Tuning Considerations
Static vs. Dynamic SQL
› Dynamic - Data access requirements are ad hoc
in nature and identified on the fly
• Used for tools like QMF, SPUFI, and others
•
•
•
•
Build and execute SQL on the fly
Access path determined at run time
User requires authorization to all accessed objects
No host variables are allowed – parameter markets used
instead
Parameter provides
placeholder for later
substitution
SQL Tuning Considerations
Static vs. Dynamic Usage
Over Time Mix of Dynamic and Static SQL has changed
› 90% static and 10% in the first 18-20 years of DB2
› Now closer to 50% for each type of SQL
– One large BMC banking customer runs 25 million SQL statements a day with
80% of those dynamic
› What’s driving dynamic SQL usage
– Dynamic SQL offers flexibility that can simplify developing complex applications
– New applications being developed on distributed platforms using connections
that only support dynamic SQL
• DB2 CONNECT, etc.
– ERP applications that were implemented with dynamic SQL
• SAP, PeopleSoft, Siebel
– New applications being developed on distributed platforms
• Younger developers are much more familiar with GUI-based programming
environments and don’t even sign on to the mainframe
– More Java and C++
Dynamic SQL Considerations
› SQL PREPARE operations required (More on Prepares later)
– Prepared dynamic SQL statements by default are not persistent across commit
points
• Frequently executed dynamic SQL statements may require many prepare operations
– Prepare costs vary significantly but can be over 500 μs
› Changes in DB2 statistics
– Dynamic SQL always uses current catalog statistics for access path selection
• Changes in DB2 statistics can cause unpredictable changes in access paths
– Some DB2 customers collect catalog statistics to drive maintenance processes
• May cause SQL performance to fluctuate unexpectedly
› Security is generally more complex with dynamic SQL
– Application users generally require authorization to the objects being accessed
– Auditing is also affected because statements are developed on the fly
› Access path determination is difficult because access path information
is not available prior to execution
Dynamic SQL Statement Caching
Introduction
› Goal is to reduce or eliminate SQL Prepare operations required for
dynamic SQL statements
› Prepare operations have become more costly over time as optimizer
has become more sophisticated
› Implementation
– Three kinds of caching
• Local Dynamic Statement Caching
• Global Dynamic Statement Caching
• Full Caching
– Cache prepared SQL statement and statement text for dynamic SQL
statements in DBM1address space
• Local Statement Cache
• Global Dynamic Statement Cache
– Controlled by various parameters
• Bind options
• DSNZPARMs
Dynamic SQL Statement Caching
Available Caching Options
Local Statement Caching
Virtual Storage
- KEEPDYNAMIC(YES)
Considerations
bind option
-If you are already VS
- MAXKEEPD DSNZPARM
constrained pre-v8
option controls size
then consider a
Global Statement Caching
-0 disables keeping
dataspace for global
-CACHEDYN(YES) ZPARM
executables
cache
-V7 and earlier allow data
-Text always held
-MAXKEEPD will also
space for this cache
impact VS constraint
-EDMDSPAC to size
-V8 the problem is
-V8 moves cache to it’s
greatly reduced
own pool
-Still an issue with
-EDMSTMTC to size
thread storage
-Removes dataspace
option
Full Caching
- Local and Global
caching both active
Dynamic Statement Caching
Impact on the Prepare Process
› Full Prepare
– Occurs when SKDS not present in global dynamic SQL cache
– Can occur because of PREPARE and EXECUTE IMMEDIATE
– Implicitly occurs with EXECUTE IMMEDIATE when using
KEEPDYNAMIC(YES)
› Short Prepare
– SKDS of dynamic SQL statement in the global cache
– Copied into local storage for the thread
› Avoided Prepare
– Only available with full caching option
– New EXECUTE statement avoids the prepare because the statement is still in
the local cache
Taking Advantage of Dynamic Statement Caching
Questions to Ask
› Are you running dynamic SQL in production environments
– Is you said no, there may be some benefit if a development/test environment
• Database tools often use dynamic SQL
• Might consider for test environments with lots of DBA activity
› Is your production dynamic SQL repeated frequently
– Products like QMF, SPUFI and other ad hoc query tools probably won’t benefit
– Are your bind parameters consistent with dynamic statement caching
› Do you have Virtual or Real Storage Constraints
– If DBM1 is virtually constrained dynamic statement caching can make the
problem worse
• EDM Pool and thread storage
– If you have real storage constraints any options will probably make the
situation worse
– If Virtual Storage Constraints but real memory is available
• Consider moving the Dynamic Statement Cache to a dataspace
Dynamic Statement Caching
Conditions Where Statements Can Utilize the Cache
› Statement text must be 100%
the same
– Use parameter markers
› Additional items must be 100%
the same or compatible
– Bind rules
– Special registers
– Authorizations
– Others
› You may not get any benefit
out of the dynamic statement
cache at all
– Most likely to benefit if you
using an ERP or some other
application that uses
dynamic SQL extensively
Turning on the Cache
› Global Statement Cache
– DSNZPARM CACHEDYN set to Y
– For V6 & V7 with no dataspace
• Calculate additional space needed for statement cache in the EDMPOOL and set
via EDMPOOL ZPARM value
– For V6 & V7 with dataspace
• Calculate space for the dataspace based on you dynamic SQL workload
• Allocated via EDMDSPAC (sets initial value)
• Upper limit specified via EDMDSMAX
– For DB2 V8
• Set size in bytes of statement cache via EDMSTMTC ZPARM
– Default is 5000 bytes
• Consider setting EDMBFIT to Y if virtual storage constraint is a problem
Turning on the Cache
› Local Statement Cache
– KEEPDYNAMIC(YES) Bind Parameter
– REOPT(ONCE) Bind Parameter
– MAXKEEPD ZPARM parameter
Dynamic SQL Statement Caching
Measuring Cache Effectiveness
Statement Pool Full
Failures
Should be 0
Increase Statement Pool Size
if not
Global Cache Hit Ratio
Shoot for 90+%
Local Cache Hit Ratio
Specific for Applications
bound with
KEEPDYNAMIC(YES)
Statement Discarded
Shoot for 0
Increase MAXKEEPD
Getting Data About Cache Usage
› As shown in previous chart
– Statistics on Statement Caching in the standard DB2 statistics records
– Metrics show details about cache hit ratios and other useful data points that
help you evaluate overall performance of your statement caches
› For more detail on Global Statement Cache usage the following
instrumentation is provided
– IFCID 316 – Provides details on statements in the cache
• First 60 bytes of SQL text
• Includes execution statistics (0 if not being collected)
– IFCID 317 can then be used to retrieve the entire SQL statement from the
cache once you have identified the statement of interest
› EXPLAIN STMTCACHE
– V8 feature that exports Dynamic Statement Cache information to the
DSN_STATEMENT_CACHE_TABLE
– Nearly identical to the detail in IFCID 316 & 317
– Multiple options including ALL, stmt-id, and stmt-token
Reviewing Global Statement Cache Information
IFCID 316 Results
•First 60 Bytes of SQL Text
•Bind Options
•Execution Statistics
Reviewing Global Statement Cache Information
IFCID 317 Results
•Full SQL Text
Reviewing Global Statement Cache Information
IFCID 318
› Execution statistics for
dynamic SQL statements
› Turn on collection with
Monitor trace IFCID 318
– Begins collecting statistics
and accumulates them for the
length of time the monitor
trace is on
– Stop Monitor trace resets all
statistics
– 2-4% overhead per dynamic
SQL statement stored in the
cache
› Recommended approach
– Run the trace only when
actively monitoring the
cache
› Use EXPLAIN STMTCACHE to
externalize data for evaluation
Extracting Information from the Dynamic Statement
Cache
› EXPLAIN STMTCACHE SQL statement
– Populates PLAN_TABLE (if defined)
• Sets QUERY_NO to UNIQID in DSC
– Plan table entry stored in decimal
• Remember this is access path currently in use for this statement
....
– Populates DSN_STATEMENT_CACHE_TABLE
• Closely resembles the statement cache seen online via IFCID 316
– A few useful fields including Reasons for invalidation if invalidated
....
– Populates DSN_STATEMENT_TABLE (if defined)
• COLLID set to ‘DSNDYNAMICSTMTCACHE’
• QUERYNO set to UNIQID from DSC
...
EXPLAIN STMTCACHE Options
› EXPLAIN STMTCACHE ALL
– Inserts rows in DSN_STATEMENT_CACHE_TABLE for each statement in the
Dynamic Statement Cache
– Does not populate the PLAN_TABLE or DSN_STATEMNT_TABLE
› EXPLAIN STMT_ID :hv or numeric literal
– Uniquely identifies the statement the be explained using the unique identifier
(UNIQID) associated with the statement and inserts row(s)
– Populates DSN_STATEMENT_CACHE_TABLE, PLAN_TABLE,
DSN_STATEMMT_TABLE, and DSN_FUNCTION_TABLE
• If they exist
– SQL Code -248 returned if statement is not found in the cache
› EXPLAIN STMT_TOKEN :hv or ‘text literal’
– Token assigned to statements by the application
– Insert rows based on the number of statements in the DSC that match the token
– Populates same tables as EXPLAIN STMT_ID option
Summary
› Dynamic SQL is growing in usage
– ERP Vendors
– Distributed applications
› DB2 offers multiple options for reducing the overhead traditionally
associated with dynamic SQL
› These options include multiple types of statement caching
– Local statement caching
– Global statement caching
– Full statement caching
› BMC offers comprehensive solutions that help tune static and
dynamic SQL and report on DB2 dynamic statement caching
Descargar

Do I need to be concerned about Application …