Working on Oracle v8i with OS as win2k/NT.Could we practice the same undermentioned concept in Oracle v9i on Sun Solaris E450 Enterprise server which we are about to do shortly!.
I have a query;
1.Say LARGE_POOL_SIZE=485K(default),SHARED_POOL_SIZE=25MB and JAVA_POOL_SIZE=20MB(default) have the following values.
Db is running on Shared Mode(MTS).Is it necessary to have JAVA_POOL_SIZE
with Forms & Reports v6i Oracle D2K.What impact will it create If LARGE_POOL_SIZE is increased from its default value and JAVA_POOL_SIZE increased or decreased!
Can LARGE_POOL_SIZE AND JAVA_POOL_SIZE be more than SHARED_POOL_SIZE.
I am trying to find out as to what is the ratio for these three parameters in order to give good performance!
Will the performance be OK if LARGE_POOL_SIZE AND JAVA_POOL_SIZE parameters are allowed to run on its default values as above!
2.At present each schema is having its own default tablespace and index tablespace.We have around 15 schemas on a 3GB Database.
Will there be any performance overhead in the short run as well as long run,if say 4 to 5 schemas are clubbed into one index tablespace as compared to earlier situation where each schema had its own index tablespace.
By doing this,what impact will be created for Backup & Recovery, maintenance operations.
3.How many indexes(numbers)in total could we create on each table.
Is the ratio of 1:.85 correct for a Table to Indexes!What should be the exact ratio of Indexes to tables(each).
Thanks & Regards,
09-28-2002, 10:05 AM
Most of your quetions are Myths/Misconceptions from old days.
The purpose of init.ora parms JAVA_POOL_SIZE/LARGE_POOL_SIZE/SHARED_POOL_SIZE is different. There is no much affinity between these parameters, unless your application is taking the advantage of all of these parameters like parallel processing, java related stuff etc.,. Same is true with rest of the questions like # of indexes, defaulting table spaces etc,.
Why do you need 20 indexes on atable for example you are gonna use 4 indexes ?
If you defalut all the schema tables to single tablespace, you can't take advantage of transportable tablespaces specific to schema etc. its just flexibility and convenience.
If you don't beleive me go to asktom.oracle.com and search and read Tom Kyte columns. He advise not to run behind ratios but, run behind your application performance like number of transactions/unit of time and thats the best metric one have to keep in mind.
09-28-2002, 12:02 PM
Well, for one thing, if you are runining in shared mode, then you should make your large pool much larger tha it is now. MTS configuration uses large pool excesively.