Home » RDBMS Server » Performance Tuning » Table space with different block size inside same database (Oracle 10g, 10.2.0.1.0 Linux)
Table space with different block size inside same database [message #532943] Fri, 25 November 2011 09:17 Go to next message
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 #532944 is a reply to message #532943] Fri, 25 November 2011 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I bet you won't notice any performance difference especially if new tablespace is on same disk volume as current TS.

>That way, Oracle will have to do less number of reads to get the required data.
But OS will need to do exactly the same amount of I/O activity

[Updated on: Fri, 25 November 2011 09:38]

Report message to a moderator

Re: Table space with different block size inside same database [message #532945 is a reply to message #532944] Fri, 25 November 2011 09:42 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #532948 is a reply to message #532944] Fri, 25 November 2011 09:58 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Thanks John, yes some else here was saying the same thing!

Well, if OS is the limitation here, how can it possibly help if I create the new TS in a different disk?

And do I have any other way out other than increasing the SGA?

[Updated on: Fri, 25 November 2011 10:00]

Report message to a moderator

Re: Table space with different block size inside same database [message #532949 is a reply to message #532948] Fri, 25 November 2011 10:26 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>And do I have any other way out other than increasing the SGA?
If OS can not pull blocks from disk fast enough, how does larger SGA help?

most performance problems are NOT impacted one way or the other by SGA size.
Re: Table space with different block size inside same database [message #532950 is a reply to message #532949] Fri, 25 November 2011 10:33 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Tune the application: don't tune I/O, tune the need for I/O.
Good luck.
Re: Table space with different block size inside same database [message #532951 is a reply to message #532949] Fri, 25 November 2011 10:33 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Sorry I think I didnt give the complete picture. Yes SGA size is not going to help with faster I/O but increasing SGA is going to increase the probability of the data available in the buffer thereby reducing the need to do an IO. I understand it wont help much incase of random access but what we are facing is sequence access.

Does it make sense?
Re: Table space with different block size inside same database [message #532952 is a reply to message #532951] Fri, 25 November 2011 10:35 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
John Watson, yeah ultimately thats where it all leads to!
Re: Table space with different block size inside same database [message #532953 is a reply to message #532950] Fri, 25 November 2011 10:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>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.

post SQL & results that substantiate statements above

If you made this disk access go to ZERO, how much faster would application run?
Re: Table space with different block size inside same database [message #532954 is a reply to message #532952] Fri, 25 November 2011 10:43 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Just one more thing. Is I/O something to be seen at an application level or at an SQL level? What I mean is, if you take my situation, I know the top IO offending SQLs but I cant think of a way to tune the biggest offender. However I can tune the next set of SQLs in that list. If I tune them does that take care of the Top offender as well?

I probably think it does because, all those SQL must be doing an I/O because the data is not available in buffer. Which means these SQLs try to consume the memory as well and hence tuning them will give more memory for the biggest offender. In the end the biggest offender ends up doing less I/O than it does today. Is this understanding correct?
Re: Table space with different block size inside same database [message #532956 is a reply to message #532954] Fri, 25 November 2011 10:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Just because something appears in any TOP 5 list, it does NOT imply that any problem exists.

since you do not answer my questions, I'll stop wasting my time trying to answer yours.
Re: Table space with different block size inside same database [message #532959 is a reply to message #532954] Fri, 25 November 2011 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You do it the wrong.
First tune the business rules
Then tune the application
Then tune the SQL
Then tune the instance
Then tune the OS and network

Do not do it bottom up.

Regards
Michel
Re: Table space with different block size inside same database [message #532960 is a reply to message #532956] Fri, 25 November 2011 11:04 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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

Re: Table space with different block size inside same database [message #532973 is a reply to message #532961] Fri, 25 November 2011 16:55 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The CIRCUIT table and the CC_UK index are the highest read objects as per the ASH report.
Returning 1 row in 1 second is not anything that I would be concerned about.
Previous Topic: Reports Tuning
Next Topic: SGA AND PGA PARAMETER SETTINGS
Goto Forum:
  


Current Time: Fri Mar 29 08:02:56 CDT 2024