Home » RDBMS Server » Performance Tuning » Cluster table access IO is unexpected (11.2.0.4)
Cluster table access IO is unexpected [message #650631] Thu, 28 April 2016 08:38 Go to next message
Roachcoach
Messages: 1509
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 Confused
Re: Cluster table access IO is unexpected [message #650633 is a reply to message #650631] Thu, 28 April 2016 09:30 Go to previous messageGo to next message
Roachcoach
Messages: 1509
Registered: May 2010
Location: UK
Senior Member
Turns out this was arraysize!!
Re: Cluster table access IO is unexpected [message #650634 is a reply to message #650631] Thu, 28 April 2016 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Block chaining?
Check cluster key size compare to cluster key size plus sum of tables row size and block size.

Re: Cluster table access IO is unexpected [message #650668 is a reply to message #650634] Fri, 29 April 2016 02:32 Go to previous message
Roachcoach
Messages: 1509
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 Smile
Previous Topic: Performance Issue after upgrade to 11.2.0.4
Next Topic: SGA_MAX_SIZE and SGA_TARGET
Goto Forum:
  


Current Time: Tue Jan 23 05:06:41 CST 2018

Total time taken to generate the page: 0.01202 seconds