Re: Advantage off parallel access at external tables

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

| 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

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