init.ora parameters for Hybrid database.

[kannan]

Hi Everybody,

I have a database, which is Hybrid, and need to tune that. I know the init.ora parameters to play with for OLTP and DSS databases tuning. But I am not sure about the Hybrid database init.ora parameters. Could somebody gives me some suggestions on how to set the parameters, which affects the performance on a hybrid database? Also I debate on MTS/parallel or both should be configured for hybrid databases.

Please let me know. Your help is greatly appreciated.

Thanks a lot.

-Kannan.

[balaap]

Hi,
For hybrid database, you may have to follow the few guidelines:

1.Isolate the timing for batch processing & online processsing (Better put batch jobs during night time).

2.Keep seperate roll-back segments for long running transactions.

3.Keep sort_area_size large enough to support huge queries.

4.You tune your applications in such a way that whenever they update blocks, let them commit or roll back as soon as possible.(This could avoid snapshot too old errors in hybrid systems.)

5.Better partition the majore tables in different tablespaces having data files in different disks with different disk controllers.

6.Isolate Tables & indexes in seperate tablespaces as of point number-5.

7.Make use of resource busy notificaion before locking any object and avoid locks & deadlock problems.

....and so many guidelines can be given to you if you browse through various tuning books....

We cannot tell you exactly what are the init.ora parameters. But you can set all the parameters based on your requirements.(You can set various parameters in test database and do the trail & error methods to finalize).

Regards,
A.P.BALASUBRMANIAN
balaap@uk2.net

[kannan]

Hi Bala,

Thank you very much for your reply. It is very good.

I am really looking for setting the below specific parameters, which affects the query plan. Could bala or anyone shed some lights on it?

1. OPTIMIZER_INDEX_CACHING
2. OPTIMIZER_INDEX_COST_ADJ
3. ALWAYS_ANTI_JOIN
4. ALAWYS_SEMI_JOIN
5. HASH_JOIN_ENABLED
6. DB_FILE_MULTIBLOCK_READ_COUNT

I am on 8.1.6 and I have to have a guideline for setting these parameters.

Thanks
Kannan.

[balaap]

OPTIMIZER_INDEX_CACHING
Setting this parameter will favour nested loop joins rather than hash or sort-merge joins. If your application queries are having lot of sort-merge joins, then you can increase this parameter from 0 to higher values.
(Valid values are 0 to 100)

OPTIMIZER_INDEX_COST_ADJ
Favouring index access rather than full table scan access for optimizer.(values ranges from 1 to 10000).
Increasing the value from 100(Default) to more will favour index access path.

ALWAYS_ANTI_JOIN / ALWAYS_SEMI_JOIN
Specify whether to use default value (NESTED_LOOPS) or other value (if you specify) when your application query is having one of the above mentioned join. If you specify not-default value, the oracle optimizer will use the other algorithm to execute the query.
(Instead of blindly following some guidance, you can execute the majore sql statements of your applications in a test database with different parameter values and come to a conclusion. You can better read the user manual. Because you will be aware of your applications,data rather than me. Always theory is theory. Practical is practical.)

HASH_JOIN_ENABLED
You can keep this value as 'TRUE' because, if your applicatoin sql faces hash join,and if it's cost is less, it will be used while executing the query (you are favouring the optimizer for more options).

DB_FILE_MULTIBLOCK_READ_COUNT
It specifies the maximum number of blocks read in one I/O operation during a sequential table scan. Setting higher values of this parameter will be useful when you are doing full table scans (DSS applications).When you are making index scan then this may not be useful.

All these parameter explanations are available in USER GUIDE. If it is possible please send the mail to me (mention what are the applications running, number of users, and other details so that I can suggest good values of all the parameters.)