Re: fetch calls

From: Alex Fatkulin <afatkulin_at_gmail.com>
Date: Fri, 3 Feb 2012 14:03:07 -0500
Message-ID: <CAMVw97+-eFf+EjjMdyyKc37hjJvRuU9jG-YBFafqb1gPBN0sQA_at_mail.gmail.com>

It does not look like increasing the fetch size is something you should be worried about -- you're doing a lot of executions with each executions returning only a handful of rows according to your data. Your PIO figures might be a candidate for driving the times up -- do you happen to have the results from a trace file with wait events breakdown?

On Fri, Feb 3, 2012 at 1:54 PM, Antony Raj <ca_raj_at_yahoo.com> wrote:
> Hi All,
>
> 99% of the response time spent on the Fetch call.I know changing the arraysize from SQL*PLUS would reduce the number of fetch calls.
> But this sql is generated from a third-party application's application server on which the maximum fetch size configured as unlimited.
> Is there any other ways to reduce the number of fetch calls?
>
>
> Rows  Operation
> 1  TABLE ACCESS BY INDEX ROWID ODSTEST (cr  pr=3 pw=0 time 036 us cost=9 size#5 card=1)
> 1     INDEX RANGE SCAN ODSTESTIDX (cr  pr=3 pw=0 time 991 us cost=8 size=0 card=1) (object id 684849)
> Database Call Statistics
> Call  Count  Misses  CPU [s]  Elapsed [s]  PIO [b]  LIO [b]  Consistent [b]  Current [b]  Rows
> Parse  36,826  1  0.140  1.390  0  0  0  0  0
> Execute  36,826  1  2.130  10.326  0  2  2  0  0
> Fetch  36,826  0  42.890  802.626  123,585  390,806  390,806  0  43,918
> Total  110,478  2  45.160  814.342  123,585  390,808  390,808  0  43,918
> Average (per execution)  3  0  0.001  0.022  3  10  10  0  1
> Average (per row)  2  0  0.001  0.019  2  8  8  0  1
>
> Thanks
> --
> http://www.freelists.org/webpage/oracle-l
>
>

-- 
Alex Fatkulin,
http://afatkulin.blogspot.com
http://www.linkedin.com/in/alexfatkulin
--
http://www.freelists.org/webpage/oracle-l

Received on Fri Feb 03 2012 - 13:03:07 CST