supports the business DevOps: Providing critical velocity in software deployment by adopting DevOps practices Cloud: Using the disruptive nature of cloud for accelerated, cost-effective growth Databases: Ensuring databases are reliable, secure, available and continuously optimized Big Data: Harnessing the transformative power of data on a massive scale Advanced Analytics: Mining data for insights & business transformation using data science 4
conquer” algorithms are adapted for multi-processor architectures • Problem is break down into more than one sub-problem of same or related type • The solutions of each sub-problem are combined to give a solution to the original problem • Oracle’s technique: divide the data in granules and do the processing 8
full scans, partitioned index range scans and tables • Nested loops, hash joins, sort merge, star transformation • Queries and subqueries • DDL and DML statements INSERT, UPDATE, DELETE, CTAS, CREATE/REBUILD INDEX, MOVE/SPLIT/COALESCE PARTITION 10
used for single operation • Default DoP: PARALLEL_THREADS_PER_CPU * CPU_COUNT * INSTANCE_COUNT • Producer and consumer model = 2xDoP Acronym Description QC Query coordinator DoP Degree of parallelism TQ Table queue DFO Data flow operation DFO Tree Data flow operation tree 11
(foreground) process becomes the query coordinator • The DoP is determined before actual query execution • QC assigns each of the PX slaves with work • Starting 12c, one of the available PX servers will execute serial steps from the execution plan • Responsible to deliver the result to the client process 12
from previous example ? • DoP represents the number of processes (PX slaves) associated with a single operation (i.e. table scan, hash join etc.) • Due to the producer and consumer model, 2xDoP might be allocated. The DoP is allocated per set of slaves (max. of two sets) • The rule of 2xDoP is not always enforced. Example: single table scan needs only one set of PX processes 18
operation - basic unit of work carried by PX servers • Identified by PX SEND operation in the execution plan • Multiple DFO’s can appear • One DFO = 1 PX server set is being used • Multiple DFO’s = most of 2 PX server sets are being used • DFO’s are grouped under one DFO tree (PX coordinator) 19
non-correlated subqueries • Statement can allocate more PX servers than 2xDoP • Mostly seen with subqueries and temp table transformation operation • Each parallelizer can start at different time Example (DoP = 2): SELECT COL1 FROM T1 WHERE T1.COL1 > (SELECT AVG(COL1) FROM T2) AND T1.COL2 > (SELECT AVG(COL2) FROM T3) ORDER BY COL3, COL4 22
/*+ PARALLEL(n) */ ... FROM ... WHERE ... • Auto DoP • The optimizer determines the needed DoP Demo: Reading execution plans for PDML. Note: In 12c, DBMS_XPLAN notes if PDML is enabled or disabled for the current session. 28
exist (not all listed) • PDML is not supported on tables with triggers • PDML is not supported for some RI constraints (self-referential, delete cascade and deferred constraints) • Data is not accessible until commit or rollback • Cluster tables are not supported • Distributed transactions are not supported • PDML is not supported on tables with bitmap indexes or LOB columns if the table is not partitioned 29
level degree has precedence over object level degree • Statement level is controlled with query hints • Object level parallel degree definition does not support hints Demo: Object level defined DoP behavior 31
• Contains session-level information for parallel execution Some important columns: DFO_NUMBER – data flow tree TQ_ID – table queue SERVER_TYPE – producer, consumer, QC or ranger NUM_ROWS – number of rows processed BYTES – total bytes processed 32
skip 1 on instance select instance ,dfo_number ,tq_id ,server_type ,process ,num_rows ,round(ratio_to_report(num_rows) over (partition by dfo_number, tq_id, server_type) * 100) as pct” , round(bytes/power(1024,2)) as mb from v$pq_tqstat order by instance, dfo_number, tq_id, server_type desc, process 33
Your own session’s DoP can be determined by looking at V$PQ_TQSTAT and V$PQ_SESSTAT. But, those views are not relevant for other sessions. • Views: V$PX_SESSION, V$PX_PROCESS, V$TRANSACTION • SQL Monitor • Look at PX_FLAGS column in [G]V$ACTIVE_SESSION_HISTORY Get query’s actual DoP with TRUNC(PX_FLAGS/2097152) 35
• Sessions are easily identified with SADDR, SID and SERIAL# columns • DoP downgrades can be identified by looking at DEGREE and REQ_DEGREE columns • In RAC you can identify from which instance the QC is running • Identify how many PX servers run concurrently in case of multiple parallelizers 36
AVAILABLE or IN USE, the STATUS column • Operating system process ID can be identified from SPID column • Starting 12c, an indicator IS_GV has been added to easily identify if the PX process is a normal one or a GV$ process • Selects on GV$ views use PX processes named PZ9n across nodes 37
related columns: PTX_XIDUSN, PTX_XIDSLT, PTX_XIDSQN • Self-join V$TRANSACTION to identify PX processes specific per transaction Demo: Using V$TRANSACTION to map PX servers transaction to QC transaction. 38
the join key. - Improvement in 12c • BROADCAST: Used when one table is significantly smaller compared to other table. - Algorithm change in 12c • RANGE: Used when statement contains ORDER BY. Rows are distributed by ranges on the sort key. - Ranging is managed by QC at query runtime • ROUND - ROBIN: Randomly distributes rows to query servers. • QC (RANDOM): Rows are randomly sent to QC. Used when statement does not have ORDER BY. • QC (ORDER): Rows are sent to QC in order. Used when statement contains ORDER BY. Distribution methods 40
result sets is much smaller than the other • Data is duplicated to all PX consumer servers • Larger results set doesn’t have to be re-distributed • Statistics are very important to CBO to pick effective distribution 43
and T2 with 2M rows each • Table T2 has foreign key defined on T1_ID column referencing T1(ID) • In T2 table, ¾ of the rows for T1_ID column have duplicate value (skew data) Demo: Observation of PX slaves work performed on join with skew data. 57
at runtime • If number of rows < DoP*2 the source is broadcast • Identified by PX SEND HYBRID HASH and STATISTICS COLLECTOR operations in the execution plan 62
------------------------ ----------------------------------------------------------- ------ ----- _px_join_skew_handling enables skew handling for parallel joins TRUE TRUE _px_join_skew_ratio sets skew ratio for parallel joins 10 10 _px_join_skew_minfreq sets minimum frequency(%) for skewed value for parallel joins 30 30 BEGIN DBMS_STATS.GATHER_TABLE_STATS(NULL,’T2' ,METHOD_OPT=>’FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 254 T1_ID' ,NO_INVALIDATE=>FALSE); END; / 65 Demo: Observation of hybrid hash and PX slave work performed on join with skew data.
Howard Bloom, conceived in 1970 • Used to test whether an element is a member of a set • False positives are possible. False negatives are not possible • Composed of: • k – different hash functions (k1 , k2 ,k3 … km ) • Bit array of m – bits (b1 , b2 , b3 … bm ), initially all set to 0 66
between PX sets • The CBO decides whether it should use bloom filters • You can “control” bloom filter usage with a query hint: - PX_JOIN_FILTER - NO_PX_JOIN_FILTER • System or session wide control with "_bloom_filter_enabled” • Exadata smart-scan also benefits from bloom filters 68
size fits all does not work well • Critical statements may run serially due to not enough available PX servers • Manual tuning is needed to determine DoP • No way to increase DoP of running statements 72
in parallel or serial mode • Doesn’t require object level settings nor hints • Object level DoP is ignored • Statements can be scheduled to run immediately or wait in queue • Controlled by: PARALLEL_DEGREE_POLICY = MANUAL | LIMITED | AUTO | ADAPTIVE PARALLEL_MIN_TIME_THRESHOLD = AUTO | integer 73
= CPU | IO | integer • Application/user • Resource manager directive: PARALLEL_DEGREE_LIMIT_P1 What is the maximum Auto DoP ? MIN (DEFAULT DOP , PARALLEL_DEGREE_LIMIT , DBRM DIRECTIVE PARALLEL_DEGREE_LIMIT_P1) 74
CPU_COUNT * PARALLEL_THREADS_PER_CPU * 2 11g: 0 PARALLEL_DEGREE_POLICY PARALLEL_DEGREE_LIMIT PARALLEL_SERVERS_TARGET CPU count Queuing threshold Parallel max server GAP ? 76
some queries to skip the queue, hence “reserved” PX processes need to be available SELECT /*+ NO_STATEMENT_QUEUING */ ... FROM ... WHERE ALTER SESSION SET PARALLEL_DEGREE_POLICY = MANUAL|LIMITED DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE( ... PARALLEL_STMT_CRITICAL => ‘BYPASS QUEUE’ ) 77
Execute DoP 4 T1 ACTIVE 8 6 T2 ACTIVE 12 8 T3 ACTIVE 16 8 T4 ACTIVE 16 6 T5 ACTIVE 6 6 T6 ACTIVE 1* Free PX processes (PARALLEL_MAX_SERVERS = 64) Used PX processes *Be aware of PARALLEL_MIN_PERCENT. If it’s set and minimum is not met, ORA-12827 will occur. 84
to be used • Managed with PARALLEL_SERVERS_TARGET initialization parameter • It prevents statements to run serially. Solves “miss the boat” idiom. Statements can wait a little bit longer instead of running with downgraded degree of parallelism, possibly in serial • Without DBRM: one FIFO queue system-wide 85
Execute DoP 4 T1 ACTIVE 8 6 T2 ACTIVE 12 8 T3 ACTIVE 16 8 T4 ACTIVE 16 6 T5 ACTIVE 6 6 T6 QUEUED Free PX processes (PARALLEL_MAX_SERVERS = 64) Used PX processes 92
to limit DoP per consumer group • Defined as a percentage of PARALLEL_SERVERS_TARGET • Use PARALLEL_QUEUE_TIMEOUT directive to define how long to wait on queue • Define priorities between consumer groups with MGMT_Pn, otherwise sessions are managed in FIFO queue • Even if some sessions might not be queued, their PX servers allocation counts towards PARALLEL_SERVER_LIMIT • In RAC environment use services mapped to consumer groups to split work between instances 95
execution bypass the buffer cache • Data goes through buffer cache when: • Objects size is < 2% of DB_CACHE_SIZE • Objects are marked with CACHE option • Full segment scans due to DML operations 97
node’s buffer cache is shared between nodes via Cache Fusion. This can lead having whole copies of tables in each node’s buffer cache • The usable size of a buffer cache is the size of a single instance • Useful for small tables or dictionary metadata 98
node’s buffer caches serve as one big virtual buffer cache • Block images are not transferred between nodes (Cache Fusion) • PX servers run locally on the same node and results are transferred to the node requester via interconnect • Controlled with PARALEL_DEGREE_POLICY = AUTO | ADAPTIVE • If the working set does not fit into buffer cache, direct path reads are used 99
• Small tables are still cached but not via automatic big table caching • Reserves percentage from the buffer cache for table scans • Temperature and object-based algorithms are used to track object usage • For RAC environments this works only for parallel queries • For single instance it works for serial and parallel queries • At database level or CDB level set: DB_BIG_TABLE_CACHE_PERCENT_TARGET 100
ALTER SYSTEM SET PARALLEL_DEGREE_POLICY = AUTO; NAME TYPE VALUE ---------------------------------- ----------- ------- db_big_table_cache_percent_target string 50 parallel_degree_policy string AUTO 101
• If the data has to be processed by next (different) PX Slave set it has to be “parked” • True blocking operations • Artificial blocking operations - HASH JOIN BUFFERED 106
Event – based tracing is still possible, but not for all PX components • Getting event – based tracing with PX can be difficult. Multiple events needs to be set, plus multiple levels • MOS Note: 1097154.1 112
to some of event 10384 and some of 10390) • execution - (equivalent to some of event 10390) • granule - (equivalent to some of event 10390 and some of 10391) • messaging - (equivalent to event 10392 and event 10393) • buffer - (equivalent to event 10399) • compilation - ( no equivalent event) • all - all of the above • none - none of the above. 114
/ CPUs / parallel_max_servers • kkscscid_pdm_eval – check whether PDML is allowed • sessPdmlMode – check whether PDML is enabled in session • kxfrAllocSlaves(begin) – how many slaves are requested? • kxfrAllocSlaves(end) – will also show how PX slaves were actually allocated • kxfpAdaptDOP – adaptive DoP • DoP downgrade reason visible under qerpxSetupAndAllocSlaves() 117
adaptive DoP • 351 DoP downgrade due to resource manager max. DoP • 352 DoP downgrade due to insufficient number of processes • 353 DoP downgrade because slaves failed to join Demo: PX tracing to determine reason for DoP downgrade. 118
iarsov
keeg
kash1064
tokuty
prog893
onk
daidaidai
occhi
yamanoku
namiki64
kentarofujii
yazm
masakiokuda
geoffreycrofte
leimatthew05
vanstee
mlcsv
emna__ayadi
quramy
kneath
axbom
jct
minecr
stephenakadiri
tammyeverts
