Re: Advantage off parallel access at external tables

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

| Id  | Operation                   | Name    | Rows  | Cost (%CPU)|
Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    29   (0)|
00:00:01 |
|   1 |  SORT AGGREGATE             |         |     1 |
|          |

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