Bug in consistent gets for recursive/nested SQL?

From: NetComrade <netcomradeNSPAM_at_bookexchange.net>
Date: Thu, 30 Sep 2004 20:16:48 GMT
Message-ID: <415c66fb.961851258@localhost>

All,
[Oracle version 8.1.7.4 on Solaris 2.7)

Is anyone aware of a bug, where consistent gets are not recorded in original query?

Reason I ask, is that we have something like the following

query1:
select col1, col2, col3...
from
  (select col1,col2,col3...
    from

       (select col1, col2, col3,pkg.function(col1)
        from t1, t2, t3
       )

   )
(

query2 (part of pkg.function above)

selece col1
from t4
where
...

Query 2 would consistently show up in Top queries (for consistent gets in statspack report); However, Query1 would not show up in statspack reports (the function _is_ used in other places, so it could've been the case that it was used much more somewhere else, and become 'bigger' then query1)

However

1. Query 1 was one of the most frequently executed queries overall for this product (it was on 'welcome' web page)
2. As soon as we tuned query1 to not call the pkg.function as much
   (moved rownum<5 in the inner query, instead of outter query), query2
   disappeared from the 'top'

In Sql*plus autotrace I couldn't reproduce this (it would seem Sql*plus would report the proper # of consistent gets). However, that doesn't mean Oracle's internal views couldn't be somehow not updated properly when the query actually executes..

Hm... I guess I could test in a clean test db, and see what happens to both autotrace, and v$ views. But I think I might've seen a post on this subject in the past.

Thanks in advance.
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Thu Sep 30 2004 - 15:16:48 CDT