Folks,
I'm trying to execute a simple qry like this takes 35 mins
When I pass a hint of the same Index , it returns in seconds.All the statistics are current, also I increased arraysize to 2000 still consistent gets/pIO is same. Give me some suggestions guys.Oracle 9i on Windows,Table Partitioned and Global partitioned Index.Code:1.select * from building_info where bld_seq_id=21261023; 120 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=37) 1 0 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'BUILDING_INFO' (Cost=5 Card=1 Bytes=37) 2 1 INDEX (RANGE SCAN) OF 'BD_SEQ_ID' (NON-UNIQUE) (Cost=4 Card=1) Statistics ---------------------------------------------------------- 1 recursive calls 1 db block gets 6123550 consistent gets 6058079 physical reads 8144 redo size 8121 bytes sent via SQL*Net to client 589 bytes received via SQL*Net from client 9 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 120 rows processed
Thanks![]()




Reply With Quote