RE: Performance bad with and without index
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oralrnr@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 4 Oct 2011 09:19:16 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Orlando L
Sent: Monday, October 03, 2011 7:14 PM
To: oracle-l@xxxxxxxxxxxxx
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
