Hands-Free Optimization of Complex Queries in

Download Report

Transcript Hands-Free Optimization of Complex Queries in

Slide 1

Parallelize or Perish!
Implementing Oracle 11gR2 Parallelism
Features for Maximum Performance

Jim Czuprynski
Zero Defect Computing, Inc.
April 19, 2013
Copyright 2013, Zero Defect Computing, Inc.


Slide 2

My Credentials
 30+ years of database-centric IT experience

 Oracle DBA since 2001
 Oracle 9i, 10g, 11g OCP
 95+ articles on databasejournal.com and ioug.org

 Teach core Oracle DBA courses (G/I+RAC, Exadata,

Performance Tuning, Data Guard)
 2009: Oracle Education Partner Trainer of the Year
 Speaker at Oracle OpenWorld, IOUG
COLLABORATE11, and IOUG COLLABORATE13
 Oracle-centric blog (Generally, It Depends)
Copyright 2013, Zero Defect Computing, Inc.

-2-


Slide 3

Our Agenda
In this session , we will:
 Explore the new parallelism features in Oracle Database

11.2.0.2, especially ADOP and IMPE
 Demonstrate how to implement appropriate initialization
parameters, hints, and object decoration to influence
parallelism for maximum effectiveness
 Demonstrate how to use the feature set of the new
DBMS_PARALLEL_EXECUTE package to leverage
massive parallelism for DML statements

Copyright 2013, Zero Defect Computing, Inc.

-3-


Slide 4

So … Why Parallelize?
 Because we can
 CPU resources aren’t as limited as in the past
 Engineered systems make this even more possible
 Because we should
 Doing twice (or 4X or 8X or 16X) as much work in same
amount of time may be the only way to satisfy SLAs
 Degrees of parallelism are readily controllable with DBRM
 Because we must
 “You’ll just have to do more with less.”
 Our CIOs and CFOs need to justify CAPEX / OPEX

Copyright 2013, Zero Defect Computing, Inc.

-4-


Slide 5

Parallelizing Single
Operations


Slide 6

Auto Degree of Parallelism (ADOP)
 Parallelism is automatically determined when query

is parsed
 Objects can either be “decorated” with appropriate

degrees of parallelism …
 … or the optimizer can automatically determine the
appropriate parallelism based on resources available

 Also possible to limit queries to a maximum degree

of parallelism
 Services offer ability to parallelize data warehousing
application workloads across multiple RAC instances
for improved response times
 No changes to existing SQL code required!
Copyright 2013, Zero Defect Computing, Inc.

-6-


Slide 7

Object “Decoration”
 Tables and indexes can now be “decorated” with a

desired degree of parallelism
 Objects can be decorated during creation, or
decoration can be added later, via the ALTER
PARALLEL clause:
SQL> ALTER TABLE ap.randomized_sorted PARALLEL 4;

 ADOP considers defined parallelism of decorated

objects when constructing a parallel execution plan
 Beware: The decorated parallelism degrees are no
guarantee of the actual degrees of parallelism that
will be used
Copyright 2013, Zero Defect Computing, Inc.

-7-


Slide 8

Parameters Controlling ADOP
Five key initialization parameters influence ADOP in
Oracle Database 11.2.0.2:
 PARALLEL_DEGREE_POLICY
 PARALLEL_DEGREE_LIMIT
 PARALLEL_MIN_TIME_THRESHOLD
 PARALLEL_SERVERS_TARGET
 PARALLEL_FORCE_LOCAL
Other initialization parameters are now deprecated or
retasked as part of ADOP in Oracle Database 11.2.0.2

Copyright 2013, Zero Defect Computing, Inc.

-8-


Slide 9

PARALLEL_DEGREE_POLICY
PARALLEL_DEGREE_POLICY determines how to
apply ADOP:
 MANUAL: (default) DBA specifies desired
parallelism via object decoration
 LIMITED: DOP is derived from elapsed time of most
costly operation during the first serial compilation
 Default:10 seconds
 Can be modified via initialization parameter

PARALLEL_MIN_TIME_THRESHOLD

 AUTO: Turns on ADOP, statement queuing, and in-

memory parallel execution (IMPE)
Copyright 2013, Zero Defect Computing, Inc.

-9-


Slide 10

PARALLEL_DEGREE_LIMIT
PARALLEL_DEGREE_LIMIT limits the total degree of
parallelism (DOP) that any “parallelizable” statement
can utilize
 CPU (default): CPU resources available via the
