Stop the Chaos! Get Real Oracle Performance by Query Tuning Part 1

Stop the Chaos! Get Real Oracle Performance by Query Tuning – Part 1 Janis Griffin Senior DBA / Performance Evangelist

© 2017 SolarWinds Worldwide, LLC. All rights reserved. Who Am I • Senior DBA / Performance Evangelist for SolarWinds • Janis.Griffin@solarwinds.com • Twitter® - @DoBoutAnything • Current – 25+ Years in Oracle®, DB2®, ASE, SQL Server®, MySQL® • DBA and Developer • Specialize in Performance Tuning • Review Database Performance for Customers and Prospects • Common Question – How do I tune it?

© 2017 SolarWinds Worldwide, LLC. All rights reserved. Agenda • Identify Database Performance Disruptors • Blame shifter • Performance hog • Query blocker • Quickly Find Which Queries To Focus On • Collect and understand wait events • Learn how to utilize wait or response time analysis • Review The Execution Plan – How It Works • Identify costly steps • Think like the optimizer • Understand the importance of statistics • Look Forward To What’s Next – Part 2

Day In The Life Of A DBA © 2017 SolarWinds Worldwide, LLC. All rights reserved. SQL and Application Management Storage and Space Management Backup And Recovery Security Policies System Resource Management

Conventional Tools Measure Database Health Not Performance • Unclear View of Performance leads to finger pointing © 2017 SolarWinds Worldwide, LLC. All rights reserved. NAME VALUE ----------------------------------- ---------- IMU undo allocation size 3112037544 consistent gets pin 2846303217 consistent gets pin (fastpath) 2842057257 no work - consistent read gets 2723822406 table scan blocks gotten 2066139012 sorts (rows) 1833907235 process last non-idle time 1506459305 session connect time 106451365 bytes sent via SQL*Net to client 1477573385 buffer is pinned count 1225559730 IMU Redo allocation size 1168332372 buffer is not pinned count 854860367 enqueue requests 808400126 enqueue releases 808392500 redo wastage 775319024 TBS Extension: bytes extended 734003200 table fetch by rowid 642993391 recursive calls 626797186 ETC… Database

Monitor Wait Time To Get Total Response Time • Understand the total time a Query spends in Database • Measure time while Query executes • Oracle helps by providing Wait Events © 2017 SolarWinds Worldwide, LLC. All rights reserved.

Wait Event Information © 2017 SolarWinds Worldwide, LLC. All rights reserved. V$SESSION SID SERIAL# USERNAME MACHINE PROGRAM MODULE ACTION CLIENT_INFO SQL_ID SQL_CHILD_NUMBER EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3 STATE (WAITING, WAITED) BLOCKING_SESSION V$SQL SQL_ID SQL_FULLTEXT PLAN_HASH_VALUE CHILD_NUMBER IS_BIND_SENSITIVE IS_BIND_AWARE IS_SHAREABLE SQL_PROFILE SQL_PATCH SQL_PLAN_BASELINE BIND_DATA IS_REOPTIMIZABLE IS_RESOLVED_ADAPTIVE_PLAN V$SQL_PLAN SQL_ID PLAN_HASH_VALUE CHILD_NUMBER OPERATION OBJECT_NAME OTHER_XML V$SQL_BIND_CAPTURE SQL_ID NAME VALUE_STRING DATATYPE_STRING LAST_CAPTURED V$SQLAREA SQL_ID EXECUTIONS PARSE_CALLS DISK_READS BUFFER_GETS DBA_OBJECTS OBJECT_ID OBJECT_NAME OBJECT_TYPE

Base Query – Not Rocket Science INSERT INTO rta_data SELECT sid, serial#, username, program, module, action, machine, osuser, sql_id, blocking_session, decode(state, 'WAITING', event, 'CPU') event, p1, p1text, p2, p2text, p3, p3text, SYSDATE date_time FROM V$SESSION s WHERE s.status = 'ACTIVE' AND wait_class != 'Idle' AND username != USER; © 2017 SolarWinds Worldwide, LLC. All rights reserved. SELECT rta.sql_id, rta.event, COUNT(*) time_in_second, tot_time FROM rta_data rta, (SELECT sql_id, COUNT(*) tot_time FROM rta_data GROUP BY sql_id) tot WHERE rta.sql_id = tot.sql_id GROUP BY rta.sql_id,rta.event, tot_time ORDER BY tot_time,rta.sql_id, time_in_second;

