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: index fast full scan - why so slow and expensive

Re: index fast full scan - why so slow and expensive

From: Chuck <skilover_nospam_at_softhome.net>
Date: 2 Feb 2005 13:42:52 GMT
Message-ID: <Xns95F158A5DD31Fskiloversofthomenet@130.133.1.4>


iamoracledba_at_gmail.com wrote in news:1107294624.883089.138330 @f14g2000cwb.googlegroups.com:

> Hi, I tried to post this at asktom.oracle.com but he was busy. So I
> think I might try my luck here:
>
> I have a table with about 16m rows. When end users try to pull up
> reports using the table, response is very slow, if they try to pull up
> more, cpu will spike up to 100%. I look at the query and explain plan
> on it, shows it is using index range scan, but when I query
> v$session_longops almost every time it is actually using INDEX FAST
> FULL SCAN - which is probably why it is slow: the index includes a
> varchar2 column with 24 bytes, so it is quite a big index. I do have
> updated stats on the table - it is analyzed at 3am each day by a
> database job using ANALYZE TABLE FOR TABLE XXX ESTIMATE STATISTICS FOR
> TABLE FOR ALL INDEXED COLUMNS
Your analyze isn't analyzing the index. Change it to "analyze table xxx estimate statistics for table FOR ALL INDEXES for all indexed columns" and see if it makes a difference. Other suggestions may help too but I like to try the simplest solution first.

Better yet, use one of the DBMS_STATS procedures to gather your statistics. ANALYZE still works but DBMS_STATS is what Oracle started recommending in back in version 8.

--
Chuck
Received on Wed Feb 02 2005 - 07:42:52 CST

Original text of this message

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