| Parallel hints with cursor queries [message #594382] |
Wed, 28 August 2013 02:04  |
 |
a_oracle
Messages: 98
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 #594386 is a reply to message #594383] |
Wed, 28 August 2013 02:52  |
|
a_oracle
Messages: 98
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 #594585 is a reply to message #594410] |
Fri, 30 August 2013 10:01 |
|
a_oracle
Messages: 98
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  .
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
