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.

.

Relevant Pages

  • Re: Question about the history of EXEC 8, OS 1100, OS 2200, etc.
    ... almost any configuration and was enough of a system to allow doing a sysgen ... 1106 at the LA Data Center, and I don't remember any such beast as a starter Exec for the pre-2200/900 systems. ... arbitrary hardware configuration. ... While I can forgive the original developers for the design of program files, I don't think there is any good reason/justification for the ugly hacks done since that have resulted in program files, large program files, and large element program files. ...
    (comp.sys.unisys)
  • 2.6.18.3 Lockup on Athlon MP
    ... I am sure it's not just a hardware issue, though, ... as well as the kernel configuration I am using. ... # ACPI (Advanced Configuration and Power Interface) Support ...
    (Linux-Kernel)
  • Re: Need your opinion - important!
    ... > Responsible for computer systems / hardware configuration management. ... > instruments in order to validate new computer configurations; ... > DUTIES AND RESPONSIBILITIES ...
    (comp.software.testing)
  • Re: Need your opinion - important!
    ... >Responsible for computer systems / hardware configuration management. ... >instruments in order to validate new computer configurations; ... >DUTIES AND RESPONSIBILITIES ...
    (comp.software.testing)
  • Need your opinion - important!
    ... Responsible for computer systems / hardware configuration management. ... instruments in order to validate new computer configurations; ... DUTIES AND RESPONSIBILITIES ...
    (comp.software.testing)
Loading