11'2 overview

1
(No Transcript)
2
Explaining the Explain Plan
3
Disclaimer

• The goal of this session to provide you with a
  guide for reading SQL execution plans and to help
  you determine if that plan is what you should be
  expecting
• This session will not provide you with sudden
  enlightenment making you an Optimizer expert or
  give you the power to tune SQL statements with
  the flick of your wrist!

4
Agenda

• What is an execution plan and how to generate one
• What is a good plan for the optimizer
• Understanding execution plans
• Cardinality
• Access paths
• Join order
• Join type
• Partitioning pruning
• Parallelism
• Execution plan examples

5
ltInsert Picture Heregt
What is an execution plan and how to generate one
6
What is an Execution plan?

• Execution plans show the detailed steps necessary
  to execute a SQL statement
• These steps are expressed as a set of database
  operators that consumes and produces rows
• The order of the operators and their
  implementation is decided by the optimizer using
  a combination of query transformations and
  physical optimization techniques
• The display is commonly shown in a tabular
  format, but a plan is in fact tree-shaped

7
What is an Execution plan?
Query SELECT prod_category, avg(amount_sold)FROM
sales s, products pWHERE p.prod_id
s.prod_idGROUP BY prod_categoryTabular
representation of plan ---------------------------
--------------------------------Id Operation
Name ------------------------------------------
-----------------0 SELECT STATEMENT 1 HASH
GROUP BY 2 HASH JOIN 3 TABLE ACCESS FULL
PRODUCTS4 PARTITION RANGE ALL 5
TABLE ACCESS FULL SALES ----------------------
------------------------------------
Tree-shaped representation of plan GROUP
BYJOIN______________
TABLE ACCESS TABLE ACCESS PRODUCTS
SALES
8
How to get an Execution Plan

• Two methods for looking at the execution plan
• EXPLAIN PLAN command
• Displays an execution plan for a SQL statement
  without actually executing the statement
• VSQL_PLAN
• A dictionary view introduced in Oracle 9i that
  shows the execution plan for a SQL statement that
  has been compiled into a cursor in the cursor
  cache
• Use DBMS_XPLAN package to display plans

Under certain conditions the plan shown with
EXPLAIN PLAN can be different from the plan shown
using VSQL_PLAN
9
How to get an Execution Plan

• Example 1 EXPLAIN PLAN command
  dbms_xplan.display function
• SQLgt EXPLAIN PLAN FOR
• SELECT prod_category, avg(amount_sold)
• FROM sales s, products p
• WHERE p.prod_id s.prod_id
• GROUP BY prod_category
• Explained
• SQLgt SELECT plan_table_output FROM
  table(dbms_xplan.display('plan_table',null,'basic'
  ))
• ------------------------------------------
  Id Operation Name ----------------------------
  --------------0 SELECT STATEMENT 1 HASH GROUP
  BY 2 HASH JOIN 3 TABLE ACCESS FULL
  PRODUCTS4 PARTITION RANGE ALL 5 TABLE
  ACCESS FULL SALES ------------------------------
  -------------

10
How to get an Execution Plan

• Example 2 Generate display execution plan for
  the last SQL stmts executed in a session
• SQLgtSELECT prod_category, avg(amount_sold) FROM
  sales s, products p WHERE p.prod_id
  s.prod_id GROUP BY prod_categoryno rows
  selectedSQLgt SELECT plan_table_outputFROM
  table(dbms_xplan.display_cursor(null,null,'basic')
  )
• ------------------------------------------
  Id Operation Name ----------------------------
  --------------0 SELECT STATEMENT 1 HASH GROUP
  BY 2 HASH JOIN 3 TABLE ACCESS FULL
  PRODUCTS4 PARTITION RANGE ALL 5 TABLE
  ACCESS FULL SALES ------------------------------
  -------------

11
How to get an Execution Plan

• Example 3 Displaying execution plan for any other
  statement from VSQL_PLAN
