From: <Christopher.Taylor2@Parallon.com>
To: <oracle-l@freelists.org>
Date: Thu, 8 Aug 2013 13:29:03 -0500
Subject: Perhaps a _REALLY_ dumb question from an experienced dba?
Thread-Topic: Perhaps a _REALLY_ dumb question from an experienced dba?
Message-ID: <F05D8DF1FB25F44085DB74CB916678E887A4F0CE79@NADCWPMSGCMS10.hca.corpad.net>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-type: text/plain
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: Christopher.Taylor2@Parallon.com

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@parallon.com
www.parallon.com<http://www.parallon.com>


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