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.
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).
If you move the mouse over the screen, you can spot areas surrounded by square blue lines.
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).
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).
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):
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:
and with the new (fetch all rows) option:
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: http://www.thatjeffsmith.com
Comments