1 SET_QUERY The SET QUERY statement is used to control query execution within a SQL session. 2 Environment You can use the SET QUERY statement: o In interactive SQL o Embedded in host language programs to be precompiled o As part of a procedure in an SQL module o In dynamic SQL as a statement to be dynamically executed Note that some options for the SET QUERY command may only be used in interactive SQL. 2 Format set-query = --> SET QUERY -+-> CONFIRM ------------------------------------------------------------------+--> +-> NOCONFIRM ------------------------------------------------------------------+ +-> LIMIT -+--> ROWS ----------------------+-----------------------+ | +--> TIME -----+-+------------+-+ | | +--> CPU TIME -+ +-> SECONDS -+ | | +-> MINUTES -+ | +-> NOLIMIT -+--> ROWS -----+---------------------------------------------------+ | +--> TIME -----+ | | +--> CPU TIME -+ | +-> EXECUTION LIMIT -+-> CPU -----+---> TIME ---> -+------------+--+ | +-> ELAPSED -+ +-> SECONDS -+ | | +-> MINUTES -+ | +-> EXECUTION NOLIMIT -+-> CPU -----+-> TIME -----------------------------------+ +-> ELAPSED -+ 2 Arguments 3 CONFIRM Lets you preview the cost of a query, in terms of I/O, before any rows are actually returned. For example: SQL> SELECT * FROM EMPLOYEES; Estimate of query cost: 52 I/O s, rows to deliver: 100 Do you wish to cancel this query (No)? YES %SQL-F-QUERYCAN, Query cancelled at user s request Some queries can result in Oracle Rdb performing a large number of I/O operations, retrieving a large number of rows, or both. The SET QUERY CONFIRM statement causes SQL to display estimated query costs. If the cost appears excessive, you can cancel the query by answering No; to continue, answer Yes. The SET QUERY CONFIRM statement is only available for interactive SQL. 3 EXECUTION_LIMIT This option imposes elapsed and CPU time limits on executing queries. This command affects all subsequent queries executed within the Rdb server process. You must be attached to a database to execute this statement. This statement affects all attaches for the current process, not just the current connection. o CPU TIME time_value [ SECONDS | MINUTES ] o ELAPSED TIME time_value [ SECONDS | MINUTES ] You can restrict the amount of elapsed time or CPU time used to execute a query. If the query is not complete before the elapsed or CPU time limit is reached, an error message is returned. The default is unlimited time for the query execution. If you omit the SECONDS and MINUTES keyword then SECONDS is the default. Dynamic SQL options are inherited from the compilation qualifier for the module. NOTE Specifying a query time limit can cause application failure in certain circumstances. For instance, an application that runs successfully during off-peak hours may fail when run during peak hours due to the load on the database. Use a positive integer for the number of seconds or minutes; negative integers are invalid and zero means no limits. If an established limit is exceeded, the query is canceled and an error message is displayed. When you set a CPU time limit, elapsed time limit and a row limit (using SET QUERY LIMIT), whichever value is reached first stops the query. Database administraors and application developers can use this feature to prevent users from overloading the system by executing long running, and probably unproductive queries. The database administrator can manage system performance and reduce unnecessary resource usage by setting option limits. 3 EXECUTION_NOLIMIT This option removes a limit imposed by the SET QUERY EXECUTION LIMIT command. Use one of the following options. o ELAPSED TIME o CPU TIME EXECUTION NOLIMIT is equivalent to assigning a limit of zero to any of the options using SET QUERY EXECUTION LIMIT. 3 LIMIT Sets limits to restrict the output generated by a query. The mechanism used to set these limits is called the query governor. The following gives you three ways to set limits using the query governor: o ROWS rows_value You can restrict output by limiting the number of rows a query can return. The optimizer counts each row returned by the query and stops execution when the row limit is reached. The default is an unlimited number of row fetches. Dynamic SQL defaults are inherited from the compilation qualifier for the module. o TIME time_value [ SECONDS | MINUTES ] You can restrict the amount of time used to optimize a query for execution. If the query is not optimized and prepared for execution before the total elapsed time limit is reached, an error message is returned. The default is unlimited time for the query compilation. If you omit the SECONDS and MINUTES keyword then SECONDS is the default. NOTE Specifying a query time limit can cause application failure in certain circumstances. For instance, an application that runs successfully during off-peak hours may fail when run during peak hours due to the load on the database. o CPU TIME time_value [ SECONDS | MINUTES ] You can restrict the amount of CPU time used to optimize a query for execution. If the query is not optimized and prepared for execution before the CPU time limit is reached, an error message is returned. The default is unlimited CPU time for the query compilation. If you omit SECONDS and MINUTES keyword then SECONDS is the default. Dynamic SQL options are inherited from the compilation qualifier for the module. Use a positive integer for the number of rows and the number of seconds; negative integers are invalid and zero means no limits. If an established limit is exceeded, the query is canceled and an error message is displayed. When you set both a time limit and the row limit, whichever value is reached first stops the output. Application developers can use this feature to prevent users from overloading the system. The database administrator can manage system performance and reduce unnecessary resource usage by setting option limits. 3 NOCONFIRM Disables the query confirm dialog that was previously enabled using SET QUERY CONFIRM. The SET QUERY NOCONFIRM statement is only available for interactive SQL. 3 NOLIMIT This option removes a limit imposed by the SET QUERY LIMIT command. Use one of the following options. o ROWS o TIME o CPU TIME NOLIMIT is equivalent to assigning a limit of zero to any of the options using SET QUERY LIMIT. 3 rows_value This argument represents the number of rows specified for the SET QUERY argument. It can be a numeric literal, a parameter name (for interactive SQL), or a parameter-marker (for dynamic SQL). 3 time_value This argument represents the number of seconds or minutes specified for the SET QUERY statement. It can be a numeric literal, a parameter name (for interactive SQL), or a parameter- market (for dynamic SQL). 2 Examples Example 1: Shows the syntax for establishing a row limit within an interactive SQL session. SQL> set query limit rows 10000; SQL> show query limit; Query limit Time is OFF Query limit Row count is 10000 rows Query limit CPU time is OFF Execution limit CPU time is OFF Execution limit Elapsed time is OFF Execution limit Row count is OFF SQL> set query nolimit rows; SQL> show query limit; Query limit Time is OFF Query limit Row count is OFF Query limit CPU time is OFF Execution limit CPU time is OFF Execution limit Elapsed time is OFF Execution limit Row count is OFF Example 2: Uses SET QUERY to establish a two second elapsed time limit for a query, and shows the error message that is displayed. SQL> set query execution limit elapsed time 2 seconds; SQL> delete from EMPLOYEES; %RDB-E-EXQUOTA, Oracle Rdb runtime quota exceeded -RDMS-E-MAXTIMLIM, query governor maximum timeout has been reached SQL> set query execution nolimit elapsed time;