Hi Tom,
I am using 9r2 on Windows.
I'd like to setup parallel query. What I am going to do is to set
PARALLEL_AUTOMATIC_TUNING = TRUE. All my tables are creted with
NOPARALLEL clause. Could I still use /*+ PARALLEL */ hint to run
queries in parallel?


Thanks,
Eugene

Hi Tom you said, it is possible to run the queries in paralle using PARALLEL hin when table is noparallen, but,PARALLEL_AUTOMATIC_TUNING is set to TRUE.

Following is the excerpt from metalink:

When PARALLEL_AUTOMATIC_TUNING is set to true, Oracle determines the default values for parameters that control parallel execution. In addition to setting this parameter, you must specify the PARALLEL clause for the target tables in the system. Oracle then tunes all subsequent parallel operations automatically.

and I did testing on my database.

jaffar@PRIMEDB> show parameter automatic;

NAME TYPE VALUE
------------------------------------ -------------------------------- -----
parallel_automatic_tuning boolean TRUE

TABLE_NAME DEGREE
------------------------------ ----------
TESTP 1

jaffar@PRIMEDB> explain plan for select /*+ PARALLEL*/ * from testp;

Explained.

jaffar@PRIMEDB> select * from table(dbms_xplan.display) dual;

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 20 | 2 |
| 1 | TABLE ACCESS FULL | TESTP | 10 | 20 | 2 |
--------------------------------------------------------------------

Note: cpu costing is off

9 rows selected.

my quries doesn't run in parallel mode?

ops$tkyte@ORA10G> drop table t;
Table dropped.
                                                                                                          
ops$tkyte@ORA10G> create table t ( x int );
Table created.
                                                                                                          
ops$tkyte@ORA10G> exec dbms_stats.set_table_stats( user, 'T', numrows=>1000000, numblks =>100000 );
PL/SQL procedure successfully completed.
                                                                                                          
ops$tkyte@ORA10G> show parameter parallel;
                                                                                                          
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     TRUE
parallel_execution_message_size      integer     4096
parallel_instance_group              string
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
recovery_parallelism                 integer     0

ops$tkyte@ORA10G> @plan "select<b> /*+ parallel(t) */ </b>count(*) from t"
ops$tkyte@ORA10G> delete from plan_table;
7 rows deleted.
                                                                                                          
ops$tkyte@ORA10G> explain plan for &1;
old   1: explain plan for &1
new   1: explain plan for select /*+ parallel(t) */ count(*) from t
                                                                                                          
Explained.
                                                                                                          
ops$tkyte@ORA10G> select * from table(dbms_xplan.display);
                                                                                                          
PLAN_TABLE_OUTPUT
----------------------------------------------
Plan hash value: 545668572
                                                                                                          
---------------------------------------------
| Id  | Operation              | Name     | Rows  |... |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |... |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |... |      |            |
|   2 |   PX COORDINATOR       |          |       |... |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |... | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |... | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |  1000K|... | PCWC |            |
|   6 |       TABLE ACCESS FULL| T        |  1000K|... | PCWP |            |
---------------------------------------------------------------------------
                                                                                                          
13 rows selected.
                                                                                                          
ops$tkyte@ORA10G> spool off
 

Try SELECT /*+parallel(TableName,n) */ FROM TableName
where n=2,3,......

Hi Tom,

Here is the step what I did:

a) The statement is like so;
insert /*+ append parallel ( t,4) */ into abc_partition_tab
(
col1, col2, col3…
) select /*+ parallel (x, 4) */ col1, col2, col3… from abc_external_tab;

b) After running, I checked out from HP-UNIX top command, it shows me for 10 second there are 4 parallel process going to use 10-60% CPU resource. Then all vanished from the list.
c) But v$session still showing 4 parallel process session executing 2 separate statement ( referencing to v$SQL) , and statement like so:

Process : P000 , P001
INSERT /*+ APPEND */ INTO …..

Process : P002, P004
SELECT /*+ Q5244000 NO_EXPAND FULL(A1) */ A1."COF_CLIENT_ID"….


But still, there is session (sqlplus) running the original statement as on query:

insert /*+ append parallel(i,4) */ into abc_partition_tab…



Above all information says, there is no PARALLEL insertion going on. And initial TOP commands parallel process output says about they were used for FTS of external table as per my understanding.

So, my question:
1) If parallel process P000 , P001 not showing any parallel hint, why sqlplus session’s statement showing parallel hint : insert /*+ append parallel(i,4) */ into abc_partition_tab…

2) Why it’s not using parallel insertion?
3) Does APPEND hint don’t allows to parallel insertion?

I don’t have any explicitly PARALLEL parameter setting on database. As I’m using degree of parallelism relate number of CPU. And my database on HP-UNIX , Oracle 9.2.

Hope, my question is explanatory. Correct me if I’m wrong.

Every one will happy on this thread, if you elaborate more about same with some good example.


Thanking you,

Suvendu

1) don't know what you mean.  but the parallel execution servers SQL is just generated for it, you and i don't have any control over it, whatever it is -- it is "correct"

2) you didn't enable parallel DML (PDML) probably?  You got parallel query but not PDML, PDML must be enabled explicitly.

3) at least a double negative in there.  When using parallel direct path insert, you must be in "append" mode.


"everyone will be happy" hmm, speaking for the masses?

Assuming BIG_TABLE is "parallel" (alter table big_table parallel), then:


ops$tkyte@ORA10GR1> alter session enable parallel dml;
Session altered.
 
ops$tkyte@ORA10GR1> insert /*+ append */ into UNIFORM_TEST 
  2 select * from big_table;
10000000 rows created.


is all you need, the degree of parallelism will be derived from you system settings (defaults based on the number of cpus) 

Hi Tom

I'm not sure I understand the the answer correctly.

My understanding is that the best approach to making SELECT, INSERT, DELETE etc. work in parallel is to set:
PARALLEL_AUTOMATIC_TUNING = TRUE
and then change the tables enabling the parallel option (not parallel <n>).

But, later in the answer, the following query is suggested:

alter session enable parallel dml;
and
insert /*+ append */ into UNIFORM_TEST
2 select * from big_table;


I am now confused about what needs to be done so that all SELECT, DELETE and INSERT statements will run in parallel for whoever connects to the DB.

Tom,

APPEND hint will ignore integrity constraints..
Is that the same behaviour for PARALLEL as well?

no it won't.

NO IT WON'T.

ops$tkyte@ORA10G> create table t ( x int check (x > 0) );
Table created.
 
ops$tkyte@ORA10G> create table t2 ( x int primary key );
Table created.
 
ops$tkyte@ORA10G> insert into t2 values ( 1 );
1 row created.
 
ops$tkyte@ORA10G> insert /*+ APPEND */ into t select 1 from dual;
1 row created.
 
ops$tkyte@ORA10G> select * from t;
select * from t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

<b>that shows we did a direct path, now:</b> 
 
ops$tkyte@ORA10G> commit;
Commit complete.
 
ops$tkyte@ORA10G> insert /*+ APPEND */ into t select 0 from dual;
insert /*+ APPEND */ into t select 0 from dual
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C0014018) violated
 
<b>that clearly shows integrity constraints are *not* ignored, what you may have meant was that direct path is disabled for a specific type of constraint:</b>
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> alter table t add constraint t_fk foreign key(x) references t2;
 
Table altered.
 
ops$tkyte@ORA10G> insert /*+ APPEND */ into t select 1 from dual;
 
1 row created.
 
ops$tkyte@ORA10G> select * from t;
 
         X
----------
         1
         1
 
ops$tkyte@ORA10G>

<b>we did not direct path because of the foreign key</b>
 

I am trying to use PARALLEL hint for INSERT, the data insertion will be in ONE single partition, as per ORACLE, even if I use PARALLEL hint to insert data in one partition, DB will not open multiple sessions (rather it will be serial).

Is there any way to force database to use parallel sessions while inserting as well?


Thanks,

Jagan

Reg. Constraints: Yes, I meant by referential integrity constraints, sorry being not very clear on the problem.

About the second problem of parallel DML’s, we are using 9.2.0.4 and there is a bug:
<<<
</code> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=2929556.8

(Bug 2929556  ORA-372 ORA-1110 inserting into partition with enable PARALLEL DML set)
>>>

To overcome this, I was using some workarounds provided by Oracle:
<<<

http://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=2929556 <code>
WORKAROUND:
-----------
Disable the PARALLEL DML
>>>

Because of volume of the data, client is not ready to make all partitions read/write, so I cannot alter the session to enable the “parallel DML”. I have just enabled “parallel query”, but as intended, it is only working for selects.

My question was, with all these constraints, is there a way to force parallelism for inserts? OR are there any suggestions to improve the performance?

One again Thanks a lot for your help.

Jagan

I end up doing BULK INSERTS in chunk of rows.
Let's see once we migrate to 10g!

Thanks,

Jagan

Tom,

I have some performance related issues to insert data into partitioned tables. I have changed the code from traditional FOR LOOP inserts to the SELECT INTO using PARALLEL hints (because of RULE based optimization).

The daily process is to refresh the data from live (OLTP) tables to so called warehouse tables. There is one table in particular that takes almost 80% of the entire process, it has monthly partitions from 1999 till date (about 700 M plus rows). Unfortunately there are 6 GLOBAL indexes on the table (used for reporting purpose) along with LOCAL indexes. I have traced the session and ran Trace Analyzer to prove that indexes are taking more time (about 70%):
Non-idle Wait (secs): 5222.307 (out of approximately 7200 secs) ==> (70%)
I/O Wait Event: db file sequential read
SQL example:
INSERT /*+ PARALLEL(target_tab,12) */ INTO target_table target_tab (columns….)
SELECT /*+ PARALLEL(source_tab,12) */ columns…
FROM target_table target_tab, source_table source_tab
WHERE conditions to check primary keys.


Could you please suggest better approach for this?

I am planning to remove GLOBAL indexes and create them as LOCAL. If I go this way, would there be any performance impact on reports (data selection criteria for may be across the months and partition is based on date/month)?


Thanks,

JD

Thanks for the quick reply.

The PDML is not enabled because of 9.2.0.4 bug (mentioned above on this page). Only parallel query is enabled. But you are right this query is not using parallel for inserts, it is only selecting using parallel hint.

About the indexes, we have exactly same thing, there is one GLOBAL index on name and as you said any search using that index would die :(

Is there a way out in 9.2.0.4 or I have to wait until the next migration?

Thanks,

JD

I will try APPEND as well.

Would it be better if I replace the local index with global partition index?

The table is partitioned by date/month and the index (local) is on name column.
If I try to partition index (globally) based on the distribution of first letter in the name column, would that create any performance problem on data retrieval?

Would it be better if I replace the GLOBAL index with global partition index?

The table is partitioned by date/month and the index (GLOBAL) is on name column.
If I try to partition index (globally) based on the distribution of first letter in the name column, would that create any performance problem on data retrieval?

I don't suppose Oracle has an option to parallelize PLSQL?

For example, I have a 12-CPU box and I want to do:
BEGIN
FOR rec in (select *,rowid from very_large_table) LOOP
do_some_very_expensive_PLSQL_here();
END LOOP;
END;

If I do the above as is in serial, it takes 12 hours. If I manually break it down into 12 equal-sized chunks and run it in 12 different sql*plus sessions, then it will run in 1 hour, e.g. the cursor from above can become:

select *, rowid from very_large_table where MOD(object_id,12) = &thread_num

So my question is, I guess:
Are there any better alternatives to "manually" coding a parellelism into the process?

(Note that the do_some_very_expensive_PLSQL_here() function is actually a number of quite complex functions which do DML on a number of different tables, so the functions cannot simply be put into a single SELECT /*+ PARALLEL */ statement.)

from my latest book (see homepage for details if you are interested):


Procedural Parallelism

I would like to discuss two types of procedural parallelism:

    *    Parallel pipelined functions, which is a feature of Oracle.
    *    "Do-it-yourself (DIY) parallelism," which is the application to 

your own applications of the same techniques that Oracle applies to parallel full table scans. DIY parallelism is more of a development technique than anything built into Oracle directly.

Many times you'll find that applications-typically batch processes-designed to execute serially will look something like the following procedure:

Create procedure process_data
As
Begin
For x in ( select * from some_table )
   Perform complex process on X
   Update some other table, or insert the record somewhere else
End loop
end 

In this case, Oracle's parallel query or PDML won't help a bit (in fact, parallel execution of the SQL by Oracle here would likely only cause the database to consume more resources and take longer). If Oracle were to execute the simple SELECT * FROM SOME_TABLE in parallel, it would provide this algorithm no apparent increase in speed whatsoever. If Oracle were to perform in parallel the UPDATE or INSERT after the complex process, it would have no positive affect (it is a single-row UPDATE/INSERT, after all).

There is one obvious thing you could do here: use array processing for the UPDATE/INSERT after the complex process. However, that isn't going to give you a 50 percent reduction or more in runtime, and often that is what you are looking for. Don't get me wrong, you definitely want to implement array processing for the modifications here, but it won't make this process run two, three, four, or more times faster.

Now, suppose this process runs at night on a machine with four CPUs, and it is the only activity taking place. You have observed that only one CPU is partially used on this system, and the disk system is not being used very much at all. Further, this process is taking hours, and every day it takes a little longer as more data is added. You need to reduce the runtime by many times-it needs to run four or eight times faster-so incremental percentage increases will not be sufficient. What can you do?

There are two approaches you can take. One approach is to implement a parallel pipelined function, whereby Oracle will decide on appropriate degrees of parallelism (assuming you have opted for that, which would be recommended). Oracle will create the sessions, coordinate them, and run them, very much like the previous example with parallel DDL where, by using CREATE TABLE AS SELECT OR INSERT ./*+APPEND*/, Oracle fully automated parallel direct path loads for us. The other approach is DIY parallelism. We'll take a look at both approaches in the sections that follow.


Parallel Pipelined Functions

We'd like to take that very serial process PROCESS_DATA from earlier and have Oracle execute it in parallel for us. To accomplish this, we need to turn the routine "inside out." Instead of selecting rows from some table, processing them, and inserting them into another table, we will insert into another table the results of fetching some rows and processing them. We will remove the INSERT at the bottom of that loop and replace it in the code with a PIPE ROW clause. The PIPE ROW clause allows our PL/SQL routine to generate table data as its output, so we'll be able to SELECT from our PL/SQL process. The PL/SQL routine that used to procedurally process the data becomes a table, in effect, and the rows we fetch and process are the outputs. We've seen this many times throughout this book every time we've issued the following:

Select * from table(dbms_xplan.display);

That is a PL/SQL routine that reads the PLAN_TABLE; restructures the output, even to the extent of adding rows; and then outputs this data using PIPE ROW to send it back to the client. We're going to do the same thing here in effect, but we'll allow for it to be processed in parallel.

We're going to use two tables in this example: T1 and T2. T1 is the table we were reading previously, and T2 is the table we need to move this information into. Assume this is some sort of ETL process we run to take the transactional data from the day and convert it into reporting information for tomorrow. The two tables we'll use are as follows:

ops$tkyte-ORA10G> create table t1
  2  as
  3  select object_id id, object_name text
  4    from all_objects;
Table created.

ops$tkyte-ORA10G> begin
  2      dbms_stats.set_table_stats
  3      ( user, 'T1', numrows=>10000000,numblks=>100000 );
  4  end;
  5  /
PL/SQL procedure successfully completed.

ops$tkyte-ORA10G> create table t2
  2  as
  3  select t1.*, 0 session_id
  4    from t1
  5   where 1=0;
Table created.

We used DBMS_STATS to "trick" the optimizer into thinking that there are 10,000,000 rows in that input table and that it consumes 100,000 database blocks. We want to simulate a big table here. The second table, T2, is simply a copy of the first table's structure with the addition of a SESSION_ID column. That column will be useful to actually "see" the parallelism that takes place.
Next, we need to set up object types for our pipelined function to return. The object type is simply a structural definition of the "output" of the procedure we are converting. In this case, it looks just like T2:

ops$tkyte-ORA10G> CREATE OR REPLACE TYPE t2_type
  2  AS OBJECT (
  3   id         number,
  4   text       varchar2(30),
  5   session_id number
  6  )
  7  /
Type created.

ops$tkyte-ORA10G> create or replace type t2_tab_type
  2  as table of t2_type
  3  /
Type created.


And now for the pipelined function, which is simply the original PROCESS_DATA 
procedure rewritten. The procedure is now a function that produces rows. It accepts as an input the data to process in a ref cursor. The function returns a T2_TAB_TYPE, the type we just created. It is a pipelined function that is PARALLEL_ENABLED. The partition clause we are using says to Oracle, "Partition, or slice up, the data by any means that works best. We don't need to make any assumptions about the order of the data."


You may also use hash or range partitioning on a specific column in the ref cursor. That would involve using a strongly typed ref cursor, so the compiler knows what columns are available. Hash partitioning would just send equal rows to each parallel execution server to process based on a hash of the column supplied. Range partitioning would send nonoverlapping ranges of data to each parallel execution server, based on the partitioning key. For example, if you range partitioned on ID, each parallel execution server might get ranges 11000, 100120000, 2000130000, and so on (ID values in that range). 

Here, we just want the data split up. How the data is split up is not relevant to our processing, so our definition looks like this:

ops$tkyte-ORA10G> create or replace
  2  function parallel_pipelined( l_cursor in sys_refcursor )
  3  return t2_tab_type
  4  pipelined
  5  parallel_enable ( partition l_cursor by any )

We'd like to be able to see what rows were processed by which parallel execution servers, so we'll declare a local variable L_SESSION_ID and initialize it from V$MYSTAT:

  6
  7  is
  8      l_session_id number;
  9      l_rec        t1%rowtype;
 10  begin
 11      select sid into l_session_id
 12        from v$mystat
 13       where rownum =1;

Now we are ready to process the data. We simply fetch out a row (or rows, as we could certainly use BULK COLLECT here to array process the ref cursor), perform our complex process on it, and pipe it out. When the ref cursor is exhausted of data, we close the cursor and return:

 14      loop
 15          fetch l_cursor into l_rec;
 16          exit when l_cursor%notfound;
 17          -- complex process here
 18          pipe row(t2_type(l_rec.id,l_rec.text,l_session_id));
 19      end loop;
 20      close l_cursor;
 21      return;
 22  end;
 23  /
Function created.

And that's it. We're ready to process the data in parallel, letting Oracle figure out based on the resources available what the most appropriate degree of parallelism is:

ops$tkyte-ORA10G> alter session enable parallel dml;
Session altered.

ops$tkyte-ORA10G> insert /*+ append */
  2  into t2(id,text,session_id)
  3  select *
  4  from table(parallel_pipelined
  5            (CURSOR(select /*+ parallel(t1) */ *
  6                      from t1 )
  7             ))
  8  /
48250 rows created.

ops$tkyte-ORA10G> commit;
Commit complete.

Just to see what happened here, we can query the newly inserted data out and group by SESSION_ID to see first how many parallel execution servers were used, and second how many rows each processed:

ops$tkyte-ORA10G> select session_id, count(*)
  2    from t2
  3   group by session_id;

SESSION_ID   COUNT(*)
---------- ----------
       241       8040
       246       8045
       253       8042
       254       8042
       258       8040
       260       8041
6 rows selected.

Apparently, we used six parallel execution servers for the SELECT component of this parallel operation, and each one processed about 8,040 records each.
As you can see, Oracle parallelized our process, but we underwent a fairly radical rewrite of our process. This is a long way from the original implementation. So, while Oracle can process our routine in parallel, we may well not have any routines that are coded to be parallelized. If a rather large rewrite of your procedure is not feasible, you may well be interested in the next implementation: DIY parallelism.



Do-It-Yourself Parallelism

Say we have that same process as in the preceding section: the serial, simple procedure. We cannot afford a rather extensive rewrite of the implementation, but we would like to execute it in parallel. What can we do? My approach many times has been to use rowid ranges to break the table up into some number of ranges that don't overlap (yet completely cover the table).

This is very similar to how Oracle performs a parallel query conceptually. If you think of a full table scan, Oracle processes that by coming up with some method to break the table into many "small" tables, each of which is processed by a parallel execution server. We are going to do the same thing using rowid ranges. In early releases, Oracle's parallel implementation actually used rowid ranges itself.

We'll use a BIG_TABLE of 1,000,000 rows, as this technique works best on big tables with lots of extents, and the method I use for creating rowid ranges depends on extent boundaries. The more extents used, the better the data distribution. So, after creating the BIG_TABLE with 1,000,000 rows, we'll create T2 like this:

big_table-ORA10G> create table t2
  2  as
  3  select object_id id, object_name text, 0 session_id
  4    from big_table
  5   where 1=0;
Table created.

We are going to use the job queues built into the database to parallel process our procedure. We will schedule some number of jobs. Each job is our procedure slightly modified to just process the rows in a given rowid range.
Note    In Oracle 10g, you could use the scheduler as well for something so simple.  In order to make the example 9i compatible, we'll use the job queues here.

To efficiently support the job queues, we'll use a parameter table to pass inputs to our jobs:

big_table-ORA10G> create table job_parms
  2  ( job        number primary key,
  3    lo_rid  rowid,
  4    hi_rid  rowid
  5  )
  6  /
Table created.

This will allow us to just pass the job ID into our procedure, so it can query this table to get the rowid range it is to process. Now, for our procedure. The code in bold is the new code we'll be adding:

big_table-ORA10G> create or replace
  2  procedure serial( p_job in number )
  3  is
  4      l_rec        job_parms%rowtype;
  5  begin
  6      select * into l_rec
  7        from job_parms
  8       where job = p_job;
  9
 10      for x in ( select object_id id, object_name text
 11                   from big_table
 12                  where rowid between l_rec.lo_rid
 13                                  and l_rec.hi_rid )
 14      loop
 15          -- complex process here
 16          insert into t2 (id, text, session_id )
 17          values ( x.id, x.text, p_job );
 18      end loop;
 19
 20      delete from job_parms where job = p_job;
 21      commit;
 22  end;
 23  /
Procedure created.

As you can see, it is not a significant change. Most of the added code was simply to get our inputs and the rowid range to process. The only change to our logic was the addition of the predicate on lines 12 and 13.

Now let's schedule our job. We'll use a rather complex query using analytics to divide the table. The innermost query on lines 19 through 26 breaks the data into eight groups in this case. The first sum on line 22 is computing a running total of the sum of blocks; the second sum on line 23 is the total number of blocks. If we integer divide the running total by the desired "chunk size" (the total size divided by 8 in this case), we can create groups of files/blocks that cover about the same amount of data. The query on lines 8 through 28 finds the high and low file numbers and block numbers by GRP and returns the distinct entries. . It builds the inputs we can then send to DBMS_ROWID to create the rowids Oracle wants. We take that output and, using DBMS_JOB, submit a job to process the rowid range: 

big_table-ORA10G> declare
  2          l_job number;
  3  begin
  4  for x in (
  5  select dbms_rowid.rowid_create
            ( 1, data_object_id, lo_fno, lo_block, 0 ) min_rid,
  6         dbms_rowid.rowid_create
            ( 1, data_object_id, hi_fno, hi_block, 10000 ) max_rid
  7    from (
  8  select distinct grp,
  9         first_value(relative_fno) 
              over (partition by grp order by relative_fno, block_id
 10           rows between unbounded preceding and unbounded following) lo_fno,
 11         first_value(block_id    ) 
              over (partition by grp order by relative_fno, block_id
 12           rows between unbounded preceding and unbounded following) lo_block,
 13         last_value(relative_fno) 
              over (partition by grp order by relative_fno, block_id
 14           rows between unbounded preceding and unbounded following) hi_fno,
 15         last_value(block_id+blocks-1) 
              over (partition by grp order by relative_fno, block_id
 16           rows between unbounded preceding and unbounded following) hi_block,
 17         sum(blocks) over (partition by grp) sum_blocks
 18    from (
 19  select relative_fno,
 20         block_id,
 21         blocks,
 22         trunc( (sum(blocks) over (order by relative_fno, block_id)-0.01) /
 23                (sum(blocks) over ()/8) ) grp
 24    from dba_extents
 25   where segment_name = upper('BIG_TABLE')
 26     and owner = user order by block_id
 27         )
 28         ),
 29         (select data_object_id 
               from user_objects where object_name = upper('BIG_TABLE') )
 30  )
 31  loop
 32          dbms_job.submit( l_job, 'serial(JOB);' );
 33          insert into job_parms(job, lo_rid, hi_rid)
 34          values ( l_job, x.min_rid, x.max_rid );
 35  end loop;
 36  end;
 37  /
PL/SQL procedure successfully completed. 

That PL/SQL block would have scheduled up to eight jobs for us (fewer if the table could not be broken in to eight pieces due to insufficient extents or size). We can see how many jobs were scheduled and what their inputs are as follows:

big_table-ORA10G> select * from job_parms;

       JOB LO_RID             HI_RID
---------- ------------------ ------------------
       172 AAAT7tAAEAAAAkpAAA AAAT7tAAEAAABQICcQ
       173 AAAT7tAAEAAABQJAAA AAAT7tAAEAAABwICcQ
       174 AAAT7tAAEAAABwJAAA AAAT7tAAEAAACUICcQ
       175 AAAT7tAAEAAACUJAAA AAAT7tAAEAAAC0ICcQ
       176 AAAT7tAAEAAAC0JAAA AAAT7tAAEAAADMICcQ
       177 AAAT7tAAEAAADaJAAA AAAT7tAAEAAAD6ICcQ
       178 AAAT7tAAEAAAD6JAAA AAAT7tAAEAAAEaICcQ
       179 AAAT7tAAEAAAEaJAAA AAAT7tAAEAAAF4ICcQ
8 rows selected.

big_table-ORA10G> commit;
Commit complete.

That commit released our jobs for processing. We have JOB_QUEUE_PROCESSES set to 0 in the parameter file, so all eight started running and shortly finished. The results are as follows:

big_table-ORA10G> select session_id, count(*)
  2    from t2
  3   group by session_id;

SESSION_ID   COUNT(*)
---------- ----------
       172     130055
       173     130978
       174     130925
       175     129863
       176     106154
       177     140772
       178     140778
       179      90475
8 rows selected.

It's not as evenly distributed as the Oracle built-in parallelism in this case, but it's pretty good. If you recall, earlier we saw how many rows were processed by each parallel execution server and, using the built-in parallelism, the row counts were very close to each other (they were off only by one or two). Here we had a job that processed as few as 90,475 rows and one that processed as many as 140,778. Most of them processed about 130,000 rows in this case.

Suppose, however, that you do not want to use the rowid processing-perhaps the query is not as simple as SELECT * FROM T and involves joins and other constructs that make using the rowid impractical. You can use the primary key of some table instead. For example, say you want to break that same BIG_TABLE into ten pieces to be processed concurrently by primary key. You can do that easily using the NTILE built-in analytic function. The process is rather straightforward:

big_table-ORA10G> select nt, min(id), max(id), count(*)
  2    from (
  3  select id, ntile(10) over (order by id) nt
  4    from big_table
  5         )
  6   group by nt;

        NT    MIN(ID)    MAX(ID)   COUNT(*)
---------- ---------- ---------- ----------
         1          1     100000     100000
         2     100001     200000     100000
         3     200001     300000     100000
         4     300001     400000     100000
         5     400001     500000     100000
         6     500001     600000     100000
         7     600001     700000     100000
         8     700001     800000     100000
         9     800001     900000     100000
        10     900001    1000000     100000

10 rows selected.

Now you have ten nonoverlapping primary key ranges, all of nice equal size, that you can use to implement the same DBMS_JOB technique as shown earlier to parallelize your process.
 

"We have JOB_QUEUE_PROCESSES set to 0 in the parameter file, so all eight started running and shortly finished"

I guess it's a typo. I think you meant 8 ?

We have 8 CPUs on each node (Sun-Fire-V490 -  3 Node RAC)
Oracle 9.2.0.6, Sun OS 2.9

Anytime we look at the number of parallel process, it is close to 50 on all 3 nodes. Only 5 to 10 tables and indexes have parallel degree 4 or 5. We are not using any parallel hint in application/query.

From Statspack Report:
======================
                                 Current      Maximum     Initial
Resource Name                  Utilization  Utilization  Allocation      Limit
------------------------------ ------------ ------------ ---------- ----------
parallel_max_servers                     52          101        101        101
          -------------------------------------------------------------

SQL> show parameter parallel
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
log_parallelism                      integer     1
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     TRUE
parallel_execution_message_size      integer     4096
parallel_instance_group              string
parallel_max_servers                 integer     100
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     TRUE
parallel_server_instances            integer     3
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

Questions
=========
1) Since we have  8 CPUs and parallel_threads_per_cpu=2, is there any advantage of having more than 16 parallel process?

