noparallel at table level
May 2, 2005 - 2pm Central time zone
Reviewer: Eugene
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
Followup May 2, 2005 - 7pm Central time zone:
yes
I dont agree with
May 3, 2005 - 2am Central time zone
Reviewer: The Human Fly from Saudi Arabia
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?
Followup May 3, 2005 - 1pm Central time zone:
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 /*+ parallel(t) */ 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
For The Human Fly
May 3, 2005 - 4am Central time zone
Reviewer: Muhammad Riaz Shahid from Dubai, UAE
Try SELECT /*+parallel(TableName,n) */ FROM TableName
where n=2,3,......
Followup May 3, 2005 - 1pm Central time zone:
or just parallel( tablename )
Ignoring PARALLEL hints
August 6, 2005 - 8am Central time zone
Reviewer: Suvendu from Bangalore , INDIA
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 sessions statement
showing parallel hint : insert /*+ append parallel(i,4) */ into abc_partition_tab
2) Why its not using parallel insertion?
3) Does APPEND hint dont allows to parallel insertion?
I dont have any explicitly PARALLEL parameter setting on database. As Im 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 Im wrong.
Every one will happy on this thread, if you elaborate more about same with some good example.
Thanking you,
Suvendu
Followup August 6, 2005 - 9am Central time zone:
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)
Unclear on PDML and parallel settings
August 17, 2005 - 5am Central time zone
Reviewer: A reader from South Africa
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.
Followup August 17, 2005 - 1pm Central time zone:
alter session enable parallel dml;
is a prerequisite for parallem DML, without it - won't matter WHAT parameters you have set.
for SELECT, the parallel parameters in the init.ora plus - parallel on the table, parallel hint --
would be enough.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c20paral.htm#365 you might want to read that and the chapters in the data warehousing guide as well
I seriously DOUBT that:
...all SELECT, DELETE and
INSERT statements will run in parallel for whoever connects to the DB....
is something you actually want to have happen in real life. parallel execution is a tool, one that
needs to be used carefully.
integrity constraints with append hint
August 19, 2005 - 5pm Central time zone
Reviewer: Jagannath Dalvi from VA, USA
Tom,
APPEND hint will ignore integrity constraints..
Is that the same behaviour for PARALLEL as well?
Followup August 20, 2005 - 4pm Central time zone:
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
that shows we did a direct path, now:
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
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:
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>
we did not direct path because of the foreign key
One more thing...
August 19, 2005 - 5pm Central time zone
Reviewer: Jagannath Dalvi from VA, USA
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
Followup August 20, 2005 - 4pm Central time zone:
not following you here?
if you use parallel direct path insert (remember to alter session to enable parallel dml!) Oracle
will use parallel execution servers - each in their own session to do the work in parallel.
Constraints and parallel DML
August 22, 2005 - 10am Central time zone
Reviewer: Jagannath Dalvi from VA, USA
Reg. Constraints: Yes, I meant by referential integrity constraints, sorry being not very clear on
the problem.
About the second problem of parallel DMLs, we are using 9.2.0.4 and there is a bug:
<<<
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 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
Followup August 23, 2005 - 4am Central time zone:
but still you said it very wrong. they are NOT IGNORED, they are very very much obeyed.
you can use conventional path sql and break your inserts up of course. If you have referential
integrity enabled, direct path is not going to happen.
Thanks
August 23, 2005 - 9am Central time zone
Reviewer: Jagannath Dalvi from VA, USA
I end up doing BULK INSERTS in chunk of rows.
Let's see once we migrate to 10g!
Thanks,
Jagan
Help on performance
September 19, 2005 - 12pm Central time zone
Reviewer: JD from VA, USA
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
Followup September 19, 2005 - 1pm Central time zone:
did you enable PDML before doing that? else it was a conventional path, serial insert (perhaps with
a parallel query).
with pdml, that insert would be using APPEND (direct path) and would be very different.
you cannot just nilly willy remove global indexes - you might make the load go faster but
absolutely KILL retrieval There was a design reason to use global, you have to find out why.
Lets say you have a table with 100 partitions, partitioned by date.
You have a GLOBAL index on last_name, range partitioned into 50 partitions.
You query: select * from t where last_name = :x;
we can find the EXACT single index partition to range scan, scan it and find the row(s).
Now, you make that global index on last_name a local one......
Now, that query has to
INDEX RANGE SCAN 100 INDEXES in order to find the row(s)!! Your retrieval would die.
PDML is not enabled
September 19, 2005 - 2pm Central time zone
Reviewer: JD from VA, USA
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
Followup September 19, 2005 - 4pm Central time zone:
have you tried using APPEND instead of a conventional path insert.
Thanks
September 21, 2005 - 9am Central time zone
Reviewer: JD from VA, UAS
I will try APPEND as well.
How about global partition index?
September 23, 2005 - 4pm Central time zone
Reviewer: JD from VA, USA
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?
Followup September 23, 2005 - 8pm Central time zone:
you do not make choices between global and local based on load speeds,
their use is driven by RETRIEVAL needs, not much else.
Sorry.. please ignore the above post
I meant to say
.
September 23, 2005 - 4pm Central time zone
Reviewer: JD from VA, USA
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?
Followup September 23, 2005 - 9pm Central time zone:
it'll still be a global index - so what was the motivation?
you'll either have 1 index partition to maintain or N index partitions to maintain?
What about Parallel PLSQL?
September 27, 2005 - 7pm Central time zone
Reviewer: Andrew from Wellington, New Zealand
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.)
Followup September 27, 2005 - 8pm Central time zone:
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.
JOB_QUEUE_PROCESSES set to 0 ?
September 27, 2005 - 10pm Central time zone
Reviewer: Arul Ramachandran from Bay Area, CA
"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 ?
Followup September 28, 2005 - 9am Central time zone:
indeed, thanks
How to calculate parallel_max_servers value?
September 28, 2005 - 10pm Central time zone
Reviewer: sami
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.
Followup September 29, 2005 - 6am Central time zone:
1) I'd say 16 is much closer to correct than 100, yes.
2) let it default. use the auto tuning stuff. set FEWER, not MORE parameters. in 9i, give parallel
automatic tuning (not needed in 10g) and parallel adaptive multi user a chance.
having 100 processes all running simultaneously with 8 cpus must "squash" that machine.
(please *test*)
Thanks for the detailed information
September 30, 2005 - 11am Central time zone
Reviewer: JD from VA, USA
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
October 10, 2005 - 6am Central time zone
Reviewer: Ik from BG
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,
Followup October 10, 2005 - 8am Central time zone:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/tuningpe.htm#19664
excellent chapter to read.
the parallel on the create table part was sufficient.
I believe you mean "parallel DML modification (update, delete, merge) operations are supported
without the need to physically partition the table"
parallel QUERY worked with or without partitioning and did not parallelize only on partition
boundaries.
To have parallel plans generated - parallel query needs to be configured (init.ora) and you either
hint it or have the base tables be "parallel" or use a sql statement that is parallelized like that
CTAS.
Try using parallel_index hint
October 10, 2005 - 3pm Central time zone
Reviewer: JD from VA, USA
October 11, 2005 - 5am Central time zone
Reviewer: Ik from BG
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,
Followup October 11, 2005 - 6am Central time zone:
The parallel on the create table (not the hint) was sufficient to create the table in parallel.
That select has the opportunity to bypass the buffer cache and do direct reads, yes.
Thanks very much
October 11, 2005 - 12pm Central time zone
Reviewer: Ik from BG
Parallel query in 10g
December 13, 2005 - 10am Central time zone
Reviewer: John Calvin from Annapolis, MD
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.
parallel_min_percent
March 13, 2006 - 11am Central time zone
Reviewer: Libo Zhang from St Louis, MO
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
Direct read
April 7, 2006 - 11am Central time zone
Reviewer: A reader
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,
April 10, 2006 - 1pm Central time zone
Reviewer: A reader
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,
Followup April 11, 2006 - 10am Central time zone:
I do not respond to each and every review/followup - sometimes I'm just going over them too fast to
give a meaningful answer.
the logical reads likely come from any index access you are doing, direct reads are for "big"
things, not small things (and likely for small things you would much prefer to go to the cache than
to disk over and over)
April 11, 2006 - 11am Central time zone
Reviewer: A reader
Thanks Tom.
April 13, 2006 - 9am Central time zone
Reviewer: Alex
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.
Followup April 14, 2006 - 11am Central time zone:
it would be because your parallel hints were not "valid" - there would have been something
preventing them from being used.
for example:
create table t ( x int );
create index t_idx on t(x);
select /*+ INDEX( T T_IDX ) */ * from t order by x;
note: updated the above sample query because the first one was wrong :) Thanks Alberto for the
email.... I used to have "where x > 0" which of course could use the index - meant "order by x"
which cannot use the index!
that will refuse totally to use the index - why? because X is nullable and an entirely null entry
in a b*tree index is not made, so the index isn't indexing all of the rows.
There must be something invalid about your hint.
April 14, 2006 - 12pm Central time zone
Reviewer: Alex
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.
Transaction Free Lists
April 17, 2006 - 9am Central time zone
Reviewer: Naresh from Cyprus
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.
Followup April 17, 2006 - 10am Central time zone:
from note 1029850.6
.....
The Transaction Free Lists:
===========================
Implicitly allocated by Oracle as needed. A Transaction Free List is a free
list dedicated to one transaction and only one. There are a minimum of 16
transactions free lists per segment, and this number increases as long as it
is necessary, until it reaches the limit of the Segment Header block size.
A transaction needs to allocate a Tx Free Lists entry when:
o It releases space in a block (DELETE or UPDATE)
o And if it has not already allocated one.
..........
Documentation Bug ?
April 17, 2006 - 1pm Central time zone
Reviewer: Jonathan Lewis from UK
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.
rownum effects
April 18, 2006 - 8am Central time zone
Reviewer: Naresh from Cyprus
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
Followup April 18, 2006 - 10am Central time zone:
the count step was where the rownum was assigned - by default parallel insert like that will be
direct pathed.
Documentation Bug - revisited.
April 18, 2006 - 1pm Central time zone
Reviewer: Jonathan Lewis from UK
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.
Followup April 18, 2006 - 3pm Central time zone:
I loved that last paragraph :)
<quote>
So even though I'm wrong (about INITRANS), I ought to be right.
</quote>
thanks for the followup, really appreciate it.
HOW CAN WE VERIFY
May 12, 2006 - 2pm Central time zone
Reviewer: jasdeep from las vegas
how can we verify that parallel in insert has been used or not.
will it show in explain plan.
can u give an example
Followup May 12, 2006 - 9pm Central time zone:
just try to query the table :) it'll fail if it was parallel
How can we verify
May 15, 2006 - 11am Central time zone
Reviewer: jasdeep
Fail ?
why ?
Followup May 15, 2006 - 11am Central time zone:
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
How to check if executed parallely or not
July 6, 2006 - 4am Central time zone
Reviewer: Shalini from India
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.
Followup July 8, 2006 - 9am Central time zone:
depends on the settings of the underlying tables. Are they parallel enabled - did you enable
parallel dml in your session.
query v$sql_plan to see the plan that has been used for the query.
Max parallel processes
July 10, 2006 - 5pm Central time zone
Reviewer: Fat geek from Auckland, New Zealand
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;
Followup July 11, 2006 - 7pm Central time zone:
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?
parallel_max_proceses
July 16, 2006 - 12am Central time zone
Reviewer: Fat Geek from Auckland New Zealand
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.
Followup July 16, 2006 - 9am Central time zone:
think about what would happen on a 4 cpu machine with 80 things going on at the same time.
I know that is what would default - but 80 things just cannot "happen" on so few cpu's.
create index in parallel
August 4, 2006 - 8am Central time zone
Reviewer: Yoav
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
Followup August 4, 2006 - 8am Central time zone:
parallel_max_servers 0
that sort of says it all doesn't it :)
that is how to disable parallel operations....
ORA-12838 Cannot read/modify an object after a parallel modification...
August 17, 2006 - 4pm Central time zone
Reviewer: Pete Beer from USA
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.
Direct Path and in parallel
August 20, 2006 - 4am Central time zone
Reviewer: Arindam Mukherjee from Kolkata, India
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 */?
Concrete Proof
August 20, 2006 - 8am Central time zone
Reviewer: Arindam Mukherjee from Kolkata, India
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.
Parallel DDL but no parallel DML
August 21, 2006 - 1pm Central time zone
Reviewer: Dave from Ripon, Yorkshire UK
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
Followup August 27, 2006 - 9am Central time zone:
got plans? (format them so we can read them please)
parallel DML
October 13, 2006 - 12am Central time zone
Reviewer: A reader
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
Followup October 13, 2006 - 7am Central time zone:
updates/deletes do not direct path, they read/write into the "middle" of tables - things that can
write above the high water marks can direct path.
Parallel ctas
November 16, 2006 - 8am Central time zone
Reviewer: Ramprasad from India
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
Followup November 16, 2006 - 3pm Central time zone:
why do you think it is not?
Parallel CTAS
November 17, 2006 - 7am Central time zone
Reviewer: Ramprasad from India
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
Followup November 17, 2006 - 8am Central time zone:
the rows are fed back from the parallel execution servers to the query coordinator and written out
- the temp segments you see used by the parallel execution servers are for the intermediate hash
joins
Duplicating rows in a table with 1.1 billion records
November 20, 2006 - 6pm Central time zone
Reviewer: raj from NJ, USA
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..
Followup November 22, 2006 - 2pm Central time zone:
curious, why are you writing code for what should and could and must be done with a single sql
statement?????
if you stop writing procedural code when you don't need to, you'll be more efficient and so will
the database.
v
January 24, 2007 - 3am Central time zone
Reviewer: Bhagat Singh from New Delhi, India
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
February 1, 2007 - 7pm Central time zone
Reviewer: Chi H from California
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?
Followup February 2, 2007 - 10am Central time zone:
with adaptive multi-user - it would not be the case that they take up all of the parallel execution servers - it tries to be fair.
can you be more specific
February 2, 2007 - 6pm Central time zone
Reviewer: Chi H from California
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.
Parallel process
February 20, 2007 - 11pm Central time zone
Reviewer: Rajesh from India
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
Parallel_enable in a package ??
April 16, 2007 - 3pm Central time zone
Reviewer: Maverick
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,
Followup April 17, 2007 - 9am Central time zone:
that is not what 'parallel enable' does, plsql is not a multi-threaded programming language (and even though java is, java in the database is not really multi-threaded either, it is a non-preemptive multitasking implementation)
parallel enable would allow your plsql package to be invoked in a parallel query, that is all.
plsql is a "linear", sequential programming language.
calling a "logging routine" should not impact your runtime measurably as far as the end user is concerned, keep it 'linear'
if you had something to invoke that took a long time and the end user didn't need to wait for it to complete, we would use AQ (advanced queues)
Thanks
April 17, 2007 - 10am Central time zone
Reviewer: Maverick
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
Followup April 17, 2007 - 10am Central time zone:
using AQ would be very similar to using dbms_job, it does not seem you want loosely coupled processing here.
why not code your logging routine and measure it's impact, that is, figure out if you have a problem to solve before solving the problem.
I can do that..but
April 17, 2007 - 12pm Central time zone
Reviewer: A reader
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,
Followup April 18, 2007 - 11am Central time zone:
no, that is not the way we approach problems, you don't design a fix for a theory.
the fix adds a level of complexity you just don't want to get into. unless and until you need it.
You don't have to simulate many users, you can take many measurements before hand.
http://asktom.oracle.com/tkyte/runstats.html
Bump for a Review above
August 13, 2007 - 12pm Central time zone
Reviewer: Mark Brady from Baltimore, MD USA
<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.
Enabling PDML in a session
February 18, 2008 - 5pm Central time zone
Reviewer: Sri from USA
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
Followup February 18, 2008 - 6pm Central time zone:
3 PARALLEL_TO_SERIAL
you have a parallel full scan that is serialized to an update.
versus, a parallel update
consider the differences in plans:
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.
also consider this:
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
Parallel Processing
February 24, 2008 - 6am Central time zone
Reviewer: Sri from USA
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
Followup February 24, 2008 - 11am Central time zone:
it means it is either
a) processing your update ( which might will include a read/query component)
b) processing your select
Just like a dedicated server can only do one thing at a time.
New Orcale formula to calculate PARALL_MAX_SERVER 10gR2
March 6, 2008 - 4am Central time zone
Reviewer: Yoav
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.
Followup March 6, 2008 - 8am Central time zone:
yes, i do - based on number of cpu's, the power of two
Parallel parameters
May 23, 2008 - 12am Central time zone
Reviewer: Chintu from FL,US
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
Followup May 23, 2008 - 9am Central time zone:
well, they are all documented - that'll tell you the impact of setting each one.
"should" - if they always should, it would be impossible to do otherwise, so they do not have to.
I let as many things as possible default, like all of them.
May 23, 2008 - 9am Central time zone
Reviewer: Chintu from FL,US
Tom, what do you mean by
"should" - if they always should, it would be impossible to do otherwise
Followup May 23, 2008 - 4pm Central time zone:
You wrote:
...
Should parallel_min_servers = parallel_max_servers*parallel_min_percent/100
....
I answered
If it SHOULD be, it WOULD be - we would not permit anything else. Just hooked on semantics here - you asked "should x=f(y)". I said "if x SHOULD equal f(y) always, we would make sure it did and you would not have any say in it"
meaning, it could, it doesn't have to
export in parallel
June 7, 2008 - 10pm Central time zone
Reviewer: LindaC from P.R., USA
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?
Followup June 9, 2008 - 11am Central time zone:
you will have to "do it yourself"
you would run multiple export sessions on the host database (9i) exporting different bits of the database (say schema by schema, many schemas at the same time)
June 9, 2008 - 3pm Central time zone
Reviewer: A reader
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.
Followup June 9, 2008 - 3pm Central time zone:
if you are using automatic pga memory management and you are using a dedicated server connection, you need not set sort/hash area size, they'll be derived based on current workload and your pga aggregate target settings.
if you use shared server in 9i - then we use sort/hash area size regardless of whether automatic memory management is on. so there you would set them.
if you use shared server in 10g and above - we use automatic memory management so you need not set them if you are using that.
June 9, 2008 - 3pm Central time zone
Reviewer: LindaC from P.R, USA
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.
Import Buffer or recordlength
June 9, 2008 - 3pm Central time zone
Reviewer: LindaC from P.R, USA
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.
Followup June 9, 2008 - 3pm Central time zone:
... I have modified the parameters in the databae to make
it bigger during the import.
..
that doesn't make sense to me, sorry - what parameters have you modified in the database for import???
Log_checkpoint_interval
June 9, 2008 - 3pm Central time zone
Reviewer: LindaC from P.R, USA
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.
Followup June 9, 2008 - 4pm Central time zone:
you would only play with this stuff if you are waiting on redo oriented events
and since you'll use commit=n, you won't be.
where did you get that advice from by the way - I'd be curious to see it in context.
buffer or recordlength
June 9, 2008 - 9pm Central time zone
Reviewer: LindaC from P.R, USA
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
Followup June 9, 2008 - 9pm Central time zone:
I'm not sure what you are doing or why.
leave underscore parameters ALONE, just stay away.
buffer or recordlength for import
June 9, 2008 - 9pm Central time zone
Reviewer: LindaC from P.R, USA
Also I would like to be sure if it is ok to use both parameters for the import.
Followup June 9, 2008 - 9pm Central time zone:
sure, it is "ok"
underscore parameters
June 9, 2008 - 10pm Central time zone
Reviewer: LindaC from P.R, USA
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.
Create index in parallel only one query running parallel
June 11, 2008 - 2pm Central time zone
Reviewer: LindaC from P.R. USA
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.
Followup June 11, 2008 - 8pm Central time zone:
...
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.
....
you set adaptive multi user on, it would seem you started one index, it consumed the resources and the others said "oh well, maybe next time, lets just go it on our own"
as designed.
If you wanted to run many of them at the same time, you would use an explicit parallel degree on them, and use a number less than 16
PArallelism parameters
June 13, 2008 - 11am Central time zone
Reviewer: LindaC from P.R. USA
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?
Followup June 13, 2008 - 11am Central time zone:
you don't need to set anything, it is already set by default, the defaults should be used unless you have a really good reason to override them.
Parallelism ver 9 and 10g
June 16, 2008 - 10am Central time zone
Reviewer: LindaC from P.R., USA
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 ?
Followup June 16, 2008 - 1pm Central time zone:
I suppose 1g means "11g"
If you have access to my book "Expert Oracle Database Architecture", I cover parallelism
The data warehousing guide does as well - suggest you read through it for sure.
10g sets it up by default as well.
PDML on a non-partitioned table with bitmap indexes
June 23, 2008 - 11am Central time zone
Reviewer: Pushkar from India
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.
Followup June 23, 2008 - 1pm Central time zone:
you just did a direct path load (parallel implies APPEND) but not parallel.
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.
No parallel, but we did a direct path load...
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.
get rid of index...
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.
and we get parallel...
parallel query is implemented via parallel execution servers which under unix will show up as pNNN processes and when active will have a session in v$session - yes. But the pNNN processes will hang around, we don't have to always create and recreate them.
Execution time and Parallel processing
June 24, 2008 - 10am Central time zone
Reviewer: A reader
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
Followup June 24, 2008 - 11am Central time zone:
1) it is called "contention", you must be contending with yourself for some resource
o memory
o cpu
o disk IO
o enqueues (locks)
2) you are doing your own parallelism here! None of our parallel parameters apply, you just have two different user sessions doing work - it is not "parallel" in the Oracle sense, it is "do it yourself" parallel
3) see #1, contention. A rollback is a logical "undo". For every row you insert, rollback will delete. For every row you delete, rollback will insert, for every row you update, rollback will un-update.
4) sure, but initrans is already "2" and you have "2", so it won't do anything
5) that changed, initrans now defaults to "2" for everything.
In order to get something in parallel running "fast" you have to ensure
a) you have sufficient resources, cpu, memory, io for both to run at the same time without exceeding the amount of that resource
b) you have split to workload in a manner that makes it so the two jobs do not contend with each other - that is, one of them works on the "top half" of the table and the other on the "bottom half" (if you were reading from a staging table - it would be nice if one of them read the first half and the other the second half so they do not contend with each other)
You mentioned bitmap indexes, I hope you have none during this time - they will lock you up tighter than a drum.
One approach to see what is being contended for would be to run the serial processes with 10046 level 12 tracing and then run them together with the same.
Then use tkprof to see what is different between the two runs.
Detrimental impact of parallelism
September 12, 2008 - 11pm Central time zone
Reviewer: A reader
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,
Default parallel clause
September 15, 2008 - 4pm Central time zone
Reviewer: A reader
Hi Tom,
Could you please help about having default parallel clause (without DOP)for all tables in DW environment?
Thanks
Followup September 16, 2008 - 10pm Central time zone:
what help do you need exactly?
parallel dop
September 17, 2008 - 6am Central time zone
Reviewer: A reader
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
Followup September 17, 2008 - 9am Central time zone:
in order to do a parallel load - you actually have to call out for it, explicitly, so yes - it would.
Yes, it will consider parallel execution if you say consider parallel execution.
Detrimental impact
September 17, 2008 - 4pm Central time zone
Reviewer: A reader
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,
Followup September 17, 2008 - 7pm Central time zone:
it can have
a) a positive impact
b) a negative impact
c) no impact whatsoever either way.
Use your knowledge to do a parallel direct path load - or not. You have to explicitly ask for it after all.
parallel_automatic_tuning
November 19, 2008 - 5am Central time zone
Reviewer: A reader
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,
Followup November 24, 2008 - 10am Central time zone:
yes, as of 10gr1
... Oracle Database provides defaults for the parallel execution initialization parameters that are adequate and tuned for most situations. The PARALLEL_AUTOMATIC_TUNING initialization parameter is now redundant and has been deprecated. ...
define "some improvement" - you should be able to tell us "what is different" between it being on or off.
Serialized update
November 19, 2008 - 12pm Central time zone
Reviewer: Ian from Amsterdam
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
Followup November 24, 2008 - 10am Central time zone:
can you give us the dbms_metadata.get_ddl for both pairs of tables.
Re question above
November 19, 2008 - 12pm Central time zone
Reviewer: Ian from Amsterdam
Tom - forget to say - yes I am doing alter session enable parallel dml; for both of them.
Regards
Ian
Bad hair day
November 21, 2008 - 6am Central time zone
Reviewer: Ian from Amsterdam
Tom
Regarding the above question - version is 9.2.0.7
Regards
Ian
Metadata
November 25, 2008 - 9am Central time zone
Reviewer: Ian from Amsterdam
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
Followup November 25, 2008 - 12pm Central time zone:
but that gives me nothing to play with - the test with.
ok, compare everything in the dictionary for the two sets of tables - statistics, parallel settings, everything.
Double checked everything
November 25, 2008 - 2pm Central time zone
Reviewer: Ian from Amsterdam
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
Parallel Query: v$SQL.PX_SERVERS_EXECUTIONS
December 10, 2008 - 6am Central time zone
Reviewer: Suvendu from Bangalore, INDIA
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>
Followup December 10, 2008 - 9am Central time zone:
you did not actually ever RUN the query.
you did an explain plan on it???
After running the query, there is no update on PX_SERVERS_EXECUTIONS column
December 11, 2008 - 1am Central time zone
Reviewer: Suvendu from Bangalore, INDIA
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
Followup December 11, 2008 - 7am Central time zone:
table was empty.
we knew that when we read the segment header.
so - it said "yeah, whatever, no parallel really needed, we'll just do serial"
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%'
TKprof outout..
December 11, 2008 - 9am Central time zone
Reviewer: Suvendu from Bangalore, INDIA
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)
Followup December 11, 2008 - 9am Central time zone:
that was the plan - we just didn't feel the need at runtime to actually do anything in parallel - it has that choice. So the PX column in v$sql was dead accurate - it told you "we didn't use any".
The 3 reads you see where the reads of the segment header which told the coordinator "you are done, nothing to see here, return the answer". The coordinator never needed to shoot anything down to any execution servers.
If you actually put data in there - it then says "ok, time to fire up the execution servers - we have some real work to do"
process level information
December 11, 2008 - 4pm Central time zone
Reviewer: Matt from MA, USA
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,
Followup December 11, 2008 - 9pm Central time zone:
1) in a parallel query - the execution servers would all be doing the same thing - it would not be that "some use hash join and some do not"
not sure what you mean
2) The factors are: how many resources do you have to completely take over and use. Do you have the cpu's, do you have the memory, do you have the IO - if so, just parallel them all - if not, do not parallel anything.
3) the query that is executed at the remote site can be run in parallel, but all of the results will be sent back to you over a single serial pipe from the remote site.
So, the query could use parallel query, turn a billion rows into 100 rows - and then send those back - that would be "useful"
But if the query runs in parallel and turns a billion rows into a billion rows (eg: it was just a full scan) - and then sends a billion rows over the single dblink - probably not useful
information at process level.
December 12, 2008 - 6pm Central time zone
Reviewer: Matt from MA, USA
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.
Followup December 12, 2008 - 6pm Central time zone:
... so for larger data it is always good to run the query on remote db and then export import, correct ?
....
export? import? That would be the slowest approach known to me. Not a chance.
information at process level
December 15, 2008 - 2pm Central time zone
Reviewer: Matt from MA, USA
So, what do you suggest for larger data ? I always learn better way of doing things from your
notes, please advice.
Thanks,
Followup December 29, 2008 - 10am Central time zone:
I don't know what you mean.
I would just
insert into t select ....;
to do a one time copy of data from A to B.
parallel insert
January 15, 2009 - 8am Central time zone
Reviewer: Badri from India
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
Followup January 16, 2009 - 6pm Central time zone:
basically, all parallel operations like that are done in separate threads or processes - regardless of whether sqlldr did it or you used a parallel direct path load.
You have a session that coordinates the others typically - you would see separate sessions show up in v$session for all the others.
Parallel inserts are done using direct path inserts (no buffer cache, just write to disk) and are done above the high water mark. each of the parallel processes would allocate and write to it's own extent(s), so the are not writing to the same areas of the tables. As they write this data, they maintain mini-indexes on the data they are loading - when they are done, they merge their indexes into the "larger overall index". Now since Oracle is used to doing things multi-user, all of this happens "concurrently".
What locking conflict were you envisioning being an issue?
parallel insert
January 19, 2009 - 8am Central time zone
Reviewer: Badri from India
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
Followup January 19, 2009 - 9pm Central time zone:
1) because a direct path load requires a table lock. If you direct path into a table, only one person at a time can do it.
2) ummm, yes, you just demonstrated it. A single user can use parallel direct path loading.
parallel insert
January 20, 2009 - 5am Central time zone
Reviewer: Badri from India
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
Followup January 20, 2009 - 7am Central time zone:
a direct path load can be done by one and only one person/thing at a time. It gets an exclusive lock on the table (segment really). If I do a direct path load into a table (segment), YOU must wait until I am done. I can do it in parallel (I can use 100 sessions if I want to) to load the table, but you cannot touch that table (segment) (modify it) until I commit. I own that table (segment) when I direct path load into.
It is the way it works. One and only one person can direct path load a segment.
If you use partitioning, I can direct path load partition 1 whilst you direct path load partition 2.
parallel insert
January 20, 2009 - 12pm Central time zone
Reviewer: Badri from India
Thanks a lot once again for your patience in explaining this yet again upon my request.
Badri
Parallel parameters / Degree of tables in OLTP database
January 22, 2009 - 1am Central time zone
Reviewer: Avi from India
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
Followup January 22, 2009 - 8am Central time zone:
parallel query in OLTP does not make sense at all? Parallel query works well when a single user wants to monopolize the entire machine. Do you actually want parallel query enabled at all????
Typo
January 22, 2009 - 1am Central time zone
Reviewer: Avi from India
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
Thanks a lot.
January 23, 2009 - 5am Central time zone
Reviewer: Avi from India
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.
Followup January 23, 2009 - 9am Central time zone:
please don't use "the stamp of tom"
I get really nervous when people do things like that...
Thanks
February 2, 2009 - 7am Central time zone
Reviewer: Avi from India
Dear Tom,
You also get nervous????? Sounds strange to me. Anyway I'll take your words.
Thanks a ton.
Followup February 2, 2009 - 11am Central time zone:
Why does it sound strange.
I get nervous because people many times hear what they want to hear - and then walk around saying "but so and so says X, X is therefore good".
And so and so didn't actually ever say X
Or so and so said X, but in a very specific context
You never want to say "so and so said X, so therefore X is true, we don't need to think about it"
for any so and so.
SQL history of parallel degree
February 3, 2009 - 12am Central time zone
Reviewer: Jeff from AZ USA
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.
Followup February 3, 2009 - 10am Central time zone:
PX_SERVERS_EXECUTIONS:
Total number of executions performed by Parallel eXecution Servers. The value is 0 when the statement has never been executed in parallel.
As for #2, why? Because there were some queries executed in parallel - look at the queries themselves to determine "why" they might have been executed.
As for #1, unless the parallel degree is set on a segment or the hint was used with an explicit degree (query text would have the degree in it), the degree will fluctuate and we really don't track that over time (and a single query can have more than "one" degree associated with it - multiple steps...)
Need help in understanding the PARALLEL hint
April 7, 2009 - 7am Central time zone
Reviewer: Manjunath from India
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
Followup April 13, 2009 - 12pm Central time zone:
rather than say "parallel N", just say "parallel please",
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/usingpe.htm#sthref2226
use the default degree of parallelism (and use atomic refresh => FALSE on your dbms_mview.refresh)
the "rule of thumb" would be to let the optimizer decide what it wants to do and what degree of parallelism it wants to use based on statistics.
as for ... Will this reason alone be sufficient for the
others to understand the ramifications or is there anything else that will help
them? ....
ummm, you have 40gb of RAM, using memory in a data warehouse is sort of something you want to do. Why do they not inately understand "we should use the memory on the machine". 1gb of pga for a warehouse is teeny tiny.
If you are sorting to disk frequently, especially multi-pass operations, you really want to utilize memory a bit more. That is obvious.
5) http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2034.htm#REFRN30191
you use message buffers to pass data between server processes. You currently have 360 of them in use in your system - at one point you had 1,157 of them in use concurrently. You've allocates 427,895 of them over time and have freed them 427,535 times (which if you add 360 is the number of allocated buffers....)
Parallel function not 'piping'
May 14, 2009 - 11am Central time zone
Reviewer: Mark Rynbeek from UK
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
Followup May 14, 2009 - 12pm Central time zone:
how can they "drip back" if they all have to fetch over a ton of data - they spend a considerable amount of time looping over data - and then return it. So they all happen to have to iterate over a lot of stuff first (you wait) and then they return.
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.
They each do a lot of work (you wait)
Then they each return a single row.
There is nothing to "drip" in your logic there.
return more data from the procedures and you'll see some "dripping" as you call it.
More pipe
May 14, 2009 - 12pm Central time zone
Reviewer: Mark Rynbeek from UK
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
Followup May 14, 2009 - 12pm Central time zone:
there is JUST ONE ROW. what could possibly drip, the overhead of parallel coordination is affecting you, the message buffering between the execution servers and the query coordinator is affecting you - the rows will not go one by one from the execution servers to the coordinator, they'll fill a message buffer.
return *more* data and you'll see "dripping", right now you have parallel query message buffering in between the execution servers and the query coordinator.
May 15, 2009 - 5am Central time zone
Reviewer: Mark from UK
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 ?
Followup May 15, 2009 - 1pm Central time zone:
not that I am aware of.
Pipelined function not being parallelized
May 21, 2009 - 2pm Central time zone
Reviewer: Ike Wiggins from Minneapolis, MN
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 May 23, 2009 - 1pm Central time zone:
you don't have any query to parallelize here ( a parallel pipelined function is a function - single function - we execute in parallel)
can you write a single function that takes a cursor as input that could be parallelized?
else, schedule N jobs - each that does their insert - and using job chaining (dbms_scheduler) you could have a finish job run
eg:
your code shedules the N insert routines and the last chained job
the N inserts run
the last chained job runs
INSTEAD OF
your code does an insert into as above
runs more code (this code would move into the last chained job)
Pipelined function not being parallelized
May 27, 2009 - 7pm Central time zone
Reviewer: Ike Wiggins from Minneapolis, MN
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
Followup May 28, 2009 - 7am Central time zone:
The individual get functions are not parallelizable -
select * from table(f()) union all select * from table(g())
In general - f and g "share" a common session, a common session state - application context values, global variables in packages - they cannot just be executed in separate parallel query slaves as that would not be true anymore.
I gave you my idea, use dbms_jobs, have a rendezvous at the end of the process to gather up the outputs.
Parallelism
June 29, 2009 - 8pm Central time zone
Reviewer: JAIME from PORTUGAL
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.
Followup July 6, 2009 - 6pm Central time zone:
you would really have to ask the person that made the recommendation "why". It does not compute to me at all.
the total degree of parallelism can easily be higher than the number of cpu's, you do things OTHER than cpu work, if you want the cpu's to be driven to capacity you will often have a higher number of worker processes than cpu's
|