W dniu 2017-11-26 o 12:43, Andy Sayer pisze:
Thanks Andy, I totally agree that makes no sense, app is populating gtt and then adding some more predicates to it (why just not populate already clean resultset:) ) .
GG, to confirm what bind variables were used when the statement was first parsed, you could use:
select * from table(dbms_xplan.display_cursor(sql_id=>'btay965futjwg',format=>'advanced -projection'));
However, this bind variables in this statement seem to have nothing to do with what went into populating the GTT so it might not really help, other than to prove that the statement was parsed with differing bind variables being peeked. You say that you should be getting a new SQL each time the application executes it due to the comment, if that were the case then I would expect the dynamic sampling to figure out the number of rows in a table much better. In my experiments, it does seem easy for the CBO to get it slightly wrong but I don't think this would ever be the sort of scale you're looking at. It is probably easier to get it wrong when you are applying further predicates against the GTT (which seems weird to me, why not just not populate those rows to begin with?)