2) We defined the "parallel_max_servers=100", is it the right value of 8 CPU machine? In other words, how to determine the correct value for "parallel_max_servers"?

Sometimes the number of parallel process exceeds 100 and we are getting error in  Oracle Advanced Replication which uses parallel degree 1.
 

Thanks Tom for the detailed information about pipelined functions and DIY technique. This is exactly I was looking for. I wish, I could have explained my problem in the first posting!

.. JD

Tom,

Question regarding parallel ddl.

We run on 9i R2.

In the DDL given below:

CREATE TABLE t PARALLEL 4 AS SELECT /*+ PARALLEL(tab,4)*/ FROM t1 partition (p_t1) tab ;

In this, do we need to specify a parallel hint for the select statement? We are not seeing a difference in plan and assuming that the hint is not relevant for CTAS. Could you confirm this for us please?

The query selects data from a single partition. I believe that 9i onwards PARALLEL operations are supported on queries accessing single partitions as well. am i correct here? Do i need to alter table and set it as parallel explicitly to be able to fire a parallel query?

Thanks,

Tom,

Sorry for not being clear :

I wanted to know how different is

CREATE TABLE t PARALLEL 4 SELECT * from t1 partition (p1)

vs

CREATE TABLE t PARALLEL 4 SELECT /*+ PARALLEL(tab,4)*/ * from t1 partition (p1) tab

My question was; if i omit the parallel hint in the select statement - would the select statement then run in serial?


One more question is: would this select (if in parallel) bypass the buffer cache? Direct reads?

Thanks,

In Oracle 10g R2, parallel_automatic_tuning is deprecated.
In your initial response to this you had recommended using this parameter to setup parallel query.

What do you recommend in 10g R2?

Thanks.

Hi Tom,

I have a need to create a global partitioned index on a large partitioned IOT table, with about 800million rows and size of 146G.

I used options, parallel 16, nologging, compress , and created a 100G dedicated LMT temp tablespace for it. However, during the index creation I did not see any parallelism kicking in. I only see one active session, which means it is creating the index serially which took about 8 hours to complete.

Someone suggest me to use parallel_min_percent=50 instead of 0, Setting PARALLEL_MIN_PERCENT to 0 means that if while creating the index, Oracle would haven't got enough resource then it will execute the operation in serial rather than parallel.

Now, my question is:
1. Will parallel_min_percent help? since I already set up
parallel_automic_tuning=true.
2. if I set parallel_min_percent = 50, I still need enough resource for DOP of 8 out of 16, otherwise , oracle will run serially. Now what is the value for parallel_min_percent will allow oracle the flexibility to kick in any parallelism when it has only that enough resource, will that be 1? if 1 is the magic number , can we set this at instance level, that will take care this issue once for all, right?

2. is setting the value for parallel_min_percent to 0 as the same as setting the value to 100? my logic is, if I set this to parallel_min_percent = 75 than oracle needs at least enough resource for 12 out of 16 of the DOP, right?

3. Say, if I set this value to 50, and oracle do have enough resource for DOP 8 out of 16, oracle will run in DOP of 8. What if afterwards, oracle have enough resource to run DOP of 12 out 16, will oracle adjust to that 12 DOP from 8 since parallel_automatic_tuning is set to TRUE?

Thanks for your help.
Libo Zhang
libo.zhang@reuters.com

Tom:

I have a query like below:

SELECT /*+ ordered  parallel_index(PT PT_TABLE_IX1,20,1)  */ 
Col1, col2, col3, col4
FROM TEMPORARY_TABLE tmp,  PARTITIONED_TABLE PT, LOOK_UP_TABLE1, LOOK_UP_TABLE2
where <Join condition>


There will be 60000*30 records in the TEMPORARY_TABLE which needs to be scanned through the entire PARTITIONED_TABLE.

The above parallelism works, but with some issues:

1. The explain plan shows the table 'PT' is accessed by INDEX ROWID
2. TEMPORARY TABLE is broadcast to all parallel processes and then each of them seems to be running the query.


I have two questions:

1.  The trace and explain plan show lot more consistent gets(logical reads). However I need a direct read (eliminate the buffered read) so that i can reduce the contention on the buffer pool. How is it possible to ensure that we can force to do a direct read rather than logical read? 

2. I tried to use ROWID(PT) hint but to no avail. Is it possible to force the buffered read? If so how? (Does the Table access through INDEX ROWID via index suggest that it is a physical index read than a logical read? In the trace file I do not see waits on direct read(its not the top wait), but rather huge waits on sequential reads(I want it the other way round...huges waits on direct read and less on sequential and scattered reads).
 

Please see the output of the below queries(I am running the current one with less DOP and the TEMPORARY TABLE with 99999 rows)


select p.server_name,
sql.sql_text
from v$px_process p, v$sql sql, v$session s
WHERE p.sid = s.sid
and p.serial# = s.serial#
and s.sql_address = sql.address
and s.sql_hash_value = sql.hash_value
/




SERV
----
SQL_TEXT
--------------------------------------------------------------------------------
P024
SELECT /*+ ordered  parallel_index(PT PT_TABLE_IX1,20,1)  */ 
Col1, col2, col3, col4
FROM TEMPORARY_TABLE tmp,  PARTITIONED_TABLE PT, LOOK_UP_TABLE1, LOOK_UP_TABLE2
where <Join condition>


SERV
----
SQL_TEXT
--------------------------------------------------------------------------------

P025
SELECT /*+ ordered  parallel_index(PT PT_TABLE_IX1,20,1)  */ 
Col1, col2, col3, col4
FROM TEMPORARY_TABLE tmp,  PARTITIONED_TABLE PT, LOOK_UP_TABLE1, LOOK_UP_TABLE2
where <Join condition>






SQL> SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
FROM v$pq_tqstat
ORDER BY dfo_number DESC, tq_id, server_type DESC, process;  2    3  

DFO_NUMBER      TQ_ID SERVER_TYP PROCESS      NUM_ROWS      BYTES
---------- ---------- ---------- ---------- ---------- ----------
         1          0 Producer   QC             199998    3598832
         1          0 Consumer   P024            99999    1799416
         1          0 Consumer   P025            99999    1799416
         1          1 Producer   P024             1103      45698
         1          1 Producer   P025              376      15598
         1          1 Consumer   QC               1479      61296

6 rows selected.


Thanks,




 

Tom:

Not sure whether you got a chance to look at my previous posting or it is something wrong with my understanding of parallel concepts, I would like to know about direct reads and how to achive them.

Thanks,

Thanks Tom.

Hi,

I'm having problems getting Oracle to use my parallel hints on complex queries. I thought I'd ask some general questions to see if I can figure this out on my own.

When I enable parallel at the table level, the optimizer will use a PQ plan. When I use a hint for the same table, same PQ level it will not. Assume that I am using the correct command, and that PQ would be benifical, do you know what could cause the difference?

I ran one of your tests above for table T and did select /*+ parallel(t) */ count(*) from t and it worked fine. So I don't know what the problem could be because

1)I know I have PQ enabled because the test worked.
2)I know PQ would be helpful in my complex query because at the table level, it will use PQ.

I'm hoping you can provide me with some ideas, some things to check for. Thanks a lot.

Thank you sir. A couple of quick follow up questions though.

1)I understand your index example, but what I don't understand with parallel is how could that effect results and cause that to be an invalid option? Your index example makes sense because Oracle couldn't get the correct answer by using that index, but with PQ, it's just more threads retrieving the same results right?

2)Can I just use select /*+ parallel */ from tbl t1, tbl2 t2... and let the optimizer choose what table would be best to use PQ or do we have to specify a table?

Thanks.

Hello Tom,

From the 9i DW Guide, Chapter on "Using Parallel Execution":

One of the parameters that impact 'Default Degree of Parallelism' is:

For parallel DML operations with global index maintenance, the minimum number of transaction free lists among all the global indexes to be updated. The minimum number of transaction free lists for a partitioned global index is the minimum number across all index partitions. This is a requirement to prevent self-deadlock.

What are the transaction free lists - are they ITL slots, or Free Lists (for free blocks)?

Thanks,
Naresh.

The quoted text from the DW Guide says the degree of parallelism is:

"For parallel DML operations with global index maintenance, the minimum number of transaction free lists among all the global indexes to be updated."

I suspect this should read minimum value of INITRANS across the global indexes, (i.e. interested transaction list not transaction free list). The point about deadlocks is that if you have INITRANS = N but N+1 parallel slaves all try to delete a row from the same index block, and the block is full, then the N+1th update cannot take place until the global transaction commits - but the global transaction won't commit until the N+1th slave has completed its update.

Possible documentation bug ? I think I checked this once, but a long time ago and I can't find the test case at present.

hi Tom,

I have a script like below to populate a table from another table, and it includes a rownum to generate a synthetic primary key.

alter table cnv_resource parallel 4;
alter table cnv_res2 parallel 4;
alter session enable parallel dml; -- probably no use since cnv_res2 is not partitioned

  1  insert /*+ PARALLEL (cnv_resource, 4) */
  2   into cnv_res2(RESOURCE_ID, RESOURCE_VALUE, RESOURCE_STATUS)
  3  select /*+ PARALLEL (cnv_resource, 4) */
  4   100000000 + rownum, RESOURCE_VALUE, RESOURCE_STATUS
  5* from cnv_resource
SQL> set timing on
SQL> /

5545961 rows created.

Elapsed: 00:00:42.68

Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=21648)
   1    0   COUNT
   2    1     TABLE ACCESS* (FULL) OF 'CNV_RESOURCE' (Cost=1 Card=82 B :Q165820
          ytes=21648)                                                  00

   2 PARALLEL_TO_SERIAL            SELECT /*+ NO_EXPAND ROWID(A1) */ A1."RESOUR
                                   CE_VALUE",A1."RESOURCE_STATUS" FROM

Repeated tests showed:
3.24 minutes without parallel
40 seconds with parallel
Second execution without parallel 2.04 minutes
Second execution with parallel 38 seconds
Third execution with parallel 42 seconds (this one was without APPEND to generate a plan)

The parallel was always faster, though I was expecting the rownum to serialize everything, including the scan.

QUESTIONs:

1) Is my interpretation of the plan below correct?

a) This part means that a parallel scan was done for the cnv_resource Table.

   2    1     TABLE ACCESS* (FULL) OF 'CNV_RESOURCE' (Cost=1 Card=82 Bytes=21648) :Q16582000
   2 PARALLEL_TO_SERIAL            SELECT /*+ NO_EXPAND ROWID(A1) */ A1."RESOURCE_VALUE",A1."RESOURCE_STATUS" FROM

b) This part means that the generation of the synthetic key with rownum and the insert itself was serial.

   0      INSERT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=21648)
   1    0   COUNT

So, the speed up seems to have been only due to parallel scan - it seems from this that the step of reading from Disk (or FS Buffers) into the program memory (PGA?) seems to be the major time consumer from this?


2) If I run only the select in parallel, it takes about 2 minutes. For the insert (with or without APPEND), it takes about 40 seconds. What could be the reson for the difference? Does it have to do with sending output the the screen? (I have "set autotrace trace").

Thanks,
Naresh
 

Following an email from Tom, I did a couple of quick tests on a 9.2.0.6 database with 8KB blocks, using a table with 256 partitions a couple of global indexes, setting parallel_max_servers = 128.

The maximum value I was allowed to set freelists to on the indexes was 99. When I tried to delete with a degree of parallel 128, I got an actual degree of 101. When I set freelists to 95, I got an actual degree of 105. It looks as if an 8KB segment header block has an implicit limit of 200 slots for freelists, and the degree is limited to 200 minus your setting of freelists (You get SEG freelists up to your setting in the block dump, then XCT freelists).

So it looks as if the documentation is correct - but perhaps only relevant in extreme cases.


As a further test (of my original assumption that the limit was set by initrans) I found that I could get a deadlock message by running with a degree of parallelism greater than the value of MAXTRANS, e.g. degree 6 when one of the indexes has MAXTRANS set to 4 resulted in:

ORA-12801: error signaled in parallel query server P003
ORA-12829: Deadlock - itls occupied by siblings at block 20491 of file 9

So even though I'm wrong (about INITRANS), I ought to be right. I would have thought that risks of ITL space problems are much more likely than risks of XCT freelist problems. Indexes and tables treat INITRANS very differently, which means an index block could be full even before its value of INITRANS is reached - which is why I would hope to base the critical test on INITRANS.

how can we verify that parallel in insert has been used or not.
will it show in explain plan.
can u give an example

Fail ?
why ?

the query will fail with:

ops$tkyte@ORA10GR2> select * from t;
select * from t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
 

Tom,

We have following settings in our database.
SQL> show parameter parallel

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

And we have inserting into a table - which is having 32 partitions and 1024 subpartitions with the following SQL(thru a overnight job)
INSERT /*+ APPEND */ into ... 

Will it be executing in parallel ?

We have come across a very strange scenario where we are hitting maximum DML Locks when we are running the above job, which is in turn firing the INSERT. I read on metalink that PDML on Partitions uses much more DML Locks and hence wanted to confirm if the above situation is a PDML. Unfortunately the job runs within seconds for us to monitor it, also it runs overnight,...

Thank you,
With Regards,
Shalini. 

We have a new system recently that has gone into production using parallel_automatic_tuning as recommended. We have the following setting (the server has 8 itanium CPUs).

SQL> show parameter parallel

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

We've been having some slowdowns and I ran a query (see below) to see what was waiting and got a response showing that one session had spawned some 60 parallel processes while all the others were running in serial (tables are set up with degree default). 

I wonder if you get diminishing returns from a large number of parallel processes handling one query like this. I saw in a response in this thread that you thought that a max of 16 parallel threads would be more appropriate for 8 cpus than 100. Our Oracle (i.e. from Oracle NZ) consultant said we should have a max of 160 (no of cpus x threads per cpu x 10), we currently have 80.

We are using resource manager to manage cpu usage to stop any loads occurring during the day from hogging the cpu and I'm inclined to also set a limit on the number of parallel processes per session to 8. Is this reasonable of should I just let Oracle do its thing? 

You said 16 seemed a better parallel_max_servers that 100 for an 8 cpu machine. Does 80 then sound too many?

SQL>   @whats_waiting.sql

TO_CHAR(SYSDATE,'D
------------------
11 JUL 06 08:59:12


PX_SER PX_STATUS      X_SID      P_SID OSUSER   CHILD_WAIT                                    PARENT_WAIT
------ --------- ---------- ---------- -------- --------------------------------------------- ---------------------------------------------
P000   IN USE            64         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P001   IN USE            49         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P002   IN USE            68         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P003   IN USE            59         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P004   IN USE            70         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P005   IN USE            58         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P006   IN USE            37         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P007   IN USE            69         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P008   IN USE            87         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P009   IN USE            79         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P010   IN USE            85         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P011   IN USE            62         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P012   IN USE            78         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P013   IN USE            90         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P014   IN USE            86         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P015   IN USE            53         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P016   IN USE            46         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P017   IN USE            39         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P018   IN USE           105         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P019   IN USE            57         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P020   IN USE            81         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P021   IN USE           116         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P022   IN USE            47         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P023   IN USE            94         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P024   IN USE           117         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P025   IN USE            77         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P026   IN USE           100         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P027   IN USE           114         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P028   IN USE            50         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P029   IN USE            96         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P030   IN USE            80         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P031   IN USE            82         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P032   IN USE            61         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P033   IN USE            99         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P034   IN USE           119         20 edrp     resmgr:waiting in end wait                    resmgr:waiting in end wait
P035   IN USE            63         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P036   IN USE            93         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P037   IN USE            83         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P038   IN USE            65         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P039   IN USE            60         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P040   IN USE            45         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P041   IN USE            44         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P042   IN USE            36         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P043   IN USE            88         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P044   IN USE            31         20 edrp     PX Deq: Table Q Normal                        resmgr:waiting in end wait
P045   IN USE           106         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P046   IN USE           109         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P047   IN USE            34         20 edrp     direct path read                              resmgr:waiting in end wait
P048   IN USE            55         20 edrp     direct path read                              resmgr:waiting in end wait
P049   IN USE            67         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P050   IN USE            41         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P051   IN USE            28         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P052   IN USE            89         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P053   IN USE           111         20 edrp     direct path read                              resmgr:waiting in end wait
P054   IN USE            71         20 edrp     direct path read                              resmgr:waiting in end wait
P055   IN USE            24         20 edrp     direct path read                              resmgr:waiting in end wait
P056   IN USE            54         20 edrp     direct path read                              resmgr:waiting in end wait
P057   IN USE           107         20 edrp     direct path read                              resmgr:waiting in end wait
P058   IN USE            43         20 edrp     PX Deq: Execution Msg                         resmgr:waiting in end wait
P059   IN USE           103         20 edrp     direct path read                              resmgr:waiting in end wait
Serial                   20          0 edrp     resmgr:waiting in end wait
Serial                   21          0 ollivs   resmgr:waiting in end wait
Serial                   28          0 edrp     resmgr:waiting in end wait
Serial                   31          0 edrp     resmgr:waiting in end wait
Serial                   34          0 edrp     resmgr:waiting in check2
Serial                   36          0 edrp     resmgr:waiting in end wait
Serial                   38          0 edrp     resmgr:waiting in check2
Serial                   41          0 edrp     resmgr:waiting in check2
Serial                   43          0 edrp     resmgr:waiting in end wait
Serial                   54          0 edrp     resmgr:waiting in check2
Serial                   55          0 edrp     resmgr:waiting in check2
Serial                   61          0 edrp     resmgr:waiting in end wait
Serial                   62          0 edrp     resmgr:waiting in end wait
Serial                   65          0 edrp     resmgr:waiting in end wait
Serial                   66          0 edrp     resmgr:waiting in end wait
Serial                   67          0 edrp     resmgr:waiting in check2
Serial                   71          0 edrp     resmgr:waiting in check2
Serial                   73          0 edrp     resmgr:wait in actses run
Serial                   82          0 edrp     resmgr:waiting in check2
Serial                   86          0 edrp     resmgr:waiting in end wait
Serial                   88          0 edrp     resmgr:waiting in end wait
Serial                   89          0 edrp     resmgr:waiting in end wait
Serial                  102          0 edrp     resmgr:waiting in end wait
Serial                  103          0 edrp     resmgr:waiting in check2
Serial                  106          0 edrp     resmgr:waiting in check2
Serial                  107          0 edrp     resmgr:waiting in check2
Serial                  109          0 edrp     resmgr:waiting in check2
Serial                  111          0 edrp     resmgr:waiting in end wait

88 rows selected.


TO_CHAR(SYSDATE,'D
------------------
11 JUL 06 09:01:45

select to_char(sysdate,'DD MON YY HH24:MI:SS') from dual;
set lines 155
set pages 1000
column OSUSER format a8
column CHILD_WAIT format a45
column PARENT_WAIT format a45
        select 'Serial' as px_server
        , null as px_status
        , w.sid as x_sid
        , 0 as p_sid
             , v.osuser
             , w.event as child_wait
        , ' ' as parent_wait
        from  v$lock l
            , v$session v
            , v$session_wait w
        where v.sid = w.sid(+)
   and   v.sid = l.sid(+)
        and   nvl(l.type,'PS') = 'PS'
   and   (v.status = 'ACTIVE')
   and   w.event not like 'PX%'
   and   osuser !='oracle'
UNION
        select x.server_name as px_server
             , x.status as px_status
             , x.sid as x_sid
             , w2.sid as p_sid
             , v.osuser
             , w1.event as child_wait
             , w2.event as parent_wait
        from  v$px_process x
            , v$lock l
            , v$session v
            , v$session_wait w1
            , v$session_wait w2
        where x.sid <> l.sid(+)
        and   to_number (substr(x.server_name,2)) = l.id2(+)
        and   x.sid = w1.sid(+)
        and   l.sid = w2.sid(+)
        and   x.sid = v.sid(+)
        and   nvl(l.type,'PS') = 'PS'
        order by 1,2
/
select to_char(sysdate,'DD MON YY HH24:MI:SS') from dual;

 

The reason we set it to 80 was that our Oracle consultant (i.e. employee of Oracle) set it to 80 on our dev box (which has 4 cpus) as he said it should be 10 x no of cpus x threads per cpu.

When we created prod, the parameters were based on those on dev and I decided to keep it at 80 to begin with rather than the recommended 160 as it seemed a little high to me.

Hi Tom,
We have HP-UX machine with 8 cpu.
I want to create an index using parallel option on
a table with 60 milion records.

In one session i run:
CREATE INDEX parallel_test ON t(update_date)
LOGGING
TABLESPACE INDEX05_TS
PCTFREE 5
INITRANS 5
MAXTRANS 255
STORAGE (
INITIAL 50M
NEXT 50M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 5
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
PARALLEL 4;

In a second session i run:
sql > select sid,serial#,qcsid,qcserial#,degree
2 from v$px_session;

no rows selected

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 0
parallel_instance_group
parallel_execution_message_siz 2152

(The database version is 8.1.7.4 and we are using optimizing_mode = rule)

I expect to get few rows back from v$px_session, but it didnt return any row.
Can you please explain why ?

Regards

It seems that some responders don't realize that this error after parallel dml is only related to the current transaction. A commit (or rollback) will allow querying.

parallel dml might not be something you want to do if you need to verify reults before commiting.

Respected Mr. Tom,

I read threads of your site just like Exam Paper and truly I feel I know nothing about Oracle. On reading this current thread, please let me ask you TWO questions that seem absolute enthralling to me. On August 19, 2005 under “integrity constraints with append hint” to this thread, your answer was as follows after editing for brevity.

Case – 1
----------

ops$tkyte@ORA10G> create table t ( x int check (x > 0) );
Table created.
ops$tkyte@ORA10G> insert /*+ APPEND */ into t select 1 from dual;
1 row created.

ops$tkyte@ORA10G> select * from t;
select * from t
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

that shows we did a direct path, now:

Question 1:
**********
Did we really make it parallel by “alter session enable parallel dml;”?


Case – 2
----------

ops$tkyte@ORA10G> create table t2 ( x int primary key );
Table created.
ops$tkyte@ORA10G> insert into t2 values ( 1 );
1 row created.
ops$tkyte@ORA10G> alter table t add constraint t_fk foreign key(x) references
t2;

ops$tkyte@ORA10G> insert /*+ APPEND */ into t select 1 from dual;

1 row created.

ops$tkyte@ORA10G> select * from t;

X
----------
1
1
We did not direct path because of the foreign key.

Question 2
*********
Why do you say that we did not direct path because of the foreign key when we used the hints /*+ APPEND */?

Respected Mr. Tom,

Thanks a lot for pointing out a cut and dried restriction on PDML in the Oracle Document. How do you remember all these things? You are really GREAT.

Tom

I've got an insert statement that I can't get to perform parallel DML. I'm inserting into a parallel (degree 8) table. The table is totally "flat", no constraints or indexes.

ALTER SESSION ENABLE PARALLEL DML;

ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;

ALTER SESSION SET HASH_AREA_SIZE = 10000000;

INSERT
/*+ append
parallel (bl,8) */
INTO
brp_lines bl
SELECT
/*+ parallel (bt1,8)
parallel (bt2,8) */
<stuff>
FROM
big_table1 bt1,
big_table2 bt2
WHERE
<joins>

Run time 4 minutes

Although 8 parallel slaves are set up at run time to perform the insert, none of them perform any IO, all of which is done by the master process

When I run a CTAS using the same sql ie

CREATE TABLE brp_lines
PARALLEL (DEGREE 8)
TABLESPACE brp_lines
NOLOGGING
AS
SELECT
/*+ parallel (bt1,8)
parallel (bt2,8) */
<stuff>
FROM
big_table1 bt1,
big_table2 bt2
WHERE
<joins>

Run time 40 seconds

It performs exactly as I expect (2 big parallel selects, a big hash join and 8 slaves doing the write)

My question is, are there any obvious settings that would so dramatically influence the execution plan of the DML ITAS.

The reason I have tried to adopt the ITAS approach is because in real life BT1 and BT2 are very large tables (600m and 800m rows) partitioned on Year/Month. When I ran the CTAS as a serial operation they performed a nice partitionwise join, but as soon as I tried to apply parallel select to the partitioned tables I ended up with full table scans and a massive resulting hash join. I was trying to use ITAS and loop around doing one insert statement per year/month to emulate the partitionwise nature.

I have a full example if required but I thought maybe there was something stupidly obvious that I'm missing

Thanks Tom

Hi Tom,

from metalink Note:50416.1

This style(direct path write) of write request is typically used for:

Direct load operations (eg: Create Table as Select (CTAS) may use this)
Parallel DML operations

does all types of parallel DML causing direct load , i understand that the default mode for insert when using in parallel is to do direct load ( correct me if i am wrong please ) , but what about update command when used in parallel , does it also do direct load ?

thanks

Hi Tom,

Parallel CTAS does not run in parallelism.

    create table wsee_reformat2_tb
    tablespace work2_ts
    parallel (degree 32)
    nologging
    pctfree 0
    as
    (select /*+ use_hash(a,b,c) parallel(a,32) parallel(b,8) parallel(c, 8) */
       a.src_id,
       a.dataprep_seqno,
       a.abt_clink,
       a.abt_alink,
       a.abt_biz_link,
       a.BUREAU_ID,
       a.rms_INDIV_ID,
       lpad(b.biz_id,10,'0') biz_id ,
       lpad(c.hh_id,16,'0') hh_id,
       a.rms_seqno
    from rms_admin.reformat1_tb a,
         rms_admin.xref_biz_tb b,
         rms_admin.xref_hh_tb c
    where a.abt_biz_link = b.abt_biz_link(+)
     and   a.abt_alink = c.abt_alink(+))


SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'))


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------
| Id  | Operation              |  Name         | Rows  | Bytes |TempSpc| Cost  |  TQ    |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------
|   0 | CREATE TABLE STATEMENT |               |   773M|   108G|       |   147K|        |      |            |
|   1 |  LOAD AS SELECT        |               |       |       |       |       | 25,04  | P->S | QC (RAND)  |
|*  2 |   HASH JOIN OUTER      |               |   773M|   108G|  3111M|   147K| 25,04  | PCWP |            |
|*  3 |    HASH JOIN OUTER     |               |   773M|    88G|  2466M| 90389 | 25,02  | P->P | HASH       |
|   4 |     TABLE ACCESS FULL  | REFORMAT1_TB  |   773M|    68G|       |  8690 | 25,00  | P->P | HASH       |
|   5 |     TABLE ACCESS FULL  | XREF_HH_TB    |   277M|  7413M|       | 24934 | 25,01  | P->P | HASH       |
|   6 |    TABLE ACCESS FULL   | XREF_BIZ_TB   |    75M|  2005M|       | 13763 | 25,03  | P->P | HASH       |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------

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

   2 - access("A"."ABT_BIZ_LINK"="B"."ABT_BIZ_LINK"(+))
   3 - access("A"."ABT_ALINK"="C"."ABT_ALINK"(+))

PX Slave SQL Information (identified by operation id):
------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 - CREATE TABLE :Q41325004 TABLESPACE "WORK2_TS" PCTFREE 0 NOLOGGING AS SELECT
              C0,C1,C2,C3,C4,C5,C6,C7,C8,C9 FROM (SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */ A1.C3 C0,A1.C4 C1,A1.C5
              C2,A1.C1 C3,A1.C0 C4,A1.C8 C5,A1.C9 C6,LPAD(A2.C1,10,'0') C7,LPAD(A1.C7,16,'0') C8,A1.C6 C9 FROM :Q41325002
              A1,:Q41325003 A2 WHERE A1.C0=A2.C0(+))

   3 - SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */ A1.C5 C0,A1.C0 C1,A2.C0 C2,A1.C1 C3,A1.C2 C4,A1.C3
              C5,A1.C4 C6,A2.C1 C7,A1.C6 C8,A1.C7 C9 FROM :Q41325000 A1,:Q41325001 A2 WHERE A1.C0=A2.C0(+)

   4 - SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ABT_ALINK" C0,A1."SRC_ID" C1,A1."DATAPREP_SEQNO"
              C2,A1."ABT_CLINK" C3,A1."RMS_SEQNO" C4,A1."ABT_BIZ_LINK" C5,A1."BUREAU_ID" C6,A1."RMS_INDIV_ID" C7 FROM
              "RMS_ADMIN"."REFORMAT1_TB" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)  A1

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   5 - SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ABT_ALINK" C0,A1."HH_ID" C1 FROM "RMS_ADMIN"."XREF_HH_TB"
              PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)  A1

   6 - SELECT /*+ NO_EXPAND ROWID(A1) */ A1."ABT_BIZ_LINK" C0,A1."BIZ_ID" C1 FROM "RMS_ADMIN"."XREF_BIZ_TB"
              PX_GRANULE(0, BLOCK_RANGE, DYNAMIC)  A1


Note: cpu costing is off


Please correct me if I am doing wrong.

Thanks,
Ram 

Hi Tom,

When the CTAS run, each slave builds its own TEMPORARY data segment. Upon completion, one TEMPORARY data segment is chosen as the segment for all other TEMPORARY segments to merge "INTO". The merged TEMPORARY data segment are
write to the base segment. During this step, should it be a parallel writes depending on the PARALLEL clause in the CTAS. I only see a single slave that is writing the merged TEMPORARY segment to the actual base segment. Please could you verifly if this should be the case?

Thank you,
Ram

I need to select a table that has 1.1 billion rows,
inserts back to the same table with just modifying one of the Key column value, only for the records that are exist in another small table.

Basically there is a small driver table with 1 million records in total and for this 1 million records there are 100 million records exist in the BIG table. Now I will have to duplicate those 100 million records with just modifying one of the key column value with the small table
column value.

In the following script, the GROUP_NR column in the BIG table is replaced with the NEW_GROUP_NR value from the DRIVER TABLE.

DECLARE
CURSOR c1 IS
SELECT ac_nr, old_group_nr, new_group_nr
FROM driver_table;

drv_count NUMBER := 0;

BEGIN
FOR rec IN c1 LOOP

drv_count := drv_count+1;

INSERT INTO big_table
SELECT AC_NR,
rec.new_group_nr,
STATUS_CD,
METHOD_TYPE,
ANYT_CODE,
ZONE,
ZONE_CODE,
UNIT_TYPE,
PORT_TYPE,
PCPPA,
COUNTRY,
CURRENCY,
PZTYPECD
FROM big_table
WHERE AC_NR = rec.ac_nr
AND GROUP_NR = rec.old_group_nr;

IF MOD(drv_count,10000) = 0 THEN
COMMIT;
END IF;

END LOOP;
COMMIT;
END;
/

The query uses the INDEX RANGE scan on the BIG TABLE.

We have tested this process with just 5000 records from
the driver table which processed 80000 records in the
BIG_TABLE and inserted 80000 records into the BIG_TABLE.
Based on the timing, the process will take more than 12 hours to process 1 million driver records and to insert 100 million records into the BIG table.


Can you please suggest us anything else to improve this process.

The following is the table structure and the index information.

DESC big_table
------------
AC_NR NOT NULL CHAR(10)
GROUP_NR NOT NULL CHAR(7)
STATUS_CD NOT NULL CHAR(1)
METHOD_TYPE NOT NULL CHAR(2)
ANYT_CODE NOT NULL NUMBER(3)
ZONE NOT NULL CHAR(6)
ZONE_CODE NOT NULL CHAR(2)
UNIT_TYPE NOT NULL CHAR(2)
PORT_TYPE NOT NULL CHAR(1)
PCPPA NOT NULL NUMBER(18,6)
COUNTRY NOT NULL CHAR(2)
CURRENCY NOT NULL CHAR(3)
PZTYPECD CHAR(2)

INDEX
---------
AC_NR
GROUP_NR
STATUS_CD
METHOD_TYPE
ANYT_CODE
ZONE

The table has 16 PARTITIONS (RANGE PARTITION)

The table is not created with PARALLEL.

SELECT degree FROM dba_tables
WHERE table_name = 'BIG_TABLE';

DEGREE
----------
1

But the index is created with 2 degree of parallelism.

SELECT degree FROM dba_indexes
WHERE index_name = 'BIG_TAB_IND';

DEGREE
----------
2

At the instance level, the following are the PARALLEL parameters setup.

SQL > SHOW PARAMETER PARALLEL

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 2152
parallel_instance_group string
parallel_max_servers integer 5
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

The server is the HP-UX server (SUPERDOME server) with 20 CPUs.

Thanks
raj..

Hello Tom
I tried to do the simulation given by you in you new book on parallel enabled function as shown in you example there are several process which got spawn to do the necessary processing
but when I run the same I always get only two child process there there anything which requires to be adjusted before the simulation

select session_id,count(*) from t2 group by session_id;

SESSION_ID COUNT(*)
---------- ----------
24 14055
50 14017

Tom,
We have parallel_adaptive_multi_user=true in our 9.2.0.6 database with parallel_max_servers=12. If my table has degree=1 (I didn't specify degree for the table), anytime I query with the /*+ parallel (t) */ hint, the engine tries to use the largest DOP possible. Whenever these queries run, they take up all parallel servers, and nothing is left for other processes.

Of course, we can explicitly declare the DOP by:
1) alter table degree=n
2) /*+ parallel (t, n) */

I want to avoid this and just have parallel_automatic_tuning and parallel_adaptive_multi_user, and allow the Oracle engine to figure out how to make the queries "play nice" without my intervention. How can we configure our database to do that?

I saw an explanation on oracle-l:
http://www.freelists.org/archives/oracle-l/01-2007/msg00899.html

I tried testing it. Here's trying to be more specific:
1) I have a table that doesn't have DOP defined
2) I have max servers = 12
3) I have adaptive multi user = true

When I run
select /*+ parallel (t) */ count(*)
from t;

Oracle allocates all 12 parallel servers no matter the system load. We could have 20 sessions logged in and running queries, but parallel execution doesn't try to be fair. It will allocate all servers to my one query.

Maybe this behavior is different in 10g?

Sorry I couldn't come up with a test case for you. It's hard to do since there are parallel processes involved, and I'd have to build a whole simulation of multiple processes running.

Hi Tom,

SUBJECT:Parallel Slaves Session is Active while Master Session is Inactive.

In our application, we have parallel execution turned on and setting in init.ora is as below

Parallel_min_servers is 20
Parallel_max_servers is 40

The application is an OLAP application where in users run Segments and Campaigns.
What we have observed is when a user runs a Segment, which fires queries against the database, paralled slaves
are spawned for the query.

However, when the user cancels the segment through the Application UI, the master session becomes INACTIVE
while the session status of the parallel SLAVES is still ACTIVE but actually do nothing.

On executing query below for parallel slaves status

select
x.server_name
, x.status as x_status
, x.pid as x_pid
, x.sid as x_sid
, w2.sid as p_sid
, v.osuser
, v.schemaname
, w1.event as child_wait
, w2.event as parent_wait
from v$px_process x
, v$lock l
, v$session v
, v$session_wait w1
, v$session_wait w2
where x.sid <> l.sid(+)
and to_number (substr(x.server_name,2)) = l.id2(+)
and x.sid = w1.sid(+)
and l.sid = w2.sid(+)
and x.sid = v.sid(+)
and nvl(l.type,'PS') = 'PS'
and x.server_name in ('P017','P020','P021')
order by 1,2

I get the result as

SERVER_NAME X_STATUS X_PID X_SID P_SID OSUSER SCHEMANAME CHILD_WAIT PARENT_WAIT
P017 IN USE 30 53 25 n071946 SIEBEL PX Deq Credit: send blkd SQL*Net message from client
P020 IN USE 41 22 25 n071946 SIEBEL PX Deq Credit: send blkd SQL*Net message from client
P021 IN USE 43 13 25 n071946 SIEBEL PX Deq Credit: send blkd SQL*Net message from client

which shows that Parent Wait is basically an idle event.

On executing query "select * from v$session where sid=25" to get Master session details, i get below result

SADDR SID SERIAL# AUDSID PADDR USER# USERNAME COMMAND OWNERID TADDR LOCKWAIT
00000408BF633008 25 17 249619 00000408C0650320 25 SIEBEL 3 2147483644 null null

STATUS SERVER SCHEMA# SCHEMANAME OSUSER PROCESS MACHINE TERMINAL PROGRAM TYPE
INACTIVE DEDICATED 25 SIEBEL n071946 7432:7032 ACCOUNT-01\WSAPP1361 WSAPP1361 nqsserver.exe

When we kill the SID session of the slaves, no work is lost i.e. end users dont experience any problem or
get an error message saying that session has been killed.

Questions I have is

1) Why aren't the Parallel Slave sessions becoming INACTIVE and actually be available rather "IN USE" status i.e. Parallel
slaves should be in "AVAILABLE" status so that another query can use these slaves for processing.
2) Is there any setting to be done to solve this?

Thanks,
Rajesh

Tom,
I have a question regarding PARALLEL_ENABLE option in procedures/packages.

I currently have a package PKG_A with a procedure 'TEST'.
When a client calls this procedure, I am trying to do some kind of logging and for this logic i'm calling another procedure [Pkg_b.GetInfo] with in this 'TEST' procedure.
I want both these calls execute parallelly [Pkg_a.TEST should continue executing while PKG_B.getInfo does some other things]

Eg:

      Pkg_A.TEST->
               Begin
                  pkg_B.getInfo(param1,parm2);
                  
                  check_some_other_info
                  --and lots of other logic involved.

               End;

      

My questions is If I declare Pkg_B.getInfo procedure with a PARALLEL_ENABLE option,
Will it run parallelly when PKG_A.TEST is called or will there be some problems?

eg:

          Create or replace Package Pkg_B
          as
             Procedure Getinfo(param1 in Integer,
                               Param1 in integer)
             PARALLEL_ENABLE;
          end;

          Create or replace Package Pkg_B
          as
             Procedure Getinfo(param1 in Integer,
                               Param1 in integer)
             PARALLEL_ENABLE
             is

              Begin
                  Test(param1*param2);
              End;
          end;
 
        

Also, Am I Using Correct syntax? [Do I have to use Parallel Enable in both Spec and body?]

Any help is greatly appreciated.
Thanks,

Thanks for responding Tom. The reason why I wanted parellel processing was, My logging process is querying some Data dictionary objects and I don't want users to wait till it finishes logging[Thinking Querying data dictionary objects might effect performance] before it goes for actual processing.

I read in some places using dbms_job we can enable parallel processing. But I really do not want to use dbms_job for this scenario..i am not rescheduling or anything that sort..

If Advanced Queues is the only way, can you show me an example of how to approach for my problem using AQ's?

Thanks a bunch

Tom, Sure I can do that ..I can try and see if that's a problem at all. But that needs lot of time spent on testing and trying to simulate many users accessing this procedure and find out how long it takes..
I was thinking if I can find a solution even if there is no problem. With that,I'm saving lot of time for myself ..
That's the reason.

But what if there is an effect on performance? can you suggest any other approach to it from the top of your head?

Thanks,

<blockquote>
1) Why aren't the Parallel Slave sessions becoming INACTIVE and actually be available rather "IN USE" status i.e. parallel slaves should be in "AVAILABLE" status so that another query can use these slaves for processing.
</blockquote>

I have a PQ that shows the parent as inactive but the 5 slaves still show as STATUS = "ACTIVE". Are they actually active? i Didn't see any other indication that they were.

Tom,

If you want your inserts,updates and deletes to be parallelized, is enabling PDML at session level mandatory in 10g also ? Because without enabling PDML at the session level I see the updates are parallelized when given a hint.
the version of the database is 10.1.0.4

Following is a small experiment to show that.

SQL> connect abc
Connected.
SQL> select sid from v$mystat where rownum = 1;

       SID                                                                      
----------                                                                      
       300                                                                      

SQL> select pdml_enabled from v$session where sid = 300;

PDM                                                                             
---                                                                             
NO                                                                              



SQL> create table myobj as select * from all_objects where 1 = 0;

Table created.

SQL> select count(*) from myobj;

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

SQL> exec dbms_stats.set_table_stats(user,'MYOBJ',numrows=>2000000,numblks=>20000)

PL/SQL procedure successfully completed.

SQL> set autotrace on explain statistics



SQL> update /*+ parallel(myobj) */ myobj
  2  set object_id=mod(object_id,10);

0 rows updated.


Execution Plan
----------------------------------------------------------                      
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=108 Card=2000000 Byt          
          es=26000000)                                                          
                                                                                
   1    0   UPDATE OF 'MYOBJ'                                                   
   2    1     PX COORDINATOR                                                    
   3    2       PX SEND* (QC (RANDOM)) OF ':TQ10000' (Cost=108 Card=20 :Q1000   
          00000 Bytes=26000000)                                                 
                                                                                
   4    3         PX BLOCK* (ITERATOR) (Cost=108 Card=2000000 Bytes=26 :Q1000   
          000000)                                                               
                                                                                
   5    4           TABLE ACCESS* (FULL) OF 'MYOBJ' (TABLE) (Cost=108  :Q1000   
          Card=2000000 Bytes=26000000)                                          
                                                                                


   3 PARALLEL_TO_SERIAL                                                         
   4 PARALLEL_COMBINED_WITH_CHILD                                               
   5 PARALLEL_COMBINED_WITH_PARENT                                              


Statistics
----------------------------------------------------------                      
         45  recursive calls                                                    
          3  db block gets                                                      
         10  consistent gets                                                    
          0  physical reads                                                     
        636  redo size                                                          
        809  bytes sent via SQL*Net to client                                   
        721  bytes received via SQL*Net from client                             
          3  SQL*Net roundtrips to/from client                                  
          2  sorts (memory)                                                     
          0  sorts (disk)                                                       
          0  rows processed                                                     

SQL> show parameter parallel

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
fast_start_parallel_rollback         string      LOW                            
parallel_adaptive_multi_user         boolean     TRUE                           
parallel_automatic_tuning            boolean     FALSE                          
parallel_execution_message_size      integer     65536                          
parallel_instance_group              string                                     
parallel_max_servers                 integer     8                              
parallel_min_percent                 integer     0                              
parallel_min_servers                 integer     4                              
parallel_server                      boolean     FALSE                          
parallel_server_instances            integer     1                              
parallel_threads_per_cpu             integer     2                              

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
recovery_parallelism                 integer     0   
                           
