Extreme Performance with Oracle Database 11g and In-Memory Parallel Execution

1
(No Transcript)
2
Extreme Performance with Oracle Database 11g and
In-Memory Parallel Execution
3
Agenda
ltInsert Picture Heregt

• Introduction to Parallel Execution
• Automatic Degree Of Parallelism
• Parallel Statement Queuing
• In Memory Parallel Execution
• Summary

4
ltInsert Picture Heregt
Introduction to Parallel Execution
5
How Parallel Execution works
Background process is spawned
User connects to the database
6
Parallel Execution Plan
SELECT c.cust_name, s.purchase_date,
s.amount FROM sales s, customers c WHERE
s.cust_id c.cust_id
ID Operation Name TQ IN-OUT PQ Distribution
0 SELECT STATEMENT                     
1   PX COORDINATOR
2    PX SEND QC RANDOM                          Q1,01 P-gtS
3     HASH JOIN                       Q1,01 PCWP
4           PX RECEIVE             Q1,01 PCWP
5 PX SEND BROADCAST                Q1,01 P-gtP BROADCAST
6      PX BLOCK ITERATOR          Q1,01 PCWP
7       TABLE ACCESS FULL CUSTOMERS Q1,01 PCWP
8   PX BLOCK ITERATOR   Q1,01 PCWP
9      TABLE ACCESS FULL            SALES Q1,01 PCWP
7
Parallel Execution of a Query
SELECT c.cust_name, s.date, s.amount FROM sales
s, customers c WHERE s.cust_id c.cust_id
Consumers
Producers
8
Producers and Consumer in the execution plan
ID Operation Name TQ IN-OUT PQ Distribution
0 SELECT STATEMENT                     
1   PX COORDINATOR
2    PX SEND QC RANDOM                          Q1,02 P-gtS
3     HASH JOIN                       Q1,02 PCWP
4           PX RECEIVE             Q1,02 PCWP
5 PX SEND HASH             Q1,00 P-gtP
6      PX BLOCK ITERATOR          Q1,00 PCWP
7       TABLE ACCESS FULL CUSTOMERS Q1,00 PCWP
8 PX RECEIVE Q1,02 PCWP
9 PX SEND HASH Q1,01 P-gtP
10   PX BLOCK ITERATOR   Q1,01 PCWP
11      TABLE ACCESS FULL            SALES Q1,01 PCWP
9
Parallel Execution of a Scan

• Data is divided into Granules
• block range or partition
• Each Parallel Server is assigned one or more
  Granules
• No two Parallel Servers ever contend for the same
  Granule
• Granules are assigned so that the load is
  balanced across all Parallel Servers
• Dynamic Granules chosen by the optimizer
• Granule decision is visible in execution plan

10
Identifying Granules of Parallelism during scans
in the plan
11
Enabling Parallel Execution

