Neoview Query Guide HP Part Number: 544801-001 Published: April 2008 Edition: HP Neoview Release 2.
© Copyright 2008 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
Table of Contents About This Document.........................................................................................................5 Supported Release Version Updates (RVUs)..........................................................................................5 Intended Audience.................................................................................................................................5 New and Changed Information in This Edition.............................................
Ordered Joins.............................................................................................................................31 ORDERED_HASH_JOIN Operator......................................................................................31 ORDERED_HASH_SEMI_JOIN Operator............................................................................32 ORDERED_HASH_ANTI_SEMI_JOIN................................................................................33 LEFT_ORDERED_HASH_JOIN Operator.....
About This Document This guide describes how to understand query execution plans and how to affect performance of an HP Neoview database. This guide is for HP users who use Neoview SQL to query a Neoview database and who have a particular interest in issues related to query performance. Supported Release Version Updates (RVUs) This manual supports HP Neoview Release 2.3.
ENTER RUN CODE ?123 CODE RECEIVED: 123.00 The user must press the Return key after typing the input. [ ] Brackets Brackets enclose optional syntax items. For example: DATETIME [start-field TO] end-field A group of items enclosed in brackets is a list from which you can choose one item or none. The items in the list can be arranged either vertically, with aligned brackets on each side of the list, or horizontally, enclosed in a pair of brackets and separated by vertical lines.
@script-file Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. For example: "{" module-name [, module-name]... "}" Item Spacing Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: DAY (datetime-expression) DAY(datetime-expression) If there is no space between two items, spaces are not permitted.
Neoview Management Dashboard Information on using the Dashboard Client, including how to install the Client, Client Guide for Database start and configure the Client Server Gateway (CSG), use the Client windows Administrators and property sheets, interpret entity screen information, and use Command and Control to manage queries from the Client. Neoview Management Dashboard Online Help Context-sensitive help topics that describe how to use the Neoview Management Dashboard Client.
Publishing History Part Number Product Version Publication Date 544579–001 Release 2.2 August 2007 HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs. Send any errors found, suggestions for improvement, or compliments to: pubs.comments@hp.com Include the document title, part number, and any comment, error found, or suggestion for improvement you have concerning this document.
1 Using Explain A query execution plan represents the specific steps and actions that the Neoview database takes to execute a query. Query plans show the exact access paths to database structures and provide the estimated number of rows resulting from each step, as well as the relative cost. Query plans are composed of a set of preexisting functions or methods called operators that implement a particular task for a query. Operators and the operations they perform are discussed in Chapter 2 (page 17).
Capturing Query Plans for Review You can manage query plans with the EXPLAIN function by capturing query execution plans that can be queried to search for plans that have changed. Cost and cardinality changes might indicate a need for more careful review to see if the plan structure is still valid. Capturing Query Plans The results of the EXPLAIN function can be represented as a table for easier viewing. To capture query plans, you must create and populate a new table with the query plans.
Capturing Query Plan Example 1. Generate the list of columns in the Explain function with the INVOKE statement: INVOKE TABLE (EXPLAIN (NULL, NULL)); -- Definition of table SUPER.SUPER.
, DESCRIPTION COLLATE DEFAULT NO DEFAULT CHAR(3000) CHARACTER SET ISO88591 COLLATE DEFAULT NO DEFAULT ) STORE BY (STATEMENT_NAME, SYSKEY ) NUMBER OF PARTITIONS 1 ; 3. Populate the table by preparing the query, and selecting and inserting it into the new table: -- PREPARE THE QUERY PREPARE q FROM SELECT * FROM a; -- INSERT THE PLAN INTO THE EXPLAIN TABLE INSERT INTO EXPLAIN_TAB SELECT 1, CURRENT_TIMESTAMP, 'R2.
SUBSTRING(STATEMENT_NAME,1,20) AS QUERY, CAST(CARDINALITY AS NUMERIC(18,4)) AS CARD, CAST(TOTAL_COST AS NUMERIC(18,4)) AS TCOST FROM EXPLAIN_TAB WHERE OPERATOR = 'ROOT' FOR READ UNCOMMITTED ACCESS ) AS T; To show a distribution of costs for the queries: SELECT SUM(CASE SUM(CASE SUM(CASE SUM(CASE SUM(CASE SUM(CASE WHEN WHEN WHEN WHEN WHEN WHEN NBR NBR NBR NBR NBR NBR < 1 THEN 1 ELSE 0 END) AS COST_L1, BETWEEN 1 AND 5 THEN 1 ELSE 0 END) AS COST_B1_5, BETWEEN 6 AND 10 THEN 1 ELSE 0 END) AS COST_B6_10, BETW
2 Query Plan Operators Use the information in this section to understand the fields (called tokens) in the DESCRIPTION column when you use the EXPLAIN statement and Neoview Performance Analyzer (NPA) to review and understand query execution plans. For information about the EXPLAIN statement, see the Neoview SQL Reference Manual.
• • • • • • • • • • • • • • • • • • • • • • • • • • • • • “MERGE_UNION Operator” (page 50) “NESTED_SEMI_JOIN Operator” (page 25) “NESTED_JOIN Operator” (page 25) “NESTED_SEMI_JOIN Operator” (page 25) “ORDER_CROSS_PRODUCT” (page 34) “ORDERED_HASH_ANTI_SEMI_JOIN” (page 33) “ORDERED_HASH_JOIN Operator” (page 31) “ORDERED_HASH_SEMI_JOIN Operator” (page 32) “PARTITION_ACCESS Operator” (page 22) “ROOT Operator” (page 20) “SEQUENCE Operator” (page 51) “SIDETREE_INSERT Operator” (page 36) “SHORTCUT_SCALAR_AGGR Ope
• • “PARTITION_ACCESS Operator” (page 22) “SPLIT_TOP Operator” (page 23) It is important to be able to identify the process in which an operation occurs. Process boundaries are identified by these operators: • • • ROOT operator. This operator and all operators under it execute inside the master executor (also called root process) until another process boundary is reached.
DAM and ESP Parallelism Partitioned parallelism uses different processes, depending on the type of operators being processed: DAM processes and ESPs. • • DAM parallelism indicates parallel execution in multiple DAM fragment instances. The instances might be accessing different tables, such as in a join or union query, or they might be accessing different partitions of one table under coordination of a SPLIT_TOP operator. DAM parallelism is characterized by no-wait communication (asynchronous access).
Token Token Description Data Type xn_autoabort_interval The transaction autoabort interval. -1 means autoabort interval as int configured for the entire system. 0 means the transaction never auto aborts. Any other value means the number of seconds after which the transaction will auto abort. Legal values are in the range 20 to 21474836 seconds. plan_version Version number associated with the plan. Value is 2020 for plans generated by the Neoview SQL R2.2compiler.
Token Token Description Data Type parent_partitioning_function Type of top partitioning and contains summary information text about the parallel plan. Usually includes one of three functions: hash-partition, round-robin partition, or broadcast. See “Repartitioning Rows .” child_partitioning_function Indicates how the input data received by the text ESP_EXCHANGE operator is organized.
The PARTITION_ACCESS operator has one child operator. The description field for this operator contains: Token Token Description Data Type fragment_id A sequential number assigned to the fragment. 0 is always master integer executor and 1 is reserved for the Explain plan. Numbers 2 to n will be ESP or DAM fragments. parent_frag The fragment_id for the parent fragment of the current fragment. integer Value is (none) for master executor.
associated with the SPLIT_TOP operator. The SPLIT_TOP operator executes in either the master executor or ESP process. It cannot execute in the DAM process. Although not considered a process boundary operator, the SPLIT_TOP operator indicates that the process needs to access more than one partition of a table through the PARTITION_ACCESS operator. The access might be parallel. Additionally, the SPLIT_TOP operator can be present in the master executor and ESP processes.
cardinalities are often shown as “1” on the SPLIT_TOP and PARTITION_ACCESS operators. For these operators, the PROBES field in the COST_DETAILS panel of NPA show the expected rows accessed, and the cardinality value for the plan step shows the expected number of rows accessed per probe. NESTED_JOIN Operator The NESTED_JOIN operator describes a portion of an execution plan that involves a nested join. This operator sends each outer row to the inner table, where it eventually goes to a scan operation.
NESTED_ANTI_SEMI_JOIN Operator The NESTED_ANTI_SEMI_JOIN operator describes a portion of an execution plan that involves a nested join. This operator returns all rows from the inner table that do not satisfy the predicates. See “NESTED_JOIN Operator” (page 25). The NESTED_ANTI_SEMI_JOIN operator has two child operators. The description field for this operator contains: Token Token Description Data Type fragment_id A sequential number assigned to the fragment.
TUPLE_FLOW Operator The TUPLE_FLOW operator describes a portion of an execution plan that typically involves an insert-select or a rowset insert, implemented as a type 1, matching partitions nested join. All rows from the outer table are selected and inserted into the inner table. This operator enables data to flow from one child to the other. The join_predicate and selection_predicates expressions are empty.
Token Token Description Data Type join_method Name of join method: merge text parallel_join_type 1 for Type1 or 2 for Type2, depending on parallel join algorithm text merge_join_predicate Expression of the join predicate expr(text) selection_predicates Expression of the WHERE clause that is not included in the merge_join_predicate or in a selection_predicates of any children expr(text) MERGE_SEMI_JOIN Operator The MERGE_SEMI_JOIN operator returns one row for the first match it finds in the in
Token Token Description Data Type merge_join_predicate Expression of the join predicate expr(text) selection_predicates Expression of the WHERE clause that is not included in the merge_join_predicate or in a selection_predicates of any children expr(text) LEFT_MERGE_JOIN Operator The LEFT_MERGE_JOIN operator describes a portion of an execution plan that involves a merge join. The LEFT_MERGE_JOIN differs from MERGE_JOIN only when it does not find a match in the inner table.
Token Token Description Data Type join_type Inner or natural join text join_method Name of join method: hash text parallel_join_type 1 for Type1 or 2 for Type2, depending on parallel join algorithm text hash_join_predicate Expression of the join predicate expr(text) selection_predicates Expression of the WHERE clause that is not included in a hash_join_predicate or one of the child scans expr(text) HYBRID_HASH_SEMI_JOIN Operator The HYBRID_HASH_SEMI_JOIN returns only one row for every oute
Token Token Description Data Type join_method Name of join method: hash text parallel_join_type 1 for Type1 or 2 for Type2, depending on parallel join algorithm text hash_join_predicate Expression of the join predicate expr(text) join_predicate Expression of the join predicate, specified in the ON clause. Used for semi and outer joins.
The ORDERED_HASH_JOIN operator has two child operators. The description field for this operator contains: Token Token Description Data Type fragment_id A sequential number assigned to the fragment. 0 is always master integer executor and 1 is reserved for the Explain plan. Numbers 2 to n will be ESP or DAM fragments. parent_frag The fragment_id for the parent fragment of the current fragment. Value is (none) for master executor.
Token Token Description Data Type join_predicate Expression of the join predicate expr(text) selection_predicates Expression of the WHERE clause that is not included in a hash_join_predicate or one of the child scans expr(text) The difference between join_predicate and hash_join_predicate tokens is that the former are the non-equijoin predicates. The latter are equijoin predicates that you use to help build and probe the hash table.
The LEFT_ORDERED_HASH_JOIN operator has two child operators. The description field for this operator contains: Token Token Description Data Type fragment_id A sequential number assigned to the fragment. 0 is always master integer executor and 1 is reserved for the Explain plan. Numbers 2 to n will be ESP or DAM fragments. parent_frag The fragment_id for the parent fragment of the current fragment. integer Value is (none) for master executor.
The INSERT operator has no child operators. The description field for this operator contains: Token Token Description Data Type fragment_id A sequential number assigned to the fragment. 0 is always master executor integer and 1 is reserved for the Explain plan. Numbers 2 to n will be ESP or DAM fragments. parent_frag The fragment_id for the parent fragment of the current fragment. Value is (none) for master executor.
Many details related to the FILE_SCAN operator also apply to the INSERT_VSBB operator. See the “FILE_SCAN Operator” (page 41). SIDETREE_INSERT Operator The SIDETREE_INSERT operator describes that part of an execution plan that inserts entire blocks of rows into a table in DAM. This operator can only be used on empty tables when the table is unaudited. The SIDETREE_INSERT operator has no child operators.
Token Token Description Data Type olt_opt_lean Indicates for short, simple operations whether a further optimization that text reduces the physical size of the plan is used. iud_type Type of update followed by table or index name expr(text) partition_name Present only if partition name is specified in the UPDATE statement. Returns the name of the partition.
Token Token Description Data Type index_end_key Expression of the end key predicates on the index expr(text) check_constraint Check constraints on the update table text Many details related to the FILE_SCAN operator also apply to the SUBSET_UPDATE operator. See the “FILE_SCAN Operator” (page 41). UNIQUE_UPDATE Operator The UNIQUE_UPDATE operator describes a portion of an execution plan that works on one row only; it updates zero or one row. The UNIQUE_UPDATE operator has no child operators.
Delete Operators CURSOR_DELETE Operator The CURSOR_DELETE operator describes a portion of an execution plan that works on one row only. The CURSOR_DELETE operation is performed by first retrieving rows from the table and then deleting each row that is required. This operation differs from SUBSET_DELETE, in which the read and delete are performed in a combined operation. The CURSOR_DELETE operator has no child operators.
This operator has no child operators. The description field for this operator contains Token Token Description Data Type fragment_id A sequential number assigned to the fragment. 0 is always master executor integer and 1 is reserved for the Explain plan. Numbers 2 to n will be ESP or DAM fragments. parent_frag The fragment_id for the parent fragment of the current fragment. Value is integer (none) for master executor.
Token Token Description Data Type object_type Value volatile is present only if the delete is to a volatile table. text partition_name Present only if partition name is specified in the DELETE statement. Returns the name of the partition.
Token Token Description Data Type access_mode The access specified: read committed, read uncommitted, stable, skip conflict, text serializable, not specified, or unknown columns_retrieved Estimate of the number of columns to be returned integer fast_scan Indication of whether an optimization for a simple scan operation is used. The value used is returned if this optimization is used. text fast_replydata_move Indication of whether an optimization for returning data from DAM is used.
Token Token Description Data Type fast_scan Indication of whether an optimization for returning data from DAM is text used. The value used is returned if this optimization is used. partition_name Present only if partition name is specified in the statement text key_columns Columns used as the primary key expr(text) executor_predicates Any predicate that is not a key predicate evaluated by the executor in DAM expr(text) part_key_predicate Predicate expression specified on the partitioning key.
Token Token Description Data Type access_mode The access specified: read committed, read uncommitted, stable, skip conflict, serializable, not specified, or unknown text columns_retrieved Estimate of the number of columns to be returned integer fast_scan Indication of whether an optimization for a simple scan operation is used. text The value used is returned if this optimization is used. fast_replydata_move Indication of whether an optimization for returning data from DAM is used.
Token Token Description Data Type olt_opt_lean Indicates whether a further optimization for short, simple operations is used. The value used is returned if this optimization is used. scan_type {full scan | subset scan | unique access} of {index index-name(table-name)| table}table-name text object_type Present only if the update is to a volatile table. Returns the text volatile in this case.
Token Token Description Data Type aggregates Expression of the aggregate function expr(text) selection_predicates Expression of the HAVING clause expr(text) grouping_columns Expression of the grouping columns expr(text) HASH_PARTIAL_GROUPBY_LEAF Operator The HASH_PARTIAL_GROUPBY_LEAF operator executes a partial group by operation as close to where the data is read as is cost effective. This strategy reduces the amount of data that must be relocated for a query.
Token Token Description Data Type aggregates Expression of the aggregate function expr(text) selection_predicates Expression of the HAVING clause expr(text) SHORTCUT_SCALAR_AGGR Operator The SHORTCUT_SCALAR_AGGR operator occurs for aggregates without a GROUP BY clause and returns one row. The description field for this operator contains: Token Token Description Data Type fragment_id A sequential number assigned to the fragment.
Token Token Description Data Type grouping_columns Expression of the grouping columns expr(text) aggregates Expression of the aggregate function expr(text) selection_predicates Expression of the HAVING clause expr(text) SORT_PARTIAL_AGGR_LEAF Operator The SORT_PARTIAL_AGGR_LEAF operator executes a partial group by operation as close to where the data is read as is cost effective. This strategy reduces the amount of data that must be redistributed for a query.
The SORT_PARTIAL_GROUPBY_LEAF operator has one child operator. The description field for this operator contains: Token Token Description Data Type fragment_id A sequential number assigned to the fragment. 0 is always master executor integer and 1 is reserved for the Explain plan. Numbers 2 to n will be ESP or DAM fragments. parent_frag The fragment_id for the parent fragment of the current fragment. Value is (none) for master executor.
TRANSPOSE Operator The TRANSPOSE operator occurs as a result of a TRANSPOSE clause. It can also occur due to an internally generated GROUPBY clause. You might see this operator if you have multiple distinct aggregates in the query. The TRANSPOSE operator has one child. The description field for this operator contains: Token Token Description Data Type fragment_id A sequential number assigned to the fragment. 0 is always master executor and 1 is reserved for the Explain plan.
Miscellaneous Operators CALL Operator The CALL operator indicates that a user-defined routine (UDR) was executed. The CALL operator has no child operators. The description field for this operator contains: Token Token Description Data Type fragment_id A sequential number assigned to the fragment. 0 is always master integer executor and 1 is reserved for the Explain plan. Numbers 2 to n will be ESP or DAM fragments. parent_frag The fragment_id for the parent fragment of the current fragment.
The SEQUENCE operator has one child operator. The description field for this operator contains: Token Token Description Data Type fragment_id A sequential number assigned to the fragment. 0 is always master integer executor and 1 is reserved for the Explain plan. Numbers 2 to n will be ESP or DAM fragments. parent_frag The fragment_id for the parent fragment of the current fragment. Value is (none) for master executor.
Operators Appearing Infrequently or Not At All COMPOUNDSTMT Operator The COMPOUNDSTMT operator describes a portion of an execution plan that involves a compound statement. It also can appear as a by-product of a triggers operation. A compound statement that has n-statements will have n-1 COMPOUNDSTMT operators. This operator enables bundling several statements together as one single atomic statement and allows data to flow from one child to the other.
The PACK operator has one child. The description field for this operator contains: Token Token Description Data Type fragment_id A sequential number assigned to the fragment. 0 is always master integer executor and 1 is reserved for the Explain plan. Numbers 2 to n will be ESP or DAM fragments. parent_frag The fragment_id for the parent fragment of the current fragment. Value is (none) for master executor.
Index C CALL operator, 51 Capturing query execution plans described, 12 example, 13 historical database, 12 COMPOUNDSTMT operator, 53 CURSOR_DELETE operator, 39 CURSOR_UPDATE operator, 36 D Delete operators CURSOR_DELETE, 39 SUBSET_DELETE, 39 Documents, related information, 7 E ESP (executor server process) cost associated with, 20 ESP_EXCHANGE operator, 21 EXPLAIN operator, 53 Explain statement capturing query plans, 12 historical explain, 12 operators, 17 F FILE_SCAN operator, 41 FILE_SCAN_UNIQUE opera
NESTED_JOIN operator, 25 NESTED_SEMI_JOIN operator, 25 O Operators CALL , 51 COMPOUNDSTMT , 53 CURSOR_DELETE, 39 CURSOR_UPDATE, 36 described, 17 ESP_EXCHANGE, 21 EXPLAIN, 53 FILE_SCAN, 41 FILE_SCAN_UNIQUE, 42 FIRSTN operator, 51 HASH_GROUPBY, 45 HASH_PARTIAL_GROUPBY_LEAF, 46 HASH_PARTIAL_GROUPBY_ROOT, 46 HYBRID_HASH_ANTI_SEMI_JOIN, 30 HYBRID_HASH_JOIN, 29 HYBRID_HASH_SEMI_JOIN, 30 INDEX_SCAN, 43 INDEX_SCAN_UNIQUE, 44 INSERT, 34 INSERT_VSBB , 35 LEFT_HYBRID_HASH_JOIN, 31 LEFT_MERGE_JOIN, 29 LEFT_NESTED_JOIN
SORT_PARTIAL_GROUPBY_LEAF operator, 48 SORT_PARTIAL_GROUPBY_ROOT operator, 49 SORT_SCALAR_AGGR operator, 49 SPLIT_TOP operator, 23, 24 SUBSET_DELETE operator, 39 SUBSET_UPDATE operator, 37 T TRANSPOSE operator, 50 TUPLE_FLOW operator, 27 TUPLELIST operator, 52 U UNIQUE_UPDATE operator, 38 UNPACK operator, 54 Update operators CURSOR_UPDATE, 36 SUBSET_UPDATE , 37 UNIQUE_UPDATE , 38 V VALUES operator, 52 57