SQL> select pdml_enabled from v$session where sid=300;

PDM                                                                             
---                                                                             
NO                                                                              


                                                                          

SQL> spool off

Can you please comment on this.

Thanks & regards

ops$tkyte%ORA10GR2> create table t as select * from all_objects where rownum <= 1;

Table created.

ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000, numblks => 1000000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select pdml_enabled from v$session where sid = (select sid from v$mystat where rownum=1);

PDM
---
NO

ops$tkyte%ORA10GR2> delete from plan_table;

0 rows deleted.

ops$tkyte%ORA10GR2> explain plan for update /*+ parallel(t) */ t set object_id = object_id+1;

Explained.

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1378397380

-------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |  1000K|    12M| 42448   (1)| 00:03:2
|   1 |  UPDATE               | T        |       |       |            |
|   2 |   PX COORDINATOR      |          |       |       |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 |  1000K|    12M| 42448   (1)| 00:03:2
|   4 |     PX BLOCK ITERATOR |          |  1000K|    12M| 42448   (1)| 00:03:2
|   5 |      TABLE ACCESS FULL| T        |  1000K|    12M| 42448   (1)| 00:03:2
-------------------------------------------------------------------------------

12 rows selected.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> alter session enable parallel dml;

Session altered.

ops$tkyte%ORA10GR2> delete from plan_table;

6 rows deleted.

ops$tkyte%ORA10GR2> explain plan for update /*+ parallel(t) */ t set object_id = object_id+1;

Explained.

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2037160838

-------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |  1000K|    12M| 42448   (1)| 00:03:2
|   1 |  PX COORDINATOR       |          |       |       |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 |  1000K|    12M| 42448   (1)| 00:03:2
|   3 |    UPDATE             | T        |       |       |            |
|   4 |     PX BLOCK ITERATOR |          |  1000K|    12M| 42448   (1)| 00:03:2
|   5 |      TABLE ACCESS FULL| T        |  1000K|    12M| 42448   (1)| 00:03:2
-------------------------------------------------------------------------------

12 rows selected.

ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> select pdml_enabled from v$session where sid = (select sid from v$mystat where rownum=1);

PDM
---
NO

ops$tkyte%ORA10GR2> update /*+ parallel(t) */ t set object_id = object_id+1;

1 row updated.

ops$tkyte%ORA10GR2> select count(*) from t;

  COUNT(*)
----------
         1

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> alter session enable parallel dml;

Session altered.

ops$tkyte%ORA10GR2> select pdml_enabled from v$session where sid = (select sid from v$mystat where rownum=1);

PDM
---
YES

ops$tkyte%ORA10GR2> update /*+ parallel(t) */ t set object_id = object_id+1;

1 row updated.

ops$tkyte%ORA10GR2> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Tom,

Oracle Datawarehousing guide has following statement in chapter 24.
quote:
"Note that the parallel execution coordinator and the parallel execution servers can only service one statement at a time. A parallel execution coordinator cannot coordinate, for example, a parallel query and a parallel DML statement at the same time."

What does that mean?

For example if we have insert into t select * from t2, does it mean that either only select portion or insert portion will be parallelized and not both ? Confused here.

Please help

Thanks & regards

From July 11, 2006 :
"if you have 8 cpus, anything over parallel 16 probably does not make sense.
why 80???? what drove you to that really high number on so few cpus? "

I found this paper in metalink https://metalink.oracle.com/metalink/plsql/docs/Upgrade_Companion.pdf page 18:
"the default value of parallel_max_server has changed in oracle database 10g:
cpu_count*parallel_threads_per_cpu*(2 ig pga>0; otherwise 1) * 5
--------------------------------------------------------8*2*2*5=160
In a data warehouse database are you still think i should stick with parallel_max_server=16 and not 160 as
oracle suggested ?
Thank You.

Should parallel_min_servers = parallel_max_servers*parallel_min_percent/100

If so what is the impact of the following

parallel_max_servers 128
parallel_min_percent 10
parallel_min_servers 8

Tom, what do you mean by

"should" - if they always should, it would be impossible to do otherwise

I need to move 500GB of data from 9.2 db in a Aix server to a 11.1.0.6.0 db in a Solaris 5.10. I want to do it in a parallel manner to speed things up. I will do it in the 11g server via a connect string to the 9.2 db.
Cant it be done? How to do it?

Thank you for your feedback.
You mention in this website: "sort area size will affect index creation, you might want it a larger then 256k "

But I ead the following: These parameters (hash_area_size and sort_areaa_size) should not be set for releases 9iR2 or higher as the auto memory manager is used for 9iR2 and 10gR1 based configurations.
So I have not configured this for the import or for the index creation script. The index nologging and parallel as you suggested.
Will run many index script at the same time, for different indexes.

Thank you for your feedback.
You mention in this website: "sort area size will affect index creation, you might want it a larger then 256k "

But I ead the following: These parameters (hash_area_size and sort_areaa_size) should not be set for releases 9iR2 or higher as the auto memory manager is used for 9iR2 and 10gR1 based configurations.
So I have not configured this for the import or for the index creation script. The index nologging and parallel as you suggested.
Will run many index script at the same time, for different indexes.

Which one to use in the import that I will submitted in parallel by myself, buffer of recordlength. I have modified the parameters in the databae to make it bigger during the import.

Is it true that for import i can improve the speed by "augmenting" the size of the log_checkpoint_interval to a bigger size than the redo size? (Os solaris 512) redo log size 600mb , thinking of changing log_chekpoint_interval to 1433600.

Thank you for your feedback.
Answering your question of "what parameters have you modified in the database for import"?
I have modified this parameters in the init.ora.
I 'am not sure about this one: _serial_direct_read=TRUE, and also not sure about the "parallel" parameters or the fast* parameter.
After doing the import without indexes or constraints I will create the indexes first and then the constraints enabling them after creating the index parallel.
The tables where altered before theh export to do it "parallel".
db_file_multiblock_read_count=128
_serial_direct_read=TRUE
db_cache_size=1073741824
large_pool_size=524288000
shared_pool_size=800M
log_buffer=524288000
pga_aggregate_target=1024M
WORKAREA_SIZE_POLICY=AUTO
SGA_TARGET=4096M
MEMORY_TARGET=6144M
MEMORY_MAX_TARGET=6144M
#LOG_CHECKPOINT_INTERVAL=1433600---->removed because
# commit=n as you said
##########################################
# Parallelism
##########################################
parallel_min_percent=0
# parallel_server=false
# parallel_server_instances=1
parallel_min_servers=2
parallel_max_servers=16
parallel_threads_per_cpu=2
parallel_execution_message_size=4096
parallel_adaptive_multi_user=TRUE
# parallel_automatic_tuning=TRUE
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300

Also I would like to be sure if it is ok to use both parameters for the import.

Thank you Tom, for the feedback, I will stay away from the underscore parameters and will comment this on the init.ora.
Really appreciate your help.

Bye.

The following parameters for parallelism are set in the init.ora:

parallel_min_percent=0
# parallel_server=false
# parallel_server_instances=1
parallel_min_servers=2
parallel_max_servers=16
parallel_threads_per_cpu=2
parallel_execution_message_size=4096
parallel_adaptive_multi_user=TRUE
# parallel_automatic_tuning=TRUE

I modified the 4 scripts with 10 creation of indexes, so that the creation of the index be PARALLEL and NOLOGGING.

When I saw the running using a third party utility I saw that only once query was using "PARALLEILSM" using all 16 parallel.
Each of the 4 scripts started with "Alter session force parallel DDL;" before the creation of the inexes.

I don't know what I did wrong. First, don't know how to set the parallel parametrs in the init.ora so to take the "defaults", did I put more parameters than necessary. If afirmative please tell me what parameters.
Also don't know why only one query is "eating' all the parallel process and the other two don't.

8 cpu's 4 gb of RAM - Oracle version 11g

How to set the parallelism* parameters?

There is no automatic_tuning for parallelism, what it te substitute parameter if there is one?

If version 1g I don't have to set anything what about version 9 and 10?
What parameters do I have to set as to implement parallelism in the database?

What about the altr session force parallel query, dml and ddl? Is this command can be use in 9g and 10g ?

Hi Tom,

In followup dated 18 February, 2008,among others, you have shown that use of PARALLEL hint if PDML is in enabled status results in PDML. However, in "Restrictions on Parallel DML" section in Chapter 21 (Using Parallel Execution)in Oracle 9i Data Warehousing Guide Release 2(9.2), it has been specified that PDML cannot happen on a non-partitioned table if it has bitmap index. 
So, I did a test:

SQL> select * from v$version ;

BANNER                                                                          
----------------------------------------------------------------                
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production                
PL/SQL Release 9.2.0.8.0 - Production                                           
CORE 9.2.0.8.0 Production                                                       
TNS for HPUX: Version 9.2.0.8.0 - Production                                    
NLSRTL Version 9.2.0.8.0 - Production       

SQL> create table  table_test
  2  as     select object_id   ,
  3                mod( object_id, 5) mod_value
  4         from   all_objects ;

Table created.

SQL> create bitmap index table_test_b1
  2         on           table_test(mod_value) ;

Index created.

SQL> desc table_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                 NOT NULL NUMBER
 MOD_VALUE                                          NUMBER

SQL> select sid,serial#, program, pdml_status, pddl_status, pq_status
  2  from   v$session
  3  where  osuser = 'psingh' ;

       SID    SERIAL# PROGRAM              PDML_STA PDDL_STA PQ_STATU           
---------- ---------- -------------------- -------- -------- --------           
       302      20075 SQLPLUS.EXE          DISABLED ENABLED  ENABLED            

SQL> insert /*+ parallel(test,2,3) */ into table_test test
  2  select object_id, mod(object_id,5)
  3  from all_objects
  4  where rownum = 1 ;

1 row created.

SQL> select *
  2  from table_test
  3  where rownum = 1;

 OBJECT_ID  MOD_VALUE                                                           
---------- ----------                                                           
       195          0                                                           

SQL> commit;

Commit complete.

SQL> alter session enable parallel dml ;

Session altered.

SQL> select sid, serial#, program, pdml_staus, pddl_status, pq_status
  2  from   v$session
  3  where  osuser = 'psingh' ;

       SID    SERIAL# PROGRAM              PDML_STA PDDL_STA PQ_STATU           
---------- ---------- -------------------- -------- -------- --------           
       302      20075 SQLPLUS.EXE          ENABLED  ENABLED  ENABLED            

SQL> insert /*+ parallel(test, 2, 3) */ into table_test test
  2  select object_id, mod(object_id, 5)
  3  from   all_objects
  4  where  rownum = 1 ;

1 row created.

SQL> select *
  2  from   table_test
  3  where  rownum = 1 ;
from   table_test
       *
ERROR at line 2:
ORA-12838: cannot read/modify an object after modifying it in parallel 


SQL> commit ;

Commit complete.

SQL> select *
  2  from   table_test
  3  where  rownum = 1 ;

 OBJECT_ID  MOD_VALUE                                                           
---------- ----------                                                           
       195          0      

On basis of above test, in contrast to what specified in the document, can i safely conclude that PARALLEL hint with PDML in enabled status results in PDML even on a non-partitioned table with bitmap index? 
Moreover, is it true that, whenever a PDML is going on, Oracle creates new sessions for each parallel server, e.g. in above test case it creates 6 (2*3) new sessions?

Thanks in advance.

ops$tkyte%ORA9IR2> drop table table_test;

Table dropped.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create table  table_test
  2  as     select object_id   ,
  3                mod( object_id, 5) mod_value
  4         from   all_objects ;

Table created.

ops$tkyte%ORA9IR2> create bitmap index table_test_b1
  2         on           table_test(mod_value) ;

Index created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert /*+ parallel(test,2,3) */ into table_test test select object_id, mod(object_id,5) from all_objects where rownum = 1 ;

1 row created.

ops$tkyte%ORA9IR2> select * from table_test where rownum = 1;

 OBJECT_ID  MOD_VALUE
---------- ----------
        30          0

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> alter session enable parallel dml ;

Session altered.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%parallel%';

NAME                                               VALUE
--------------------------------------------- ----------
queries parallelized                                   0
DML statements parallelized                            0
DDL statements parallelized                            0
DFO trees parallelized                                 0
Parallel operations not downgraded                     0
Parallel operations downgraded to serial               0
Parallel operations downgraded 75 to 99 pct            0
Parallel operations downgraded 50 to 75 pct            0
Parallel operations downgraded 25 to 50 pct            0
Parallel operations downgraded 1 to 25 pct             0

10 rows selected.

ops$tkyte%ORA9IR2> insert /*+ parallel(test, 2, 3) */ into table_test test select object_id, mod(object_id, 5) from   all_objects where  rownum = 1 ;

1 row created.

ops$tkyte%ORA9IR2> select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%parallel%';

NAME                                               VALUE
--------------------------------------------- ----------
queries parallelized                                   0
DML statements parallelized                            0
DDL statements parallelized                            0
DFO trees parallelized                                 0
Parallel operations not downgraded                     0
Parallel operations downgraded to serial               0
Parallel operations downgraded 75 to 99 pct            0
Parallel operations downgraded 50 to 75 pct            0
Parallel operations downgraded 25 to 50 pct            0
Parallel operations downgraded 1 to 25 pct             0

10 rows selected.
<b>
No parallel, but we did a direct path load...
</b>
ops$tkyte%ORA9IR2> select * from   table_test where  rownum = 1 ;
select * from   table_test where  rownum = 1
                *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


ops$tkyte%ORA9IR2> commit ;

Commit complete.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> drop index table_test_b1;

Index dropped.

<b>get rid of index...</b>

ops$tkyte%ORA9IR2> select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%parallel%';

NAME                                               VALUE
--------------------------------------------- ----------
queries parallelized                                   0
DML statements parallelized                            0
DDL statements parallelized                            0
DFO trees parallelized                                 0
Parallel operations not downgraded                     0
Parallel operations downgraded to serial               0
Parallel operations downgraded 75 to 99 pct            0
Parallel operations downgraded 50 to 75 pct            0
Parallel operations downgraded 25 to 50 pct            0
Parallel operations downgraded 1 to 25 pct             0

10 rows selected.

ops$tkyte%ORA9IR2> insert /*+ parallel(test, 2, 3) */ into table_test test select object_id, mod(object_id, 5) from   all_objects where  rownum = 1 ;

1 row created.

ops$tkyte%ORA9IR2> select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%parallel%';

NAME                                               VALUE
--------------------------------------------- ----------
queries parallelized                                   0
DML statements parallelized                            1
DDL statements parallelized                            0
DFO trees parallelized                                 1
Parallel operations not downgraded                     0
Parallel operations downgraded to serial               0
Parallel operations downgraded 75 to 99 pct            0
Parallel operations downgraded 50 to 75 pct            0
Parallel operations downgraded 25 to 50 pct            0
Parallel operations downgraded 1 to 25 pct             1

10 rows selected.

<b>and we get parallel...</b>

Hi Tom

First of all, a ton of thanks will be too small for your wonderful explanation and your patience to answer so many queries from around a world of Oracle programmers.

I would be very thankfull if you can help me with your suggestions for my issue.

I am using Oracle 9iR2.I am trying to run 2 different procedures in parallel. Both these procedures process huge amount of data with operations such as inserts with some transformations. Both these procedures use a transaction temporary table in common and session temporary table(while populating this table I have a column containing different values).

I have no transaction control statements in this(as I am using temporary tables).

When I try to run these 2 procedures sequentially I find the below timings.

proc1 = 7 mins 27 secs
proc2 = 5 mins 41 secs


After running when I try to rollback the processing sequentially I find below timings.

to rollback proc1 changes = 4.32
to rollback proc2 changes = 1.31

Now when I try to run these two procedures exectly in parallel I find the below timings

proc1= 10 mins 21 secs
proc2=  9 mins 41 secs

and the rollback times are

to rollback proc1 changes = 8 mins and 45 secs
to rollback proc2 changes = 4 mins and  9 secs

I tried to see the parallel parameter details from the db server

SQL> 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     2148
parallel_instance_group              string
parallel_max_servers                 integer     5
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

My questions
============
1.Why this behaviour happens on a database like oracle which supports parallel processing?

2.Is there any parameter I need to change in the above list of parameter values?

3.Why rollback operation has a varying time according to parallel and sequential runs of procedures?

4.Can I change the INITRANS parameter of all the tables which are modified in these procedures?

5.In Oracle tables have a default value of 1 for INITRANS and indexes have a default of 2. Why it is 2 for indexes?

Please advice if there is any way to get the execution times of the procedures low when running them in parallel.


Thanks in advance

Badri

Hi Tom,

In a DW environment (10g), do you think that parallel clause (without DOP) for dimension tables can have detrimental impact (by creating parallel execution coordinator and servers)on overall performance? Or oracle will know (based upon stats) that being small tables,no parallelism will be performed, as DBA is saying that every table should be created with parallel clause???

Regards,

Hi Tom,

Could you please help about having default parallel clause (without DOP)for all tables in DW environment?

Thanks

Hi Tom,

Just wanted to know that for small tables (few hundred rows) created with parallel clause, will oracle start creating parallel execution (especially load) processes? which may have more overhead then actually benefit, or oracle will NOT do parallel execution( based upon stats) because there is not much benefit in doing parallelism.

Regards

Hi Tom,
Thanks for the response.
Do you think that for DW load of small tables in parallel can have detrimental impact in overall performance of the machine? as Oracle will create additional processes, which will not be any benefit so far as loading is concerned - rather may impact the overall performance of other jobs.

Regards,

Hi Tom,
Going through your books and things here, the importance of parallel_automatic_tuning was something I started looking into. But as per 10g document ( http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/whatsnew.htm#sthref55 ), this parameter is deprecated. But I also noticed that there is some improvement in performance if this is set to TRUE in 10g, bit confusing, should we use this parameter or not in 10g?
Regards,

Hi Tom

I have a table that I am updating in parallel - this was working in my test system but since it was refreshed from production last week it no longer works. If I create an exact copy of the table using exp/imp to an indexfile it does update in parallel.

This is the plan of the one that does work:

---------------------------------------------------------------------------------------------------
Operation            |  Name                 | Rows  | Bytes | Cost  |  TQ    |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
UPDATE STATEMENT     |                       |    15M|   519M| 25300 |        |      |            |
 INDEX MAINTENANCE   | XXX_TASK_ASSIGNMENTS  |       |       |       | 90,04  | P->S | QC (RAND)  |
  UPDATE             | XXX_TASK_ASSIGNMENTS  |       |       |       | 90,03  | P->P | RANGE      |
   HASH JOIN         |                       |    15M|   519M| 25300 | 90,02  | P->P | HASH (BLOCK|
    TABLE ACCESS FULL| JTF_TASKS_B           |  8681K|   190M| 12500 | 90,00  | P->P | HASH       |
    TABLE ACCESS FULL| XXX_TASK_ASSIGNMENTS  |    15M|   187M| 10705 | 90,01  | P->P | HASH       |
---------------------------------------------------------------------------------------------------

And this is the one that does not work. JTF_TASK_ASSIGNMENTS does not have any triggers.

-------------------------------------------------------------------------------------------------
Operation            |  Name                 | Rows  | Bytes | Cost  |  TQ    |IN-OUT| PQ Distrib
-------------------------------------------------------------------------------------------------
UPDATE STATEMENT     |                       |    15M|   519M| 25300 |        |      |           
 UPDATE              | JTF_TASK_ASSIGNMENTS  |       |       |       |        |      |           
  HASH JOIN          |                       |    15M|   519M| 25300 | 83,02  | P->S | QC (RAND) 
   TABLE ACCESS FULL | JTF_TASKS_B           |  8681K|   190M| 12500 | 83,00  | P->P | HASH      
   TABLE ACCESS FULL | JTF_TASK_ASSIGNMENTS  |    15M|   187M| 10705 | 83,01  | P->P | HASH      
-------------------------------------------------------------------------------------------------

My question is - can you think of any reason why it is serializing? Parallel update takes 36 minutes - serial update takes 5+ hours. This is the update statement:

update
(select /*+ parallel(a,4) parallel(b,4) */
        a.actual_start_date
       ,a.actual_end_date
       ,b.scheduled_start_date
       ,b.scheduled_end_date
 from jtf.xxx_task_assignments a
     ,jtf.jtf_tasks_b b
 where a.task_id = b.task_id
)
set actual_start_date = scheduled_start_date
   ,actual_end_date = scheduled_end_date

Thanks and Regards

Ian

Tom - forget to say - yes I am doing alter session enable parallel dml; for both of them.

Regards

Ian

Tom

Regarding the above question - version is 9.2.0.7

Regards

Ian

Tom

They are identical apart from the table names. This is the bit after the column definitions:

   ) PCTFREE 10 PCTUSED 70 INITRANS 10 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 268435456 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 4 FREELIST GROUPS 4 BUFFER_POOL KEEP)
  TABLESPACE "JTFD" 

Regards

Ian

Tom

I backed up the stats from the original table then updated the rows in the stattab table to xxx instead of jtf then restored the stats into the new table. So the stats are identical. I have double checked the table definition - degree, instances, everything. All identical.

One thing I didn't mention was that the original table had two new columns added with an alter table command. But not in the same SQL*Plus session. Columns were added then exit sqlplus - then in a new session explain plan the update.

So to replicate this exactly I have recreated the xxx table without the two new columns using the output from dbms_metadata. I am in the process of populating it with all 15 Million rows from the original table - then will add the two new columns to see if that is the problem.

Sorry about not giving you a nice clear test case - but I can't replicate the problem at the moment!

Will let you know.

Thanks and Regards

Ian

Hi Tom,

I did the test case, as you demonstrated earlier in this thread. From this test case, I was looking for use of new column of v$SQL in 10.2 to check whether this query run in parallel mode, but after doing the test case, it's NOT showing such info.

Could you please, direct me where I'm missing..?

Thanks,
Suvendu


-----------

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> show parameter parallel

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean TRUE
parallel_execution_message_size integer 4096
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 160
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> drop table t;

Table dropped.

SQL> create table t (x int);

Table created.

SQL> exec dbms_stats.set_table_stats('TEST','T',numrows=>1000000,numblks=>100000);

PL/SQL procedure successfully completed.

SQL> explain plan for select /*+ parallel(t) myQuery */ count(1) from t;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 3026717286

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1520 (1)| 00:00:19 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 1000K| 1520 (1)| 00:00:19 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T | 1000K| 1520 (1)| 00:00:19 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------

13 rows selected.

SQL> l
1 select PX_SERVERS_EXECUTIONS, sql_text from v$sql
2* where sql_text like '%myQuery%'
SQL> /

PX_SERVERS_EXECUTIONS SQL_TEXT
--------------------- ------------------------------------------------------------
0 select PX_SERVERS_EXECUTIONS, sql_text from v$sql where sql_
text like '%myQuery%'

0 explain plan for select /*+ parallel(t) myQuery */ count(1)
from t


SQL>

Hi Tom,

After runing the query, still there is no update on PX_SERVERS_EXECUTIONS column. Below is the tkprof out of the query.

Could you please, update with a test case on it?


Thanks,
Suvendu



SQL ID : 0wkzfj4vkzba8
select /*+ parallel(t) myQuery */ count(1)
from
t


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 0.00 0.00 0 2 0 0
Fetch 2 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 5 0 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 28

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=5 pr=0 pw=0 time=0 us)
1 PX COORDINATOR (cr=5 pr=0 pw=0 time=0 us)
1 PX SEND QC (RANDOM) :TQ10000 (cr=3 pr=0 pw=0 time=0 us)
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
0 PX BLOCK ITERATOR (cr=3 pr=0 pw=0 time=0 us cost=1520 size=0 card=1000000)
0 TABLE ACCESS FULL T (cr=3 pr=0 pw=0 time=0 us cost=1520 size=0 card=1000000)

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


