Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 


Tracing Parallel Execution - Part I
July 8, 2005
Mark Rittman

If you're looking to tune an SQL statement or a batch job, a common way to find out what happened during the execution of the SQL is to run an extended SQL trace and examine the wait events. But what happens if you are using parallel execution, and all your trace file contains is the parallel execution wait events that are generally considered idle events? Your trace file shows how long your query took to run, and the work involved in controlling the PQ slaves, but the real details of what took up all the execution time are actually to be found in the corresponding PQ slave trace files in the BDUMP directory.

To take an example, I create a copy of the SH.SALES table and enable parallel DML with a degree of parallelism of 2:

SQL*Plus: Release 10.1.0.4.0 - Production on Tue Apr 26 19:49:45 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table sales_copy as select * from sales;

Table created.

SQL> alter table sales_copy parallel 2;

Table altered.

I then query the parallel_min_servers parameter to make sure some PQ slaves are available:

SQL> show parameter parallel_min_servers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers                 integer     4
SQL> 

Now, I turn on extended SQL tracing for my session:

SQL> alter session set timed_statistics = true;

Session altered.

SQL> alter session set max_dump_file_size = unlimited;

Session altered.

SQL> alter session set tracefile_identifier = 'px_test1';

Session altered.

SQL> alter session set events = '10046 trace name context forever, level 8';

Session altered.

Now, I run my SELECT statement against the SALES_COPY table and the PRODUCTS table;

SQL> select p.prod_name, sum(s.amount_sold)
  2  from   products p, sales_copy s
  3  where  p.prod_id = s.prod_id
  4  group by p.prod_name;

PROD_NAME                                          SUM(S.AMOUNT_SOLD)
-------------------------------------------------- ------------------
128MB Memory Card                                           571333.75
17" LCD w/built-in HDTV Tuner                              7189171.77
18" Flat Panel Graphics Monitor                            5498727.81
3 1/2" Bulk diskettes, Box of 100                           370204.56
3 1/2" Bulk diskettes, Box of 50                            254128.79
Adventures with Numbers                                     175563.92
CD-R Mini Discs                                             384553.62
CD-R with Jewel Cases, pACK OF 12                           170405.76
CD-RW, High Speed Pack of 5                                 230233.35
CD-RW, High Speed, Pack of 10                               106468.41
DVD-R Discs, 4.7GB, Pack of 5                                904537.2
...
Y Box                                                       2082330.3

71 rows selected.

Then turn off extended SQL trace:

SQL> alter session set events '10046 trace name context off';

Session altered.

Now, if I format the trace file using TKPROF

C:\oracle\admin\markr10g\udump>tkprof markr10g_ora_11184_px_test1.trc markr10g_ora_11184_px_test1.txt

TKPROF: Release 10.1.0.4.0 - Production on Tue Apr 26 20:09:12 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

and then take a look at the part of the trace file that relates to my query, I see the following wait events:

select p.prod_name, sum(s.amount_sold)
from   products p, sales_copy s
where  p.prod_id = s.prod_id
group by p.prod_name

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       1.43        851         87          0           0
Execute      1      0.00       0.07          0          3          0           0
Fetch        6      0.00       3.14          3          4          0          71
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.01       4.65        854         94          0          71

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
     71  PX COORDINATOR  (cr=7 pr=3 pw=0 time=3110386 us)
      0  SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us)(Orphan Entry)
      0  PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
      0  SORT GROUP BY (cr=0 pr=0 pw=0 time=0 us)(Orphan Entry)
      0  HASH JOIN  (cr=0 pr=0 pw=0 time=0 us)
      0   BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
      0    PX RECEIVE  (cr=0 pr=0 pw=0 time=0 us)
     72  TABLE ACCESS FULL PRODUCTS (cr=4 pr=3 pw=0 time=15278 us)(Orphan Entry)
      0   PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
      0    TABLE ACCESS FULL SALES_COPY (cr=0 pr=0 pw=0 time=0 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  PX Deq: Join ACK                                3        0.00          0.00
  PX Deq: Parse Reply                             5        0.02          0.04
  SQL*Net message to client                       6        0.00          0.00
  PX Deq: Execute Reply                          61        0.20          2.79
  PX qref latch                                  31        0.02          0.22
  db file sequential read                         1        0.01          0.01
  db file scattered read                          1        0.00          0.00
  SQL*Net message from client                     6      126.37        126.40
  PX Deq: Signal ACK                              4        0.09          0.10
********************************************************************************

 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational