RE: Performance bad with and without index
Date: Tue, 4 Oct 2011 09:19:16 -0400
Message-ID: <00ef01cc8298$38abba00$aa032e00$_at_rsiz.com>
If you shoot us the results from what's below it probably will become obvious. If not, it might be something interesting. Not so wild guess: horrible cluster factor on your indexed column, but enough total rows in the table that the cost estimate for the index just wins. Also tell us how long the select count(some_unindexed_column) from tab takes. If what is below does not make it obvious, then trace. If INV_ITEM_ID is a numeric column, you probably get better results removing the apostrophes around the numbers...
If there is a reason folks frequently query by INV_ITEM_ID, and it currently has a lousy cluster factor, and physically reordering the table would not dramatically change the cluster factor of other important index access paths for the worse, then you could make this faster by physically rebuilding this table in order. But since it seems likely that vouchers arrive over time with inv_item_id values that are random over time, the shelf life of that reordering will be low. Since you already have 10 million rows in the table though, the rate of insertion might be more important. (New rows won't scatter anything but the new rows.) Single table clusters and IOTs are also options to consider.
Good luck.
(Please read all my ifs and ands carefully. If I fat fingered a copy and
paste below, just fix it. You might have to adjust the linesize.)
mwf
desc voucher_line
set linesize 140 pagesize 50
set null ~
column owner format a12
column column_name format a30
column column_position format 90 hea CP
column clustering_factor format 9,999,990 hea CLUSTERING
column blevel format 0 hea B
break on owner on table_name skip 1 on TT on index_name on IT on U on blevel
select i.owner,
decode(i.table_owner,i.owner,' ','*') X,
i.table_name,
substr(i.table_type,1,2) TT,
i.index_name,
substr(i.index_type,1,2) IT,
substr(i.uniqueness,1,1) U,
clustering_factor,
i.blevel,
ic.column_name, ic.column_position, ic.descend
from dba_indexes i,dba_ind_columns ic
where i.table_owner = '&table_owner' and i.table_name = 'VOUCHER_LINE' and i.owner = ic.index_owner and i.index_name = ic.index_name and i.table_owner = ic.table_owner and i.table_name = ic.table_name
order by i.owner,i.table_name,i.index_name,ic.column_position;
select
table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,avg_
space_freelist_blocks,to_char(last_analyzed,'yyyy/mm/dd hh24:mi:ss')
from user_tables
where table_name ='VOUCHER_LINE';
select
--+ gather_plan_statistics
count(*), INV_ITEM_ID from VOUCHER_LINE where INV_ITEM_ID in ('3260', '3250', '3255' )
group by INV_ITEM_ID;
select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Orlando L
Sent: Monday, October 03, 2011 7:14 PM
To: oracle-l_at_freelists.org
Subject: Performance bad with and without index
All,
We have a query that runs slow on a 10g database for certain set of values
only. After getting complaints from user, we investigated it. the query was
selecting rows from a table with 10 million rows, but without index. so we
added an index. If the query uses index it becomes slower or gives about
the same response time. the reason is because for the set of values the
users complain about there are more than 50,000 rows:
PRD2> select count(*) from VOUCHER_LINE;
COUNT(*)
9,894,236
PRD2> select count(*), INV_ITEM_ID
2 from VOUCHER_LINE
3 where INV_ITEM_ID in ('3260', '3250', '3255' )
4 group by INV_ITEM_ID;
COUNT(*) INV_ITEM_ID
-------------------- ------------------
54,882 3255
72,522 3250
66,574 3260
The time taken is anywhere from 9 minutes to 14 minutes. Can anyone suggest an idea to speed up this query.
Orlando.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 04 2011 - 08:19:16 CDT