).PrioData Select Fast!
VERSION 1.0 - USER MANUAL
This is the user's manual for Select Fast!. Although Select Fast! 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. We intentionally wrote this manual as an overview, to make it short, leaving some details to the program user interface itself. While using the program, you will see many labels and message boxes alerting you to important information. Please consider these resources as a complementary documentation. Thank you.
Select Fast! is a tool to assist the process of analyzing and improving SQL execution (aka "SQL tuning"). It accomplishes this by providing an easy interface to make query runs, set bind variable and parameters, collect statistics and execution plans and save this logical structure as a whole (in "Analysis Files").
You begin your analysis by creating "queries", which are different versions of the query you want to tune (or simple different queries, the program does not make any assumption about them). Typically, you will start with one version, which is the one you have and want to analyze. Then you will try different strategies for optimization (changing query/subquery structure, using hints, etc.) that will create different versions. Select Fast will keep track of all versions you create.
For any "query", you can create one or more "parametrizations". A "parametrization" is a set of bind variable values and initialization parameters values (selected among those that can affect query optimization).
Finally, under the "parametrizations", you can generate "executions" or "plans":
An "execution", is a set of statistics collected after a real execution of the query on some database.
A "captured plan" is an execution plan obtained by parsing the query and retrieving the plan steps from V$SQL_PLAN. It also brings information for the last execution for the workareas, (from V$SQL_PLAN_STATISTICS_ALL), when available.
A "generated plan" is an execution plan generated by the EXPLAIN PLAN command.
This is the basic structure of an analysis, the individual elements will be covered in details in the following sections.
You manage connections from Select Fast! to Oracle databases with the Manage Connections window (accessible through Menu -> Connections -> Manage Connections...). Inside this window, you will provide the usual information (username, password and connect string) to all connections you wish to use in your analysis. This information will be included in the Analysis File when you save the analysis. By default, no passwords will be saved in the Analysis File (for security reasons, since you may be sharing these files with others), but you can change this behavior, if you want, in the Preferences window. You can configure Select Fast! to always save passwords, or to ask again for each save operation, if you want to have fine control when to save or not your passwords.
Typically, you will use one connection for each schema you may require to run your queries on (and/or capture and generate plans), plus one extra connection for each database, to access the V$ views, only if the other ones do not have such privileges. When required, Select Fast! will detect automatically which connection it can use in order to query the V$ views for statistics and captured plans. However, this connection must be open for it to be detected.
At any given time, only one of the connections can be selected as "the active connection". The active connection is the one with which the queries will be executed, plans will be generated, and queries will be parsed to have captured plans. That is, the connection that will "run" the query, in every way. The other ones may be used behind-the-scenes by the program, to gather execution statistics, or to read the V$SQL_PLAN view in order to capture the plan. This is a key feature of Select Fast!, you do not have to get special privileges in the account that runs the query to make your analysis. You can change the active connection in the Manage Connections window (by clicking the "Make Active" link) or directly in the main window, by selecting it in a combo box.
Please note that more than one real database connection may be created for each connection information you provide in this window, as new connections may be needed to perform simultaneous operations.
In Select Fast's main window there are the following elements:
1) The browser: at the left, it is a tree-structured view of your analysis, with all queries, parametrizations, executions and plans you have created. Each time you select an item, the other elements are changed in order to display information about it.
2) The message log: at the bottom, it is a log panel where you receive messages from the system based on ocurring events.
3) The query editing panel: it is a panel with a text box for editing the query text, a combo box to select the active connection, and buttons to make the main operations (generate/capture plan, execute query, store query). These commands can also be accessed by a context menu in the browser, or the main menu.
4) The statistics viewing panel: it will show execution statistics for the selected execution (if an execution is the selected item in the browser), or for the last execution of the same parametrization (if the selected item is a plan, or a parametrization). If the selected item is a query, it will show the last execution for the last parametrization.
5) The plan viewing panel: it will show the execution plan (whether generated or captured) for the selected item in the browser, or the last plan of the same parametrization (if the selected item is an execution, or a parametrization). If the selected item is a query, it will show the last plan for the last parametrization.
After you connect to each account, Select Fast! gathers information about what system views and privileges are available to it.
When you run some operation, a dialog box may be shown reporting that some views are not available. That means that these views cannot be queried by the active connection, and the program failed to find another opened connection, to the same database, that has these privileges. You should have ate least one Oracle account that is able to see some V$ catalog views (the exact list of views is shown in the message log). 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 the message log (the necessary GRANT commands are presented, for your convenience).
When the privileges are given, you will need to disconnect and connect again to the account which received the privileges to make them recognized by Select Fast!.
If you cannot obtain those privileges, you can still use Select Fast!, but only for editing queries and generating plans (not capturing).
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. We recommend (in absence of other security issues), to give the privileges via SELECT_CATALOG_ROLE, to make it more manageable.
Editing and managing you query versions is a simple and straightforward task in Select Fast!. You simply edit them in the query text box, and the program will keep track of your versions. That is, every time you run a command (generate plan, execute query, etc.) it will detect if the query text is a different version and save it as a new item in the browser. If the query text is equal to an existing item, this one will be selected. You can also explicitly save a new query version by choosing the "store version" command, instead of running any command. If you don't want to keep a query version, you can delete it from the browser (via the context menu).
If you want to use bind variables in your queries, you just put them in your text, for example "where x = :t". You can also use JDBC variable syntax: "where x = ?" (this feature is useful if you are tuning a query for a Java project).
Tip: learn to use the shortcut keys (F5 to generate plan, F6 to capture, F7 to parametrizations, F8 to execute and Ctrl-R to store version), it will make your work more productive.
To run a query (create an "execution") or create a "plan", you must have a parametrization. However, the parametrization behavior is designed so that you do not need to worry about them if you don't use them. That is, if your query does not use bind variables, a default parametrization will be created for you the time you first generate a plan or execute the query, and all executions and plans will be created under it. You will want other parametrizations only if you need to change some initialization parameter value or execution limits. If so, you must explicitly call the "Edit Bind Variables and Parameters" window, by clicking the "Configure" button or menu item, or pressing F7.
However, if your query does use bind variables, this is how Select Fast! will behave: when you first do something with the query, if it is an execution, it will ask for the variable values (by opening the "Edit Bind Variables and Parameters" window). If you want, you can also set parameters in this moment. But if your first operation is a plan capture or generation, Select Fast! will create a default parametrization with null values for the bind variables (this is because you don't need the values to generate a plan).
After the first parametrization is created for the query, whenever a plan, an execution or a parametrization is the selected item in the browser, any operation request will use the associated parametrization. If the selected item is the query itself, the "Edit Bind Variables and Parameters" window will show up for you to decide which parametrization to use, or create a new one. However, in the special case when the query does not use bind variables, and has only one parametrization, this one will be selected automatically even if the query is the selected item.
In the "Edit Bind Variables and Parameters" window, you just need to configure the set of values you want. The existing parametrizations are there (in the left browser) just to provide an easy way to copy values. When you click OK, the set of values you configured will be compared with every other parametrization in the query. If it is equal to one of them, this one will be selected. If not, a new one will be created. The result of this behavior is such that you can never really "edit" a parametrization, every change will always create a new one. Of course, you can always delete unused parametrizations (from the context menu at the main window's left browser).
Select Fast! supports both the original bind variable syntax (that is, an identified preceded by a colon, like :v or :b1) and the JDBC syntax (with a ? sign). In the latter case, it will automatically convert the query and variable names to the original syntax (naming them :x1, :x2, and so on) before parsing or executing them.
In a parametrization, you can also configure initialization parameter values to be set in the session. Any parameter you include here will be changed with an ALTER SESSION command just before running a query or generating a plan with this parametrization. Select Fast! will never issue an ALTER SYSTEM command or make any permanent change to the initialization parameter. Just type the parameter name and value and click "Add". The combo box shows the most commonly used parameters when tuning a query, but note that it is an editable combo box, so you can type any parameter name you want, even those hidden parameters like _unnest_subquery or _newsort_enabled.
Three parameters are already included by default by Select Fast!:
1) cursor_sharing = EXACT: although EXACT is already the Oracle default value for this initialization parameter (and is the value used in most systems), it is forced by Select Fast! in every session just in case you are running it on a database where this parameter has been changed. Changing this parameter may be a DBA option in some systems, but it can make Select Fast! unable to correctly spot cursors in V$SQL to capture execution plans. So, if you change this parameter from its default value in a parametrization, please be aware that "capture plan" may not work or report wrong plans.
2) timed_statistics = TRUE: usually, the DBA sets this parameter to FALSE in production databases, in order to avoid the overhead of requesting the time from the operating system. Select Fast! suggests you to set this parameter to TRUE in its sessions in order to have CPU usage statistics collected in query executions. Of course, the parameter will be set only in Select Fast! sessions, not affecting the overall performance of your system. If you set this parameter to FALSE, CPU statistics will be set to zero.
3) statistics_level = ALL: the Oracle default value for this parameter is TYPICAL, which does not cause collection of plan execution statistics. Since plan execution statistics are important information to the query optimization analysis process, Select Fast! suggests you to always set this parameter to ALL in its sessions.
Of course, you can change these parameters to other values if you need to, but please be aware of the limitations above.
The third thing you can configure in a parametrization are time and record count limits. They will affect only executions (not plans) and represent a way to control over long running executions. In both cases, the execution will be interrupted after the specified limit is reached. The timeout limit is mandatory (there is a default of 1800 seconds, and the default can be changed in the Preferences window).
To run the query with the parametrization you selected (or to automatically create one at run), simply press F8 or select "Execute" from one of the menus. This will create an item in the browser, initially in the 'Running' state. Due to Select Fast's multi-threaded interface, you can continue working (editing queries, generating/capturing plans, even running other queries simultaneously). When it is finished, the execution statistics will be shown, if the item is still selected (if not, you will only see the item's label in the browser has changed).
If an error occurs while executing the query, the "execution" will be marked as "[Error]" and the error message will be output in the message log.
The query results are not shown. Select Fast! is only a tuning tool, it assumes the query results are correct and you can review them in other tools you may have (there are a number of freeware tools to run a query and display the results in a tabular form) or good old SQL*Plus. In Select Fast!, you run a query only to measure execution time and other statistics.
The execution statistics are shown in the statistics viewing panel. The two first ones (client time and rows returned) are collected by Select Fast!, they are not real Oracle statistics. They mean the elapsed time for the execution (as measured from the client, that is, Select Fast!), and the total number of rows returned. It is not the scope of this manual to show how to interpret the stats, but if you have some experience with Oracle tuning you will recognize them by name (the names in V$STATNAME). For the statistics that are measured in time (cpu time, etc), the unit may be presented in seconds (s), miliseconds (ms) or microseconds, which is the default (in that case, no symbol is presented).
An execution can be interrupted before it is finished in two cases:
1) automatically by Select Fast! after reaching a specified limit (timeout or number of records)
2) manually by user, at any time, by selecting 'Interrupt' from the browser context menu.
In both cases, statistics will be collected and displayed as well, but with a note saying they are related to a partial execution.
NOTE: Sometimes a query cannot be immediately interrupted, it can be interrupted only if it's in the fetch phase (after the execute phase). Some queries (especially those with ORDER BY) may have a long execute phase. This is an intrinsic limitation among Oracle tools (even the SQL*Plus), because in the execute phase execution flow is transferred to the server and cannot be interrupted there. If timeout is reached or you select 'Interrupt' during the execute phase, the execution will be flagged to be interrupted as soon as the execute phase ends, and no rows will be returned.
There are two ways you can get the execution plan for your queries in Select Fast: "generating" it or "capturing" it. They are separate commands, each one with its menu item, button and shortcut key (F5 to generate and F6 to capture).
To generate, Select Fast! will use the traditional EXPLAIN PLAN command (if the PLAN_TABLE does not exist, a message box will appear asking you if the program can create it).
To capture, Select Fast! will try to locate a cursor in the library cache (using V$SQL) corresponding to a previous execution of the query from the same Select Fast! session. If not found, it will issue a parse call (a "prepare" command) for the statement, to force it into the cache. After that, will retrieve the execution plan directly from V$SQL_PLAN, along with the last execution statistics for the plan steps from V$SQL_PLAN_STATISTICS_ALL, if available (this view is available only in Oracle 9.2.0 and above, and statistics are generated only if initialization parameter statistics_level is set to ALL).
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.
It comprises the standard plan step information taken from V$SQL_PLAN or PLAN_TABLE (depending on the command used to generate the plan), plus the plan statistics for the last execution from V$SQL_PLAN_STATISTICS_ALL (only if it's a captured plan and the stats were available). Also, data from optimizer statistics for tables and indexes (to identify missing or outdated statistics) is placed in the steps that use the table or index, as recovered from DBA/ALL_TABLES and DBA/ALL_INDEXES.
These are the values that may be displayed in the Execution Plan Step Info box (note that not all of them are available to all kinds of steps):
bytes produced (estimated)
Number of bytes produced by the operation, as estimated by the optimizer. Recovered from V$SQL_PLAN.BYTES.CPU cost
CPU cost (in number of standardized CPU cycles) of this operation as estimated by the optimizer. Recovered from V$SQL_PLAN.CPU_COST.IO Cost
IO cost (in number of single-block reads equivalent time) of this operation as estimated by the optimizer. Recovered from V$SQL_PLAN.IO_COST.last starts
Number of times this step was executed during the last execution of the query. Recovered from V$SQL_PLAN_STATISTICS_ALL.LAST_STARTS.search columns
Number of columns used for search in an index operation (range or unique scan). Recovered from V$SQL_PLAN.SEARCH_COLUMNS.last output rows
Number of rows actually output by this step during the last execution of the query. Recovered from V$SQL_PLAN_STATISTICS_ALL.LAST_OUTPUT_ROWS.last CR (consistent) buffer gets
Number of blocks retrieved in consistent read mode, by this step, during the last execution of the query. Recovered from V$SQL_PLAN_STATISTICS_ALL.LAST_CR_BUFFER_GETS.last disk reads
Number of physical disk reads done by this step, during the last execution of the query. Recovered from V$SQL_PLAN_STATISTICS_ALL.LAST_DISK_READS.last elapsed time
Elapsed time for this step, during the last execution of the query. Recovered from V$SQL_PLAN_STATISTICS_ALL.LAST_ELAPSED_TIME.object's row count (analyzed)
Row count for the object (table or index) used in this step, as of the last optimizer statistics gathering. Recovered from DBA_TABLES or DBA_INDEXES, column NUM_ROWS.date of object's last analyze
Date of the last optimizer statistics gathering for the object used in this step. Recovered from DBA_TABLES or DBA_INDEXES, column LAST_ANALYZED.temp space (estimated)
Estimate, by the cost-based optimizer, of the amount of temporary segment space that will be needed by this step. Recovered from V$SQL_PLAN.TEMP_SPACE.estimated size for OPTIMAL execution
Estimate, by the optimizer, of the minimum size of the workarea to provide optimal execution. Recovered from V$SQL_PLAN_STATISTICS_ALL.ESTIMATED_OPTIMAL_SIZE.estimated size for ONEPASS execution
Estimate, by the optimizer, of the minimum size of the workarea to provide one-pass execution. Recovered from V$SQL_PLAN_STATISTICS_ALL.ESTIMATED_ONEPASS_SIZE.last memory used
Memory used by the workarea during the last execution of the query. Recovered from V$SQL_PLAN_STATISTICS_ALL.LAST_MEMORY_USED.last temp segment size
Temporary segment size created in the last instantiation of this workarea. Recovered from V$SQL_PLAN_STATISTICS_ALL.LAST_TEMPSEG_SIZE.last execution
Efficiency of the execution of this step, during the last execution of the query (ex. OPTIMAL, 1 PASS, 2 PASSES, etc.). Recovered from V$SQL_PLAN_STATISTICS_ALL.LAST_EXECUTION.sizing policy
Sizing policy that was in effect for this workarea during the generation of this execution plan: AUTO or MANUAL. Recovered from V$SQL_PLAN_STATISTICS_ALL.POLICY.last CU (current) buffer gets
Number of blocks retrieved in current mode, by this step, during the last execution of the query. Recovered from V$SQL_PLAN_STATISTICS_ALL.LAST_CU_BUFFER_GETS.last disk writes
Number of physical disk writes done by this step, during the last execution of the query. Recovered from V$SQL_PLAN_STATISTICS_ALL.LAST_DISK_WRITES.other tag
Value recovered from V$SQL_PLAN_STATISTICS_ALL.OTHER_TAG.partition start
Start partition of a range of accessed partitions. Recovered from V$SQL_PLAN_STATISTICS_ALL.PARTITION_START.partition stop
Stop partition of a range of accessed partitions. Recovered from V$SQL_PLAN_STATISTICS_ALL.PARTITION_STOP.distribution
Name of the method used to distribute rows from producer query servers to consumer query servers. Recovered from V$SQL_PLAN_STATISTICS_ALL.DISTRIBUTION.degree of parallelism
degree of parallelism for this step during last execution of the query. Recovered from V$SQL_PLAN_STATISTICS_ALL.LAST_DEGREE.average active time
Average time this work area is active. Recovered from V$SQL_PLAN_STATISTICS_ALL.ACTIVE_TIME.
For the values that are measured in time (cpu time, etc), the unit may be presented in seconds (s), miliseconds (ms) or microseconds, which is the default (in that case, no symbol is presented). For the values that mean amount of memory or disk space, the unit may be presented in megabytes (MB) kilobytes (KB), or bytes, in which case no symbol is presented.
It is recommended to use captured plans whenever possible (the main requirement is to have access to the V$ views used), because they are the actual execution plans used for the statements, that is, the execution plan generated when it was last parsed. Also, only captured plans show plan step execution statistics, which may be valuable information to understand what is really happening during the execution. It is recommended to capture plan right after an execution, so you'll have both sets of data (the execution statistics and the plan) closely related to each other (they will refer to the same execution).
In the Tools -> Preferences menu you can access the Preferences window. It's a simple window where you can configure some preferences for Select Fast. These preferences are stored in Windows Registry, associated with the user, they are not saved with analysis files.
The configurations are:
a) the default value for execution timeout that is placed in parametrizations when they are first created.
b) configurations to set up how to form the browser label for plans or executions. During the analysis it may be useful, to ease visual comparison, to have some information from the item shown directly in the browser.
c) security configurations about whether to save passwords in analysis files and show them openly in the Manage Connections window.
In the Tools -> Tuning Advisor menu item you can access the Tuning Advisor window. This window is a front-end to Oracle 10g Tuning Advisor functionality. This is a feature of the Oracle Database (available from 10g on) that provides advice for tuning the queries. In the window you can choose one of the queries from your analysis in the upper combo box. The text of the query will be shown in the text box below it (but cannot be edited).
The Oracle 10g Advisors are task-oriented, that means, you must first give a command to prepare a task (create it), then you give another command to execute it and finally give a third command to get its results. Select Fast! automates this process to give you easier access to the Oracle Tuning Advisor functionality. The tasks and its results are persistent, they are kept in the database until you delete (drop) them. When you close the Tuning Advisor window, Select Fast! gives you the option to delete the tasks you have created in the session, or keep them in the database.
To use the Oracle Tuning Advisor, you must have ADVISOR system privilege. Here again, you can have it with any of the accounts you have in your analysis, Select Fast! will detect it and use it. Oracle's Tuning Advisor is able to create a tuning task for a statement as if run by another user.
After selecting your query, you click "Start Tuning Task". Select Fast! will then create the task and start it. Elapsed time will be displayed. You will not be able to close the window while a task is running, but you can interrupt it (after started, the "Start Tuning Task" converts to a "Stop Tuning Task" button). When the task finishes, the Advisor Report will be retrieved and shown. It will not be saved in the Analysis File.
You can run as many tasks you want in the same "session". All of them (their names) will be recorded and you will have the option to drop them when you close the window.
In the File menu there are the usual save/open/save as.../close file operations to be done with analysis files. Only one analysis file may be opened at a time, so if you choose to open another, you will be warned to save the one you are editing, if changed.
Analysis files include all queries, parametrizations, executions and plans you created. They also include connection informations, but not passwords (you can change this behavior in Preferences). However, they do not include Preferences settings (these are system-wide settings, stored in Windows Registry) or Tuning Advisor results.
When you open a file, the connections information is loaded, but no connection is made. If you want to reconnect to all accounts at once, the Connect All command (in Connections menu, or the Ctrl-F2 shortcut) is provided for your convenience.
Version: Select Fast! works only on Oracle Database version 9.0.1 or later.
Connecting to a SYS account: Currently, Select Fast! 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.
Oracle Data Types Supported: Currently, Select Fast! will support, for bind variable definition, only the following data types: VARCHAR and CHAR, NVARCHAR and NCHAR, ROWID, NUMBER, DATE and TIMESTAMP. For TIMESTAMP, only a precision of 3 decimal places is supported (millisecond precision). This is due to limitations of the Microsoft .Net Provider for Oracle.
Trial Version Limitations: The trial version of Select Fast! has the following limitations:
1) You cannot use more than 4 database accounts in the same analysis.
2) You cannot save a file with more than 10 items in the analysis. An item is a query, parametrization, execution or plan (connections do not count as items).
3) You can run Tuning Advisor only once per session.
4) There is a splash window that is shown every time you start the program and requires you to wait 5 seconds to dismiss it.
These limitations are designed so that you are able to evaluate the product, but not to make "production" use of it. Also, the trial version is limited to 30 days. Please read the license agreement for more details. If you want to use Select Fast! without these limitations or after the 30-day trial period, we require you to buy a license. Please consider that the price is not very high, and your purchase will encourage further development of the tool. Thank you.
However, if you have a real buying intention, but think these limitations are preventing you from fully evaluate the product, contact Priodata for an agreement with a less-limited trial license.
From the program UI you can access this user manual by selecting Help -> User Manual in the main menu. The manual file should be in the initial working directory as specified in the properties of the shortcut you used to start the program. When you install using the original MSI file you downloaded, and call it from the Start menu or desktop shortcuts, everything will be in place, so you'll have problems only if you change those properties. In this case, to correct the problem just copy this file (UserMan.rtf) to the directory specified in the shortcut.
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.
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 menu item Help -> About/License remains unblocked to give you access to the License Window.
Clicking that menu item 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. All trial version limitations are now disabled and full use of the product is allowed.
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, because 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 as 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, with its limitations. If the trial period is expired, it will block.
________________________________________Copyright (c) 2006 By Priodata Software e Serviços de Informática Ltda, Brazil
All Rights Reserved.