Oracle Database Enterprise Edition customers have the option of extending their functionality set via various optional ‘packs.’ One of the most popular packs is the Oracle Diagnostics Pack.

Funny, most people call it the ‘Diagnostic’ Pack, but it’s technically known as the Diagnostics Pack. Anyways, the pack includes a LOT of features. You can see all of them listed here.

Now in previous versions of Oracle SQL Developer, we have exposed a few of these features via several reports, including:

  • ASH Report For the Past 30 Minutes
  • ASH Rows Detail
  • Daily ASH Statistics Chart
  • Last AWR Report

In version v4.0 (new for Early Adopter 2!), we have greaty expanded the support for the Diagnostics Pack. But instead of building additional reports, we’ve created an entirely new Performance node in the DBA panel:

To access the DBA panel, open it via the View menu, and then add a connection.
To access the DBA panel, open it via the View menu, and then add a connection.

Before using any of these features, you’ll be asked to confirm that you do indeed have licensed said features.

There’s too much to go over in a single post here, but I’ll cover a few basic scenarios.

Running an AWR Report

The first thing you need to do is decide what time period you want to run the report against. AWR stands for Automatic Workload Repository. The repository contains performance and diagnostic stats for a period of time collected via snapshots. These snapshots are setup to be taken on a regular interval.

You can optionally create a baseline which is simply a pre-defined stand and end group of snapshots. These can be easier to reference than manually picking the start and end snapshot periods.

You can see the available snapshots and the time period they cover.
You can see the available snapshots and the time period they cover.

Now, what if you want to see what was going on for the past few minutes and there’s no snapshot to cover that activity? Well, you could go into the Active Session History (ASH) bits, but since we’re talking about AWR here, I’ll mention you can force a new snapshot to be taken on demand.

Simply open the Snapshots tree and right click.

You can also delete snapshots from here.
You can also delete snapshots from here.

Now with my snapshot newly created and available, I can go back to my AWR report viewer and select it.

Now hit the ‘Go’ button 🙂

The AWR report viewer toolbar
The AWR report viewer toolbar

The report will now be generated and displayed inside of SQL Developer. Since we can render basic HTML, it’s not an issue. You’ll find the in-report hyperlinks are navigable. But if you want a little nicer presentation, you can send the report to your default web browser using this button;

This sends the report to your browser
This sends the report to your browser

Like so…

The report is spooled out to your OS/User temp directory
The report is spooled out to your OS/User temp directory

If you want a more permanent version of the report, you can also save it using the appropriate toolbar button. Hint, it’s the button that looks like a disk 🙂

I Want to Know More About A Particular Query

If I’m looking at my Top SQL portion of my AWR report, I can pull out a SQLID of note, and then feed that to the AWR SQL Report Viewer.

Don't know the SQLID? You can browse the available ones for the given snapshot time range.
Don’t know the SQLID? You can browse the available ones for the given snapshot time range.

One More Thing

There’s nothing more frustrating than running a report only to notice it’s not the report you wanted. And instead of having to start completely over, you can simply use the ‘Toggle Inputs’ button and tweak your settings. Me likey A LOT. And I didn’t even ask for this bit, the developer came up with it himself. And why am I surprised…. 😉

Reconfigure your settings and re-run the report as needed.
Reconfigure your settings and re-run the report as needed.

I also like that the SQLID and Snapshot ‘pickers’ are smart enough to auto-scroll in the grid to that particular value. It’s these little things I think that will make the users happy. Having the reports is nice, but making them easy to generate and work with is what will bring them back.

Author

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

