Re: fetch calls

From: Antony Raj <ca_raj_at_yahoo.com>
Date: Fri, 3 Feb 2012 11:22:12 -0800 (PST)
Message-ID: <1328296932.69742.YahooMailNeo_at_web36801.mail.mud.yahoo.com>



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_at_rulegen.com> To: ca_raj_at_yahoo.com
Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> 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_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:22:12 CST

Original text of this message