Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Ravi.

Asked: October 09, 2000 - 12:28 am UTC

Last updated: March 29, 2011 - 3:21 am UTC

Version: 805

Viewed 10K+ times! This question is

You Asked

Dear Tom,

I am testing our application for improving performance against parallel query. One table have 8 million rows and thought parallel query on full table scan of that table will improve performance. On the contrary, I get less performance with parallel query.

I altered that table for degree 4
No one is using that db except me
parallel_min_servers=4
parallel_max_servers=30
All the other parallel init.ora parameters are left to default

The query used in parallel query are

select /*+ PARALLEL (t_name, 4) */ count(*) from t_name;
select /*+ PARALLEL (t_name, 4) */ max(trx_amt) from t_name;

The query used in Non parallel are

select /*+ NOPARALLEL (t_name) */ count(*) from t_name;
select /*+ NOPARALLEL (t_name) */ max(trx_amt) from t_name;


For the same query, I got the answer in non parallel mode in 59secs, where as the same is taking around 2min20sec in parallel mode. I repeatedly got the answers with the same response time.

Will parallelism on full table scan always not improve the performance. Do I need to set any other parameters. Please give your suggestion.

Regards
Ravi

and Tom said...

Well, lets see the plans. I'll theorize here.

select /*+ PARALLEL (t_name, 4) */ count(*) from t_name;

will do a parallel 4 full tablespace on the table T_NAME. If T_name is "wide" this will be alot of data to scan. On the other hand:

select /*+ NOPARALLEL (t_name) */ count(*) from t_name;

will do an INDEX FAST FULL SCAN on the primary key or any index on a non-null column to count the rows. As the index is much smaller (perhaps much less then 1/4 of the width of the table) this has infinitely less data to shift through and will run in a very small fracation of the time -- even though its a single thread (this single thread has less data to read then any of the 4 PQ slaves above given the index is "skinny" )

As for the MAX() -- I'll make the same guess. TRX_AMT is indexed and the parallel 4 goes for the full tablecan and noparallel just uses an index range scan or index fast full scan.


We ALWAYS need the plans to answer definitively -- otherwise we are just guessing.

Also remember -- doing lots of things simultaneously is not always going to be faster then doing one thing intelligently. Putting 10 programmers to work on a subroutine might take longer then letting 1 good programmer work on it. Same with SQL -- PQO is a tool, not a silver bullet. It will be faster sometimes and sometimes -- NOT.

So, if you can get the plans -- feel free to followup with a new question (autotrace in sqlplus or tkprof+sql_trace+timed_statistics will do that for you)


Rating

  (107 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Parallel execution doesnt show in the explain plan- why

A reader, October 11, 2001 - 6:48 pm UTC

SQL> select /*+ PARALLEL (T,4) */ count(*) from T;

  COUNT(*)
----------
   1003453

Elapsed: 00:01:67.12
SQL> set autotrace on
SQL> /

  COUNT(*)
----------
   1003453

Elapsed: 00:01:72.06

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6358 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T(Co
          st=6358 Card=5399048)


Tom, can you conclusively show us a query using parallel query , and getting benefitted from it.

That will help us tide over the starting problem. 

Tom Kyte
October 11, 2001 - 7:02 pm UTC

I don't think you have PQ setup.  It is most likely just doing a full scan.  Check your PARALLEL settings (show parameter parallel)

ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace off
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2          l_n number;
  3          l_start number default dbms_utility.get_time;
  4  begin
  5          select count(*) into l_n from big;
  6          dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
  7  end;
  8  /
106 hsecs

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /
99 hsecs

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2          l_n number;
  3          l_start number default dbms_utility.get_time;
  4  begin
  5  
  6          select /*+ FULL(big) PARALLEL(big,4) */ count(*) into l_n
  7        from big;
  8          dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' );
  9  end;
 10  /
69 hsecs

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> /
66 hsecs

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on
ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from big
  2  /

  COUNT(*)
----------
    540064


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'BIG'




Statistics
----------------------------------------------------------
          0  recursive calls
         45  db block gets
       7382  consistent gets
       7367  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ FULL(big) PARALLEL(big,4) */
  2  count(*)
  3  from big
  4  /

  COUNT(*)
----------
    540064


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=7 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     SORT* (AGGREGATE)                                                                                :Q14000
   3    2       TABLE ACCESS* (FULL) OF 'BIG' (Cost=7 Card=18869)                                              :Q14000


   2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
                                   ROM (SELECT /*+ NO_EXPAND ROWID(A2) */ 0 FRO
                                   M "BIG" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)
                                    A2) A1

   3 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
          0  recursive calls
        633  db block gets
       7578  consistent gets
       7367  physical reads
          0  redo size
        369  bytes sent via SQL*Net to client
        430  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed 

Setup for parallel query

mike, October 13, 2001 - 11:27 am UTC

Tom,
I have never got the parallel qyery work.
Please post your method of setup, if you do not mind.

Tom Kyte
October 13, 2001 - 1:32 pm UTC

I just have one init.ora:

parallel_max_servers = 25

and the others are default:


ops$tkyte@ORA817DEV.US.ORACLE.COM> show parameter parallel

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
fast_start_parallel_rollback         string  LOW
optimizer_percent_parallel           integer 0
parallel_adaptive_multi_user         boolean FALSE
parallel_automatic_tuning            boolean FALSE
parallel_broadcast_enabled           boolean FALSE
parallel_execution_message_size      integer 2148
parallel_instance_group              string
parallel_max_servers                 integer 25
parallel_min_percent                 integer 0
parallel_min_servers                 integer 0
parallel_server                      boolean FALSE
parallel_server_instances            integer 1
parallel_threads_per_cpu             integer 2
recovery_parallelism                 integer 0

 

you have to run it to show the parallel query used

mike, October 13, 2001 - 4:57 pm UTC

Thanks tom.
I had to run the query to find out if the PQ used:

SQL> explain plan for select count(*) from t;

Explained.

SQL>  @plan

QUERY_PLAN
------------------------------------------------------------
  SORT AGGREGATE
    SORT AGGREGATE
      TABLE ACCESS FULL T


4 rows deleted.

$$$ it does not show the PQ used

SQL> set autotrace trace
SQL> select count(*) from t;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=104 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     SORT* (AGGREGATE)                                        :Q8000
   3    2       TABLE ACCESS* (FULL) OF 'T' (Cost=104 Card=13784)      :Q8000


   2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
                                   ROM (SELECT /*+ NO_EXPAND ROWID(A2)

   3 PARALLEL_COMBINED_WITH_PARENT

$$ it is 8.1.5 

the BIG in your parallel query test

mike, October 15, 2001 - 2:47 pm UTC

Tom,
I have hardly obtained better performance than non parallel. And I want to duplicate your test. What is the definition of BIG and how to create it?

Tom Kyte
October 15, 2001 - 4:44 pm UTC

My big was:

create table big as select * from all_objects;

insert /*+ append */ into big select * from big;
commit;

(repeat above two until there are lots of rows).


how many cpus do you have, how is your disk, what type of disk. lots of things affect this.

Worked, but at the expense of resources (as expected)

mike, October 15, 2001 - 5:29 pm UTC

SQL> declare
  2   l_n number;
  3   l_start number default dbms_utility.get_time;
  4  begin
  5   select count(*) into l_n from big;
  6   dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' 
  7   );
  8  end;
  9  /
324 hsecs

PL/SQL procedure successfully completed.

SQL> declare
  2  l_n number;
  3  l_start number default dbms_utility.get_time;
  4  begin
  5  select /*+ FULL(big) PARALLEL(big,4) */ count(*) into l_n
  6  from big;
  7  dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs' 
  8  );
  9  end;
 10  /
98 hsecs

PL/SQL procedure successfully completed.

SQL> select count(*) from big;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'BIG'




Statistics
----------------------------------------------------------
          0  recursive calls
         48  db block gets
       7842  consistent gets
       7773  physical reads
          0  redo size
        601  bytes sent via SQL*Net to client
        467  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select /*+ FULL(big) PARALLEL(big,4) */ count(*) from big;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     SORT* (AGGREGATE)                                        :Q18000
   3    2       TABLE ACCESS* (FULL) OF 'BIG' (Cost=5 Card=10047)      :Q18000


   2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
                                   ROM (SELECT /*+ NO_EXPAND ROWID(A2)

   3 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
          0  recursive calls
       2205  db block gets
       8561  consistent gets
       7826  physical reads
          0  redo size
        601  bytes sent via SQL*Net to client
        500  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
 

cbo

A reader, October 17, 2001 - 5:55 pm UTC

In order to use parallel query , is it necessary to switch to cbo, or to rephrase it, will using parallel switch the optimizer to cbo.

Tom Kyte
October 17, 2001 - 6:56 pm UTC

parallel query implies CBO, you will be using CBO with parallel query.

parallel query works only when I give table alias- why

Nag, October 17, 2001 - 6:49 pm UTC

Tom

Look below , the parallel query works only when I give the table alias, why is this happening. 


SQL> select /*+ FULL(dev.t) PARALLEL(a,4) */
  2     count(*)  from  dev.t a ;

  COUNT(*)
----------
134234

Elapsed: 00:00:38.78

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6358 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     SORT* (AGGREGATE)                                        :Q239000
   3    2       TABLE ACCESS* (FULL) OF 'PORT_POSN_DEAL_DAILY_011010'  :Q239000
          (Cost=6358 Card=5399048)



   2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
                                   ROM (SELECT /*+ NO_EXPAND ROWID(A2)

   3 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
         19  recursive calls
        323  db block gets
      66207  consistent gets
      66100  physical reads
        692  redo size
        209  bytes sent via SQL*Net to client
        253  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select /*+ FULL(dev.t) PARALLEL(dev.t,4)
  2  count(*)  from  dev.t ;
select /*+ FULL(dev.t) PARALLEL(dev.t,4)
       *
ERROR at line 1:
ORA-01742: comment not terminated properly


Elapsed: 00:00:02.97
SQL> select /*+ FULL(dev.t) PARALLEL(dev.t,4) co
unt(*)  from  dev.t ;
select /*+ FULL(dev.t) PARALLEL(dev.t,4) count(*
       *
ERROR at line 1:
ORA-01742: comment not terminated properly


SQL> select /*+ FULL(dev.t) PARALLEL(dev.t,4) */
 count(*)  from  dev.t ;

  COUNT(*)
----------
134234

Elapsed: 00:01:107.36

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6358 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'PORT_POSN_DEAL_DAILY_011010' (Co
          st=6358 Card=5399048)





Statistics
----------------------------------------------------------
          0  recursive calls
         21  db block gets
      66107  consistent gets
      66094  physical reads
          0  redo size
        210  bytes sent via SQL*Net to client
        253  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>  

Tom Kyte
October 17, 2001 - 7:19 pm UTC

cause the documentation says that is the way it works:

</code> http://docs.oracle.com/docs/cd/A81042_01/DOC/server.816/a76992/hints.htm#4926 <code>

"The PARALLEL hint must use the table alias if an alias is specified in the query."

(please try to clean up your examples -- the syntax errors just make it confusing)


Nag, October 18, 2001 - 11:10 am UTC

"The PARALLEL hint must use the table alias if an alias is specified in the query."

Tom , kindly observe theuse of the word 'if' in the above sentence.

That means, in effect if no alias is using in the query , the query should still use parallel query. That also means that alias is a not mandatory for parallel query.

In the example I gave a above( sorry for posting it with cleaning it up), the query is not doing parallel query if I dont use the table alias. That is my concern.

It works when I use table alias, but it does not work when no table alias is used in the query. This is telling me contrary to what I find in the documentation -- i.e. use of a table alias is in fact mandatory to use parallel query.

Kindly clarify.


Tom Kyte
October 18, 2001 - 1:04 pm UTC

because you used the owner.tablename, not just the tablename as it said.  Consider:

ops$tkyte@ORA717DEV.US.ORACLE.COM> select /*+ full(big) parallel(big,2) */ count(*)
  2  from ops$tkyte.big
  3  /

  COUNT(*)
----------
         0

Elapsed: 00:00:00.72

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     SORT* (AGGREGATE)                                                                                :Q18000
   3    2       TABLE ACCESS* (FULL) OF 'BIG' (Cost=1 Card=82)                                                 :Q18000


   2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
                                   ROM (SELECT /*+ NO_EXPAND ROWID(A2) */ 0 FRO
                                   M "OPS$TKYTE"."BIG" PX_GRANULE(0, BLOCK_RANG
                                   E, DYNAMIC)  A2) A1

   3 PARALLEL_COMBINED_WITH_PARENT

ops$tkyte@ORA717DEV.US.ORACLE.COM> select /*+ full(big) parallel(<b>ops$tkyte.big,2</b>) */ count(*)
  2  from ops$tkyte.big
  3  /

  COUNT(*)
----------
         0

Elapsed: 00:00:00.70

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'BIG' (Cost=1 Card=82)

 

CTAS with no logging and parallel degree of 5

A reader, November 02, 2001 - 3:13 pm UTC

Tom

We have 5 tables and all have one to one relationship.
We are create table t with all the data from all the 5 tables.

The total number of rows are going to be 25 million.

We are trying to use no logging feature. But we want to know how to specify the parallel degree along with this DDL.

Kindly guide us.


Tom Kyte
November 02, 2001 - 8:40 pm UTC

see
</code> http://docs.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c22paral.htm#5579 <code>
for full examples and documentation on how to do this.

alter session enable parallel dml

A reader, November 05, 2001 - 6:29 am UTC

Tom, in book from J.Couchman I have read:"User must remember to issue the alter session enable parallel DML" in order to use parallel DML, but I not saw you do it. And I have ever thought, I should enable the use of parallel DML by create table using parallel clause too.
Can you explain me what need I really set? For example, is my update statememt will use parallel DML if I have not issued alter session enable parallel, but by create table
parallel clausel was used? Or in my session parallel DML is enabled, but by create table not?
How can I see if parallel DML is enabled in session. Show parameter not work hier?

Tom Kyte
November 05, 2001 - 8:15 am UTC

you didn't see me do that for the simple reason that I did not do any parallel DML (insert/update/delete) in any of these examples.

I did parallel queries -- selects -- none of which need parallel dml enables.

You do not need parallel DML enabled to create a table in parallel, see the link in the above comment for how to achieve this.


If you want to do a parallel update, delete or insert, you will be enabling parallel dml for your session. See
</code> http://docs.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c22paral.htm#880 <code>
for details.

There is no way to see if PDML is enabled -- but it is perfectly safe to enable it more then once so you can just enable it if you are not sure if it is enabled or not.


A reader, November 05, 2001 - 11:32 am UTC

Tom, what I really mean in my question is parallel options
specified in the create table statment for later use of parallelism in select or DML statement, not during the creation of table, not create as select.
create table test ... parallel(degree 4);
How does it works?
Works it as parallel hint? And what happens if table was created with parallel clause and parallelism is disabled in session? Should I ever use parallel hint to force oracle to use PDML?
Sorry for misunderstanding, english is not my native langvuage.

Tom Kyte
November 05, 2001 - 1:01 pm UTC

If you:

create table tt ( owner, cnt ) parallel 5
as
select owner, count(*) from t group by owner;

It will create the table in paralle AND it will set the default degree of parallelism for the table to 5.

It'll do both. But this is NOT PDML, this is PDDL.

You really want to read the Oracle Server Concepts manual to get a better, full understanding of this. PDML, Parallel Query, PDDL -- they are all explained in there with examples.

See
</code> http://docs.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/part_6.htm#435986 <code>
...

The way to see if PDML is enabled

Olaf, November 05, 2001 - 11:38 am UTC

I can see this in PDML_ENABLED column in v$session.

Tom Kyte
November 05, 2001 - 12:33 pm UTC

doh, you are right -- sorry about that

Is multiple CPU required to enable parallel query?

Tony, July 29, 2003 - 2:09 am UTC

My server has only one CPU. Is it possible to do parallel query?


Tom Kyte
July 29, 2003 - 7:05 am UTC

can you run more then one program at a time on your computer?

if so, you can do parallel query.

PQ not working

A reader, October 20, 2003 - 7:03 am UTC

Hi

I dont undestand why we cannot use PQ in our server

select name, value from v$parameter where name like '%parallel%';

NAME VALUE
------------------------------------ ----------------------------------------
parallel_server FALSE
parallel_server_instances 1
recovery_parallelism 0
fast_start_parallel_rollback LOW
parallel_broadcast_enabled FALSE
parallel_adaptive_multi_user FALSE
parallel_threads_per_cpu 2
parallel_automatic_tuning FALSE
optimizer_percent_parallel 0
parallel_min_percent 0
parallel_min_servers 0
parallel_max_servers 12
parallel_instance_group
parallel_execution_message_size 2148

set autotrace trace exp

select /*+ parallel(a,4) */ * from GCIGT_INGRESOS_AF_TDE_RN a

Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54474 Card=15080420 Bytes=3800265840)
1 0 PARTITION RANGE* (ALL) :Q84000
2 1 TABLE ACCESS* (FULL) OF 'GCIGT_INGRESOS_AF_TDE_RN' (Cost=54474 Card=15080420 Bytes=3800265840) :Q84000
1 PARALLEL_COMBINED_WITH_PARENT
2 PARALLEL_TO_SERIAL
SELECT /*+ Q84000 NO_EXPAND ROWID(A1) */ A1."IGINA_ANIO",A1."IGINA_COD_CLIENTE_S

set autotrace on
select /*+ parallel(a,4) */ * from GCIGT_INGRESOS_AF_TDE_RN a

select * from v$pq_sysstat;

STATISTIC VALUE
------------------------------ ----------
Servers Busy 0
Servers Idle 0
Servers Highwater 0
Server Sessions 0
Servers Started 0
Servers Shutdown 0
Servers Cleaned Up 0
Queries Initiated 0
DML Initiated 0
DFO Trees 0
Sessions Active 0
Local Msgs Sent 0
Distr Msgs Sent 0
Local Msgs Recv'd 0
Distr Msgs Recv'd 0

Anything wrong with my setup?



Tom Kyte
October 20, 2003 - 8:46 am UTC

well, the query plan says "parallel"

did you actually "run" the query? how about a cut and paste of:

set autotrace on
select /*+ parallel(a,4) */ COUNT(*) from GCIGT_INGRESOS_AF_TDE_RN a;
set autotrace off
select * from v$pq_sysstat;




yes I did run

A reader, October 20, 2003 - 10:02 am UTC

Hi

I ran the query, the results of v$pq_sysstat I posted is after I ran the query!

Tom Kyte
October 20, 2003 - 10:34 am UTC

hows about that cut and paste for me. cut and paste -- no editing!!!

PDML

shankar, October 20, 2003 - 10:46 am UTC

Tom, If I had to do PDML, does the table need to be a partitioned table? If I have a table that is stiped across disks using RAID, why can't I do PDML on this? Thanks.

Tom Kyte
October 20, 2003 - 11:00 am UTC

depends on the version.

9i does pdml without partitioning
8i did pdml with partitioning


RAID has nothing to do with it at our level. We don't really know if you are on raid or what. it is not really relevant.

performance issues with partitioning....

Sam Bernard, October 20, 2003 - 11:09 am UTC

Any known performance issues with partitioning of tables that one should know before designing tables for partitioning? Is there any document or link that you could guide me to learn about partitioning issues in relation to performance would be greatly appreciated. Thanks.

Tom Kyte
October 20, 2003 - 11:28 am UTC

well, -- in both of my books I've written on this topic. You need to UNDERSTAND what is happening under the covers (the example I used in my last book was someone who simply HASH partitioned an OLTP table and locally partitioned all indexes. the result -- the system did 8 TIMES the IO on this table. They didn't understand what they had just done, didn't design to use partitioning, just turned it on and said "go fast". that'll never fly)

