Dictionary managed objects

Printable View

Show 40 post(s) from this thread on one page

08-14-2003, 07:39 AM
abhaysk

Simple logic says me that Parallel Write/Read is better over serial, in the perspective of table/index being on diff TS(datafiles/disk/drive) over table/index being on same TS(datafile)..

Ok for Writes,
Memory structures are written to Random memory first which are marked as Dirty Blocks( these include blocks of Table/Index ).

Now when Oracle signal OS to write it to disk, there will be contention if Table/Index are on same Disk/Sector of disk, as write now calls for process to be serial.

Had you had on diff datafiles(disk) it would have been parallel thus increasing OS performance to mantain data in Random memory.

For Reads,
consider user1,user2 requesting data from tableA, former is wanting to be read based on Index ROWID(or say only Index data) & later FTS.. so here again there would be contention as to Blocks of Index/Tables will have to be serial.

Had it been on diff Disks it would have been prallel retrival of data which would increase OS performance.

Abhay.
08-14-2003, 09:36 AM
slimdave

You're all crazy.

Quote:

1) indexes are (much) smaller than tables, so if you use uniform space allocation they perhaps should go in different TS's.

Sure, seperate them on the basis that they are different sizes, but small indexeson a large tables can still go in the same TS as small tables.

Quote:

2) this is one way to spread load across disks (hardware striping is better but we can't all do that).

How about this - for each TS create enough data files to spread the load for that TS over all the available devices. LMT's with uniform extents will do this automatically, by allocating exents in a round-robin fashion.

Quote:

3) (actually an example of #2) Reading a row via index might require (say) 2 I/O's on the index and 1 on the table - if there is a FTS running at the same time, you win if they are on different disks.

Sure, but what if that requires that the TS for the full table scan be limited to particular devices, and that indexes go on another device? You could choke the FTS by doing that, whereas spreading the TS over all devices would improve performance.

Quote:

Agreed that the reads and writes are sequential, during select it will go to index first and then to table, and during inserts, first in table then in index. So seperating them on tablespaces on same disk won't do any good.

But if there are simultanious inserts and selects from many users then i think having indexes and tables on seperate tablespaces on seperate disks will get the benefit.

If it is not beneficial to put tables and indexes on different drives for a single operation, why would it be beneficial for 100's of simultaneous operations? Your aim ought to be to spread the load as evenly as possible over all devices, and you do that by spreading indexes and tables each over all devices. There is therefore no rationale for dedicating TS's to either indexes of tables.

Quote:

And morever i feel that by keeping indexes and tables on seperate tablespaces, i can manage them better.

One example that comes to my mind.

Say i have a very highly volatile table (lots of transaction going on all the time) having millions of rows in a tablespace. Now if i create an index on this same tablespace, won't this add up to the i/o being carried out by other online users.

Now if i were to create this index on another tablespace on seperate disk totally, at least it would reduce the inserts (but not the reads) on the same disk as the inserts would go to seperate disks altogether during the index creation...

Sure, but you've done that at the expense of permanently choking the i/o rate for the table, even when you were not creating the index. User's wouldn't notice the performance fall off due to index creation because their performance is already degraded! You finish creating the index, and the table still cannot support the i/o rate that it ought to by spreading it over all devices. A better solution is to create the index at a quiet time of day.
08-14-2003, 09:46 AM
slimdave

Quote:

Simple logic says me that Parallel Write/Read is better over serial, in the perspective of table/index being on diff TS(datafiles/disk/drive) over table/index being on same TS(datafile)..

Ok for Writes,
Memory structures are written to Random memory first which are marked as Dirty Blocks( these include blocks of Table/Index ).

Now when Oracle signal OS to write it to disk, there will be contention if Table/Index are on same Disk/Sector of disk, as write now calls for process to be serial.

Had you had on diff datafiles(disk) it would have been parallel thus increasing OS performance to mantain data in Random memory.

For Reads,
consider user1,user2 requesting data from tableA, former is wanting to be read based on Index ROWID(or say only Index data) & later FTS.. so here again there would be contention as to Blocks of Index/Tables will have to be serial.

Had it been on diff Disks it would have been prallel retrival of data which would increase OS performance.

Abhay. [/B]

How do you maximise parallelism of read and write, on indexes and tablespaces? You spread each one over every device.

The trouble with the "separatist" logic is the assumption that a session encounters contention when a table and it's indexes share devices, and that this contention can be avoided when the two segment types are separated.

This is not so -- for a single operation the read/write on table and index do not occur at the same time, they are separate processes.

You get contention through different sessions operating on the same table/index. You mitigate this by spreading both the table and the index over as many devices as possible.

For a single session with a parallel query operation, then there is even more importance to spreading the i/o for both tables and indexes over as many devices as possible.
08-14-2003, 10:13 AM
DaPi

Quote:

Originally posted by slimdave
You're all crazy.

That I knew :)

Your idea of 'striping' with LMT sounds nice - I'll consider that for the future.

Quote:

Originally posted by slimdave
Sure, but what if that requires that the TS for the full table scan be limited to particular devices, and that indexes go on another device? You could choke the FTS by doing that, whereas spreading the TS over all devices would improve performance.

