RE: Perhaps a _REALLY_ dumb question from an experienced dba?
Date: Thu, 8 Aug 2013 19:27:56 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90141D28A_at_exmbx06.thus.corp>
It's not a very good description - but I haven't read it in context, so maybe there's some justification.
In principle the execute call for a query may do all the work necessary to create the result set - but that's for cases like aggregate queries or ordered queries that require the entire result to be created before any rows can be returned. In practise that has actually happened on the first fetch CALL for many years; I forget when the changed happened. You might want to go back to the raw trace file and look at the FETCH lines to see if most of the work was done on the first FETCH: call, or whether it was shared to some degree over the 2 calls. (There are reasons why you shouldn't really assume anything from the spread unless you know how the query works and what the data looks like.)
I think Tanel has a description somewhere which explains that an execution plan is simply a nested set of subroutines that ultimately produce rows. In general (and ignoring several details) when your front end issues a fetch call with an arraysize of N, you actually call the top-level subroutine N times to acquire N rows for the array. If your query was of a type that had to create the entire result set then you're just picking the first N rows from that result set; otherwise each call will be doing the recursive calls down the plan to generate the next row in the result set.
The sort of thing your plan could be is a relatively hash join of a couple of small data sets that does a hash join that uses a lot of CPU to generate a very large intermediate set, then uses a lot more CPU to crunch that down to a very small data set. The first fetch call would result in the whole data set being generate in memory - returning one row, the second fetch call would return the subsequent 9 rows. (I'm assuming you were running from SQL*Plus with the default arraysize when I float that guess.)
Regards
Jonathan Lewis
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Christopher.Taylor2_at_Parallon.com [Christopher.Taylor2_at_Parallon.com] Sent: 08 August 2013 19:29
To: oracle-l_at_freelists.org
Subject: Perhaps a _REALLY_ dumb question from an experienced dba?
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-- http://www.freelists.org/webpage/oracle-lReceived on Thu Aug 08 2013 - 21:27:56 CEST