Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is there a way to speed up Index Fast Full Scan
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 DBAReceived on Sun Oct 01 2006 - 06:24:54 CDT