Re: Advantage off parallel access at external tables
Date: Fri, 4 Apr 2008 05:33:31 -0700 (PDT)
Message-ID: <72bc1a8a-2c46-43a3-8838-5c54919678a5@m3g2000hsc.googlegroups.com>
> So it seems possible to have an external table in multiple datafiles, in
> which case parallelism might help.
Yes, I tried that it is possible to have multiple data files.
>I don't know whether the standard SQL loader access driver (which is used in >most cases) supports parallallism on single files ; maybe some guru can tell
I don't think so. I think it is serial-mode with the SQL*Loader. But thats not important... in my test's I just can't find an improvement by doing the parallel access. It was the opposite....parallel access was slower by the use of two source files.
I post my test results here, maybe I did it wrong and I can't test
like this at the time I'm doing parallel access.
As you can see the fastest access is with one source file parallel on
or off is quite the same.
It follows two source files without parallel and the slowest access is
with two source files and parallel on.
Somebody can explain that?
Thank you very much in advance!!!
Kind regards
Sebastian
Ext_table_1 is one external table with one source file. Parallel is 5
SQL> set autotrace on
SQL> select count(*) from Ext_table_1;
COUNT(*)
411047
Elapsed: 00:00:12.21
Execution Plan
Plan hash value: 2009794828
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 1 | 29 (0)|
00:00:01 |
| 1 | SORT AGGREGATE | | 1 |
| |
| 2 | EXTERNAL TABLE ACCESS FULL| Ext_table_1 | 8168 | 29
(0)| 00:00:01 |
Statistics
24 recursive calls
0 db block gets
539 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Ext_table_1 is an external table with two source files. Parallel is 5
SQL> set autotrace on
SQL> select count(*) from Ext_table_1;
COUNT(*)
411041
Elapsed: 00:00:19.92
Execution Plan
Plan hash value: 2009794828
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 1 | 29 (0)|
00:00:01 |
| 1 | SORT AGGREGATE | | 1 |
| |
| 2 | EXTERNAL TABLE ACCESS FULL| Ext_table_1 | 8168 | 29
(0)| 00:00:01 |
Statistics
24 recursive calls
0 db block gets
539 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Ext_table_1 is an external table with two source files. Parallel is not set
SQL> set autotrace on
SQL> select count(*) from Ext_table_1;
COUNT(*)
411041
Elapsed: 00:00:15.26
Execution Plan
Plan hash value: 2009794828
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 1 | 29 (0)|
00:00:01 |
| 1 | SORT AGGREGATE | | 1 |
| |
| 2 | EXTERNAL TABLE ACCESS FULL| Ext_table_1 | 8168 | 29
(0)| 00:00:01 |
Statistics
24 recursive calls
0 db block gets
539 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
F1 Dump is an external table with one source file. Parallel is not set.
SQL> select count(*) from Ext_table_1;
COUNT(*)
411047
Elapsed: 00:00:12.71
Execution Plan
Plan hash value: 2009794828
| Id | Operation | Name | Rows | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 1 | 29 (0)|
00:00:01 |
| 1 | SORT AGGREGATE | | 1 |
| |
| 2 | EXTERNAL TABLE ACCESS FULL| Ext_table_1 | 8168 | 29
(0)| 00:00:01 |
Statistics
24 recursive calls
0 db block gets
539 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Received on Fri Apr 04 2008 - 07:33:31 CDT