Re: tkprof - disk reads greater than query+current

<wagen123_at_yahoo.com> wrote in message
news:1177099240.399682.248700_at_b75g2000hsg.googlegroups.com...
> Jonathan,
>
> This is a Oracle text query - uses DOMAIN index.
> select * from ( select a.*, rownum rnum from ( select /*
> +FIRST_ROWS(21)*/
> columnA, columnB, columnC, create_date from tableName
> where
> contains ( columnD, 'MICROSOFT') > 0 and
> create_date between sysdate - 180 and sysdate order by create_date
> desc ) a
> where rownum <= 21) where rnum > 0;
>
> Thanks
> wagen
>
>

If this is persistent behaviour, I'd run a couple of queries with event 10046 set at level 8 to catch the disk read waits and see exactly where they came.

Context indexes are built from multiple objects, there may be some funny code in their use that hits the sample bug. On a more simplistic level, you may find that some of the underlying objects do not have statistics so 10g is doing dynamic sampling - which can result in exactly the same anomaly. Did you find any funny SQL in the tkprof output file when you first ran your tests ?

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html