You need to understand how the structures are implemented -- what you'll be doing to the data. Unless you do tons of full scans, partitioning probably won't make you go "faster" -- but it can certainly make you go lots slower if you don't design to use it.

You will want to read
</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/toc.htm <code>

just ctl-f for partition to see some of the relevant chapters.

partition

Reader, October 20, 2003 - 1:15 pm UTC

I was trying to create a paritioned table per your demo in your new book. I was wondering why it is using the "regular" tablespace demo for temp segment instead of the usual temp tablespace. Thanks.

SQL> create table b_hashed nologging
  2  partition by hash(object_id) partitions 10
  3  tablespace demo
  4  as
  5  select * from b;
select * from b
              *
ERROR at line 5:
ORA-01652: unable to extend temp segment by 640 in tablespace DEMO
 

Tom Kyte
October 20, 2003 - 2:17 pm UTC

when you create a table oracle does this:

a) allocates an extent - this extent is marked as temporary
b) fills it
c) upon completion - it will turn the TEMPORARY extents into "real ones"


the reason it does this is that if the system fails for whatever reason during a) or b) -- we don't need to do anything to recover. upon system restart SMON will simply clean up the temporary extents.


so, CREATE TABLE as select, CREATE INDEX and others will use temporary extents while they are creating and then convert them over to real ones when done.

It is not "temp" like sort space "temp", it is temp as in "if we fail, get rid of this junk, if we succeed, we'll just turn it into real stuff"

pasting here

A reader, October 20, 2003 - 3:37 pm UTC

Parameters are in previous post, cheers

sql>select table_name, num_rows, partitioned, degree from user_tables where num_rows > 10000000;

TABLE_NAME NUM_ROWS PAR DEGREE
------------------------------ ---------- --- ----------
GCIGT_INGRESOS_AF_TDE_RN 15080420 YES 1
GCIGT_INGRESOS_TDE_RN 13471688 NO 1


sql>set autotrace trace exp
sql>col plus_plan_exp for a100
sql>set line 140
sql>select /*+ parallel(a,4) */ * from tgcigt10.GCIGT_INGRESOS_AF_TDE_RN a;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54474 Card=15080420 Bytes=3800265840)
1 0 PARTITION RANGE* (ALL) :Q88000
2 1 TABLE ACCESS* (FULL) OF 'GCIGT_INGRESOS_AF_TDE_RN' (Cost=54474 Card=15080420 Bytes=3800265840) :Q88000
1 PARALLEL_COMBINED_WITH_PARENT
2 PARALLEL_TO_SERIAL SELECT /*+ Q88000 NO_EXPAND ROWID(A1) */
A1."IGINA_ANIO",A1."IGINA_COD_CLIENTE_S

sql>set autotrace trace exp stat
sql>select /*+ parallel(a,4) */ * from tgcigt10.GCIGT_INGRESOS_AF_TDE_RN a;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=54474 Card=15080420 Bytes=3800265840)
1 0 PARTITION RANGE* (ALL) :Q90000
2 1 TABLE ACCESS* (FULL) OF 'GCIGT_INGRESOS_AF_TDE_RN' (Cost=54474 Card=15080420 Bytes=3800265840):Q90000
1 PARALLEL_COMBINED_WITH_PARENT
2 PARALLEL_TO_SERIAL SELECT /*+ Q90000 NO_EXPAND ROWID(A1) */ A1."IGINA_ANIO",A1."IGINA_COD_CLIENTE_S

Statistics
----------------------------------------------------------
19 recursive calls
1340 db block gets
1154037 consistent gets
566654 physical reads
692 redo size
SP2-0642: Error interno SQL*Plus estado 1075, contexto 1:5:0
No es seguro continuar
67390856 bytes received via SQL*Net from client
607123 SQL*Net roundtrips to/from client
26 sorts (memory)
0 sorts (disk)
15178027 rows processed

sql>select * from v$pq_sysstat;

STATISTIC VALUE
------------------------------ ----------
Servers Busy 0
Servers Idle 0
Servers Highwater 0
Server Sessions 0
Servers Started 0
Servers Shutdown 0
Servers Cleaned Up 0
Queries Initiated 0
DML Initiated 0
DFO Trees 0
Sessions Active 0
Local Msgs Sent 0
Distr Msgs Sent 0
Local Msgs Recv'd 0
Distr Msgs Recv'd 0


Tom Kyte
October 20, 2003 - 8:02 pm UTC

while this query is running -- query the various v$px views (eg: v$px_process)

if you don't see any, we'll continue here.

if you do, contact support and file a tar to see whats up with that v$ view not getting filled in.

there is no parallel process

A reader, October 21, 2003 - 3:24 am UTC

Hi

While I was running I checked at OS level as well and see if there are pxxx processes.

ps -ef |grep gc00
oracle81 3730 1 0 23:15:20 ? 0:03 ora_smon_e81gc00
oracle81 3754 1 0 23:15:21 ? 0:00 ora_reco_e81gc00
oracle81 27569 1 0 09:19:43 ? 0:00 oraclee81gc00 (LOCAL=NO)
oracle81 3699 1 0 23:15:20 ? 0:00 ora_pmon_e81gc00
oracle81 23260 1 1 08:35:42 ? 36:54 oraclee81gc00 (LOCAL=NO)
oracle81 3704 1 0 23:15:20 ? 3:54 ora_lgwr_e81gc00
oracle81 4095 4093 0 23:18:26 ? 2:54 oraclee81gc00 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle81 27571 27508 0 09:19:46 pts/11 0:00 grep gc00
oracle81 3701 1 0 23:15:20 ? 1:14 ora_dbw0_e81gc00
oracle81 27524 1 0 09:18:56 ? 0:00 oraclee81gc00 (LOCAL=NO)
oracle81 27472 27470 1 09:18:27 ? 0:12 oraclee81gc00 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle81 27283 1 3 09:15:47 ? 1:31 oraclee81gc00 (LOCAL=NO)
oracle81 24583 1 0 08:57:37 ? 6:38 oraclee81gc00 (LOCAL=NO)
oracle81 3712 1 0 23:15:20 ? 0:26 ora_ckpt_e81gc00

None!

select * from v$px_process;

no rows selected

:-0







Tom Kyte
October 21, 2003 - 7:36 am UTC

should need to but can you alter that table "parallel" and try that.

still no px process

A reader, October 21, 2003 - 7:42 am UTC

Hi, here is what I did

alter table tgcigt10.GCIGT_INGRESOS_AF_TDE_RN parallel 4;

select table_name, degree from dba_tables where degree > 1;

TABLE_NAME DEGREE
------------------------------ ----------
GCIGT_INGRESOS_AF_TDE_RN 4

set autotrace trace exp stat
select /*+ parallel(a,4) */ * from tgcigt10.GCIGT_INGRESOS_AF_TDE_RN a;

while running it in another session did this

select * from v$px_process;

no rows selected

ps -ef |grep gc00
oracle81 3730 1 0 23:15:20 ? 0:05 ora_smon_e81gc00
oracle81 3754 1 0 23:15:21 ? 0:00 ora_reco_e81gc00
oracle81 24074 1 0 13:29:09 ? 0:23 oraclee81gc00 (LOCAL=NO)
oracle81 23017 1 0 13:15:31 ? 2:48 oraclee81gc00 (LOCAL=NO)
oracle81 3699 1 0 23:15:20 ? 0:00 ora_pmon_e81gc00
oracle81 3704 1 0 23:15:20 ? 7:08 ora_lgwr_e81gc00
oracle81 4095 4093 0 23:18:26 ? 4:20 oraclee81gc00 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle81 25253 25044 0 13:38:28 pts/2 0:00 grep gc00
oracle81 21238 21237 0 13:05:57 ? 0:00 oraclee81gc00 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle81 15605 1 0 12:09:22 ? 0:23 oraclee81gc00 (LOCAL=NO)
oracle81 9230 1 5 10:52:08 ? 156:52 oraclee81gc00 (LOCAL=NO)
oracle81 25143 25142 1 13:36:20 ? 0:05 oraclee81gc00 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle81 3158 1 0 10:00:05 ? 0:02 oraclee81gc00 (LOCAL=NO)
oracle81 3701 1 0 23:15:20 ? 2:33 ora_dbw0_e81gc00
oracle81 27524 1 0 09:18:56 ? 0:00 oraclee81gc00 (LOCAL=NO)
oracle81 3712 1 0 23:15:20 ? 0:48 ora_ckpt_e81gc00


!!!!! What´s is wrong?

Tom Kyte
October 21, 2003 - 4:57 pm UTC

I'll have to ask you to contact support -- everything "looks ok", i see no reason short of you queried v$parameter in the wrong database or something.

sorry -- don't see "whats wrong" from here.

Dumb Question : what is hsecs

A reader, October 21, 2003 - 2:02 pm UTC

Please forgive me for asking this dumb question.
What is hsecs in your example of timing
dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs'
);

Is it milli seconds.

Sometimes you divide it by hundred.
dbms_output.put_line( (dbms_utility.get_time-l_start)/100
);
What will that be . Just seconds ?



Tom Kyte
October 21, 2003 - 5:23 pm UTC

hsecs = hundredths of seconds.

x/100 would be seconds.

Table NOPARALLEL

M S Reddy, October 29, 2003 - 11:29 am UTC

Tom,
One quick question .Will the parallel hint work if the table is created as NOPARALLEL.if yes then for what is CTAS noparallel useful.if we have the parallel processing enabled on the server will a table be created automatically as PARALLEL.

Thanks,
Reddy

Tom Kyte
October 29, 2003 - 11:52 am UTC

that just sets the default degree of parallelism, you can override that with hints, yes.



Parallel query server died unexpectedly.

alex, December 01, 2003 - 3:48 pm UTC

Tom

When I am running very large query for 3M records to be fetched having joins with 4 tables those table are also very big 100m of data in each and are partitoned but after some time I am getting following error message

Parallel query server died unexpectedly.

please tell me any soln.

thanks

Tom Kyte
December 02, 2003 - 8:11 am UTC

as you would with any internal error -- contact support and they'll help you diagnose it.

PDML

A reader, December 30, 2003 - 4:58 pm UTC

Tom,

Pls clarify the following :

1. Can I do a PDML on a table even its degree of parallelism at the table level is 1 by using ALTER SESSION ENABLE PARALLEL DML
2. If I enable a PDML eg INSERT, do I need to specify the degree in both INSERT and SELECT. If not, if I specify a degree of parallelism in SELECT alone say 4, my understanding is that 8 PX slaves will be run ( 4 for SELECT and 4 for INSERT - enabled automatically because PDML enabled). Is that correct?
3. Pls. clarify the following :
a. CREATE TABLE AS SELECT...
b. CREATE TABLE AS SELECT /*+PARALLEL(...)*/ ... Is this
called PDDL?
c. CREATE TABLE t DEGREE 4 AS SELECT ....
d. CREATE TABLE t DEGREE 4 AS SELECT /*+ PARALLEL(...)*/
e. INSERT INTO t SELECT ....
f. INSERT INTO t SELECT /*+ PARALLEL(...)*/
g. ALTER SESSION enable parallel DML
INSERT INTO t SELECT /*+ PARALLEL(...) */

In the above cases, which of the SELECTs/INSERTs puts data above the HWM and the restrictions on them.

Kailash

PDDL???

A reader, December 30, 2003 - 5:59 pm UTC

Tom,

I am getting confused with the terms. Pls clarify :

I have a DDL like the one below. You mention that it is called PDDL.

create table tt ( owner, cnt ) parallel 5
as
select owner, count(*) from t group by owner;

Can you tell me what this construct is called then :

create table tt ( owner, cnt )
as
select /*+PARALLEL(t,5) */ owner, count(*) from t group by owner;

Also pls clarify the following terms :

1. CTAS. Is it CREATE TABLE...AS SELECT ?
2. CTAS in parallel. Is it CREATE TABLE .. SELECT /*+PARALLEL(t,5) */...?
3. INSERT INTO .. SELECT
4. INSERT INTO .. SELECT in parallel
5. Direct load insert and Parallel Direct Load insert
6. pCTAS (Parallel CREATE TABLE AS)

Thanks

Tom Kyte
December 30, 2003 - 6:23 pm UTC

things that start with CREATE are DDL.

That first one created a table serially, and that table has a default degree of parallelism of 5.

the second could create the table in parallel and the table will have a default degree of parallelism (no value set)

CTAS is create table as select

You need to read that chapter! really, its all in there (as well as in the concepts guide!)



pCTAS

A reader, December 30, 2003 - 8:24 pm UTC

Tom,

Say you have a table T with DEGREE of parallelism set to 4. You enforce a Parallel DML (e.g INSERT) in your session
ALTER SESSION ENABLE PARALLEL DML
INSERT INTO T SELECT ... FROM T1
1. In the above case
a. Will only INSERT be done in parallel and SELECT in SERIAL since SELECT does not have a degree of parallelism or
b. Both INSERT and SELECT be done in PARALLEL using the degree of table T or
c. INSERT will be done in PARALLEL using degree of T and SELECT will also be done in PARALLEL using degree of T1. Pls. clarify

2. When you say pCTAS do you mean
CREATE TABLE a DEGREE 5
AS SELECT ... FROM B
or
CREATE TABLE a
AS SELECT /*+ PARALLEL(b,4) */ * FROM B ....
My understanding is that in the first case the table A will be created with a degree of 5, but the SELECT will be done in serial and in the second case A will be created with a default degree of parallelism and the SELECT will be done in parallel.

3. In Tim Gorman's book on Essential Oracle8i DW, he discusses the following :
a. INSERT INTO .. SELECT
b. INSERT INTO .. SELECT in parallel in Oracle7
c. CREATE TABLE .. AS SELECT
d. CREATE TABLE .. AS SELECT using parallel query
e. Parallel CREATE TABLE .. AS SELECT
What is the difference between d and e

I read the documentation but I am confused. It would be really helpful if you could clear my confusion


Tom Kyte
December 31, 2003 - 9:17 am UTC

from the link provided:

http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/tuningpe.htm#66531

<quote>
Rules for Parallelizing INSERT ... SELECT

An INSERT ... SELECT statement parallelizes its INSERT and SELECT operations independently, except for the DOP.

You can specify a parallel hint after the INSERT keyword in an INSERT ... SELECT statement. Because the tables being queried are usually not the same as the table being inserted into, the hint enables you to specify parallel directives specifically for the insert operation.

You have the following ways to specify parallel directives for an INSERT ... SELECT statement (assuming that PARALLEL DML mode is enabled):

    * SELECT parallel hints specified at the statement
    * Parallel clauses specified in the definition of tables being selected
    * INSERT parallel hint specified at the statement
    * Parallel clause specified in the definition of tables being inserted into

You can use the ALTER SESSION FORCE PARALLEL DML statement to override parallel clauses for subsequent INSERT operations in a session. Parallel hints in insert operations override the ALTER SESSION FORCE PARALLEL DML statement.

Decision to Parallelize

The following rule determines whether the INSERT operation should be parallelized in an INSERT ... SELECT statement:

The INSERT operation will be parallelized if and only if at least one of the following is true:

    * The PARALLEL hint is specified after the INSERT in the DML statement.
    * The table being inserted into (the reference object) has a PARALLEL declaration specification.
    * An ALTER SESSION FORCE PARALLEL DML statement has been issued previously during the session.

The decision to parallelize the INSERT operation is made independently of the SELECT operation, and vice versa.

Degree of Parallelism

Once the decision to parallelize the SELECT or INSERT operation is made, one parallel directive is picked for deciding the DOP of the whole statement, using the following precedence rule Insert hint directive > Session> Parallel declaration specification of the inserting table > Maximum query directive.
</quote>


the answer is (a).  there will be 4 slaves doing the insert as select -- each fed from the same pq slave.  eg: if you:

ops$tkyte@ORA9IR2> alter table t parallel 4;
 
Table altered.
 
ops$tkyte@ORA9IR2> insert into t select * from big_table.big_table;

and looked at the running sql, you'd see 4 sessions running sql like this:

OPS$TKYTE(16,6611) ospid = 1020 command = 2 program = oracle@tkyte-pc-isdn.us.oracle.com (P003) dedicated server=1020
Wednesday 09:07  Wednesday 09:07 last et = 5
INSERT /*+ APPEND */ INTO "OPS$TKYTE"."T"("ID","OWNER","OBJECT_N
AME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE"
,"CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GEN
ERATED","SECONDARY") SELECT C0,C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C1
1,C12,C13 FROM :Q12000



If instead you did something like:

 
ops$tkyte@ORA9IR2> insert into t select /*+ parallel(t2 2) */ * from big_table.big_table t2;
 

(note I have max pq servers at 5 in my setup....)  you would see sql like this:

OPS$TKYTE(10,12549) ospid = 1016 command = 2 program = oracle@tkyte-pc-isdn.us.oracle.com (P001) dedicated server=1016
Wednesday 09:11  Wednesday 09:11 last et = 9
INSERT /*+ APPEND */ INTO "OPS$TKYTE"."T"("ID","OWNER","OBJECT_N
AME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE"
,"CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GEN
ERATED","SECONDARY") SELECT C0,C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C1
1,C12,C13 FROM :Q17000
--------------------
OPS$TKYTE(15,47387) ospid = 1014 command = 2 program = oracle@tkyte-pc-isdn.us.oracle.com (P000) dedicated server=1014
Wednesday 09:11  Wednesday 09:11 last et = 9
INSERT /*+ APPEND */ INTO "OPS$TKYTE"."T"("ID","OWNER","OBJECT_N
AME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT_TYPE"
,"CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GEN
ERATED","SECONDARY") SELECT C0,C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C1
1,C12,C13 FROM :Q17000
--------------------
OPS$TKYTE(16,6619) ospid = 1078 command = 3 program = oracle@tkyte-pc-isdn.us.oracle.com (P004) dedicated server=1078
Wednesday 09:11  Wednesday 09:11 last et = 9
SELECT /*+ Q17000 NO_EXPAND ROWID(A1) */ A1."ID" C0,A1."OWNER" C
1,A1."OBJECT_NAME" C2,A1."SUBOBJECT_NAME" C3,A1."OBJECT_ID" C4,A
1."DATA_OBJECT_ID" C5,A1."OBJECT_TYPE" C6,A1."CREATED" C7,A1."LA
ST_DDL_TIME" C8,A1."TIMESTAMP" C9,A1."STATUS" C10,A1."TEMPORARY"
C11,A1."GENERATED" C12,A1."SECONDARY" C13 FROM "BIG_TABLE"."BIG
_TABLE" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)  A1
--------------------
OPS$TKYTE(19,8) ospid = 1018 command = 3 program = oracle@tkyte-pc-isdn.us.oracle.com (P002) dedicated server=1018
Wednesday 09:11  Wednesday 09:11 last et = 9
SELECT /*+ Q17000 NO_EXPAND ROWID(A1) */ A1."ID" C0,A1."OWNER" C
1,A1."OBJECT_NAME" C2,A1."SUBOBJECT_NAME" C3,A1."OBJECT_ID" C4,A
1."DATA_OBJECT_ID" C5,A1."OBJECT_TYPE" C6,A1."CREATED" C7,A1."LA
ST_DDL_TIME" C8,A1."TIMESTAMP" C9,A1."STATUS" C10,A1."TEMPORARY"
C11,A1."GENERATED" C12,A1."SECONDARY" C13 FROM "BIG_TABLE"."BIG
_TABLE" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)  A1


It backed the DOP off to two for the insert, two for the select (since I can only have 5) and did the query in parallel - each PQ slave feeding a different insert process.

<b>this sort of stuff is why I heavily promote that every single DBA/Developer on the planet should have their own copy installed on their own machine.  In this controlled environment -- it is very easy and fast to see what ACTUALLY takes place!</b>

2) you are correct on the first point.

Lets see on the second point:
ops$tkyte@ORA9IR2> create table a
  2  as
  3  select /*+ parallel(b,4) */ * from big_table.big_table b;
 
Table created.
 
ops$tkyte@ORA9IR2> select table_name, degree from user_tables where table_name = 'A';
 
TABLE_NAME                     DEGREE
------------------------------ ----------
A                                       1


<b>A hint is not really part of the DDL, it will not affect the DOP setting for the table itself, it applies to the query and the query alone</b>


3) I've not read his book, i don't know what he might have been signifying there -- it could have been your #2 example.

The first creates a "parallel" table as select (could be a parallel create table as select), the second creates a table using parallel query.

 

DOP

A reader, December 31, 2003 - 10:48 am UTC

Tom,

Thanks much for your reply. I have the following questions :
1. If a degree of parallelism is not specified, does that mean it will use degree of 1. If so, then what is meant by default degree of parallelism? How is it determined?
2. In the Oracle link you had sent you about PX execution and in Tim Gorman's book, he mentions that we can use the NONAPPEND insert in PARALLEL DML and here is the quote from chapter 21 of the documentation :

<quote>
The append mode is the default during a parallel insert: data is always inserted into a new block which is allocated to the table. Therefore the APPEND hint is optional. You should use append mode to increase the speed of INSERT operations, but not when space utilization needs to be optimized. You can use NOAPPEND to override append mode.
</Quote>

I am not able to see any specific documentation on NOAPPEND.
My understanding so far has been that NOAPPEND insert performs a conventional insert and a parallel DML is not applicable for conventional insert. Pls. correct me if my understanding is wrong and clarify
1. What NOAPPEND means and how it works in Parallel DML for both partitioned and nonpartitioned tables and how HWM and table locks,constraints,triggers etc are handled in that case
2. Difference between NOAPPEND and APPEND hint in Parallel DML
3. Under what situations would you use NOAPPEND hint

I would appreciate if you could throw some light on this

Tom Kyte
December 31, 2003 - 11:05 am UTC

1) not specified when? where? but anyway, if there is no DOP, then 1 is the "dop" (eg: serial, single)

