Home » RDBMS Server » Performance Tuning » Parallel hints with cursor queries (Oracle 11.2.0.2.0, Windows XP)
Parallel hints with cursor queries [message #594382] Wed, 28 August 2013 02:04 Go to next message
a_oracle
Messages: 95
Registered: November 2010
Member
Hi All,

Does parallel hint works in cursor queries? The cursor query is something like :

cursor c is
select /*+ parallel(s,8) */
from table ref_tab s ---- >>
<where condition>;

The table ref_tab hold data for a single day at any point of time and gets truncate before loading the next days data.
On average the table holds around 7 million rows and doesn't contains any index (think that's fine as all together we are loading the whole set).
And, we are using bulk logic with save exceptions to open the cursor and load the data into the target table.
So basically was just wondering if parallel hint will be of any help in retrieving the table data.



[Edit MC: disable smilies]

[Updated on: Wed, 28 August 2013 02:10] by Moderator

Report message to a moderator

Re: Parallel hints with cursor queries [message #594383 is a reply to message #594382] Wed, 28 August 2013 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
7 million is few, no parallel needed; it will more harm than help.

Regards
Michel
Re: Parallel hints with cursor queries [message #594385 is a reply to message #594382] Wed, 28 August 2013 02:37 Go to previous messageGo to next message
John Watson
Messages: 4611
Registered: January 2010
Location: Global Village
Senior Member
Generally, I don't think there is any point in trying to parallelize a cursor like that: even if the table can be read in parallel, the rows will have to be serialized for processing. But it can be done, take a look at Method 8 in this excellent article, http://www.orafaq.com/node/2450

Re: Parallel hints with cursor queries [message #594386 is a reply to message #594383] Wed, 28 August 2013 02:52 Go to previous messageGo to next message
a_oracle
Messages: 95
Registered: November 2010
Member
Hi Michel,

Thanks for the suggestion, i was just digging more to get the explain plans and here is what i got:
Any suggestions on this? Was just wondering why the rows in the query are so low even though the table analyzed?

-- > Cursor Query
select id,
bus_dt,
value ,
pv_value,
decode(value,'bc_fund_asset_alloc_calc_mkt',bc_fund_asset_alloc_calc_pct,
       'bc_fund_sector_allocation_name',bc_fund_sector_allocation_pct,
	   'bc_fund_geo_allocation_cntry',bc_fund_geo_allocation_pct) allocation_pct
from ref_tab s
unpivot
(pv_value for (value) in
(a as 'a',b as 'b',
 c as 'c'))
where bus_dt = '23-aug-2013';  


Table is analyzed:

NUM_ROWS BLOCKS SAMPLE_SIZE LAST_ANALYZED
7097674 107761 7097674 8/27/2013 22:03


---- >> Plan with paralle hint /*+ parallel(ref_tab,8) */
Plan hash value: 1961924239
 
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                     |     3 | 30186 | 62732 (-39)| 00:12:33 |        |      |            |
|   1 |  PX COORDINATOR        |                     |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)  | :TQ10000            |     3 | 30186 | 62732 (-39)| 00:12:33 |  Q1,00 | P->S | QC (RAND)  |
|*  3 |    VIEW                |                     |     3 | 30186 | 62732 (-39)| 00:12:33 |  Q1,00 | PCWP |            |
|   4 |     UNPIVOT            |                     |       |       |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR |                     |     1 |    58 |  4068   (1)| 00:00:49 |  Q1,00 | PCWC |            |
|*  6 |       TABLE ACCESS FULL| ref_tab             |     1 |    58 |  4068   (1)| 00:00:49 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------


---- >> Plan  with no paralle hint

Plan hash value: 1048229279
 
-------------------------------------------------------------------------------------------
| Id  | Operation           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                     |     3 | 30186 | 87996   (1)| 00:17:36 |
|*  1 |  VIEW               |                     |     3 | 30186 | 87996   (1)| 00:17:36 |
|   2 |   UNPIVOT           |                     |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| ref_tab             |     1 |    58 | 29332   (1)| 00:05:52 |
-------------------------------------------------------------------------------------------
 		  

[Updated on: Wed, 28 August 2013 03:41]

Report message to a moderator

Re: Parallel hints with cursor queries [message #594399 is a reply to message #594386] Wed, 28 August 2013 03:44 Go to previous messageGo to next message
a_oracle
Messages: 95
Registered: November 2010
Member
Thanks John,
That really should help me out a lot. Will try to implement that and will post the findings soon.
Just few more questions. Does the explain plan above seems correct?
Any more suggestions are always welcome Smile

[Updated on: Wed, 28 August 2013 03:47]

Report message to a moderator

Re: Parallel hints with cursor queries [message #594410 is a reply to message #594386] Wed, 28 August 2013 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First of all, if "bus_dt" is of DATE datatype the following is wrong: "bus_dt = '23-aug-2013'".
Compare DATE to DATE and not DATE to STRING => use TO_DATE with a format mask.
In addition, add an index on this column and this will be far faster... and NO parallel hint.

Regards
Michel
Re: Parallel hints with cursor queries [message #594585 is a reply to message #594410] Fri, 30 August 2013 10:01 Go to previous messageGo to next message
a_oracle
Messages: 95
Registered: November 2010
Member
Hi John,

Thanks again for the advise, but could not convince the client to take the bold step of having an autonomous transaction Smile.

Hi Michel,
Thanks. have added the index on stage table and it did increase the performance for some files but for the below file having 7 million rows,
it still goes for a full table scan and seems it will as the data constitute more than 70%of rows in the table at a time.

Well finally decided to discard the cursor and bulk processing and try implementing the append with merge hint and insert data at one go.
The append hint will never use existing space in the data blocks but will use brand new data blocks above the high water mark.
Now, considering that 7 million rows per day will be inserted into the table daily and within short duration, the table size will be growing tremendously,
how feasible it to use the append hint with same.
Can someone advise what extra precautions I have to take before I go for this approach. Just to let you know, I am considering the below approach.

1. Partition the table. (possibly weekly/monthly with subpartitions)
2. The target has primary constraints and the consequent unique key created. So, before loading data, set the index to unusable state.
--- Please advise if the above is correct
3. Set the target table to nologging state before loading the data.
4. User merge to update/insert data into the target table with append hint. Something like:

merge/*+ append parallel(a,4) */ into target_table a
using(select /*+ parallel(a,4) */
from ref_tab <where clause> <unpivot clause>)

Now, my primary concern is reducing the index maintenance while parallel load and if append hint will not eat up the space for the table after sometime
because of this append hint.

Any suggestion is heartily welcomed Smile
Re: Parallel hints with cursor queries [message #594591 is a reply to message #594585] Fri, 30 August 2013 10:53 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>merge/*+ append parallel(a,4) */ into target_table a
What results when 4 RAM resident parallel sessions are all competing for THE single "top" block to complete an INSERT operation?
Will this approach be faster, slower, or the same as a single thread?
Please post actual benchmark results.
Re: Parallel hints with cursor queries [message #594592 is a reply to message #594585] Fri, 30 August 2013 10:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59298
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 28 August 2013 09:11
7 million is few, no parallel needed; it will more harm than help.

Regards
Michel

Re: Parallel hints with cursor queries [message #594599 is a reply to message #594592] Fri, 30 August 2013 11:22 Go to previous messageGo to next message
a_oracle
Messages: 95
Registered: November 2010
Member
Ok.Agreed. It's bad idea. So does that mean I have to just use append hint with no parallel clause, not even in the select statement.Well have tried with a conventional merge with indexes/constraints intact and for inserting 7 million rows it took around 10 minutes.
So, trying hard to get something better. Can you suggest me a approach or corrections for the above.
Got one link from web. By Tom Kyte.
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1951476814728
But still feel not conclusive. Not sure what should be my design approach

[Updated on: Fri, 30 August 2013 11:27]

Report message to a moderator

Re: Parallel hints with cursor queries [message #594601 is a reply to message #594599] Fri, 30 August 2013 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
Which is slower, the SELECT or the INSERT?
Unless & until you have quantified what exactly is slow, you are only shooting in the dark trying to guess how to make it faster
Re: Parallel hints with cursor queries [message #594602 is a reply to message #594601] Fri, 30 August 2013 11:35 Go to previous messageGo to next message
a_oracle
Messages: 95
Registered: November 2010
Member
@Swan: Require a trace file for that. Will try to post it here once the DBA (Lord Of The Rings) have some mercy on me and send me.
But seeing the explain plan which I had posted above, it seems the select is only slow and since the data subset is huge, it will always do a full table scan.
Re: Parallel hints with cursor queries [message #594607 is a reply to message #594602] Fri, 30 August 2013 11:47 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
> it seems the select is only slow and since the data subset is huge, it will always do a full table scan.

observe what I did on my puny laptop regard moving just under 7,000,000 rows
SQL> set time on
09:33:23 SQL> create table foobar1 as select * from foobar;

Table created.

09:34:01 SQL> select count(*) from foobar1;

  COUNT(*)
----------
   6431901

09:34:13 SQL> 


Something a seriously wrong if your production system take multiple minutes to move 7,000,000 rows!
Re: Parallel hints with cursor queries [message #594685 is a reply to message #594382] Sun, 01 September 2013 13:56 Go to previous message
Lalit Kumar B
Messages: 2411
Registered: May 2013
Location: World Wide on the Web
Senior Member
a_oracle wrote on Wed, 28 August 2013 12:34

The table ref_tab hold data for a single day at any point of time and gets truncate before loading the next days data.


It is just using a temporary table for daily loading activity. So why not implement a pipelined function and make utmost use of the parallel sessions(if you really want it to be parallel)

Enhance ETL process with pipelined function
Previous Topic: Performance issue with a query using DB links
Next Topic: Database crash Due To CPU Starvation
Goto Forum:
  


Current Time: Thu Oct 02 11:37:01 CDT 2014

Total time taken to generate the page: 0.06179 seconds