7 Replies Latest reply on Jul 31, 2017 3:05 AM by Buntoro

Monitor Sessions not showing current sql

EdStevens Jul 27, 2017 6:37 PM

SQL Dev 17.2.0.188 64-bit on Win7 Pro

Am doing side-by-side comparisons between SQL Dev and SQL Navigator, hopeing to be able to drop SQL Nav and save the money.

As one test I have a somewhat long running PL/SQL script that performs several sql statements, and also calls some stored procedures which of course have their own activity. While this is running (from sql plus, at a command line, not within either product) I put SQL Dev on one screen and SQL Nav on the other. In each I open their respective session browser tool and select the test session, and the 'current sql' tab. Set both to auto-refresh the view every 10 seconds. What I see is that SQL Nav will show a sql statement, while at the very same time SQL Dev reports "No text available". I can watch each "blink" when it refreshes the view, within 1 second of each other, and consistently SQL Nav will show me some sql from my test session, while SQL Dev continues to insist "No text available" In both products I am connected to the database with the same account, having the DBA role.

1. Re: Monitor Sessions not showing current sql

EdStevens Jul 27, 2017 6:49 PM (in response to EdStevens)

I also observe the same thing watching the respective 'longops' tabs. SQL Nav giving a steady progression of longops, while SQL Dev .. nada.
Like Show 0 Likes(0)

Actions
2. Re: Monitor Sessions not showing current sql

thatJeffSmith-Oracle Jul 27, 2017 8:17 PM (in response to EdStevens)

Copy the report down to a user defined report, inspect the SQL. we're grabbing sql_id from v$sessions - which might be NULL, you may want to have it do a NVL sub for prev_sql_id.

No idea what SQL your other tool is running, but you should be able to compare the two. View > Log > Statements is the equiv of their Spool SQL feature I think.
Like Show 0 Likes(0)

Actions
3. Re: Monitor Sessions not showing current sql

EdStevens Jul 28, 2017 12:30 PM (in response to thatJeffSmith-Oracle)

thatJeffSmith-Oracle wrote:

Copy the report down to a user defined report,
I'm afraid I don't know how to do that.

inspect the SQL. we're grabbing sql_id from v$sessions - which might be NULL, you may want to have it do a NVL sub for prev_sql_id.

No idea what SQL your other tool is running, but you should be able to compare the two. View > Log > Statements is the equiv of their Spool SQL feature I think.
I poked around all through their menu structure and couldn't find anything even resembling that.

If for nothing other than my own enlightenment, I'd love to do the above, but am at a complete loss on both products.

FWIW, I've now observed that when I first click on a session I get the sql, but as soon as 'auto refresh' does its job, it reverts to 'No text available'. I immediately click on the session (in the session list) and get the sql back. Now that is certainly looking to me like a bug, that 'auto refresh' seems to clear the slate on the tabbed items under the session list, instead of also refreshing whatever tab is open.
Like Show 0 Likes(0)

Actions
4. Re: Monitor Sessions not showing current sql

thatJeffSmith-Oracle Jul 28, 2017 12:38 PM (in response to EdStevens)

when the auto-refresh fires, the selected ROW is no longer selected. so you have to pick your session again

How to customize/view the Sessions report, or any other canned report

And the Statements panel (not avail for THICK connections, JDBC only)...

do stuff, see stuff
Like Show 0 Likes(0)

Actions
5. Re: Monitor Sessions not showing current sql

EdStevens Jul 28, 2017 1:01 PM (in response to thatJeffSmith-Oracle)

thatJeffSmith-Oracle wrote:

when the auto-refresh fires, the selected ROW is no longer selected. so you have to pick your session again
That explains everything. Consider this an enhancement request. Auto-refresh to remember the selected session and reload the session detail tabs.

How to customize/view the Sessions report, or any other canned report

"Now I see" said the blind man. Thanks for the tips.
Like Show 0 Likes(0)

Actions
6. Re: Monitor Sessions not showing current sql

thatJeffSmith-Oracle Jul 28, 2017 1:11 PM (in response to EdStevens)

>> Auto-refresh to remember the selected session and reload the session detail tabs.
I THINK that should be doable, it might even be a bug.
Like Show 0 Likes(0)

Actions
7. Re: Monitor Sessions not showing current sql

Buntoro Jul 31, 2017 3:05 AM (in response to thatJeffSmith-Oracle)

Hi Jeff,

If that is doable, than I'll add vote to the enhancement request.
As for now, we need to re-select the row everytime refresh takes place.
Consider it for both manual and auto refresh as well as parent and child report.

FYI,
Toad also has this Session Monitor feature and the "Refresh" does remember the last selected row.
This is really helpful, not only reduces the click effort but also maintains eye contact point on result refresh.

If this option is available on the report level, that would be great, since not all users might have the same preferences.

Thanks,

Buntoro
Like Show 0 Likes(0)

Actions

Go to original post