2) NOAPPEND is documented in the performance and tuning guide. NOAPPEND uses convention sql to insert, it'll use existing space on the freelists, it'll not write directly to disk. It'll be just like an insert in any application is normally. it cannot bypass undo and redo.

they tell you in that paragraph when you might use noappend. "space utilization"

DOP

A reader, December 31, 2003 - 11:03 am UTC

Tom,

Thanks much for your reply. I have the following questions :
1. If a degree of parallelism is not specified, does that mean it will use degree of 1. If so, then what is meant by default degree of parallelism? How is it determined?
2. In the Oracle link you had sent you about PX execution and in Tim Gorman's book, he mentions that we can use the NONAPPEND insert in PARALLEL DML and here is the quote from chapter 21 of the documentation :

<quote>
The append mode is the default during a parallel insert: data is always inserted into a new block which is allocated to the table. Therefore the APPEND hint is optional. You should use append mode to increase the speed of INSERT operations, but not when space utilization needs to be optimized. You can use NOAPPEND to override append mode.
</Quote>

I am not able to see any specific documentation on NOAPPEND.
My understanding so far has been that NOAPPEND insert performs a conventional insert and a parallel DML is not applicable for conventional insert. Pls. correct me if my understanding is wrong and clarify
1. What NOAPPEND means and how it works in Parallel DML for both partitioned and nonpartitioned tables and how HWM and table locks,constraints,triggers etc are handled in that case
2. Difference between NOAPPEND and APPEND hint in Parallel DML
3. Under what situations would you use NOAPPEND hint

I would appreciate if you could throw some light on this

Reader

A reader, December 31, 2003 - 2:41 pm UTC

How large is your big_table.big_table


Tom Kyte
December 31, 2003 - 2:59 pm UTC

didn't really matter here -- the DOP was set by me.

could be 1 row
could be 1,000,000 rows (it was)
could be 100,000,000,000 rows.....

I use this script (from my book Effective Oracle by Design) to set up this "big_table" as big as I want it to be, you just pass it the number of rows you want:


create table big_table
as
select rownum id, a.*
from all_objects a
where 1=0
/
alter table big_table nologging;

declare
l_cnt number;
l_rows number := &1;
begin
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a
where rownum <= &1;

l_cnt := sql%rowcount;

commit;

while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into big_table
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from big_table
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/

alter table big_table add constraint
big_table_pk primary key(id)
/

begin
dbms_stats.gather_table_stats
( ownname => user,
tabname => 'BIG_TABLE',
method_opt => 'for all indexed columns',
cascade => TRUE );
end;
/
select count(*) from big_table;


NOAPPEND

A reader, December 31, 2003 - 4:05 pm UTC

Tom,

You mentioned that NOAPPEND uses conventional SQL Insert. My understanding is that conventional SQL does not support Parallel DML. If so how is Parallel DML with NOAPPEND. Pls clarify

Thanks

Tom Kyte
December 31, 2003 - 4:59 pm UTC

it isn't. it can use parallel query to get the data, but the insert will be a single insert path using conventional path.

NOAPPEND

A reader, December 31, 2003 - 7:34 pm UTC

Tom,

Wishing you a wonderful and happy New Year 2004. You mentioned that NOAPPEND will use a conventional path INSERT. What would happen if
1. Use ALTER SESSION ENABLE PARALLEL DML. Then use PARALLEL and NOAPPEND hints in the query . Will this give an error or will it ignore the NOAPPEND error.
2. So it will be the same as a normal INSERT except that SELECT will be performed in parallel. Pls confirm.

Thanks much


Tom Kyte
December 31, 2003 - 7:38 pm UTC

it'll do noappend. (you can try it!)

the query can be done in parallel.
the insert will not

Contention

A reader, January 04, 2004 - 6:34 pm UTC

Tom,

1. If u specify ALTER SESSION PARALLEL DML and also use NOAPPEND hint, you mention that it will use NOAPPEND hint and does not perform parallel DML and only the SELECT will be performed in parallel if PARALLEL hint specified. Is my understanding correct?
2. If a PARALLEL select is performed and the INSERT is done in serial, what about FREELIST contention. Will FREELIST need to be increased to reduce contention. Pls explain

Tom Kyte
January 04, 2004 - 7:26 pm UTC

1) yes
2) you have only one inserting session. no contention with yourself.

DOP

A reader, February 12, 2004 - 12:32 am UTC

hi Tom,

when i use
ALTER SESSION FORCE PARALLEL DML parallel 12

select ...
from a,b,c -- a,b,c are partiioned table
where ...

then 'alter session statment..' means the following (1) or (2)? (when I open top session tool from oracle, i ust see 12 sessions)


1)
select /*+ parallel(a,12) parallel(b,12) parallel(c,12) */
from a,b,c
from ...
where ...

2)
select /*+ parallel(a,i) parallel(b,j) parallel(c,k) */
from a,b,c
from ...
where ...

where i+j+k = 12




Thanks!
steve

Tom Kyte
February 12, 2004 - 8:37 am UTC

check out the link(s) above - we've documented how the DOP is computed. In your case, 12 is the number of parallel query processes you will try to use and neither of 1) nor 2) would be true necessarily.

parallel ddl

sam, March 18, 2004 - 2:30 pm UTC

Tom,

I just want to be sure..

In the query below big_emp will be set with degree 4
& also create table in parallel ??

create table big_emp parallel (degree 4)
as select * from emp;

What is a way to check this out ??
I am checking this by oracle process. Before & during execution. IS there any alternate way of checking ?





Tom Kyte
March 18, 2004 - 3:20 pm UTC

explain plan.... (if you don't have 9ir2, use your own query against the plan table as dbms_xplan wont exist yet)

ops$tkyte@ORA10G> explain plan for create table big_emp parallel (degree 4)
  2  as
  3  select * from scott.emp;
 
Explained.
 
ops$tkyte@ORA10G> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------- --------------------------
Plan hash value: 2508419448
 
--------------------------------------------------------------------------------------------------------- -------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Di strib |
--------------------------------------------------------------------------------------------------------- -------
|   0 | CREATE TABLE STATEMENT |          |    14 |   518 |     1   (0)| 00:00:01 |        |      |       |
|   1 |  PX COORDINATOR        |          |       |       |            |          |        |      |       |
|   2 |   PX SEND QC (RANDOM)  | :TQ10000 |    14 |   518 |     1   (0)| 00:00:01 |  Q1,00 | P->S | QC (R AND)  |
|   3 |    LOAD AS SELECT      |          |       |       |            |          |  Q1,00 | PCWP |       |
|   4 |     PX BLOCK ITERATOR  |          |    14 |   518 |     1   (0)| 00:00:01 |  Q1,00 | PCWC |       |
|   5 |      TABLE ACCESS FULL | EMP      |    14 |   518 |     1   (0)| 00:00:01 |  Q1,00 | PCWP |       |
--------------------------------------------------------------------------------------------------------- -------
 
12 rows selected.
 
ops$tkyte@ORA10G>
 

Why no PQ on FFS

Rob, March 19, 2004 - 2:08 pm UTC

Tom:

If I understand it correctly a Fast Full Scan (FFS) of an index reads all the blocks of the index much like a FTS reads all of the blocks of a table. In your first response to the original poster you imply that a FFS will not use Parallel Query. Why is that? Also will a FTS of an IOT use PQ.

Thanks as always for your help.

Tom Kyte
March 19, 2004 - 2:26 pm UTC

they asked using the way back machine -- version 8.0.5

IOT's do support PQ nowadays.

ops$tkyte@ORA9IR2> drop table t;
 
Table dropped.
 
ops$tkyte@ORA9IR2> create table t ( x int primary key, data char(80) ) organization index;
 
Table created.
 
ops$tkyte@ORA9IR2> alter table t parallel;
 
Table altered.
 
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000, numblks => 100000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select count(*) from t;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     SORT* (AGGREGATE)                                                                                :Q6000
   3    2       INDEX* (FAST FULL SCAN) OF 'SYS_IOT_TOP_35849' (UNIQUE) (Cost=2 Card=1000000)                  :Q6000
 
 
   2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
                                   ROM (SELECT /*+ INDEX_RRS(A2 "SYS_IOT_TOP_35
                                   849") */ 0 FROM "T"  PX_GRANULE(0, BLOCK_RAN
                                   GE, DYNAMIC) A2) A1
 
   3 PARALLEL_COMBINED_WITH_PARENT
 
ops$tkyte@ORA9IR2>
 

parallel dml and partition

Riyaz, April 10, 2004 - 3:23 am UTC

My question is:

In oralce 9i database release 2 product family, It has been given as following:

As a remark for "Parallel DML", it has been given as "No longer requires partition option".

What does it mean?. Is this means, Partition is not required if parallel DML is there. Please explain. By going through the parllel DML related quries in this desk, I feel that, it is good option, but cannot be used as a replacement for "partition".


Tom Kyte
April 10, 2004 - 11:44 am UTC

it used to be that in order to do PDML (parallel update/delete) you needed to have partitioned the table (as the update/delete would be assigned to each partition basically).

In 9ir2, that implementation restriction was lifted, the table no longer needs be partitioned physically -- we'll dynamically partition it at runtime if need be.

so, in the past in order to do PDML, table had to be partitioned.
today in 9ir2, table need not be partitioned to perform PDML

partitioning was never a "replacement" for PDML (or vice versa), it was that PDML used to rely on partitioning to be in place to operate.

Performance in Paralle Query

Sreejayan, May 31, 2004 - 3:16 am UTC

Excellent work.. Tom, Learnt lot of info. on this... Please keep up the good work to the millions of Oracle users worldwide!!!

Parallelism in DML

Sergio Vilela, June 07, 2004 - 3:25 pm UTC

I am looking for to execute a parallel DML for UPDATE/DELETE in non-partitioned tables. Reading your archived questions I see that is not possible in Oracle 8i.
May you confirm this?

Thanks

Tom Kyte
June 07, 2004 - 3:44 pm UTC

yes I can.

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96531/ch2_9ir2.htm#74432 <code>


you can achieve "DIY" parallel update in many cases however....


do you have access to my book "Effective Oracle By Design" -- i describe how to implement DIY parallelism by running a query to get rowid ranges that "cover" a table without overlapping. then you can first up N sqlplus sessions and issue:


update t set ..... where rowid between 'rowid1' and 'rowid2';




Parallel query in cursor.

A reader, July 06, 2004 - 6:16 pm UTC

Hi Tom,

Would it be useful if I declared a cursor as such:

declare
cursor c
is
select /*+ PARALLEL (t, 4) */ *
from t;

and then in my pl/sql code did the following:

begin
for i in c
loop
...do processing here...
end loop;
end;

Would the cursor take advantage of parallel query processing while retrieving its results? Is this is a good application of parallel query processing? I'm trying to use parallel query processing for such a scenario, and unfortunately I can not do it in one go since I need to capture statistics on how many records were read, processed, rejected etc.

Thanks.


Tom Kyte
July 06, 2004 - 7:57 pm UTC

Not really - you are processing row by row. doesn't matter HOW FAST that server feeds you at that point, does it?


You would need to employ "do it yourself" parallelism.


search for

chunks dbms_rowid

on this site for an example.

Parallel Query

John, July 07, 2004 - 10:37 am UTC

Tom,
Pls have a look on the following query.If I don't use parallel option,query runs 10 secs,but with parallel it takes 24 secs.

=============================================
SQL> set autotrace on
SQL> SELECT /*+ parallel(a,4) */ REG_LOCATION_TYPE,
  2    count(r.REG_NBR)   
  3  FROM REGISTRATION_ADDRESS ra,
  4  ADDRESS a,
  5    REGISTRATION r 
  6  WHERE ( ra.REG_NBR=r.REG_NBR) 
  7    AND  ( a.ADDRESS_ID=ra.ADDRESS_ID) 
  8    AND  ( a.ADDRESS_TYPE_ID='F'
  9    AND r.CREATED_BY  NOT LIKE 'FDA%') 
 10    AND  ( r.REG_STATUS_ID  !=  3 
 11    AND  ra.STATUS  =  'V' )
 12   GROUP BY r.REG_LOCATION_TYPE ;

R COUNT(R.REG_NBR)
- ----------------
I           107068
D            97705

Elapsed: 00:00:23.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1729 Card=3 Bytes=13
          8)

   1    0   SORT* (GROUP BY) (Cost=1729 Card=3 Bytes=138)              :Q4005
   2    1     SORT* (GROUP BY) (Cost=1729 Card=3 Bytes=138)            :Q4004
   3    2       HASH JOIN* (Cost=1647 Card=124873 Bytes=5744158)       :Q4004
   4    3         TABLE ACCESS* (FULL) OF 'REGISTRATION' (Cost=383 Car :Q4001
          d=143584 Bytes=3158848)

   5    3         HASH JOIN* (Cost=1219 Card=186476 Bytes=4475424)     :Q4003
   6    5           TABLE ACCESS* (FULL) OF 'ADDRESS' (Cost=703 Card=1 :Q4002
          86477 Bytes=1491816)

   7    5           TABLE ACCESS* (FULL) OF 'REGISTRATION_ADDRESS' (Co :Q4000
          st=516 Card=745842 Bytes=11933472)



   1 PARALLEL_TO_SERIAL            SELECT /*+ CIV_GB */ A1.C0,COUNT(SYS_OP_CSR(
                                   A1.C1,0)) FROM :Q4004 A1 GROUP BY A1

   2 PARALLEL_TO_PARALLEL          SELECT /*+ PIV_GB */ A1.C0 C0,SYS_OP_MSR(COU
                                   NT(*)) C1 FROM (SELECT /*+ ORDERED N

   3 PARALLEL_COMBINED_WITH_PARENT
   4 PARALLEL_FROM_SERIAL
   5 PARALLEL_TO_PARALLEL          SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */
                                    A2.C1 C0 FROM :Q4002 A1,:Q4000 A2 W

   6 PARALLEL_TO_PARALLEL          SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ADDRES
                                   S_ID" C0 FROM "ADDRESS" PX_GRANULE(0

   7 PARALLEL_FROM_SERIAL


Statistics
----------------------------------------------------------
        109  recursive calls
         12  db block gets
      38663  consistent gets
      47248  physical reads
        784  redo size
        283  bytes sent via SQL*Net to client
        276  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          4  sorts (disk)
          2  rows processed
============================================ 

Tom Kyte
July 07, 2004 - 11:59 am UTC

don't run it in parallel then?


parallel query is for big stuff.

parallel query has necessary startup and overhead.

parallel query should be used to take something that takes MINUTES or HOURS to execute and run it faster.


if you have a query that runs in 10 seconds, it is very doubtful that the extra work PQ must go through (develop the parallel plan, perhaps start the processes, coordinate the processes, aggregate the results, etc) is worth it.

using parallel option

ramki, July 10, 2004 - 11:53 am UTC

Can i use parallel servers on a server with only a single cpu???? or is it mandatory for me to have more than one cpu ???

Tom Kyte
July 10, 2004 - 8:54 pm UTC

it can make sense to do parallel 2 for some admin operations on a single CPU (2xcpu's is a magic number)

while one "create me an index" process is reading your disk, the other could be using the cpu and sorting.

A reader, July 22, 2004 - 1:55 pm UTC

Tom:
I have a situation like this..i have a big (select)parallel query that gets the results in 19 mins..when i did an
insert /*+ append */ in to table (parallel query)..its going for ever..why should the insert take that long
thanks

Tom Kyte
July 23, 2004 - 8:09 am UTC

what does explain plan show? are they the same?

and does it get the "first rows" in 19 minutes or "all of the rows" in 19 minutes.


and how many rows are you talking about.

Regarding Parallelism

Muhammad Riaz Shahid, August 23, 2004 - 12:53 pm UTC

Regarding Parallelism

Consider the case:

SQL> set autotrace on
SQL> ed
Wrote file afiedt.buf

  1  select count(*) from custom_data.be_mst 
  2* where st_Reg_no in (select REG_NO from stax.AMIR_IMP)
SQL> 
SQL> /

  COUNT(*)    
==========    
        38    

Elapsed: 00:00:01.97

Execution Plan
==========================================================                        
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=35 Card=1 Bytes=22)             
   1    0   SORT (AGGREGATE)   
   2    1     HASH JOIN (Cost=35 Card=3732 Bytes=82104)                           
   3    2       VIEW OF 'VW_NSO_1' (Cost=6 Card=995 Bytes=7960)                   
   4    3         SORT (UNIQUE) (Cost=6 Card=995 Bytes=12935)                     
   5    4           TABLE ACCESS (FULL) OF 'AMIR_IMP' (Cost=1 Card=995            
           Bytes=12935)        
              
   6    2       TABLE ACCESS (FULL) OF 'BE_MST' (Cost=28 Card=27232 By            
          tes=381248)          
              




Statistics
==========================================================                        
          0  recursive calls   
          9  db block gets     
        333  consistent gets   
        330  physical reads    
          0  redo size         
        367  bytes sent via SQL*Net to client                                     
        425  bytes received via SQL*Net from client                               
          2  SQL*Net roundtrips to/from client                                    
          4  sorts (memory)    
          0  sorts (disk)      
          1  rows processed    

SQL> ed
Wrote file afiedt.buf

  1  select /*+parallel(t 10)*/ count(*) from custom_data.be_mst t
  2* where st_Reg_no in (select REG_NO from stax.AMIR_IMP)
SQL> /

  COUNT(*)    
==========    
        38    

Elapsed: 00:00:03.53

Execution Plan
==========================================================                        
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=35 Card=1 Bytes=22)             
   1    0   SORT (AGGREGATE)   
   2    1     SORT* (AGGREGATE)                                        :Q138920   
   02         
              
   3    2       HASH JOIN* (Cost=35 Card=3732 Bytes=82104)             :Q138920   
   02         
              
   4    3         VIEW* OF 'VW_NSO_1' (Cost=6 Card=995 Bytes=7960)     :Q138920   
   00         
              
   5    4           SORT (UNIQUE) (Cost=6 Card=995 Bytes=12935)                   
   6    5             TABLE ACCESS (FULL) OF 'AMIR_IMP' (Cost=1 Card=9            
          95 Bytes=12935)      
              
   7    3         TABLE ACCESS* (FULL) OF 'BE_MST' (Cost=28 Card=27232 :Q138920   
           Bytes=381248)                                               01         
              


   2 PARALLEL_TO_SERIAL            SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F   
                                   ROM (SELECT /*+ ORDERED NO_EXPAND US           
              
   3 PARALLEL_COMBINED_WITH_PARENT                                                
   4 PARALLEL_FROM_SERIAL      
   7 PARALLEL_TO_PARALLEL          SELECT /*+ Q13892001 NO_EXPAND ROWID(A1) */    
                                   A1."ST_REG_NO" C0 FROM "CUSTOM_DATA"           
              


Statistics
==========================================================                        
         19  recursive calls   
        217  db block gets     
        342  consistent gets   
        330  physical reads    
        684  redo size         
        367  bytes sent via SQL*Net to client                                     
        425  bytes received via SQL*Net from client                               
          2  SQL*Net roundtrips to/from client                                    
         15  sorts (memory)    
          0  sorts (disk)      
          1  rows processed    


SQL> show parameter parallel

NAME                                 TYPE                             VALUE
==================================== ================================ ==============================
fast_start_parallel_rollback         string                           LOW
optimizer_percent_parallel           integer                          0
parallel_adaptive_multi_user         boolean                          FALSE
parallel_automatic_tuning            boolean                          FALSE
parallel_broadcast_enabled           boolean                          FALSE
parallel_execution_message_size      integer                          2148
parallel_instance_group              string
parallel_max_servers                 integer                          15
parallel_min_percent                 integer                          0
parallel_min_servers                 integer                          0
parallel_server                      boolean                          FALSE
parallel_server_instances            integer                          1
parallel_threads_per_cpu             integer                          2
recovery_parallelism                 integer                          0

SQL> spo off


We have 4 CPU machine (2.4 GHz each). My questions are:

(1) why there are more LIOS (and more elapsed time) when we select table in parallel as compare to a case not using parallel hint.
(2) Why the "Redo Size" is shown in autotrace output when we go for parallel ? 

Tom Kyte
August 23, 2004 - 1:10 pm UTC

1) because parallel isn't "fast=true" but here we are talking nominal numbers, for all intents and purposes -- the same as far as I'm concerend.

