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.

This is COOL :)
This is COOL 🙂

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

Pst! We updated this feature again for 4.2. It offers a Top SQL page as well as the Storage/IO bits.

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.

Note on the sessions panel, the 'Blocked' line is up to '2'
Note on the sessions panel, the ‘Blocked’ line is up to ‘2’

So if I double-click on that graph, and old friend comes up.

Kill 'em all
Kill ’em all

Now back to our main FX screen. There’s another indicator that something is amiss. Waits.

That's a really out-weighted proportion of wait time for Application...
That’s a really out-weighted proportion of wait time for Application…

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…

Row lock contention wait event
Row lock contention wait event


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.

Zoom to fit.
Zoom to fit.
thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

36 Comments

  1. 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

  2. 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!

    • thatjeffsmith

      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.

  3. In instance-viewer double-cliq for me works with STORAGE, but not for SESSIONS, WAITS… any idea ?

    • thatjeffsmith

      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…

    • thatjeffsmith

      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…

  4. 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!!

  5. 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]

  6. 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

  7. 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!

  8. 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. 🙂

  9. 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!

  10. vad roytman Reply

    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

    • thatjeffsmith

      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.

  11. 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

    • thatjeffsmith

      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.

  12. diego loureda Reply

    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

    • thatjeffsmith

      it should be using the gv$ views already…a RAC dashboard might come later assuming folks and like this first

    • diego loureda

      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!

  13. fernando somoza Reply

    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.

    • thatjeffsmith

      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

    • fernando somoza

      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!

  14. 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

    • 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

    • thatjeffsmith

      I’ve passed the feedback to the developer. I’ll let you know when there’s an update.

      And thanks!

Reply To Philip Moore Cancel Reply