SQL> select PX_SERVERS_EXECUTIONS, sql_text from v$sql
2 where sql_text like '%myQuery%'
3 /

PX_SERVERS_EXECUTIONS SQL_TEXT
--------------------- --------------------------------------------------
0 select /*+ parallel(t) myQuery */ count(1) from t

ops$tkyte%ORA11GR1> create table t (x int, y char(200));

Table created.

ops$tkyte%ORA11GR1> insert /*+ append */ into t
  2  select level, 'x' from dual connect by level <= 1000000;

1000000 rows created.

ops$tkyte%ORA11GR1> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> set serveroutput off
ops$tkyte%ORA11GR1> select /*+ parallel(t) myQuery */ count(1) from t;

  COUNT(1)
----------
   1000000

ops$tkyte%ORA11GR1> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0wkzfj4vkzba8, child number 0
-------------------------------------
select /*+ parallel(t) myQuery */ count(1) from t

Plan hash value: 3126468333

--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |  1116 (100)|          |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |  1000K|  1116   (1)| 00:00:14 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| T        |  1000K|  1116   (1)| 00:00:14 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

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

   6 - access(:Z>=:Z AND :Z<=:Z)


23 rows selected.

ops$tkyte%ORA11GR1> select PX_SERVERS_EXECUTIONS, sql_text from v$sql where sql_text like '%myQuery%';

PX_SERVERS_EXECUTIONS SQL_TEXT
--------------------- ------------------------------
                    8 select /*+ parallel(t) myQuery
                       */ count(1) from t

                    0 select PX_SERVERS_EXECUTIONS,
                      sql_text from v$sql where sql_
                      text like '%myQuery%'

Tom,
Thanking you so much for answering me.

As, you told, from the segment header we came to know that there is no actual rows, so we continue with serial mode. But from my earlier reply, the tkprof also said that, the query was EXECUTED in parallel mode, when there is ONLY statistics.

I'm sure, I'm going to learn more new stuff. Please, correct me if I'm wrong.

Thanking you,
Suvendu

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=5 pr=0 pw=0 time=0 us)
1 PX COORDINATOR (cr=5 pr=0 pw=0 time=0 us)
1 PX SEND QC (RANDOM) :TQ10000 (cr=3 pr=0 pw=0 time=0 us)
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=0 us)
0 PX BLOCK ITERATOR (cr=3 pr=0 pw=0 time=0 us cost=1520 size=0 card=1000000)
0 TABLE ACCESS FULL T (cr=3 pr=0 pw=0 time=0 us cost=1520 size=0 card=1000000)

Hi Tom,

Thanks a lot for creating this site, its wonderful and I learn a lot from it. I have just bought your book Expert one on one 2nd edition, it is very good and you have explained many topics in great details. After reading on Parallel processing and how it works, I have some questions,
1. is it possible to find some inforamtion for each process ? information like why only some process used for hash join or sort and not others?
select /*+ parallel (b) */ a.*, b.*
from a, b
where a.some_column = b.some_column

2. what factors, I should look for selecting table in parallel process ? for example, if I have big table x joining with very big table y (40 times x), should I use parallel (x) or parallel (y) in the select clause ? and does it change if size of table x grows 10 times or size of y grows 100 times ? or I should use just parallel ?

3. If I have a table over dblink, does it process the same way ?
ex.
select /*+ parallel (a) */ a.*, b.*
from a, b@db_link_table
where a.some_column = b.some_column

Thanks,

Thanks Tom for your reply. I did not knew that over dblink it would be send in one pipe, so for larger data it is always good to run the query on remote db and then export import, correct ?

for #2,on selecting from big table or very big table, I still have question and here is the scenario,

I have a table X, which has 2 Million distinct account numbers and of size 25 MB and table Y, which has ~5.4 Billion rows and size of ~3.4 TB. account number is the primary key on both the tables.

DB is 10.0.2 , CPU 8 and I am the only user to use the maximum of box,

Total System Global Area 4294967296 bytes
Fixed Size 2147872 bytes
Variable Size 1522448864 bytes
Database Buffers 2768240640 bytes
Redo Buffers 2129920 bytes

When I run the query considering X (with 2M ban) in parallel mode, it takes 8 hours to return 175 million rows, but if I use Y in the query, it takes ~50 hours to do the same.

And this is not the end,the things changes for another very big table Z with ~120 Million rows, if I join Z with X and select X in parallel it takes more time then selecting z in parallel.

Though, type of query is different, both large tables have account number as primary key and that is the only column which is used for joining with table X.

Please see explain plan, for x joins with y and parallel (Y) in select statement:

INSERT STATEMENT CHOOSECost: 7,561,068 Bytes: 23,814,000 Cardinality: 441,000
21 LOAD AS SELECT C_CHARGE
20 PX COORDINATOR
19 PX SEND QC (RANDOM) SYS.:TQ10004 Cost: 7,561,068 Bytes: 23,814,000 Cardinality: 441,000
18 HASH GROUP BY Cost: 7,561,068 Bytes: 23,814,000 Cardinality: 441,000
17 PX RECEIVE Cost: 7,561,068 Bytes: 23,814,000 Cardinality: 441,000
16 PX SEND HASH SYS.:TQ10003 Cost: 7,561,068 Bytes: 23,814,000 Cardinality: 441,000
15 HASH GROUP BY Cost: 7,561,068 Bytes: 23,814,000 Cardinality: 441,000
14 HASH JOIN Cost: 7,560,972 Bytes: 499,947,228 Cardinality: 9,258,282
4 PX RECEIVE Cost: 1,566 Bytes: 339,115,491 Cardinality: 30,828,681
3 PX SEND HASH SYS.:TQ10001 Cost: 1,566 Bytes: 339,115,491 Cardinality: 30,828,681
2 PX BLOCK ITERATOR Cost: 1,566 Bytes: 339,115,491 Cardinality: 30,828,681
1 INDEX FAST FULL SCAN INDEX (UNIQUE) CLM_PK Cost: 1,566 Bytes: 339,115,491 Cardinality: 30,828,681
13 PX RECEIVE Cost: 7,453,667 Bytes: 27,018,549,007 Cardinality: 628,338,349
12 PX SEND HASH SYS.:TQ10002 Cost: 7,453,667 Bytes: 27,018,549,007 Cardinality: 628,338,349
11 HASH JOIN Cost: 7,453,667 Bytes: 27,018,549,007 Cardinality: 628,338,349
8 BUFFER SORT
7 PX RECEIVE Cost: 746 Bytes: 29,494,972 Cardinality: 2,268,844
6 PX SEND BROADCAST LOCAL SYS.:TQ10000 Cost: 746 Bytes: 29,494,972 Cardinality: 2,268,844
5 INDEX FAST FULL SCAN INDEX (UNIQUE) BAN_PK Cost: 746 Bytes: 29,494,972 Cardinality: 2,268,844
10 PX BLOCK ITERATOR Cost: 7,447,075 Bytes: 163,991,895,000 Cardinality: 5,466,396,500 Partition #: 20 Partitions accessed #1 - #22
9 TABLE ACCESS FULL TABLE CHARGE_HISTORY Cost: 7,447,075 Bytes: 163,991,895,000 Cardinality: 5,466,396,500 Partition #: 20 Partitions accessed #1 - #22

And this is when I put small table X in select statement parallel (X) joins with Big table Y

Plan
INSERT STATEMENT CHOOSECost: 16,354,996 Bytes: 386,431,509 Cardinality: 8,221,947
17 LOAD AS SELECT C_CHARGE
16 PX COORDINATOR
15 PX SEND QC (RANDOM) SYS.:TQ10002 Cost: 16,354,996 Bytes: 386,431,509 Cardinality: 8,221,947
14 HASH GROUP BY Cost: 16,354,996 Bytes: 386,431,509 Cardinality: 8,221,947
13 HASH JOIN Cost: 16,348,210 Bytes: 386,431,509 Cardinality: 8,221,947
4 PX RECEIVE Cost: 1,566 Bytes: 339,115,491 Cardinality: 30,828,681
3 PX SEND HASH SYS.:TQ10000 Cost: 1,566 Bytes: 339,115,491 Cardinality: 30,828,681
2 PX BLOCK ITERATOR Cost: 1,566 Bytes: 339,115,491 Cardinality: 30,828,681
1 INDEX FAST FULL SCAN INDEX (UNIQUE) CLM_PK Cost: 1,566 Bytes: 339,115,491 Cardinality: 30,828,681
12 PX RECEIVE Cost: 116 Bytes: 8,310 Cardinality: 277
11 PX SEND HASH SYS.:TQ10001 Cost: 116 Bytes: 8,310 Cardinality: 277
10 TABLE ACCESS BY LOCAL INDEX ROWID TABLE CHARGE_HISTORY Cost: 116 Bytes: 8,310 Cardinality: 277 Partitions accessed #1
9 NESTED LOOPS Cost: 16,264,057 Bytes: 20,088,168,696 Cardinality: 558,004,686
6 PX BLOCK ITERATOR
5 INDEX FAST FULL SCAN INDEX (UNIQUE) BAN_PK Cost: 54 Bytes: 12,089,274 Cardinality: 2,014,879
8 PARTITION RANGE ITERATOR Cost: 4 Cardinality: 277 Partition #: 16
7 INDEX RANGE SCAN INDEX (UNIQUE) CHARGE_HISTORY_PK Cost: 4 Cardinality: 277 Partition #: 16

From the cost of the query it looks good for parallel(big) but real execution time is less for parallel (small), what I am missing here ? please advice.

So, what do you suggest for larger data ? I always learn better way of doing things from your notes, please advice.

Thanks,

Hi Tom

A simple 'Thanks a lot' is not sufficient for the kind of guidance you offer to the world of Oracle Programmers. Coming to the point,

When we are doing a table load/insert rows in a table using a transaction, if we opt to use parallel option then, I read for

(i)SQL Loader multiple sessions depending on the DOP, are created and load is performed into the table simultanously.
(ii)If we insert records with parallel hint then different transactions are created in the same session and the records are inserted simultanously.(please correct me if I am wrong in this point).

My doubt is how the locking issue is handled by oracle in these 2 scenarios, definitely oracle has a solution to overcome the locking conflict in these 2 cases, just eager to know how it happens.

Will really appreciate if you can please clear this.

Thanks in advance
Badri

Hi Tom,

Once again 'THANKS A LOT' for such an informative reply. Idea was to get the concept clear on what exactly happens in the background when we do insert using sqlldr parallel and parallel direct path hints. Based on your reply I was just testing a scenario as shown below.
(Please tolerate if I am testing your patience, for the sake of me knowing oracle better :). )

I opened 2 sessions from my system.

Version: Oracle 9iR2.

Session 1:
==========
create table a(x int);
(Table Created.)
insert /*+ append parallel(a 4) */ into a select /*+ parallel(b 4) */ level from dual b connect by level <1000;
(999 rows created.)

With this status of Session 1 if I open Session 2 and do the below insert for 9999 rows,

Session 2:
==========
insert /*+ append parallel(a 4) */ into a select /*+ parallel(b 4) */ level from dual b connect by level <10000;
(session hangs)

after issuing the commit in Session 1, we can see the message 9999 rows created in Session 2.

Please note that in the insert statement I have mentioned the append(for insert above high water mark) and parallel(for 4 simultaneous sessions to do the job) hints in both Sessions.

1.Why Session 2 hangs when Session 1 is not committed,if Session 2 is going to parallelly insert records in its own extents(also above high water mark)?
2.Will parallel load really work when we insert data using direct load method?

I am absolutely sure that I am well on my way to get the concept clear and know something new in this region.

Thanks in advance
Badri

Thanks Tom for replying.

#2 is understood and fine.

#1 Breaking your reply into 2 parts for understanding sake,

a)because a direct path load requires a table lock.

As per my understanding any type of insert or DML statements require a lock on the table. What is the reason you mentioned this point specifically for direct path load?

b)If you direct path into a table, only one person at a time can do it.

Well. This reply is for a person doing the load manually.If I am correct then what happens when the server opens multiple sessions depending on the DOP of the table to do the direct path load parallelly. Will there be any difference in the parallel load process when it is done manually or automatically by the server?

As per your previous reply can I say,

When parallel direct path load happens, the DB Server opens multiple parallel sessions depending on the DOP and these sessions are coordinated by one session which opened them. Each session write to its own extents above high water mark(per this point I understand that the preformatted data blocks are created using the fresh memory extents) and they are merged by the session which coordinates them all and the preformatted data blocks are moved into table's storage in the physical data file.

My doubt is that if sessions are using their own fresh memory extents, then they need not have to be writing data in the same areas of the table. If this is the case then how Session 2 hangs in my example above?

Got a bit confused, requesting you to throw some more light on this.

Thanks in advance
Badri

Thanks a lot once again for your patience in explaining this yet again upon my request.

Badri

Dear Tom,

In one of our telecom billing database I see parallel query related events quite often. This is OLTP environment and at least 250+ user at a time accessing this database.
Earlier many times I have found that setting noparallel to table/index improves overall DB performance until specified in select clause in case of FTS but value of PARALLEL_ADAPTIVE_MULTI_USER, PARALLEL_AUTOMATIC_TUNING was set to false in those databases.
Now in this database I'm not sure if degree 2,4 will help so I'm planning to change it to NOPARALLEL but in this thread you said to use value of PARALLEL_ADAPTIVE_MULTI_USER, PARALLEL_AUTOMATIC_TUNING parameters to true and degree of table/index as parallel, i.e., default.
Please suggest me what should I keep the value of these parameters and degree of table/indexes considering this scenario as we don't have test setup for this DB?


Server  : HP-UX 11.11
DB Version : 8.1.7.4
Memory  : 16GB
CPU  : 8

SQL>select degree,count(*) from dba_tables group by degree;

DEGREE       COUNT(*)
---------- ----------
         1       4871
         2          2
         4         15
   DEFAULT          2

SQL> select event,count(*) from v$session_wait group by event order by 2;

EVENT                                     COUNT(*)
--------------------------------------- ----------
SQL*Net message to client                        1
direct path read                                 1
direct path write                                1
smon timer                                       1
pmon timer                                       1
db file scattered read                           3
latch free                                       4
PX Deq Credit: send blkd                         4
db file sequential read                          5
PX Deq: Execution Msg                            6
rdbms ipc message                                8
SQL*Net message from client                    358

SQL> sho parameter parallel

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

SQL>select sum(bytes)/1024/1024 from dba_segments;

SUM(BYTES)/1024/1024
--------------------
          720099.023


SQL>select * from v$license;

SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER  USERS_MAX
------------ ---------------- ---------------- ------------------ ----------
           0                0              369                402          0

Dear Tom,

By mistake I pasted init parameter value of other DB in my earlier post. Please ingnore that as original value is as follows:

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

Dear Tom,
Many thanks for reply. Actually I'm also in favour of setting degree to 1 ,i.e., noparallel. I write the parallel degree in sql itself if needed. But here at my client, people insist to have degre >1 on tables.
Now I'll ask them to disable degree of parallelism on table/indexes as this has stamp of Tom too. :)
Thanks Again.

Dear Tom,
You also get nervous????? Sounds strange to me. Anyway I'll take your words.
Thanks a ton.

As a DBA, I want to understand the impact of parallel executions on the database performace.

1. How can I get the parallel degrees that were used by SQL statements during recent days (such as past two days)?

2. I queried $SQL, but got big number on the PX_SERVERS_EXECUTIONS column for some queries in our 10.2.0.3 database. Why?

PARSING_SCHEMA_NAME PX_SERVERS_EXECUTIONS
SYS 3813
SYS 814
DBSNMP 739
DBSNMP 739
DBSNMP 712
SYS 283
SYSMAN 214
SYS 86
DBSNMP 74
BI 61
BI 61

Thanks a lot.

Hi Tom,

I had a few doubts on using the PARALLEL operations through hints. My application is a datawarehouse and we are using PARALLEL hints extensively in the materialized view definitions. As the MVs are all refreshed as the last activitity after the daily load, it is safe to assume that there is no user activity at the time when PARALLEL is at play.

Oracle Version: 10.2.0.4
cpu_count = 16
RAM = 40 Gigs
SGA_TARGET = 16 Gigs
PGA_AGGREGATE_TARGET = 1 Gig

The related parameters are sized as: (all at their defaults)

fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2152
parallel_instance_group              string
parallel_max_servers                 integer     320
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


Typically, the way we write our hint is as under:(it is from an existing query part in the materialized view ddl)

SELECT
/*+ PARALLEL (A 4) PARALLEL (B 4)  PARALLEL (C 4)  PARALLEL (E 4)  PARALLEL (F 4)  PARALLEL (H 4)  PARALLEL (I 4)  PARALLEL (J 4)*/
DISTINCT
--will contain about 20 fields from all FROM tables
FROM
EDW.FACT1 A,
EDW.DMV1 B,
EDW.DIM1 C,
EDW.DMV2 E,
EDW.FACT2 F,
EDW.DIM2 H,
EDW.DIM3 I,
(SELECT
/*+ PARALLEL (X 4) */
SUM (NVL(PROJ_STOCK_QTY,0)) PROJECT_STOCK_QTY,
SUM (NVL(NON_PROJ_STOCK_QTY,0)) NON_PROJECT_STOCK_QTY,
SUM (NVL(RECEIVING_OUT,0)) RECEIVING_OUT_QTY,
SUM (NVL(INSP_STOCK_QTY,0)) INSP_STOCK_QTY,
SUM (NVL(RESTRICTED_STOCK_QTY,0)) RESTRICTED_STOCK_QTY,
SUM (NVL(BLOCKED_STOCK_QTY,0)) BLOCKED_STOCK_QTY,
SUM (NVL(RETURNS_STOCK_QTY,0)) RETURNS_STOCK_QTY,
MATERIAL_NO MATERIAL_NO
FROM EDW.DIM4 X
WHERE 
X.CURRENT_FLG = 1
GROUP BY 
MATERIAL_NO ) J
WHERE 
--Join and Filter clauses here
;


My questions are:

1. Should we blindly include all tables and the resultset(PARALLEL (J 4)) in the hint? Some of those tables are quite small by warehouse standards(less than 100K rows). Explain plan statement indicates that all are not being used.

2. If I have to selectively choose tables for PARALLEL hint, should I just go for the largest of the tables that are being full table scanned? Any thumb rule in this regard? 

3. The job to refresh the materialized views will be running in serial mode(one after other) atleast for now. I am already using the atomic_refresh=false. My parallel max servers are 320. So, is it safe to assume I can increase the degree to 32 provided I keep the number of tables to 4-5? I would definetely be doing detailed tests to increase even beyond the current 4, but my question is a theoretical one. Is that allocattion correct?

4. My PGA_AGGREGATE_TARGET is too small currently(at 1G). I have to recommend increasing it to a higher number. I have more than 1% multi-pass executions for even operations that require 512K. Will this reason alone be sufficient for the others to understand the ramifications or is there anything else that will help them?

5. Can you please help me in understanding the below output?

SQL> SELECT * FROM V$PX_PROCESS_SYSSTAT WHERE STATISTIC LIKE 'Buffers%';

STATISTIC                           VALUE
------------------------------ ----------
Buffers Allocated                  427895
Buffers Freed                      427535
Buffers Current                       360
Buffers HWM                          1157



Thanks and regards
Manjunath

Hi Tom

I'd like to get back to the parallel_pipelined function example. I've seen odd behaviour with a function that I made, where the results did not pipe back one by one but came back in one go.

Here's an example (based on yours) how to reproduce it

create table t0
as
select rownum r from all_objects where rownum < 15
/

create table t1
as
select rownum id, object_name text
from all_objects,t0;

analyze table t1 compute statistics;


create or replace
function parallel_pipelined( l_cursor in sys_refcursor )
return t2_tab_type
pipelined
parallel_enable ( partition l_cursor by any )
is
l_session_id number;
l_rec t1%rowtype;
begin
select sid into l_session_id
from v$mystat
where rownum =1;
loop
fetch l_cursor into l_rec;
exit when l_cursor%notfound;
if mod(l_rec.id,100000)=0 then
pipe row(t2_type(l_rec.id,to_char(sysdate,'hh24:mi:ss'),l_session_id));
end if;
end loop;
close l_cursor;
return;
end;
/

set arraysize 1


select *
from table
(parallel_pipelined
(CURSOR (select /*+ parallel(t1) */ * from t1))
)
/

ID TEXT SESSION_ID
---------- ------------------------------ ----------
300000 15:41:06 130
100000 15:41:03 138
400000 15:41:06 138
200000 15:41:05 140
500000 15:41:07 170
600000 15:41:08 170
700000 15:41:08 170

So the difference with yours is that the PIPE ROW is conditional..
Could you tell me why the pipe is not 'dripping back' the results? I get them all in one go after 6 seconds. If you remove the if-clause with the mod, the piping works (you get all rows back of course)

Many thanks
Mark

ops$tkyte%ORA11GR1> create or replace
  2      function parallel_pipelined( l_cursor in sys_refcursor )
  3      return t2_tab_type
  4      pipelined
  5      parallel_enable ( partition l_cursor by any )
  6  is
  7      l_session_id number;
  8      l_rec        t1%rowtype;
  9     begin
 10         select sid into l_session_id
 11           from v$mystat
 12          where rownum =1;
 13         loop
 14             fetch l_cursor into l_rec;
 15             exit when l_cursor%notfound;
 16             if mod(l_rec.id,100000)=0 then
 17             pipe row(t2_type(l_rec.id,to_char(sysdate,'hh24:mi:ss') || ' looped ' || l_cursor%rowcount || ' times',l_session_id));
 18             end if;
 19         end loop;
 20         close l_cursor;
 21         return;
 22    end;
 23  /

Function created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> set arraysize 1
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select *
  2  from table
  3     (parallel_pipelined
  4       (CURSOR (select /*+ parallel(t1) */ * from t1))
  5      )
  6  /

        ID
----------
TEXT
-------------------------------------------------------------------------------
SESSION_ID
----------
    700000
11:46:04 looped 84563 times
       143

    800000
11:46:04 looped 99815 times
       147

    900000
11:46:06 looped 120694 times
       135

    400000
11:46:01 looped 46274 times
       170

    200000
11:46:00 looped 25984 times
       148

    600000
11:46:03 looped 80665 times
       148

    500000
11:46:02 looped 63968 times
       139

    100000
11:45:59 looped 10389 times
       138

    300000
11:46:01 looped 36643 times
       138


9 rows selected.

Thanks Tom, I see your point.

But the timestamp of the actual PIPE ROW is in the result ( using to_char(sysdate,'hh24:mi:ss') )
So it turns out that there were seconds between the piping.. enough to have results sent one by one.

Like in your output session 148 finds results with 3 seconds in between:

200000
11:46:00 looped 25984 times
148

600000
11:46:03 looped 80665 times
148

So I would expect to see that first result earlier than the second.

cheers!
Mark

Ok, that clears things up, thanks!

Just one more thing - would there be any way (a HINT?) to influence this query message buffering in between the execution servers and the query coordinator? Some setting to make buffer size =1 ?

Okay, we thought we had a brilliant idea of using unions to call pipelined functions so our code execute in parallel....

