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

comments embedded

On 1 Feb 2005 13:50:24 -0800, iamoracledba_at_gmail.com wrote:

>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:
>
>1. is it possible that explain plan will show INDEX RANGE SCAN whereas
>it is actually doing INDEX FAST FULL SCAN?
Yes

in general is it possible
>that explain plan is doing sth different than what is really happening
>in the db? Yes

I thought autotrace may give you the wrong thing, but
>explain plan should be accurate
>

The only thing that is accurate is the plan included in a trace file. To dump the plan in a trace file you use tkprof *without* the explain= syntax.

>2. is there any way I can disable index fast full scan on the big
>indexes using hint etc?
>

Using the INDEX hint. However it is a *hint*

>3. what does VALIDATE STRUCTURE do exactly(that is different from just
>analyze table compute/statistics)?>

Validate the structure of the B-tree. Consequently the B-tree will be read in cache.
>4. Is INDEX FAST FULL SCAN always slow and cpu intensive?

No. Index fast full scan will always read all keys in an index, using db_file_multiblock_read_count.
As index fast full scan will always read all keys it will ignore the part in the where clause referring to the index.

 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!

The maximum pct_used is 89 percent. You may want to coalesce the index.
Also the clustering factor of this index may be bad. The clustering factor determines whether or not the records are stored in the table according to the sequence of the index. As most records are smaller than a block, and you always read a block, Oracle might have pre-read adjacent records. CBO takes into account this factor to determine whether or not an index can be used.

--
Sybrand Bakker, Senior Oracle DBA