Advantage off parallel access at external tables
SePp
I did some testing to find an advantage of parallel access on external
tables. I did an Select count(*) statement
on an external table with around 450.000 records. The table reads the
data from one file.
But the results are quit different. It takes around 14 to 26 seconds
and sometimes the parallel access is faster and sometimes they have
the same speed. Is there no real advantage or is the way I'm testing
it wrong?
Do I understand the hole parallel access wrong and I have only an
advantage at the moment I'm access two or more external files.
I read somewhere (can't find the source again) the parallel access is
only recommended on huge external files. Can somebody confirm that?
Thank you very much in advance for you information and help!
Kind regards,
Sebastian
Shakespeare
"SePp" <C_o_...@gmx.de> schreef in bericht
news:6943b631-8164-4ef6...@b64g2000hsa.googlegroups.com...
From the docs:
---------------------------------------------------
Parallel Access to External Tables
After the metadata for an external table is created, you can query the
external data directly and in parallel, using SQL. As a result, the external
table acts as a view, which lets you run any SQL query against external data
without loading the external data into the database.
The degree of parallel access to an external table is specified using
standard parallel hints and with the PARALLEL clause. Using parallelism on
an external table allows for concurrent access to the datafiles that
comprise an external table. Whether a single file is accessed concurrently
is dependent upon the access driver implementation, and attributes of the
datafile(s) being accessed (for example, record formats).
------------------------------------------
So it seems possible to have an external table in multiple datafiles, in
which case parallelism might help.
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
us...
Shakespeare
SePp
> 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
ora...@msn.com
> | 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
>
There is no parallel access in this query, regardless of how you set
PARALLEL for this table; the query plan says this fairly clearly by
the absence of PX entries in the plan. A parallel query plan would
look like this:
Execution Plan
----------------------------------------------------------
Plan hash value: 2047745192
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8665 | 761K| 7 (15)|
00:00:01 | | | |
| 1 | PX COORDINATOR | | | |
| | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 8665 | 761K| 7 (15)|
00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 8665 | 761K| 7 (15)|
00:00:01 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| TEST | 8665 | 761K| 7 (15)|
00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Notice the PX entries for the coordinator and the slaves. Your plan,
for both 'parallel' queries, has no such information.
> 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
>
Again, this is no different from your non-parallel plans because there
is no parallel access occurring. My guess is you have
parallel_min_servers and parallel_max_servers set to 0, disabling
parallel query access to the table in question. Parallel access is
available for external tables, as evidenced below:
SQL> alter table admin_ext_employees parallel 6;
Table altered.
SQL> select * from admin_ext_employees;
<.... data here ...>
Execution Plan
----------------------------------------------------------
Plan hash value: 1746058442
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168
| 965K| 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | |
| | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 8168
| 965K| 5 (20)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 8168
| 965K| 5 (20)| 00:00:01 | Q1,00 | PCWC | |
| 4 | EXTERNAL TABLE ACCESS FULL| ADMIN_EXT_EMPLOYEES | 8168
| 965K| 5 (20)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
78 recursive calls
0 db block gets
227 consistent gets
0 physical reads
0 redo size
1403 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
SQL>
Again notice the PX entries in the query plan, indicating parallel
query coordinator and slave processes.
> 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
Even though your external table is altered for parallel access, your
database isn't configured to provide any parallel query slaves, which
then prevents the queries from executing in parallel. This is why you
don't see any benefit from the parallel configuration for your
external table. Set parallel_max_serverr to a non-zero value, restart
your database and try your query examples again and you'll find you
are executing in parallel and there may be some benefit obtained.
David Fitzjarrell
SePp
>
> read more »
Hi thx for your fast reply. It looks your correct.
My problem is when I do:
alter system set parallel_min_servers = 5;
alter system set parallel_max_servers = 56;
Nothing changes the autotrace displays still the same information (see
below). I use OracleExpress 10g is it possible that I can't do it with
this version?
Thanks for helping!!!!
Greets
Sebastian
----------------------------------------------------------
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_DUMP | 8168 | 29 (0)|
00:00:01 |
-------------------------------------------------------------------------------
Statistiken
----------------------------------------------------------
24 recursive calls
0 db block gets
536 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
ora...@msn.com
> > | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> > Time | TQ |IN-OUT| PQ Distrib |
> > | 0 | SELECT STATEMENT | | 8665 | 761K| 7 (15)|
> > 00:00:01 | | | |
> > | 1 | PX COORDINATOR | | | |
> > | | | | |
> > | 2 | PX SEND QC (RANDOM)| :TQ10000 | 8665 | 761K| 7 (15)|
> > 00:00:01 | Q1,00 | P->S | QC (RAND) |
> > | 3 | PX BLOCK ITERATOR | | 8665 | 761K| 7 (15)|
> > 00:00:01 | Q1,00 | PCWC | |
> > |* 4 | TABLE ACCESS FULL| TEST | 8665 | 761K| 7 (15)|
> > 00:00:01 | Q1,00 | PCWP | |
> > | Id | Operation | Name | Rows |
> > Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
> > | 0 | SELECT STATEMENT | | 8168
> > | 965K| 5 (20)| 00:00:01 | | | |
> > | 1 | PX COORDINATOR | |
> > | | | | | | |
> > | 2 | PX SEND QC (RANDOM) | :TQ10000 | 8168
> > | 965K| 5 (20)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
> > | 3 | PX BLOCK ITERATOR | | 8168
> > | 965K| 5 (20)| 00:00:01 | Q1,00 | PCWC | |
> > | 4 | EXTERNAL TABLE ACCESS FULL| ADMIN_EXT_EMPLOYEES | 8168
> > | 965K| 5 (20)| 00:00:01 | Q1,00 | PCWP | |
> | Id | Operation | Name | Rows | Cost (%CPU)|
> Time |
> -------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 1 | 29 (0)|
> 00:00:01 |
> | 1 | SORT AGGREGATE | | 1 |
> | |
> 00:00:01 |
>
> Statistiken
> ----------------------------------------------------------
> 24 recursive calls
> 0 db block gets
> 536 consistent gets
> 0 physical reads
> 0 redo size
> 418 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
Knowing that you're using XE answers this question:
Parallel query is not available in Express Edition (XE).
If possible upgrade to 10gR2 Standard or Enterprise edition (or 11.1.0
Standard or Enterprise) so you can use such functionality.
David Fitzjarrell
SePp
Hi David,
Thank youi very much for the information! You helped a lot!!!!
Kind regards.
Sebastian
Vladimir M. Zakharychev
>
> Knowing that you're using XE answers this question:
>
> Parallel query is not available in Express Edition (XE).
>
> If possible upgrade to 10gR2 Standard or Enterprise edition (or 11.1.0
> Standard or Enterprise) so you can use such functionality.
>
According to this document:
http://www.oracle.com/technology/products/database/oracle10g/pdf/twp_general_10gdb_product_family.pdf
Parallel Query/DML is *only* available in 10g Enterprise edition, SE
and SE1 don't have it either (page 13.) Not sure if things changed
with 11g.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com