The problem is that each function is sequentially executed :-( We wanted the functions themselves to be executed concurrently. Even if we were to pass in cursor to the functions and use the parralel_enable (partion by any) option, I don't think it would make a difference because they would be processed serially, does that make sense? Do you know of a workaround for this?

Sample query for reference.
INSERT INTO xxpa_pfp_temp_lines xpt
-- Find Current Rev Budget
(SELECT *
FROM TABLE(get_rev_budget(p_as_of_date, l_project_info.project_id)))
UNION ALL
(SELECT *
FROM TABLE(get_orig_rev_budget(p_as_of_date, l_project_info.project_id)))
UNION ALL
-- Find Current Cost Budget
(SELECT *
FROM TABLE(get_cost_budget(p_as_of_date, l_project_info.project_id)))
UNION ALL
(SELECT *
FROM TABLE(get_orig_cost_budget(p_as_of_date, l_project_info.project_id)))
UNION ALL
-- Find Current Actual Units and Qty reported (from Payroll/Timecard)
(SELECT *
FROM TABLE(get_hr_units(p_as_of_date, l_project_info.project_id)))
UNION ALL
-- Find Current Actual Expenditures/Cost
(SELECT *
FROM TABLE(get_actual_expenditures(p_as_of_date, l_project_info.project_id)))
UNION ALL
-- Find Committed cost
(SELECT *
FROM TABLE(get_committed_cost_amt(p_as_of_date, l_project_info.project_id)))
UNION ALL
-- Find PCIM Known Risks (KR)
(SELECT *
FROM TABLE(get_pcim_data(p_as_of_date, l_project_info.project_id, l_pfp_header_id)));

Result execution:
12:29:48: Cached 23 Expenditure Type IDs..
12:29:48: ====Finished Initialization=====
12:29:48: Generating for PFP Year Month: 200904
12:29:48: Starting Rev Budget..
12:29:49: Rev Budget completed for Project..
12:29:49: Starting Original Rev Budget..
12:29:49: Original Rev Budget completed for Project..
12:29:49: Starting Cost Budget..
12:29:49: Cost Budget completed for Project..
12:29:49: Starting Original Cost Budget..
12:29:49: Original Cost Budget completed for Project..
12:29:49: Starting HR Units..
12:29:54: HR Units completed for Project..
12:29:54: Starting Actual Expenditures..
12:31:02: Expenditures completed for Project..
12:31:02: Starting Commitments..
12:31:24: Commitments completed for Project..
12:31:24: Starting PCIM Known Risks..
12:31:27: PCIM Known Risks completed for Project..
12:31:27: ====Finished Get Data=====

Thanks,

Ike Wiggins
http://bipublisher.blogspot.com

Followup Question: Would I get parallelism if create a function that is:
1. pipelined parallel enabled
2. partions the row by any
3. that takes in the unioned pipelined functions as a cursor


Example:

SELECT * FROM
TABLE(SUPER_FUNCTION(CURSOR(
SELECT * FROM TABLE(get_rev_budget(...)
UNION ALL
SELECT *
FROM TABLE(get_orig_rev_budget(..))
)))

Ike Wiggins
http://bipublisher.blogspot.com

Hi Tom,

We had oracle tunning our datawarehouse long time ago.

When i came to my company I read the tunning report done.
The whole report was done on 4 queries using different parallel degree, he showed how a single query improved with paralellism while he added more data to the table.
While he added data to the table the query or queries improved while paralellism was increased

So finally he recomended using for tables
Table size DEGREE
XMB -> degree N
XMB+YMB -> degree N +Z ............

I cannot understand his decisions trying 4 queries more or less, he makes decisions over all the database and how the degree should be taken for each table on the database, not even taking care if the table was partitioned ,or how many sessions could be on the database at some point in time having the new degrees on the table.

By the way he recommended using parallel degree of 16 -> big tables.
Maybe i am mistaken but if we have cpu = 24 (when the report was done) having possibly 33 sessions for one query "1 (qsid) + 16 + 16" with 24 cpus is not a good idea.

1)Could you please answer if he should have taken more care with the decisions of parallel degree on the database tables.

2)Was the decission of degree 16 on a CPU = 24 a good idea.

Dear Tom,
Thank you for your recent presentations in Moskow. They were interesting and very helpful.

We use your DIY parallelism widely. It works fine while we can split our task to many nearly independent peaces of work and in case of some crash we will run the process again and it will process the rest of peaces.

But sometimes these peaces are not logically independent e.g. in order to switch business date we have to process several sets of tables and logically it should looks like one transaction.

At present we solve the problem using locks to manage parallel processes: jobs will finish data processing and then wait for some lock to release. The main process will analyze the results of each job and it will signal them whether to commit or rollback. But it looks complex and we still have possibility of crash between releasing of lock and actual commit. In this case we will run the process again but for some time our database will be in logically inconsistent state.

Is there any solution that allows us to use something like two phase commit inside database?

P.S. I hope that I've answered on your "why do we need this transactional behaviour" question :-)

DBMS_SCHEDULER will help us to implement business logic in simpler way. We even will be able to simplify our recover procedure.
But it solves only 90+% of our task.
The great thing will be not to write this recover procedure at all and to commit the results of parallel tasks or rollback them simultaneously.

Probably my English is unclear.

What we need:

Task 1: Move some part of the data from table 1 and table 2 to history tables then clear tables.
Task 2: Move the contents of table 3 and table 4 to history tables with some complex transformation then update table 3 and 4
…
Task N: process table X

We cannot use Parallel DML because of its restrictions (these tables have triggers and self-referential foreign keys). More over, sometime we have to implement our logic in PL/SQL because of its complicity.

At present we run these tasks in parallel using jobs and when each task finishes, it waits for other tasks to finish and then for command to commit or rollback. So we’ve implemented something like two-phase commit. It would be great to use some embedded solution.

Thanks a lot.

It would work if we were able to get exclusive access to the tables but in reality some processes can insert new data to them. I imagine if it is possible to use several client sessions and to start distributed transaction between them and to run different tasks in different sessions in parallel (using different threads) and then use usual two phase commit?

Thanks a lot

Dear Tom,
Thank you very much for your advice. Probably we will think how to handle possible rare situation when our database is in inconsistent state instead of playing with «atomic bomb».

Hope to see your new presentations in Kiev or San Francisco this year.

Best regards,
Andrey

For VLDB processing need to ensure that my processing runs in parallel. However PQ servers may not be available ( used for other jobs ). How to force the sql ( e.g. CTAS ) run in parallel or die in case that PQ servers are not available for session now?

Thanks in advance,

Grzegorz

Tom,
We have a data extraction process which runs every night for an hour. The process has many CREATE TABLE <tablename> NOLOGGING PARALLEL(DEGREE 4) statements. Earlier the process without PARALLEL used to take 4-5 hours. Now the DBAs have started complaining about the load on the file system due to PARALLEL clause. They want as to remove the PARALLEL clause now. Is this using PARALLEL not a correct approach?

Hi tom,

My parallel params are


parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_max_servers integer 35
parallel_threads_per_cpu integer 2

1. I run the following statements.
aleter session force parallel query parallel 5;
run <query>;
this query takes 10 slaves.
Is this possible to override my session settings.

2. is there a parameter at instance level to control session/query other than logon trigger
to take a particular amount of slaves sothat all the sessions will share equal no of slaves?
because if first user runs query, he consumess 15 slaves, next qry will have to consume less slaves.

Tom,
Thanks for the answer you provided on June-22nd. The production server has 16 CPUs and the DBAs couldn't come up with any performance bottleneck issue related with this script. They are of the opinion that, since the table has PARALLEL DEGREE 4, each query , which involves this table(which is created with PARALLEL DEGREE 4) will have wait contention issue and adversely affect the performance. My question to you is, how does a query on a table which has PARALLEL DEGREE 4 affects the performance?

Hello Tom,

There are many tables in one of my database schema ( oracle verion 10.2.0.4) with Degree as DEFAULT. does it mean that they have a degree > 1. ( Database Server has 8CPU and CPU per thread is 2). I was reading in metalink that Default = parallel_threads_per_cpu * cpu_count.

explain for queries off those tables shows the parallel processing...

is this understanding correct ?
and if I see lot of waits related with parallel execution - is it ok to test with degree = 1 ?

Hi Tom,

I have two queries(ITAS) both of them mutually exclusive(insert in separate table and select from separate tables) when i run them alone they finish in half the time they take when i run both the script in parallel.

Both of these script use 32 parallel threads at a time and parallel_max_server =64 on our Oracle 10gR2 running on HPUX with
8 Intel(R) Itanium 2 9100 series processors
16 logical processors (2 per socket)

When i traced the wait events. I see that most of the time its PX Deq Credit: send blkd and PX Deq: Table Q Normal.

Also when i kept continuous monitoring i see that one of the script does only single rowid range scan whereas the other one does 8 in parallel.

Can you please tell if there is any upper limit on rowid range scans when we run multiple queries in parallel.

Hi tom

Our dwh system is oracle solaris 5.10 with oracle 10.2.0.4,32 cpu ,32g memory. and parallel settings are

parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean TRUE
parallel_execution_message_size integer 4096
parallel_instance_group string
parallel_max_servers integer 200
parallel_min_percent integer 0
parallel_min_servers integer 0

A join query on table with 180 partitions took around 128 processes. After this query, if i started the
other query (which usually take 128 processes), it is going into serial mode eventhough it can take upto 72 processes.
whichever query comes first, take full advantage of parallel. other queries are going into serial.

My question is

1. why 2nd query goes in serial mode instead of using 72 available PQ slaves.
2. How to avoid each query have a fair equal chance for parallel? ( how about parallel_min_percent).
3. In 10g, all the sessions are PQ_status enable by default. Query on a table with 180 partitions with degree 1,
sometimes taking parallel or serial eventhough PQ slaves are availble.

Thanks in advance.

Hi Tom,

Thanks for guidence. if a table has 32 partitions with bitmap local index on them. partition pruning take place during the query and 5 partitions are select with DOP =5.

for optimizer possible access paths are:
full scan the partition by each slave process.
bitmap index scan the partition with by each slave process.

I am putting this question because there is some confusion that parallel processing uses only full table scan or partition scan as the access path.

I am doing some research on RDBMS architecture of different database and have not able to find the answer anywhere.

Hi tom,

We have migrated a database from HP plateform to Linux plateform (Oracle 10g). We have done the export and import activity. Now, while creating index on very large tables parallally from multiple session, INDEX CREATION is getting bery slow. We have explored option of increasing pga agreegate,db_multiblock_read_count setting, parallel indexing option along with nologging option. There is no significant change in the
performance.

Parameters are set as follows :
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
parallel_automatic_tuning boolean FALSE
parallel_degree_limit string CPU
parallel_degree_policy string MANUAL
parallel_execution_message_size integer 8192
parallel_force_local boolean FALSE
parallel_instance_group string
parallel_io_cap_enabled boolean FALSE
parallel_max_servers integer 8
parallel_min_percent integer 0
parallel_min_servers integer 0
parallel_min_time_threshold string AUTO
parallel_server boolean TRUE
parallel_server_instances integer 3
parallel_servers_target integer 128
parallel_threads_per_cpu integer 2
recovery_parallelism integer 0


If we are running index creation scripts,one by one, it is taking very less time as compared to several index creation at a time through multiple session.

Could you please let us know if, we can do someting to increase the performance of INDEX CREATION at multiple session at a time?

Happy New Year!

In one of your initial response related to DIY parallelism,
it should be possible to do the below :=

select min(rowid), max(rowid), count(*), nt
from ( select rowid, ntile(8) over (order by rowid) nt
from some_table
)
group by nt order by nt;


and kickstart 8 parallel jobs to delete from some_table (which is large one) or any other processing.

This should help us save time as it is doing the updates/deletes in parallel.Right ?

first time to be here =]

I'm curious about parallel execution in ORACLE, thus lead me to this link.

Looking into the view v$parameter, I found that parameter 'PARALLEL_AUTOMATIC_TUNING' is marked isdeprecated='TRUE' in 10g. And I learned it might brought ORA-32004 if 'PARALLEL_AUTOMATIC_TUNING' is turned on with a system reboot.

I'm now wondering:
1. if it is a good choice to use 'PARALLEL_AUTOMATIC_TUNING' in 10g.

2. will the DEGREE value in TABLES as well as INDEXES help without 'PARALLEL_AUTOMATIC_TUNING' parameter and /* +PARALLEL */ hint?

my regards

Tom,
  We have a DB that can not run query in parallel.
SQL> set autot trace
SQL> select /*+ parallel(T)*/
  2   count(*)
  3    from CUSM T;

Elapsed: 00:03:28.52

Execution Plan
----------------------------------------------------------
Plan hash value: 2872246982

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |  3660   (0)| 00:00:44 |       |       |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          |  8541 |  3660   (0)| 00:00:44 |     1 |    36 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| CUSM     |  8541 |  3660   (0)| 00:00:44 |     1 |    36 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------


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

SQL> set autot off
SQL> select name, value
  2    from v$mystat s, v$statname n
  3   where s.statistic# = n.statistic#
  4     and n.name like '%parallel%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
DBWR parallel query checkpoint buffers written                            0
queries parallelized                                                      0
DML statements parallelized                                               0
DDL statements parallelized                                               0
DFO trees parallelized                                                    0

Elapsed: 00:00:00.01

--when the first query is running I issued the following query in another session 
SQL> select status, count(*) from v$pq_slave group by status;

STAT   COUNT(*)
---- ----------
IDLE          1

SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_execution_message_size      integer     2152
parallel_instance_group              string      ORAUTF1
parallel_max_servers                 integer     320
parallel_min_percent                 integer     0
parallel_min_servers                 integer     0
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0


SQL> select * from v$pq_sysstat;

STATISTIC                                                         VALUE
------------------------------------------------------------ ----------
Servers Busy                                                          0
Servers Idle                                                          1
Servers Highwater                                                    38
Server Sessions                                                  531452
Servers Started                                                     793
Servers Shutdown                                                    792
Servers Cleaned Up                                                    0
Queries Initiated                                                114146
DML Initiated                                                         0
DDL Initiated                                                         0
DFO Trees                                                        176207
Sessions Active                                                       0
Local Msgs Sent                                                 2309610
Distr Msgs Sent                                                 1417713
Local Msgs Recv'd                                               3409953
Distr Msgs Recv'd                                               2141506


SQL> !vmstat

System configuration: lcpu=16 mem=24576MB

kthr    memory              page              faults        cpu    
----- ----------- ------------------------ ------------ -----------
 r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa
 3  1 4991553 1024360   0   0   0 295 10393   0 387 63739 8561 17  5 77  0

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

It strange that v$pq_sysstat shows
  Servers Highwater                                                    38
  Servers Started                                                     793
I think it means that there have been some parallel query,
But I cannot run any sql in parallel
After set parallel_min_servers=10,I can find some ora_xx_p0x process ,
But the query still cannot run in parallel .
Could you give me any suggestions?
Tanks and Best Regards

ops$tkyte%ORA10GR2> set serveroutput off
ops$tkyte%ORA10GR2> select /*+ parallel(t) */ count(*) from big_table.big_table t;

  COUNT(*)
----------
    100000

ops$tkyte%ORA10GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  7f35pd0r9w6a7, child number 0
-------------------------------------
select /*+ parallel(t) */ count(*) from big_table.big_table t

Plan hash value: 1764098166

------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |       |    80 (100)|          |
|   1 |  SORT AGGREGATE       |              |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| BIG_TABLE_PK |   100K|    80   (3)| 00:00:01 |
------------------------------------------------------------------------------


14 rows selected.

Hi Tom,
We have a table like TEMP_LOCKBOX_REMITTANCE of   73294780
records...Now we are trying to update it -
############
alter session enable parallel dml;
alter table contract_payment parallel (degree 6);

--------TEMP_LOCKBOX_REMITTANCE
declare
          
     cursor c_cur3 is select rowid
       from TEMP_LOCKBOX_REMITTANCE 
       where  payment_method_type_code='MC';
       --and rownum = 1;

     
 Type cur3_type is table of c_cur3%rowtype;
 

 c3 cur3_type;
 
 begin
 -------
  Open c_cur3;
  LOOP
    Fetch c_cur3 bulk collect into c3 LIMIT 1000000;  
     --MC
    Forall I3 in c3.first .. c3.last
      UPDATE TEMP_LOCKBOX_REMITTANCE
      SET PAYMENT_CONTROL_NUMBER='C217DC8C813A77562F9A2979302CBD32E1924E940939D6461169DCE01F6150A7G425',
      last_modified_by = USER,
      changed_by       = USER,
      last_modified    = sysdate,
      changed_date     = sysdate  
      where rowid      = c3(I3).rowid;
   EXIT when c_cur3%NOTFOUND;
   commit;
  END LOOP; 
 Close c_cur3; 
 --------------------------------------------------------------------
EXCEPTION
  WHEN OTHERS THEN
   Dbms_Output.put_line('Error '||sqlerrm);
end;
/
############
I saw that 
SQL> show parameter parallel

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
fast_start_parallel_rollback         string
LOW
parallel_adaptive_multi_user         boolean
TRUE
parallel_automatic_tuning            boolean
FALSE
parallel_execution_message_size      integer
2152
parallel_instance_group              string

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------

parallel_io_cap_enabled              boolean
FALSE
parallel_max_servers                 integer
200
parallel_min_percent                 integer
0
parallel_min_servers                 integer
5

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
parallel_server                      boolean
FALSE
parallel_server_instances            integer
1
parallel_threads_per_cpu             integer
2
recovery_parallelism                 integer
0
#########
We are having 16 CPU ... 
As I mentioned , the above update is one of the update statements...we are trying in 6 sessions each of them having similar update on the same table (mutually exclusive)... 
$ jobs
[6] +  Running                 nohup ./rel_6.sh >>a6.log 2>&1 &
[5] -  Running                 nohup ./rel_5.sh >>a5.log 2>&1 &
[4]    Running                 nohup ./rel_4.sh >>a4.log 2>&1 &
[3]    Running                 nohup ./rel_3.sh >>a3.log 2>&1 &
[2]    Running                 nohup ./rel_2.sh >>a2.log 2>&1 &
[1]    Running                 nohup ./rel_1.sh >>a1.log 2>&1 &
 Here the rel_1 is calling the 1st update , rel_2 is calling the 2nd update like that...
Now, unfornutely speed of update is still very very slow...even though INDEX / statistics all looks fine...my questions are - 1> Is there any way to speedup the update ?
2> can we set the statistics to boost up the speed ?
Our target is to speed up the update in any way...Thanks is advance.. Regards INDRANIL , IBM

EXCEPTION
  WHEN OTHERS THEN
   Dbms_Output.put_line('Error '||sqlerrm);
end;
/

hi,Tom

YL1> select /*+ full(c) parallel(c) */ count(*) from cusm c;

  COUNT(*)
----------
     16169

Elapsed: 00:10:15.54
YL1> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bf3g673du1c93, child number 0
-------------------------------------
select /*+ full(c) parallel(c) */ count(*) from cusm c

Plan hash value: 2872246982

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |       |  3429 (100)|          |       |       |        |      |            |
|   1 |  SORT AGGREGATE        |          |     1 |            |          |       |       |        |      |            |
|   2 |   PX COORDINATOR       |          |       |            |          |       |       |        |      |            |
|   3 |    PX SEND QC (RANDOM) | :TQ10000 |     1 |            |          |       |       |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE     |          |     1 |            |          |       |       |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |          | 16169 |  3429   (0)| 00:00:42 |     1 |    36 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| CUSM     | 16169 |  3429   (0)| 00:00:42 |     1 |    36 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------

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

   6 - access(:Z>=:Z AND :Z<=:Z)


23 rows selected.

Elapsed: 00:00:00.04
YL1> select name, value
  2    from v$mystat s, v$statname n
  3   where s.statistic# = n.statistic#
  4     and n.name like '%parallel%';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
DBWR parallel query checkpoint buffers written                            0
queries parallelized                                                      0
DML statements parallelized                                               0
DDL statements parallelized                                               0
DFO trees parallelized                                                    0

Elapsed: 00:00:00.09


during the query running,in other session:
YL1> select count(*) from v$pq_slave;

  COUNT(*)
----------
         1
YL1> show parameter parallel_max_server

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     240

No query parallelized.
Any other parameters may caused this?

I have two other questions about parallel processing:
  1)How can I find the best DOP in a DW environment?
  2)Look at the following ,Why CURRENT_UTILIZATION greater than parallel_max_servers?
SQL> select * from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION   LIMIT_VALUE
------------------------------ ------------------- --------------- -------------------- --------------------
....
parallel_max_servers                           354             433        100                 3600

22 rows selected.

Elapsed: 00:00:00.01
SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
....
parallel_max_servers                 integer     100
parallel_min_percent                 integer     0
....

Thanks !

Dear Tom,

Kindly clarify my doubts.

1. When Parallel update is used, two rows for the same update statement appears in v$sql. Why? The only difference I could see on v$sql_shared_cursor is for DIFF_CALL_DURN column.

2. The elapsed_time on v$sql / DBA_HIST* for the parallel execution is sum of all slaves and parent sessions. Its misleading. How to find the parent sessions elapsed_time alone?

3. For parallel queries, if DOP is 4, 9 sessions run. Why?

Hi Tom,

For batch jobs, there are the following parallel execution strategies

1. Manual parallel execution from outside DB by multi-threading Java/C++ code

2. Manual parallel execution from inside DB by something like dbms_parallel_execute

3. Parallel enabled pipelined function

4. regular PL/SQL procedure, parallel DMLs

From my personal experience, if you can do 2, don't do 1, but 1 is easiest to use RAC to increase processing ability by simply adding node; if you can do 4, don't do 2. Between 4 and 2, the only major difference is 2 allows you to control chunking mechanism because you know more about your data than Oracle Optimizer.

But if it's just transforming a big table like 10 billion records, and both 4 and 2 can do the job, what would be your strategy? How can they easily take advantage of RAC if they run inside DB?

Thanks

Tom:

I was reading your "Do-It-Yourself Parallelism" approach.

I have some raw files (nearly 8,000 files) to be processed by some complex plsql validation.
The database has 16 cpu of dual core & no other process will be utilizing this cpu, while we run this complex validation.

Now we thought of appliying this DIYP. So we pulled all 8,000 file names into a table and used the below query to break it up into non-overlapping chuncks.

create table t(file_key number,file_name varchar2(40));

select file_key,ntile(8) over(order by file_key) as chunk_size
from t;

This query will break it up into 8 non-overlapping chunks. Now i need to open 8 sqlplus session and run the below piece of code?

where N=1,2,3...8.

begin
 for x in (select * from ( 
   select file_key,ntile(8) over(order by file_key) as chunk_size
   from t) 
   where chunk_size = N )
 loop
  dbms_job.submit(l_job_id,'complex_process(JOB)');
 end loop;
 commit;
end;
/

1) If we go by this, then session=1 will have 1000 raws files processing and session=2 will have another 1000 and so on. Does this cause any delay on CPU waits?
2) OR, do i need to run the above plsql block for N=1 and wait till it completes before N=2 is started?

Hi

Found an interesting article
http://blogs.oracle.com/datawarehousing/entry/in-memory_parallel_execution_i

Got curious if I can find out from explain plan whether buffer cache or direct data block access is used ?

Also wondered that if I have parallel query which is using joins or subqueries with nested loops to other tables, how are these tables accessed when in-memory parallel query is not used. Is buffer cache used when accessing them ?

Hi Tom,

We have 6 nodes participating in a RAC. Node 1 and 2 are configured to satisfy connections via a SERVICE.

We were thinking to use the parameter Parallel_force_local set to TRUE, so that jobs don't get spawned over both the nodes to reduce Interconnect traffic.

Some questions:

1) Lets suppose we have bandwidth of spawning 8 and 12 PX based on parallel_max_target on node 1 and 2.

