Gints, you'll never see anything useful in this view, look at note *264473.1 on metalink to see why
rgds * On 7/4/06, Gints Plivna <gints.plivna@xxxxxxxxx> wrote:
I'm playing around materialized views a bit and the general question is - can i somehow find out whether a query was rewritten or not from v$ views. I can of course query v$sql_plan for mat_view rewrite or exact materialized view names but this view is rather soon aged out.
Then I found a very promising view V$SQL_REDIRECTION with a column reason that according to oracle docs and view_definition can take value also 'QUERY REWRITE'. I was very frustrated to find that in my 220.127.116.11 there wasn't any rows and in my 10.2.0.1 there were only rows with reason 'READ ONLY'. Of course it was quite normal because the underlying x$kkssrd haven't any rows at all in 9.2 and only rows with reason=0 in 10.2. And now the question is - why x$kkssrd isn't populated? Probably I am looking in a wrong direction and V$SQL_REDIRECTION is intended for some other purposes?
P.S. Jonathan if you read it and the general question seems deja vu for you, you are right :) -- http://www.freelists.org/webpage/oracle-l