Home » RDBMS Server » Performance Tuning » PARALLEL - Hint not working!!!
| PARALLEL - Hint not working!!! [message #233629] |
Fri, 27 April 2007 00:35  |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi,
Oracle Version - 9.2.0.7
parallel_max_servers - 16
SELECT /*+ FULL(FEM_TERM_DEPOSITS) PARALLEL(FEM_TERM_DEPOSITS,16) */
AS_OF_DATE,
ACCOUNT_OPEN_DATE,
ISO_CURRENCY_CD,
ORIGINATION_DATE,
case
when (MATURITY_DATE - ORIGINATION_DATE) <= 14 then '01. 7 to 14 days'
when (MATURITY_DATE - ORIGINATION_DATE) > 14 and
(MATURITY_DATE - ORIGINATION_DATE) <= 45 then '02. 15 to 45 days'
when (MATURITY_DATE - ORIGINATION_DATE) > 45 and
(MATURITY_DATE - ORIGINATION_DATE) <= 90 then '04. 46 to 90 days'
when (MATURITY_DATE - ORIGINATION_DATE) > 90 and
(MATURITY_DATE - ORIGINATION_DATE) <= 179 then '05. 91 to 179 days'
when (MATURITY_DATE - ORIGINATION_DATE) > 179 and
(MATURITY_DATE - ORIGINATION_DATE) <= 365 and
MONTHS_BETWEEN(MATURITY_DATE,ORIGINATION_DATE) < 12 then '06. 180 to 364 days'
when MONTHS_BETWEEN(MATURITY_DATE,ORIGINATION_DATE) >= 12 AND
MONTHS_BETWEEN(MATURITY_DATE,ORIGINATION_DATE) < 36 then '08. 1 Yrs to < 3 Yrs'
when MONTHS_BETWEEN(MATURITY_DATE,ORIGINATION_DATE) >=36 AND
MONTHS_BETWEEN(MATURITY_DATE,ORIGINATION_DATE) < 60 then '09. 3 Yrs to < 5 Yrs'
when MONTHS_BETWEEN(MATURITY_DATE,ORIGINATION_DATE) >= 60 then '10. > 5 Yrs and Above'
end periods,
case
when CUR_GROSS_RATE<=3 then '<=3%'
when CUR_GROSS_RATE>3 AND CUR_GROSS_RATE<=3.25 then '>3 - <=3.25%'
when CUR_GROSS_RATE>3.25 AND CUR_GROSS_RATE<=3.5 then '>3.25 - <=3.5%'
when CUR_GROSS_RATE>3.5 AND CUR_GROSS_RATE<=3.75 then '>3.5 - <=3.75%'
when CUR_GROSS_RATE>3.75 AND CUR_GROSS_RATE<=4 then '>3.75 - <=4%'
when CUR_GROSS_RATE>4 AND CUR_GROSS_RATE<=4.25 then '>4 - <=4.25%'
when CUR_GROSS_RATE>4.25 AND CUR_GROSS_RATE<=4.5 then '>4.25 - <=4.5%'
when CUR_GROSS_RATE>4.5 AND CUR_GROSS_RATE<=4.75 then '>4.5 - <=4.75%'
when CUR_GROSS_RATE>4.75 AND CUR_GROSS_RATE<=5 then '>4.75 - <=5%'
when CUR_GROSS_RATE>5 AND CUR_GROSS_RATE<=5.25 then '>5 - <=5.25%'
when CUR_GROSS_RATE>5.25 AND CUR_GROSS_RATE<=5.5 then '>5.25 - <=5.5%'
when CUR_GROSS_RATE>5.5 AND CUR_GROSS_RATE<=5.75 then '>5.5 - <=5.75%'
when CUR_GROSS_RATE>5.75 AND CUR_GROSS_RATE<=6 then '>5.75 - <=6%'
when CUR_GROSS_RATE>6 AND CUR_GROSS_RATE<=6.25 then '>6 - <=6.25%'
when CUR_GROSS_RATE>6.25 AND CUR_GROSS_RATE<=6.5 then '>6.25 - <=6.5%'
when CUR_GROSS_RATE>6.5 AND CUR_GROSS_RATE<=6.75 then '>6.5 - <=6.75%'
when CUR_GROSS_RATE>6.75 AND CUR_GROSS_RATE<=7 then '>6.75 - <=7%'
when CUR_GROSS_RATE>7 AND CUR_GROSS_RATE<=7.25 then '>7 - <=7.25%'
when CUR_GROSS_RATE>7.25 AND CUR_GROSS_RATE<=7.5 then '>7.25 - <=7.5%'
when CUR_GROSS_RATE>7.5 AND CUR_GROSS_RATE<=7.75 then '>7.5 - <=7.75%'
when CUR_GROSS_RATE>7.75 AND CUR_GROSS_RATE<=8 then '>7.75 - <=8%'
when CUR_GROSS_RATE>=8 then '>=8%'
end rates,
zone,
branch,
sum(case when (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE < 1500000
and ACCOUNT_OPEN_DATE <= as_of_date and ACCOUNT_OPEN_DATE >= trunc(as_of_date,'month')
then (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE else 0 end) as New_Below_15_Lacks_amt,
sum(case when (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE >= 1500000 and
(CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE < 10000000
and ACCOUNT_OPEN_DATE <= as_of_date and ACCOUNT_OPEN_DATE >= trunc(as_of_date,'month')
then (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE else 0 end) as New_Above_15_Lacks_amt,
sum(case when (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE >= 10000000
and ACCOUNT_OPEN_DATE <= as_of_date and ACCOUNT_OPEN_DATE >= trunc(as_of_date,'month')
then (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE else 0 end) as New_Above_1_Crore_amt,
sum(case when (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE < 1500000
and ACCOUNT_OPEN_DATE <= as_of_date and ACCOUNT_OPEN_DATE >= trunc(as_of_date,'month')
then 1 else 0 end) as New_Below_15_Lacks_noa,
sum(case when (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE >= 1500000 and
(CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE < 10000000
and ACCOUNT_OPEN_DATE <= as_of_date and ACCOUNT_OPEN_DATE >= trunc(as_of_date,'month')
then 1 else 0 end) as New_Above_15_Lacks_noa,
sum(case when (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE >= 10000000
and ACCOUNT_OPEN_DATE <= as_of_date and ACCOUNT_OPEN_DATE >= trunc(as_of_date,'month')
then 1 else 0 end) as New_Above_1_Crore_noa,
sum(case when account_close_date = trunc(as_of_date,'month')
then case
when avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1) < 1500000
then avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)
else 0
end
else case
when avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)/(account_close_date - trunc(as_of_date,'month')) <1500000
then avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)/(account_close_date - trunc(as_of_date,'month'))
else 0
end
end) mat_Below_15lacks_amt ,
sum(case when account_close_date = trunc(as_of_date,'month')
then case
when avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1) >= 1500000 and
avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)< 10000000
then avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)
else 0
end
else case
when avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)/(account_close_date - trunc(as_of_date,'month')) >=1500000 and
avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)/(account_close_date - trunc(as_of_date,'month'))<10000000
then avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)/(account_close_date - trunc(as_of_date,'month'))
else 0
end
end) mat_Above_15lacks_amt,
sum(case when account_close_date = trunc(as_of_date,'month')
then case
when avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1) >= 10000000
then avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)
else 0
end
else case
when avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)/(account_close_date - trunc(as_of_date,'month')) >=10000000
then avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)/(account_close_date - trunc(as_of_date,'month'))
else 0
end
end) mat_Above_1Crore_amt,
sum(case when account_close_date = trunc(as_of_date,'month')
then case
when avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1) < 1500000
then 1
else 0
end
else case
when avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)/(account_close_date - trunc(as_of_date,'month')) <1500000
then 1
else 0
end
end) mat_Below_15lacks_noa,
sum(case when account_close_date = trunc(as_of_date,'month')
then case
when avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1) >= 1500000 and
avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1) < 10000000
then 1
else 0
end
else case
when avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)/(account_close_date - trunc(as_of_date,'month')) >=1500000 and
avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)/(account_close_date - trunc(as_of_date,'month'))<10000000
then 1
else 0
end
end) mat_Above_15lacks_noa,
sum(case when account_close_date = trunc(as_of_date,'month')
then case
when avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1) >= 10000000
then 1
else 0
end
else case
when avg_book_bal*(last_day(as_of_date)-trunc(as_of_date,'month')+1)/(account_close_date - trunc(as_of_date,'month')) >=10000000
then 1
else 0
end
end) mat_Above_1Crore_noa,
sum(case when (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE < 1500000 then 1 else 0 end) TD_Below_15Lacks_noa,
sum(case when (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE >= 1500000 and
(CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE < 10000000 then 1 else 0 end) TD_above_15lacks_noa,
sum(case when (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE >= 10000000 then 1 else 0 end) TD_Above_1Cr_noa,
sum(case when (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE < 1500000 then (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE else 0 end) TD_Below_15Lacs_amt,
sum(case when (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE >= 1500000 and
(CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE < 10000000 then (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE else 0 end) TD_above_15lacks_amt,
sum(case when (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE >= 10000000 then (CUR_PAR_BAL+OVERDUES_AMOUNT)*NOTIONAL_RATE else 0 end) TD_Above_1Cr_amt,
MATURITY_DATE,
account_close_date,
scheme_code
FROM fem_term_deposits a,owb_target.ref_etl_branch_dtl b
where
to_char(a.org_unit_id) = b.org_unit_id
GROUP BY
AS_OF_DATE,
case
when (MATURITY_DATE - ORIGINATION_DATE) <= 14 then '01. 7 to 14 days'
when (MATURITY_DATE - ORIGINATION_DATE) > 14 and
(MATURITY_DATE - ORIGINATION_DATE) <= 45 then '02. 15 to 45 days'
when (MATURITY_DATE - ORIGINATION_DATE) > 45 and
(MATURITY_DATE - ORIGINATION_DATE) <= 90 then '04. 46 to 90 days'
when (MATURITY_DATE - ORIGINATION_DATE) > 90 and
(MATURITY_DATE - ORIGINATION_DATE) <= 179 then '05. 91 to 179 days'
when (MATURITY_DATE - ORIGINATION_DATE) > 179 and
(MATURITY_DATE - ORIGINATION_DATE) <= 365 and
MONTHS_BETWEEN(MATURITY_DATE,ORIGINATION_DATE) < 12 then '06. 180 to 364 days'
when MONTHS_BETWEEN(MATURITY_DATE,ORIGINATION_DATE) >= 12 AND
MONTHS_BETWEEN(MATURITY_DATE,ORIGINATION_DATE) < 36 then '08. 1 Yrs to < 3 Yrs'
when MONTHS_BETWEEN(MATURITY_DATE,ORIGINATION_DATE) >=36 AND
MONTHS_BETWEEN(MATURITY_DATE,ORIGINATION_DATE) < 60 then '09. 3 Yrs to < 5 Yrs'
when MONTHS_BETWEEN(MATURITY_DATE,ORIGINATION_DATE) >= 60 then '10. > 5 Yrs and Above'
end,
case
when CUR_GROSS_RATE<=3 then '<=3%'
when CUR_GROSS_RATE>3 AND CUR_GROSS_RATE<=3.25 then '>3 - <=3.25%'
when CUR_GROSS_RATE>3.25 AND CUR_GROSS_RATE<=3.5 then '>3.25 - <=3.5%'
when CUR_GROSS_RATE>3.5 AND CUR_GROSS_RATE<=3.75 then '>3.5 - <=3.75%'
when CUR_GROSS_RATE>3.75 AND CUR_GROSS_RATE<=4 then '>3.75 - <=4%'
when CUR_GROSS_RATE>4 AND CUR_GROSS_RATE<=4.25 then '>4 - <=4.25%'
when CUR_GROSS_RATE>4.25 AND CUR_GROSS_RATE<=4.5 then '>4.25 - <=4.5%'
when CUR_GROSS_RATE>4.5 AND CUR_GROSS_RATE<=4.75 then '>4.5 - <=4.75%'
when CUR_GROSS_RATE>4.75 AND CUR_GROSS_RATE<=5 then '>4.75 - <=5%'
when CUR_GROSS_RATE>5 AND CUR_GROSS_RATE<=5.25 then '>5 - <=5.25%'
when CUR_GROSS_RATE>5.25 AND CUR_GROSS_RATE<=5.5 then '>5.25 - <=5.5%'
when CUR_GROSS_RATE>5.5 AND CUR_GROSS_RATE<=5.75 then '>5.5 - <=5.75%'
when CUR_GROSS_RATE>5.75 AND CUR_GROSS_RATE<=6 then '>5.75 - <=6%'
when CUR_GROSS_RATE>6 AND CUR_GROSS_RATE<=6.25 then '>6 - <=6.25%'
when CUR_GROSS_RATE>6.25 AND CUR_GROSS_RATE<=6.5 then '>6.25 - <=6.5%'
when CUR_GROSS_RATE>6.5 AND CUR_GROSS_RATE<=6.75 then '>6.5 - <=6.75%'
when CUR_GROSS_RATE>6.75 AND CUR_GROSS_RATE<=7 then '>6.75 - <=7%'
when CUR_GROSS_RATE>7 AND CUR_GROSS_RATE<=7.25 then '>7 - <=7.25%'
when CUR_GROSS_RATE>7.25 AND CUR_GROSS_RATE<=7.5 then '>7.25 - <=7.5%'
when CUR_GROSS_RATE>7.5 AND CUR_GROSS_RATE<=7.75 then '>7.5 - <=7.75%'
when CUR_GROSS_RATE>7.75 AND CUR_GROSS_RATE<=8 then '>7.75 - <=8%'
when CUR_GROSS_RATE>=8 then '>=8%'
end,
zone,
branch,
scheme_code,
MATURITY_DATE,
account_close_date,
ACCOUNT_OPEN_DATE,
ORIGINATION_DATE,
ISO_CURRENCY_CD
In the above query, FEM_TERM_DEPOSITS has around 41004558 records, I want to do a full tablescan since I'm selecting all records. But, in the hint I've specified FULL & PARALLEL. It is not spawnning multiple processes. I mean it is not runing as per the hint.
What could be the problem.
Brayan.
|
|
|
|
|
|
| Re: PARALLEL - Hint not working!!! [message #233648 is a reply to message #233629] |
Fri, 27 April 2007 01:44  |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi Michel,
1/ What is your parallel_min_percent value?
parallel_min_percent -> 0
2/ Are you sure that you have the hardware to satisfy n*16 processes reading at the same time
This query will not be run by users. Instead to refresh a materialzed view. So I have resouce to run the query.
Where as /*+ APPEND PARALLEL (FEM_TERM_DEPOSITS,16) */ working fine.
3/ I'm not sure that in this case a parallel (above all a parallel 16) will improve the performances. After all, you have to group all the rows. How many blocks do you have?
Blocks -> 3381358
Is there a hint to read multiple blocks at a time. I mean overriding db_file_multi_read_count. At present db_file_multiblock_read_count = 8.
Brayan.
|
|
|
|
|
|
|
|
| Re: PARALLEL - Hint not working!!! [message #233729 is a reply to message #233629] |
Fri, 27 April 2007 04:33 |
Brayan
Messages: 315
Registered: June 2002
|
Senior Member |
|
|
Hi,
Is it because of the complex query with multiple group by it is not working parallel. I did not find any docs regarding this.
But a simple query on the same table works fine.
SELECT /*+ PARALLEL(FEM_TERM_DEPOSITS,16) */
AS_OF_DATE,
org_unit_id,
ACCOUNT_OPEN_DATE,
ISO_CURRENCY_CD,
ORIGINATION_DATE FROM FEM_TERM_DEPOSITS
Brayan.
|
|
|
|
| Re: PARALLEL - Hint not working!!! [message #233827 is a reply to message #233729] |
Fri, 27 April 2007 12:58 |
dbaxchangedba
Messages: 26
Registered: November 2005
|
Junior Member |
|
|
"But, in the hint I've specified FULL & PARALLEL. It is not spawnning multiple processes. I mean it is not runing as per the hint.
What could be the problem."
Probably because you have aliases defined for the table. If an alias is defined for a table in the FROM clause then the hint needs the alias and not the actual table name and so in your case it needs to be something like this:
/*+ FULL(a) PARALLEL(a,16) */
Good luck......
http://www.dbaxchange.com
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Nov 04 20:04:50 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
