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.
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.
View the latest Profiler Extension change log here.
- Download and install SQL Developer from
- In SQL Developer, select Tools > Preferences, then select the Extensions tree item.
- Click Check for Updates.
- If the Welcome dialog appears, click Next. The Source dialog appears.
- 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):
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.
- To install or update over the internet, click Add. The Update Center dialog box appears.
- In the Name field, enter: Hotsos Extensions.
- In the Location field, the copy and paste the URL below for your version of SQL Developer:
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
- Click OK. The Source dialog appears.
- Click Next. The Updated dialog appears.
- Click the checkbox next to the Hotsos Extensions you want to install, then click Next to download. The Summary dialog appears.
- Click Finish. The Preferences dialog appears.
- Click OK. The Confirm Restart dialog appears.
- Click Yes to restart SQL Developer.
- 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.
- 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
To enable the Hotsos Profiler Extension, do one of the following:
Enabling Hotsos Profiler Extension
- 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.
Generating a ProfileTo generate a profile:
- Enter the statements you want to profile in a SQL Worksheet.
- Press F5 or click the Run Script icon.
Creating a Directory ObjectThe 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:
- 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.
- Enter the password for the SYS account if you have selected one of the Grant access options.
- Click OK.
The script used to grant access for the Profiler Extension is below:
v_schema VARCHAR2(40) := '"PUBLIC"';
select value into v_user_dump_dest
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 ;
Working with the Profiler Extension HistoryThe 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 PreferencesThe preferences govern the Hotsos Profiler Extension output and how the Extension appears in SQL Developer.
To set preferences for the Profiler SQL Developer Extension:
- Select Tools > Preferences. The Preferences dialog appears.
- Select the Hotsos Profiler tree item. The Hotsos Profiler Configuration Parameters appear.
- 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.
Setting Up LoggingThe 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 LogTo clear the Hotsos Profiler Extension log, right-click on a Hotsos Profiler line in the log panel, then select Clear.
Changing the Logging LevelAfter 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 FileYou can save logging messages to a file by setting up the directory for log files in the SQL Developer preferences.
- Select Tools > Preferences. The Preference dialog appears.
- Select the Environment > Log tree items. The Environment:Log dialog opens.
- Check Save Logs to File, then enter a Log Directory or click Browse to find a directory.
- 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: