| Table space with different block size inside same database [message #532943] |
Fri, 25 November 2011 09:17  |
 |
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
|
Member |
|
|
Hi,
All the analysis till now on our system proves that our system is clearly I/O bound and db sequential read is the biggest culprit.
We have even identified the index which is being affected by sequential read. I am thinking of creating a new tablespace with 32K blocksize (currently all table spaces are 8k) and migrate this index to the new space. That way, Oracle will have to do less number of reads to get the required data.
But is there anything wrong in having just one tablespace with a differnt block size? Or is there anything that I have to be watchful about while doing it?
Regards,
Rags
|
|
|
|
|
|
| Re: Table space with different block size inside same database [message #532945 is a reply to message #532944] |
Fri, 25 November 2011 09:42  |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Performance mght degrade. I've never seen any proof of this, but I have heard that the block replacement algorithm for non-standard block size buffer cache pools is not optimized in the same way as fot the default pool. That is why Support always say the same thing: non-standard block sizes are intended for importing transported tablespaces, not for performance tuning.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Table space with different block size inside same database [message #532960 is a reply to message #532956] |
Fri, 25 November 2011 11:04 |
|
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
|
Member |
|
|
No I didnt mean to not answer your query. I was typing mine when you posted your reply.
Here is teh SQL.
SELECT circuitcircuit.usedby2circuit consumercircuitid,
circuitcircuit.turned turnedvalue,
route.direction routedirection,
route.loadbalanceratio loadbalanceratio,
circuittype.transparencybehaviour transpbehaviour,
circuittype.behaviour behaviour,
circuit.circuit2startnode startnodeid,
circuit.circuit2startport startportid,
circuit.circuit2endnode endnodeid,
circuit.circuit2endport endportid,
circuit.circuit2resolutionstatus resolutionstatus,
route.routesequence routesequence
FROM circuitcircuit,
circuit,
circuittype,
route
WHERE circuitcircuit.uses2circuit = :B1
AND circuitcircuit.usedby2circuit = circuit.circuitid
AND circuit.circuit2circuittype = circuittype.circuittypeid
AND route.route2circuit = circuitcircuit.usedby2circuit
AND route.routesequence = circuitcircuit.routesequence
Here is the explain plan
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| |
| 1 | NESTED LOOPS | | 1 | 92 | 9 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 76 | 7 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 64 | 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| CIRCUITCIRCUIT | 1 | 20 | 4 (0)| 00:00:01 |
| 5 | INDEX RANGE SCAN | CC_UK | 1 | | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| CIRCUIT | 1 | 44 | 2 (0)| 00:00:01 |
| 7 | INDEX UNIQUE SCAN | CCT_PK | 1 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | CIRCUITTYPE_M | 1 | 12 | 1 (0)| 00:00:01 |
| 9 | INDEX UNIQUE SCAN | CCTTYPE_PK | 1 | | 0 (0)| |
| 10 | TABLE ACCESS BY INDEX ROWID | ROUTE | 1 | 16 | 2 (0)| 00:00:01 |
| 11 | INDEX UNIQUE SCAN | ROE_UK | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
The CIRCUIT table and the CC_UK index are the highest read objects as per the ASH report.
*code formatted by BlackSwan
[Updated on: Fri, 25 November 2011 11:08] by Moderator Report message to a moderator |
|
|
|
| Re: Table space with different block size inside same database [message #532961 is a reply to message #532960] |
Fri, 25 November 2011 11:13 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
It helps to put code through a formatter:SELECT circuitcircuit.usedby2circuit consumercircuitid,
circuitcircuit.turned turnedvalue,
route.direction routedirection,
route.loadbalanceratio loadbalanceratio,
circuittype.transparencybehaviour transpbehaviour,
circuittype.behaviour behaviour,
circuit.circuit2startnode startnodeid,
circuit.circuit2startport startportid,
circuit.circuit2endnode endnodeid,
circuit.circuit2endport endportid,
circuit.circuit2resolutionstatus resolutionstatus,
route.routesequence routesequence
FROM circuitcircuit,
circuit,
circuittype,
route
WHERE circuitcircuit.uses2circuit = :B1
AND circuitcircuit.usedby2circuit = circuit.circuitid
AND circuit.circuit2circuittype = circuittype.circuittypeid
AND route.route2circuit = circuitcircuit.usedby2circuit
AND route.routesequence = circuitcircuit.routesequence
[update: BS got there frst! Amazing how helpful everyone is today]
[Updated on: Fri, 25 November 2011 11:14] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
