Re: fetch calls

From: Antony Raj <ca_raj_at_yahoo.com>
Date: Fri, 3 Feb 2012 11:13:58 -0800 (PST)
Message-ID: <1328296438.60237.YahooMailNeo_at_web36802.mail.mud.yahoo.com>

 
Average 10 LIOs and 3 PIOs per execution..I think i need to tweak the index..  Resource Usage Profile  overall current Component Total Duration [s] % Number of Events Duration per Event [s] db file sequential read 774.982 88.546 123,585 0.006 SQL*Net message from client 54.956 6.279 36,826 0.001 CPU 45.160 5.160 n/a n/a
SQL*Net message to client 0.086 0.010 36,826 0.000 log file switch completion 0.037 0.004 1 0.037 latch: object queue header operation 0.009 0.001 1 0.009 library cache lock 0.002 0.000 1 0.002

library cache pin  0.001  0.000  1  0.001          
Total  875.234  100.000     
db file sequential read   overall current 
Range [μs]  Total Duration [s]  %  Number of Events  %  Duration per Event [μs]  Blocks [b]  Blocks per Event [b]                
128 ≥ duration < 256  10.010  1.292  44,734  36.197  224  44,734  1.000 

256 ≥ duration < 512 11.669 1.506 36,441 29.487 320 36,441 1.000 512 ≥ duration < 1024 4.422 0.571 6,257 5.063 707 6,257 1.000

1024 ≥ duration < 2048  1.896  0.245  1,334  1.079  1,421  1,334  1.000 
2048 ≥ duration < 4096  16.810  2.169  5,048  4.085  3,330  5,048  1.000 
4096 ≥ duration < 8192  106.976  13.804  17,329  14.022  6,173  17,329  1.000 
8192 ≥ duration < 16384  87.075  11.236  8,189  6.626  10,633  8,189  1.000 
16384 ≥ duration < 32768  60.714  7.834  2,592  2.097  23,424  2,592  1.000 

32768 ≥ duration < 65536 47.073 6.074 1,128 0.913 41,731 1,128 1.000

65536 ≥ duration < 131072  12.995  1.677  152  0.123  85,494  152  1.000 
131072 ≥ duration < 262144  4.492  0.580  26  0.021  172,772  26  1.000 
262144 ≥ duration < 524288  3.634  0.469  9  0.007  403,767  9  1.000 
524288 ≥ duration < 1048576  26.918  3.473  30  0.024  897,253  30  1.000 
1048576 ≥ duration < 2097152  380.298  49.072  316  0.256  1,203,475  316  1.000                

Total 774.982 100.000 123,585 100.000 6,271 123,585 1.000

 
 
 
 
 
 

________________________________

From: Alex Fatkulin <afatkulin_at_gmail.com> To: ca_raj_at_yahoo.com
Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Friday, February 3, 2012 2:03 PM
Subject: Re: fetch calls

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:13:58 CST