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

index fast full scan - why so slow and expensive

From: <>
Date: 1 Feb 2005 13:50:24 -0800
Message-ID: <>

Hi, I tried to post this at 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:

  1. is it possible that explain plan will show INDEX RANGE SCAN whereas it is actually doing INDEX FAST FULL SCAN? in general is it possible that explain plan is doing sth different than what is really happening in the db? I thought autotrace may give you the wrong thing, but explain plan should be accurate
  2. is there any way I can disable index fast full scan on the big indexes using hint etc?
  3. what does VALIDATE STRUCTURE do exactly(that is different from just analyze table compute/statistics)?
  4. Is INDEX FAST FULL SCAN always slow and cpu intensive? or you think it is sth. with that index? Here is the stats from index_stats: DBAS_at_ORA8>exec wadb.print_table('select * from index_stats'); HEIGHT : 3 BLOCKS : 131072 NAME : DEFREF_UNQ_IDX PARTITION_NAME : LF_ROWS : 16649139 LF_BLKS : 116211 LF_ROWS_LEN : 561827021 LF_BLK_LEN : 8000 BR_ROWS : 116210 BR_BLKS : 319 BR_ROWS_LEN : 1497112 BR_BLK_LEN : 8032 DEL_LF_ROWS : 591 DEL_LF_ROWS_LEN : 20274 DISTINCT_KEYS : 16649139 MOST_REPEATED_KEY : 1 BTREE_SPACE : 932250208 USED_SPACE : 563324133 PCT_USED : 61 ROWS_PER_KEY : 1 BLKS_GETS_PER_ACCESS : 4 PRE_ROWS : 0 PRE_ROWS_LEN : 0 OPT_CMPR_COUNT : 2 OPT_CMPR_PCTSAVE : 1

Thanks! Received on Tue Feb 01 2005 - 15:50:24 CST

Original text of this message