Missing statements in SQL Monitoring

Since Oracle 11g, the database offers real-time SQL Monitoring, a great feature for insights of currently and previously running queries.
In most cases this works great without effort, but sometimes your statements won’t show.
This can be caused by statements with very large execution plans or statements with short execution times.

Monitor statements with very large execution plans

To include statements with very large execution plans in SQL Monitoring, we need to increase two thresholds. The following values served me well, but your millage may vary:

ALTER SESSION SET "_SQLMON_MAX_PLAN"=4020;
ALTER SESSION SET "_SQLMON_MAX_PLANLINES"=4000;

Remember to execute these commands in the same session and before the statements you want to monitor!

Monitor short running statements

To include short running statements into SQL Monitoring, we can use the MONITOR hint.

example:
SELECT /*+ MONITOR*/ * FROM DUAL;

Remeber that this hint is only valid when the parameter CONTROL_MANAGEMENT_PACK_ACCESS is set to DIAGNOSTIC+TUNING.

Execlude statements from SQL Monitoring

To exclude statements from SQL Monitoring, we can use the NO_MONITOR hint.

example:
SELECT /*+ NO_MONITOR*/ count(*) FROM DBA_OBJECTS CROSS JOIN DBA_OBJECTS;

Access SQL Monitoring

The SQL Monitoring informations can be accessed through

  • the tables V$SQL_MONITOR and V$SQL_PLAN_MONITOR
    (RAC:  GV$SQL_MONITOR and GV$SQL_PLAN_MONITOR)
  • a PL/SQL procedure:
    variable l_rep CLOB;
    exec :l_rep := DBMS_SQLTUNE.REPORT_SQL_MONITOR();
    print :l_rep
  • or with Oracle Enterprise Manager
Advertisements

SQL-Developer: Migrate settings files (settings, code templates, snippets, connections etc.)

If you, like me, have a lot of personal settings, code templates, snippets, connections etc. in SQL-Developer, you may want to migrate them to a new version or another computer from time to time. Here are the most important configuration files of SQL-Developer for migrations and backup:

User dependent files

  • Code templates:
    %USERPROFILE%\Application Data\SQL Developer\CodeTemplate.xml
  • Snippets:
    %USERPROFILE%\Application Data\SQL Developer\UserSnippets.xml
  • SQL history:
    %USERPROFILE%\Application Data\SQL Developer\SqlHistory.xml

SQL-Developer version dependent files

The numbers in the following paths vary with the version of SQL-Developer you have installed!
Please look for the appropriate directories of your installation.

  • Accelerators:
    %USERPROFILE%\Application Data\SQL Developer\system3.0.04.34\o.ide.11.1.1.4.37.59.31\settings.xml
  • Connections:
    %USERPROFILE%\Application Data\SQL Developer\system3.0.04.34\o.jdeveloper.db.connection.11.1.1.4.37.59.31\connections.xml
  • Syntax highlights:
    %USERPROFILE%\Application Data\SQL Developer\system3.0.04.34\o.sqldeveloper.11.1.2.4.34\product-preferences.xml

SQL-Developer: Change GUI language to english

SQL-Developer uses the same language for its GUI that you have set for your operation system. But when the GUI has another language than English, it’s hard to match it with help texts and the documentation!

It gets even worse! Some languages, e.g. German, don’t have all the menu entries the English GUI has!

So, if don’t like that you would have to change your operation system language, because you can’t set the GUI language in the preferences of SQL-Developer.

But there is a remedy for that – all you have to do is to add single line of text to a SQL-Developer configuration file.

Here is how:

  1. Close all open SQL-Developer instances.
  2. Find the “sqldeveloper.conf” file
    1. Windows:
      1. goto the directory where the SQL-Developer program is installed.
      2. goto the subdirectory “sqldeveloper\bin”
    2. Mac OS X:
      1. goto your applications folder (“/Applications”)
      2. open the SQLDeveloper-App via context menu (“right mouse click”)
      3. goto the subdirectory “Contents/Resources/sqldeveloper/sqldeveloper/bin”
  3. Open the file “sqldeveloper.conf” with the text editor of your choise.
  4. Add a new line with the following text to the file:
    AddVMOption -Duser.language=en
  5. Save the file.

The next time you start SQL-Developer, you will see that the GUI language is now english.

SQL-Developer: Using macros

Are you missing the feature to record and playback a macro in SQL-Developer?
Well, it’s there, just a bit hidden!

[Update: If you use SQL Developer version 4.01 or newer please read this post: Using macros – Update for version 4.01 ++]

Activate the macro functions

  1. Select “Tools/Preferences” from the main menu.
  2. Select “Shortcut Keys” in the list on the left.
  3. Enter “macro” the right search field.
  4. Assign “Ctrl+Period” to “Macro Playback”. Period is the “.” key on your keyboard.
  5. Assign “Ctrl+Shift+Period” to “Macro Toggle Recording”.

Record a macro

To start the recording of the macro, use an editor window of SQL-Developer and press the keys control (ctrl), shift and period (.) together.
The status bar on the bottom shows “Keystroke recording started.”

Any key you will press from now on will be recorded for replay through this macro.

When you are done press the keys control (ctrl), shift and period (.) together again.
The status bar on the bottom shows “Keystroke recording ended.”

There is only one drawback: You can only have one macro at a time. When you record a new macro the previous macro is gone!

Use the macro

Just press the keys control (ctrl) and period (.) together and your macro gets played back.

Example

Adding quotes and commas to a list
Ever wanted the get rid of manually adding quotes and commas to a list of strings?
With a macro it is easy to get from this:

Item 01
Item 02
Item 03

Item 99

to this:

‘Item 01’,
‘Item 02’,
‘Item 03’,

‘Item 99’,

Here is how:

  1. Go to the first line of the list.
  2. Start the recording by pressing the keys control (ctrl), shift and period (.) together.
  3. Press the following keys (pos1) (‘) (end) (‘) (,) and (“arrow down”) one after each other.
  4. End the recording by pressing the keys control (ctrl), shift and period (.) together again.
  5. Play back the macro by pressing the keys control (ctrl) and period (.) together.
  6. Repeat step 5 until the list is done.

SQL Developer: Execute current worksheet file via SQL*Plus (external tools)

TOAD for Oracle offers a nice feature called “Execute via SQL*Plus”, which executes your current TOAD file in a newly opened SQL*Plus session. It’s a great feature to execute SQL scripts in a controlled and fresh environment without the hassle of opening SQL*Plus, connecting to the database and executing your script manually.
Sad but true, SQL Developer doesn’t offer such a feature out of the box.

So here is how you can set it up yourself:

1. Select “Tools/External Tools” from the main menu.

2. Press the “New…” button in the “External tools” dialog window.

3. Program and arguments:

  • Program Executable:
    C:\WINDOWS\system32\cmd.exe  (May vary at your system!)
  • Arguments:
    /K start cmd /k sqlplus.exe ${sqldev.dbuser}@${sqldev.conn} @${file.path}
  • Run directory:
    ${file.dir}


4. Caption and icon
I have used the icon of the SQLDeveloper.exe here.

5. Menus and toolbars

6. Choose when the tool is enabled

7. Press the “Finish” button.

Now you can execute any file in SQL*Plus with a single button. Remember to assign a connection to your worksheet and to save your file before you start SQL*Plus.
When you start your new external tool, SQL*Plus will open a new database connection with the connection assigned to your worksheet and ask you for the password – that’s it.