suggestion required

[saurabhvtechno]

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.

Regards,
Saurabh

[abhaysk]

32

[saurabhvtechno]

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.

Regards,
Saurabh

[abhaysk]

Max I/O size on NT is 256KB to my guess.

As far as Index having same name for 3 or 4 columns is concerned.. Such Index are called Composite Index..

I would suggest u better RTM

[marist89]

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).

[marist89]

Originally posted by abhaysk
32

[abhaysk]

Originally posted by marist89

32 was for "What is the maximum limit for this parameter in Windows NT environment." assuming block size is 8K ..

[slimdave]

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

[saurabhvtechno]

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

ISAG 46 192 145
MBOI 67 128 60
MMAT 46 512 465
TACP 4581 6432 1850
TDLH 74 800 725
TDLI 7310 27360 20049
TGLI 3590 18272 14681
TGPI 8300 37664 29363
TGRH 707 2496 1788
TGRI 48 6976 6927
TIVH 642 2496 1853
TIVI 202 2496 2293
TIVP 1176 27392 26215
TSBH 104 1696 1591
TSBI 224 18240 18015
TSBP 15837 92320 76482
TSOI 62 64 1

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.

Please note that db_block_size is only 4KB.

Regards,
Saurabh

[DaPi]

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.