Monitoring Full tablescans

[M.Shakeel Azeem]

SQL> select name,value
2 from v$sysstat
3 where name like 'table scans%'
4 ;

NAME VALUE
----------------------------------------------------------------
table scans (short tables) 6599797
table scans (long tables) 14003
table scans (rowid ranges) 0
table scans (cache partitions) 0
table scans (direct read) 0

SQL> show parameter db_file_multiblock_read_count

NAME TYPE VALUE
------------------------------------ ------- --------------
db_file_multiblock_read_count integer 32

should i increase db_file_multiblock_read_count and how much it is needed to be increased?

[Thomasps]

What you are expecting by increasing db_file_multiblock_read_count? Do you face any problem?

[craigdba]

What are you looking for exactly do you know that FTS are causing you a problem?

[Telco_DBA]

SQL>

NAME VALUE
----------------------------------------------------------------
table scans (long tables) 14003

NAME TYPE VALUE
------------------------------------ ------- --------------
db_file_multiblock_read_count integer 32

should i increase db_file_multiblock_read_count and how much it is needed to be increased?

(1) I agree with the previous two responses.

==> You really need to find out if those table scans (long tables) are required or caused by a wrong execution plan.

==> Read your statspack-report, watch V$SQLAREA.

(The "table scans (short tables)" are usually OK (typically on tiny lookup-tables, eg. 100 rows of country-codes in 1 Oracle-Block...)

(2) !!! Warning: !!! Increasing the parameter db_file_multiblock_read_count integer from 32 (that's in case of 16 KB Oracle Blocksize 512 KB) can cause even NEGATIVE impact, as MORE execution plans might switch to full-table-scans.

For this reason some vendors of Oracle-based applications recommend on OLTP-systems "db_file_multiblock_read_count = 4" to reduce the problem with unwanted full-table-scans.

(On a reporting-system that's of course completely different!)

==> You really need to find out if those table scans are required.

In case of reports where a full-table-scan is the best execution plan you still can change that parameter at session-level!

alter session set db_file_multiblock_read_count = 128;


Good Luck,

[slimdave]

The value of db_file_multiblock_read_count ought to reflect the size of a multiblock read on your system divided by the size of the system tablespace blocks. That's all you really need to know, IMHO.

[marist89]

full table scan != bad

[ixion]

full table scan != good
full table scan != bad

Ok so which one is it really? The answer is it depends.
Depends on your application type and sql statement that is being executed.

[dbasan]

SQL>
should i increase db_file_multiblock_read_count and how much it is needed to be increased?

Do you know optimizer would choose a full table scan over an index if you set this value high?

[yxez]

hi friends,

How "high" is high and how "low" is low (the value of db_file_multiblock_read_count) for the optimizer to
use index or full table scans?

[slimdave]

hi friends,

How "high" is high and how "low" is low (the value of db_file_multiblock_read_count) for the optimizer to
use index or full table scans?

Making the count smaller increases the number of i/o requests (from Oracle) for a full scan to be completed, therefore the optimizer would tend to favour index access paths. Other than that it depends on far too many variables to describe here. An excellent reference would be Jonathan Lewis' new book.

What problem are you trying to solve?