Personal tools
You are here: Home Products Extensions for Oracle SQL Developer
Document Actions

Hotsos Extensions for SQL Developer

Why You Need the Hotsos Extensions for SQL Developer

Developers can now take control of performance diagnostics and tuning right from the SQL Developer IDE. Hotsos provides the tools needed to know what's happening in the Oracle database. Our first extension harnesses the power of the Hotsos Profiler right on the developer's desktop. Execute a batch of SQL or PL/SQL commands and immediately view a Profile of the server generated trace file.

Prerequisites

Hotsos Extensions have been tested for versions 1.2, 1.5, 2.1, 3.0, and 3.1 of SQL Developer. The Hotsos Profiler Extension requires an existing Hotsos Profiler installed version 5.1.17 or higher, and requires Oracle database 9.2 or higher.

Change Logs

View the latest Profiler Extension change log here.

Installation Instructions

  1. Download and install SQL Developer from OTN
  2. In SQL Developer, select Tools > Preferences,  then select the Extensions tree item.
  3. Click Check for Updates.
  4. If the Welcome dialog appears, click Next. The Source dialog appears.
  5. If you do not have internet access on the machine with SQL Developer, the zip archive of the current version may be obtained from your Hotsos Sales Representative or from our portal as follows (select one of the following for the version of SQL Developer you are using):
    SQL Developer
    Version
    Download Link
    SQL Developer 1.2 http://portal.hotsos.com/products/sqldevext/pub/hotsosprofilerext/at_download
    SQL Developer 1.5 http://portal.hotsos.com/products/sqldevext/pub2/hotsosprofilerext15/at_download
    SQL Developer 2.1 http://portal.hotsos.com/products/sqldevext/sqldevext212/sqldevext212.zip/download
    SQL Developer 3.x http://portal.hotsos.com/products/sqldevext/sqldevext310/sqldevext310.zip/download

    To install using this file, click Install From Local File and fill in the File Name appropriately. Click Next. Skip to step 12.
  6. To install or update over the internet, click Add. The Update Center dialog box appears.
  7. In the Name field, enter: Hotsos Extensions.
  8. In the Location field, the copy and paste the URL below for your version of SQL Developer:
    SQL Developer
    Version
    Extension URL
    SQL Developer 1.2 http://portal.hotsos.com/products/sqldevext/pub/sqldeveloper.xml/at_download
    SQL Developer 1.5 http://portal.hotsos.com/products/sqldevext/pub2/sqldeveloper.xml/at_download
    SQL Developer 2.1 http://portal.hotsos.com/products/sqldevext/sqldevext212/sqldevext212.xml/download
    SQL Developer 3.x http://portal.hotsos.com/products/sqldevext/sqldevext310/sqldevext310.xml/download
  9. Click OK. The Source dialog appears.
  10. Click Next. The Updated dialog appears.
  11. Click the checkbox next to the Hotsos Extensions you want to install, then click Next to download. The Summary dialog appears.
  12. Click Finish. The Preferences dialog appears.
  13. Click OK. The Confirm Restart dialog appears.
  14. Click Yes to restart SQL Developer.
  15. If you are running the Profiler Extension for SQL Developer 2.1 with Profiler version 5.2.x, you may be prompted to select the Profiler home directory when SQL Developer restarts. Simply browse to the location where you installed Profiler. This location will be saved in the preferences so you will not need to specify it the next time you start SQL Developer.
  16. If the SQL Worksheet does not display correctly, or if the Hotsos Profiler Extension does not appear to be installed, close SQL Developer and restart it again. This is necessary to circumvent a bug in the SQL Developer 1.2 auto-update Extension.

How to Use the Hotsos Profiler Extension for Oracle SQL Developer


Enabling Hotsos Profiler Extension

To enable the Hotsos Profiler Extension, do one of the following:
  • Select Hotsos Profiler > Profile Statements. The P5 icon next to Profile Statements indicates if the Profiler Extension is enabled. The icon appears in color when the Extension is enabled and is gray when it disabled.
  • Click the P5 icon in the toolbar. The icon appears in color when the Extension is enabled and is gray when it disabled.
If the icon will not show the enabled state, verify that Hotsos Profiler has been installed, the preferences are set correctly (see Setting Up Preferences section), and that the P5 environment variables are set correctly.

Generating a Profile

To generate a profile:
  1. Enter the statements you want to profile in a SQL Worksheet.
  2. Press F5 or click the Run Script icon.
Profiler runs, and when it is finished, an HTML document opens in your default browser, if you have selected that option in your preferences.

Creating a Directory Object

The Hotsos Profiler Extension requires a directory object and permissions to the system views which allow access to the trace files on the database server. Your DBA can execute a script (which appears in the Create Directory Object dialog) to provide this access, or you can enter a password and select an option to execute the script automatically.

When this dialog appears:
  1. Select one of the following options:
    DBA has executed script manually.
    Select this option if your DBA has already granted the required access.
    Grant access to PUBLIC (all accounts).
    Select this option if you want to grant the required access to all accounts.
    Grant access to account only.
    Select this option if you want to grant the required access only to the specified user.
  2. Enter the password for the SYS account if you have selected one of the Grant access options.
  3. Click OK.

The script used to grant access for the Profiler Extension is below:

