Contents Lab128 - Tools for Advanced Oracler Tuning and Monitoring. Reference Guide.

SQL Central Basics

The SQL Central is a SQL editor with the capability to run a single query or a series of queries (script). The built-in repository can store SQL text or references to SQL files. For each executed query, Execution Statistics are collected (similar to Autotrace in SQLPlus). Optionally, the query can be executed with session settings "statistics=all" enabling collection of Plan Statistics. The bind variables are supported: the editor for bind variables automatically pops up, if bind variables are used.

The Concept.

The basic features SQL Central is SQL editing, query execution, and viewing the results. There are no limitations on opening more than one SQL Central windows and connecting, running SQL or editing concurrently in several windows. The SQL text can be stored for later use in a built-in repository. It uses a tree view to navigate between SQL scripts.

The basic unit of SQL Central is the SQL Page. When a SQL page is selected, its content shows in the editor panel. The content of the page can be viewed, edited, or executed. The page is a named object, so the name is the page's unique identifier. The page name can be changed only if the new name is unique. The name is shown in the SQL Central navigation tree view. The tree has only two levels: the Folder level and the Page level. As the naming suggests, the folder keeps a group of pages. The folders also have names that help to identify the group. Page names and Folder names can be viewed and changed in the "Page Properties" tabbed view as Repository Page Name and Repository Folder Name. The page name can be also changed by editing its name in-place in the tree.

The SQL Central built-in repository stores the Pages and their contents. There is a special type of page - the pages linked to the SQL files. In this case, the page stores the file name and its path in the repository. No actual text for this page is stored in the repository - the text is stored in the file itself. On the surface, pages of all types behave identically. The difference is only how the SQL text is stored. Deleting a page linked to a file does not delete the file.

The Folders in the navigation tree view use the icon, while the Pages use the icon. Some pages have red icons - these are template pages that cannot be changed. If you try to edit the content, the page will be cloned, placed in the template folder, and will be given an auto-generated name.

Tool Bar.

The Tool Bar includes shortcuts to miscellaneous operations in SQL Central which are self-explanatory. You can place a mouse pointer over the buttons and read hints. The two drop-down boxes "SCH:" and "USR:" allow to change current schema (equivalent of 'alter session set current_schema=...') and change of current user, which is similar to 'su' command in Unix. Note that only users with system privilege "become user" are able to change current user. DBA role typically includes this privilege by default. Also note that you must have Full version of Oracle client. If the "USR:" box is grayed, that means you are using Oracle Instant client.

Navigation Tree.

The Navigation tree helps find a needed page. The tree has two levels. It would make sense to place related pages in the same folder and give the folder a meaningful name. The page name can be edited in the Navigation tree by selecting it and clicking on it. Pages can be dragged and dropped from one folder to another.

When SQL Central is opened, it creates a blank page named "user.UnNamed_XX". You can use this page, or switch to another one. When a new blank page is needed, click on the first icon in the icon bar. If you need to open a file, click on the next icon.

The navigation tree has a self-explanatory pop-up menu, offering a list of actions for the selected page.

SQL Editor.

The editor provides SQL keyword highlighting and multilevel undo features. It automatically indents SQL statements based on ";" and "/" delimiters. PL/SQL blocks must be delimited by "/". The current SQL statement is identified by the icon. This is the statement where execution will start.

The different modes of SQL execution are:

You can set a breakpoint by clicking on the statement's indent mark. The Explain Plan can be called for the current SQL statement.

When text is selected, all execution modes are valid but the scope is confined by the selection.

When execution is in progress, the page containing running SQL cannot be edited. All other pages can be viewed and edited at that time.

If there was an error during execution of the SQL statement, the cursor is placed into the error spot and the error message is shown in the status line.

SQL Editor Hot Keys.

These are hot keys common in most editors, including SQL Central:
These are hot keys specific to SQL Central editor:

Query Result Set.

A query executed in the "Execute" (F9) mode returns a result set, which can be viewed in the lower grid. If the result set is big, only the first N rows will be presented. This is indicated by a status message above the grid. You can browse the result set by scrolling to the last or first row using the corresponding icons. Optionally, returned data can be viewed as plain text using "Text" tab of the view. The text format is used automatically for the execution of PL/SQL code to show output data from dbms_output package.

The "Exec Stats" tab is used to show Execution Statistics of last executed statement.

The SQL_ID or SQL Address/Hash value of last executed SQL statement is displayed in "Last SQL" active link. Click on this link to open Explain Plan window and show plan from v$sql_plan. This is an actual plan used during execution. Note that the plan obtained by "explain plan for ..." command ("Explain Plan" button in the toolbar) can differ from the actual plan during execution. The difference can be caused by default assumptions about data type of bind variables used by "explain plan for ..." command. In contrast, during the actual execution, the user has to provide the type and values themselves therefore the Oracle doesn't guess the type of variables.

The rows in the grid can be selected and copied onto the clipboard. To select an entire grid, click on the upper left empty cell of the grid. To include header names in a selection, click there again. The grid data can be searched by pressing Ctrl-F - this will bring up the Find Dialog window.

The cells can be selected using different ways: using the mouse or options in the popup menu. The selected cells can be copied into clipboard. The popup menu also allows copying column header(s).

When a query execution is started, the content of the grid from the previous query is not purged until the query is finished. During this time, the grid is shown with a reddish background, and the "Output from the previous query!" warning is posted in the status line.


Once a connection to Oracle is established, the reports are enabled. The reports are context-sensitive. For example, once you execute the SQL statement, the SQL level reports are enabled and will be providing data for that executed statement. The popup menu for the SQL editor contains report items. One very useful menu item is "Describe Object" which opens report for the object name on the cursor. Same action can be achieved by pressing F11.