You know we already have a Real Time SQL Monitoring interface in SQL Developer?
It's Tools > Monitor SQL
Yes, Jeff, i do know about the option "Tools > Monitor SQL" , and i was trying it many times before arriving to conclusion that in the current release this interface is not working for RAC databases. (Our shop is 70% RAC)
Every time I use "Tools > Monitor SQL" option and then get connected by using the SCAN name, I end up being connected to the least busiest instance of the clustered database, which is exactly correct for the SCAN (load balancer), and at the same time exactly wrong for the purpose of Real-Time monitoring.
Hope, oracle can make changes to the option "Tools > Monitor SQL" so it actually would support RAC in future releases.
However, my initial question was about the use of
What are your suggestion about using
directly from SQL DEV 4.0 (for real-time monitoring or post-execution )
1 person found this helpful
Why not connect directly to the node in question - bypass the SCAN?
>>Oracle can make changes to the option "Tools > Monitor SQL" so it actually would support RAC in future releases
What would this look like in your opinion?
As for the DBMS_SQLTUNE interface, I would probably start with some custom reports. However I would make sure the provided interface in SQLDev doesn't already do the job first -- less work for both parties that way
Q.> Why not connect directly to the node in question - bypass the SCAN?
A.> Because when we have 8 nodes in the cluster, we are not always sure which instance was executing bad SQL. Also, in case of the PARALLEL hint or parallel execution, we may have sessions 4 nodes out of 8, as an example.
There's definitely 'room' to improve the interface, just tell me what you'd like it to look like. For instance, you want a gv overview of all running SQLIDs across the nodes that you could then select to run a report for? Not saying that's what we'd do, but just wondering what it is you're asking for.
Jeff, first, thank you so much for looking into this issue and helping us.
In regard to the improvements, you have pretty much nailed it in your email above.
Yes, it should be gv$ views showing all SQL_IDs across all instances of the clustered database.
Filtering on STATUS is already there and it is good.
Report that is produced by the right button click on any row and then "Show SQL Details" is also very good.
I guess this report looks very much like DBMS_SQLTUNE.report_sql_monitor API
So, this is it.
Appreciate your help.