following formula:
threads_per_CPU X total CPUs in the cluster
 N > 1: Specific value of one or greater
 IO: I/O resource constraints
 AUTO: Optimizer automatically determines value

Copyright 2013, Zero Defect Computing, Inc.

- 10 -


Slide 11

PARALLEL_MIN_TIME_THRESHOLD
PARALLEL_MIN_TIME_THRESHOLD defines the
minimum execution time before which parallelism
would provide little benefit to a statement’s execution

• AUTO: (default) Oracle determines the optimum
threshold value
• N > 0: The number of seconds to be used as the
minimum threshold value (default: 10 seconds)

Copyright 2013, Zero Defect Computing, Inc.

- 11 -


Slide 12

PARALLEL_SERVERS_TARGET
Specifies the number of parallel server processes
allowed to run before parallel statement queuing is
enforced
 Based on following formula:
2 * (PARALLEL_THREADS_PER_CPU *
CPU_COUNT * concurrent parallel users)
 where concurrent parallel users will be set to:
 Automatic Memory Management disabled: 1
 Automatic PGA Management enabled: 2
 Automatic SGA Management enabled: 4

Copyright 2013, Zero Defect Computing, Inc.

- 12 -


Slide 13

PARALLEL_FORCE_LOCAL
For Real Application Clustered (RAC) databases
only, this determines whether parallel server
processes can be distributed across all
participating instances in the cluster
• FALSE: (Default) Parallel query server processes
may be mapped to any node in a clustered
environment
• TRUE: Parallel query server processes may only
be mapped to the instance at which the initial
connection was mapped
Copyright 2013, Zero Defect Computing, Inc.

- 13 -


Slide 14

Retasked & Deprecated Parameters
As of Oracle Database 11.2.0.2, the following
parameters have been retasked or deprecated:
Parameter

11.2.0.2 and beyond

PARALLEL_IO_CAP_ENABLED

Deprecated; replaced by value of IO for
PARALLEL_DEGREE_LIMIT

PARALLEL_THREADS_PER_CPU

Deprecated to a “hidden” parameter
(_PARALLEL_THREADS_PER_CPU)

Copyright 2013, Zero Defect Computing, Inc.

- 14 -


Slide 15

Parallelism: Rules of Precedence
SQL statement
issued
1st Pass: Compute serial cost

Generate serial
plan

2nd Pass: Compute parallel cost
Is estimated elapsed time >
PARALLEL_MIN_TIME_THRESHOLD?

No.
No.

Yes!

Generate parallel
plan via DOP

Is estimated parallel elapsed time <
estimated serial elapsed time?

Yes!
Execute
serial plan

Execute
parallel plan
Copyright 2013, Zero Defect Computing, Inc.

- 15 -


Slide 16

What About Optimizer Hints?
As of 11.2.0.2, the scope of PARALLEL hints is applied
at the statement level:
 Hints now supercede parallelism that’s been
specified at the table or object level
 Initialization parameter PARALLEL_DEGREE_POLICY
also dictates different handling of parallelism:
 If set to MANUAL (the default), then PARALLEL hints

dictate the degree of parallelism for the specific object
 If set to LIMITED, then PARALLEL hints dictate degree
of parallelism for the statement itself and not the object

Copyright 2013, Zero Defect Computing, Inc.

- 16 -


Slide 17

Statement-Level PARALLEL Hints
Some examples of this new PARALLEL hinting:
 Set degrees of parallelism for tables AP.INVOICES and

AP.INVOICE_ITEMS to 3 and none, respectively:
ALTER TABLE ap.invoices PARALLEL 3;
ALTER TABLE ap.invoice_items NOPARALLEL;

 If PARALLEL_DEGREE_POLICY = MANUAL, then the

+PARALLEL hint is applied to the object:
SELECT /*+ PARALLEL(invoices 8) */ I.vendor_id, L.extd_amt
FROM ap.invoices I, ap.invoice_items L
WHERE I.invoice_id = L.invoice_id;

 If PARALLEL_DEGREE_POLICY = LIMITED, then the

+PARALLEL hint is applied to the statement:
SELECT /*+ PARALLEL(8) */ I.vendor_id, L.extd_amt
FROM ap.invoices I, ap.invoice_items L
WHERE I.invoice_id = L.invoice_id;
Copyright 2013, Zero Defect Computing, Inc.

- 17 -


Slide 18

ADOP and IMPE:
Parallelism In Combat


Slide 19

ADOP: Prerequisites
DBMS_RESOURCE_MANAGER.CALIBRATE_IO
procedure must be run before implementing ADOP!
SQL> SET
COL SERVEROUTPUT
start_dtm
HEADING "Start Time“
ON FORMAT A19
COL end_dtm
FORMAT A19
HEADING "End Time“
DECLARE
Maximum Througput (MBPS):
789
COL nbr_disks
FORMAT 99999
HEADING "# of|Phys|Disks“
nIOPS
INTEGER;
Maximum IOPS:
858
COL max_iops
FORMAT 99999999
HEADING "Maximum|IOPS“
nMBPS
INTEGER;
Actual Latency(ms):
8
COL max_mbps
FORMAT 99999999
HEADING "Maximum|Thruput|(MBPS)“
nLatency
INTEGER;
COL max_pmbps
FORMAT 99999999
HEADING "Max Large|I/O Rqsts|Per Prcs“
BEGIN
COL latency
FORMAT 99999999
DBMS_RESOURCE_MANAGER.CALIBRATE_IO
( HEADING "Latency“
Results
of
CALIBRATE_IO
Operations
TTITLE "Results
of CALIBRATE_IO =>
Operations|(from
DBA_RSRC_IO_CALIBRATE)"
num_physical_disks
36
SELECT
,max_latency => 10 (from DBA_RSRC_IO_CALIBRATE)
TO_CHAR(start_time,
hh24:mi:ss') start_dtm
,max_iops => 'yyyy-mm-dd
nIOPS
#
of
Maximum Max Large
,TO_CHAR(end_time,
'yyyy-mm-dd
hh24:mi:ss') end_dtm
,max_mbps =>
nMBPS
Phys,max_iops
Maximum
Thruput I/O Rqsts
,num_physical_disks
nbr_disks
,max_mbps
,max_pmbps
,actual_latency
=> nLatency);
Start
Time
End
Time
Disks
IOPS
(MBPS)
Per
,latency
DBMS_OUTPUT.PUT_LINE ('Maximum Througput (MBPS): ' || Prcs
nMBPS);Latency
FROM dba_rsrc_io_calibrate
DBMS_OUTPUT.PUT_LINE ('Maximum IOPS:
' || nIOPS);
------------------------------------------------ORDER BY
1,2;
DBMS_OUTPUT.PUT_LINE
('Actual Latency(ms):
'--------|| nLatency);
2013-03-24 2013-03-24
36
858
789
250
8
TTITLE
OFF
END;
22:07:26
/ 21:49:26

Copyright 2013, Zero Defect Computing, Inc.

- 19 -


Slide 20

Example: ADOP In Action
Execution Plan
---------------------------------------------------------Plan hash value: 1647334853

SELECT /*+ MONITOR PARALLEL OPT_PARAM('cell_offload_processing' 'FALSE') */
DISTINCT
---------------------------------------------------------------------------------------------------------| Id | Operation
| Rows | Bytes |TempSpc| Cost (%CPU)| Time
|
C.cust_id ,C.cust_last_name | Name
---------------------------------------------------------------------------------------------------------,P.prod_id
|
0 | SELECT
STATEMENT
|
|
29M| 1755M|
|
494K (1)| 00:00:16 |
|
1 | PX COORDINATOR
|
|
|
|
|
|
|
,P.prod_name
|
2 |
PX SEND QC (ORDER)
| :TQ10003 |
29M| 1755M|
|
248K (1)| 00:00:08 |
|
3 |
SORT UNIQUE
|
|
29M| 1755M| 2237M|
248K (1)| 00:00:08 |
,S.channel_id
|
4 |
PX RECEIVE
|
|
29M| 1755M|
| 2440
(9)| 00:00:01 |
,S.promo_id
|
5 |
PX SEND RANGE
| :TQ10002 |
29M| 1755M|
| 2440
(9)| 00:00:01 |
|* 6 |
HASH
JOIN
|
|
29M|
1755M|
|
2440
(9)| 00:00:01 |
,S.quantity_sold
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
,S.amount_sold
| 14 |
PX SEND BROADCAST
| :TQ10001 |
72 | 2160 |
|
2
(0)| 00:00:01 |
| 15 |
PX BLOCK ITERATOR
|
|
72 | 2160 |
|
2
(0)| 00:00:01 |
FROM sh.sales
S, sh.customers C, sh.products P 72 | 2160 |
| 16 |
TABLE ACCESS STORAGE FULL| PRODUCTS |
|
2
(0)| 00:00:01 |
| 17 | WHEREBUFFER
SORT
|
|
55500
|
704K|
|
1967
(1)| 00:00:01 |
S.cust_id = C.cust_id
| 18 |
PX BLOCK ITERATOR
|
| 55500 |
704K|
|
225
(1)| 00:00:01 |
AND S.prod_id
= P.prod_id
| 19 |
TABLE ACCESS
STORAGE FULL | CUSTOMERS | 55500 |
704K|
|
225
(1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------AND ((C.cust_last_name LIKE 'Smith%')
Predicate Information (identified by operation id):
OR (S.amount_sold BETWEEN 1000 AND 5000))
--------------------------------------------------ORDER BY 8, 1 DESC, 3, 4 DESC, 5, 6, 2 DESC, 7;
6 - access("S"."CUST_ID"="C"."CUST_ID")
filter("C"."CUST_LAST_NAME" LIKE 'Smith%' OR "S"."AMOUNT_SOLD">=1000 AND "S"."AMOUNT_SOLD"<=5000)
Note
----- automatic DOP: Computed Degree of Parallelism is 2

ADOP was successfully used for this query as
indicated in the EXPLAIN PLAN NOTES section

Copyright 2013, Zero Defect Computing, Inc.

- 20 -


Slide 21

In-Memory Parallel Execution (IMPE)
 Leverages available buffer cache memory of multiple

11gR2 Real Application Cluster (RAC) instances to
scale out query execution
 For queries that can take advantage of parallelism,
IMPE provides methods to subdivide objects
required to answer a query across multiple database
instance buffer caches
 The result: A significant reduction in total physical I/O
required to retrieve all required blocks
 The more database instances available, the larger
the potential “scale out” capability
 No changes to existing code!
Copyright 2013, Zero Defect Computing, Inc.

- 21 -


Slide 22

Monitoring Parallel
Operations


Slide 23

Monitoring via SQL Monitor

Copyright 2013, Zero Defect Computing, Inc.

- 23 -


Slide 24

SQL Monitor: IMPE In Action
Execution Details (DOP=2 , Servers Allocated=4)
SET`Parallel
LONG 10000000
` Instances : 2
SET` LONGCHUNKSIZE 10000000
SET`========================================================================================
LINESIZE 230
`| Instance |
Name
| Type | Server# | Elapsed | Queuing |
Cpu
|
IO
|
SET`|PAGESIZE 10000
|
|
|
| Time(s) | Time(s) | Time(s) | Waits(s) |
`========================================================================================
SELECT
DBMS_SQLTUNE.REPORT_SQL_MONITOR(sql_id
=> '74r61jam587ns',
report_level|=> 'ALL')
`| 1
| PX Coordinator | QC
|
|
2.41 |
0.01 |
2.06 |
`| 2 dual;
| p000
| Set 1 |
1 |
3.84 |
|
3.16 |
0.67 |
FROM
`|
|
|
|
|
|
|
|
|
`| 1
| p000
| Set 1 |
2 |
4.66 |
|
3.90 |
0.76 |
`| 2
| p001
| Set 2 |
1 |
13 |
|
13 |
|
`| 1
| p001
| Set 2 |
2 |
10 |
|
10 |
|
`========================================================================================
. . .
===================================================================================================
| Id
|
Operation
|
Name
| Rows
| Cost |
Time
| Start |
|
|
|
| (Estim) |
| Active(s) | Active |
===================================================================================================
| -> 0 | SELECT STATEMENT
|
|
|
|
240 |
+14 |
| -> 1 |
PX COORDINATOR
|
|
|
|
253 |
+1 |
|
|
|
|
|
|
|
|
| -> 2 |
PX SEND QC (ORDER)
| :TQ10003 |
29M | 248K |
238 |
+16 |
| -> 3 |
SORT UNIQUE
|
|
29M | 248K |
252 |
+2 |
|
|
|
|
|
|
|
|
|
4 |
PX RECEIVE
|
|
29M | 2440 |
104 |
+2 |
|
5 |
PX SEND RANGE
| :TQ10002 |
29M | 2440 |
13 |
+2 |
|
6 |
HASH JOIN
|
|
29M | 2440 |
13 |
+2 |
|
10 |
TABLE ACCESS STORAGE FULL
| SALES
|
919K | 294 |
1 |
+2 |
. . .
|
18 |
PX BLOCK ITERATOR
|
|
55500 | 225 |
1 |
+2 |
|
19 |
TABLE ACCESS STORAGE FULL
| CUSTOMERS |
55500 | 225 |
1 |
+2 |
===================================================================================================


Slide 25

Parallelizing Batch
Operations


Slide 26

Parallelizing Batch Operations
DBMS_PARALLEL_EXECUTE is a new Oraclesupplied package in 11.2.0.2:
 Extends the benefits of DOP to batch SQL
 Leverages parallel processing by “chunking” work
into smaller executable batched tasks
 Tasks are implicitly constructed and executed via
DBMS_SCHEDULER
 Three “chunking” methods provided:
 CREATE_CHUNKS_BY_ROWID
 CREATE_CHUNKS_BY_NUMBER_COL
 CREATE_CHUNKS_BY_SQL
Copyright 2013, Zero Defect Computing, Inc.

- 26 -


Slide 27

“Chunking” Parallel Batch Operations (1)
DECLARE
vc_chunk_sql VARCHAR2(1000);
vc_sql_stmt VARCHAR2(1000);
n_try
NUMBER;
n_status NUMBER;
BEGIN
DBMS_PARALLEL_EXECUTE.CREATE_TASK (
task_name => 'PET_2'
,comment => 'Example #2: DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL'
);
vc_chunk_sql :=
'SELECT (DECODE(MOD(invoice_id,1000),0,invoice_id,0) - 999), '
|| '(DECODE(MOD(invoice_id,1000),0,invoice_id,0)) FROM ap.invoices '
|| 'WHERE DECODE(MOD(invoice_id,1000),0,invoice_id,0) > 0 ORDER BY 1';
DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL('PET_2', vc_chunk_sql, false);
vc_sql_stmt := 'UPDATE /*+ ROWID (dda) */ ap.invoices SET taxable_amt =
(balance_due * 0.20) WHERE invoice_id BETWEEN :start_id AND :end_id';

Copyright 2013, Zero Defect Computing, Inc.

- 27 -


Slide 28

“Chunking” Parallel Batch Operations (2)
DBMS_PARALLEL_EXECUTE.RUN_TASK(
task_name => 'PET_2'
,sql_stmt => vc_sql_stmt
,language_flag => DBMS_SQL.NATIVE
,parallel_level => 10
,job_class => 'DEFAULT'
);

n_try := 0;
n_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(task_name => 'PET_2');
WHILE(n_try < 2 and n_status != DBMS_PARALLEL_EXECUTE.FINISHED)
LOOP
n_try := n_try + 1;
DBMS_PARALLEL_EXECUTE.RESUME_TASK(task_name => 'PET_2');
n_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS(task_name => 'PET_2');
END LOOP;
DBMS_PARALLEL_EXECUTE.DROP_TASK(task_name => 'PET_2');
END;
/
Copyright 2013, Zero Defect Computing, Inc.

- 28 -


Slide 29

Monitoring Parallelized Batches
COL chunk_id

FORMAT 99999

HEADING "Chunk|#"

Chunked
Tasks Operations
Controlled
Controlled
Via DBMS_PARALLEL_EXECUTE
Via DBMS_PARALLEL_EXECUTE
COL task_owner
FORMAT A08
HEADING
"Task|Owner"
COL task_owner
FORMAT
A08
HEADING "Task|Owner"
(From
(From
DBA_PARALLEL_EXECUTE_TASKS)
DBA_PARALLEL_EXECUTE_CHUNKS)
COL task_name
FORMAT A08
HEADING
COL task_name
HEADING"Task
"Task Name"
Name"
Task
Chunk
Job FORMAT A08
COL
chunk_type
FORMAT
A12
HEADING
"Chunk|Type"
Chunk
Task
Start
End
COL status Status
A12
Owner
Task Nam Type
Pfx FORMAT
SQL Statement
TextHEADING "Status"
Flag Level Class
COLTask
status
FORMAT
A08
HEADING
# Owner
Nam start_rowid
Status -------- -----IDFORMAT
IDA18
Start Time
End Time
Job -------Name
--------------------------------------------------------------------- -----COL
HEADING"Status"
"Start|ROWID"
-------------------------------------------------------------------------------------------------COL table_owner
FORMAT
A08A18
SYS
PET_2
NUMBER_RANGE
FINISHED TASK$
UPDATE
/*+ ROWIDHEADING
(dda)
*/ "Table|Owner"
ap.invoices
1
10 DEFAULT
COL end_rowid
FORMAT
HEADING
"End|ROWID"SE
211 SYS
PROCESSED
1FORMAT
1000
2013-04-08.22:52:52
TASK$_1932_2
_1932
T taxable_amt
= HEADING
(balance_due
*2013-04-08.22:52:52
0.20)
WHE
COLPET_2
table_name
FORMAT
A2499999
Name"
COL start_id
HEADING"Table
"Start|ID"
212 SYS
PROCESSED
1001
2000 2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_10
COL end_id
FORMAT
HEADING"Job|Class"
"End|ID"
COLPET_2
job_class
FORMAT
A0899999
HEADING
213 SYS
PET_2
PROCESSED
2001
3000 2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_2
COL start_dtm
FORMAT
HEADING"Number|Column"
"Start Time"
COLPET_2
number_column
FORMAT
A12A21
HEADING
214 SYS
PROCESSED
3001
4000
2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_2
COL end_dtm
HEADING"SQL
"End
Time"
COLPET_2
sql_stmt
FORMAT
A40A21
HEADING
Statement
Text" WRAP
215 SYS
PROCESSED
4001FORMAT
5000
2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_2
COL
job_name
FORMAT
A15
HEADING
"Job
Name"
216 SYS
PET_2
PROCESSED
5001
6000
2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_2
COL job_prefix
FORMAT A05
HEADING "Job|Pfx"
COL
task_comment
FORMAT
A20
HEADING
"Task
Comment"
WRAP
217 SYS
PET_2
PROCESSED
6001
7000
2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_10
COL language_flag
FORMAT 99999
HEADING "Lang|Flag"
COL error_code
HEADING "Error|Code"
218 SYS
PET_2
PROCESSED
7001FORMAT
800099999
2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_10
COL parallel_level
FORMAT 99999
HEADING "Para-|llel|Level"
219 SYS
PET_2
PROCESSED
8001
9000
2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_2
COL error_message
FORMAT A40
HEADING "Error Message" WRAP
TTITLE
"Tasks
Controlled
Via
DBMS_PARALLEL_EXECUTE
220 SYS
PET_2
PROCESSED
9001
10000
2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_10
TTITLE "Chunked Operations Controlled Via DBMS_PARALLEL_EXECUTE
|(From
DBA_PARALLEL_EXECUTE_TASKS)"
221 SYS
PET_2
PROCESSED
10001
11000
2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_2
|(From DBA_PARALLEL_EXECUTE_CHUNKS)"
222 SYS
PET_2
PROCESSED
11001
12000
2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_10
SELECTSELECT
223 SYS
PET_2
PROCESSED
12001 13000 2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_2
task_owner
chunk_id
224 SYS
PET_2
PROCESSED
13001 14000 2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_10
,task_name
,task_owner
225 SYS
PET_2
PROCESSED
14001 15000 2013-04-08.22:52:52
2013-04-08.22:52:52
TASK$_1932_4
,task_name
,chunk_type
,status
,status
,start_rowid
,table_owner
,end_rowid
,table_name
,start_id
,number_column
,end_id
,job_prefix
,TO_CHAR(start_ts, 'yyyy-mm-dd.hh24:mi:ss') start_dtm
,sql_stmt
,TO_CHAR(end_ts, 'yyyy-mm-dd.hh24:mi:ss') end_dtm
,language_flag
,job_name
,parallel_level
,error_code
,job_class
,error_message
FROM FROM
dba_parallel_execute_tasks
dba_parallel_execute_chunks
BY chunk_idtask_name;
ORDER ORDER
BY task_owner,
;
TTITLE OFF

TTITLE OFF

Copyright 2013, Zero Defect Computing, Inc.

- 29 -


Slide 30

Parallelism: Conclusions
Parallelism in Oracle 11.2.0.2 Database is …
 Easier to configure, with less guesswork
 Leverages “decoratability” of objects
 Automatically applied (once it’s configured properly)
 Available for single statements or batch operations
 IMPE leverages available power of RAC instances
 Easier to monitor, especially with SQL Monitor

Copyright 2013, Zero Defect Computing, Inc.

- 30 -


Slide 31

Thank You For Your Kind Attention
Please fill out a session evaluation form!
Session P52
Parallelize or Perish!
Implementing Oracle 11gR2 Parallelism
Features for Maximum Performance
If you have any questions or comments, feel free to:
E-mail me at jczuprynski@zerodefectcomputingcom
Follow my blog (Generally, It Depends):
http://jimczuprynski.wordpress.com
Follow me on Twitter (@jczuprynski)
Connect with me on LinkedIn (Jim Czuprynski)
Copyright 2013, Zero Defect Computing, Inc.

- 31 -