| INDEX FULL SCAN (MIN/MAX) [message #635671] |
Mon, 06 April 2015 05:00  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Hi all,
More questions.
I have a feeling I'm missing something fundamental here, however I won't know until I ask.
I'm having a bit of confusion about the INDEX FULL SCAN (MIN/MAX) operation.
I expect this to be a super walk down either to min/max of the index to return the value as these are maintained. However, after some updates to the table, I see huge buffer gets which frankly I neither expect nor understand.
create table t as select rownum accno, 0 seqno from dual connect by level < 1500000
/
create index idx1 on t(accno);
/
create index idx2 on t(seqno)
/
--takes a while to update otherwise, behaviour is the same without PDML
alter session enable parallel dml;
set autot on
select min(seqno) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 3688702437
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX2 | 1 | 13 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Fully expected results up until this point...
update /*+ parallel(4)*/ t set seqno=(select min(seqno)+1 from t)
where seqno = (select min(seqno) from t)
1499999 rows updated.
commit;
Commit complete.
1* select min(seqno) from t
/
MIN(SEQNO)
----------
2
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3688702437
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX2 | 1 | 13 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2932 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As you can see, after an update, the consistent gets leap from 3, to 2932...
If I rebuild the index it drops to 3 again.
alter index idx2 rebuild
/
Index altered.
select min(seqno) from t
/
Execution Plan
----------------------------------------------------------
Plan hash value: 3688702437
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX2 | 1 | 13 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
It is as if it is visiting every leaf block in the "min" branch block, but only after an update - this is something I'd not expect.
Oh and the kicker....if I update the whole table to inflate the gets as demonstrated THEN set one single to to a lower value, the gets drop to 3 again.
truncated code:
select min(seqno) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 3688702437
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX2 | 1 | 13 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
3000 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
update t set seqno=(select min(seqno)-1 from t)
where seqno = (select min(seqno) from t) and rownum=1
/
1 row updated.
select min(seqno) from t
/
Execution Plan
----------------------------------------------------------
Plan hash value: 3688702437
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX2 | 1 | 13 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
It only seems to manifest after a) many updates and b) where there are lots of values sharing the min
Am I going mad, is this behaviour what you guys would expect?
[Updated on: Mon, 06 April 2015 05:01] Report message to a moderator |
|
|
|
|
|
|
|
| Re: INDEX FULL SCAN (MIN/MAX) [message #635855 is a reply to message #635698] |
Fri, 10 April 2015 00:05  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Brilliant demonstration, thank you. It looks like that UPDATE makes a complete mess of the index. I wonder what would happen if you created the index with (say) PCTFREE 50%. Would there be enough free space with the initial low-density to rearrange everything in a single update without making a hash of it. I also wonder what would happen if you somehow force the update to run through the rows in reverse order (ie. update high-values first, leaving space from the lower-values to move into).
Not asking you to run these tests; just thinking out loud. We know bulk updates perform terribly anyway, but this is a god lesson that there can be lasting damage to the index as well, which can only be fixed with a rebuild.
Ross Leishman
|
|
|
|
| Re: INDEX FULL SCAN (MIN/MAX) [message #635923 is a reply to message #635855] |
Mon, 13 April 2015 02:35 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Thanks 
It's not actually limited to bulk updates, the guys having the problem were doing OLTP type row by row updates - the table was servicing test software passing one value at a time on demand and they were rolling the sequence up to ensure that the record wasn't reused until all the other ones had been. Came to me because the service feeding the testware had slowed to the point of unacceptability (is that a word?).
Given enough time and inserting data, the index would eventually (mostly) fix itself as the deleted parts are maintained when rows are inserted in. The particular circumstance here would never insert any rows.
It's actually a fairly niche problem, having massive key duplication AND mass updates AND looking for a min of that value AND never inserting into the table to clean up the 'deleted' index entries.
I doubt pct free would help in this case because of the duplication (it's the root of all the evil) and oracle needing to keep the index sorted, keeping in mind that even non-unique indexes are unique under the hood and sorted, it wouldnt be able to put a new entry for a duplicate in the "middle" of the index range it occupied - it needs to sit at the end.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