Active Session History (ASH) • V$ACTIVE_SESSION_HISTORY • Data warehouse for session statistics • Oracle 10g and higher • Data is sampled every second • Holds at least one hour of history • Never bigger than: • 2% of SGA_TARGET • 5% of SHARED_POOL (if automatic sga sizing is turned off) • WRH$_ACTIVE_SESSION_HISTORY • Above table gets flushed to this table • AKA – dba_hist_active_sess_history • Need Tuning & Diagnostics Packs • On Enterprise Only • Costs extra $$$ © 2017 SolarWinds Worldwide, LLC. All rights reserved. SELECT summary.sql_id, event, sql_text, event_time_in_seconds, tot_time_in_seconds FROM (SELECT a.sql_id, DECODE(a.session_state, 'WAITING', a.event, 'ON CPU') event, SUBSTR(v.sql_text,1,30) sql_text, SUM(a.wait_time + a.time_waited)/1000000 event_time_in_seconds FROM v$active_session_history a, v$sqlarea v, dba_users u WHERE a.sample_time BETWEEN SYSDATE - 1 AND SYSDATE AND a.sql_id = v.sql_id AND a.user_id = u.user_id AND u.username <>'SYS' GROUP BY a.sql_id, DECODE(A.session_state, 'WAITING', a.event, 'ON CPU'), SUBSTR(v.sql_text,1,30)) detail, (SELECT sql_id, SUM(wait_time + time_waited)/1000000 tot_time_in_seconds FROM v$active_session_history WHERE sample_time BETWEEN SYSDATE - 1 AND SYSDATE GROUP BY sql_id) summary WHERE detail.sql_id = summary.sql_id ORDER by tot_time_in_seconds, sql_id, event_time_in_seconds

Wait Time Analysis Focus on queries spending the most time in the database © 2017 SolarWinds Worldwide, LLC. All rights reserved. Almost 1 hour of wait time. 60% on resmgr:cpu quantum 30% on Memory/CPU 10% on db file sequential read

My Top 10 Wait Events • From my experience: • There is a small list of wait events you need to know well • The other 1500+ you can Google or ask Oracle • Need to know: • Causes of these waits • How to reduce / fix these waits • Top Waits Recorded Webinar © 2017 SolarWinds Worldwide, LLC. All rights reserved. 1. db file sequential read 2. db file scattered read 3. CPU 4. log file sync 5. read by other session 6. db file parallel read 7. direct path read / write 8. direct path read / write temp 9. enq: TX - row lock contention 10. cursor: pin S wait on X

Benefits of Wait Time Analysis – Cont. • Get baseline metrics • How long does it take now • What is acceptable (10 sec, 2 min, 1 hour) • Get number of Buffer Gets • Measurement to compare against while tuning • Collect Wait Event Information • Locking / Blocking (enq) • I/O problem (db file sequential read) • Latch contention (latch) • Network slowdown (SQL*Net) • May be multiple issues • All have different resolutions © 2017 SolarWinds Worldwide, LLC. All rights reserved.

Other Benefits: Customer Query Suddenly Runs Slower • Three different plans in 30 days. Performance regressed with plan 3776626630 Why is query, which runs once a day, suddenly taking so long? Sudden plan change © 2017 SolarWinds Worldwide, LLC. All rights reserved.

Review the Execution Plan • EXPLAIN PLAN • Estimated plan - can be wrong for many reasons • Best Guess, Blind to Bind Variables or Data types • Explain Plan For … sql statement & DBMS_XPLAN.display • Set autotrace (on | trace | exp | stat | off) • Tracing (all versions) / TKPROF • Get all sorts of good information • Works when you know a problem will occur • V$SQL_PLAN (Oracle 9i+) • Actual execution plan • Use DBMS_XPLAN.display_cursor for display • Historical Plans – AWR, Solarwinds DPA • Shows plan changes over time © 2017 SolarWinds Worldwide, LLC. All rights reserved.

How an Execution Plan is Created Query Transformer – rewrites query to be more efficient Plan Generator – creates multiple plans using different access paths & join types. Plan with lowest cost is chosen Estimator – looks at selectivity, cardinality & cost Data Dictionary Schema Definition Statistics Etc… Parsed Query (from Parser) Transformed Query Query + Estimates Default Plan sent to Row Source Generator to create execution plan Init.ora parameter to control behavior: OPTIMIZER_FEATURES_ENABLED © 2017 SolarWinds Worldwide, LLC. All rights reserved.

Execution Plan Steps • Show the sequence of operations performed to run SQL Statement • Order of the tables referenced in the statements • Access method for each table in the statement • INDEX • INLIST ITERATOR • TABLE ACCESS • VIEW • Join method in statement accessing multiple tables • HASH JOIN • MERGE JOIN • NESTED LOOPS • Data manipulations • CONCATENATION • COUNT • FILTER • SORT • Statistic Collectors • New in 12C © 2017 SolarWinds Worldwide, LLC. All rights reserved.

