Home » RDBMS Server » Performance Tuning » INDEX FULL SCAN (MIN/MAX) (11.2.0.3)
INDEX FULL SCAN (MIN/MAX) [message #635671] Mon, 06 April 2015 05:00 Go to next message
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 #635677 is a reply to message #635671] Mon, 06 April 2015 07:54 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
It appears, as near as I can tell, to be related to this:

https://richardfoote.wordpress.com/2008/10/28/fragmented-indexes-due-to-large-number-of-duplicate-entries-more/

If only it didnt start to "behave" when I create a new min, would make a lot more sense Confused

[Updated on: Mon, 06 April 2015 08:00]

Report message to a moderator

Re: INDEX FULL SCAN (MIN/MAX) [message #635698 is a reply to message #635677] Tue, 07 April 2015 02:23 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Indeed it is because oracle still reads the index blocks full of deleted entries.

https://richardfoote.wordpress.com/2008/07/01/deleted-index-entries-part-v-trouble/

The reason the gets improve at a "new" min is because that value used to exist and that can be recycled thus meaning it doesn't need to (in this case) read the blocks full of deleted rows.

Thanks to Mr Foote for helping out here Smile
Re: INDEX FULL SCAN (MIN/MAX) [message #635855 is a reply to message #635698] Fri, 10 April 2015 00:05 Go to previous messageGo to next message
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 Go to previous message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Thanks Smile

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.
Previous Topic: indexing nulls
Next Topic: Do we need to rebound instance when we Determine the Practical Range for FAST_START_MTTR_TARGET
Goto Forum:
  


Current Time: Thu Mar 28 23:41:27 CDT 2024