SQL Developer has been my favorite tool for database development and administration from the beginning, as it has many advantages over commercial tools, neglecting the price difference.

The name of the tool is a bit misleading as it suggest that the target audience are database developers, which is not correct.

The main progress during the last several years is particular made in the area of database administration.

Latest version of the SQL Developer (4.1) has many interesting features, but in this article I’ll cover only three of them which are particularly useful from the performance tuning perspective, and I’ll show you how to use it in practice.

 

1.

DB Instance viewer

This feature is not only proof of concept what the latest version of the Java FX can do (this is what of the reasons why SQL Developer 4.1 requires JDK 8), but it provides all information for database monitoring.

First let setup DB Instance viewer.

Check if you can see DBA window. If not go to the View–>DBA.

After that you need to setup account with privileges required to monitor the database.

At the end you need to click on the DB Instance under the Database Status as shown on the following picture.

006_1

After that, the new DB Instance tab will show up.

If you want to detach DB Instance window from the SQL Developer (for example to show on the large screen), go to Window–>Configure Window–>Float (or right click on the DB Instance tab).

006_4

If you move the mouse over the screen, you can spot areas surrounded by square blue lines.

006_3

When you double click on it, new tab in SQL Developer will be opened related to the performance field shown on the screen (in this case related to Waits).

006_5

Performance analyst and/or DBA can start searching in depth what was going on.

2.

Statement Log

At this point we are approaching the second great feature of SQL Developer related to performance analysis and tuning.

In the screen above you can change refresh rate which is currently 0 (which means no refreshing) into 5 (5 seconds refresh rate).

You have to have Log view enabled (View–>Log).

After that you can track exactly what SQL Developer is sending through the JDBC connection to database.

In this case we can track how we get the Waits part of DB Instance (Statement Log has been refreshed automatically – in this new commands are shown every 5 seconds as we set refresh interval for Wait view the same interval).

006_6

The following two statements executes each 5 seconds:

SELECT c.WAIT_CLASS,
SUM(m.WAIT_COUNT)                AS WAIT_COUNT,
ROUND(SUM(m.TIME_WAITED)/100, 3) AS TIME_WAITED
FROM V$WAITCLASSMETRIC_HISTORY m,
V$SYSTEM_WAIT_CLASS c
WHERE m.WAIT_CLASS# = c.WAIT_CLASS#
AND c.WAIT_CLASS   != ‘Idle’
GROUP BY c.wait_class
ORDER BY 1 NULL;

SELECT e.inst_id,
e.event,
e.sample_count
FROM
(SELECT inst_id,
wait_class_id,
event,
COUNT(*) sample_count
FROM gv$active_session_history
WHERE sample_time >= sysdate – 1/24
GROUP BY inst_id,
wait_class_id,
event
) e,
(SELECT wait_class_id,
wait_class
FROM V$SYSTEM_WAIT_CLASS
WHERE wait_class=:WAIT_CLASS
) c
WHERE e.wait_class_id    = c.wait_class_id
ORDER BY 1,2 “WAIT_CLASS”=12;

In the same way you can reverse engineering every functionality of the SQL Developer, and learn how to get some functionality from the pure SQL.

 

3.

Autotrace – Fetch all rows option

Autotrace option is one of the tools/Db features that I’m using very often.

I’ll leave the theoretical part, as It’s not in scope of the SQL Developer new features topic, but I want to point that in the latest version it is possible to get the Autotrace statistics based on the all rows.

To enable this feature, go into the Tools–>Preferences–>Database–>Autotrace/Explain Plan and click on Fetch all rows (see the next picture):

006_7

I’ve created GTT table (on commit delete rows) based on all_objects table with the following script:

create global temporary table objects_gtt

on commit preserve rows

as

select * from dba_objects;

Next picture is showing the default behavior of Autotrace in SQL Developer:

006_autotrace_default

and with the new (fetch all rows) option:

006_autotrace_fetch_all_rows

As you can see, with new option, you can get realistic statistics, as it’s created when the SQL Developer fetches all rows.

You can learn much more about general new features of SQL Developer from the Jeff Smith blog in the following link: https://www.thatjeffsmith.com

 


Get notified when a new post is published!

 

Loading

Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.