Query

[nik_flash]

hi all,
I have a application which is using oracle9i database.
Now we have a requirement to have a DSS system which will be using the information from the Application's db. But it is planned that we have a separate schema on the same db or different database altogether..Could you please suggest which would be better approach?
considering that we will be replicating the data from the Application db to the schema or db of the DSS.

[slimdave]

Is this going to be on the same hardware?

If so, OLTP and DSS are tough to mix in the same instance, but that's probably still your best option. You'll need to look at using Resource Manager to prevent the DSS section from eating all the resources. However RM works on the basis of CPU load management, not I/O, and you might find your OLTP subsystem suffering because of it.

If your OLTP performance is critical then I'd consider making a rare exception to my "spread all your data over all your disks" rule and isolate the two subsystems into their own disk/controller areas. You might even consider implementing the DSS sectionon tablespaces of a different block size to the OLTP section in order to isolate their block buffer areas.

All of these suggestions are made to preserve the performance of the OLTP section, and prevent the DSS section from "owning" the box. If the DSS usage is likely to be light, or OLTP performance can take a hit sometimes, then you could relax on some of this -- if you think you can relax, then start by forgetting about the i/o isolation. The memory and RM suggestions would stand, though.

[nik_flash]

Originally posted by slimdave
Is this going to be on the same hardware?

If so, OLTP and DSS are tough to mix in the same instance, but that's probably still your best option. You'll need to look at using Resource Manager to prevent the DSS section from eating all the resources. However RM works on the basis of CPU load management, not I/O, and you might find your OLTP subsystem suffering because of it.

If your OLTP performance is critical then I'd consider making a rare exception to my "spread all your data over all your disks" rule and isolate the two subsystems into their own disk/controller areas. You might even consider implementing the DSS sectionon tablespaces of a different block size to the OLTP section in order to isolate their block buffer areas.

All of these suggestions are made to preserve the performance of the OLTP section, and prevent the DSS section from "owning" the box. If the DSS usage is likely to be light, or OLTP performance can take a hit sometimes, then you could relax on some of this -- if you think you can relax, then start by forgetting about the i/o isolation. The memory and RM suggestions would stand, though.

Thanx for the reply,
Yes it is going to be on the same hardware.
Do you mean that I have different db_block_size for OLTP and DSS system and keep the two subsystems in the separate disks?
Yes the DSS system is going to be light and not many heavy computations will be involved. So I feel we can keep the two subsystem in the same instance.
Please correct me if I am wrong.

[slimdave]

Having a higher block size for the DSS component would be a good thing in itself, but if the block size is different to that of the OLTP component then you have to configure different block buffer areas (at least one per unique TS block size) and this helps to reduce the impact of the dss on the oltp component.

None of these steps are absolutely required -- if the OLTP is very lightly used then you could probably just mix everything together.

[nik_flash]

Thanx for these suggestions slimdave.