SQL Performance Tuning in the Digital World Sheryl

SQL Performance Tuning in the Digital World Sheryl M. Larsen BMC Session Code: A 05 15 November 10: 20 -11: 20 am | Platform: z/OS

Why are we here? MLC Savings of 5 -20%! All CEC ABCD $3. 6 M / Yr $Millions by % Reduction %MSUs MLC $ MLC Yr 5% 10% 20% 100% $18. 0 $0. 9 $1. 4 $3. 6 19% 23% $4. 1 $0. 2 $0. 41 $0. 8

Why now ? Business Growing • Acquisitions • Organic (Web, Mobile) MLC Cost • Not all MLC usage drives Revenue • Not all MLC $ increases from usage

Digital Business Drives Data Volume and Complexity 90% is unstructured data *IDC Digital Universe Study, Dec. 2012 4

Your Expanding Resource - Data

Destructive Forces Can Be Costly to Your Data Bad SQL Bad SQL Bad Access Paths Bad Access Paths

Once Upon a Time: • • DB 2 CPU was rising DB 2 Tools were under utilized DB 2 SQL Coding and Tuning skills were low DB 2 performance analysis staff could not be proactive and was shrinking • A wizard was summoned to come on board as a dedicated proactive performance analyst and mentor

Skills Needed to Tackle New Challenges Objective 1: Understand what new DB 2 features will change the way we tune queries. Objective 2: Understand what external forces will change the way we tune queries Objective 3: Learn the 5 ways to make queries go faster Objective 4: Learn what the long term implications are in using hints & profiles Objective 5: Understand what else in the subsystem impacts performance

Newer DB 2 SQL Features (DB 2 9, 10, 11, 12) Call from trigger, statement isolation, FOR READ ONLY KEEP UPDATE LOCKS, SET CURRENT SCHEMA, Client special registers, long SQL object names, SELECT from INSERT, UPDATE or DELETE, INSTEAD OF TRIGGER, SQL PL in routines, BIGINT, file reference variables, XML, FETCH FIRST & ORDER BY in subselect & fullselect, caseless comparisons, INTERSECT, EXCEPT, MERGE, not logged tables, Omni. Find, spatial, range partitions, data compression, DECFLOAT, optimistic locking, ROLE, TRUNCATE, index & XML compression, created temps, inline LOB, administrative privileges, implicit cast, increased timestamp precision, currently committed, moving sum & average, index include columns, row and column access controls, time travel query, GROUPING SETS, ROLLUP, CUBE, global variables, Text Search functions, accelerated tables, DROP COLUMN, array data type, XML enhancements, ARRAY and LOB global variables, Dynamic SQL in SQLPL UDFs and stored procedures, Enhanced MERGE support, Piece-wise modification of data (DELETE), XMLModify multiple update support, Numeric-based pagination, Data-dependent pagination

What will Change? • Some of the new features arenew really expensive and bubble to the top • You will need a broader, more simplistic view • You will need cognitive automation

More External Forces • • Things are getting bigger Things are getting more complicated No control over applications or data Good enough is no longer good enough

Five Ways to Make SQL Go Faster 1. 2. 3. 4. 5. Rewrite SQL Alter/change the indexes Improve the DB 2 catalog statistics Change the DB 2 Optimizer’s mind Keep the data organization perfect

What About New Access Paths? What if the optimizer started to favor many smaller indexes? Adaptive Index Runtime decision

Index advisor- a wise opinion

Use Math to Change the Optimizer’s Mind +0, CONCAT ‘ ‘ also – 0, *1, /1 – Place no op next to predicate – Use as many as needed – Discourages index access, however, preserves Stage 1 – Can Alter table join sequence – Can fine tune a given access path – Can request a table scan – Works at the predicate level

Math Example - Scan SALES_ID. MNGR. REGION Index MNGR Index SELECT S. QTY_SOLD , S. ITEM_NO , S. ITEM_NAME FROM SALE S WHERE S. SALES_ID > : hv-id +0 AND S. MNGR = : hv-mngr CONCAT ‘ ‘ AND S. REGION BETWEEN : hvlo AND : hvhi CONCAT ‘ ‘ ORDER BY S. REGION FOR FETCH ONLY WITH UR • • • REGION Index If you know the predicates do very little filtering, force a table scan Use a No Op on every predicate This forces a table scan FOR FETCH ONLY encourages parallelism WITH UR for read only tables to reduce CPU Should this be Documented?

DISTINCT Table Expressions Example • SELECT Columns FROM TABX, TABY, (SELECT DISTINCT COL 1, COL 2 …. . FROM BIG_TABLE Z WHERE local conditions) AS BIGZ WHERE join conditions • Optimizer is forced to analyze the table expression prior to joining TABX & TABY

What are the Long Term Implications in using Hints? SORTN UJOG SORTC UJOG Page Rang e NNNN NNYN Y NNNN NYNN NNNN Y NNNN NNNN NNNN Col Corr Func Name Qblock Type Opt Hint Used TS Remark s GG …. Good 1 …. Bad 1 S AB SELECT GG S CD UPDATE NN EF INSERT …. XX DELECT …. +0 GA NCOSUB XX …. Bogus 3 SL CORSUB XX …. Extra > ML DELCUR …. R KL UPDCUR …. parens S TL SELUPD …. help R R SS XX SS

Use of Profiles for Tuning More Than One Query at a Time • NPAGES THRESHOLD – integer Pages Threshold for Index Access 0 or greater = use special access path selection for tables under a given size • MAX_RIDBLOCKS – maximum number of RID blocks per RID pool for that a test system uses to model a production system • STAR JOIN – DISABLE or ENABLE to use Star Join Access • MIN STAR JOIN TABLES – integer number between 3 and 225 to set the minimum number of tables allowed in a Star Join • SORT_POOL_SIZE – the sort pool size that a test system uses to model a production system

What’s the difference between last night execution and the nights before ?

SQL Performance Technology Should: Monitor workload impact with a light footprint Identify expensive queries, not the highest CPU Send alerts for degrading SQL day or night Give smart REORG advice Give smart index advice Track access path changes from migrations to new applications or DB 2 upgrades across a workload • Have many similar dash boards to enable a broader user audience • • •

Skills Needed to Tackle New Challenges Objective 1: Understand what new DB 2 features will change the way we tune queries. Objective 2: Understand what external forces will change the way we tune queries Objective 3: Learn the use of math, empty data and creative table expressions in changing the optimizer's mind. Objective 4: Learn what the long term implications are in using hints Objective 5: Understand the use of profiles for tuning more than one query at a time Improved SLAs Reduced MLC costs

DB 2 Resource Optimization is the Key Optimized DB 2 databases Reorg Advisors to Keep Data Perfect Reduced risk of bad SQL and Access Paths in the Peak SQL Performance Advisors Proactively report Degradation or Failure

More Resource Optimization • • Know if you are capped Know if you run CICS everywhere Scale Batch parallelism wisely REORG frequently Run checks frequently Run compares regularly Trap and fix expensive queries early

Digital Transformation

Sheryl M. Larsen BMC Sheryl_Larsen@bmc. com A 05 SQL Tuning in 2016 Please fill out your session evaluation before leaving!