Re: Semi-deterministic?

From: Sayan Malakshinov <xt.and.r_at_gmail.com>
Date: Thu, 6 Feb 2014 19:41:36 +0400
Message-ID: <CAOVevU7EsbYsbBPBeCR9BuWMj0AU6b-TO4p4LYkXAohkhCe36w_at_mail.gmail.com>



Jonathan,

My tests showed that, in contrast to deterministic functions caching, scalar subquery caching do not depend on fetch size: http://orasql.org/category/oracle/scalar-subquery-caching/

On Thu, Feb 6, 2014 at 7:22 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>wrote:

>
> I've not followed the conversation, apologies if this has already been
> explained:
>
> The scalar subquery caching duration is the database call - from SQL*Plus
> you probably have arraysize = 15
>
> 100/15 = 6.667 which rounds up to 7, but I think you'd also have seen the
> first fetch call returning one row followed by 7 fetches returning the
> remaining 99.
>
> Set arraysize 101, rinse and repeat
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> _at_jloracle
>
>
> >
> > now, let's see if we can force the optimizer not to merge with the
> rownum and no_merge double
> > protection.
> >
> > truncate table rs.log1;
> > select /*+ monitor */ l1, p1.n1
> > from rs.t1 ,
> > (select /*+ no_merge */ rs.pkg1.calculate_n1(100) n1 from dual where
> rownum=1) p1 where rownum
> > <=100 ;
> >
> > select count(*) from rs.log1;
> > COUNT(*)
> > ----------
> > 8
> >
> > Eight is better than 100, But, why 8 executions, when there is a need
> for just 1 execution?.
> > Interesting.
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Feb 06 2014 - 16:41:36 CET

Original text of this message