To some extent I want to choke FTS in a predominantly OLTP environment. FTS with Parallel on mirrored disks gets them both going, this gives me adequate response times (around 1 minute) for these exceptional requests and no noticable impact on OLTP with 1 or 2 queries with FTS going on at the same time (with >= 3 queries with FTS at the same time it limps).
08-14-2003, 10:26 AM
slimdave

Quote:

Your idea of 'striping' with LMT sounds nice - I'll consider that for the future.

Note that it only works with uniform extents.

Quote:

To some extent I want to choke FTS in a predominantly OLTP environment. FTS with Parallel on mirrored disks gets them both going, this gives me adequate response times (around 1 minute) for these exceptional requests and no noticable impact on OLTP with 1 or 2 queries with FTS going on at the same time (with >= 3 queries with FTS at the same time it limps).

Are you not choking the FTS at the expense of everyone else though? Suposing you had that table spread out over al disks -- you might then be able to support 3, 4 or more FTS without impacting other users.

To provide choking in the way that you want, I'd say that a better solution would be a combination of Resource Manager and careful limiting of parallelism, no?
08-14-2003, 10:43 AM
DaPi

Quote:

Originally posted by slimdave
Are you not choking the FTS at the expense of everyone else though? Suposing you had that table spread out over al disks -- you might then be able to support 3, 4 or more FTS without impacting other users.

I had concluded that I had reduced the impact on other users because 2 out of 3 of I/O's for a retrieval via index (as in my example above) do not have to compete with the FTS. I use parallel 2 (it naïvely matches number of procs and physical disks per logical disk) to make sure it doesn't get out of hand. (And I don't really need to support >= 3 FTS queries at the same time.)

I confess I don't follow your comment about Resource Manager . . . guess a little RTFM is needed.
08-14-2003, 10:55 AM
abhaysk

Quote:

Originally posted by slimdave
This is not so -- for a single operation the read/write on table and index do not occur at the same time, they are separate processes.

Ok, Did i tell single process requesting data, will try to access data from table/index simultaneously?

I said for read, when multilple users trying to access data from same table, some needing full table data and some only indexed data, then it will have contention. So Its always better if you STRIP table data from index.

Ok, for writes I didnt say writes for table/index are simultaneous, but I said when OS tries to make changes permanent (wrting data in datafile/disk), it can be parallel if table/index data are striped into diff datafiles/devices/sectors.

does it make sense?

Abhay.
08-14-2003, 10:57 AM
slimdave

Quote:

Originally posted by DaPi
[B]I had concluded that I had reduced the impact on other users because 2 out of 3 of I/O's for a retrieval via index (as in my example above) do not have to compete with the FTS.

The trouble is, however, that by confining the table and the index to subsets of your available devices you are providing a smaller pool of resources for each one. So the chances of index i/o competing with index i/o, and the chances of table i/o competing with table i/o, are both increased.

Quote:

I use parallel 2 (it naïvely matches number of procs and physical disks per logical disk) to make sure it doesn't get out of hand. (And I don't really need to support >= 3 FTS queries at the same time.)

Ah, but if you don't need to support more FTS operations then why do you want to choke them? How about supporting more index i/o's by spreading the indexes across all the devices?

Quote:

I confess I don't follow your comment about Resource Manager . . . guess a little RTFM is needed.

Heaven forbid that i should suggest such a thing myself, however since you mention it ...

Note that a point not well made in the resource manager documentaton (for my liking) is that it's focus is exclusively on managing and sharing cpu resources, not i/o resources. However there are some things you can do to manage parallelism.
08-14-2003, 11:15 AM
DaPi

Thanks slimdave, I've got my homework assignment now ;)

It will probably be 6 months before I can come back with any results - my test machine is RAID5 so I can only 'test' the idea of 'striped' LMT on production! Coming full circle - I'll probably keep the indexes & tables is separate TS's so that if it all looks sad, I can revert to separate disks by moving the files :D
08-14-2003, 11:27 AM
slimdave

Quote:

Ok, Did i tell single process requesting data, will try to access data from table/index simultaneously?

I said for read, when multilple users trying to access data from same table, some needing full table data and some only indexed data, then it will have contention. So Its always better if you STRIP table data from index.

But all you are doing is increasing the chances of contention on the table, and increasing the chances of contention on the index. There is no way to completely eliminate all contention, but the aim should be to reduce the probability of contention. You do that by increasing the possible number of devices that an operation might use.

Suppose you have eight devices. You could choose to segregate tables and indexes by dedicating (for example) four of them to indexes and four of them to tables, or you could mix them up by spreading both indexes and tables across all devices.

Here's a table that compares the number of devices available to support different types of data access

Code:

Query Type Segregated Mix ========== ========== === 100% Table 4 8 100% Index 4 8 50/50 8 8

So you see, with the segment types spread across all devices you always have the entire i/o subsystem available.

By segregating the segment types you drastically reduce the flexibility. By focussing on the 50/50 situation you are only looking at a situation where the segregation would work There are many situations where you could be looking at an instance where you have sessions suffering excessive waits on i/o operations because 4 of your devices are choked -- the other four devices might be only at 25% of their capacity.

Show 40 post(s) from this thread on one page