2) dictionary updates for temp, delayed block cleanout, recursive SQL operations, could be anything -- again, so minimal as to be in the "who cares" sort of realm.


PQ is something you apply to queries that take minutes or hours or days, PQ is entirely inappropriate for small problems. PQ has significant startup and runtime implications.


I have an analogy in my book "Effective Oracle by Design" -- suppose you have to write a report for your manager.

You have 12 people that work for you.

It is a one page report.

You could either:

a) do it yourself

or

b) 1) break it into 12 paragraphs
2) get your employees together for a meeting
3) assign out the 12 pieces of work
4) wait for them to complete in some order
5) assemble them back together into a cohesive answer
6) deliver the report


b) won't be effective for a single page report.


On the other hand, if this was a 12 chapter report -- each chapter being 100 pages, well, then b) would be better than a) in all probability.

Version

Muhammad Riaz Shahid, August 23, 2004 - 12:55 pm UTC

Sorry ! I forgot to mention version in my last post. Its 8.1.7 on Widnwoz 2000 Advanced Server.

PQ

A reader, August 23, 2004 - 1:14 pm UTC

"PQ is something you apply to queries that take minutes or hours or days, PQ is entirely inappropriate for small problems. PQ has significant startup and runtime implications"

I have a 8-CPU Unix server. If PQ is not used, wont Oracle use only 1 of the CPUs and the other 7 are sitting around twiddling their thumbs? Seems like a awful waste?

Thanks

Tom Kyte
August 23, 2004 - 1:21 pm UTC

if you have small problems -- starting up and coordinating 8 things would be a huge awful waste.

Oracle will use all 8 cpus for everything. Oracle is a multi-process engine -- smon, pmon, dedicated servers, lgwr, and more are all going at the same time....


You don't need PQ to use all 8, if you have 100 people asking lots of small questions, that machine will be utilized.

If you just have one person asking small questions like this one, you bought way too much machine.

Using multiple CPUs

A reader, August 23, 2004 - 2:34 pm UTC

"Oracle will use all 8 cpus for everything. Oracle is a multi-process engine -- smon, pmon, dedicated servers, lgwr, and more are all going at the same time"

"You don't need PQ to use all 8, if you have 100 people asking lots of small questions, that machine will be utilized"

Can you show me how to see this?

On Solaris 8, 'man ps' tells me there is a -P switch

-P Prints the number of the processor to which the process or lwp is bound, if any, under an additional column header, PSR.

I do 'ps -efP' and the PSR column comes up with all '-'

I am having difficulty seeing exactly how various Oracle processes utilize multiple CPUs in a SMP system

Thanks


Tom Kyte
August 23, 2004 - 3:11 pm UTC

get 100 people hitting your database and run "top", you'll see it.


Oracle is multi-process on unix.

Unix runs multiple processes naturally on many cpus, it is what it is designed to do.

We do not use binding or afinity, we just let the OS put us on a cpu and move us around when needed.


So, load your machine with lots of users and watch, you'll see the cpu's go up in utilization.

don't over analyze this -- this is what the OS does, it does it naturally.

Hi,

A reader, August 25, 2004 - 10:52 am UTC

I have two schemas in a database. A query on schema 1 runs in 45 seconds and consumes quite a lot of processes. ( I have set to degree 8 at table level).
On schema 2, same query is running for ever and it is running using single process. I am not sure why the same query is not making use of parallel processes.
The degree is also 8 on schema 2 for all the tables.

Any clue?

Tom Kyte
August 25, 2004 - 10:59 am UTC

compare the plans. use explain plan and dbms_xplan (i'm assuming current software since you neglected to mention any)

i would suspect "stats are not correct" first and foremost.

what investigation have you done so far?

Hi,

A reader, August 25, 2004 - 11:09 am UTC

Thanks for your update.

The plan in two schemas for the same query are different.
Although I have used DBMS_STATS (estimate percent 5) for both the schemas, I am not sure why the plan is different.

The other difference is the quantity of data in schema 2 (slow schema) is about 3% more than schema 1. I think this should not cause any difference in plan or performance.

Now I know the plans are different, How can I make sure the plans in both the schamas are same? I cannot use any hints in the query since the report runs in Business Objects.

Does a incorrect plan cause the report not to use parallel processes?

Thanks,

Tom Kyte
August 25, 2004 - 11:32 am UTC

what is *different* about the plans -- are the cardinalities in the plans even close to reality.

an "incorrect plan" does not cause it to not use PQ, there is no cause/effect there. the decision to use or NOT use PQ is due entirely to the choice of plan.

A reader, August 25, 2004 - 1:37 pm UTC

The max_parallel_servers in the init.ora is set to 5 and if i issue a SELECT /*+ PARALLEL(T,8) */ * FROM T ,Will 8 sessions be opened or just 5?


Tom Kyte
August 25, 2004 - 1:43 pm UTC

if max is set to 5, the max will be 5.

Hi

A reader, August 26, 2004 - 10:30 am UTC

Continuing my question after I left off yesterday afternoon.

We made the explain plan in schema 1 and schema 2 exactly same and even then schema 2 runs serially for about 5 minutes and then picks the parallel processes. The total time to run the query is 15 minutes.
On schema 1, it runs serially for about 15 seconds and completes the same query within a minute.

People are waiting for our reply desperately.
I still don't have any clue on this matter.

Tom Kyte
August 26, 2004 - 10:41 am UTC

a query will not run for 5 minutes and then decide, oh well, don't really like this plan, lets go parallel.


something is wrong in your analysis of this "problem" (heck, i'm not even sure who you are or which of the above is from you or what the problem is exactly, but thats another story i guess)

HI

A reader, August 26, 2004 - 12:01 pm UTC

Alright. I have some more information about the two schemas (PRD1 and PRD2). PRD1 is fast and PRD2 is slow.

PRD1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.40 0.40 0 6 0 0
Execute 1 5.33 12.18 535 6584 0 0
Fetch 2 2.01 20.05 0 8 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 7.75 32.64 535 6598 0 1


PRD2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.38 0.39 0 0 0 0
Execute 1 27.83 348.72 73751 62794 31264 0
Fetch 2 5.86 34.75 0 44 32 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 34.08 383.86 73751 62838 31296 1


PRD1

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
29385 consistent gets
21348 physical reads
0 redo size
492 bytes sent via SQL*Net to client
4863 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2020 sorts (memory)
0 sorts (disk)
1 rows processed


PRD2
65 recursive calls
31305 db block gets
87954 consistent gets
96744 physical reads
2536 redo size
492 bytes sent via SQL*Net to client
4863 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2038 sorts (memory)
0 sorts (disk)
1 rows processed


Thanks,

Tom Kyte
August 26, 2004 - 2:00 pm UTC

sorry, but not very "useful"

so where did PQ come into play here at all? no query? sorry -- not going to go anywhere here. suggest you file a tar with support, they'll go back and forth with you to get the needed information.

HI,

A reader, August 26, 2004 - 6:20 pm UTC

Thanks. I filed tar with Oracle support.

Adaptive Parallel Query

sami, August 27, 2004 - 5:45 pm UTC

Dear Tom,

Version :10g,RedHat Linux AS 3.0
Hardware: Intel x86, Single CPU
2-Node RAC

I have a long running query, would like to get the advantages of "Adaptive Parallel Query" [running the query across the instances].

Could you kindly explain what are the
a)initialization parameters
b)segment properties
c)HINT in the query
has to be set to make it work.

Thanks in advance.


Performance in Parallel query - LOB

Pravin, September 08, 2004 - 1:00 pm UTC

We have a table with about 5million records in each partition (with a LOB column).

We need to delete about 4million records from this. We decided to create a new table with the required 1million records & put that back into the partition (exchange).

Just read that tables with LOBs do NOT support parallel creation (*&*&% DOOMED *&*&%) & takes about 1 hour... (SUN F15 Unix box, 16 CPU's , 16*4GB RAM, 4GB SGA).

Can you please suggest some alternate option?

cheers
Pravin

Tom Kyte
September 08, 2004 - 1:46 pm UTC

if the new table is to be partitioned, there shouldn't be any issues? each partition can be done in parallel.

At the very least, you could:

a) run a CTAS from t partition(pN) to create N tables (one each for each partition). do all of them "at the same time".

b) index them (for each local index)

c) exchange partitions with these tables

d) rebuild any global indexes you might have.



Performance in Parallel query - LOB

Pravin Ningoo, September 08, 2004 - 7:56 pm UTC

The new table is NOT partitioned.

table A is partitioned (say 5 partitions, each with 5million records + CLOB).

4 million records are to be deleted from partition 1.

so, we do this....
create table B as select * from table A where rownum < 1million;

this DDL cannot be parallelized as it has a LOB in it.


Tom Kyte
September 09, 2004 - 7:33 am UTC

make sure the lob is cached (by default "not"). It is most likely the wait for the direct path write here -- you can get dbwr to help you out by caching the lob.

Create Table with Parallel Option

Reader, October 09, 2004 - 7:55 am UTC

Hi Tom,

We are using Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production on Sun Solaris box. In our Appliaction there a Batch Process which uploads around 657909 rows every 3 hours. I have set up a External Table with the following structure :-
CREATE TABLE "SYS_SQLLDR_X_EXT_TEMP_CDR" 
(
  TMP_CDR_SRC_ID VARCHAR2(30),
  TMP_CDR_DEST_ID VARCHAR2(30),
  TMP_CDR_TOT_BYTES VARCHAR2(200),
  TMP_CDR_ATTRIB1 VARCHAR2(100),
  TMP_CDR_ATTRIB2 VARCHAR2(100)
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
    LOAD WHEN ((1: 1) != "S")
    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test.bad'
    LOGFILE 'test.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' NOTRIM 
    MISSING FIELD VALUES ARE NULL 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      TMP_CDR_SRC_ID CHAR(255)
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
      TMP_CDR_DEST_ID CHAR(255)
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
      TMP_CDR_TOT_BYTES CHAR(255)
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
      TMP_CDR_ATTRIB1 CHAR(255)
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
      TMP_CDR_ATTRIB2 CHAR(255)
        TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
       )
  )
  location 
  (
    'test.dat'
  )
)REJECT LIMIT UNLIMITED

test.dat contains the following data
********************************
80.228.73.86|210.18.132.234|5|235|1
66.55.143.211|210.18.135.174|74|100712|1
65.24.181.23|210.18.132.234|10|410|2
134.102.69.141|203.212.192.244|1|147|1
200.227.71.216|210.18.132.234|5|205|1
80.2.238.52|202.88.129.142|7|359|4
.
.
.
.
.
657909 records.
**********************************

The destination table for this data has the following structure:
CREATE TABLE TEMP_CDR ( 
  TMP_CDR_SRC_ID      VARCHAR2 (30), 
  TMP_CDR_DEST_ID     VARCHAR2 (30), 
  TMP_CDR_TOT_BYTES   VARCHAR2 (200), 
  TMP_CDR_ATTRIB1     VARCHAR2 (100), 
  TMP_CDR_ATTRIB2     VARCHAR2 (100), 
  TMP_CDR_START_TIME  DATE, 
  TMP_CDR_END_TIME    DATE) NOLOGGING;

The TMP_CDR_START_TIME and TMP_CDR_END_TIME contain the timestamp of the file(MP_CDR_START_TIME contains a time of 3 hours earlier than TMP_CDR_END_TIME). These 2 columns are populated via a function call during the data load.

Now if I load the table TEMP_CDR by using :-
SQL> create table temp_cdr nologging parallel as select * from SYS_SQLLDR_X_EXT_TEMP_CDR;

Table created.

Elapsed: 00:00:04.49

657909 rows are loaded within a whopping 4 secs.

But since I require 2 more columns I cannot use this script because I get the following Error:-
  1  create table temp_cdr(TMP_CDR_SRC_ID,TMP_CDR_DEST_ID,TMP_CDR_TOT_BYTES,TMP_CDR_ATTRIB1,TMP_CDR_ATTRIB2,TMP_CDR_START_TIME date, TMP_CDR_END_TIME date) nologging parallel as select TMP_CDR_SRC_ID,
  2       TMP_CDR_DEST_ID,
  3       TMP_CDR_TOT_BYTES,
  4       TMP_CDR_ATTRIB1,
  5       TMP_CDR_ATTRIB2,
  6       to_date(GetDateTime(getdates(1)),'dd/mm/yyyy hh24:mi:ss'),
  7       to_date(GetDateTime(getdates(2)),'dd/mm/yyyy hh24:mi:ss')
  8*    FROM "SYS_SQLLDR_X_EXT_TEMP_CDR"
SQL> /
create table temp_cdr(TMP_CDR_SRC_ID,TMP_CDR_DEST_ID,TMP_CDR_TOT_BYTES,TMP_CDR_ATTRIB1,TMP_CDR_ATTRIB2,TMP_CDR_START_TIME date, TMP_CDR_END_TIME date) nologging parallel as select TMP_CDR_SRC_ID,
                      *
ERROR at line 1:
ORA-01773: may not specify column datatypes in this CREATE TABLE

*************

so I first create the table temp_cdr with the 2 date columns and do a direct insert :-
SQL> INSERT /*+ parallel */ INTO "TEMP_CDR"
  2  (
  3    TMP_CDR_SRC_ID,
  4    TMP_CDR_DEST_ID,
  5    TMP_CDR_TOT_BYTES,
  6    TMP_CDR_ATTRIB1,
  7    TMP_CDR_ATTRIB2,
  8    TMP_CDR_START_TIME,
  9    TMP_CDR_END_TIME
 10  )
 11  SELECT
 12    TMP_CDR_SRC_ID,
 13    TMP_CDR_DEST_ID,
 14    TMP_CDR_TOT_BYTES,
 15    TMP_CDR_ATTRIB1,
 16    TMP_CDR_ATTRIB2,
 17    to_date(GetDateTime(getdates(1)),'dd/mm/yyyy hh24:mi:ss'),
 18    to_date(GetDateTime(getdates(2)),'dd/mm/yyyy hh24:mi:ss')
 19  FROM "SYS_SQLLDR_X_EXT_TEMP_CDR";

657909 rows created.

Elapsed: 00:08:14.60
****************

