Re: fetch calls

From: Toon Koppelaars <toon.koppelaars_at_rulegen.com>
Date: Fri, 3 Feb 2012 20:10:10 +0100
Message-ID: <CAA9w=Eum+63H-wfHRnPuW96VXHqJ2-=HqTrYwgfeC7Z29Q1NLA_at_mail.gmail.com>

I don't see arraysize being the problem here. - Your 'application' is submitting the SQL (btw. what is the sql-text?) 36K times.

- It is even parsing it 36K times.
- It fetches on average 1.3 rows per execution.
- For that it requires a bit over 10 block visits, which is not too bad at

all.
- And it does all this in about 0.001 CPU second per execution. - There is a lot of physical I/O going on...: which explains the huge difference between CPU and elapsed time. - Might be worthwile to run this tracefile through Method-R's profiler, to see what skew is going on or not.

Your issue is it is executing the SQL 36k times...

On Fri, Feb 3, 2012 at 7: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
>
>
>

-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.rulegen.com/am4dp-backcover-text


--
http://www.freelists.org/webpage/oracle-l

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