Perhaps a _REALLY_ dumb question from an experienced dba?

From: <Christopher.Taylor2_at_Parallon.com>
Date: Thu, 8 Aug 2013 13:29:03 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E887A4F0CE79_at_NADCWPMSGCMS10.hca.corpad.net>

Env: 10.2.0.4 Oracle 3 node RAC on RHELinux 64-bit Oracle Docs say:

---

Stage 8: Fetch Rows of a Query Result
In the fetch phase, rows are selected and ordered (if requested by the query), and each successive fetch retrieves another row of the result, until the last row has been fetched.

---

So is that telling me that each FETCH runs through the execution plan selecting data? Or is that telling me each FETCH selects from the result that has already been built in the previous execute phase? (I was thinking the latter not the former)

The reason I ask this is that I have a query that has the following statistics and I'm completely puzzled.

(If anyone would like to banter off list, I can put together a side email with a trace file, SQL statement etc)

Fetches: 2, Rows 10
Fetch Time CPU = 17,308.3267s
Fetch Time Elapsed = 16,924.2760s

Consistent Reads = 202,866
Physical Reads = 0

Statement Cumulative Statistics
Call Cache

Misses  Count   - Seconds -     Physical
Reads   - Logical Reads -       Rows
                        CPU     Elapsed         Consistent      Current
Parse   1       1       0.0990s 0.1036s 0       54      0       0
Exec    0       1       0.0010s 0.0009s 0       0       0       0
Fetch           2       17,308.3267s    16,924.2760s    0       202,866 0       10
Total   1       4       17,308.4267s    16,924.3804s    0       202,920 0       10

Per Fch 0.5     2.0     8,654.2134s     8,462.1902s     0.0     101,460.0       0.0     5.0
Per Row 0.1     0.4     1,730.8427s     1,692.4380s     0.0     20,292.0        0.0     1.0

Statement Flat Profile
Event Name      % Time  Seconds Calls   - Time per Call -
                                Avg     Min     Max
FETCH calls [CPU]       100.0%  17,308.3247s    2       8,654.1624s     0.0010s 17,308.3237s
SQL*Net message from client [idle]      0.0%    0.2447s 1       0.2447s 0.2447s 0.2447s
PARSE calls [CPU]       0.0%    0.0980s 1       0.0980s 0.0980s 0.0980s
gc cr block 2-way       0.0%    0.0427s 119     0.0004s 0.0003s 0.0006s
gc cr block 3-way       0.0%    0.0262s 54      0.0005s 0.0004s 0.0010s
library cache lock      0.0%    0.0051s 21      0.0002s 0.0002s 0.0007s
EXEC calls [CPU]        0.0%    0.0010s 1       0.0010s 0.0010s 0.0010s
row cache lock  0.0%    0.0004s 1       0.0004s 0.0004s 0.0004s
SQL*Net message from client     0.0%    0.0004s 1       0.0004s 0.0004s 0.0004s
SQL*Net message to client       0.0%    0.0000s 2       0.0000s 0.0000s 0.0000s

Total 100.0% 17,308.7433s

Chris D. Taylor
Oracle DBA
Parallon ITS
6640 Carothers Parkway
Franklin, TN 37067
P: 615.344-8419
christopher.taylor2_at_parallon.com
www.parallon.com<http://www.parallon.com>

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

Received on Thu Aug 08 2013 - 20:29:03 CEST