DECLARE
  v_schema VARCHAR2(40) := '"PUBLIC"';
  v_user_dump_dest VARCHAR2(2000);
BEGIN

  select value into v_user_dump_dest
  from v$parameter
  where name = 'user_dump_dest';

  EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY "HOTSOS_UDUMP_DEST" AS ''' || v_user_dump_dest || '''' ;
  IF v_schema != '"SYS"' THEN
    EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY HOTSOS_UDUMP_DEST TO ' || v_schema ;
    EXECUTE IMMEDIATE 'GRANT ALTER SESSION TO ' || v_schema ;
    EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$INSTANCE TO ' || v_schema ;
    EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$SESSION TO ' || v_schema ;
    EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$PROCESS TO ' || v_schema ;
    EXECUTE IMMEDIATE 'GRANT SELECT ON SYS.V_$MYSTAT TO ' || v_schema ;
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON DBMS_UTILITY TO ' || v_schema ;
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON DBMS_LOB TO ' || v_schema ;
  END IF;
END;

Working with the Profiler Extension History

The Hotsos Profiler Extension downloads copies of trace files to the directory you specify in the Preferences. You can view the history of the Profiler Extension session, open the Profile output files, or clear those files from the history.

To view the Profiler Extension history, select View > Hotsos Profiler History. The history appears as a docked tab in SQL Developer.

To open one or more Profile output files from the history, select the desired files in the History panel, then click Open. The files open in your default browser.

To clear files from the history, click Clear in the History panel. All history will be cleared.

Setting Up Preferences

The preferences govern the Hotsos Profiler Extension output and how the Extension appears in SQL Developer.

To set preferences for the Profiler SQL Developer Extension:
  1. Select Tools > Preferences. The Preferences dialog appears.
  2. Select the Hotsos Profiler tree item. The Hotsos Profiler Configuration Parameters appear.
  3. Make the desired changes. The Configuration Parameters are described below:
    Open output file in external web browser
    Check this box if you want the Profiler output to automatically open in your default web browser when you profile code.
    Enable Profiler on startup
    Check this box if you want the Profiler Extension to be enabled when you start SQL Developer.
    Profiler home directory (new in version 2.1.1)
    Click the browse button to select the home directory where Hotsos Profiler is installed.
    Directory to save trace files
    Enter a directory path or click Browse to find a directory. All trace files generated with the Hostos Profiler Extension will be stored in the specified directory.
    Call definitions
    Enter a call definitions file location or click Browse to find a call definitions file. The Profiler User Guide contains more information about specifying call definitions. To see the Profiler User Guide, select Start > All Programs > Hotsos > P5 > Profiler > User Guide.
    Transform parameters
    Enter a transform parameters file location or click Browse to find a transform parameters file. The Profiler User Guide contains more information about specifying transform parameters. To see the Profiler User Guide, select Start > All Programs > Hotsos > P5 > Profiler > User Guide.
    Maximum trace file size (MB)
    Enter or select the maximum trace file size you want to automatically download when you profile code. If the trace file that is generated exceeds this size, you will be asked to confirm the download, or the download will be skipped without confirmation, depending on the setting for Confirm if limit is exceeded.
    Confirm if limit is exceeded
    If this box is checked, you will be asked to confirm the trace file download if the file exceeds the value specified in Maximum trace file size (MB). If this box is not checked, the download will be skipped without a confirmation if the limit is exceeded.
    Maximum Java heap size (MB)
    Select a maximum size for the Java heap during execution of the Hotsos Profiler.

    Logging

    Setting Up Logging

    The Profiler Extension uses SQL Developer’s built-in logging facility. To enable logging initially, edit the file logging.conf, found in the sqldeveloper\bin subdirectory of your SQL Developer installation. Add the following line to this file:

            hotsos.level = INFO

    Valid logging levels include: OFF, SEVERE, WARNING, INFO, CONFIG, FINE, FINER, FINEST, and ALL. Levels FINE through ALL can be used to show debugging output. Once the logging level is initially set, you can change the logging level through the SQL Developer interface.

    Clearing the Log

    To clear the Hotsos Profiler Extension log, right-click on a Hotsos Profiler line in the log panel, then select Clear.

    Changing the Logging Level

    After you have initially set up logging for the Hotsos Profiler Extension, you can set the logging level from the SQL Developer application.
    To set the logging level, right-click on a Hotsos Profiler line in the log panel. Highlight Change Log Level and select a level from the flyout menu.

    Saving Log Messages to a File

    You can save logging messages to a file by setting up the directory for log files in the SQL Developer preferences.
    1. Select Tools > Preferences. The Preference dialog appears.
    2. Select the Environment > Log tree items. The Environment:Log dialog opens.
    3. Check Save Logs to File, then enter a Log Directory or click Browse to find a directory.
    4. Select any other desired options, then click OK.

    NOTE: Saving the log to a file does not work yet in SQL Developer, but should in a future version. To control the location of the log files, add this line to your logging.conf file, modifying the directory to your desired location:

            java.util.logging.FileHandler.pattern=C:\\SQLDeveloper\\sqldeveloper\\log\\sqldev%u.log

Powered by Plone, the Open Source Content Management System

This site conforms to the following standards: