Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> index fast full scan - why so slow and expensive
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(though I might need to change it to analyze only once a week). Sometimes if I analyze the table again, and/or do ANALYZE TABLE XXX VALIDATE STRUCTURE, the problem would go away.
I'd appreciate your input on this. I know the developer's code is not good, but still explain plan should be showing the real plan. specifically, I have the questions below:
Thanks! Received on Tue Feb 01 2005 - 15:50:24 CST