There is a job which runs by a user whose restrict dop under a resouce_consumer_group needs 16 PX.

1.Will the job be spawned across 16 Slaves on both nodes 1 and 2?
2. Will the DOP of that SQL statement be reduced to 12 to satisfy Node 2 (un-used limits) because the parameter is set to TRUE.



Thanks

Dear Tom, 

I am trying to enable parallel processing to move massive data from old db design to a new db design.  I am the only user in the database so I think the parallel processing will be a good fit for us. But it seems I cannot get to run any query in parallel. The steps to move the data are as follows:

1. Enable parallel mode in tables and indexes with degree 4
   alter table Tgt_tab  parallel 4
   alter index Tgt_tab_idx parallel 4
2. Force the session to run in parallel
   alter session FORCE PARALLEL DML;
3. Use the append hint to do the insert 
   INSERT /*+ APPEND */ INTO Tgt_tab (ID, ....) 
   SELECT t1.* FROM src_tab1 t1, src_tab2 t2
   WHERE t1.id=t2.id;

The explain plan shows full scan on src_tab1 and index full scan on src_tab2. No parallel execution

We are running oracle 11gR2. I tried to test with your example and these are my results:

SQL> drop table t;

Table dropped.

SQL> create table t ( x int );

Table created.

SQL> exec dbms_stats.set_table_stats( user, 'T', numrows=>1000000, numblks =>100000 );

PL/SQL procedure successfully completed.

SQL> show parameter parallel;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      AUTO
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     80
parallel_min_percent                 integer     0
parallel_min_servers                 integer     8
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     32
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0
SQL> explain plan for select /*+ PARALLEL*/ * from t;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000K|    12M| 37566   (1)| 00:08:46 |
|   1 |  TABLE ACCESS FULL| T    |  1000K|    12M| 37566   (1)| 00:08:46 |
--------------------------------------------------------------------------

8 rows selected.

I hope you can shed some light here as I fail to see why the queries are not running in parallel. 

Thank you,
Daissy

Hi Tom, 

This is the result of my query:
SQL> select * from v$version
  2  ;

BANNER
------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

No, it is not Enterprise Edition

Tom,
Please disregard my recent request as I understood that the parallel feature is only available for EE.
Thank you though for the lead you gave as I could finally understand what was going on.
Thank you again,
Daissy

Hi,

As per

http://docs.oracle.com/cd/B28359_01/server.111/b28313/usingpe.htm#autoId33

"Enabling Parallel DML
A DML statement can be parallelized only if you have explicitly enabled parallel DML in the session with the ENABLE PARALLEL DML clause of the ALTER SESSION statement.
"
If the above statement is true, in the example below, why does the explain plan(shown below), show that the update is being done in parallel mode?

CREATE TABLE SK_TEMP
( PARTITION_KEY                NUMBER(10)    NOT NULL,
  OBJ_NUM                      NUMBER(10)    NOT NULL,
  OBJECT_NAME                  VARCHAR2(300) NOT NULL,
  OBJECT_NAME_DUP              VARCHAR2(300)) 
PARTITION BY HASH (PARTITION_KEY)
(  
  PARTITION SK_TEMP_P01
    TABLESPACE APISA_P01,  
  PARTITION SK_TEMP_P02
    TABLESPACE APISA_P02,  
  PARTITION SK_TEMP_P03
    TABLESPACE APISA_P03,  
  PARTITION SK_TEMP_P04
    TABLESPACE SK_TEMP_P04 
);

SELECT   


CREATE TABLE SK_TEMP
( PARTITION_KEY                NUMBER(10)    NOT NULL,
  OBJ_NUM                      NUMBER(10)    NOT NULL,
  OBJECT_NAME                  VARCHAR2(300) NOT NULL,
  OBJECT_NAME_DUP              VARCHAR2(300)) 
PARTITION BY HASH (PARTITION_KEY)
PARTITIONS 5;

INSERT INTO SK_TEMP
(PARTITION_KEY,
 OBJ_NUM,
 OBJECT_NAME)
(SELECT MOD(OBJECT_ID, 20) PARITION_KEY,
        OBJECT_ID,
        OBJECT_NAME
 FROM ALL_OBJECTS);
 
COMMIT;

ANALYZE TABLE SK_TEMP COMPUTE STATISTICS;

SQL> SET AUTOTRACE TRACEONLY
SQL> UPDATE /*+PARALLEL(T, 8) */ SK_TEMP T
  2  SET OBJECT_NAME_DUP = OBJECT_NAME
  3  WHERE PARTITION_KEY IN (1, 2, 3, 7,10);

21622 rows updated.


Execution Plan
----------------------------------------------------------                      
Plan hash value: 2704836492                                                     
                                                                                
--------------------------------------------------------------------------------
-----------------------------------------------                                 
                                                                                
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time    
 | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |                                 
                                                                                
--------------------------------------------------------------------------------
-----------------------------------------------                                 
                                                                                
|   0 | UPDATE STATEMENT      |          | 62025 |  2119K|    17   (6)| 00:00:01
 |       |       |        |      |            |                                 
                                                                                
|   1 |  UPDATE               | SK_TEMP  |       |       |            |         
 |       |       |        |      |            |                                 
                                                                                
|   2 |   PX COORDINATOR      |          |       |       |            |         
 |       |       |        |      |            |                                 
                                                                                
|   3 |    PX SEND QC (RANDOM)| :TQ10000 | 62025 |  2119K|    17   (6)| 00:00:01
 |       |       |  Q1,00 | P->S | QC (RAND)  |                                 
                                                                                
|   4 |     PX BLOCK ITERATOR |          | 62025 |  2119K|    17   (6)| 00:00:01
 |KEY(I) |KEY(I) |  Q1,00 | PCWC |            |                                 
                                                                                
|*  5 |      TABLE ACCESS FULL| SK_TEMP  | 62025 |  2119K|    17   (6)| 00:00:01
 |KEY(I) |KEY(I) |  Q1,00 | PCWP |            |                                 
                                                                                
--------------------------------------------------------------------------------
-----------------------------------------------                                                                                                          
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   5 - filter("PARTITION_KEY"=1 OR "PARTITION_KEY"=2 OR "PARTITION_KEY"=3 OR "PA
RTITION_KEY"=7 OR "PARTITION_KEY"=10)                                           
                                                                                

Statistics
----------------------------------------------------------                      
         52  recursive calls                                                    
      77866  db block gets                                                      
      14323  consistent gets                                                    
          0  physical reads                                                     
   13669348  redo size                                                          
        387  bytes sent via SQL*Net to client                                   
        407  bytes received via SQL*Net from client                             
          3  SQL*Net roundtrips to/from client                                  
          1  sorts (memory)                                                     
          0  sorts (disk)                                                       
      21622  rows processed                                                     

ops$tkyte%ORA11GR2> CREATE TABLE SK_TEMP
  2  ( PARTITION_KEY                NUMBER(10)    NOT NULL,
  3    OBJ_NUM                      NUMBER(10)    NOT NULL,
  4    OBJECT_NAME                  VARCHAR2(300) NOT NULL,
  5    OBJECT_NAME_DUP              VARCHAR2(300))
  6  PARTITION BY HASH (PARTITION_KEY)
  7  PARTITIONS 5;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> INSERT INTO SK_TEMP
  2  (PARTITION_KEY,
  3   OBJ_NUM,
  4   OBJECT_NAME)
  5  (SELECT MOD(OBJECT_ID, 20) PARITION_KEY,
  6          OBJECT_ID,
  7          OBJECT_NAME
  8   FROM ALL_OBJECTS);

72286 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> COMMIT;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'SK_TEMP' );

PL/SQL procedure successfully completed.


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2> UPDATE /*+PARALLEL(T, 8) */ SK_TEMP T
  2   SET OBJECT_NAME_DUP = OBJECT_NAME
  3    WHERE PARTITION_KEY IN (1, 2, 3, 7,10);

18065 rows updated.

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  3bfvt5f8c4hj7, child number 0
-------------------------------------
UPDATE /*+PARALLEL(T, 8) */ SK_TEMP T  SET OBJECT_NAME_DUP =
OBJECT_NAME   WHERE PARTITION_KEY IN (1, 2, 3, 7,10)

Plan hash value: 145170646

--------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |       |       |    18 (100)|
|   1 |  UPDATE               | SK_TEMP  |       |       |            |
|   2 |   PX COORDINATOR      |          |       |       |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000 | 51633 |  1764K|    18   (0)| 00:00:01
|   4 |     PX BLOCK ITERATOR |          | 51633 |  1764K|    18   (0)| 00:00:01
|*  5 |      TABLE ACCESS FULL| SK_TEMP  | 51633 |  1764K|    18   (0)| 00:00:01
--------------------------------------------------------------------------------

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

   5 - access(:Z>=:Z AND :Z<=:Z)
       filter(("PARTITION_KEY"=1 OR "PARTITION_KEY"=2 OR "PARTITION_KEY"=3 OR "P


24 rows selected.

ops$tkyte%ORA11GR2> select count(*) from sk_temp;

  COUNT(*)
----------
     72286

<b>the fact that I can read the table indicates that the update was *not* parallel.  Part of the plan was - but not the update...</b>


ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> alter session enable parallel dml;

Session altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> UPDATE /*+PARALLEL(T, 8) */ SK_TEMP T
  2   SET OBJECT_NAME_DUP = OBJECT_NAME
  3    WHERE PARTITION_KEY IN (1, 2, 3, 7,10);

18065 rows updated.

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  3bfvt5f8c4hj7, child number 5
-------------------------------------
UPDATE /*+PARALLEL(T, 8) */ SK_TEMP T  SET OBJECT_NAME_DUP =
OBJECT_NAME   WHERE PARTITION_KEY IN (1, 2, 3, 7,10)

Plan hash value: 2318138013

--------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |          |       |       |    18 (100)|
|   1 |  PX COORDINATOR       |          |       |       |            |
|   2 |   PX SEND QC (RANDOM) | :TQ10000 | 51633 |  1764K|    18   (0)| 00:00:01
|   3 |    UPDATE             | SK_TEMP  |       |       |            |
|   4 |     PX BLOCK ITERATOR |          | 51633 |  1764K|    18   (0)| 00:00:01
|*  5 |      TABLE ACCESS FULL| SK_TEMP  | 51633 |  1764K|    18   (0)| 00:00:01
--------------------------------------------------------------------------------

<b>see how the update step moved down INTO the parallel part of the plan...</b>


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

   5 - access(:Z>=:Z AND :Z<=:Z)
       filter(("PARTITION_KEY"=1 OR "PARTITION_KEY"=2 OR "PARTITION_KEY"=3 OR "P


24 rows selected.

ops$tkyte%ORA11GR2> select count(*) from sk_temp;
select count(*) from sk_temp
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

<b>and we cannot read the table until we commit - one of the side effects of a parallel DML statement...</b>

Hello Sir,

One of our Sr. developer says whenever you use parallel hint, always use with full hint. Is it so? even my query does full scan without specifying full hint, still do i need to give full hint? any reason for this?

Thanks

Hi Tom,

I would assume that any query using UNION is a good candidate for a parallel query. Because if you think about it the SQLs in the UNION can very well be executed independently of each other.

But why is it that most execution plan of UNIONs will not consider parallel query?

Also reading from your book I got the impression that you are not a fan of parallel query in an OLTP environment, although you did say that 'nothing is ever black and white".

Hi Tom,

"but they currently are not. Check back in a year maybe..."

Why are they not independent?

Why can't I?
1)Divvy up the 2 SQLs into 2 sessions(or PQ slaves), then
2)Assemble the result.

Hi, Tom can you explain how it's working.

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2

CREATE TABLE b AS select * from a
takes 10 seconds

ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2
ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4

CREATE TABLE b AS select * from a
takes 5 seconds

ALTER SESSION FORCE PARALLEL DDL PARALLEL 4
ALTER SESSION disable PARALLEL QUERY PARALLEL 4
takes same 5 seconds

v$px_process in any case shows 4 processes running.

1. Is there a way to see which process from v$px_process is doing DDL or QUERY? v$session.action always says "SQL Window - Query data of table"?

2. What algorithm Oracle uses for determining number and type of parallel processes when I try to force? Why not 2 for QUERY and 4 for DDL as I tried to force?
I noticed that if parallel degree for DDL and QUERY is different Oracle always uses degree of DDL. If I disable parallel ddl then Oracle uses degree of QUERY, but time of CREATE TABLE is the same as parallel query disabled.

3. In data warehouse I use following concept:
INSERT /*+ append */
INTO Dest_Table(...)
SELECT *
FROM TABLE(Parallel_Pipelined_ETL_Function(CURSOR(SELECT /*+ parallel(t1) */ * FROM Src_Table1 t1)))

Can I have x parallel processes for select, y for Pipelined functions and z for insert?
My testing shows that I can:
ALTER SESSION FORCE PARALLEL QUERY PARALLEL x
ALTER SESSION FORCE PARALLEL DML PARALLEL z

but number of pipelined funcions is allways the same like DML

4. Why parallel hints is not working for me? Only 'alter session ... force ...' works?

I know that preferred way is to leave everything to Oracle. But sometimes there are problems not with Oracle, but with people and you are forced to force Oracle. I can't change any parameters in database myself. And it is very very hard to convince administrators to change something. They told me they want to sleep quietly and do not want to use anything new from last two releases of Oracle DB and so on.
So I'm researching how it works and what results I can get myself

Hi Tom -
Great writting on DYI parallelism. Very creative approach.

I use PERCENTIAL_DISC to break data volumn from "big_table" into 4 groups, put them into loop to process (aggregate and load into GTT and then insert into final table)using dbms_job.

Finally I want to schedule to run the main procedure once a day. First run goes fine. Second run gets into DEAD LOCK between jobs while one is deleting "rslt_table" and another is inserting into "rslt_table". Please see the code snippet.

Question 1: why would job1 and job2 dead lock?
Question 2: how would I preseve them from deal lock?

-->> code snippet
/* split big_table by id into 4 groups to process with DIY - parallelism approach*/
BEGIN
FOR x IN (
SELECT
MIN (id) AS t1,
PERCENTILE_DISC (0.25) WITHIN GROUP (ORDER BY id) AS t2
FROM big_table
UNION ALL
SELECT
PERCENTILE_DISC (0.25) WITHIN GROUP (ORDER BY id) +1 AS t1,
PERCENTILE_DISC (0.50) WITHIN GROUP (ORDER BY id) AS t2
FROM big_table
UNION ALL
SELECT
PERCENTILE_DISC (0.50) WITHIN GROUP (ORDER BY id) +1 AS t1,
PERCENTILE_DISC (0.75) WITHIN GROUP (ORDER BY id) AS t2
FROM big_table
UNION ALL
SELECT
PERCENTILE_DISC (0.75) WITHIN GROUP (ORDER BY id) +1 AS t1,
MAX (id) AS t2
FROM big_table)
LOOP

--x_gtt1, x_gtt2, x_gtt3 and x_gtt4 are global temporary table build with ON COMMIT PRESERVE ROWS clause

strSQL :=
'BEGIN
INSERT /*+ APPEND */ INTO x_gtt'|| tbl_suffix ||'
SELECT
/*+ PARALLEL(te,8) */
te.id,
... -- fields
FROM
te,
t
WHERE
te.id_fk = t._id
AND t.id between '
|| x.t1
|| ' AND '
|| x.t2
|| ' GROUP BY
te.id,
t.id;
COMMIT; /* this commit prevents ORA-12838: cannot read/modify an object after modifying it in parallel. "A transaction containing a direct-path INSERT statement cannot be or become distributed." */

DELETE FROM rslt_table WHERE id BETWEEN ' || x.t1 || ' AND ' || x.t2 ||';'||'
COMMIT;

INSERT /*+ APPEND */ INTO rslt_table SELECT * FROM x_gtt'|| tbl_suffix ||';
COMMIT;
END;';

DBMS_JOB.submit(jobno, strSQL);
commit; --commit the job to kick off right now;

-- dbms_output.put_line('strSQL='||strSQL);
-- dbms_output.put_line('job='||jobno);

tbl_suffix := tbl_suffix + 1;
END LOOP;
END;


Thanks as always!
Prakash

Note to my last posting;

Actually there is no DEAD LOCK, just the jobs were blocking each other while one session is INSERTING the other is DELETING. Due to the heavy volume, it seems to me they were gone dead lock.

To patch the LONG BLOCKING, I added intermidate table to load data from all 4 GTTs and last job that gets to run TRUNCATE, INSERT to the final table.

DIY parallelism approach worked OK to me except it requires some level of settings but worth to try.

Pleaes suggest if you see other options to DELETE, INSERT with DIY parallelism.



Thanks,

Tom -

I played with rowid ranges and it sure computes many times faster than percential_disc usage. Though rowid ranges is limited in my world as my process has to run on different schema and access tables over synonyms.I sure see the benefits of rowid ranges.

From your questioning response, I applied bind variables and took off /*+ APPEND */ and intermidate tables. With all this changes I am happy with the response time.

I think I have some room to expand my DIY parallelism from 4 to 6 or more if needed.


Thanks,
Prakash

Hi Tom,

Forgot to mention that the reporting schema that will run the script has no access to dba_objects, dba_extents or any dba objects.

Would there be workaround in such setup?

Thanks,
Prakash

Hi Tom,

First of all thanks for your valuable responses.

Can you please let me know how to find the number of parallel slaves available in the database? It's a RAC database and version: 11gr2.

Thank you

ops$tkyte%ORA11GR2> show parameter parallel_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     160

Hi Tom,

Sorry for not being clear about my requirement. I want to determine number of parallel slaves available in an instance(at a given time) which we would use to decide whether to run a query or not. It is a 5-node RAC database.

Thanks Tom

Hi Tom,
THis is excerpts for the plan of bad query query which is dieing out because of the Temp space issue ( ORA-01652: unable to extend temp segment by 128 in tablespace TEMP )
we have sufficent temp tablespace space ( around 100gb)

----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
| | HASH JOIN | | 9 | 4752 | 79467 (1)| 00:15:54 | Q1,04 | PCWP | |
| 10 | BUFFER SORT | | | | | | Q1,04 | PCWC | |
| 11 | PX RECEIVE | | 8204K| 579M| 34250 (2)| 00:06:52 | Q1,04 | PCWP | |
| 12 | PX SEND HASH | :TQ10001 | 8204K| 579M| 34250 (2)| 00:06:52 | | S->P | HASH |
|* 13 | TABLE ACCESS FULL | t1 | 8204K| 579M| 34250 (2)| 00:06:52 | | | |
| 14 | PX RECEIVE | | 3510K| 1520M| 45197 (1)| 00:09:03 | Q1,04 | PCWP | |
| 15 | PX SEND HASH | :TQ10003 | 3510K| 1520M| 45197 (1)| 00:09:03 | Q1,03 | P->P | HASH |
|* 16 | HASH JOIN BUFFERED | | 3510K| 1520M| 45197 (1)| 00:09:03 | Q1,03 | PCWP | |
| 17 | BUFFER SORT | | | | | | Q1,03 | PCWC | |
| 18 | PX RECEIVE | | 4076K| 213M| 43007 (1)| 00:08:37 | Q1,03 | PCWP | |
| 19 | PX SEND HASH | :TQ10000 | 4076K| 213M| 43007 (1)| 00:08:37 | | S->P | HASH |
|* 20 | TABLE ACCESS FULL | t2 | 4076K| 213M| 43007 (1)| 00:08:37 | | | |
| 21 | PX RECEIVE | | 3192K| 1214M| 2177 (1)| 00:00:27 | Q1,03 | PCWP | |
| 22 | PX SEND HASH | :TQ10002 | 3192K| 1214M| 2177 (1)| 00:00:27 | Q1,02 | P->P | HASH |
| 23 | PX BLOCK ITERATOR | | 3192K| 1214M| 2177 (1)| 00:00:27 | Q1,02 | PCWC | |
| 24 | TABLE ACCESS FULL | t3 | 3192K| 1214M| 2177 (1)| 00:00:27 | Q1,02 | PCWP | |

Question/s

1. do parallel_max_servers integer and parallel_threads_per_cpu settings would have made this query to run out of temp space?
2. is it something if i have huge number of parallel_max_servers set in the instance and those parallel server would consume the temp.
3. is remedy is to reduce the parallel sevrer ?

yes Tom,
tables involved here has degree set to more then 1.

so true , if we want to run parallel then we would need to add disks...:)

Also,

q1)
how do we bencharmk how much big really i need temp. so i would do
a) allow autoextend on on temp
b) run the big query
c) when query ends , make autoextend off
at (c) we would get the maximum temp size we would need.

Q2) How the temp size allocation happens in case parallel query. I did some benchmark and found if I have noparallel query succeeds , but when i have set parallel degree at table then query run out of temp space... why Oracle does not try to maintain the temp usage ( within max limit) as if it did in single thread run?..

Tom
we ran the above serially ( no parallel)

In above plan we have TABLE ACCESS FULL t2 during which we see around 100GB temp is being used.

during full table access it has to filter some of the column of t2 .
but the filter is resulting again reading 90% of the table.

question

do the full table scan results into huge temp tablespace usage?

regards

Tom,
Just to add to it.

a) t1 and t2 are full scaned and hash joined later.

b) t2 table is just 2 Gb and t1 is around 1 GB

c) in session long ops t2 full scan was showing at the same time temp usage was increasing and touches 90GB.
filter on t2 is ( col1 = ? and col2 = ? and col3 <>? ), so during full scan candidate rows were selected from t2 based on the filter.

e) filter ( col1 = ? and col2 = ? and col3 <>? ) results in alomost reading the 95% of the table data. So indexing on these filter predicates didnt helped.

e) Once t2 full scan was over then temp usage remained at 90Gb and no further increase..

f) next long ops was hash join and so on ...

g) we have work_area policy AUTO and pga_aggregate_target 9GB.. and seen pga_usage around 4GB during above.



question.
1. How come full scan of 2GB table resulted in 90GB of temp usage? while participating in hash join with t1 .
2. is it pointing to any bug related with temp usage?

many thanks.

Hi Tom & Team,

Oracle 9207: Having a partitioned table with a partitioned index asociated to it, and gathering statistics with DBMS_STATS I found a pretty interesting thing.

Apparently while gathering table partition statistics, 16 sub-processes where spawned, but while gathering index partition statistics just the session process was in charge of performing that task.

Why is this happening? I specified 8 sub-processes to be used, not 16 and I thought that those 8 processes where going to process every request, not only table...

Used statetment: exec dbms_stats.gather_table_stats(ownname=>'&&OWNER_NAME',tabname=>'&&TABLE_NAME',GRANULARITY=>'ALL',estimate_percent=>0.1,degree=>8,cascade=>true);


I would appreciate any comment on this subject.

Tom,

As discussed   in the thread above 

<quote>......show me the plan, I don't see how a 2gb table would explode out to 90gb of time. </quote>.
I am furnishing the details,
This is real puzzling.


RDBMS : 11.2.0.2 
OS : Windows 2008


 

Batch job runs the below Statement ( through application)


