Re: Improving performance of full table scans

anish wrote:
> Hi all,
>
> Can anyone help me in the ways for imporving the performance of full
> table scans without additional hardware.
>
> Anish

I'd like to add the converse of Brian's suggestion, the RECYCLE cache. The general idea is, you have an object that gets full-scanned, thrashing the lru algorithms that control what is in DEFAULT. See the Performance Tuning Guide on checking V$BH for candidates for this treatment, too big to be kept in memory.

Also, there are many things that are version and configuration dependent, including the resource advisors (I like 9.2 OEM's) that can give some idea if you are even close. You should always post detailed version and platform information. pga targets can help, too.

init parameters can affect the optimizers decision to full-table scan, especially DB_FILE_MULTIBLOCK_READ_COUNT, OPTIMIZER_INDEX_CACHING, and OPTIMIZER_INDEX_COST_ADJ. What kind of system is it, DW, OLTP, DSS, something else? Parallelization may be useful if you have the cpu's and are DW.

Some hardware and OS configurations can make a big difference. What are yours?

Of course, the best performing sql is the sql that isn't done, so we would need more details about your full table scans.

The physical distribution of the data in the table can make a big difference, too. If you have a lot of free space, that's just more useless stuff to read. If you have the various types of chaining, that can be really bad. If you have fully stuffed blocks and then exp/imp, you might make things worse, because you might add pctfree to the data.  So it is worthwhile to know your data, know your application. We don't know that because you haven't told us.

Other things going on can impact the performance too. If you have archive logging fighting data access over the disk controller, for example, don't do that.

RAID levels can make a difference, although changing from 5 to 0+1 might need more hardware.

etc.

jg

-- 
@home.com is bogus.
This better not have been homework.