-
Thanks for all your replies.
The re-org is actually addressing a couple of issues. Firstly, fragmentation is so bad that the number of records in fet$ and so on is making administration difficult; queries on cetain data dictionary views still haven't returned records after a few hours!
In addition, the previous DBA had created a volume group for indexes and a volume group for tables, each using different spindles. I'm intentionally undoing this by having a single volume group with indexes and tables sharing the same tablespaces. For the benefit of future readers I'd recommend doing a search on the Google groups comp.database.oracle.server. There is a load of information on why there is no point in separating tables and indexes.
I reckon I'll be leaving db_file_multiblock_read_count alone. Given it effects the CBO, it's something to look at another day.
With regard to a single datafile, it seems the file size limit on HP-UX is 64GB so I'll be forced to have multiple files per tablespace whether I like it or not.
Anyway, thanks for the confirmation that the number of segments of per tablespace should not be a concern.
Austin
-
IMHO it is wiser to figure out how much bytes your server can read in 1 I/O and determine your extent sizes and db_file_multiblock_read_count based on this value.
If you follow this link , you'll find the procedure that I used to determine my extent sizes. I learned this at the AskTom-site.
HTH
-
the procedure is setup a big table and full scan it using event 10046, then compare the wait times
my server reads up to 1MB in each I/O however it seems that best performance is achieved by setting db_file_multiblock_read_count to 16, since my db_block_size is 8k seems that 128K is the best I/O although it can read 1MB
-
Pando: how do you calculate the wait times of event 10046? Summarize the 'ela='-values in the trace file?
-
Thank you for replies. I'm going to look into this. I'll be using Trace Analyzer to summarize the 10046 trace file for me. Ejfritters, you can get this from MetaLink, it's very good:
http://metalink.oracle.com/metalink/...&p_id=224270.1
-
Wow! Thanks for the link, hacketta1!
