Re: Parallel Query

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Thu, 14 Jun 2001 13:18:35 +0200
Message-ID: <3B289D8B.ED6A4E98@0800-einwahl.de>

Your query cannot use the partition elimination because you do not specify a literal condition on your partition key. For achieving that in sqlplus, do the following:

SQL>
SQL> drop table ods_facts;

Table dropped.

SQL>
SQL> create table ods_facts (

  2  	     iteration_id number
  3  	     , period_id number
  4  	     , oem_revenue number

  5 )
  6 partition by range (iteration_id) (

  7  	     partition p1 values less than (2)
  8  	     , partition p2 values less than (3)
  9  	     , partition pm values less than (maxvalue)

 10 )
 11 /

Table created.

SQL>
SQL> drop table ods_iterations;

Table dropped.

SQL>
SQL> create table ods_iterations (

  2  	     iteration_id number
  3  	     , iteration_name varchar2 (50)
  4  	     , version varchar2 (50)

  5 )
  6 /

Table created.

SQL>
SQL> insert into ods_iterations (iteration_name, version, iteration_id)   2 values ('Iteration_1', 'Quarterly Forecast and Actuals - Feb 2000', 1);

1 row created.

SQL>
SQL> truncate table plan_table;

Table truncated.

SQL>
SQL> explain plan for
  2 select a.period_id,sum(nvl(a.oem_revenue,0))   3 from ods_facts a,
  4 ods_iterations b
  5 where a.iteration_id = b.iteration_id   6 and b.iteration_name = 'Iteration_1'   7 and b.version = 'Quarterly Forecast and Actuals - Feb 2000'   8 group by period_id
  9 /

Explained.

SQL> 
SQL> @@utlxpls.sql
SQL> Rem
SQL> Rem $Header: utlxpls.sql 28-jun-99.06:02:16 kquinn Exp $
SQL> Rem
SQL> Rem utlxpls.sql
SQL> Rem
SQL> Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      utlxpls.sql - UTiLity eXPLain Serial plans
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      script utility to display the explain plan of the last explain
plan
SQL> Rem      command. Do not display information related to Parallel Query
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      Assume that the PLAN_TABLE table has been created. The script
SQL> Rem      utlxplan.sql should be used to create that table
SQL> Rem
SQL> Rem      To avoid lines from truncating or wrapping around:
SQL> Rem	 'set charwidth 80' in svrmgrl
SQL> Rem	 'set linesize	80' in SQL*Plus
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    kquinn	06/28/99 - 901272: Add missing semicolon
SQL> Rem    bdagevil	05/07/98 - Explain plan script for serial plans
SQL> Rem    bdagevil	05/07/98 - Created
SQL> Rem
SQL> 
SQL> 
SQL> Rem
SQL> Rem Display last explain plan
SQL> Rem
SQL> select '| Operation		 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |'

as "Plan Table" from dual
  2 union all
  3 select

'--------------------------------------------------------------------------------'

