Neoview Control Query Default (CQD) Reference Guide HP Part Number: 633849-001 Published: September 2010 Edition: HP Neoview Release 2.
© Copyright 2010 Hewlett-Packard Development Company, L.P. 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.........................................................................................................7 Supported Release Version Updates (RVUs)..........................................................................................7 Intended Audience.................................................................................................................................7 Document Organization...................................................................
7 Managing Histograms.................................................................................................31 CACHE_HISTOGRAMS_REFRESH_INTERVAL................................................................................31 HIST_AUTO_GENERATION_OF_SAMPLE........................................................................................31 HIST_MISSING_STATS_WARNING_LEVEL......................................................................................32 HIST_NO_STATS_REFRESH_INTERVAL...
AUTO_QUERY_RETRY_WARNINGS.................................................................................................61 SAVE_DROPPED_TABLE_DDL...........................................................................................................62 Index.................................................................................................................................
About This Document This guide describes Neoview Control Query Defaults (CQDs) that are used to override system-level default settings. Supported Release Version Updates (RVUs) This guide supports N02.05 and all subsequent N-series RVUs until otherwise indicated in a replacement publication. N02.05 is the Neoview RVU provided with Release 2.5.
Italic Letters Italic letters, regardless of font, indicate variable items that you supply. Items not enclosed in brackets are required. For example: file-name Computer Type Computer type letters within text indicate case-sensitive keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: myfile.sh Bold Text Bold text in an example indicates user input typed at the terminal. For example: ENTER RUN CODE ?123 CODE RECEIVED: 123.
DAY (datetime-expression) @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.
1 Introduction This section provides information on these topics: • “Overview of CQDs” (page 11) • “Setting CQDs” (page 12) Overview of CQDs This guide describes CQDs that are used to override system-level default settings.
Setting CQDs Different methods are available to set a CQD depending on whether you want to test or implement a CQD. Testing a CQD To test a CQD in order to evaluate the effect of a CQD on a query, use one of these methods: • Use Neoview Command Interface (NCI) to enter the CQD prior to executing the query or displaying the plan of the query.
2 Managing Adaptive Segmentation This section describes these CQDs that are used for managing adaptive segmentation: • “DEFAULT_DEGREE_OF_PARALLELISM” (page 13) • “PARALLEL_NUM_ESPS” (page 13) DEFAULT_DEGREE_OF_PARALLELISM Category Influencing Query Plans Description Defines the minimum size of the adaptive segment; the number of processors available for query operator parallelism.
Impact Lowering the value of this CQD can increase the throughput of high concurrency small and medium query workloads, but has the potential disadvantage of increasing the elapsed time of some of the long-running queries. Level Service Conflicts/Synergies Not applicable Addressing the real problem Not applicable Introduced in release Neoview Release 1.
3 Managing Skew This section describes these CQDs that are used for managing skew: • “SKEW_EXPLAIN” (page 15) • “SKEW_SENSITIVITY_THRESHOLD” (page 15) SKEW_EXPLAIN Category Influencing Query Plans Description Turns on the reporting of anti-skew join plan details in EXPLAIN or EXPLAIN options ‘f. Values ’OFF’ Disables the use of SKEW_EXPLAIN. ‘ON’ Enables skew information in EXPLAIN. The default value is ‘OFF’.
Conflicts/Synergies This CQD is only relevant if the SKEW_ROWCOUNT_THRESHOLD has been met. The SKEW_ROWCOUNT_THRESHOLD CQD controls the row count of the table at which the optimizer looks for a skew. To change that setting contact your HP representative. Addressing the real problem Skew is quite common in a real BI application, and is effectively addressed by skew buster. However, there may be design opportunities that could help address the problem as well. Introduced in release Neoview Release 2.
4 Managing Automated STATS Generation This section describes these CQDs that are used for managing automated STATS generation: • “USTAT_AUTO_CV_SAMPLE_SLOPE” (page 17) • “USTAT_AUTO_PRIORITY” (page 17) • “USTAT_MAX_READ_AGE_IN_MIN” (page 18) • “USTAT_MIN_ROWCOUNT_FOR_SAMPLE” (page 19) • “USTAT_NECESSARY_SAMPLE_MAX” (page 19) • “USTAT_OBSOLETE_PERCENT_ROWCOUNT” (page 20) USTAT_AUTO_CV_SAMPLE_SLOPE Category Update Statistics and Reorg Description For update statistics automation, or when using the NECESSA
Values 0 through max unsigned For any value greater than 199 a priority of 199 is used. integer The default value is 150. Usage Setting this CQD lower than 150 means that automated update statistics during the maintenance window is less likely to interfere with other work on the system, but may not get as much done during the window. Setting this CQD to a value higher than 150 may slow down other work during the maintenance window, but allow more tables to have histograms generated.
Introduced in release Neoview Release 2.3 Deprecated in release Not applicable USTAT_MIN_ROWCOUNT_FOR_SAMPLE Category Update Statistics and Reorg Description Sets the minimum rows that need to be in a table before sampling is used to update statistics for that table. If a table has a fewer rows than the value of this CQD, the SAMPLE option is silently ignored when performing update statistics. Values 1 through max unsigned integer The default value is 10000.
Impact Setting this CQD to a smaller value means that when NECESSARY keyword or automation is used, the maximum sample size is smaller. Update statistics may run faster, but may also create less accurate histograms. Setting this CQD to a larger value results in larger samples, with update statistics running longer, while providing more accurate histograms. Level System Conflicts/Synergies Not applicable Addressing the real problem Not applicable Introduced in release Neoview Release 2.
5 Optimizer This section describes these CQDs that are used by the Optimizer: • “HASH_JOINS” (page 21) • “JOIN_ORDER_BY_USER” (page 21) • “MDAM_SCAN_METHOD” (page 22) • “MERGE_JOINS” (page 22) • “NESTED_JOINS” (page 23) • “OPTIMIZATION_LEVEL” (page 23) • “RISK_PREMIUM_NJ” (page 24) • “RISK_PREMIUM_SERIAL” (page 25) • “ROBUST_QUERY_OPTIMIZATION” (page 26) • “SUBQUERY_UNNESTING” (page 28) HASH_JOINS Category Update Statistics and Reorg Description Determines if Hash Join is considered by the optimizer to
Production usage This setting is to be used only for forcing a desired join order that was not generated by default by the optimizer. It can be used as a workaround for query plans with inefficient join order. Impact Because you are in effect forcing the optimizer to use a plan that joins the table in the order specified in the FROM clause, the plan generated may not be the optimal one.
Production usage Merge Join is an efficient join implementation strategy if the physical schema was designed to take advantage of it. For example, large tables are physically ordered based on the most frequently joined column(s). Impact Turning Merge Join OFF may result in the optimizer not considering potentially efficient query plans, for queries with large joins on tables that are physically ordered by the join column(s).
Values ‘0’, ‘2’, ‘3’, ‘5’ The default value is ‘3’. Usage Reduce the optimization level when compile time is longer than desired and queries have relatively small execution cost and are simple in structure. Production usage Use this CQD only as a workaround for queries with unacceptable compile time or plan quality. Impact Lowering the optimization level below the system default may result in inefficient query execution plans.
Introduced in release Neoview Release 2.3 Deprecated in release Not applicable RISK_PREMIUM_SERIAL Category Influencing Query Plans Description Influences the optimizer to choose a parallel plan over a serial plan, by making a serial plan relatively more expensive. Values Any positive fractional value The default value is ‘1.2’. Usage Review ROBUST_QUERY_OPTIMIZATION first before considering the use of this CQD.
ROBUST_QUERY_OPTIMIZATION Category Influencing Query Plans Description Provides a simpler way to influence the optimizer's choice of query plans. The optimizer chooses query plans based on cardinality estimates (the number of result rows estimated at each step of a query execution plan). Actual cardinalities encountered at query execution often differ from estimates.
Values ‘MIN’ No risk premium ‘HIGH’ and ‘MAXIMUM’ Higher levels of risk premium ‘SYSTEM’ Safe risk premium The default value is ‘SYSTEM’. Usage MAXIMUM tells the optimizer to make the safest choice of query plans. This means: • RISK_PREMIUM_NJ is set to 5.0 – nested join must be 400% cheaper before it can win over competing (hash) join plans • RISK_PREMIUM_SERIAL is set to 2.
Conflicts/Synergies This conflicts with the RISK_PREMIUM_NJ, RISK_PREMIUM_SERIAL, and PARTITIONING_SCHEME_SHARING settings. Use this CQD when possible. Use the risk premium settings rarely, when specific premiums need to be set differently to address specific issues. If overall this CQD is working well but you have outliers, such as poor nested join plans or inappropriate serial plans, you could use the individual CQDs at a finer granularity, such as at a query level, to get better plans.
6 File Size and Placement (POS) This section describes this CQD: “POS_ABSOLUTE_MAX_TABLE_SIZE” (page 29). POS_ABSOLUTE_MAX_TABLE_SIZE Category File size and placement Description Controls the maximum size to which a table can grow. Values Positive numeric value Unit is megabytes. The default value is . Usage For permanent tables created by DBAs the size and growth of tables should be well known.
7 Managing Histograms This section describes these CQDs that are used to manage histograms: • “CACHE_HISTOGRAMS_REFRESH_INTERVAL” (page 31) • “HIST_AUTO_GENERATION_OF_SAMPLE” (page 31) • “HIST_MISSING_STATS_WARNING_LEVEL” (page 32) • “HIST_NO_STATS_REFRESH_INTERVAL” (page 33) • “HIST_PREFETCH” (page 34) • “HIST_ROWCOUNT_REQUIRING_STATS” (page 34) • “HIST_USE_SAMPLE_FOR_CARDINALITY_ESTIMATION” (page 35) CACHE_HISTOGRAMS_REFRESH_INTERVAL Category Histograms Description Defines the time interval after whic
Values ‘ON’ If the sample table is missing, the compiler creates the sample table before using it to estimate cardinalities. ‘OFF’ The sample table is used to estimate cardinalities if it exists, else the optimizer continues to use histograms. The default value is ‘OFF’. Usage The feature can be used to control the usage of Compile Time Statistics (CTS).
Values ‘0’ Display no warnings. Update Statistics Automation Server still continues to record missing column statistics. ‘1’ Display only missing single column statistics warnings. These include 6008 and 6011. ‘2’ Display all missing single and multi-column statistics warnings for scans only. ‘3’ Display all missing single and multi-column statistics warnings for scans and join operators only. Update Statistics Automation Server continues to record missing column statistics.
Values Integer Unit is seconds. The default value is ‘3600’ (1 hour). Usage Histogram statistics are “fake” when update statistics is not being run, but instead the customer is updating the histogram tables directly with statistics to guide the optimizer.
Values Integer The default value is ‘50000’. Usage Use this CQD to reduce the number of statistics warnings. Production usage Not applicable Impact Missing statistics warnings are not displayed for smaller tables, which in most cases don't impact plan quality much. However, there may be some exceptions where missing statistics on small tables could result in less than optimal plans. Level System Conflicts/Synergies Use this CQD with HIST_MISSING_STATS_WARNING_LEVEL.
8 Sequence Generator This section describes these CQDs that are used by the sequence generator: • “SEQUENCE_GENERATOR_CACHE” (page 37) • “SEQUENCE_GENERATOR_CACHE_INCREMENT” (page 38) • “SEQUENCE_GENERATOR_CACHE_INITIAL” (page 39) • “SEQUENCE_GENERATOR_CACHE_MAXIMUM” (page 40) SEQUENCE_GENERATOR_CACHE Category Runtime controls Description Specifies the number of values to fetch into the sequence generator cache from the sequence generator object, when values are needed to insert rows into a table with a
Addressing the real problem Not applicable Introduced in release Neoview Release 2.4 Deprecated in release Not applicable SEQUENCE_GENERATOR_CACHE_INCREMENT Category Runtime controls Description Controls the incremental setting of the internal sequence generator cache size per request. The cache size is used to set how many unique values are obtained from the sequence generator per request.
SEQUENCE_GENERATOR_CACHE_INITIAL Category Runtime controls Description Controls the initial setting of the internal sequence generator cache size per request. The cache size is used to set how many unique values are obtained from the sequence generator per request. The computation starts with the SEQUENCE_GENERATOR_CACHE_INITIAL, then multiplies it with the SEQUENCE_GENERATOR_CACHE_INCREMENT and compares it to the SEQUENCE_GENERATOR_CACHE_MAXIMUM.
SEQUENCE_GENERATOR_CACHE_MAXIMUM Category Runtime controls Description Controls the maximum setting of the internal sequence generator cache size per request. The cache is used to set how many unique values are obtained from the sequence generator per request. The computation starts with the SEQUENCE_GENERATOR_CACHE_INITIAL, then multiplies it with the SEQUENCE_GENERATOR_CACHE_INCREMENT and compares it to the SEQUENCE_GENERATOR_CACHE_MAXIMUM.
9 Managing Catalogs and Schemas This section describes these CQDs that are used for managing catalogs and schemas: • “SCHEMA” (page 41) • “PUBLISHING_ROLES” (page 41) SCHEMA Category Schema controls Description Sets the default schema for the session. Values SQL identifier The default is the Default ROLE of the user. Usage A SET SCHEMA statement, or a control query default SCHEMA statement, can be used to override the default schema name.
Introduced in release Neoview Release 2.
10 Managing Materialized Views This section describes these CQDs that are used for managing materialized views: • “MV_ALLOW_SELECT_SYSTEM_ADDED_COLUMNS” (page 43) • “MVGROUP_AUTOMATIC_CREATION” (page 43) MV_ALLOW_SELECT_SYSTEM_ADDED_COLUMNS Category Materialized view controls Description When an incremental Materialized View (MV) is created, system columns are added to support the maintenance of the MV. When this CQD is ON, system added columns can be viewed when executing a “select *” from an MV.
Values ‘ON’ Materialized View groups are automatically created and MVs added or dropped from these groups. ‘OFF’ User controls the creation of Materialized View groups and the adding and dropping of MVs from these groups. The default value is ‘ON’. Usage The purpose of automatically creating Materialized View groups and adding and dropping MVs from these groups is to make Materialized View maintenance as transparent as possible.
11 Managing Isolation Level This section describes these CQDs that are used for managing the isolation level: • “ISOLATION_LEVEL” (page 45) • “ISOLATION_LEVEL_FOR_UPDATES” (page 46) ISOLATION_LEVEL Category Transaction control and locking Description Specifies the default transaction isolation level that queries use. Values ‘READ UNCOMMITTED’ ‘READ COMMITTED’ ‘REPEATABLE READ’ ‘SERIALIZABLE’ The default value is ‘READ COMMITTED’ (ANSI).
Introduced in release Neoview Release 1.0 Deprecated in release Not applicable ISOLATION_LEVEL_FOR_UPDATES Category Transaction control and locking Description Specifies the default transaction isolation level for these update operations: INSERT UPDATE DELETE Values ‘READ UNCOMMITTED’ ‘READ COMMITTED’ ‘REPEATABLE READ’ ‘SERIALIZABLE’ The default value is ‘READ COMMITTED’ (ANSI). Usage Set this CQD to READ UNCOMMITTED to prevent users from performing any updates.
12 Executor This section describes this CQD: “EXE_MEMORY_LIMIT_PER_CPU” (page 47). EXE_MEMORY_LIMIT_PER_CPU Category Runtime controls Description Specifies the amount of virtual memory (per CPU) that a query can obtain at execution time. The total amount of virtual memory of a query consists of those allocated for big memory operators BMOs (HashJoin, HashGroupBy, Sort, and MergeJoin), as well as those used by non-BMOs (ProbeCache, PA, Exchange, and PhysicalSequence).
Introduced in release Neoview Release 2.4.
13 Transaction Control and Locking This section describes these CQDs that are used for transaction control and locking: • “INSERT_VSBB” (page 49) • “MULTI_COMMIT_SIZE” (page 50) • “NOT_ATOMIC_FAILURE_LIMIT” (page 51) • “OPI_ERROR73_RETRIES” (page 51) • “TABLELOCK” (page 52) • “TIMEOUT” (page 53) • “BLOCK_TO_PREVENT_HALLOWEEN” (page 54) • “UPD_ORDERED” (page 54) INSERT_VSBB Category Transaction control and locking Description Provides a method of inserting rows into a table whereby rows are buffered into
Addressing the real problem Use Neoview Transporter for loading. Avoid the scenarios outlined that cause these problems. If there are multiple inserters try and pre-partition the data and merge the streams first, as Neoview Transporter does. Also, if a single rowset inserter is inserting into all partitions, pre-partitioning the data and using separate inserters is ideal. Introduced in release Neoview Release 1.0 Deprecated in release Behavior change for rowset inserters scenario expected in R2.5.
NOT_ATOMIC_FAILURE_LIMIT Category Transaction control and locking Description Specifies the maximum number of nonfatal errors that are allowed during a single non atomic rowset insert statement. If the number of non fatal errors exceeds this value then the execution of insert statement terminates in an error and the transaction is rolled back. Values ‘30’ through ‘32000’ The default value is ‘32000’. Prior to R2.
TABLELOCK Category Transaction control and locking Description Specifies whether to use table locks for subsequent DML access to tables. Values ‘SYSTEM’ To indicate whether the system determines when table locks are to be used for accessing the table. ‘ON’ Table locks are always used. ‘OFF’ Table locks are not used. The default value is ‘SYSTEM’. Usage See the Neoview SQL Reference Manual, Introduction chapter, sections on Database Integrity and Locking and Transaction Management.
TIMEOUT Category Transaction control and locking Description Specifies the time in hundredths of seconds allowed to complete access requests from DML operations for rows locked by other transactions. If the time elapses before the system can grant a request to access or lock the data, the DML statement fails, and an error is returned. Values -1 through 2147483519 The time in hundredths of seconds to wait for a lock before returning an error. ‘-1’ Directs the system not to time out.
BLOCK_TO_PREVENT_HALLOWEEN Category Runtime controls Description A self-referencing insert is one which inserts into a target table and also scans from the same target table as part of the query that produces rows to be inserted. Inconsistent results are produced by the insert statement if the statement scans rows which have been inserted by the same statement. This is sometimes called the “Halloween problem.
Values ‘ON’ The optimizer generates and considers plans where the rows are inserted, updated, or deleted in clustering key order. ‘OFF’ The optimizer does not generate plans where the rows must be inserted, updated, or deleted in clustering key order. The default value is ‘ON’. Usage Inserting, updating or deleting rows in the clustering key order is most efficient and highly recommended.
14 Compiler Cache This section describes this CQD: “QUERY_CACHE” (page 57). QUERY_CACHE Category Transaction control and locking Description Attempts to reduce compilation times by storing and reusing previously compiled query plans. It maximizes the chances of plan reuse by parameterizing literals in equality predicates. Two equality predicates, "col = val1" and "col = val2", are considered to match if their selectivities match.
Conflicts/Synergies You should be aware that the cache allocated is divided into text caching and template caching. Text caching gets approximately 25% of the cache memory. Query plan caching occurs prior to parsing (text-based caching) and after parsing (template-based caching). The compiler caches same-text queries as text cache hits. Same-text queries are queries whose SQL texts are identical in everything, including case and white space.
15 Miscellaneous This section describes these miscellaneous CQDs: • “DEF_MAX_HISTORY_ROWS” (page 59) • “LAST0_MODE” (page 59) • “QUERY_LIMIT_SQL_PROCESS_CPU” (page 60) • “STREAM_TIMEOUT” (page 61) • “AUTO_QUERY_RETRY_WARNINGS” (page 61) • “SAVE_DROPPED_TABLE_DDL” (page 62) DEF_MAX_HISTORY_ROWS Category Runtime controls Description Controls the maximum number of rows maintained in the history buffer used for OLAP Window functions and Sequence functions.
Values ‘ON’ ‘OFF’ The default value is ‘OFF’. Usage This setting provides a realistic measure of the query's performance, minus the cost/time of returning the rows to the client. It is especially useful for testing the plans and performance of queries that return large result sets. Production usage Only use this CQD to assess the performance of a query. Impact The query runs completely but no rows are returned.
STREAM_TIMEOUT Category Runtime controls Description Enables unblocking using a timeout. When a SQL table is read with stream access, it never returns the end-of-data condition, but rather blocks as it waits for more rows to be inserted or updated. Applications, however, may need for the stream to temporarily unblock and return control so that the application can commit its transaction, or do other work.
Introduced in release Neoview Release 2.4 Deprecated in release Not applicable SAVE_DROPPED_TABLE_DDL Category Operational controls Description Saves DDL text, with the actual partition file names and other details, to be able to recreate dropped tables in order to recover them at a later time. It only applies when dropping tables.
Index A P AUTO_QUERY_RETRY_WARNINGS, 61 PARALLEL_NUM_ESPS, 13 POS_ABSOLUTE_MAX_TABLE_SIZE, 29 PUBLISHING_ROLES, 41 B BLOCK_TO_PREVENT_HALLOWEEN, 54 C CACHE_HISTOGRAMS_REFRESH_INTERVAL, 31 CQDs overview, 11 setting, 12 Q QUERY_CACHE, 57 QUERY_LIMIT_SQL_PROCESS_CPU, 60 R D RISK_PREMIUM_NJ, 24 RISK_PREMIUM_SERIAL, 25 ROBUST_QUERY_OPTIMIZATION, 26 DEF_MAX_HISTORY_ROWS, 59 DEFAULT_DEGREE_OF_PARALLELISM, 13 S HASH_JOINS, 21 HIST_AUTO_GENERATION_OF_SAMPLE, 31 HIST_MISSING_STATS_WARNING_LEVEL, 32 HIST_N