8 mins and 14 secs as compared to only 4 secs :-(
Does this mean that the Parallel Clause if of No USE?
Is there any other way out?.

Thanks and Regards,



 

Tom Kyte
October 09, 2004 - 5:20 pm UTC

why are you specifying the DATE type? it'll get it from the to_date in the select list.

just
create table t as
select .... to_date( .... ) name, to_date(.... ) name2 from ....;

it'll do the right thing.

(and GetDateTime(getdates( -- they are "parallel enabled" -- and most importantly -- they *cannot in any way shape or form* be done "in sql without calling plsql" right?)

Parallel query and inline view

Vadim Gotovsky, October 14, 2004 - 1:23 pm UTC

Hi Tom,

maybe you could help with understanding the following urgent issue with parallel query.

I am running the query:

SELECT /*+ parallel_index (D) push_pred (TB) push_pred (I) */
count(*)
FROM
ICE.ICE_EQ_DETAIL_MTD_EURO D,
(SELECT * FROM FDD.TRADE_BALANCES B1
WHERE
B1.SEQ_NUM = (SELECT MAX(B2.SEQ_NUM)
FROM FDD.TRADE_BALANCES B2
WHERE
B1.TRDBALID = B2.TRDBALID AND
B1.TRDBALSRC = B2.TRDBALSRC
)) TB,
(SELECT * FROM FDD.INSTRUMENTS I1
WHERE
I1.SEQ_NUM = (SELECT MAX(I2.SEQ_NUM)
FROM FDD.INSTRUMENTS I2
WHERE
I1.INST_ID = I2.INST_ID AND
I1.INST_SRC = I2.INST_SRC
)) I
WHERE
"PROFIT_CTR" = '0000051733'
AND "POSTDATE" >= '20040101'
AND "POSTDATE" <= '20041028' AND
D.TRDBALID = TB.TRDBALID(+) AND
D.TRDBALSRC = TB.TRDBALSRC(+) AND
D.INST_ID = I.INST_ID(+) AND
D.INST_SRC = I.INST_SRC(+)


-------------------------------------------------------------------------------
| Operation | Name |IN-OUT| PQ Distr |Pstr|Pstp|
-------------------------------------------------------------------------------
| SELECT STATEMENT | | | | | |
| SORT AGGREGATE | | | | | |
| NESTED LOOPS OUTER | | | | | |
| NESTED LOOPS OUTER | | | | | |
| PARTITION RANGE ITERATO| | PCWP | | 1 | 29 |
| TABLE ACCESS BY LOCAL |ICE_EQ_DETAIL_MTD_E | P->S | QC (RAN | | |
| INDEX RANGE SCAN |ICE_EQ_DETAIL_MTD_E | PCWP | | 1 | 29 |
| PARTITION LIST SINGLE | | | |KEY |KEY |
| VIEW PUSHED PREDICATE | | | | |
| INDEX RANGE SCAN |TRADE_BALANCES_PK | | |KEY |KEY |
| SORT AGGREGATE | | | | | |
| PARTITION LIST SING| | | |KEY |KEY |
| INDEX RANGE SCAN |TRADE_BALANCES_PK | | |KEY |KEY |
| PARTITION LIST SINGLE | | | |KEY |KEY |
| VIEW PUSHED PREDICATE | | | | | |
| INDEX RANGE SCAN |INSTRUMENTS_PK | | |KEY |KEY |
| SORT AGGREGATE | | | | | |
| PARTITION LIST SINGL| | | |KEY |KEY |
| INDEX RANGE SCAN |INSTRUMENTS_PK | | |KEY |KEY |
-------------------------------------------------------------------------------

Query returns 2.6 million rows, I am trying to speed it up by using parallel degree. ICE_EQ_DETAIL_MTD_EU table
is partitioned, so I hoped to scan partitioned index in parallel and each stream would access other tables in inline views. But it
does not happen. By some reason parallel degree is not pushed into inline view. It causes serious performance issues with the
query.

1. Any idea how I can force inline views to be accessed in parallel as well keeping same execution plan (with Nested Loops)
2. Is there any better way of getting TRADE_BALANCES and INSTUMENTS tables rows with maximum SEQ_NUM. Currently subselect is used that slows query down even further.

Thanks,

Vadim

Tom Kyte
October 14, 2004 - 7:36 pm UTC

(SELECT * FROM FDD.TRADE_BALANCES B1
WHERE
B1.SEQ_NUM = (SELECT MAX(B2.SEQ_NUM)
FROM FDD.TRADE_BALANCES B2
WHERE
B1.TRDBALID = B2.TRDBALID AND
B1.TRDBALSRC = B2.TRDBALSRC
)) TB

why not write that as

select *
from ( select..., max(seq_num) over (partition by trdbalid,trdbalsrc) max_sn
from t )
where seq_num = max_sn


so as to make a single pass on the objects. you can use parallel hints in the inline views themselves.


parallel query and inline view

Vadim Gotovsky, October 14, 2004 - 8:07 pm UTC

Tom,

thanks for your prompt response. I tested simplified version of query (left only one inline view)

explain plan for
SELECT /*+ parallel_index (D) push_pred (TB) */
*
FROM
ICE.ICE_EQ_DETAIL_MTD_EURO D,
(select *
from ( select t.*, max(seq_num) over (partition by trdbalsrc, trdbalid) max_sn
from trade_balances t)
where seq_num = max_sn) TB
WHERE
"PROFIT_CTR" = '0000051733' AND
"POSTDATE" >= '20040101' AND
"POSTDATE" <= '20041028' AND
D.TRDBALID = TB.TRDBALID(+) AND
D.TRDBALSRC = TB.TRDBALSRC(+)

SELECT STATEMENT
HASH JOIN OUTER
PARTITION RANGE ITERATOR
TABLE ACCESS BY LOCAL INDEX ROWID
INDEX RANGE SCAN
VIEW
WINDOW BUFFER
TABLE ACCESS BY INDEX ROWID
INDEX FULL SCAN

The problem that appears with suggested solution is that predicate is not getting pushed into new inline view.
True, I can use parallel hints inside view but I want to avoid scanning the whole TRADE_BALANCES and INSTRUMENTS tables, they are huge - 30 and 20 million rows respectively. What I want is that predicates from outer query would be pushed inside inline views:

AND d.trdbalid = t.trdbalid(+)
AND d.trdbalsrc = t.trdbalsrc(+)
AND d.inst_id = i.inst_id(+)
AND d.inst_src = i.inst_src(+)

and at the same time each parallel stream that scnas ICE.ICE_EQ_DETAIL_MTD_EURO table will Nested Loop into inline views tables. I also tried hint USE_NL, it did not help.

Thanks,

Vadim

Tom Kyte
October 15, 2004 - 10:51 am UTC

question -- are we looking at a "fake" query -- do you really do a count(*) ? (at which point i would say -- just drop the inline views, they don't seem to be relevant?)

execution plan

Vadim Gotovsky, October 14, 2004 - 8:18 pm UTC

sorry, here is complete plan

SELECT STATEMENT |
HASH JOIN OUTER |
PARTITION RANGE ITERATOR |
TABLE ACCESS BY LOCAL INDEX ROWID| ICE_EQ_DETAIL_MTD_EURO
INDEX RANGE SCAN | ICE_EQ_DETAIL_MTD_EURO_IDX1
VIEW |
WINDOW BUFFER |
TABLE ACCESS BY INDEX ROWID | TRADE_BALANCES
INDEX FULL SCAN | TRADE_BALANCES_PK

Parallel query and inline view

Vadim Gotovsky, October 15, 2004 - 11:06 am UTC

Hi Tom,

sorry, I was using count(*) just to measure the time it takes to get all the rows. Sorry - i forgotten to take it off. It is really select *

thanks

Vadim

Tom Kyte
October 15, 2004 - 12:03 pm UTC

count(*) is inappropriate -- never use that to "test", it materially affects the query plans!!!!! radically and totally. set termout off, run query, set termout on in a script -- OK, set autotrace traceonly -- run query -- OK, select count(*) -- doesn't make sense.




Parallel q and inline view

Vadim, October 15, 2004 - 11:15 am UTC

Tom, this is what i came up with after sleepless night :

select
t_trdbalsrc, t_trdbalid, i_inst_src, i_inst_id,
tb_seq, i_seq, tb_max_sn, i_max_sn
from
(
SELECT /*+ parallel_index (D) index (D) use_nl (TB) use_nl (I) ordered */
d.* , tb.trdbalsrc t_trdbalsrc, tb.trdbalid t_trdbalid,
i.inst_src i_inst_src, i.inst_id i_inst_id,
nvl(tb.seq_num,1) tb_seq, nvl(i.seq_num,1) i_seq,
max(nvl(tb.seq_num,1)) over (partition by tb.trdbalsrc, tb.trdbalid) tb_max_sn,
max(nvl(i.seq_num,1)) over (partition by i.inst_src, i.inst_id) i_max_sn
FROM ice.ice_eq_detail_mtd_euro d,
fdd.trade_balances tb,
fdd.instruments i
WHERE "PROFIT_CTR" = '0000051733'
AND "POSTDATE" >= '20040101'
AND "POSTDATE" <= '20041028'
AND d.trdbalid = tb.trdbalid(+)
AND d.trdbalsrc = tb.trdbalsrc(+)
AND d.inst_id = i.inst_id(+)
AND d.inst_src = i.inst_src(+)
)
where
tb_max_sn=tb_seq and
i_max_sn=i_seq

SELECT STATEMENT |
SORT AGGREGATE |
SORT AGGREGATE |
VIEW |
WINDOW SORT |
WINDOW SORT |
NESTED LOOPS OUTER |
NESTED LOOPS OUTER |
PARTITION RANGE ITE|
TABLE ACCESS BY LO|ICE_EQ_DETAIL_MTD_E
INDEX RANGE SCAN |ICE_EQ_DETAIL_MTD_E
PARTITION LIST ITER|
INDEX RANGE SCAN |TRADE_BALANCES_PK
PARTITION LIST ITERA|
INDEX RANGE SCAN |INSTRUMENTS_PK


----------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | TQ |IN-OUT| PQ Distrib | P
----------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | 52 | 14748 | | | |
| SORT AGGREGATE | | 1 | 52 | | | | |
| SORT AGGREGATE | | 1 | 52 | | 5,02 | P->S | QC (RANDOM)|
| VIEW | | 34K| 1M| 14748 | 5,02 | PCWP | |
| WINDOW SORT | | 34K| 3M| 14748 | 5,02 | PCWP | |
| WINDOW SORT | | 34K| 3M| 14748 | 5,01 | P->P | HASH |
| NESTED LOOPS OUTER | | 34K| 3M| 14174 | 5,00 | P->P | HASH |
| NESTED LOOPS OUTER | | 34K| 2M| 7181 | 5,00 | PCWP | |
| PARTITION RANGE ITE| | | | | 5,00 | PCWP | |
| TABLE ACCESS BY LO|ICE_EQ_DETAIL_MTD_E | 34K| 1M| 187 | 5,00 | PCWP | |
| INDEX RANGE SCAN |ICE_EQ_DETAIL_MTD_E | 36K| | 193 | 5,00 | PCWP | |
| PARTITION LIST ITER| | | | | 5,00 | PCWP | |
| INDEX RANGE SCAN |TRADE_BALANCES_PK | 1 | 28 | 1 | 5,00 | PCWP | |
| PARTITION LIST ITERA| | | | | 5,00 | PCWP | |
| INDEX RANGE SCAN |INSTRUMENTS_PK | 1 | 26 | 1 | 5,00 | PCWP | |
----------------------------------------------------------------------------------------------------



Still takes awhile despite using PQ all the way through.
I think 2 WINDOW sorts hurting it.
Do you think there is more optimal solution ?


thanks,

Vadim

Tom Kyte
October 15, 2004 - 12:05 pm UTC

are the estimated row counts at all accurate here?

34k rows is tiny.


how long is "long"

Parallel q and inline view

Vadim, October 15, 2004 - 12:23 pm UTC

Tom,

1. When I use count(*), i first make sure that plan does not change. It is just quicker way, will refrain in future.
2. query returns 2.6 million rows and takes 15 minutes using all parallel streams.
Not sure how it comes up with 15K, I usually do not look at the estimation neither at cost, it proves to be wrong in many cases. I check plan and responce time

Thanks

Vadim

Tom Kyte
October 15, 2004 - 3:10 pm UTC

1) even if the plan does not change -- it is "different" -- count(*) will strip off columns that are not relevant -- even if you ask for them. you cannot compare them.

2) if the optimizer is constantly coming up with bad estimates *YOU WILL NEVER GET THE RIGHT PLAN*. These are the most important pieces of information, garbage in, garbage out.

do you have global statistics
are they even remotely current/correct
are any of the estimated cardinalities near "accurate"

if not, we need to fix that, you've been ignoring the MOST VITAL information.

Parallel query and inline view

Vadim Gotovsky, October 18, 2004 - 1:32 am UTC

Tom,

I agree with you on select count(*) - will not use it again.

I wish CBO worked like this: perfect stats in - perfect execution plan out. 
Unfortunately it is not the case. I wonder why Oracle has that many hints. Why almost every recursive statement is hinted and why until recently Oracle never recommended to collect stats on data dictionary tables. I guess due to imperfection of CBO.

I also did not say that I always ignore cost and estimated rows. I just said that it often has wrong values. As I think in the case being discussed. 
I think the issue with estimated number of rows is that data is skewed and partitions not evenly populated. The actual number of rows that are returned is 1.8M.
But I don't think stats are the issue here, I achieved desired execution plan using hints. It is just still not fast enough. The question is if there is any better way of doing it. Even with parallel query now it still takes up to 15 min to just start returning rows. I suspect 2 WINDOW SORTs are expensive.

Thanks



I recollected stats again.

Global stats are there:

SQL> select table_name,num_rows from dba_tables
  2  where table_name='ICE_EQ_DETAIL_MTD_EURO';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
ICE_EQ_DETAIL_MTD_EURO           13464781


  1  select index_name,num_rows,distinct_keys from dba_indexes
  2* where table_name='ICE_EQ_DETAIL_MTD_EURO'
SQL> /

INDEX_NAME                       NUM_ROWS DISTINCT_KEYS
------------------------------ ---------- -------------
ICE_EQ_DETAIL_MTD_EURO_IDX2       8714968           426
ICE_EQ_DETAIL_MTD_EURO_IDX1      13643468           233
ICE_EQ_DETAIL_MTD_EURO_IDX3      13571132          2471
ICE_EQ_DETAIL_MTD_EURO_IDX4       8725209          1443
ICE_EQ_DETAIL_MTD_EURO_IDX5      12524413            19
ICE_EQ_DETAIL_MTD_EURO_IDX6      12560086           668
ICE_EQ_DETAIL_MTD_EURO_IDX7      13511987         28702

SQL> select partition_name,num_rows from dba_tab_partiti
  2  where table_name='ICE_EQ_DETAIL_MTD_EURO'
  3  order by partition_position
  4  /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
OLD_VALS                                0
P20041001                          211915
P20041002                               0
P20041003                               0
P20041004                          303913
P20041005                          945939
P20041006                         1239189
P20041007                          237029
P20041008                          100000
P20041009                             337
P20041010                               0
P20041011                         1443531
P20041012                          100000
P20041013                               0
P20041014                          100000
P20041015                               0
P20041016                               0
P20041017                               0
P20041018                          100000
P20041019                               0
P20041020                          100000
P20041021                           50000
P20041022                          100000
P20041023                               0
P20041024                               0
P20041025                          100000
P20041026                               0
P20041027                          100000
P20041028                           50000
P20041029                          100000
P20041030                               0
P20041031                               0
P20041101                               0
P20041102                               0
P20041103                               0
P20041104                               0
P20041105                               0
P20041106                               0
P20041107                               0
P20041108                               0
P20041109                               0
P20041110                               0
P20041111                               0
P20041112                               0
P20041113                               0
P20041114                               0
P20041115                               0
P20041116                         7486687
P20041117                               0
P20041118                               0
P20041119                               0
P20041120                               0
P20041121                               0
P20041122                               0
P20041123                               0
P20041124                               0
P20041125                               0
P20041126                               0
P20041127                               0
P20041128                               0
P20041129                               0
P20041130                               0
OTHER                              596241


PARTITION_NAME                   NUM_ROWS DISTINCT_KEYS
------------------------------ ---------- -------------
OLD_VALS                                0             0
P20041001                          211915             9
P20041002                               0             0
P20041003                               0             0
P20041004                          303913            62
P20041005                          945939           383
P20041006                         1239189           418
P20041007                          237029            72
P20041008                          100000             7
P20041009                             337             3
P20041010                               0             0
P20041011                         1443531           191
P20041012                          100000             7
P20041013                               0             0
P20041014                          100000             7
P20041015                               0             0
P20041016                               0             0
P20041017                               0             0
P20041018                          100000             7
P20041019                               0             0
P20041020                          100000             7
P20041021                           50000             7
P20041022                          100000             7
P20041023                               0             0
P20041024                               0             0
P20041025                          100000             7
P20041026                               0             0
P20041027                          100000             7
P20041028                           50000             7
P20041029                          100000             7
P20041030                               0             0
P20041031                               0             0
P20041101                               0             0
P20041102                               0             0
P20041103                               0             0
P20041104                               0             0
P20041105                               0             0
P20041106                               0             0
P20041107                               0             0
P20041108                               0             0
P20041109                               0             0
P20041110                               0             0
P20041111                               0             0
P20041112                               0             0
P20041113                               0             0
P20041114                               0             0
P20041115                               0             0
P20041116                         7362931           254
P20041117                               0             0
P20041118                               0             0
P20041119                               0             0
P20041120                               0             0
P20041121                               0             0
P20041122                               0             0
P20041123                               0             0
P20041124                               0             0
P20041125                               0             0
P20041126                               0             0
P20041127                               0             0
P20041128                               0             0
P20041129                               0             0
P20041130                               0             0
OTHER                              596241           434

63 rows selected.


----------------------------------------------------------------------------------------
| Id  | Operation                               |  Name                        | Rows  |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |  2374 |
|   1 |  VIEW                                   |                              |  2374 |
|   2 |   WINDOW SORT                           |                              |  2374 |
|   3 |    WINDOW SORT                          |                              |  2374 |
|   4 |     NESTED LOOPS OUTER                  |                              |  2374 |
|   5 |      NESTED LOOPS OUTER                 |                              |  2374 |
|   6 |       PARTITION RANGE ITERATOR          |                              |       |
|   7 |        TABLE ACCESS BY LOCAL INDEX ROWID| ICE_EQ_DETAIL_MTD_EURO       |  2374 |
|   8 |         INDEX RANGE SCAN                | ICE_EQ_DETAIL_MTD_EURO_IDX1  | 49503 |
|   9 |       PARTITION LIST ITERATOR           |                              |       |
|  10 |        INDEX RANGE SCAN                 | TRADE_BALANCES_PK            |     1 |
|  11 |      PARTITION LIST ITERATOR            |                              |       |
|  12 |       INDEX RANGE SCAN                  | INSTRUMENTS_PK               |     1 |
----------------------------------------------------------------------------------------

 

Tom Kyte
October 18, 2004 - 8:07 am UTC

"But I don't think stats are the issue here"

something is -- look at the estimated cardinality. you have said "very very far off" -- garbage in, garbage out -- if the estimtated cardinality is so very very far away from reality -- that is going to be a serious problem.

Parallel query

Vadim, October 18, 2004 - 2:00 am UTC

sorry, no remote tables either

Parallel q and inline view

Vadim, October 18, 2004 - 10:46 am UTC

Tom,

This is what I used to collect stats:

DBMS_STATS.GATHER_TABLE_STATS('ICE','ICE_EQ_DETAIL_MTD_EURO',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,DEGREE=>DBMS_STATS.DEFAULT_DEGREE,CASCADE=>TRUE);

Is it sufficient ? I could create histograms as well, but in application binds are used.

Again, execution plan seems fine for me. Cardinality IS wrong but even if it was correct I cannot see better plan than what we have unless you tell me there is a better way. Query is not too complex. Parallel processing is happening now through all tables. I suppose this is just best that can be done. Two WINDOW SORTS make it slow, still faster than SQL with subquery.

Thanks

Vadim

Tom Kyte
October 18, 2004 - 11:04 am UTC

is the data you "where" on heavily skewed.

Parallel q

Vadim, October 18, 2004 - 11:50 am UTC

yes, it is very skewed

Tom Kyte
October 18, 2004 - 1:16 pm UTC

that is what it looks like -- why are you using binds with a big parallel query? Most of the times -- you might want to

a) get histograms
b) not use binds, to get the perfect plan

since you are running queries that take many seconds

instead of running many queris per second.

CPU change

yogesh, October 28, 2004 - 6:07 am UTC

I have a situation, my existing production server have 4 x 262MHz CPU's and 500 G storage. But this server is now old and disks are frequently creating problems. This server will be replaced in 6 months time.

So management guys suggested using test server for next 6 months. This server have huge storage, 2TB+. But the problem is it have only one CPU 1 x 1200MHz. Which is of course more than 4 x 262MHz. My concerns are about performance and the parameters, which I'm using in my init.ora.

parallel_min_servers = 2
parallel_max_servers = 8


Will it a good idea to use test box as production?


Tom Kyte
October 28, 2004 - 12:23 pm UTC

big big difference between 4xslow cpus and 1xfast cpu.

if i have lots of concurrent users, I'd probably favor 4xslow cpus -- to avoid expensive context switching between cpu's.

you'd have to benchmark your *load* on that 1xfast machine, if you are doing lots of concurrent things -- you won't be happy with it.

Can PMON start parallel servers?

Arun Gupta, October 28, 2004 - 11:40 am UTC

Tom,
I was trying to tune a delete statement which runs for several hours. After the statement was running for about 15 minutes, I killed my session and got a message session marked for kill. Since the session was still shown as Killed, I killed the OS process on the server. Immediately, I saw 5 parallel server processes start (P000 to P004) and do some activity for few minutes and then disappear. I ran the tests many times and same behaviour is observed. The parallel servers do not start till I kill my process at OS level. There is nothing in the delete statement that would invoke parallel servers, all tables and indexes used in the delete statement have a degree=1 and instances=1. We are on 9ir2. My thinking is that when PMON started cleanup of my process, it somehow started the parallel servers since parallel_max_servers=5 in this database. Is this the expected behaviour? I used a combination of SQL*Plus and Oracle OEM to monitor my session and Oracle instance.

Thanks

Arun Gupta, October 29, 2004 - 9:50 am UTC

Tom,
The document pertains to transaction recovery during the instance recovery phase. The database was not in instance recovery mode. I had just killed my own session. The database was up all the time. Does the same transaction recovery mechanism apply in such a situation also?

It is really impressive how you remember each and every Oracle document. Wish I could do that too.

Thanks

Tom Kyte
October 29, 2004 - 10:45 am UTC

same thing happens.

trust me, i don't remember each document -- i know in general lots of stuff about oracle -- then i search for the details

ALTER TABLE PARALLEL

A reader, December 23, 2004 - 11:45 am UTC

Everything else staying the same, what impact does ALTER TABLE t PARALLEL; have on queries involving t?

My understanding of PQO was that it kicks in only when a table is being full scanned?

But I see that when I do ALTER TABLE PARALLEL, queries which used to use an index on t now start to FTS it (in parallel).

So I am having difficulty understanding the cause and the effect here? I thought that, after doing its regular costing, if the table is going to FTS anyway, CBO will invoke PQO to do the FTS. But it seems that alter table parallel is making a FTS more attractive to the CBO?

Comments? Thanks

Tom Kyte
December 23, 2004 - 12:40 pm UTC

PQ decreases the cost of the full scan.

CBO takes the least cost query in general, that is its goal. If you open up the possibility of PQ -- it becomes an option. it was not an option before. It costs less to full scan a table in parallel than it would in a single serial process.

Hash joining 2 large tables

A reader, January 06, 2005 - 9:49 am UTC

Suppose I have 2 large tables (50 million rows each) with a 1:1 PK relationship i.e. they should have been 1 table to begin with.

I am trying to combine them as follows
create table new
as select ...
from t1,t2
where t1.pk=t2.pk;

The plan is a simple hash join of the FTS of the 2 tables.

I thought that this would be a perfect candidate for PQ? I did

create table new
as select /*+ parallel(t1) parallel(t2) */ ...
from t1,t2
where t1.pk=t2.pk;

But to my surprise, this took much longer than without the parallel hints.

Why is this? Isnt this a perfect use of PQO? Divide up the table into parts, give each PQ slave a part, and let it rip?

What am I missing here?

Thanks

Tom Kyte
January 06, 2005 - 11:06 am UTC

well, lets say it divided T1 into "10 parallel partitions", you would now have to join T1 (pq partition1) to T2, and T1(pq partition1) to T2, and .....


Unless the tables were physically partitioned by primary key (say hash partitioned into 16 partitions), we'd have to join the first PQ partition of T1 to all of T2 and so on.....

Eg: PQ would have to do more work in this case. Since T2 and T1 have pk values "scattered everywhere"

Why my PQ option is slow ?

Parag J patankar, March 25, 2005 - 9:38 am UTC

Hi Tom,

I am new to parallel query (PQ)

19:44:44 atlas@INFOD> show parameter cpu

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
cpu_count integer 4
parallel_threads_per_cpu integer 2

for testing purpose I have executed following query

declare
l_n number;
l_start number default dbms_utility.get_time;
begin
dbms_output.put_line (' ');
dbms_output.put_line (' ');
dbms_output.put_line (' ');
select count(*) into l_n from t;
dbms_output.put_line ((dbms_utility.get_time - l_start)|| 'Hsecs');
select /* full(t) parallel(t,04) */ count(*) into l_n from t;
dbms_output.put_line ((dbms_utility.get_time - l_start)|| 'Hsecs');
end;
/
it is showing me
19:53:36 atlas@INFOD> @j
24512 Hsecs
31994 Hsecs

My table "t" is having only 358048 records.

Why my parallel query option is taking more time as parallel qyery should distribute 4 slaves to different available 4 CPUs and gets results faster ? ( I am sure at this time nobody is using the system and I have checked the results 3-4 times )

regards & thanks
pjp

Tom Kyte
March 25, 2005 - 6:24 pm UTC

parallel query is useful to take things that take minutes, hours, days and speeding them up.

for things that take seconds -- it takes longer to coordinate everything than it does to do the query serially.


That and you need sufficient IO resources -- if this was a comparision of:

a) 1 person read a single device and count vs
b) 4 people contend for the same device, count and coordinate....