from dual
  4 union all
  5 select rpad('| '||substr(lpad(' ',1*(level-1))||operation||

  6  		 decode(options, null,'',' '||options), 1, 27), 28, ' ')||'|'||
  7  	    rpad(substr(object_name||' ',1, 9), 10, ' ')||'|'||
  8  	    lpad(decode(cardinality,null,'  ',
  9  		     decode(sign(cardinality-1000), -1, cardinality||' ',
 10  		     decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
 11  		     decode(sign(cardinality-1000000000), -1,
trunc(cardinality/1000000)||'M',
 12  			    trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
 13  	    lpad(decode(bytes,null,' ',
 14  		     decode(sign(bytes-1024), -1, bytes||' ',
 15  		     decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
 16  		     decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
 17  			    trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
 18  	    lpad(decode(cost,null,' ',
 19  		     decode(sign(cost-10000000), -1, cost||' ',
 20  		     decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
 21  			    trunc(cost/1000000000)||'G'))), 8, ' ') || '|' ||
 22  	    lpad(decode(partition_start, 'ROW LOCATION', 'ROWID',
 23  		 decode(partition_start, 'KEY', 'KEY', decode(partition_start,
 24  		 'KEY(INLIST)', 'KEY(I)', decode(substr(partition_start, 1, 6),
 25  		 'NUMBER', substr(substr(partition_start, 8, 10), 1,
 26  		 length(substr(partition_start, 8, 10))-1),
 27  		 decode(partition_start,null,' ',partition_start)))))||' ', 7, ' ')|| '|'
||
 28  	    lpad(decode(partition_stop, 'ROW LOCATION', 'ROW L',
 29  	       decode(partition_stop, 'KEY', 'KEY', decode(partition_stop,
 30  	       'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6),
 31  	       'NUMBER', substr(substr(partition_stop, 8, 10), 1,
 32  	       length(substr(partition_stop, 8, 10))-1),
 33  	       decode(partition_stop,null,' ',partition_stop)))))||' ', 7, '

')||'|' as "Explain plan"
 34 from plan_table
 35 start with id=0 and timestamp = (select max(timestamp) from plan_table  36 where id=0)
 37 connect by prior id = parent_id

 38  	     and prior nvl(statement_id, ' ') = nvl(statement_id, ' ')
 39  	     and prior timestamp <= timestamp

 40 union all
 41 select

'--------------------------------------------------------------------------------'
from dual;


| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |

 --------------------------------------------------------------------------------


| SELECT STATEMENT          |          |     5 |  530 |     17 |       |       |

|  SORT GROUP BY            |          |     5 |  530 |     17 |       |       |

|   NESTED LOOPS            |          |     5 |  530 |      2 |       |       |

|    TABLE ACCESS FULL      |ODS_ITERA |     1 |   67 |      1 |       |       |

|    PARTITION RANGE ITERATO|          |       |      |        |   KEY |   KEY |

|     TABLE ACCESS FULL     |ODS_FACTS |   492 |   18K|      1 |   KEY |   KEY |

--------------------------------------------------------------------------------

9 rows selected.

SQL>
SQL> truncate table plan_table;

Table truncated.

SQL> 
SQL> column partval new_value partval
SQL> 
SQL> select b.iteration_id as partval

  2 from ods_iterations b
  3 where b.iteration_name = 'Iteration_1'   4 and b.version = 'Quarterly Forecast and Actuals - Feb 2000'   5 /

         1

SQL>
SQL> explain plan for
  2 select a.period_id,sum(nvl(a.oem_revenue,0))   3 from ods_facts a
  4 where a.iteration_id = to_number ('&&partval.')   5 group by period_id
  6 /
old 4: where a.iteration_id = to_number ('&&partval.') new 4: where a.iteration_id = to_number (' 1')

Explained.

SQL> 
SQL> @@utlxpls.sql
SQL> Rem
SQL> Rem $Header: utlxpls.sql 28-jun-99.06:02:16 kquinn Exp $
SQL> Rem
SQL> Rem utlxpls.sql
SQL> Rem
SQL> Rem  Copyright (c) Oracle Corporation 1998, 1999. All Rights Reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem      utlxpls.sql - UTiLity eXPLain Serial plans
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem      script utility to display the explain plan of the last explain
plan
SQL> Rem      command. Do not display information related to Parallel Query
SQL> Rem
SQL> Rem    NOTES
SQL> Rem      Assume that the PLAN_TABLE table has been created. The script
SQL> Rem      utlxplan.sql should be used to create that table
SQL> Rem
SQL> Rem      To avoid lines from truncating or wrapping around:
SQL> Rem	 'set charwidth 80' in svrmgrl
SQL> Rem	 'set linesize	80' in SQL*Plus
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    kquinn	06/28/99 - 901272: Add missing semicolon
SQL> Rem    bdagevil	05/07/98 - Explain plan script for serial plans
SQL> Rem    bdagevil	05/07/98 - Created
SQL> Rem
SQL> 
SQL> 
SQL> Rem
SQL> Rem Display last explain plan
SQL> Rem
SQL> select '| Operation		 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |'

as "Plan Table" from dual
  2 union all
  3 select

'--------------------------------------------------------------------------------'

from dual
  4 union all
  5 select rpad('| '||substr(lpad(' ',1*(level-1))||operation||

  6  		 decode(options, null,'',' '||options), 1, 27), 28, ' ')||'|'||
  7  	    rpad(substr(object_name||' ',1, 9), 10, ' ')||'|'||
  8  	    lpad(decode(cardinality,null,'  ',
  9  		     decode(sign(cardinality-1000), -1, cardinality||' ',
 10  		     decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
 11  		     decode(sign(cardinality-1000000000), -1,
trunc(cardinality/1000000)||'M',
 12  			    trunc(cardinality/1000000000)||'G')))), 7, ' ') || '|' ||
 13  	    lpad(decode(bytes,null,' ',
 14  		     decode(sign(bytes-1024), -1, bytes||' ',
 15  		     decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
 16  		     decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
 17  			    trunc(bytes/1073741824)||'G')))), 6, ' ') || '|' ||
 18  	    lpad(decode(cost,null,' ',
 19  		     decode(sign(cost-10000000), -1, cost||' ',
 20  		     decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
 21  			    trunc(cost/1000000000)||'G'))), 8, ' ') || '|' ||
 22  	    lpad(decode(partition_start, 'ROW LOCATION', 'ROWID',
 23  		 decode(partition_start, 'KEY', 'KEY', decode(partition_start,
 24  		 'KEY(INLIST)', 'KEY(I)', decode(substr(partition_start, 1, 6),
 25  		 'NUMBER', substr(substr(partition_start, 8, 10), 1,
 26  		 length(substr(partition_start, 8, 10))-1),
 27  		 decode(partition_start,null,' ',partition_start)))))||' ', 7, ' ')|| '|'
||
 28  	    lpad(decode(partition_stop, 'ROW LOCATION', 'ROW L',
 29  	       decode(partition_stop, 'KEY', 'KEY', decode(partition_stop,
 30  	       'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6),
 31  	       'NUMBER', substr(substr(partition_stop, 8, 10), 1,
 32  	       length(substr(partition_stop, 8, 10))-1),
 33  	       decode(partition_stop,null,' ',partition_stop)))))||' ', 7, '

')||'|' as "Explain plan"
 34 from plan_table
 35 start with id=0 and timestamp = (select max(timestamp) from plan_table  36 where id=0)
 37 connect by prior id = parent_id

 38  	     and prior nvl(statement_id, ' ') = nvl(statement_id, ' ')
 39  	     and prior timestamp <= timestamp

 40 union all
 41 select

'--------------------------------------------------------------------------------'
from dual;


| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |

 --------------------------------------------------------------------------------


| SELECT STATEMENT          |          |     2 |   78 |     16 |       |       |

|  SORT GROUP BY            |          |     2 |   78 |     16 |       |       |

|   TABLE ACCESS FULL       |ODS_FACTS |     2 |   78 |      1 |     1 |     1 |

--------------------------------------------------------------------------------

6 rows selected.

SQL>
SQL> spool off

As you can see the partition elimination requires you to specify the partition key as a literal (the "to_number ()" is needed because the '&&partval.' may yield null. This would cause an syntax error if used directly as number.).

By the way, the utlxpls.sql can be found in $ORACLE_HOME/rdbms/admin.

Martin

Abhijit Bhattacharya wrote:

> 
> I have SQL optimization and tuning questions.
> 
> select a.period_id,sum(nvl(a.oem_revenue,0))
> from ods_facts a,
> ods_iterations b
> where a.iteration_id = b.iteration_id
> and b.iteration_name = 'Iteration_1'
> and b.version = 'Quarterly Forecast and Actuals - Feb 2000'
> group by period_id
> 
> This query goes against our data warehousing database and takes approx 4
> minutes
> to execute. ods_facts is the fact table with number of dimensions.
> ods_iterations is
> one such dimension table with less than 200 records. Iteration ID is the
> 
> primary key in ods_iterations table. Fact table is having more than 11
> million
> records and is range partitioned by iteration id. The last two where
> conditions in where clause uniquely identifies one iteration and
> thus forces access to one and only one partition in
> ods_facts table. This partition contains approx 300,000 rows.
> What are the methods of speeding up this query ? (I already have local
> bitmap index
> on iteration_id column in ods_facts table and increased degree of
> parallelism of
> ods_facts to 6. We have both Oracle 8.0.5 and Oracle 8.1.6
> installations.)
> While doing another experiment and I copied these 300,000 rows into
> another
> table using "Create As Select" statement, created related indexes and
> accidentally executed similar query. In this situation it takes less
> than 1 minute. Since ods_facts is partitioned
> and my query was accessing only one partition so I was assuming the run
> time
> of second query to be same as first one. How can I explain the
> difference ?
> 
> How do I confirm that the database is really using parallel query
> processing ? I will appreciate any input. Please copy my direct email
> address also (abhijit_bhattach_at_hotmail.com)
> 
> Here is the explain plan for the above query
> 
> Execution Plan
> ----------------------------------------------------------
>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=56 Card=12 Bytes=768 )
> 
>    1    0   SORT* (GROUP BY) (Cost=56 Card=12 Bytes=768)
> :Q65002
 2    1     SORT* (GROUP BY) (Cost=56 Card=12 Bytes=768)
> :Q65001
 3    2       NESTED LOOPS* (Cost=8 Card=49689 Bytes=3180096)
> :Q65001
>    4    3         TABLE ACCESS* (FULL) OF 'ODS_ITERATIONS' (Cost=1
> Card=1 Bytes=61)   :Q65000
>    5    3         PARTITION RANGE* (ITERATOR)   !
> :Q65001
>    6    5           TABLE ACCESS* (FULL) OF 'ODS_FACTS' (Cost=71
> Card=12521626 Bytes=37564878) :Q65001
> 
>    1 PARALLEL_TO_SERIAL            SELECT /*+ CIV_GB */
> A1.C0,SUM(SYS_OP_CSR(A1
>                                    .C1,0)) FROM :Q65001 A1 GROUP BY A1.
> 
>    2 PARALLEL_TO_PARALLEL          SELECT /*+ PIV_GB */ A1.C1
> C0,SYS_OP_MSR(SUM
>                                    (NVL(A1.C2,0))) C1 FROM (SELECT /*+
> 
>   ! ; 3 PARALLEL_COMBINED_WITH_PARENT
>    4 PARALLEL_TO_PARALLEL          SELECT /*+ NO_EXPAND ROWID(A1) */
> A1."ITERAT
>                                    ION_ID" C0 FROM "ODS_ITERATIONS" A1
> 

> 5 PARALLEL_COMBINED_WITH_PARENT
> 6 PARALLEL_COMBINED_WITH_PARENT Received on Thu Jun 14 2001 - 06:18:35 CDT