I am trying to capture all the sql_hash_value's for a session which will be running for 5 minutes executing different sql statements. can someone please let me know if we can do this. Thanks in advance.
Printable View
I am trying to capture all the sql_hash_value's for a session which will be running for 5 minutes executing different sql statements. can someone please let me know if we can do this. Thanks in advance.
look at v$open_cursor, or better yet - set sql trace for the session. Then you could see how long each took and what they did, including bind values.
Tom, thanks for your response. How long will the entries in v$open_cursor stay (for a session) after that session disconnects?
They won't - they're only there as long as the session is connected. I do believe the "open" part is relative though - I think they don't drop out of the view even if the app closes the cursors.
If what you're wanting is to tune a process, you can't beat doing a level 12 sql trace, you'll get all the wait times and bind variables. You can look at the trace directly, or run tkprof to get a summary and find the expensive sqls.
For a complete analysis of traces, see Cary Millsap's book Optimizing Oracle Performance - it totally changed my approach to tuning.
Let me know if you need more info on tracing