• There are three ways to enable parallel Execution
• Enable the table(s) for parallel execution
• alter table sales parallel
• alter table customers parallel
• Use a parallel hint
• select / parallel(c) parallel(s) /
• c.state_province, sum(s.amount) revenue
• from customers c, sales s
• where s.customer_id c.id
• and s.purchase_dateto_date('01-JAN-2007','DD-MON-
  YYYY')
• and c.country 'United States'
• group by c.state_province
• Use alter session force parallel query

12
Controlling Parallel Execution on RAC
Use RAC Services Create two services Srvctl add
service d database_name -s ETL
-r sid1, sid2 Srvctl add service d
database_name -s AHOC -r sid3,
sid4
Ad-Hoc queries
ETL
NoteNew Parameter to force a parallel statement
to run on just node the query was issued on
called PARALLEL_FORCE_LOCAL default FALSE
13
How could we enhance Parallel Execution?

• Current Issues
• Difficult to determine ideal DOP for each table
  without manual tuning
• One DOP does not fit all queries touching an
  object
• Not enough PX server processes can result in
  statement running serial
• Too many PX server processes can thrash the
  system
• Only uses IO resources

• Solution
• Oracle automatically decides if a statement
• Executes in parallel or not and what DOP it will
  use
• Can execute immediately or will be queued
• Will take advantage of aggregated cluster memory
  or not

14
ltInsert Picture Heregt
Automatic Degree of Parallelism
15
Automatic Degree of Parallelism

• Business Requirement
• Parallelism is completely manual
• Tuning typically required to determine ideal DOP
• Generally reserved for well-defined
  workload(large SQL)
• One DOP does not fit all queries touching an
  object

• Solution
• Oracle automatically decides if a statement
• Executes in parallel or not
• What DOP the statement will use

16
Automatic Degree of ParallelismAuto DOP

• Statement with elapse times less than the
  threshold go serial
• Statement with elapse times greater than
  threshold are candidates for Parallel
• Optimizer derives the DOP for the statement based
  on resource requirements for all scans operations
• Applies to all types of statements Query, DML, or
  DDL
• Explain plan has been enhanced to show DOP
  selected

17
How Auto Degree of Parallelism works
Statement is hard parsed And optimizer determines
the execution plan
SQLstatement
18
Controlling Auto DOP

• Controlled by two init.ora parameters
• PARALLEL_DEGREE_POLICY
• Controls whether or not auto DOP will be used
• Default is MANUAL which means no Auto DOP
• Set to AUTO to enable auto DOP
• PARALLEL_MIN_TIME_THRESHOLD
• Controls which statements are candidate for
  parallelism
• Default is 10 seconds
• PARALLEL_DEGREE_LIMIT
• Controls maximum DOP that can be used
• Default is CPU meaning DEFAULT DOP

19
Controlling Auto DOP

• Statement with an elapse time less than
  PARALLEL_MIN_TIME_THRESHOLD run serial
• Statement above threshold are candidate for
  parallelism
• Maximum DOP controlled by PARALLEL_DEGREE_LIMIT
• Default value is CPU
• PARALLEL_THREADS_PER_CPU X CPU_COUNT
• Actual DOP MIN(PARALLEL_DEGREE_LIMIT, ideal
  DOP)
• Statements excluded from auto-dop
• SQL issued from PL/SQL system packages
• Index rebuild, statistics collection etc
• Typically PL/SQL packages control their own
  parallelism

20
Explain plan enhancement for Auto DOP
PLAN_TABLE_OUTPUT
Plan hash value 2489314924 Plan hash value 2489314924 Plan hash value 2489314924 Plan hash value 2489314924 Plan hash value 2489314924 Plan hash value 2489314924 Plan hash value 2489314924 Plan hash value 2489314924 Plan hash value 2489314924
ID Operation Name Rows Bytes Cost Time Pstart Pstop
0 Select Statement 96000 9889 5 000001
1 PX COORDINATOR
2 PX SEND QC (RANDOM) TQ100 96000 9889 5 000001
3 PX BLOCK ITERATOR 96000 9889 5 000001
4 Table Access Full Sales 96000 9889 5 000001 1 16
Note - Computed Degree of Parallelism is 16
because of parallel threshold
21
ltInsert Picture Heregt
Parallel Statement Queuing
22
Parallel Statement Queuing

• Business Requirement
• With the introduction of Auto DOP
• More statements will run in parallel
• Possible to exhaust all parallel execution server
  processes
• Potential system thrashing due to too many
  processes

• Solution
• Parallel Statement Queuing
• Oracle automatically decides if a statement can
  execute immediately or not
• Prevents serializing parallel queries when
  parallel servers are not available
• Prevents system thrashing

23
Parallel Statement Queuing

• When a parallel statement starts checks if PX
  servers are available
• Let it run if there are enough PX servers
  available
• Queue the statement if there are not enough PX
  servers available
• Monitors RAC-wide availability of PX servers
• Adaptive to dynamic environments
• Services
• The service your session belongs to determines
  the limits on queuing
• Cluster reconfiguration
• Queue is aware of nodes leaving and joining the
  cluster and adjusts the limits accordingly

24
How Parallel Statement Queuing Works
Statement is parsed and oracle automatically
determines DOP
SQLstatements
If not enough parallel servers available queue
the statement
FIFO Queue
If enough parallel servers available execute
immediately
25
Controlling Parallel Statement Queuing

• Enabled when PARALLEL_DEGREE_POLICY is set to
  AUTO
• The Statement queue is enforced with a strict
  FIFO policy
• PARALLEL_SERVER_TARGET indicates how many PX
  servers are available to run queries before
  queuing kicks-in
• Default values 4 X PARALLEL_THREADS_PER_CPU X
  CPU_COUNT
• This a soft limit and does not replace
  PARALEL_MAX_SERVERS

Total PX servers available
PX server 1- 64 available to run queries before
queuing kicks in
26
Controlling Parallel Statement Queuing

• Two new hints
• To by-passes parallel statement queuing
• SELECT / NO_STMT_QUEUING / col1 FROM foo
• To delay statement execution until resources are
  available without having PARALLEL_DEGREE_POLICY
  is set to AUTO
• SELECT / STMT_QUEUING / col1 FROM foo
• VSQL_PLAN_MONITOR has a new status value for SQL
  that is queued
• SELECT s.sql_id, s.sql_text
• FROM vSQL_MONITOR m, vSQL s
• WHERE m.status'QUEUED
• AND m.sql_id s.sql_id
• Two new wait events
• PX Queuing Statement queue
• Indicates the first query in the queue
• ENQ JX SQL statement queue
• All other queries in the queue wait on this
  enqueue

27
Monitoring Statement Queuing in EM
Awaiting screen shot from EM
28
Monitoring Statement Queuing in EM
Wait event indicates stmt is at the head of the
queue
29
Monitoring Statement Queuing in EM
Wait event indicates stmt is queued
30
ltInsert Picture Heregt
In-Memory Parallel Execution
31
In-Memory Parallel Execution

• Business Requirement
• Traditionally Parallel Execution takes advantage
  of the IO capacity of a system
• Disk speeds are not keeping up with Moores law
  while CPU and Memory are

• Solution
• In-Memory Parallel Execution harness the memory
  capacity of the entire system
• Scan data nearly 10 X faster than scanning from
  disk

32
Prior to Oracle Database 11gR2Parallel Execution
and the buffer cache
33
Prior to Oracle Database 11gR2Parallel Execution
and the buffer cache
34
How In-Memory Parallel Execution Works

• Detect if the object fits in the aggregated
  buffer cache of the cluster
• If so, distribute affinitizes the blocks among
  the nodes and make PQ aware of the affinity
• If not, continue to by-pass the buffer cache and
  read directly from disk
• Subsequent access to the object will be conducted
  only by PX servers on the node to each the data
  was affinitized

35
In-Memory Parallel Execution
36
How In-Memory Parallel Execution Works in detail

• Decision to use the buffer cache is based on set
  of heuristics including
• Ratio between buffer cache size and object size
• Frequency at which the object is accessed
• How much the object changes between accesses
• In RAC fragments of the object are affinitized
  in the buffer cache on each of the active
  instances
• Affinity is based on FileNumber and ExtentNumber
  unless hash partitioned
• Automatically prevents multiple instances reading
  the same data from disk
• Only PX process on the same RAC node can access
  each of the fragments of the object

37
How In-Memory Parallel Execution Works
Determine the size of the table being looked at
SQLstatement
38
Controlling In-Memory Parallel Execution

• Controlled by PARALLEL_DEGREE_POLICY
• Active only when set to AUTO
• No way to turn it off

39
ltInsert Picture Heregt
Summary
40
New Parallel Execution Init.ora Parameters
Parameter Value Description
PARALLEL_DEGREE_LIMIT CPU Max DOP that can be selected with AUTO DOP
PARALLEL_DEGREE_POLICY MANUAL Specifies if AUTO DOP, Queuing, In-memory PE will be enabled
PARALLEL_FORCE_LOCAL FALSE Restricts parallel server processes to the node where query is issued
PARALLEL_MIN_TIME_THRESHOLD AUTO Specifies min execution time a statement should have before AUTO DOP will kick in
PARALLEL_SERVERS_TARGET 4CPU_COUNT PARALLEL_THREADS_PER_CPU ACTIVE_INSTANCES Specifies of parallel processes allowed to run parallel stmts before queuing will be use
PARALLEL_EXECUTION_MESSAGE_SIZE 16KB Specifies size of the message buffers used for communication
41
Gradually introduce Auto Parallel Execution

• PARALLEL_DEGREE_POLICY has three possible modes
• Manual
• As before, DBA must manually specify all aspects
  of parallelism
• No AUTO DOP, Stmt Queuing, In-Memory Parallel
  Execution
• Useful for well-understood existing applications
• Limited
• Restricted AUTO DOP for queries with tables
  decorated with default PARALLEL
• No Stmt Queuing, In-Memory Parallel Execution
• Useful in a mixed-world environment when a
  limited number of statements would benefit from
  parallel execution
• Auto
• All qualified statements subject to executing in
  parallel
• Statements can be queued
• IN-memory PQ available
• Useful when deploying new applications in 11g
  that would benefit from parallel execution

42
For More Information
search.oracle.com
Parallel Execution
Or http//www.oracle.com/technology/products/bi/db
/11g/pdf/twp_parallel_execution_fundamentals_11gr2
.pdf
43
Q A