RE: Semi-deterministic?

From: McPeak, Matt <vxsmimmcp_at_subaru.com>
Date: Wed, 5 Feb 2014 22:28:12 +0000
Message-ID: <D7864FA3E7830B428CB2A5A5301B63EE7D29A9FC_at_S7041VA005.soa.soaad.com>



Sayan nailed it. Thanks!

Inline view and "WITH" clause (no MATERIALIZE) do not work -- they call the function once per row.

WITH /*+ MATERIALIZE */ works, but increases buffer gets to 76 per execution.

Sayan's method works and requires only 5 buffer gets per execution.

Thank you, everyone!

Matt

From: Sayan Malakshinov [mailto:xt.and.r_at_gmail.com] Sent: Wednesday, February 05, 2014 4:57 PM To: McPeak, Matt
Cc: ORACLE-L; Riyaj Shamsudeen
Subject: RE: Semi-deterministic?

You can simply use scalar subquery caching: Select ....
,add_months (... , (select your_function (..) from dual Best regards,
Sayan Malakshinov
Senior Oracle performance tuning engineer PSBANK
http://orasql.org
06.02.2014 1:51 пользователь "McPeak, Matt" <vxsmimmcp_at_subaru.com> написал: Not sure that works, but I’ll try it.  
By the way I should mention that my best approach so far is to access the functions in a WITH clause with the /*+ MATERIALIZE */ hint.  I’m looking to improve on that.  (The drawback there is that the materialized temp table gets created pretty big for some reason – the query takes 76 buffer gets instead of 5 buffer gets if I just hard-code the values).  
Thanks,
Matt

 
 
 
 

From: Riyaj Shamsudeen [mailto:riyaj.shamsudeen_at_gmail.com] Sent: Wednesday, February 05, 2014 4:45 PM To: McPeak, Matt
Cc: Oracle Mailinglist
Subject: Re: Semi-deterministic?
 
Hello Matt
  Join that as an inline view. For example:  
SELECT

, et.eta_date,


FROM big_table 
   join (select ADD_MONTHS(po_date, xxcust_profiles_pkg.get_profile_value(‘LEADTIME_MONTHS’))  eta_date from dual) et  
;

Cheers

Riyaj Shamsudeen
Principal DBA,
Ora!nternals -  http://www.orainternals.com- Specialists in Performance, RAC and EBS Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member Co-author of the books: Expert Oracle Practices, Pro Oracle SQL, Expert RAC Practices 12c. Expert PL/SQL practices  
 
On Wed, Feb 5, 2014 at 1:32 PM, McPeak, Matt <vxsmimmcp_at_subaru.com> wrote: Hi…
 
I have a database view that needs has a select-list item something like this:  
SELECT

, ADD_MONTHS(po_date, xxcust_profiles_pkg.get_profile_value(‘LEADTIME_MONTHS’)) eta_date,

FROM big_table;
 
 
Now, here is the thing: I want to execute get_profile_value once per call, but not once per row.  
I don’t think I can make the function DETERMINISTIC, because it’s not (the profile value is stored in the database and may be changed).  
Also, I am shy about giving xxcust_profiles_pkg the SERIALLY_REUSABLE pragma, because I cannot guarantee that it won’t be called from places where it will yield an ORA-6534 (Cannot access serially reusable package…).  
I really want it to behave like SYSDATE – where all rows in the query use the same value but not all queries in the same session do.  
Does anyone have any ideas / thoughts?
 
Thanks,
Matt

 
 
 

i0zX+n{+i^ Received on Wed Feb 05 2014 - 23:28:12 CET

Original text of this message