Re: Optimizing Sql - unable to use index
Date: 19 Jul 2004 08:27:47 -0700
Message-ID: <f28184a0.0407190727.5bac6980_at_posting.google.com>
hkazmi_at_despammed.com (Haider Kazmi) wrote in message news:<f1a5e8ad.0407160632.53abd233_at_posting.google.com>...
> I need help trying to optimize a SQL query. I am using Oracle 8i.
>
> I have a table with about 1.2 million records, lets call it T1. I am
> doing a join from another table, lets say T2 which has a restriction
> on it. T2's id is a foreign key on T1.
>
> T1 also has a index on
> T2id2T1id T1 (T1.t2Id, T1.id)
>
> What happens is if I retrive just the T1.id from the query, it uses
> the T2id2T1id index for a reverse walk. However as soon as I retrieve
> some other column from T1, say T1.some_col, oracle decides to do a
> full table scan of T1.
>
> Even if I force an optimizer hint /*+ index (t1 t2id2t1id) */, it does
> a full scan of this index.
>
> Any clues to why oracle decides to do a full table scan on a
> 1.2million record table???
Take a look at this article on setting the init.ora parameters optimizer_index_cost_adj and optimizer_index_caching at http://www.dbazine.com/jlewis12.shtml .
I ran the following, written based on my understanding of the above article, through out a day without any values overriding the defaults to get a guess for some 'good' initials settings. After bouncing the instance users did see a significant improvement in search time because indexes were used more frequently. When I ran the same scripts after setting them the first time and trying the new values the response was actually worse.
set linesize 50;
select to_char(sysdate,'YYYY-MM-DD-HH24:MI:SS') from dual;
select 'optimizer_index_cost_adj='||to_char(round(100*(max(seq)/max(scat))))
as "jlewis init.ora settings"
from (
select average_wait as "SEQ",0 as "SCAT" from v$system_event
where event = 'db file sequential read'
union all
select 0,average_wait from v$system_event
where event = 'db file scattered read'
)
union all
select 'optimizer_index_caching='||to_char(round(avg(cache_hit_ratio)))
from (
select (1-(phy.value/(cur.value+con.value)))*100 cache_hit_ratio
from v$sysstat cur
,v$sysstat con
,v$sysstat phy
where cur.name = 'db block gets'
and con.name = 'consistent gets'
and phy.name = 'physical reads'
and (1-(phy.value/(cur.value+con.value)))*100 > 0
union all
select (1-((phy.value-dir.value)/(cur.value+con.value)))*100
cache_hit_ratio
from v$sysstat cur
,v$sysstat con ,v$sysstat phy ,v$sysstat dir
where cur.name = 'db block gets'
and con.name = 'consistent gets' and dir.name = 'physical reads direct' and phy.name = 'physical reads'
and (1-((phy.value-dir.value)/(cur.value+con.value)))*100 > 0 union all
select (1-(physical_reads/
(decode(db_block_gets+consistent_gets,0,.0000000001,db_block_gets+consistent_gets))))*100 cache_hit_ratio
from v$buffer_pool_statistics
where
(1-(physical_reads/
(decode(db_block_gets+consistent_gets,0,.0000000001,db_block_gets+consistent_gets))))*100
> 0
); Received on Mon Jul 19 2004 - 17:27:47 CEST