Re: Advantage off parallel access at external tables

On Apr 4, 10:26 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> On Apr 4, 2:59 pm, SePp <C_o_z_..._at_gmx.de> wrote:
>
> > On Apr 4, 5:15 pm, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
>
> > > Comments embedded.
> > > On Apr 4, 7:33 am, SePp <C_o_z_..._at_gmx.de> wrote:
>
> > > > > 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
>
> > > 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 |
>
> > > ...
>
> > > 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
> > 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

Hi David,

Thank youi very much for the information! You helped a lot!!!!

Kind regards.

Sebastian Received on Sat Apr 05 2008 - 04:55:12 CDT