The SQL Developer team has been steadily adding features tailored to the DBA since the version 3.0 release. This steady march continues with version 4.1.
The DBA feature I want to talk about today is the Instance Viewer.
To open it, you’ll need:
- SQL Developer 4.1
- JDK 8 – this screen uses Java FX, and it’s designed with Java 8
- a very high privileged account – I’m using SYS b/c I can
Note what I didn’t say you would need. There’s no requirement to deploy any agents or create/install any database or server side objects. Nothing happens, nothing is collected, nothing is stored until the window is launched. And then the data is cached locally and goes away when you close the window.
So no repository, no playback, no alerts. Just quick-n-dirty show me what’s going on. Got it?
To Open It
Open the DBA panel.
Add a connection.
Navigate to the ‘Database Status’ node, and click on ‘DB Instance.’
It will take a few moments for the screen to paint.
From there you can do a couple of things. You could detatch/float it as a separate window to zoom out if you wanna go ‘fullscreen.’ Or, you can zoom the window to fit your screen. That’s a ctrl+mouse-drag – in or out.
What You’re Seeing
Key database activity and statuses, refreshed every few seconds. If something’s greyed out or blurry, you need a higher priv account.
If you double-click on a chart, we’ll open a detail report.
A Quick Scenario: Blocked Sessions
I’ve got peop…SESSIONS…to kill. Someone’s application is ‘hanging’ – why? Well, maybe it’s database contention.
So if I double-click on that graph, and old friend comes up.
Now back to our main FX screen. There’s another indicator that something is amiss. Waits.
Note we tried to match the wait class event types to how they’re displayed in em12c.
If I double-click on waits to see wait history…
Note, you’ll need to make sure the diagnostic pack is licensed for this drill down report. Everything else in the Instance Viewer goes against the ‘normal’ views. But we figured if you wanted to see wait events, history would be important.
Don’t miss this: a button to auto-resize the viewer to fit your desktop space. Especially nice if you have floated the window to a secondary monitor.
36 Comments
Hello Jeff,
it is realy very nice feature! But not on my Win10Pro machine!
I have installed the newest sql developer version 4.1.3 on my windows 10 professional and have found a bug!
After changing to “instance viewer” on the DBA tab the window size and the resolution of the sql developer window will be dramatically decreased!
Only while trying to open the “instance viewer”. All other views or reports will be shown without any problems.
I have tested this behaviour with sql developer 4.1.3 (with and without embedded jdk) and with sql developer 4.1.2 with embedded jdk. Buth versions presents me the UNcool feature or cool BUG in sql developer 4.1 release on my windows 10 professional machine!
On my windows 7 professional machine with sql developer 4.1.2 I have no problems!
Any suggestions?
I have already created the Service Request.
Best regards
Hello!
Do you know if there is the way to get the instance viewer report via sqldeveloper CLI? I want to schedule this report automatically in order to obtain a kind of html report or something like it.
Thanks and Regards!
Sorry no.
It’s really a series of reports that refresh at different rates, on a single screen.
If you need multiple folks to keep an eye on the health of the database, or multiple databases, I’d advise you to use an actual monitoring solution like Oracle’s em12c.
In instance-viewer double-cliq for me works with STORAGE, but not for SESSIONS, WAITS… any idea ?
I mouse over sessions graph, it highlights with a blue-border. I double-click – it opens the sessions report.
What happens exactly for you you?
I have got blue border on SESSIONS, WAITS, PROCESSES, MEMORY… and 2 inside STORAGE (files and redologs). Only double-cliq on files or redologs will open (or fill) a new tab. With the other choices not thing appends.
I use french version 64b with JDK8 inside…
No idea. I would open an Service Request with My Oracle Support. Be ready to supply your entire environment, database version, etc.
If I change display language (AddVMOption -Duser.language=en
AddVMOption -Duser.country=US in …\sqldeveloper\bin\sqldeveloper.conf), all double-cliq works correctly !
Could you test next version also in french ?
I have also read in forums that other functions won’t work in german…
Good catch! Definitely a bug.
Hello Jeff.
Thanks for your blog, It´s absolutely GREAT!!
What do I need to do? I double click on Sessions, and nothing happens. I double click on the Waits, and nothing happens.
Am I missing something?
Thanks!!
please can some one help me on how to create connections on sql developer 4.1,have being trying to do dat but give an error of “THE NETWORK ADAPTOR COULD NOT ESTABLISH THE CONNECTION error”
please send the solution to my mail [email protected]
Hi Jeff,
I have a question about the find/replace…
I’ve really missed the regular expression back-reference replace feature that was available in versions prior to 4.0.
I would often use it to overload procedures – so for example i would replace expression:
“, (\S+)$”
with:
“^,? \1 => \1”
– to change text:
“p1
p2
p3”
to:
“p1 => p1
, p2 => p2
, p3 => p3”
Can you please bring this functionality back? I was eagerly anticipating using this in 4.1 – but still cannot.
Also – another annoying bug is that when you use LDAP – it seems like you always have to click the “Load” button in the “New / Select Database Connection” dialog box – and then re-select the database – whenever you want to update a password, change a username, etc. This bug has been around for a long time – should i submit this on My Oracle Support?
Thanks.
Phil
Did you look at the post on search and replace where i talked about that? Answer is in the comments.
Hi Jeff,
I just looked at the post – thanks for the tip. I am happy to report that the $1, $2, etc. works great!
Thanks!
Phil
AWESOME
This looks great but it doesn’t seem to load. I’m using Java 1.8.0_40 and a DBA account but it just sits at ‘Waiting for Editor to initialize’ no matter what I do. Hopefully this gets fixed because I can’t wait to use it!
Oops, sorry just saw the link above to https://community.oracle.com/thread/3685937 – I’ll head there.
where did you get the 1.8u40 java install from? Is it a full jdk or what we embedded with teh win64 download?
Sorry, I did comment on the other thread in the end. It was the Win64 embedded, I went and got the full jdk, changed product.conf and I was off the races. Can’t wait for 4.1 final!
I came across a scenario last night with DB Instance viewer. I had 3 prod servers in view and I had left it running for some time. I’m on a mac, so I put it to sleep and went home. Later in the evening, I was doing some work and the username/password prompt came up. If I hit cancel, it would return to the prompt. There was no way out of it other than to force quit. Yea, I know I could’ve supplied the password but I wasn’t on the vpn at the time. 🙂
Hi,
thank you for providing us nice tips with demos. I have downloaded Sql Dev 4.1 version and installed java 8 as well but DB instance screen doesn’t open at all.
Could you please suggest on how to fix it?
Thanks in advance!
it could be a bad sqldev or a bad java install, please share your info to this thread?
Jeff –
Is there any way to provide the same GUI to non-DBA users in read-only mode? What set of privileges would be required to grant to the non-DBA user to get access to this new feature?
Thank you,
vr
It’s already in a ‘read only’ mode. Some of the drill down reports might allow you to do things, but only if you have those privs, like ‘disconnect session.’
You would need something similar to SELECT ANY DICTIONARY – we go against the GV$ views pretty hard.
Jeff,
I work for bank and we can not install JDK. so i love the bundle of sqldeveloper that you just unzip and use it.
Beta bundles are without JDK, so i have to wait for few months before i use it. Is there anyway you can copy JDK directory from other device and make it work for SQLDeveloper?
I have lot of carved up reports with charts that i use on regular basis. so when i saw instance viewer, i got really excited but that JDK thing is affecting me. let me know if you have some trick that i can use.
Thanks
Ken
Yes, just put down the 8 jdk on another machine, zip it up, and extract it to your work machine, then set the java home in the product.conf to point to that directory.
Hello Jeff,
Any plan to start using gv$ views for sql monitoring and to view RAC information on the Instance View?
If it’s already there and I can’t find it I apologize 🙂
Regards
Diego
it should be using the gv$ views already…a RAC dashboard might come later assuming folks and like this first
You are right, I was checking and there is a column shared for instance and also dop.
This is a very nice improvement. I hope you guys keep going on this direction and make the instance viewer also for RAC.
Thanks!
Jeff, I’m on a macbook pro, I changed the product.conf to point to the java 8 home. I started up the DB Instance and it went thru a couple refresh cycles fine but when I started moving the whole graphic around to see it better, it locked up. I got the spinning colored circle and my mac couldn’t recover. I’ve got 8GB RAM on this thing. If there’s a log for me to look for, let me know. Great info all on one screen so I definitely like it.
If you tried it again, does it lock up again? Your machine sounds just like mine and a few of our developers, so not sure what’s going on. There’s no log, but you could attach to the process with jstack to get a log dump
I tried it again and it did do better for a while. I was able to double click on the sessions block, for example, and it opened up the query window. Then when I kept on sliding the graphic and trying to double click on other things, it bombed out. This time the application shutdown completely. It did the spinning ball then died. Also the widget (spinning dial) for when it’s waiting to do something behind the scenes, was as large as the app itself. I know you’ll make it work man!
Hi,
I have to ask this, why would you be doing redo log switches in days? Just makes it difficult and in my case, not useful.
I do like your blog and I do follow it.
Thank you for doing this and sharing
Sam
you’d rather see it broken up to hours in a day?
Yes, if I start generating a lot of redo, I’d rather see it by hour, I’d be able to see the issue quicker. By day, in my case that is at least 48 log switches for one database so the graph is not and will not be very helpful.
Thank you for responding
Have a great day.
Sam
I’ve passed the feedback to the developer. I’ll let you know when there’s an update.
And thanks!