Thanks all. I went right to the complicated and skipped over the
possibility that you guys pointed out to me.
dbms_space showed me
Unformatted : 0 / 0
Freespace 1 ( 0 - 25% free) : 0 / 0
Freespace 2 ( 25 - 50% free) : 0 / 0
Freespace 3 ( 50 - 75% free) : 0 / 0
Freespace 4 ( 75 - 100% free) : 2,140,345 / 17,533,706,240
Full : 51,493 / 421,830,656
Henry
On Sat, Sep 24, 2016 at 11:50 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@
freelists.org] *On Behalf Of *Mark W. Farnham
*Sent:* Saturday, September 24, 2016 10:05 AM
*To:* jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
*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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@
freelists.org <oracle-l-bounce@xxxxxxxxxxxxx>] *On Behalf Of *Jonathan
Lewis
*Sent:* Saturday, September 24, 2016 1:34 AM
*To:* oracle-l@xxxxxxxxxxxxx
*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
@jloracle
------------------------------
*From:* oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
behalf of Henry Poras [henry.poras@xxxxxxxxx]
*Sent:* 23 September 2016 21:17
*To:* oracle-l@xxxxxxxxxxxxx
*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:
-db_version : 11.2.0.4
-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
***************************************