43 Comments

  1. I have a question that i have a RAC setup and i connect to my db using SCAN ip.how to generate the reports from both nodes at once using sql developer because when i connect it will connect using only one node.

    • Your data is the same, regardless of which Node instance you’re connected to.

      Unless you mean about the instance itself? Look at the V$ vs GV$ views.

  2. Sunil Kumar Noothi Reply

    Below is my version of sql developer and I logged in as SYSTEM user, but not able to see performance tab, can you please advise me, how?

    About
    —–

    Oracle SQL Developer 17.2.0.188
    Version 17.2.0.188
    Build 188.1159

    IDE Version: 13.0.0.1.42.170225.0201
    Product ID: oracle.sqldeveloper
    Product Version: 17.2.0.188.1159

    Version
    ——-

    Component Version
    ========= =======
    Oracle IDE 17.2.0.188.1159
    Java(TM) Platform 1.8.0_131

  3. Thanks for another helpful/incredibly informative post, Jeff!
    I’m grateful your blog exists 🙂

    • Without awesome users/customers like yourself, it wouldn’t! So you should also thank yourself 🙂

      And someone taught me recently that I should be better at accepting compliments, so let me also say, thanks!

  4. Matthew Sultana Reply

    Is there a way to get the AWR generated using SQL Developer to display more than 10 top sql’s. I know it can be done manually but wish to use the performance Node.

  5. Hi Jeff,
    I run the AWR report on 2 node RAC from the DBA panel succesfully . After shutdown node1 Instance combo disappear. When I run it for snaps taken from node2 gives ora-20020 database/instance/snapshot mismatch.

    Thanks
    E. Gumusay

  6. Jeff,
    I like what you guys are doing with SQL Developer…keep it up! One quick question….any idea why my AWR Report Viewer only has the ” Freeze Content” and “Refresh” buttons available for use. The other buttons are visible but “grayed out”. When the form is initially displayed all buttons are available for selection for a split second and then most of them are quickly “grayed out”. I’m almost 100% sure I’ve viewed AWR reports from this version of SQL Developer (4.0.0.13 build 13.80) before. Can you pass along some trouble-shooting tips that I can use to resolve this problem. Looking forward to hearing from you and reading more great tips!

    Thanks…
    Lloyd

    • That’s a bug. And it was a REAL PITA to fix. But a few plucky customers were able to give us enough run-time logs to finally nail it down. And it’s fixed for an update that’s due soon. So stay tuned!

    • Hello Jeff,
      has this error been fixed. I seem to be getting the same error with the latest sql developer version 17.3.
      Could you please direct me to the bug fix. I guess it must be an operating system error, because it suddenly started appearing on all the old version too. I am on windows 7 64 bits.

      Thanks Chudi

    • No idea, it’s not a current known issue. You should open a Service Request with My Oracle Support.

  7. Hi Jeff,
    thanks for the article and your very useful blog in general.
    If i want to allow a non-dba user to be able to generate awr reports with sql developer 4, what minimum permissions do i need to grant to that user?
    It works with the following:
    GRANT SELECT ANY DICTIONARY TO username;
    GRANT EXECUTE ON SYS.DBMS_WORKLOAD_REPOSITORY TO username;
    However, to grant “select any dictionary” is not allowed in our company. I tried it with granting SELECT_CATALOG_ROLE, but then the user gets ORA-00942 when clicking on awr in sql developer.

    • George – Just run ‘SQL> GRANT SELECT ON sys.wrm$_wr_control TO ;’ to fix the ORA-00942 error, that way you can use select_catalog_role instead of select any dictionary.

  8. Vishal Desai Reply

    Hi Jeff,

    I have reports in DBA and Reports section. How do I consolidate all reports under one umbrella?

    Thanks,
    Vishal

    • Not sure what you mean, there aren’t any ‘reports’ per se under the DBA menu. Those are interactive screens for the most part. If you’re saying you want one place to get all the information provided between those 2 interfaces, then I don’t have a good answer. There is some overlap between the two.

  9. Please don’t turn SQL Developer into a one-size-fits-all solution to every aspect of databases.

    If there hadn’t been so many extra features might we already be using v4?

    • You’d rather we quit doing work on it, and just ship it? I guess we could do that, but you’re currently outnumbered by the folks who’ve been asking for this for years. It’s not a casual thing we just threw in for giggles.

      We’re the database IDE/GUI. Some widgets you’ll use, some you won’t. But the widget you can live w/o is the widget someone else will use everyday.

    • No offence intended, but bloat is bad in my opinion. It’s called SQL Developer, not database GUI.

    • No offense taken, but it is more a database GUI than just a simple development tool for SQL coders. Hence our product description on our Oracle.com page

      Oracle SQL Developer is a free integrated development environment that simplifies the development and management of Oracle Database. SQL Developer offers complete end-to-end development of your PL/SQL applications, a worksheet for running queries and scripts, a DBA console for managing the database, a reports interface, a complete data modeling solution, and a migration platform for moving your 3rd party databases to Oracle.

  10. hi !

    perfomance node don’t exists on sqldeveloper 4.

    with the same user, toad works fine on awr

    • fernando silva

      …. and it sounds right …. thank you !

      it’s hard to check version on sqldeveloper …..

    • In Fernando’s defense, it doesn’t say version 4 EA1 or 4 EA2

      it says version 4.0.0.12
      Build Main – 12.84

      on the version tab it shows
      Oracle IDE 4.0.0.12.84

  11. Sunil Kumar Noothi Reply

    Jeff, what if am not a SYS User? can I still collect an AWR Report? I logged in as a SYSTEM User but couldn’t find Performance tab?

  12. Jeff, this looks really good. one thing I noticed today about EA2 is

    1) AskTom search bar is missing completely in both EA1 and EA2. Are we not going to support that after 3.2 SQL developer any more?

    2) AWR -> SQL report viewer , when you change the start ID and End ID by snapshots for the second time, the SQL ID’s displayed in the Browse drop down button is not getting refreshed. It still retains the SQL ID’s of the Previous start ID and Previous End ID’s

    • Yes, we killed the search bar. We figured if you want to search the internet you would use your browser anyway.

      #2 sounds like a bug, if I can confirm that, I’ll log it. Thanks for the heads-up Rajesh!

    • Jeff,

      When I run this AWR against 3 node RAC database, I got ORA-01427 sub query return more than one row error message, screen print emailed to your gmail account ([email protected])

    • I just logged that bug – a particularly icky one – this morning. We’ll get it going for RAC ASAP.

    • Jeff,

      Today I found a display issues with 4.0 EA2 and 3.2.20.09

      Run this script in Database.
      create table t(x number);
      insert into t values(1);
      commit;

      Run this query ( select * from t; ) in both 3.2 and 4.0 EA2, I got the display perfect in 3.2 but not in 4.0 EA2. ( I shared you the screen print to your gmail account [email protected]) . Can you please check and revert ?

    • That’s not my GMAIL address, but I think I know what you’re talking about – the column/cell width taking up the entire panel to fit on screen?

      Yes, that’s a bug and will be fixed!

  13. Rinie Romme Reply

    Hi Jeff, great article! I’m very glad with these new features

Write A Comment