Re: fetch calls

This SQL is executed 36k times once for each emplid using bind variables.
 
select .....FROM table FILL  where FLAG ='P' and EMPLID = :1 and EMPL_RCD = :2 
and  DEPENDENT_BENEF  <> '00';

________________________________
From: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
To: ca_raj@xxxxxxxxx 
Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx> 
Sent: Friday, February 3, 2012 2:10 PM
Subject: Re: fetch calls


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@xxxxxxxxx> 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@xxxxxxxxxxx
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


Other related posts: