Re: fetch calls
- From: Antony Raj <ca_raj@xxxxxxxxx>
- To: Alex Fatkulin <afatkulin@xxxxxxxxx>
- Date: Fri, 3 Feb 2012 11:13:58 -0800 (PST)
Â
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@xxxxxxxxx>
To: ca_raj@xxxxxxxxx
Cc: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
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@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
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
Alex Fatkulin,
http://afatkulin.blogspot.com
http://www.linkedin.com/in/alexfatkulin
--
//www.freelists.org/webpage/oracle-l
Other related posts:
