Home » SQL & PL/SQL » SQL & PL/SQL » Parallel DML is not working. (Oracle10G, AIX5.3)
Parallel DML is not working. [message #437261] Sat, 02 January 2010 14:42 Go to next message
patjose
Messages: 12
Registered: January 2010
Junior Member
Hi Experts,

Parallel DML is not working for me.

Both remote and local databases are on same type of hardware and on separate boxes. Network latency between 2 boxes is less than a millisecond.

Env details:
Oracle10.2.0.3
IBM-AIX5.3
8CPU(4.8GHz) PowerPC6 machine with 64GB RAM.
SGA_TARGET=2GB
PGA_AGGREGATE_TARGET=1GB
parallel_max_servers=200
parallel_min_servers=200


DB is in no-archive log mode and test table in cust DB has 18 million records, table size is roughly 4GB.
Executed following query and elapsed time is 6 minutes and 52 seconds:

insert /*+ append */ into test
select * from test@cust;

Modified the query for parallelism and executed, but elapsed time is 6 minutes and 55 seconds:

insert /*+ append parallel( 8 ) */ into test
select /*+ parallel( 8 ) */ * from test@cust;

How can I improve the timing?

Thanks for your time!
Re: Parallel DML is not working. [message #437265 is a reply to message #437261] Sat, 02 January 2010 15:02 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Did you set parallel_max_servers to a value greater than 0?
2/ Did you enable parallel dml? (alter session enable parallel dml)
3/ Is parallel_min_percent parameter compatible with a parallel 8?
4/ What are the values of parallel_automatic_tuning and parallel_adaptive_multi_user parameters?
5/ Are you sure your hardware is compatible with a parallel 8? Otherwise you may have less performances with parallel than with serial.

Regards
Michel
Re: Parallel DML is not working. [message #437274 is a reply to message #437261] Sat, 02 January 2010 19:14 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>insert /*+ append parallel( 8 ) */ into test select /*+ parallel( 8 ) */ * from test@cust;
>How can I improve the timing?
Not every SQL can be improved.
Sometimes you must accept reality.
Re: Parallel DML is not working. [message #437275 is a reply to message #437274] Sat, 02 January 2010 20:27 Go to previous messageGo to next message
patjose
Messages: 12
Registered: January 2010
Junior Member
Michel, Blackswan,

thanks for the pointers.

When parallel_automatic_tuning is set to true, during instance startup throws a warning as a depracated parameter in 10g.

Michel,

Below, I captured sql session for all your questions. Let me know if anything comes up in particular..

I was trying different options to get it faster like datapump, copy command and all as I've good hardware in terms of memory(64GB), CPU (8 of them), CPU Speed(4.3GHz), network latency (0.2 millisec). Tablesize is 4GB and no special datatypes, so just want to make sure that I'm using best possible approach...

 
SQL>  !prtconf | grep -i processors
Number Of Processors: 8
SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     TRUE
parallel_execution_message_size      integer     4096
parallel_instance_group              string
parallel_max_servers                 integer     20
parallel_min_percent                 integer     50
parallel_min_servers                 integer     8
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

SQL>  alter session enable parallel dml;

Session altered.

SQL> select count(*) from test@cust;

  COUNT(*)
----------
  17833147

SQL> truncate table test;

Table truncated.

SQL>  INSERT /*+ APPEND*/ INTO test select * from test@cust;

17833147 rows created.

Elapsed: 00:06:54.66

SQL> truncate table test;

Table truncated.

Elapsed: 00:00:00.30
SQL> insert /*+ append parallel(8) */ into test
select /*+ parallel(8) */ * from test@cust;  

17833147 rows created.

Elapsed: 00:06:52.68



Again, thanks for your time...
Re: Parallel DML is not working. [message #437276 is a reply to message #437275] Sat, 02 January 2010 20:58 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
post EXPLAIN PLAN for both SQL.

What is underlying storage subsystem involved with table test?
If RAID, which flavor?
For table test, how many physical disk drives hold this table on each system?

For system getting the INSERT data, provide details for disks involved with UNDO and REDO.
Ideally data disk, UNDO disk, & REDO disk are separate from each other.

More clues may be obtained by doing
ALTER SESSION SET SQL_TRACE=TRUE
& then invoking the INSERT.
A manual inspection of the raw trace file may show where time is being spent (waited/wasted).

Is there any way you can prove or disprove whether or not the network is the bottleneck?

[Updated on: Sat, 02 January 2010 21:11]

Report message to a moderator

Re: Parallel DML is not working. [message #437279 is a reply to message #437276] Sat, 02 January 2010 23:07 Go to previous messageGo to next message
patjose
Messages: 12
Registered: January 2010
Junior Member
The mount point is on EMC Clarion Disk Array and RAID Type is RAID1/0. So lun is mirrored and striped. This particular lun has around 60 disks each with 73GB. So undo, redo files, temp and datafiles are on this lun and hence on the same mount point.

Here is round-trip timing for the remote server which is almost 0 ms.
 
sdp1:/oradata/pcat> ping cust_test1
PING cust_test1: (cust_test1): 56 data bytes
64 bytes from cust_test1: icmp_seq=0 ttl=255 time=0 ms
64 bytes from cust_test1: icmp_seq=1 ttl=255 time=0 ms



SQL Trace details, looks like so many recursive calls going on.
 
SQL>  SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
SQL> ALTER SESSION SET SQL_TRACE=TRUE  ;

Session altered.

SQL> INSERT /*+ APPEND*/ INTO BILL_INVOICE_DETAIL select * from BILL_INVOICE_DET

17833147 rows created.


Execution Plan
----------------------------------------------------------
ERROR:
ORA-12840: cannot access a remote table after parallel/insert direct load txn


SP2-0612: Error generating AUTOTRACE EXPLAIN report

Statistics
----------------------------------------------------------
       7813  recursive calls
     261233  db block gets
       2671  consistent gets
          2  physical reads
    1483628  redo size
        812  bytes sent via SQL*Net to client
        780  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
   17833147  rows processed


Here is the explain plan for both statements.
 
SQL> explain plan for  INSERT /*+ APPEND*/ INTO BILL_INVOICE_DETAIL select * from BILL_INVOICE_DETAIL@cust1;

Explained.

SQL> select * FROM   TABLE( DBMS_XPLAN.DISPLAY );

29 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3013799171

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         | 16360 | 32720 |    33   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |            |          |
---------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         14  recursive calls
         12  db block gets
         50  consistent gets
          0  physical reads
          0  redo size
       3268  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         29  rows processed

SQL>  explain plan for insert /*+ append parallel(8) */ into BILL_INVOICE_DETAIL
select /*+ parallel(8) */ * from BILL_INVOICE_DETAIL@cust1;   2  

Explained.

SQL> select * FROM   TABLE( DBMS_XPLAN.DISPLAY );

29 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3013799171

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         | 16360 | 32720 |    33   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |            |          |
---------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         14  recursive calls
         12  db block gets
         50  consistent gets
          0  physical reads
          0  redo size
       3268  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         29  rows processed

SQL> 
Re: Parallel DML is not working. [message #437282 is a reply to message #437275] Sun, 03 January 2010 01:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With your parameters you let Oracle chooses for serial or parallel access, maybe it does not choose the parallel one.
Activate 10046 trace at level 8 and reexecute (alter session set events '10046 trace name context forever, level 18'Wink, then disconnect and post the trace file.
Assume the remote databasae is on a remote server, are you sure you network supports 8 processes sending data at the rate they can load from the disks? Are you sure your disk(s) can support the writing of 8 processes?
Check cpu, disk and network consumption of both servers during the statement execution.

By the way, you didn't post explain plan for your statement but for "select * FROM TABLE( DBMS_XPLAN.DISPLAY );", you should execute "set trace off" before the explain plan...

Regards
Michel
Re: Parallel DML is not working. [message #437303 is a reply to message #437261] Sun, 03 January 2010 10:11 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Are you sure this is the correct syntax anyway?

insert /*+ append parallel(8) */ into BILL_INVOICE_DETAIL
select /*+ parallel(8) */ * from BILL_INVOICE_DETAIL@cust1;


I always use alias for parallel hints.
Im not saying your way is wrong, but just try this and see what happens:-

insert /*+ append parallel(b,8) */ into BILL_INVOICE_DETAIL b
select /*+ parallel(a,8) */ * from BILL_INVOICE_DETAIL@cust1 a;

Re: Parallel DML is not working. [message #437304 is a reply to message #437303] Sun, 03 January 2010 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Indeed, "tablespec" element is mandatory and it is the simplest answer to the problem. Smile

Regards
Michel
Re: Parallel DML is not working. [message #437310 is a reply to message #437261] Sun, 03 January 2010 12:35 Go to previous messageGo to next message
patjose
Messages: 12
Registered: January 2010
Junior Member

Modified the SQL, still not finding any improvement. Tried for DOP for 8 and 2 to see if any difference. Session is below.

 12:31:54 SQL> alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
12:32:09 SQL> 
12:32:21 SQL> insert /*+ append parallel(b,8) */ into BILL_INVOICE_DETAIL
select /*+ parallel(a,8) */ * from BILL_INVOICE_DETAIL@cust1;  12:32:22   2  


17833147 rows created.

Elapsed: 00:06:55.12
12:39:19 SQL> 12:39:19 SQL> truncate table BILL_INVOICE_DETAIL;

Table truncated.

Elapsed: 00:00:00.30
12:42:47 SQL> insert /*+ append parallel(b,2) */ into BILL_INVOICE_DETAIL
12:42:55   2  select /*+ parallel(a,2) */ * from BILL_INVOICE_DETAIL@cust1;  

17833147 rows created.

Elapsed: 00:06:58.18
12:49:53 SQL> 
 


There is only one process which takes about 99% of CPU ( one CPU I guess, as the machine has 8 virtual and 16 logical CPUs). Here is output of that nmon for this process.
  PID       %CPU     Size      Res     Res      Res     Char    RAM      Paging    		   Command 
            Used       KB      Set     Text     Data     I/O     Use   io   other repage
 1708178    99.5    94724    52696    45136     7560     2515    0%      0      0      0    oracle   
 


Logging is turned on for ('10046 trace name context forever, level 8';). Since trace file is around 63MB, attaching the tkprof file.

Thanks..


  • Attachment: tkprof.txt
    (Size: 7.64KB, Downloaded 198 times)
Re: Parallel DML is not working. [message #437313 is a reply to message #437310] Sun, 03 January 2010 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
insert /*+ append parallel(b,8) */ into BILL_INVOICE_DETAIL
select /*+ parallel(a,8) */ * from BILL_INVOICE_DETAIL@cust1;

As there is no alias named a or b in your query the parallel clauses apply to nothing.

Quote:
Logging is turned on for ('10046 trace name context forever, level 8'Wink. Since trace file is around 63MB, attaching the tkprof file.

Post the first 100 lines and post all STAT lines.
Gives the number of WAIT and FETCH lines, give all the type of WAIT lines, post some of them.

Flush the shared pool before the test and reexecute then we can have the execution plans.

Regards
Michel

[Updated on: Sun, 03 January 2010 13:35]

Report message to a moderator

Re: Parallel DML is not working. [message #437318 is a reply to message #437261] Sun, 03 January 2010 18:17 Go to previous messageGo to next message
patjose
Messages: 12
Registered: January 2010
Junior Member
Updated the SQL and shared pool is flushed.
Now the temp tablespace is growing from 300MB to 5GB and timing is increased to 11 plus minutes. At least something different now.

 
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 8';

Session altered.

Elapsed: 00:00:00.00
SQL> 
SQL>  alter session enable parallel dml;

Session altered.

Elapsed: 00:00:00.00
SQL> insert /*+ append parallel(b,8) */ into BILL_INVOICE_DETAIL b
select /*+ parallel(a,8) */ * from BILL_INVOICE_DETAIL@cust1 a;   

17833147 rows created.

Elapsed: 00:11:26.03
SQL> 


Excerpts from trace file is attached. Interesting thing is following wait happened so many times.


sdp1:/oradump/pcat/udump> grep "WAIT #6:"  pcat_ora_1761970.trc | wc -l
  563774

  • Attachment: SQL_trace.txt
    (Size: 20.06KB, Downloaded 271 times)
Re: Parallel DML is not working. [message #437366 is a reply to message #437318] Mon, 04 January 2010 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You are now working in parallel as you have PX% wait events (you will see the plan if you run TKPROF on the new trace file)

2/ As you have 8 processes working it is not surprising you need more temp space

3/ As I said, parallelism does not mean faster, it depends on many things like your hardware (more here Oracle has to extend temp tablespace which takes time and slow down every one due to internal locks)

4/ TKPROF will show you the new waits due to parallelism. Post it.

Regards
Michel
Re: Parallel DML is not working. [message #437513 is a reply to message #437261] Mon, 04 January 2010 21:02 Go to previous message
patjose
Messages: 12
Registered: January 2010
Junior Member
Boxes are not available for this testing next few days I guess Sad .

Anyway, thanks Michel for your time and pointers. I think I can make progess..

Thanks once again...
Previous Topic: ORA-00942 Grant access to Role for Function
Next Topic: need help please
Goto Forum:
  


Current Time: Wed Sep 28 07:27:59 CDT 2016

Total time taken to generate the page: 0.36492 seconds