Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is there a way to speed up Index Fast Full Scan

Re: Is there a way to speed up Index Fast Full Scan

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sun, 01 Oct 2006 13:24:54 +0200
Message-ID: <kv8vh21jv9603gr50t6hv6puk47l6b77f3@4ax.com>


On 30 Sep 2006 21:22:07 -0700, dbaplusplus_at_hotmail.com wrote:

>SORT_AREA_SIZE=20M
>I have looked into statspack reports, do not see any noticeable delays
>for wait events.
>Yes, my sql_satement is not asking anything too stressaful, yet my
>staements take 1-2 minutes to execute. I will try the event trace you
>discuss. Thanks.

Again, setting sort_area_size to 20m is not recommended. Are you aware, this is not a *global* limit, but a *per sort* limit. Are you aware how many concurrent sorts you have? Are you aware it is useless to set sort_area_size to anything bigger than the extent_size of your temporary tablespace? Do you agree, once you do that, assuming an initial extent of 1M, you force Oracle to allocate 20 extents in one shot, once your sort exceeds 20M.
Do you agree you are likely suffocating the O/S and it is probably paging and faulting heavily?
So why do you not investigate that?
Why do you judge 'I do not see any noticeable delays' and are withhelding the statspack report from this group.

Chances are you have an extreemly bad tuned instance, and there is a fully untuned application running on that instance. Do you think speeding up index fast full scans is really going to solve anything?

--
Sybrand Bakker, Senior Oracle DBA
Received on Sun Oct 01 2006 - 06:24:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US