That and if you were using the CBO, the select count(*) would be doing an index fast full scan of any index on a not null column.

The full table scan would full scan the table, not a skinny small index........



but if it is taking 245.12 seconds to query and count 360k records -- on a machine big enough to have 4 cpus, you have a problem -- that is really really slow.

PQ

Parag J Patankar, March 25, 2005 - 9:41 am UTC

Hi Tom,

Sorry in my question in thread I have forgotten to include following explain plan

1* select /*+ full(t) parallel(t,4) */ count(*) from t
20:03:23 atlas@INFOD> /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2380 Card=1)
1 0 SORT (AGGREGATE)
2 1 SORT* (AGGREGATE) :Q22000
3 2 TABLE ACCESS* (FULL) OF 'T' (Cost=2380 Card=1007332) :Q22000


2 PARALLEL_TO_SERIAL SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) F
ROM (SELECT /*+ NO_EXPAND ROWID(A2)

3 PARALLEL_COMBINED_WITH_PARENT

regards & thanks
pjp

Can't run parallel with function.

Kamal, April 12, 2005 - 1:23 pm UTC

Hi Tom,
 I am doing following and as you can see from the timing it's not running select in parallel. How can I use this parallel hint to run my function in parallel.


/*******************/
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2148
parallel_instance_group              string

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
parallel_max_servers                 integer     20
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_threads_per_cpu             integer     2
/****************************/

SQL> set autotrace on
SQL> create or replace package my_package as
  2  function f4 return number;
  3  PRAGMA RESTRICT_REFERENCES  (f4,WNDS,WNPS,RNDS,RNPS,TRUST);
  4  end;
  5  /

Package created.

Elapsed: 00:00:00.01
SQL> create or replace package body my_package as
  2
  3  function f4  return number is
  4  begin
  5  dbms_lock.sleep(10);
  6  return 1;
  7  end;
  8
  9  end;
 10  /

Package body created.

Elapsed: 00:00:00.00
SQL> select /*+ PARALLEL (t,4) */ my_package.f4 from emp t where rownum < 5;
        F4
----------
         1
         1
         1
         1

Elapsed: 00:00:41.15

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=4)
   1    0   COUNT (STOPKEY)
   2    1     PX COORDINATOR
   3    2       PX SEND* (QC (RANDOM)) OF ':TQ10000' (Cost=1 Card=14)  :Q1000
   4    3         PX BLOCK* (ITERATOR) (Cost=1 Card=14)                :Q1000
   5    4           TABLE ACCESS* (FULL) OF 'EMP' (TABLE) (Cost=1 Card :Q1000
          =14)



   3 PARALLEL_TO_SERIAL
   4 PARALLEL_COMBINED_WITH_CHILD
   5 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
         12  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        429  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL>

 

Tom Kyte
April 12, 2005 - 9:06 pm UTC

sorry, but that is running in parallel.

why do you think the timing says otherwise? your function isn't "relevant", it didn't need to be parallelized, the full scan was.

and heck, it could have been - just that the first 4 rows came from the same PX process.

parallel execution of Function in select

Kamal, April 13, 2005 - 4:50 am UTC

Tom,
I tried it with 196 rows again with same result. Having changed the sleep to 1sec. I get results after 3min 22 secs.

I was expecting turnaround of approx 60 secs if the function is executed by four processes for four batches of rows.

So does the oracle only gets rows in parallel and then applies function in serial ?

/********************************/

196 rows selected.

Elapsed: 00:03:22.62

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=196 Bytes=5
88)

1 0 PX COORDINATOR
2 1 PX SEND* (QC (RANDOM)) OF ':TQ10000' (Cost=1 Card=196 By :Q1000
tes=588)

3 2 PX BLOCK* (ITERATOR) (Cost=1 Card=196 Bytes=588) :Q1000
4 3 TABLE ACCESS* (FULL) OF 'TEST' (TABLE) (Cost=1 Card= :Q1000
196 Bytes=588)



2 PARALLEL_TO_SERIAL
3 PARALLEL_COMBINED_WITH_CHILD
4 PARALLEL_COMBINED_WITH_PARENT


Statistics
----------------------------------------------------------
53 recursive calls
3 db block gets
25 consistent gets
0 physical reads
664 redo size
3002 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
196 rows processed
/********************************/

Tom Kyte
April 13, 2005 - 9:13 am UTC

with such a simple query, you cannot really test this. basically you are saying "taking this full scan and run it in parallel", great -- we do and the coordinator is trying to get the rows from the first PX process, which takes 10 seconds per row to generate them.

It doesn't need or have to go from process to process asking for a row at a time (in fact that would be rather inefficient in general). it is asking the first guy for "lots of rows"


You cannot generalize anything from this really, the queries plans with multiple tables and operations can get horribly complex, you have a really really simple case here.

Parallel DML takes less cost but more time!!

Magesh, September 01, 2005 - 1:34 pm UTC

Hi, 
I am trying to see what is the advantage/difference I will be getting by using parallel dml. Here is my test case: 

SQL> insert into emp3 select * from emp1; 

784896 rows created. 

Elapsed: 00:00:11.07 

Execution Plan 
---------------------------------------------------------- 
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=56 Card=32704 Bytes= 
1504384) 

1 0 TABLE ACCESS (FULL) OF 'EMP1' (Cost=56 Card=32704 Bytes=15 
04384) 




SQL> rollback; 

Rollback complete. 

Elapsed: 00:00:02.08 
SQL> insert /*+ parallel(emp3) */ into emp3 
2 select /*+ parallel(emp1) */ * from emp1; 

784896 rows created. 

Elapsed: 00:00:15.06 

Execution Plan 
---------------------------------------------------------- 
0 INSERT STATEMENT Optimizer=CHOOSE (Cost=14 Card=32704 Bytes= 
1504384) 

1 0 TABLE ACCESS* (FULL) OF 'EMP1' (Cost=14 Card=32704 Bytes=1 :Q226300 
504384) 0 



1 PARALLEL_TO_SERIAL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."EMPNO" 
,A1."ENAME",A1."JOB",A1."MGR",A1."HI 


SQL> sho parameter cpu 

NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
cpu_count integer 2 
parallel_threads_per_cpu integer 2 
SQL> 

From the above results, when I was using the parallel hint, it reduced the cost a lot, but it took more time than the normal insert. I am actually surprised. Isn't it less cost means less resource and the query/dml should perform faster? Can you explain this? I am running this from a client machine, I don't have access to the server and so, I cannot change the init parameters. parallel_automatic_tuning is set to false in this database. But I did the same kind of test in my local database with parallel_automatic_tuning set to true, which produced the same results - Less cost but more time. So are we to conclude, even if you reduce the cost of a query plan, it doesn't mean that the query will perform any better? Please help me understand. 

Note: EMP1/EMP3 are replicas of demobld.emp.

Thanks 

Tom Kyte
September 01, 2005 - 4:04 pm UTC

parallel query is good for things taking a long long LONG time.

You do not have such a thing, the overhead of setting up PQ and doing it and coordinating overrides the benefit of parallel in this case

(besides, you didn't enable pdml, that is an alter command, so you did a parallel query to a SERIAL insert -- see that in the plan??)

who does sort when createing index in parallel?

Steven Zhang, September 03, 2005 - 2:00 am UTC

Hi Tom,
My oracle version is Oracle8.1.7 ; When I create a index in parallel,I set sort statistics event 10032 on all parallel sessions in v$px_session.When index is created, I only find parent session dumping its trace file ;It seems only parent session does sort ,other slaves don't.

Once before, I thought when creating index in parallel,one part of slave sessions do full table scan and one part of slave sessions do sort,and parent session or other slaves merge them and write result to disk.

So before I thought setting a large sort_area_size on parent session doesn't work out when indexing in parallel,because slave sessions couldn't inherit parent session's parent sort_area_size.

If slaves does sort ,they would use default sort_area_size in init.ora which is often small on OLTP system and bring inefficient multiple merges sort.

Now I'm confused who would do sort during parallel indexing?

Thanks
Steven


Tom Kyte
September 03, 2005 - 8:52 am UTC

they do what you describe, execution servers sort.

Parallel query execution.

Suvendu, September 15, 2005 - 7:20 pm UTC

Hi Tom,

Thanks a lot for your answer.

Here is question regarding PARALLEL QUERY execution.

With default setting of PARALLEL options in database, when we goes to avail parallel feature on DML,
we issues the ALTER SESSION ENABLE PARALLEL DML statement. And in our case there are 8 processes available on
our OS, where it goes to affect with 4 processes on subsequent DML operations.

But, it is does not affect on SELECT statements (though it clearly saying ENABLE PARALLEL DML),
where we usually use /*+ PARALLEL */ hint to utilize parallel option on SELECT statement.

Question 1 : Is there any way to utilize the parallel feature on SELECT statement without using hints and with default setting of parallel parameters in database?
Question 2 : Instead of 8 processes, why it is going use 4 processes?


Please, correct me, if I'm wrong.

Thanking you.

Regards,
Suvendu


Tom Kyte
September 15, 2005 - 8:38 pm UTC

enable PDML allows for parallel insert/update/delete/merge.

unless the table is "parallel" enabled, you would hint it in the query to get parallel query.


Why 4? Likely you have other users on the system and the algorithms say "not enough resources to go 16, lets go 4"

or you have it set at 4 in the init.ora...

or you are in 8i and have 4 partitions (and the degree of parallelism is fixed to the number of partitions)

or you are in 9i and have some feature enabled that makes it work like 8i with regards to PDML (in 9i, we are not limited to the number of partitions -- UNLESS, you have bitmap indexes or lobs..)

and so on..

Effect of PARALLEL option on execution plan.

Suvendu, September 17, 2005 - 8:28 am UTC

Hi Tom,
Thank you so much for your help. I don't have more words to praise you except THANKS.

Sorry, not to give you details information on above question. But your answer help me what I was asking.

The SELECT statement goes to execute PARALLEL without HINTS, if we specify parallel option on creation or later with
ALTER TABLE <tabname> PARALLEL (DEGREE <n>);

But after changing the tables, instead of increase response time it going to decrease more than usual time
OR there is no change on response time.


Here I paste my query and execution plan from both options:


SELECT (100.0 * SUM (iri_pos_fact_curr_target.dollar_sales)),
"CATEGORY_MASTER".category_id
FROM "POS_FACT_CURR" pos_fact_curr_target,
"PRODUCT_MSTR_ASSORT" product_mstr_assort_alias1,
"BRAND_MASTER",
"CATEGORY_MASTER"
WHERE ( (pos_fact_curr_target.time_id = 1) AND (pos_fact_curr_target.geog_id = 379) )
AND (product_mstr_assort_alias1.prod_id = pos_fact_curr_target.prod_id )
AND (product_mstr_assort_alias1.trademark_id = "BRAND_MASTER".trademark_id )
AND (product_mstr_assort_alias1.category_id = "BRAND_MASTER".category_id )
AND ("BRAND_MASTER".category_id = "CATEGORY_MASTER".category_id)
GROUP BY "CATEGORY_MASTER".category_id;


Before ALTER TABLE <tabname> PARALLEL (DEGREE 8);
================================================

Elapsed: 00:00:02.21

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 903 | 359 | | |
| 1 | SORT GROUP BY | | 21 | 903 | 359 | | |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID | POS_FACT_CURR | 1 | 18 | 355 | 1 | 1 |
| 3 | NESTED LOOPS | | 378 | 16254 | 355 | | |
| 4 | NESTED LOOPS | | 2718 | 67950 | 82 | | |
|* 5 | HASH JOIN | | 2718 | 54360 | 82 | | |
| 6 | TABLE ACCESS FULL | BRAND_MASTER | 4662 | 41958 | 11 | | |
| 7 | VIEW | index$_join$_002 | 42099 | 452K| 69 | | |
|* 8 | HASH JOIN | | 2718 | 54360 | 82 | | |
|* 9 | HASH JOIN | | 2718 | 54360 | 82 | | |
| 10 | BITMAP CONVERSION TO ROWIDS| | | | | | |
| 11 | BITMAP INDEX FULL SCAN | IDX_PRD_MSTR_ASSORT_CAT_ID | | | | | |
| 12 | INDEX FAST FULL SCAN | IDX_PRD_MSTR_ASSORT_PROD_ID | 42099 | 452K| 1 | | |
| 13 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 14 | BITMAP INDEX FULL SCAN | IDX_PRD_MSTR_ASSORT_TRAD_ID | | | | | |
|* 15 | INDEX UNIQUE SCAN | SYS_C002829 | 1 | 5 | | | |
| 16 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 17 | BITMAP AND | | | | | | |
|* 18 | BITMAP INDEX SINGLE VALUE | IDX_PROD_POS_FACT_CURR | | | | 1 | 1 |
|* 19 | BITMAP INDEX SINGLE VALUE | IDX_GEOG_POS_FACT_CURR | | | | 1 | 1 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("POS_FACT_CURR_TARGET"."TIME_ID"=1)
5 - access("PRODUCT_MSTR_ASSORT_ALIAS1"."TRADEMARK_ID"="BRAND_MASTER"."TRADEMARK_ID" AND
"PRODUCT_MSTR_ASSORT_ALIAS1"."CATEGORY_ID"="BRAND_MASTER"."CATEGORY_ID")
8 - access("indexjoin$_alias$_007".ROWID="indexjoin$_alias$_006".ROWID)
9 - access("indexjoin$_alias$_006".ROWID="indexjoin$_alias$_005".ROWID)
15 - access("BRAND_MASTER"."CATEGORY_ID"="CATEGORY_MASTER"."CATEGORY_ID")
18 - access("PRODUCT_MSTR_ASSORT_ALIAS1"."PROD_ID"="POS_FACT_CURR_TARGET"."PROD_ID")
19 - access("POS_FACT_CURR_TARGET"."GEOG_ID"=379)

Note: cpu costing is off



After ALTER TABLE <tabname> PARALLEL (DEGREE 8);
===============================================

Elapsed: 00:00:03.07
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 903 | 236 | | | | | |
| 1 | SORT GROUP BY | | 21 | 903 | 236 | | | 09,04 | P->S | QC (RAND) |
|* 2 | HASH JOIN | | 378 | 16254 | 232 | | | 09,03 | P->P | HASH |
|* 3 | HASH JOIN | | 2718 | 67950 | 20 | | | 09,01 | P->P | HASH |
| 4 | NESTED LOOPS | | 4662 | 65268 | 2 | | | 09,00 | P->P | BROADCAST |
| 5 | TABLE ACCESS FULL| BRAND_MASTER | 4662 | 41958 | 2 | | | 09,00 | PCWP | |
|* 6 | INDEX UNIQUE SCAN| SYS_C002829 | 1 | 5 | | | | 09,00 | PCWP | |
| 7 | TABLE ACCESS FULL | PRODUCT_MSTR_ASSORT | 42099 | 452K| 18 | | | 09,01 | PCWP | |
|* 8 | TABLE ACCESS FULL | POS_FACT_CURR | 5859 | 102K| 212 | 1 | 1 | 09,02 | P->P | HASH |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PRODUCT_MSTR_ASSORT_ALIAS1"."PROD_ID"="POS_FACT_CURR_TARGET"."PROD_ID")
3 - access("PRODUCT_MSTR_ASSORT_ALIAS1"."TRADEMARK_ID"="BRAND_MASTER"."TRADEMARK_ID" AND
"PRODUCT_MSTR_ASSORT_ALIAS1"."CATEGORY_ID"="BRAND_MASTER"."CATEGORY_ID")
6 - access("BRAND_MASTER"."CATEGORY_ID"="CATEGORY_MASTER"."CATEGORY_ID")
8 - filter("POS_FACT_CURR_TARGET"."TIME_ID"=1 AND "POS_FACT_CURR_TARGET"."GEOG_ID"=379)

Note: cpu costing is off


My understanding, with PARALLEL option COST going to reduced. And if I go to minimize response time I believe
materialized view is the only option as per my knowledge. And we are getting PARALLEL option is going for FTS on
this big table POS_FACT_CURR. Is not it a BAD execution plan here?

No. of rows in tables are:
BRAND_MASTER 4662
CATEGORY_MASTER 21
POS_FACT_CURR 81127816
PRODUCT_MSTR_ASSORT 42099

Our database on HP unix with Oracle 9.2.0
The entire table contains latest statistics.


I would appreciate, if you could comment on my understanding and the differences on both execution plan.

Please, correct me if I'm wrong.

Thanking you.

Regards,
Suvendu


Tom Kyte
September 17, 2005 - 11:29 am UTC

Parallel is *not* fast=true.

You need to have the available resources
And lots of them (lots of disk, lots of cpu, lots of memory)

And most importantly, you need a "big" problem - a query taking many minutes, hours, days or more.

Then the application of parallel can be very useful.



-

Rennie, September 28, 2005 - 2:01 pm UTC

Tom,

From the link which you have given earlier in this thread:
</code> http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c22paral.htm#5579 <code>

If i issue CREATE TABLE .. COMPRESS PARALLEL .. AS SELECT ..

Would i be losing space in any way?

This is the text pasted from the link
"When you create a table or index in parallel, it is possible to create pockets of free space--either external or internal fragmentation. This occurs when the temporary segments used by the parallel execution servers are larger than what is needed to store the rows."

Could you please explain what this implies and also how do i check whether unused pockets of free space exist in my database?

Thanks,

Tom Kyte
September 28, 2005 - 5:25 pm UTC

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1093033278124#46802401575019 <code>

and the excerpt from my latest book there -- it explains this in some detail.

Rennie, September 29, 2005 - 1:16 am UTC

Tom,

So can i conclude that; given my scenario (TABLE COMPRESSION) - PARALLEL DDL (in terms of extent allocation) works best with LMT + AUTOALLOCATE only.

For DMTs as well as LMTs with Uniform extents it is not advisable to go for the PARALLEL option especially if one of your goals is to reduce diskspace usage.

Thanks,



Tom Kyte
September 29, 2005 - 7:05 am UTC

for space allocation - yes, it could be - since extent trimming cannot happen with uniform sizes.

Either that or use smaller uniform sizes to reduce the amount of "wastage"


DMT's can be trimmed, they are not "uniform"




Parallel Query and Shared Pool Text

Sami, October 05, 2005 - 3:57 pm UTC

Dear Tom,

This is the output from v$sql.sql_text column.

I am sure this is a parallel query. All the tablenames are converted into :QXXXX.

How to identify the real table name?

I have 1000+ such queries are in my shared pool and most of the queries elapsed time is more than 30 mins(in v$session_longops). I want to find out the actual tablename for these queries.

SELECT A1.C0 C0,A1.C1 C1,A1.C2 C2,A1.C3 C3,A1.C4 C4,A1.C5 C5,A1.C6 C6,A1.C7 C7 F
ROM (SELECT A1.C0 C0,A1.C1 C1,A1.C2 C2,A1.C3 C3,A1.C4 C4,A1.C5 C5,A1.C6 C6,A1.C7
C7 FROM (SELECT /*+ ORDERED NO_EXPAND USE_HASH(A3) */ A2.C6 C0,A2.C7 C1,A2.C0 C
2,A2.C5 C3,A2.C11 C4,A2.C4 C5,A2.C9 C6,TO_CHAR(A2.C6,'DD-Mon-YYYY') C7
FROM
(SELECT /*+ ORDERED NO_EXPAND USE_HASH(A5) */ A5.C0 C0,A4.C0 C1,A5.C9 C2,A4.C1 C3,A5
.C1 C4,A5.C2 C5,A5.C3 C6,A5.C4 C7,A5.C5 C8,A5.C6 C9,A5.C7 C10,A5.C8 C11
FROM :Q44491002 A4,:Q44491000 A5
WHERE A5.C0=A4.C0) A2,:Q44491001 A3
WHERE A3.C0=A2.C0)
A1 ORDER BY A1.C0 DESC,A1.C1 DESC,A1.C2 DESC) A1
WHERE ROWNUM<=15

As always many thanks.

Tom Kyte
October 05, 2005 - 8:21 pm UTC

well, they might not be tables at all - but the subresults of some other process.

but the parent sql should be there an it would be in the range of longer than 30 minutes - i don't know of any way to turn those into "table names"

A reader, October 19, 2005 - 9:43 am UTC

> show parameter parallel

NAME TYPE VALUE
------------------------------------ ----------- -----------fast_start_parallel_rollback string LOW
log_parallelism integer 1
parallel_adaptive_multi_user boolean FALSE
parallel_automatic_tuning boolean FALSE
parallel_execution_message_size integer 4096
parallel_instance_group string
parallel_max_servers integer 60
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_server boolean FALSE
parallel_server_instances integer 1
parallel_threads_per_cpu integer 1
recovery_parallelism integer 0


If parallel processes have reached the max limit of 60, subsequent parallel queries will go in serial(non-parallel),intead of going in parallel.

Is there any way we can prevent these queries (running in non-parallel mode due to parallel processes reaching the max limit) from executing at all ? If these queries execute in serial, it is going to take quite a while and we want to prevent that from running at all

A reader, October 19, 2005 - 10:27 am UTC

Thanks, Tom

I had read that before but was not able to find it now ;-)

