RE: Stopkey not stopping FTS
Date: Sat, 24 Sep 2016 11:50:05 -0400
Message-ID: <01a701d2167b$528370e0$f78a52a0$_at_rsiz.com>
typo repair:
"Since circa 1993 Oracle has declined to implement the enhancement of an optional low water mark on tables (that would also facilitate bottom to low water mark shrink), and they also have no utility to sort the free list so that early blocks from the extent map are can be used first for non +append inserts. Even putting a pointer to the first row in the first real row in the first block by default scan has not reached the top of the implementation queue, despite the value I believe this would have to balancing block load on parallel scans.
"
should be something like:
Since circa 1993 Oracle has declined to implement the enhancement of an optional low water mark on tables (that would also facilitate bottom to low water mark shrink), and they also have no utility to sort the free list so that blocks early in the extent map that are empty can be those first used for non +append inserts. Even putting a pointer in the first block scanned in extent map order to the lowest block containing a row start piece has not reached the top of the implementation queue when it has from time to time been considered a useful possible enhancement. I also believe knowing the extent map order low water mark could improve balancing the allocation of blocks to be read via parallel scans.
Sorry for the previously garbled message.
mwf
PS: When stopkey was introduced it DID very briefly (in calendar duration) fail to stop scanning as you thought your symptoms described. That was fixed in September 1990 by Gary Hallmark's team (and probably by Gary, himself.) I seriously doubt that would be repeated, but it is possible for the rownum check to be in the delivery of rows for projection layer as opposed to sooner. That type of delivery rownum maximum without injecting a plan difference might also be a useful feature (for development and debugging time as opposed to for production time.)
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Mark W. Farnham
Sent: Saturday, September 24, 2016 10:05 AM
To: jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org
Subject: RE: Stopkey not stopping FTS
Checking the rba of the first row returned and comparing it to your extent addresses is a useful way to definitively dianose whether in fact you have the "empty front" problem. Simply selecting dbms_rowid.rowid_block_number(rowid) block_no for a non-indexed column is usually effective.
Since circa 1993 Oracle has declined to implement the enhancement of an optional low water mark on tables (that would also facilitate bottom to low water mark shrink), and they also have no utility to sort the free list so that early blocks from the extent map are can be used first for non +append inserts. Even putting a pointer to the first row in the first real row in the first block by default scan has not reached the top of the implementation queue, despite the value I believe this would have to balancing block load on parallel scans.
After giving up on lobbying for this nearly continually around 1996, I re-raise it every five years or so when the opportunity is ripe. I encourage folks to suggest to Oracle this would be a useful enhancement to implement. IF ever implemented, it should definitely be optional because it would have to be maintained on insert (non +append) operations on the table if an insert is below the current low water mark.
For now, the only recipe I have is to rebuild, possibly using partition exchange, a union view (aka poor man's partition exchange), or redefinition so your table is "low dense." If you do the rebuild, I recommend ordering by your most useful indexed access order unless this creates a much worse cluster factor for a significant second-through-nth place indexed access order. (That is the theoretical concern that I've never observed in the field. You can diagnose whether that would happen on your actual data on a copy and simply look at the cluster factors for any indexes to see whether this is an actual artifact in your data. IF you ever see this in data "in the wild" that was not constructed to demonstrate the potential problem, I would be interested so I may revise my note "never seen in the field versus the theoretical concern. (In laboratory data it is not difficult to build such that ordering by one column or column set hurts the cluster factor of an index on another column or column set. Whether it occurs regarding indexes actually used for range access in naturally occurring data is an open question as far as I know.)
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis
Sent: Saturday, September 24, 2016 1:34 AM
To: oracle-l_at_freelists.org
Subject: RE: Stopkey not stopping FTS
Table Stats::
Table: TAB Alias: TAB
#Rows: 6508761 #Blks: 1988479 AvgRowLen: 50.00 ChainCnt: 0.00
If you have 6.5M rows and 2M blocks you're averaging less than 4 rows per block - even though your average row length is 50 bytes.
Sayan was suggesting that over time you've deleted a lot of data from the table leaving a huge number of empty blocks before you find the blocks that hold the first 5 rows. I'll go a little further - I'll guess that your code always uses "insert /*+ append */" so you're always inserting above the high water mark and unable to reuse the freespace that exists below the HWM. That might help to explain why a relatively small volume of deletes could leave you with a very large but empty table and a tablescan that takes hundreds of thousands of blocks before it finds the first extant rows.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
behalf of Henry Poras [henry.poras_at_gmail.com]
Sent: 23 September 2016 21:17
To: oracle-l_at_freelists.org
Subject: Stopkey not stopping FTS
I am running a
SELECT * FROM tab WHERE rownum <=5;
and expecting a few blocks to be read and the data returned. Instead, the query is doing a FTS of the entire table before returning the 5 records.
A few details:
-The optimizer's plan is to read just a few blocks (as seen in execution
plan obtained from dbms_xplan after executing query and from 10053 trace.
Will display later). But it's executing a FTS.
-8K block size and avg_row_length of 50, 1988479 blocks
-autotrace shows 1989873 physical reads, 10046 trace has ~3700 direct path
reads, most with 128 blocks. Those reads consume the bulk of the run time
Why am I reading the whole table instead of just the first few blocks?
dbms_explan.display_cursor returns:
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 5 |00:00:58.93 | 1985K| 1988K|
|* 1 | COUNT STOPKEY | | 1 | | | 5 |00:00:58.93 | 1985K| 1988K| | 2 | TABLE ACCESS FULL | TAB | 1 | 5 | 2 (0)|5 |00:00:58.93 | 1985K| 1988K|
Predicate Information (identified by operation id):
1 - filter(ROWNUM<=5)
10053 trace includes:
BASE STATISTICAL INFORMATION
Table Stats::
Table: TAB Alias: TAB
#Rows: 6508761 #Blks: 1988479 AvgRowLen: 50.00 ChainCnt: 0.00
Access path analysis for TAB
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Sep 24 2016 - 17:50:05 CEST