INSERT      /*+ APPEND */INTO T_LOAD
            (rowid_object, rowid_object_matched, rowid_match_rule,
             automerge_ind)
   SELECT          /*+ USE_HASH (b1, b2) */
          DISTINCT b1.rowid_object, b2.rowid_object, 'SVR1.2Q5X', 1
              FROM t$mle_party b1,
                   (SELECT /*+ USE_HASH (C_MT_PARTY_IDENTIFIER , C_MT_PARTY_ADDRESS) */
                           c_mt_party_identifier.rowid_object,
                           c_mt_party_identifier.party_idnty_value,
                           c_mt_party_identifier.party_idnty_type_cd,
                           c_mt_party_address.party_type,
                           c_mt_party_address.party_name,
                           c_mt_party_address.country,
                           c_mt_party_address.bpa_string
                      FROM c_mt_party_identifier INNER JOIN c_mt_party_address
                           ON c_mt_party_address.rowid_object =
                                            c_mt_party_identifier.rowid_object
                     WHERE c_mt_party_address.party_type = 'ORG') b2
             WHERE b1.rowid_object != b2.rowid_object
               AND COALESCE (b1.party_idnty_value,
                             COALESCE (b2.party_idnty_value, 'S~!#*+')
                            ) =
                      COALESCE (b2.party_idnty_value,
                                COALESCE (b1.party_idnty_value, 'T~!#*+')
                               )
               AND COALESCE (b1.party_idnty_type_cd,
                             COALESCE (b2.party_idnty_type_cd, 'S~!#*+')
                            ) =
                      COALESCE (b2.party_idnty_type_cd,
                                COALESCE (b1.party_idnty_type_cd, 'T~!#*+')
                               )
               AND b1.party_name = b2.party_name
               AND b1.country = b2.country
               AND b1.bpa_string = b2.bpa_string;

It is resulting into

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP 
  


  
explain plan for the above is :      

Elapsed: 00:00:00.06
13:08:22 SQL> select * from  table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------ 

Plan hash value: 1115960413

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                |                      |     1 |   277 |       |   134K  (1)| 00:26:56 |
|   1 |  LOAD AS SELECT                 | T_LOAD         |       |       |       |            |          |
|   2 |   HASH UNIQUE                   |                      |     1 |   277 |       |   134K  (1)| 00:26:56 |
|*  3 |    FILTER                       |                      |       |       |       |            |          |
|   4 |     NESTED LOOPS OUTER          |                      |     1 |   277 |       |   134K  (1)| 00:26:56 |
|*  5 |      HASH JOIN                  |                      |     1 |   236 |    25M|   134K  (1)| 00:26:56 |
|*  6 |       HASH JOIN                 |                      |   136K|    23M|   128M| 77430   (1)| 00:15:30 |
|   7 |        TABLE ACCESS FULL        | T$MLE_PARTY          |  1130K|   115M|       |  3369   (1)| 00:00:41 |
|*  8 |        TABLE ACCESS FULL        | C_PARTY_ADDRESS      |  8204K|   578M|       | 34250   (2)| 00:06:52 |
|*  9 |       TABLE ACCESS FULL         | C_PARTY              |  4076K|   213M|       | 43007   (1)| 00:08:37 |
|* 10 |      TABLE ACCESS BY INDEX ROWID| C_PARTY_IDENTIFIER   |     1 |    41 |       |     6   (0)| 00:00:01 |
|* 11 |       INDEX RANGE SCAN          | SVR1_17K3            |     4 |       |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------- 

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

   3 - filter(COALESCE("B1"."PARTY_IDNTY_VALUE",COALESCE("C_MT_PARTY_IDENTIFIER"."IDENTIFIER_VALUE",'S~!#*+'))=COA
              LESCE("C_MT_PARTY_IDENTIFIER"."IDENTIFIER_VALUE",COALESCE("B1"."PARTY_IDNTY_VALUE",'T~!#*+')) AND
              COALESCE("B1"."PARTY_IDNTY_TYPE_CD",COALESCE("C_MT_PARTY_IDENTIFIER"."IDENTIFIER_TYPE_CD",'S~!#*+'))=COALESCE("C_M
              T_PARTY_IDENTIFIER"."IDENTIFIER_TYPE_CD",COALESCE("B1"."PARTY_IDNTY_TYPE_CD",'T~!#*+')))
   5 - access("B1"."PARTY_NAME"="PARTY_NAME" AND "BO"."ROWID_OBJECT"="C_MT_PARTY_ADDRESS"."PARTY_ROWID")
       filter("B1"."ROWID_OBJECT"<>"BO"."ROWID_OBJECT")
   6 - access("B1"."COUNTRY"="C_MT_PARTY_ADDRESS"."COUNTRY" AND
              "B1"."BPA_STRING"=NVL2("C_MT_PARTY_ADDRESS"."ADDRESS_LINE_1","C_MT_PARTY_ADDRESS"."ADDRESS_LINE_1"||'
              ','')||NVL2("C_MT_PARTY_ADDRESS"."ADDRESS_LINE_2","C_MT_PARTY_ADDRESS"."ADDRESS_LINE_2"||'
              ','')||NVL2("C_MT_PARTY_ADDRESS"."CITY","C_MT_PARTY_ADDRESS"."CITY"||'
              ','')||NVL2("C_MT_PARTY_ADDRESS"."STATE","C_MT_PARTY_ADDRESS"."STATE"||'
              ','')||NVL2("C_MT_PARTY_ADDRESS"."POSTAL_CODE","C_MT_PARTY_ADDRESS"."POSTAL_CODE"||'
              ','')||NVL2("C_MT_PARTY_ADDRESS"."POSTAL_PLUS_CODE","C_MT_PARTY_ADDRESS"."POSTAL_PLUS_CODE"||' ',''))
   8 - filter("C_MT_PARTY_ADDRESS"."HUB_STATE_IND"=1 AND "C_MT_PARTY_ADDRESS"."CONSOLIDATION_IND"<>9)
   9 - filter("PARTY_TYPE"='ORG' AND "HUB_STATE_IND"=1 AND "CONSOLIDATION_IND"<>9)
  10 - filter("C_MT_PARTY_IDENTIFIER"."IDENTIFIER_TYPE_CD"(+)='DUNS_NUMBER' AND
              "C_MT_PARTY_IDENTIFIER"."HUB_STATE_IND"(+)=1 AND "C_MT_PARTY_IDENTIFIER"."CONSOLIDATION_IND"(+)<>9)
  11 - access("BO"."ROWID_OBJECT"="C_MT_PARTY_IDENTIFIER"."PARTY_ROWID"(+))

40 rows selected.


Size - Objects 
Elapsed: 00:00:00.01 
13:15:54 SQL> select sum(bytes)/(1024*1024) from user_segments where segment_name ='C_PARTY'; 

SUM(BYTES)/(1024*1024) 
---------------------- 
                  1865 

Elapsed: 00:00:00.04 
13:16:06 SQL> select sum(bytes)/(1024*1024) from user_segments where segment_name ='C_PARTY_IDENTIFIER'; 

SUM(BYTES)/(1024*1024) 
---------------------- 
                  2174 

Elapsed: 00:00:00.06 
13:16:24 SQL> select sum(bytes)/(1024*1024) from user_segments where segment_name ='C_PARTY_ADDRESS'; 

SUM(BYTES)/(1024*1024) 
---------------------- 
                  1479 

Elapsed: 00:00:00.04 
13:16:37 SQL> select sum(bytes)/(1024*1024) from user_segments where segment_name ='T$MLE_PARTY'; 

SUM(BYTES)/(1024*1024) 
---------------------- 
                   148 
 

SQL> select  sql_text, DIRECT_WRITES from v$sql where direct_writes<>0 and users_executing <>0

SQL_TEXT                  DIRECT_WRITES
insert /*+ APPEND */ into T_LOAD….            3,611,355


-- db_block_size   8192

-- after few mins.. and it kept growing


SQL> SELECT sum(blocks)*8192 /1024/1024/1024 GB
  FROM v$tempseg_usage
  WHERE tablespace = 'TEMP'

GB
124.5703125
-- we have now 220 GB allocated

--
 
SQL>select sql_text,  direct_writes  from v$sql where sql_id = '3gyv1d9gd6h1c' ORDER BY DIRECT_WRITES DESC

SQL_TEXT               DIRECT_WRITES
insert /*+ APPEND */ into T_LOAD….      16060140


select  sql_text, DIRECT_WRITES from v$sql where direct_writes<>0 and users_executing <>0

SQL_TEXT                  DIRECT_WRITES
insert /*+ APPEND */ into T_LOAD….            3,611,355

  

SQL> select  segtype, sql_id, blocks*8192/1024/1024/1024 GB  from v$sort_usage order by 3 desc

SEGTYPE  SQL_ID    GB
HASH,    1qqkpxrfhc36w  123.30859375 --- SQL_ID  belongs to a procudure call ( which is a logger proc..) from the same session... !!
LOB_DATA  9wra2qj9c4k5s  0.0009765625
INDEX   9wra2qj9c4k5s  0.0009765625
LOB_DATA  9wra2qj9c4k5s  0.0009765625
DATA   1qqkpxrfhc36w  0.0009765625
LOB_DATA  2kv7w0dfp4117  0.0009765625
DATA   2kv7w0dfp4117  0.0009765625
LOB_DATA  2kv7w0dfp4117  0.0009765625
INDEX   2kv7w0dfp4117  0.0009765625
DATA   9wra2qj9c4k5s  0.0009765625
LOB_DATA  1qqkpxrfhc36w   0.0009765625

Tom,
further to add:

a) When the same insert statement as above is directly ran on DB ( thus exlduing application layer) it never resulted running out of temp.

b) When the job ( hence the above INSERT statement) is run from the application , we used the query:

SELECT a.username, a.sid, a.serial#, a.osuser,a.LOGON_TIME , b.tablespace, b.blocks, b.blocks*8192/1024/1024 USED_MB , c.sql_text, c.sql_id
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;

to monitor the temp usage.
initially until below FTS is 20%

|* 8 | TABLE ACCESS FULL | C_PARTY_ADDRESS | 8204K| 578M| | 34250

we saw this temp usage query clearly showing the Insert statement consuming the temp . after that above query does not show no rows but the temp usage kept increasing.
at the same time..
select segtype, sql_id, blocks*8192/1024/1024/1024 GB from v$sort_usage order by 3 desc

SEGTYPE SQL_ID GB
HASH, 1qqkpxrfhc36w 123.30859375 --- SQL_ID belongs to a procudure call ( which is a
logger proc..) from the same session... !!
..

1qqkpxrfhc36w is the SQL_ID belonging to a procedure call ( which a logger - it does not use any sort/hash/group by etc ) .

Question is
1. why child cursor(1qqkpxrfhc36w) is displayed in v$sort_usage under hash SEGTYPE, when it does not involve in using the temp ( here FTS was though continuing on C_PARTY_ADDRESS during the time v$sort_usage shoed SQL_ID of the child cursor )

2. What setting at application level might influnce the temp usage and hence more temp usage than the direct SQL*PLus Insert statment run? something related with curosr setting? [ we monitored the sessions and found only the procedure logger call is being made additionally( as mentioned in point (b) above) , but this procedure has no scope of temp usage ].

thanks Tom,

Here are the answers.

...what are your PGA settings?
pga aggregate target = 4GB
work area size policy = AUTO

in last review
...a) and is it using the same plan?
yes, both (a) and (b) are using the same plan.

comments.
1) When we ran the INSERT directly in SQL*PLus it didnt used even 1 iota of temp, looks everything was lucky to find space in memory ( PGA).. we have 9GB pga set and sum of the size of all these tables is not more than 9GB.

2) Looks application is setting ( we are checking this ..) work area size policy manul and individually setting hash and sort area and if these are set too small, then temp might explode to hundreds of GB.

your comments

regards

Hi,

Why this

select sum(result)
from(select /*+ parallel(t1) */ log(power(log(power(id,2)/id,10),3),2)+log(power(log(power(id,2)/id,10),3),2)result from t1)

takes longer than this (complex calculation on the pipelined function)

select sum(id)
from table(parallel_pipelined
(CURSOR(select /*+ parallel(t1) */ id
from t1)))

t1 (2 million rows)

Many thanks

Many thanks for your reply.

The calculation on the parallel_pipelined is exactly the same.

I tried cast float and there is no difference.

It seems doing the calculation on the parallel_pipelined uses less CPU.

I have quite a few queries doing this kind of calculations on many columns and I wonder if I should use parallel_pipelined to do so.

From the stats report

===============================================================================================
RunStats report : 29-JUN-2012 11:50:02
===============================================================================================
-----------------------------------------------------------------------------------------------
1. Summary timings
-----------------------------------------------------------------------------------------------
Type Name Run1 Run2 Diff
----- -------------------------------------------------- ------------ ------------ ------------
TIMER cpu time (hsecs) 1 4 3
TIMER elapsed time (hsecs) 7,814 13,876 6,062
Comments:
1) Run1 was 43.7% quicker than Run2
2) Run1 used 43.7% less CPU time than Run2
-----------------------------------------------------------------------------------------------
2. Statistics report
-----------------------------------------------------------------------------------------------
Type Name Run1 Run2 Diff
----- -------------------------------------------------- ------------ ------------ ------------
...
STAT physical read IO requests 2,440 2,440 0
STAT physical read bytes 282,468,352 282,468,352 0
STAT physical read total IO requests 2,440 2,440 0
STAT physical read total bytes 282,468,352 282,468,352 0
STAT physical read total multi block requests 2,427 2,427 0
STAT physical reads 34,481 34,481 0
STAT physical reads cache 0 0 0
STAT physical reads cache prefetch 0 0 0
STAT physical reads direct 34,481 34,481 0
...
LATCH redo writing 82 163 81
LATCH session allocation 321 404 83
LATCH object queue header heap 116 223 107
LATCH active service list 238 363 125
STAT bytes received via SQL*Net from client 611 749 138
LATCH library cache lock allocation 70 232 162
LATCH cache buffer handles 67 238 171
LATCH library cache pin allocation 85 275 190
LATCH object queue header operation 144 359 215
LATCH channel operations parent latch 377 662 285
LATCH process queue reference 16,215 16,527 312
LATCH messages 510 916 406
LATCH JS queue state obj latch 540 1,008 468
LATCH session idle bit 2,127 1,496 -631
LATCH enqueue hash chains 1,440 2,582 1,142
LATCH enqueues 1,402 2,611 1,209
LATCH shared pool simulator 1,132 2,783 1,651
LATCH checkpoint queue latch 2,087 3,829 1,742
LATCH library cache lock 1,591 4,346 2,755
LATCH library cache pin 1,275 4,271 2,996
LATCH shared pool 2,468 6,797 4,329
STAT session uga memory 196,488 203,880 7,392
LATCH library cache 6,462 21,315 14,853
STAT Cached Commit SCN referenced 26,782 571 -26,211
STAT CPU used when call started 58,443 102,861 44,418
STAT recursive cpu usage 58,424 102,855 44,431
STAT CPU used by this session 58,429 102,861 44,432
STAT session uga memory max 196,488 254,272 57,784
LATCH simulator hash latch 70,310 203,058 132,748
LATCH SQL memory manager workarea list latch 444,039 737,954 293,915
STAT session pga memory max 786,432 0 -786,432
STAT session pga memory -589,824 262,144 851,968
LATCH row cache objects 1,388,762 2,434,648 1,045,886
STAT consistent gets 1,573,400 36,422 -1,536,978
STAT consistent gets direct 1,571,459 34,481 -1,536,978
STAT no work - consistent read gets 1,571,443 34,465 -1,536,978
STAT session logical reads 1,573,400 36,422 -1,536,978
STAT table scan blocks gotten 1,571,459 34,481 -1,536,978
STAT recursive calls 1,571,234 845 -1,570,389
LATCH cache buffers chains 2,947,831 7,496,323 4,548,492
STAT table scan rows gotten 83,004,147 1,586,493 -81,417,654
-----------------------------------------------------------------------------------------------
3. Latching report
-----------------------------------------------------------------------------------------------
Type Name Run1 Run2 Diff
----- -------------------------------------------------- ------------ ------------ ------------
LATCH total latches used 4,891,602 10,945,762 6,054,160
Comments:
1) Run1 used 55.3% fewer latches than Run2
-----------------------------------------------------------------------------------------------
4. Time model report
-----------------------------------------------------------------------------------------------
Type Name Run1 Run2 Diff
----- -------------------------------------------------- ------------ ------------ ------------
TIME parse time elapsed 316 337 21
TIME PL/SQL execution elapsed time 8,143 7,374 -769
TIME DB CPU 267,534 144,606 -122,928
TIME sql execute elapsed time 430,322 143,631 -286,691
TIME DB time 431,404 144,606 -286,798

>> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

drop table t1;

create table t1
as
SELECT ID
FROM(
SELECT ROWNUM id
FROM ( SELECT 1 just_a_column
FROM dual
CONNECT BY LEVEL <= 2000000
))
WHERE ID NOT IN(1,10);

ALTER TABLE t1
add CONSTRAINT t1_PK PRIMARY KEY (id);

exec dbms_stats.gather_table_stats( user, 't1' )

drop type t2_tab_type;
drop type t2_type;

CREATE OR REPLACE TYPE t2_type
AS OBJECT (
id number,
text varchar2(30),
session_id number
);

create or replace type t2_tab_type
as table of t2_type;
/

create or replace
function parallel_pipelined( l_cursor in sys_refcursor )
return t2_tab_type
pipelined
parallel_enable ( partition l_cursor by any )
is
l_session_id number;
v_id t1.id%type;
begin
/* select sid into l_session_id
from v$mystat
where rownum =1;*/
loop
fetch l_cursor into v_id;
exit when l_cursor%notfound;
-- complex process here
pipe row(t2_type(log(power(log(power(v_id,2)/v_id,10),3),2)+log(power(log(power(v_id,2)/v_id,10),3),2), 'text', l_session_id));
end loop;
close l_cursor;
return;
end;
/

select sum(id)
from table(parallel_pipelined
(CURSOR(select /*+ parallel(t1) */ id
from t1)
));

-524599.237324657
01:40 mins

select sum(result)
from(
select /*+ parallel(t1) */ log(power(log(power(id,2)/id,10),3),2)+log(power(log(power(id,2)/id,10),3),2) result
from t1
);

-524599.237324657
02:56 mins

select sum(result)
from(
select /*+ parallel(t1) */ log(power(log(power(cast(id as float),2)/cast(id as float),10),3),2)+log(power(log(power(cast(id as float),2)/cast(id as float),10),3),2) result
from t1
);

-524599.237324657
02:56 mins

Many thanks.

ops$tkyte%ORA10GR2> set numformat 999999999.999999999
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select sum(id)
  2  from table(parallel_pipelined
  3            (CURSOR(select /*+ parallel(t1, 5) */  id
  4                      from  t1)
  5             ));

             SUM(ID)
--------------------
    -58890.523578653

Elapsed: 00:00:27.72
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select sum(result)
  2  from(
  3  select  /*+ parallel(t1, 5) */
  4  log(
  5    power(
  6        log(
  7            power(id,2d)/id,10d
  8               ),
  9            3d),
 10    2d)+
 11  log(
 12     power(
 13        log(
 14               power(id,2d)/id,
 15             10d),
 16            3d),
 17    2d) result
 18  from (select cast(id as binary_double) id from t1)
 19  );

         SUM(RESULT)
--------------------
    -58890.523578653

Elapsed: 00:00:01.17
ops$tkyte%ORA10GR2> set timing off

Many thanks for looking into it.
Hope you can find an explanation as we have lots of SQL queries doing similar calculations. It might help us to improve them. I would like to know why, more than only an empirical confirmation.

Cheers.

Ok, thanks! I wasn't aware of the use of binary double for better performance.

But still having better performance with the parallel pipelined function in the same conditions:

select sum(result)
from(
select /*+ parallel(t1, 5) */
log(
power(
log(
power(id,2d)/id,10d
),
3d),
2d)+
log(
power(
log(
power(id,2d)/id,
10d),
3d),
2d) result
from (select cast(id as binary_double) id from t1)
);

00:25 seconds
-524599.237324659

create or replace
function parallel_pipelined( l_cursor in sys_refcursor )
return t2_tab_type
pipelined
parallel_enable ( partition l_cursor by any )
is
l_session_id number;
v_id t1.id%type;
begin
/* select sid into l_session_id
from v$mystat
where rownum =1;*/
loop
fetch l_cursor into v_id;
exit when l_cursor%notfound;
-- complex process here
pipe row(t2_type(log(power(log(power(v_id,2d)/v_id,10d),3d),2d)+log(power(log(power(v_id,2d)/v_id,10d),3d),2d), 'text', l_session_id));
end loop;
close l_cursor;
return;
end;
/

select sum(id)
from table(parallel_pipelined
(CURSOR(select /*+ parallel(t1,5) */ cast(id as binary_double) id
from t1)
));
00:21 seconds
-524599.237324657


I guess 2d is the same as cast(2 as binary_double) ?

Many thanks.

Hi Tom,

Correct me if I am wrong, but is parallel processing somehow invokes extra extent allocation or any fragmentation for that matter?  

We have a table in production environment (Oracle9i EER 9.2.0.8.0) which is about 120 GB in size. There are two indexes one local, and the other global created on this table.

Now, we need to migrate this table and its dependencies to a new version (Oracle11g EER 11.2.0.2.0). We have created the indexes with PARALLEL degree 16.
But then, as a cost to the faster parallel process, we now find that the total space occupied by these indexes in target, is much larger than that in the source.

In source, for INDEXES:

SQL> SELECT SUM(BYTES)/1024/1024/1024 from dba_segments@<db_link_source> WHERE OWNER=<owner> AND SEGMENT_TYPE LIKE '%INDEX%';

SUM(BYTES)/1024/1024/1024
-------------------------
               56.4414063


In target, for INDEXES:

SQL> SELECT SUM(BYTES)/1024/1024/1024 from dba_segments WHERE OWNER=<owner> AND SEGMENT_TYPE LIKE '%INDEX%';

SUM(BYTES)/1024/1024/1024
-------------------------
               110.025391


The indexes are valid in both source and target and neither of them is compressed.

I missed a part: These are the only two indexes that this owner has.

Thank you Tom,

It was such an informative article.

Tom,

1) With benefits like automatic extent management and "extent trimming" why would people go for uniform space allocation while creating tablespaces.

As you say, the golden rule would be to understand the requirement first, but then can there be any requirement for uniform allocation at tablespace level. I guess it would require user predictibility, from which letting oracle to manage space should be much better.

One more thing,
if I create a LMT with autoallocate and then create a table having storage clauses such as initial and next, which one would override the other. Will it consider the initial, next values at the object level or will it consider the space management clause of the tablespace.

Thanks a lot for your help.

ops$tkyte%ORA11GR2> create table t ( x int ) storage( initial 2m next 1m minextents 5 );

Table created.

ops$tkyte%ORA11GR2> select extent_id, blocks*8/1024 from dba_extents where owner = user and segment_name = 'T';

no rows selected

ops$tkyte%ORA11GR2> insert into t values (  1 );

1 row created.

ops$tkyte%ORA11GR2> select extent_id, blocks*8/1024 from dba_extents where owner = user and segment_name = 'T';

 EXTENT_ID BLOCKS*8/1024
---------- -------------
         0             1
         1             1
         2             1
         3             1
         4             1
         5             1

6 rows selected.


ops$tkyte%ORA11GR2> 

Hi Tom,

I have a general question on parallelism on Joins. Recenly one of my seniors told me that for parallel joins to happen we need to have parallelism on both the tables.

Select /*+ FULL(a) full(b) parallel(a,4) parallel(b,4) */ a.* from
emp a,dept b
where a.dept_no = b.dept_no;

But if we do

Select /*+ FULL(a) full(b) parallel(a,4) no_parallel(b) */ a.* from
emp a,dept b
where a.dept_no = b.dept_no;

The query will have large waits on serial to parallel conversion or vice versa.

Say the emp table is huge and size is about 5 GB and dept table is quite small about 50 MB . So parallelism on dept table is not necessary ,(full scan of dept will take few seconds).

1. Is it really required that both table must be of similar parallelism for a parallel join to happen.
2. If one is parallel and other is serial will we have parallel to serial wait event hampering the code.

Regards,
Snehasish das.