Home » RDBMS Server » Performance Tuning » PARALLEL - Hint not working!!!
PARALLEL - Hint not working!!! [message #233629] Fri, 27 April 2007 00:35 Go to next message
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 #233643 is a reply to message #233629] Fri, 27 April 2007 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ What is your parallel_min_percent value?
2/ Are you sure that you have the hardware to satisfy n*16 processes reading at the same time
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?

Regards
Michel
Re: PARALLEL - Hint not working!!! [message #233648 is a reply to message #233629] Fri, 27 April 2007 01:44 Go to previous messageGo to next message
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 #233650 is a reply to message #233648] Fri, 27 April 2007 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For your last question, MBRC can be modified at session level, so you can modify it as you want before executing your query.

Regards
Michel

[Updated on: Fri, 27 April 2007 01:53]

Report message to a moderator

Re: PARALLEL - Hint not working!!! [message #233662 is a reply to message #233629] Fri, 27 April 2007 02:11 Go to previous messageGo to next message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

MBRC I have given as ALTER SESSION..

But, the problem I have with parallel execution.

Brayan.
Re: PARALLEL - Hint not working!!! [message #233729 is a reply to message #233629] Fri, 27 April 2007 04:33 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: PARALLEL - Hint not working!!! [message #233832 is a reply to message #233827] Fri, 27 April 2007 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good eyes!
When a lot of garbage is posted we can't see what is essential.

Regards
Michel
Re: PARALLEL - Hint not working!!! [message #233911 is a reply to message #233629] Sat, 28 April 2007 02:22 Go to previous message
Brayan
Messages: 315
Registered: June 2002
Senior Member
Hi,

Thanks a lot.

Even I could not check for that alias. This query was given by a developer.

Thanks.
Previous Topic: refresh table mon_mods$
Next Topic: can we grant SYSDBA privilege to SYSTEM user ?
Goto Forum:
  


Current Time: Tue Dec 06 00:07:39 CST 2016

Total time taken to generate the page: 0.13846 seconds