Examine the Execution Plan • Find Expensive Operators • Examine cost, row counts and time of each step • Look for full table or index scans • Review the Predicate Information • Know how bind variables are being interpreted • Review the data types • Implicit conversions • Know which step filtering predicate is applied • Review the Join Methods • Nested Loops – good for large table / small table (lookup) joins • Hash Joins – good for large table / large table joins • Check out the Notes Section • They are becoming increasingly important © 2017 SolarWinds Worldwide, LLC. All rights reserved.

Execution Plan Details SELECT e.empno EID, e.ename "Employee_name", d.dname "Department", e.hiredate "Date_Hired" FROM emp e, dept d WHERE d.deptno = :P1 AND e.deptno = d.deptno; Actual Plan: V$SQL_PLAN using dbms_xplan.display_cursor © 2017 SolarWinds Worldwide, LLC. All rights reserved.

24 Gather Information on Expensive Steps • Understand objects in execution plans • Table Definitions & Segment sizes • Is it a View? • Get underlying definition • Number of Rows / Partitioning • Examine Columns in Where Clause • Cardinality of columns • Data Skew / Histograms • Statistic Gathering • Tip: Out-of-date statistics can impact performance • Use TableTuningStats.sql • Run it for expensive data access targets • OracleTuningStats.sql SELECT e.empno EID, etc… FROM emp e, dept d WHERE d.deptno = :P1 AND e.deptno = d.deptno; © 2017 SolarWinds Worldwide, LLC. All rights reserved.

© 2017 SolarWinds Worldwide, LLC. All rights reserved. Review Indexes & Constraints • Get Index definitions • Know the order of columns and their selectivity • Review existing keys and constraints • Know Multi-Table Relationships (ERD) • Primary key and foreign definitions • Check and not null constraints • Make sure the optimizer can use the index • Functions on indexed columns can turn off index • Consider a function index • Is the index INVISIBLE? • Look for implicit conversions • Get sample bind variable values SELECT name, position, datatype_string, value_string FROM v$sql_bind_capture WHERE sql_id = '0zz5h1003f2dw’; FREE - Oracle SQL Developer Data Modeler: Oracle SQL Developer Data Modeler Tip: Keys & constraints help the optimizer create better execution plans

Know Which Optimizer Features You are Using • Show parameter optimizer • What is supporting the Execution Plan • SQL Plan Management (Baselines) / Profiles / Outlines / Patches • Dynamic Statistics, Statistics Feedback or SQL Directives • Adaptive Cursor Sharing • Adaptive Plans • 12C Optimizer and Plan Stability • Notes Section gives you clues Adaptive Query Optimizer Adaptive Plans Adaptive Statistics Join Methods Parallel Distribution Dynamic Statistics Automatic Reoptimization Sql Plan Directives © 2017 SolarWinds Worldwide, LLC. All rights reserved.

History of Optimizer Features • Oracle 7+ – Introduced cost-based optimizer • Allowed for Hash joins, Histograms, Partitioning & Parallel queries • Required statistics gathering • Quickly found out that plans could change over time • 8.1.7+ Stored Outlines to control plan changes • Oracle 10g – SQL Profiles / Tuning Advisor • Sub-optimal execution plans still generated • Performance Regression overtime - No Evolution • DBMS_SQLTUNE – Costs $$$ • Oracle 11 – SQL Patches, SQL Plan Management (Baselines) & Adaptive Cursor Sharing • SQL Patches free both in Standard or Enterprise • Baselines free with Enterprise • Oracle 12C – Adaptive Optimizer • Allows for automatic plan evolution & SPM Evolve Advisor © 2017 SolarWinds Worldwide, LLC. All rights reserved.

Execution Plan using Adaptive Optimizer (12c) SELECT sql_id, child_number, SUBSTR(sql_text, 1,30) sql_text, IS_RESOLVED_ADAPTIVE_PLAN, IS_REOPTIMIZABLE FROM v$sql WHERE sql_text like 'select /* jg */%' ORDER BY sql_id,child_number • IS_REOPTIMIZABLE is for next execution • Y - the next execution will trigger a reoptimization • R – has reoptimization info but won’t trigger due to reporting mode • N -the child cursor has no reoptimization info select /* jg */ p.product_name from order_items o, product p where o.unit_price = :b1 and o.quantity > :b2 and o.product_id = p.product_id; Adaptive Query Optimizer Adaptive Plans Adaptive Statistics Join Methods Parallel Distribution Dynamic Statistics Automatic Reoptimization Sql Plan Directives © 2017 SolarWinds Worldwide, LLC. All rights reserved.

Optimizer_Adaptive_reporting_only alter session set optimizer_adaptive_reporting_only=TRUE; select * from table(dbms_xplan.display_cursor('8qpakg674n4mz',0,format=>'+report')); © 2017 SolarWinds Worldwide, LLC. All rights reserved.

Understand Statistics gathering • GATHER_*_STATS procedures have many parameters • Should only set 2-4 parameters (per Tom Kyte) • SCHEMA NAME • TABLE NAME • PARTITION NAME • DOP • Defaults for: exec dbms_stats.gather_schema_stats(‘SOE’); New GET_PREFS function DBMS_STATS package • Rewritten in 11g • A Faster & better AUTO_SAMPLE_SIZE • 100% in less time & more accurate than 10% estimate • Avoid using ESTIMATE_PERCENT select dbms_stats.get_prefs('ESTIMATE_PERCENT') from dual; © 2017 SolarWinds Worldwide, LLC. All rights reserved.

Optimizer now tries to fix Statistics Mistakes • Dynamic Statistics • Missing, Insufficient, Stale Statistics or Parallel Execution • New level 11 in 12c • alter session set OPTIMIZER_DYNAMIC_SAMPLING = 11; • Statistics Feedback • Collectors sample statistics on 1st execution • Default stats compared with actual rows sampled • If they differ significantly, optimizer stores correct estimates for future use • Stored in OPT_ESTIMATE hints in V$SQL_REOPTIMIZATION_HINTS • SQL Plan Directives • Additional info for missing column group statistics or histograms • Stored in DBA_SQL_PLAN_DIRECTIVES and DBA_SQL_PLAN_DIR_OBJECTS • Dynamic sampling performed on directive • Until statistics are gathered for the column group (e.g. City / State / Country) • Not tied to a specific sql statement – defined on a query expression What wrong with these pictures? © 2017 SolarWinds Worldwide, LLC. All rights reserved.

© 2017 SolarWinds Worldwide, LLC. All rights reserved. Case Study – Current Pay Check For Specific Employees SELECT e.first_name, e.last_name, l.region_name FROM emp e INNER JOIN dept d ON e.department_id = d.department_id INNER JOIN loc l ON l.location_id = d.location_id WHERE (e.last_name LIKE :b1) AND e.employee_id IN ( SELECT employee_id FROM wage_pmt w WHERE w.employee_id = e.employee_id AND w.pay_date>= trunc(sysdate)-31);

© 2017 SolarWinds Worldwide, LLC. All rights reserved. Wait Time Analysis Almost 100% on PGA memory allocation wait. New wait event in 12.2 – not documented. No statistics, Unique indexes Added PKs and Fks

ACCEPT SQL_ID CHAR PROMPT 'Enter SQL_ID> ' DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => '&sql_id', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 60, task_name => '&sql_id', description => 'Tuning task for Current Paycheck'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '&sql_id'); SELECT DBMS_SQLTUNE.report_tuning_task('&sql_id') AS recommendations FROM dual; EXEC DBMS_SQLTUNE.drop_tuning_task('&sql_id'); • Stay tuned for Part 2 of this Webinar Series © 2017 SolarWinds Worldwide, LLC. All rights reserved. Ask The Tuning Advisor No Recommendations

Summary • Quickly Identify Database Performance Disruptors • Using health metrics alone can be misleading • Make sure you are tuning the correct query • Use wait time analysis • Monitor each step for time and resource being used • Understand The Execution Plan • Focus on the costly steps • Know what the optimizer knows • Review the underlying statistics • Monitor to see your tuning improvements • Next up – Part 2 • More tuning techniques and common coding mistakes • What to do if you can’t change the code © 2017 SolarWinds Worldwide, LLC. All rights reserved.

• Try Database Performance Analyzer FREE for 14 days • Improve root cause of slow performance • Quickly identify root cause of issues that impact end-user response time • See historical trends over days, months, and years • Understand impact of VMware® performance • Agentless architecture with no dependence on Oracle Packs, installs in minutes © 2017 SolarWinds Worldwide, LLC. All rights reserved. Resolve Performance Issues quickly—Free Trial www.solarwinds.com/dpa-download/

The SolarWinds, SolarWinds & Design, Orion, and THWACK trademarks are the exclusive property of SolarWinds Worldwide, LLC or its affiliates, are registered with the U.S. Patent and Trademark Office, and may be registered or pending registration in other countries. All other SolarWinds trademarks, service marks, and logos may be common law marks or are registered or pending registration. All other trademarks mentioned herein are used for identification purposes only and are trademarks of (and may be registered trademarks) of their respective companies. Thank You!!!

Stop the Chaos! Get Real Oracle Performance by Query Tuning Part 1

In this document

More Related Content

What's hot

Viewers also liked

Similar to Stop the Chaos! Get Real Oracle Performance by Query Tuning Part 1

More from SolarWinds

Recently uploaded

Stop the Chaos! Get Real Oracle Performance by Query Tuning Part 1