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?

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
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
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

Database Performance Disruptors
Blame Shifter Performance Hog Query Blocker

Need Specific Tools For Performance
• How Do You Find The Fastest Way To Work?

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

Wait Event Information
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;
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 $$$
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
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
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
Query spent approx. 26
hours in database. 55% on
Memory/CPU. 40% on
direct path read

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

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

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

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

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

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

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

Execution – Actual vs Explain Plan
• Bind Variable Peeking Example and Adaptive Cursor Sharing

23
1st run with :p1=10 Changed after 2nd execution of :p1=40
Bind Variable Peeking / Adaptive Cursor Sharing

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;

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

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

Execution Plan using Optimizer Feature: SPM (baselines)
Select * from dba_sql_plan_baselines

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

Adaptive Plan example
New format options for
dbms_xplan are:
‘+adaptive’ – inactive steps
‘+report’ – reporting_only
• Adapted on first execution

Optimizer_Adaptive_reporting_only
alter session set optimizer_adaptive_reporting_only=TRUE;
select * from table(dbms_xplan.display_cursor('8qpakg674n4mz',0,format=>'+report'));

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;

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?

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);

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

Execution Plan

Understanding The Underlying Objects

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
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

• 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
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

Recommended

More Related Content

What's hot (20)

Viewers also liked (13)

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

More from SolarWinds (20)

Recently uploaded (20)

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