difference between enable and force?

Karmit, February 07, 2006 - 5:11 pm UTC

Hi Tom,

What is the difference between:

"alter session enable parallel dml;"

and

"alter session force parallel dml;"

Thanks,
Karmit


parallel query

Karmit, February 14, 2006 - 4:14 pm UTC

Hi Tom,

Would there be any difference in performance between the
following scenarios? (running 10g Rel 2 on Linux 8 processor blade box).

Assume Table BB contains about 300 mil records (no LOBS)
and nothing else is running on the box?
Also, assume following constant settings.
parallel_adaptive_multiuser=TRUE
parallel_max_servers=100
parallel_automatic_tuning=TRUE (although deprecated in 10gR2)


Case A
------
1. Table definition of AA is DEFAULT degree of parallelism.
2. Table definition of BB is DEFAULT degree of parallelism.
3. INSERT /*+ APPEND */ INTO AA SELECT <columns> FROM BB;


Case B
------
1. Table definition of AA is DEFAULT degree of parallelism.
2. Table definition of BB is DEFAULT degree of parallelism.
3. ALTER SESSION ENABLE PARALLEL DML;
4. INSERT /*+ APPEND */ INTO AA SELECT <columns> FROM BB;


Case C
------
1. Table definition of AA is DEFAULT degree of parallelism.
2. Table definition of BB is DEFAULT degree of parallelism.
3. ALTER SESSION FORCE PARALLEL DML;
4. INSERT /*+ APPEND */ INTO AA SELECT <columns> FROM BB;


Case D
------
1. Table definition of AA is NOPARALLEL
2. Table definition of BB is NOPARALLEL
3. INSERT /*+ APPEND parallel(AA,8) */ INTO AA
SELECT /*+ parallel(BB,8) */ <columns> FROM BB;


Case E
------
1. Table definition of AA is NOPARALLEL
2. Table definition of BB is NOPARALLEL
3. ALTER SESSION FORCE PARALLEL DML;
4. INSERT /*+ APPEND parallel(AA,8) */ INTO AA
SELECT /*+ parallel(BB,8) */ <columns> FROM BB;


Case F
------
1. Table definition of AA is NOPARALLEL
2. Table definition of BB is NOPARALLEL
3. ALTER SESSION FORCE PARALLEL DML;
4. INSERT /*+ APPEND parallel(AA) */ INTO AA
SELECT /*+ parallel(BB) */ <columns> FROM BB;


Is there logical explanation for any difference in
performance (if any) ?

Regards,
Karmit


Tom Kyte
February 14, 2006 - 4:42 pm UTC

this is like a quiz...


you want to use the choice that either

a) does no parallel
b) does parallel

depending on your needs. do you want parallel, when you tested it, did it go faster. do you have the IO throughput to handle it.

these are "variations on a theme". it matters no how you get your parallelism - via a hint, whatever. Only that you "did" and got a certain degree. those are the only things you can compare - and you would test.

parallel query

Karmit, February 15, 2006 - 1:46 pm UTC

Hi Tom,
Actually what I was hoping to understand is whether
there is any point in:
1. Having an ALTER SESSION FORCE PARALLEL
when the underlying tables are defined as parallel?
2. Giving a PARALLEL hint when the APPEND hint is given
and the underlying table is defined as PARALLEL?

And lastly:

3. Any difference between ENABLE PARALLEL and FORCE
PARALLEL DML?

Regards,
Karmit

parallel query

Karmit, February 16, 2006 - 4:00 am UTC

Hi Tom,

Sorry to go on about this. I'm not able to find any clear
distinction within the Oracle docs about this.

Any comments on the above queries would be a great help.

Regards,
Karmit

Tom Kyte
February 16, 2006 - 11:49 am UTC

enable "permits" it
force "forces" it

have you see the sql reference guide - it lists out in a bulleted fashion this information for enable and force.

parallel query

karmit, February 16, 2006 - 7:42 pm UTC

Tom,
Thank. Will have a look at the SQL reference as well. Had
been looking at the Datawarehouse guide till now.
Also, any comments on this?

....
....

1. Having an ALTER SESSION FORCE PARALLEL
when the underlying tables are defined as parallel?
2. Giving a PARALLEL hint when the APPEND hint is given
and the underlying table is defined as PARALLEL?


....
....

Regards,
Karmit

Tom Kyte
February 17, 2006 - 1:29 pm UTC

what is to comment about it?

A reader, February 17, 2006 - 12:52 pm UTC

Tom,

I often saw the full hint was used like the following
What is the full hint used for here ?

select /*+ FULL(big) PARALLEL(big,4) */ count(*)
from big;

Thanks



Tom Kyte
February 17, 2006 - 4:58 pm UTC

to have a full scan of big take place.

A reader, February 17, 2006 - 5:43 pm UTC

Thanks Tom,


In which situation we prefer

select /*+ FULL(big) PARALLEL(big,4) */ count(*)
from big;

rather than

select /*+ PARALLEL(big,4) */ count(*)
from big;

?



Tom Kyte
February 18, 2006 - 8:19 am UTC

when you want to make sure a parallel full tablescan takes place, use first one.

parallel query

Karmit, February 18, 2006 - 9:42 am UTC

Hi Tom,

By "any comments on this" I meant to refer to my previous
post with specific questions regarding whether there is
any point in:

1. Having an ALTER SESSION FORCE PARALLEL
when the underlying tables are defined as parallel?

--> Can there be a case where though the referred tables are
defined as PARALLEL (DEFAULT), an INSERT INTO AS SELECT ...
will not be done in parallel?



2. Giving a PARALLEL hint when the APPEND hint is given
and the underlying table is defined as PARALLEL?

--> Can there be a case where the APPEND hint is given
and the underlying destination table is defined as
PARALLEL (DEFAULT), but still the INSERT /*+ APPEND */
is not done in parallel?
--> If there is such a case, then will the PARALLEL hint
address that issue? or is a PARALELL hint redundant
if the underlying table is defined as PARALLEL (DEFAULT)
and the operation has an APPEND hint?

Meanwhile, I stumbled across two more "doubts".

3. I understand that an APPEND operation "locks" the table
and no further DML/DDL can take place on that table until
the APPEND completes. Now, if the table in question is a
partitioned table AND the INSERT /*+ APPEND */ operation
inserts data ONLY into a specific partition - will the
full table be locked OR will only that specific partition
be locked? I'm trying to find out whether there is any hint
which will enable Oracle to lock only a specific partition
while the APPEND is going on?

4. I saw your comment to another readers query
"when you want to make sure a parallel full tablescan takes
place, use first one"...
Is it correct to say that if just the PARALLEL hint is given
and there is an index on the table, the select might actually
do a PARALLEL scan of the index? and not a parallel FTS as
possibly intended? hence FTS+PARALLEL hint ?



Regards,
Karmit



Tom Kyte
February 18, 2006 - 4:41 pm UTC

have you checked out the link I provided above, it tells you what happens, when - with each setting...




the segment being appended into need be locked, if you identify that segment during the operation, eg:


ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert /*+ append */ into t partition(part1) select to_date( '12-mar-2003', 'dd-mon-yyyy'), 1, 1 from dual;

1 row created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          insert into t select to_date( '14-mar-2003', 'dd-mon-yyyy'), 1, 1 from dual;
  5          commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> declare
  2          pragma autonomous_transaction;
  3  begin
  4          insert into t select to_date( '12-mar-2003', 'dd-mon-yyyy'), 1, 1 from dual;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4



the parallel hint by itself would not be sufficient to ensure a parallel full table scan.  it would allow a parallel plan to be considered. 

Alexander the ok, March 01, 2006 - 3:50 pm UTC

Hi Tom,

I was playing with PQ recently to see how it effected a largish query against a "wide" (50 or so columns) table. It helped considerably. In Effect Oracle by Design you say you use it as a last resort. Why is this. What are the drawbacks to using it? Are there any risks I am running by enabling PQ against our "wide" tables? Or did you just mean that more often than not, this just means your database design has flaws, and those are the things that should be corrected. Thanks.

Tom Kyte
March 02, 2006 - 8:48 am UTC

PQ is a tool.

It solves a specific (small) set of problems.

It should not be the first thing you look to to tune something. It is a sledge hammer, you might just need a screwdriver.



Alexander the ok, March 02, 2006 - 9:26 am UTC

Well after having looked through some of your other books last night and this morning the answer was easy; Not scalable. Makes sense. I have a couple other questions:

1) When we enable a table for parellel, how does Oracle decide to use it or not for a given query/dml operation?

2) Can we use PQ only for specific batch jobs, when few or none are on the system with setting at the table level? I couldn't get a PQ plan via only a hint.

Tom Kyte
March 02, 2006 - 12:45 pm UTC

1) the CBO costs out the various plans and chooses from them.

2) you can get a PQ via a hint, definitely.

PQ_DISTRIBUTE hint

A reader, March 20, 2006 - 9:59 am UTC

Tom:

I was trying to use PQ_DISTRIBUTE hint for the following type of query

select /*+ PQ_DISTRIBUTE(a broadcast, none) */ h.*
from (select distinct col1 from temp1) a, (complex query view with union all) h
where a.col1 = h.col1


<temp1 is also a complex view>

The idea is to parallelize join....Will this depend on the instance level parameters such as parallel min servers, max servers? can i use PQ_DISTRIBUTE with queries as above...


Thanks,



Tom Kyte
March 22, 2006 - 12:37 pm UTC

parallel will always depend on the instance parameters (you can turn off parallel, you can turn it "on" - enable it).

you can use hints with any query - as to whether it makes sense - that we cannot say here.

If you don't have a clear idea that it COULD be used, trying to use it seems rather "pointless"?

I'm not really sure what operation you are trying to parallelize really.

after reading the links, still can't get parallel to work

A reader, April 18, 2006 - 4:20 pm UTC

Hi Tom,

Oracle version: 9.2.0.6
Solaris 8, 12 CPUs




The following query runs in parallel - no problem:

select /*+ parallel(b,8) */
cust_id, a.SERVICE_CLASS_DESC rate_plan,
dbms_random.random random_value
from service_class_dim a,
cust_history b
where a.SERVICE_CLASS_DESC in ('A','B')
and a.SERVICE_CLASS_ID = to_number(b.cust_class)
and b.histo_start_date < to_date('20060301','yyyymmdd')
and b.histo_end_date > to_date('20060301','yyyymmdd')
and b.cust_status = 'A'


But the following does not:


create table random_ids (cust_id,rate_plan,random_value)
parallel 8 as
select /*+ parallel(b,8) */
cust_id, a.SERVICE_CLASS_DESC rate_plan,
dbms_random.random random_value
from service_class_dim a,
cust_history b
where a.SERVICE_CLASS_DESC in ('A','B')
and a.SERVICE_CLASS_ID = to_number(b.cust_class)
and b.histo_start_date < to_date('20060301','yyyymmdd')
and b.histo_end_date > to_date('20060301','yyyymmdd')
and b.cust_status = 'A'


I have tried the following:

o alter session force parallel DML parallel 8;
o alter session force parallel DDL parallel 8;
o removing the hint in the select
o remove the degree specification (8)


Any ideas?

Tom Kyte
April 18, 2006 - 6:23 pm UTC

bit of help here

a) how do you know? what was your method to determine this.

b) simple creates and dbms_stats.set_table_stats for the service class dim and cust_history - with some represenatative stats - so we can reproduce?

Same problem as reader.

Alexandre Haratsaris, July 05, 2006 - 8:25 am UTC

Tom,
I have instance parameters already set, but parallel query does not seems to work,although plan cost changed and is lower than before.

Follow the query and explain :

SELECT /*+ PARALLEL(aia,4) PARALLEL(apsa,4) PARALLEL(aca,4) PARALLEL(aipa,4) */
aia.invoice_id
, nvl(paia.po_header_id,0) po_header_id
, nvl(aipa.org_id,0) org_id
, nvl(aia.vendor_id,0) vendor_id
, nvl(aia.vendor_site_id,0) vendor_site_id
, nvl(decode(cavsv.description, null, 'Não'
, 'N' , 'Não'
, 'Y' , 'Sim'),'Não') description
, nvl(aia.invoice_date, to_date(null)) invoice_date
, nvl(aia.invoice_num,' ') invoice_num
, nvl(aia.source,' ') source
, nvl(aia.invoice_amount,0) invoice_amount
, nvl((SELECT nvl(sum(aida.amount),0)
FROM apps.ap_invoice_distributions_all aida
, apps.gl_code_combinations gcc
, cvrd_acl_gl_accounts_v cagl
WHERE aida.invoice_id = aia.invoice_id
And gcc.code_combination_id = aida.dist_code_combination_id
And gcc.segment3 = cagl.flex_value_meaning
And UPPER(cagl.flex_value_meaning) like UPPER('%JUROS%')),0) Interests
, nvl((SELECT nvl(sum(aida.amount),0)
FROM apps.ap_invoice_distributions_all aida
, apps.gl_code_combinations gcc
, cvrd_acl_gl_accounts_v cagl
WHERE aida.invoice_id = aia.invoice_id
And gcc.code_combination_id = aida.dist_code_combination_id
And gcc.segment3 = cagl.flex_value_meaning
And UPPER(cagl.flex_value_meaning) like UPPER('%MULTA%')
And UPPER(cagl.flex_value_meaning) <> UPPER('Gastos com multa de transito')),0) Fine
, nvl(apsa.discount_amount_available +
apsa.second_disc_amt_available +
apsa.third_disc_amt_available,0) Others
, nvl(aia.terms_date, to_date(null)) terms_date
, nvl(aca.check_date, to_date(null)) check_date
FROM cvrd_acl_value_sets_v cavsv
, apps.ap_invoices_all aia
, apps.ap_payment_schedules_all apsa
, apps.ap_checks_all aca
, apps.ap_invoice_payments_all aipa
, (SELECT distinct pda.po_header_id, aida.invoice_id
FROM apps.ap_invoice_distributions_all aida
, apps.po_distributions_all pda
WHERE aida.po_distribution_id = pda.po_distribution_id) paia
WHERE cavsv.flex_value (+) = nvl(aia.attribute3,'N')
AND cavsv.flex_value_set_name (+)= 'CVRD_AP_S_N'
AND aca.void_date is null
AND aia.invoice_id = apsa.invoice_id
AND aia.invoice_id = aipa.invoice_id(+)
AND aipa.check_id = aca.check_id(+)
AND NVL (aipa.reversal_flag, 'N') = 'N'
AND paia.invoice_id (+) = aia.invoice_id ;

