Introduction

This is the user's manual for Query Plan. Although Query Plan is very simple to use, we recommend you to read completely this manual (it's not very large) in order to make better use of the program. Thank you.

Connecting

When you enter Query Plan, the connection panel is shown in the upper side of the window. There are the usual text boxes for an Oracle connection (username, password and connection string). Type the information in the boxes and click the "Connect" button. After connection, the panel will shrink to a one-line panel with a label telling you some info about the database you're connected and a "Disconnect" button in the right side. When you click it, the panel goes back to the original size so you can connect again.

Once a connection is successfully made, the data you entered are saved in the recent connections list. For convenience of the user, the recent connection list can be accessed, when the panel is in original size, in the combobox next to the "Connect" button. If you select an entry in the combobox, the connection information will be placed automatically in the text boxes. By default, passwords are not saved in the recent connections list (for security purposes), but you can alter this behavior by enabling the check box "Save Passwords" right below the connection information in the connection panel. You can also set the maximum number of entries to save (from 3 to 30).

Views Missing Dialog

After you connect, a dialog box may be shown reporting that some views are missing. That means that these views cannot be queried by the user you logged on. You should use an Oracle account that is able to see some V$ catalog views. The default manager account of all Oracle databases, SYSTEM, has this privilege. If you do not have access to SYSTEM account, ask the DBA to give you the privileges shown in this dialog box (the necessary GRANT commands are presented, for your convenience).

When the privileges are given, you can click the "Check Again" button to verify that Query Plan can now access the views. If all views are now accessible, the list of commands will become empty and the "Close" button will turn into an "OK" button. You click it and go on.

If you click "Close" without solving the missing views problem, you can still use Query Plan, but note that some features will not be available.

You (or the DBA) can choose to give the privileges to the views specifically, or give the more general SELECT_CATALOG_ROLE privilege. This is a role that will enable the user to see all catalog views. However, in this case you may be required to disconnect from Query Plan and connect again to the user when granting SELECT_CATALOG_ROLE in order to the views be accessible. If you click "Check Again", Query Plan will continue to report that views as missing, but if you reconnect, the Missing Views window will not be shown.

Querying Cursors

Cursors are SQL statements stored in the SGA (Oracle server memory area). They are there because they were once parsed and maybe executed by some user. Querying the cursors may provide useful information about performance of these statements. This is what Query Plan was built for.

The dynamic performance view used by Query Plan to get the cursors is V$SQL. It is beyond the scope of this manual to explain the meaning of the columns in this view, but it can be found in Oracle documentation (Oracle Database Reference Manual). To find the cursor you want to analyze, you must define the WHERE (and optionally the ORDER BY) clause for the V$SQL view, and enter it in the text box at the left side.

A simple working query is presented as default. You can edit it manually, if you are acquainted with the V$SQL columns, or you can also call the Where Clause Assistant by clicking the button beside the text box (the button with "..."). This window shows combo boxes with possible predicates and order by clauses you may want to use. Once you click OK, the where clause will be created and copied to the text box (WARNING: it will overwrite the existing one). Note that the Where Clause Assistant provides predicates with fixed values, but you can change them afterwards (in the text box). It also does not save its state: every time you click in the button it will appear blank again (so, be careful to not to lose your query: if in doubt, click Cancel, this will close the assistant without erasing your query). The Where Clause Assistant should be viewed as just a helper, we encourage the users to get familiar with the V$SQL view so to be able to refine your query manually.

To retrieve the cursors you click on the "Refresh" button (of course, you must be connected). This will query the V$SQL view and show the results in the area below. The results are limited to a maximum of 30 statements.

After the query, each cursor is presented in a one-line panel in the results area, that shows the first characters of the SQL statement. To expand the panel and see additional information, click in the down-arrow button in the right side. The panel will expand and show more information about the cursor. Each value correspond to the column in V$SQL with the same name. Again, it is not the scope here to explain each value, but if you pass the mouse over the label, a tip text will show a short description of each data.

For the time columns (CPU and Elapsed Time), the unit may be presented in seconds (s), miliseconds (ms) or microseconds, which is the default (in that case, no symbol is presented).

When done with this cursor, you can click the up-arrow button to return it to the shrunk state.

Viewing the Execution Plan

When expanded, the cursor panel shows a "View Plan" button. If clicked, this button opens a new window where the user can see the complete SQL statement and its execution plan (if available). The execution plan is taken directly from the stored informations in the SGA (from V$SQL_PLAN), so no PLAN_TABLE needs to be created. This has also the advantage to get the actual execution plan used for the statement, that is, the execution plan generated when it was last parsed.

In the execution plan tree view, when you select a specific step from the plan, the "Execution Plan Step Info" panel will show detailed information for the selected step in the tree. This information can help deeper analysis of the execution plan, identifying bottleneck points. This information is taken from V$SQL_PLAN itself and from V$SQL_PLAN_STATISTICS_ALL, when available. Again, if you pass the mouse over each label, a tip text will show a short description of its meaning.

Known Limitations

Version: Query Plan works only on Oracle Database version 9.0.1 or later.

Connecting to a SYS account: Currently, Query Plan cannot connect to a SYS account, because Oracle does not allow a connection to this user in normal mode (should be SYSDBA or SYSOPER) and Microsoft .Net Provider for Oracle does not provide a way to set the connection mode. You can workaround this limitation by connecting as user SYSTEM, or any other that have SELECT_CATALOG_ROLE privilege.

Accessing this Manual

From inside the program you can access this user manual by clicking the "User Manual" button.

License Window

As stated in the License Agreement, you have the right to use the trial version for 30 days for evaluation purposes. After that, you must buy a license key or uninstall the program. This program has a feature that will block usage after the trial period. In fact, depending on your use of the tool, the blocking algorithm may give you a few more days after expiration. You may consider that a bonus trial.

If your choice is to buy a license key to continue using the product, you can buy it at our site www.priodata.com. After receiving your license key (which is a string with 54 characters, letters and numbers, uppercase/lowercase matters), you can patch it into your installed version, even if it has already blocked. The button "About/License" remains unblocked to give you access to the License Window.

Clicking that button will open the License Window. This window displays copyright and version information and provides a text box for you to input the license key. Type it (or copy and paste from the email you received it) and click the "Install License" button. If the license is recognized as valid, the box and the button vanish and the License Window now shows version information for a Licensed Version and your license number. If the program was blocked it is now unblocked.

If your license key is not recognized, check if you typed it correctly, remove blank spaces that may have been interposed by email transfer, and check uppercase and lowercase letters, Priodata's license mechanism is case-sensitive. If you still have problems, you can ask email support (at ).

If you want to transfer your license to a different computer, click the "Uninstall License" button. The text box with the license will show up again and you can cut and paste the license to another computer. Please note that you are not allowed to keep the same license installed in more than one computer, except in special cases specified in the License Agreement. So, after you copy it, you must not reinstall the license in the first computer. After uninstalling a license, the installed version reverts back to the trial version. If the trial period is expired, it will block.

________________________________________