Hi,
Can anybody suggest me for this problem
Here we have Oracle 8i on Windows NT set up.
I run the following Query to check how many number of times full table scan is taking place
and got the following result
select name,value from v$sysstat where name='table scans(long tables)';
name value
table scans(long tables) 24519
The value for the Parameter DB_FILE_MULTIBLOCK_READ_COUNT is 16
My question is should I increase this value for the better performance.How much should I increase?What is the maximum limit for this parameter in Windows NT environment.
Thanks for the answer.
Can you plz also let me know the maximum size of this parameter on Windows NT and UNIX platform.
I have one more doubt.
I would be help full if some body can clear this doubt also.
Before asking this question ,I would like to tell you that I am working at one of my companies client site.Here I have found that on certain big tables, indexes are made on 3-4 columns but name of the index is common for all the indexes on each column.It seams to be very strange to me.
My question is that,how oracle optimizer will decide,which index to follow if it tries to perform index scan.
I have checked out optimizer is performing full table scan on all these tables.The optimizer mode is "CHOOSE"
Is this is the reason why optimizer is not able to perform index scan and doing full tble scan?
In this case should I need to rebuild all the indexes on these tables.
Originally posted by saurabhvtechno I run the following Query to check how many number of times full table scan is taking place
To paraphrase TKyte, "all full scans are not evil, all indexes are not good".
My question is should I increase this value for the better performance.How much should I increase?What is the maximum limit for this parameter in Windows NT environment.
My suggestion would be to find which queries are causing FTS and tune them if appropriate. Arbitrarily increasing your mbrc can cause other queries to favor FTS over index hits (which may or may not be good).
Originally posted by saurabhvtechno I have checked out optimizer is performing full table scan on all these tables.The optimizer mode is "CHOOSE"
Is this is the reason why optimizer is not able to perform index scan and doing full tble scan?
check the value for the init parameter optimizer_index_cost_adj -- set it to somewhere between 10 and 20
Thanks to all of you for giving me suggestion on my question.
Today I came to know the following details about the database related to high water mark and unused blocks.
My database contains 586 tables, out of which following tables are found with unused blocks.Kindly note that all these tables are very big tables and heavy transaction takes place on these tables.
Following are the details.
Table_Name UnUsed Block Total Blocks High Water Mark
My Question is
Is is it possible to skip or deallocate the unused blocks because during full table scan,oracle will definetely be scanning this unused blocks also,which might be causing performance loss.
Whether dellocating these blocks will cause any performance gain
Should I increase the extent size also for these tables.
Kindly guide me how can we skip/deallocate the unused blocks.
Suggest me the neccessary actions I should perform to increase the performance in this condition.
A quick look indicates that the unused blocks are above the high water mark (compare HWM with total minus unused blocks). FTS scans only up to the HWM, so no problem.
Reply With Quote
Bookmarks