0 SELECT STATEMENT Optimizer=HINT: RULE (Cost=62673 Card=23502
48 Bytes=512354064)

1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=21 Card=1 Bytes=106)
3 2 MERGE JOIN (CARTESIAN) (Cost=19 Card=1 Bytes=90)
4 3 NESTED LOOPS (Cost=16 Card=1 Bytes=74)
5 4 NESTED LOOPS (Cost=6 Card=5 Bytes=260)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'FND_FLEX_VALUE
_SETS' (Cost=3 Card=1 Bytes=41)

7 6 INDEX (UNIQUE SCAN) OF 'FND_FLEX_VALUE_SETS_U2
' (UNIQUE) (Cost=2 Card=1)

8 5 TABLE ACCESS (BY INDEX ROWID) OF 'FND_FLEX_VALUE
S' (Cost=3 Card=5 Bytes=55)

9 8 INDEX (RANGE SCAN) OF 'FND_FLEX_VALUES_N3' (NO
N-UNIQUE) (Cost=2 Card=5)

10 4 TABLE ACCESS (BY INDEX ROWID) OF 'FND_FLEX_VALUES_
TL' (Cost=2 Card=1 Bytes=22)

11 10 INDEX (UNIQUE SCAN) OF 'FND_FLEX_VALUES_TL_U1' (
UNIQUE) (Cost=1 Card=1)

12 3 BUFFER (SORT) (Cost=17 Card=3 Bytes=48)
13 12 TABLE ACCESS (BY INDEX ROWID) OF 'AP_INVOICE_DISTR
IBUTIONS_ALL' (Cost=3 Card=3 Bytes=48)

14 13 INDEX (RANGE SCAN) OF 'AP_INVOICE_DISTRIBUTIONS_
U1' (UNIQUE) (Cost=2 Card=3)

15 2 TABLE ACCESS (BY INDEX ROWID) OF 'GL_CODE_COMBINATIONS
' (Cost=2 Card=1 Bytes=16)

16 15 INDEX (UNIQUE SCAN) OF 'GL_CODE_COMBINATIONS_U1' (UN
IQUE) (Cost=1 Card=1)

17 0 SORT (AGGREGATE)
18 17 NESTED LOOPS (Cost=21 Card=1 Bytes=106)
19 18 MERGE JOIN (CARTESIAN) (Cost=19 Card=1 Bytes=90)
20 19 NESTED LOOPS (Cost=16 Card=1 Bytes=74)
21 20 NESTED LOOPS (Cost=6 Card=5 Bytes=260)
22 21 TABLE ACCESS (BY INDEX ROWID) OF 'FND_FLEX_VALUE
_SETS' (Cost=3 Card=1 Bytes=41)

23 22 INDEX (UNIQUE SCAN) OF 'FND_FLEX_VALUE_SETS_U2
' (UNIQUE) (Cost=2 Card=1)

24 21 TABLE ACCESS (BY INDEX ROWID) OF 'FND_FLEX_VALUE
S' (Cost=3 Card=5 Bytes=55)

25 24 INDEX (RANGE SCAN) OF 'FND_FLEX_VALUES_N3' (NO
N-UNIQUE) (Cost=2 Card=5)

26 20 TABLE ACCESS (BY INDEX ROWID) OF 'FND_FLEX_VALUES_
TL' (Cost=2 Card=1 Bytes=22)

27 26 INDEX (UNIQUE SCAN) OF 'FND_FLEX_VALUES_TL_U1' (
UNIQUE) (Cost=1 Card=1)

28 19 BUFFER (SORT) (Cost=17 Card=3 Bytes=48)
29 28 TABLE ACCESS (BY INDEX ROWID) OF 'AP_INVOICE_DISTR
IBUTIONS_ALL' (Cost=3 Card=3 Bytes=48)

30 29 INDEX (RANGE SCAN) OF 'AP_INVOICE_DISTRIBUTIONS_
U1' (UNIQUE) (Cost=2 Card=3)

31 18 TABLE ACCESS (BY INDEX ROWID) OF 'GL_CODE_COMBINATIONS
' (Cost=2 Card=1 Bytes=16)

32 31 INDEX (UNIQUE SCAN) OF 'GL_CODE_COMBINATIONS_U1' (UN
IQUE) (Cost=1 Card=1)

33 0 HASH JOIN (OUTER) (Cost=62673 Card=2350248 Bytes=512354064
)

34 33 HASH JOIN (OUTER) (Cost=8244 Card=1226091 Bytes=24399210
9)

35 34 HASH JOIN (Cost=7533 Card=1226091 Bytes=167974467)
36 35 TABLE ACCESS (FULL) OF 'AP_PAYMENT_SCHEDULES_ALL' (C
ost=1045 Card=1201478 Bytes=40850252)

37 35 FILTER
38 37 HASH JOIN (OUTER)
39 38 FILTER
40 39 HASH JOIN (OUTER)
41 40 TABLE ACCESS (FULL) OF 'AP_INVOICES_ALL' (Co
st=2581 Card=1196693 Bytes=84965203)

42 40 TABLE ACCESS (FULL) OF 'AP_INVOICE_PAYMENTS_
ALL' (Cost=1212 Card=1220052 Bytes=20740884)

43 38 TABLE ACCESS (FULL) OF 'AP_CHECKS_ALL' (Cost=930
Card=435242 Bytes=6528630)

44 34 VIEW OF 'CVRD_ACL_VALUE_SETS_V' (Cost=16 Card=5 Bytes=
310)

45 44 NESTED LOOPS (Cost=16 Card=5 Bytes=440)
46 45 NESTED LOOPS (Cost=6 Card=5 Bytes=325)
47 46 TABLE ACCESS (BY INDEX ROWID) OF 'FND_FLEX_VALUE
_SETS' (Cost=3 Card=1 Bytes=41)

48 47 INDEX (UNIQUE SCAN) OF 'FND_FLEX_VALUE_SETS_U2
' (UNIQUE) (Cost=2 Card=1)

49 46 TABLE ACCESS (BY INDEX ROWID) OF 'FND_FLEX_VALUE
S' (Cost=3 Card=5 Bytes=120)

50 49 INDEX (RANGE SCAN) OF 'FND_FLEX_VALUES_N3' (NO
N-UNIQUE) (Cost=2 Card=5)

51 45 TABLE ACCESS (BY INDEX ROWID) OF 'FND_FLEX_VALUES_
TL' (Cost=2 Card=1 Bytes=23)

52 51 INDEX (UNIQUE SCAN) OF 'FND_FLEX_VALUES_TL_U1' (
UNIQUE) (Cost=1 Card=1)

53 33 VIEW (Cost=53102 Card=2296582 Bytes=43635058)
54 53 SORT (UNIQUE) (Cost=53102 Card=2296582 Bytes=48228222)
55 54 HASH JOIN (Cost=36554 Card=2296582 Bytes=48228222)
56 55 VIEW OF 'index$_join$_013' (Cost=23437 Card=229658
2 Bytes=22965820)

57 56 HASH JOIN (Cost=36554 Card=2296582 Bytes=4822822
2)

58 57 INDEX (FAST FULL SCAN) OF 'AP_INVOICE_DISTRIBU
TIONS_N7' (NON-UNIQUE) (Cost=7066 Card=2296582 Bytes=2296582
0)

59 57 INDEX (FAST FULL SCAN) OF 'AP_INVOICE_DISTRIBU
TIONS_U1' (UNIQUE) (Cost=7066 Card=2296582 Bytes=22965820)

60 55 TABLE ACCESS (FULL) OF 'PO_DISTRIBUTIONS_ALL' (Cos
t=11484 Card=2244464 Bytes=24689104)

Tks,
Alexandre

parallel

Florin, October 11, 2007 - 4:45 am UTC

Hi Tom,

I wanted to make this query to run faster, so I added the following hint:

SELECT /*+ full(re) full(rsr) use_hash(re rsr) parallel (re 4) */
re.event_id, re.start_time, re.subscriber_id, re.customer_id,
re.event_type_id, re.cycle_code, re.cycle_month, re.cycle_year,
re.target_cycle_code, re.source_id, re.dynamic_data,
re.sys_creation_date, re.sys_update_date, re.application_id,
re.sub_partition_id
FROM rated_event re, rpr1_subs_rerate rsr
WHERE re.cycle_code = 11
AND re.cycle_month = 6
AND re.cycle_year = 2007
AND rsr.run_id = 350
AND rsr.process_sts = 'D'
AND re.sub_partition_id = 1
AND rsr.cycle_code = re.cycle_code
AND rsr.cycle_month = re.cycle_month
AND rsr.cycle_year = re.cycle_year
AND rsr.customer_id = re.customer_id
AND (re.subscriber_id LIKE
DECODE (rsr.subscriber_id,
0, '%',
rsr.subscriber_id
)
)
AND re.sys_creation_date <= sysdate
-- TO_DATE ('2007-07-10 09:00:09', 'YYYY-MM-DD HH24:MI:SS')
ORDER BY rsr.target_system,
rsr.customer_id,
rsr.subscriber_id,
re.start_time ASC;

The RATED_EVENT table is partitioned by (CYCLE_CODE, CYCLE_MONTH, SUB_PARTITION_ID).
I have for each month, for each cycle, 4 sub_partition_id (0,1,2,3).

PARTITION BY RANGE (CYCLE_CODE,CYCLE_MONTH,SUB_PARTITION_ID)
(
PARTITION PCYCLE1_MONTH1_0 VALUES LESS THAN (1, 1, 1),
PARTITION PCYCLE1_MONTH1_1 VALUES LESS THAN (1, 1, 2),
PARTITION PCYCLE1_MONTH1_2 VALUES LESS THAN (1, 1, 3),
PARTITION PCYCLE1_MONTH1_3 VALUES LESS THAN (1, 1, 4)


When the process is started, we are passing the all 4 values of the sub_partition_id (0,1,2,3).
I'm expecting to see 4 queries , each one of them with parallel 4.
Somehow, I can see only the first query (sub_partition_id=0) going in 16 parallel, and the rest (1,2,3) without parallel at all, so the first one is finishing in 5 min, while the other three in one hour. I have 8 cpu's and parallel_max_server=16.

The table RATED_EVENT is huge: each partition has 10 GB (15 million rows) while the second table RPR1_SUBS_REARTE is very small.

Kindly request your advice.


parallel DML

Chintu, May 21, 2008 - 5:57 pm UTC

just a follow-up of your reponse to parallel DML.
Does the parallel DML not get enabled when you set parameters parallel_max_servers = 25 in init.ora
or does it apply to only Queries

Tom Kyte
May 21, 2008 - 9:50 pm UTC

PDML (parallel dml) differs from parallel query in that regards; unless you explicitly ask for it, you will not get it:

big_table@ORA10GR1> alter session enable parallel dml;
Session altered.

The fact that the table is ¿parallel¿ is not sufficient, as it was for parallel query. The reasoning behind the need to explicitly enable PDML in your session is the fact that PDML has certain limitations associated with it

Parallel Insert

chintu, May 22, 2008 - 11:41 am UTC

Is the hint alone not sufficient.
though I have hint below and also
alter session enable parallel dml
But don't think there is a parallel processing happening here
********************************************************************************

INSERT /*+ PARALLEL(OJP,DEFAULT) */ INTO PRICING OJP ( STORE, ITEM,
WINNING_PRICE, WINNING_PRICE_TYPE, REGULAR_PRICE, CREATE_SIGN_FLAG,
COMMENTS, THEME, EVENT_NAME, CREATION_DATE_TIME, TYPE, ACTION_TYPE,
START_DATE, END_DATE, POS_CONSUME_DATE, SIGN_CONSUME_DATE,
FUTURE_SUBSCRIBER, SRC_TBL_LAST_UPDATE_BY, SRC_TBL_LAST_UPDATE_DATE )
VALUES
( :B9 , :B8 , :B7 , :B6 , :B5 , :B4 , NULL, NULL, NULL, :B3 , 1, 'ADD', :B2 ,
:B1 , NULL, NULL, NULL, NULL, NULL )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 823979 80.19 158.51 25156 23912 3417277 823979
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 823980 80.19 158.51 25156 23912 3417277 823979

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 998 (JDA_POS) (recursive depth: 1)

Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT GOAL: CHOOSE

********************************************************************************
Tom Kyte
May 23, 2008 - 8:08 am UTC

(tell me the logic you would propose to parallelize a SINGLE ROW INSERT - and for a bonus question - explain why you think that would even HELP and not make it slower. I can only think that it would make it slower, but actually I cannot think of the logic to parallelize a single row insert)

chintu, May 23, 2008 - 12:20 pm UTC

I think I was confused was trying something funny. I also feel this was slowup the process. Thanks for reminding.

A reader, June 11, 2008 - 1:10 pm UTC

I have a question on the parameter parallel_max_servers. Should it just be set to the number of CPUs or can it be higher. We had a database where we had the value set to 8 on a 8 cpu box and we increased to 160. Is that too much ?
Tom Kyte
June 11, 2008 - 4:09 pm UTC

well, can your 8 cpus do 160 things at the same time?

No, not a chance.


Is there a good chance that they could service 16 concurrently running processes? Yes, probably, if we assume that we do a mixture of cpu and IO - so some of the 16 are doing IO, some of the 16 are computing on the CPU.

2 * #cpus is a good number.

A reader, June 11, 2008 - 7:21 pm UTC

Thanks, that helps.

Parallel hint in multiuser environment

Girish, December 16, 2008 - 4:35 am UTC

Hi Tom,

With reference your above example if we use PARALLEL hint in query how it will affect to a front end application ( say in Java ).
And our DBA recommends not to use parallel hint in query ( query used in stored proc ) because it affects performance of front end application when multiple users in large number access web page

Could you please clarify how it will affect front end application

Thanks,
Girish
Tom Kyte
December 29, 2008 - 11:10 am UTC

there is but one answer for this:

it totally depends. It will either

a) go faster
b) go slower
c) not change at all


think about what parallel query means - think about what happens on the computer.

Say you have a 4 cpu machine. If a single user runs a parallel 8 query - they will have 8 (or more) processes running using CPU. They will be hitting the disks, using memory, using the CPU.

Now, if 10 users try to do that at the same time - what will happen? Does your machine have resources to deal with that? Can it absorb the increased workload?

Here is a short excerpt from Expert Oracle Database Architecture on this subject:

<quote>

When to Use Parallel Execution

Parallel execution can be fantastic. It can allow you to take a process that executes over many hours or days and complete it in minutes. Breaking down a huge problem into small components may, in some cases, dramatically reduce the processing time. However, one underlying concept that it will be useful to keep in mind while considering parallel execution is summarized by this very short quote from Practical Oracle8i: Building Efficient Databases (Addison-Wesley, 2001) by Jonathan Lewis:

PARALLEL QUERY option is essentially nonscalable.


Parallel execution is essentially a nonscalable solution. It was designed to allow an individual user or a particular SQL statement to consume all resources of a database. If you have a feature that allows an individual to make use of everything that is available, and then allow two individuals to use that feature, you¿ll have obvious contention issues. As the number of concurrent users on your system begins to overwhelm the number of resources you have (memory, CPU, and I/O), the ability to deploy parallel operations becomes questionable. If you have a four-CPU machine, for example, and on average you have 32 users executing queries simultaneously, then the odds are that you do not want to parallelize their operations. If you allowed each user to perform just a ¿parallel 2¿ query, then you would now have 64 concurrent operations taking place on a machine with just four CPUs. If the machine were not overwhelmed before parallel execution, it almost certainly would be now.

In short, parallel execution can also be a terrible idea. In many cases, the application of parallel processing will only lead to increased resource consumption, as parallel execution attempts to use all available resources. In a system where resources must be shared by many concurrent transactions, such as an OLTP system, you would likely observe increased response times due to this. It avoids certain execution techniques that it can use efficiently in a serial execution plan and adopts execution paths such as full scans in the hope that by performing many pieces of the larger, bulk operation in parallel, it would be better than the serial plan. Parallel execution, when applied inappropriately, may be the cause of your performance problem, not the solution for it.

So, before applying parallel execution, you need the following two things to be true:

o You must have a very large task, such as the full scan of 50GB of data.
o You must have sufficient available resources. Before parallel full scanning 50GB of data, you would want to make sure that there is sufficient free CPU (to accommodate the parallel processes) as well as sufficient I/O. The 50GB should be spread over more than one physical disk to allow for many concurrent read requests to happen simultaneously, there should be sufficient I/O channels from the disk to the computer to retrieve the data from disk in parallel, and so on.

If you have a small task, as generally typified by the queries carried out in an OLTP system, or you have insufficient available resources, again as is typical in an OLTP system where CPU and I/O resources are often already used to their maximum, then parallel execution is not something you¿ll want to consider. So you can better understand this concept, I present the following analogy.


</quote>

A reader, January 04, 2009 - 10:55 pm UTC

This is the ULTIMATE answer for my question

Thanks a ton Tom

insert APPEND with select PARALLEL

A reader, March 28, 2011 - 10:02 am UTC

In an effort to compress table data, I am using the following process to compress one partition at a time:
create table t_temp compress as select * from t where 1=2;

insert /*+ APPEND */ into t_temp 
select /*+ PARALLEL(t,8) */ * from t partition (p1);

alter table t exchange partition p1 with table t_temp;

This reduces the number of blocks in partition p1 after the exchange.

However, I found that if I use:

alter session enable parallel dml;

insert /*+ PARALLEL(tt,8) */ into t_temp tt
select /*+ PARALLEL(t,8) */ * from t;

for the INSERT, the number of blocks in partition p1 after the exchange is actually greater than the original table t.

I realize this is a side effect of using the parallel INSERT, but I was wondering if there is any performance benefit of doing the INSERT with ONLY APPEND while the SELECT is still performed in PARALLEL or will the PARALLEL SELECT actually impose a performance bottleneck?

Thanks !
Tom Kyte
March 29, 2011 - 3:21 am UTC

... any performance benefit of doing the INSERT with ONLY APPEND while the SELECT is still performed in PARALLEL or will the PARALLEL SELECT actually impose a performance bottleneck? ....

there can be - yes, you are performing the IO's in parallel instead of read/write/read/write in serial.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions