Re: Advantage off parallel access at external tables

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 Received on Fri Apr 04 2008 - 15:26:50 CDT