| Cluster table access IO is unexpected [message #650631] |
Thu, 28 April 2016 08:38  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
Hi all,
I'm wondering if anyone has any decent sources/has looked at these in depth previously.
I have what I would expect to be a fairly simple state of affairs but I can't reconcile the block accesses/IO, do clusters work differently from normal tables?
The thing is a bit of a big one so I'll spare the DDL as much as possible.
I've an index cluster with various tables and when I issue the following pseudocode:
select * from $table1 where PK='VAL';
select * from $table2 where PK='VAL';
select * from $table3 where PK='VAL';
Depending on the table, I get a different number of consistent reads.
I have checked that all the data for the rows reside in the same block and they do, yet I get consistently different numbers for IOs depending on the table the query is issued against.
I've dumped a 10046 and I can see that in all cases the cluster index is using 2 IO, but the table access varies - which makes zero sense to me because the rows should all be in the same block.
I do not understand why selecting some data from block A takes more IO than selecting some different data from block A.
I'd expect consistent values for IO/consistent gets no matter the object being queried, provided they all reside within the same block. The system is 100% at rest, it's not undo reads or anything.
I assume that this is the nature of cluster tables - that there are oddities behind the scenes at work, however I can find absolutely nothing on this topic. It is as if they are a legend, never used anywhere.
I appreciate this is a long shot 
|
|
|
|
|
|
|
|
| Re: Cluster table access IO is unexpected [message #650668 is a reply to message #650634] |
Fri, 29 April 2016 02:32  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
There was chaining, but it was a controlled environment, so I knew about it.
It didnt make sense what should be the same number of IOs was not the same, yet I forgot about the client settings in my pursuit for understanding of how they worked.
Increasing the array size in sql*plus sorted it out 
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