• DirectlySQLgt SELECT plan_table_output
  FROMtable(dbms_xplan.display_cursor('fnrtqw9c233t
  t',null,'basic'))
• IndirectlySQLgt SELECT plan_table_outputFROM
  vsql s, TABLE(dbms_xplan.display_cursor(s.sql_id,
  s.child_number, 'basic')) t WHERE s.sql_text
  like 'select PROD_CATEGORY'

Note More information on www.optimizermagic.blogsp
ot.com
12
DBMS_XPLAN parameters

• DBMS_XPLAN.DISPLAY takes 3 parameters
• plan table name (default 'PLAN_TABLE'),
• statement_id (default null),
• format (default 'TYPICAL')
• DBMS_XPLAN.DISPLAY_CURSOR takes 3 parameters
• SQL_ID (default last statement executed in this
  session),
• Child number (default 0),
• format (default 'TYPICAL')
• Format is highly customizable
• Basic
• Typical
• All
• Additional low level parameters show more detail

13
ltInsert Picture Heregt
What is a good plan for the optimizer
14
Whats a Good Plan for the Optimizer?

• The Optimizer has two different goals
• Serial execution Its all about cost
• The cheaper, the better
• Parallel execution its all about performance
• The faster, the better
• Two fundamental questions
• What is cost?
• What is performance?

15
What is Cost?

• A magically number the optimizer makes up?
• Resources required to execute a SQL statement?
• Result of complex calculations?
• Estimate of how long it will take to execute a
  statement?

16
What is performance?

• Getting as many queries completed as possible?
• Getting fastest possible elapsed time using the
  fewest resources?
• Getting the best concurrency rate?

• Actual Definition
• Performance is fastest possible response time for
  query
• Goal is to complete the query as quickly as
  possible
• Optimizer does not focus on resources needed to
  execute the plan

17
ltInsert Picture Heregt
Understanding an Execution Plan
18
SQL Execution Plan

• When looking at a plan can you determine if the
  following is correct?
• Cardinality
• Are the correct number of rows coming out of each
  object?
• Access paths
• Is the data being accessed in the best way? Scan?
  Index lookup?
• Join order
• Are tables being joined in the correct order to
  eliminate as much data as early as possible?
• Join type
• Are the right join types being used?
• Partitioning pruning
• Did I get partition pruning? Is it eliminating
  enough data?
• Parallelism

19
Cardinality

• What is it?
• Estimate of number rows that will be returned
• Cardinality for a single value predicate
  num_rows total / num_distinct total
• E.g. 100 rows total, 10 distinct values gt
  cardinality10 rows
• OR if histogram present num_rows Density

• Why should you care?
• Influences access method and Join Order
• If estimate is off it can have a huge impact on a
  plan

• What causes Cardinality to be wrong?
• Data Skews
• Multiple single column predicates on a table
• A function wrapped where clause predicate

20
Cardinality or Selectivity
21
Data Skew

• Cardinality num_rows / num_distinct
• If there is a data skew the selectivity could be
  way off
• Create a histogram to correct the selectivity
  calculation
• Oracle automatically creates a histogram if it
  suspects a data skew

• Be careful
• Histograms have an interesting side effects on
  statements with binds
• Less relevant for data warehousing
• Prior to 11g stmt with binds had only one plan
  based on first literal value
• But presence of a histogram indicate skew
  unlikely one plan good for all bind values
• In 11g multiple execution plans allowed for a
  single statement

22
Multiple Single Column Predicates

• Optimizer always assumes each additional
  predicate increases the selectivity
• Selectivity of predicate 1 selectivity of
  predicate 2 etc
• But real data often shows correlations
• Job title influences salary, car model influences
  make
• How do you tell the Optimizer about the
  correlation?

• Extended Optimizer Statistics provides a
  mechanism to collect statistics on a group of
  columns
• Full integration into existing statistics
  framework
• Automatically maintained with column statistics
• Instantaneous and transparent benefit for any
  migrated application

23
A function Wrapped Where Clause Predicate

• SELECT
• FROM customers
• WHERE lower(country_id) 'us'
• Applying a function to a column means the
  optimizer does not know how it will effect the
  cardinality
• Most likely the optimizer will under-estimate the
  cardinality
• Creating extended statistics for this function
  allows the optimizer to get the correct
  cardinality
• exec dbms_stats.gather_table_stats(sh,'customers
  ', method_opt gt -
• 'for all columns size skewonly for
  columns(lower(country_id))')

24
Access Paths

• How to get data out of the table
• The access path can be
• Full table scan
• Table access by Rowid
• Index unique scan
• Index range scan (descending)
• Index skip scan
• Full index scan
• Fast full index scan
• Index joins
• Bitmap indexes

25
Access Path
If you know the wrong access method is being used
check cardinality, join order
26
Access Path examples
A table countries contains 10K rows has a
primary key on country_id What plan would you
expect for these queries?
Select country_id, name from countries where
country_id in ('AU','FR','IE)
Select country_id, name from countries where
country_id between 'AU' and 'IE'
Select country_id, name from countries where
name'USA'
27
Join Type

• A Join retrieve data from more than one table
• Possible join types are
• Nested Loops joins
• Hash Joins
• Partition Wise Joins
• Sort Merge joins
• Cartesian Joins
• Outer Joins

28
Join Type Example 1

• What Join type should be use for this Query?
• SELECT e.name, e.salary, d.dept_name
• FROM hr.employees e, hr.departments d
• WHERE d.dept_name IN ('Marketing,'Sales')
• AND e.department_idd.department_id
• Employees has 107 rows
• Departments has 27 rows
• Foreign key relationship between Employees and
  Departments on dept_id

29
Join Type Example 2

• What Join type should be use for this Query?
• SELECT o.customer_id, l.unit_price l.quantity
• FROM oe.orders o ,oe.order_items l
• WHERE l.order_id o.order_id
• Orders has 105 rows
• Order Items has 665 rows

30
Join Type Example 3

• What Join type should be use for this Query?
• SELECT o.order_id,0.order_date,e.name
• FROM oe.orders o , hr.employees e
• Orders has 105 rows
• Employees has 107 rows

31
Join Type Example 4

• What Join type should be use for this Query?
• SELECT d.department_id,e.emp_id
• FROM hr.employees e FULL OUTER JOIN
  hr.departments d
• ON e.department_id d.department_id
• Employees has 107 rows
• Departments has 27 rows
• Foreign key relationship between Employees and
  Departments on dept_id

32
Join Type
If the wrong join type is used go back and check
the stmt is written correctly and the cardinality
estimates are accurate
33
Join Orders

• The order in which the tables are join in a multi
  table stmt
• Ideally start with the table that will eliminate
  the most rows
• Strongly effected by the access paths available
• Some basic rules
• Joins that definitely results in at most one row
  always go first
• When outer joins are used the table with the
  outer join operator must come after the other
  table in the predicate
• If view merging is not possible all tables in the
  view will be joined before joining to the tables
  outside the view

34
Join order
1
2
3
If the join order is not correct, check the
statistics, cardinality access methods
35
Partition Pruning
Q What was the total sales for the weekend of
May 20 - 22 2008?
Select sum(sales_amount) From SALES Where
sales_date between to_date(05/20/2008,MM/DD/YYY
Y) And to_date(05/23/2008,MM/DD/YYYY)
36
Partition Pruning
If you see the word KEY listed it means the
partitions touched will be decided at Run Time
37
Bloom Filter
7. Hash JoinConsumers will complete the hash
join by probing into the hash table from the time
time to find actual matching rows
6. Reduced row sentOnly rows that have a match
in the bit vector get sent to the consumers
4. Bloom Filter sendBIT VECTOR is sent as an
additional filter criteria to the scan of the
sales table
1. Table scanTime table is scanned and sent
3. Table ScanSales table is scan and rows are
filtered based on query predicates
38
Parallelism

• Goal is to execute all aspects of the plan in
  parallel
• Identify if one or more sets of parallel server
  processes are used
• Producers and Consumers
• Identify if any part of the plan is running serial

39
Parallelism
If you see any lines beginning with the letter S
you are running Serial check DOP for each table
index used
40
Identifying Granules of Parallelism during scans
in the plan

• Data is Partitioned into Granules either
• block range
• Partition
• Each parallel server is allocated one or more
  granules
• The granule method is specified on line above the
  scan in the operation section

41
Identifying Granules of Parallelism during scans
in the plan
42
Access Paths and how they are parallelized
43
Parallel Distribution

• Necessary when producers consumers sets are
  used
• Producers must pass or distribute their data
  into consumers
• Operator into which the rows flow decides the
  distribution
• Distribution can be local or across other nodes
  in RAC
• Five common types of redistribution

44
Parallel Distribution

• HASH
• Assumes one of the tables is hash partitioned
• Hash function applied to value of the join column
• Distribute to the consumer working on the
  corresponding hash partition
• Broadcast
• The size of one of the result sets is small
• Sends a copy of the data to all consumers
• Range
• Typically used for parallel sort operations
• Individual parallel servers work on data ranges
• QC doesnt have to sort just present the
  parallel server results in the correct order
• Partitioning Key Distribution PART (KEY)
• Assumes that the target table is partitioned
• Partitions of the target tables are mapped to the
  parallel servers
• Producers will map each scanned row to a consumer
  based on the partitioning column
• Round Robin
• Randomly but evenly distributes the data among
  the consumers

45
Parallel Distribution
Shows how the PQ servers distribute rows between
each other
46
ltInsert Picture Heregt
Example of reading a plan
47
Example SQL Statement and Block Diagram

• SELECT '(' pcode ')' pcode_desc AS
  PRODUCT, CNT
• FROM (SELECT a.pcode, b.pcode_desc,
  count(a.pcode) CNT
• FROM BMG.t_acct_master_hd a
• ,BMG.hogan_pcode_hd_ref b
• ,BMG.t_tran_detail_hd c
• WHERE a.pcode b.pcode
• AND a.acct_numc.acct_num
• AND a.co_idc.co_id
• AND c.asof_yyyymm200102
• AND c.tran_amt lt2000000000
• GROUP BY a.pcode , b.pcode_desc
• ORDER BY a.pcode , b.pcode_desc )

Multiple Terabytes
1 Gigabyte in size
48
Example Contd Execution plan
49
Example Contd Execution plan
50
Example Contd Execution plan
51
Example Contd Execution plan - Solution
52
Determining if you get the right plan
Query SELECT quantity_soldFROM sales s,
customers cWHERE s.cust_id c.cust_idID What do
you expect the plan to look like for this
statement?S NOT NULL)
53
Q A