HP Neoview Performance Analysis Tools HP Part Number: 586321-001 Published: September 2009 Edition: HP Neoview Release 2.
© Copyright 2009 Hewlett-Packard Development Company, L.P.
Table of Contents Learn About This Document...............................................................................................7 Publishing History..................................................................................................................................7 New and Changed Information in This Edition.....................................................................................7 Intended Audience...........................................................................
Add Tools.........................................................................................................................................23 Aggregate Functions........................................................................................................................23 System Metrics......................................................................................................................................24 Processing Node Utilization...........................................
Platform Status Counters Information.......................................................................................45 Statement and Service Counter Details...........................................................................................46 Neoview WMS Services Configuration................................................................................................46 Services Details Log File.................................................................................................
Query Workbench Query Details.........................................................................................................78 Explain Plan.....................................................................................................................................78 Operator Groups........................................................................................................................79 Analyze the Explain Plan Through the Grid................................................
Learn About This Document This guide describes how to use the Neoview Performance Analysis Tools to monitor and analyze queries. Publishing History This guide supports HP Neoview Release 2.4 Service Pack 1 (SP1). The publication date and part number indicate this document’s current edition: Part Number Product Version Publication Date 586321-001 HP Neoview Release 2.4 SP1 September 2009 544814-001 HP Neoview Release 2.
Added these glossary terms: • Data source • Menu bar • Neoview platform • SQL statement Intended Audience This guide is intended for users who perform database administrator tasks and have a need to monitor and analyze query activity. Related Documentation This manual is part of the HP Neoview customer library. Neoview Customer Library The manuals in the Neoview customer library are listed here for your convenience.
Neoview Reports Online Help Help topics that describe how to use the HP Neoview Reports Tool. Neoview Repository User Guide Information about using the Repository, including descriptions of Repository views and guidelines for writing Neoview SQL queries against the views. Neoview System Monitor Quick Start Instructions for starting, using, customizing, and troubleshooting the Neoview System Monitor.
Include the document title, part number, and any comment, error found, or suggestion for improvement you have concerning this document.
1 Install and Start the HP Neoview Performance Analysis Tools What are the HP Neoview Performance Analysis Tools? The Neoview Performance Analysis Tools provide insight into queries running on the Neoview data warehousing platform. Prior to Neoview Release 2.3 SP3, this product was known as Neoview Performance Analyzer. In Neoview Release 2.3 SP3 and following releases, Neoview Performance Analyzer is one of three components of the Neoview Performance Analysis Tools (NPA Tools).
• Neoview Query Viewer — Live operational view of queries running on the Neoview platform. Standalone product. Same as Live View in Neoview Performance Analyzer. • Neoview Query Workbench — Query plan analysis and execution tool. Standalone product. Same as Query Workbench in Neoview Performance Analyzer.
Install the Neoview Performance Analysis Tools The HP Neoview Performance Analysis Tools are installed with a Windows installer. You can find the installer by searching for “HP Neoview Performance Analysis Tools” on the HP Software Depot web site. See Resources on the Web.
NOTE: You must connect to the Neoview data warehousing platform through the client driver to use the full capabilities and features of the Neoview Performance Analyzer. See Connect to the Neoview Platform. Start the Neoview Query Viewer To start the Neoview Query Viewer, double-click the Neoview Query Viewer icon on your desktop or choose Start>All Programs>HP Neoview Performance Analysis Tools>Neoview Query Viewer.
NOTE: You are not required to use the same user name for connecting to NCI and NPA, however, you should log on to the same Neoview platform. Exit Any Neoview Performance Analysis Tool To exit any of the NPA Tools, close the window. NPA Tools will confirm that you want to exit. Exiting the application securely disconnects all active sessions.
2 Use the Workspace Neoview Performance Analyzer Workspace The workspace is the heart of the Neoview Performance Analyzer and all activity occurs within this space. The workspace is a content area where you can design the layout of the performance tools and views. You can customize a view of the Neoview platform that you want to see. A new workspace can be created through the Workspace menu bar command. See Start a Workspace.
The Neoview Pane The Neoview pane allows you to define which system metrics and workload metrics you want to display in the workspace content area. Through the use of views, you can switch between different layout types and make performance decisions on the resource usage of the system and the areas and workload that need to be investigated and optimized. Use the Neoview Pane describes the features available for use.
Define the Layout for the Workspace To define the layout of your workspace, drag in the tools you want to use from the Workspace Toolbox. The Timeline Controller appears as soon as you add any tool. For more information about the tools and workspace toolbox, see The Workspace Toolbox At a Glance. Save a Workspace Layout To save a workspace layout, choose Save from the Workspace menu bar command or click the disk icon on the toolbar.
Help. For Neoview Query Viewer and Neoview Query Workbench, the commands are Workspace, Edit, Tools, Display, and Help. Task Toolbar The task toolbar provides quick access to standard features as well as NPA-specific tasks: Workspace Toolbox The workspace toolbox appears at the bottom of the application. Rename a Workspace By default, workspaces are numerically named (Default Workspace, New Workspace #2, and so on).
then be used as defaults for all subsequently created workspaces. To set up default values for individual workspaces, set the options values on an active workspace. The available options: Option Description Connection Settings Allow you to set the connection and command timeout settings. The values you enter should be numeric and represent the time in seconds. Triage Space Settings Described in Triage Space Settings, allow you to specify Triage Space settings such as the fetch limit.
However, you can run the Neoview Query Viewer and Neoview Query Workbench tools as those tools do not require access to the Neoview Repository. The Connect dialog provides a Test option that allows you to check your connection to the Neoview platform. After you select OK, NPA connects to the Neoview platform and the workload filter dimensions in the Triage Space.
3 Use the Workspace Toolbox The Workspace Toolbox At a Glance The workspace toolbox is a panel of tools that you can use to graphically map the state of the Neoview platform. These tools map various performance metrics either at the system or workload level. The workspace toolbox appears at the bottom of all NPA views, including Neoview and customized views, Live View, Triage Space, and the Query Workbench. However, you use the metrics only in the Neoview and customized views.
System Metrics The System Metrics tool is a shortcut to select the default system metric tools in a single view. Clicking on this icon will add these tools to the workspace: Processing Node Utilization, Processing Node Queue Length, Cache Hits, and System I/O Activity. The default aggregate function for Processing Node Utilization and Processing Node Queue Length is an average function. The other metrics use a total (or sum) aggregate function by default.
1. 2. 3. 4. 5. In the Triage Space, create a workload filter for the information you want to capture in the Workload Metric. The items you define in the filter determine the graphed information in the Workload Metric. For more information about filter definition, see Filter the Queries. Save the filter by selecting the Save button at the top of the Workload Filters. The filter is saved in XML file format (*.xml). In the Neoview pane, add a Workload Metric tool from the workspace toolbox.
4 Use the Neoview Pane Neoview and Customized Views The Neoview and customized views allow you to define which system and workload metrics you want to display in the workspace content area. Through the use of views, you can switch between different layout types and make performance decisions on the resource usage of the system and the areas and workload that need to be investigated and optimized. NOTE: To view graph information, you must turn on Fetch Timeline Information in the Tools>Options settings.
that is graphed varies depending to the type of tool that you drag and drop onto the workspace content area (that is, in the Neoview pane or customized view). To “rubber band” or select areas of a graph, click the left mouse button in the area of the graph you want to review and drag the box around the content. Adjust Time Settings You adjust time settings through the Tools>Options>Time Settings menu bar command.
NOTE: You cannot delete the Neoview pane. Other Tasks on Graphs Place the cursor in a graph and right-click. The right-click menu provides these tasks: Task Description Copy Copies a graph to the clipboard. Save Image As Saves the current graph based on a selected format to a file. Available formats include png, jpg, gif, tif, and bmp. Page Setup Sets up the graph's page layout for printing, allows you to select the paper size and source, page orientation, and margins.
5 Use the Live View Pane The Live View pane gives you a point-in-time display. It displays queries that are currently running on the system (or completed in the last 1 minute) with information from the Neoview Workload Management Services (WMS). In the Neoview Performance Analyzer, Live View is one of the four panes. The Live View pane in the Neoview Performance Analyzer provides the same information as the Neoview Query Viewer, which is a standalone tool for operational support.
NOTE: Neoview Query Viewer does not show the Get Session or Load Triage buttons. Configure Live View Options To configure the Live View options, use the Tools>Options menu to adjust the settings. From the Options menu, you can set the following options: This Option ... Controls Connection Timeout Displays the number of seconds to wait for establishing a connection. Command Timeout Displays the number of seconds to wait when executing any command. The default value is 600 (seconds).
Actions on Live View Queries The Live View displays its own SQL statements in the query list. Queries, SPJ calls, and other SQL statements initiated by Live View are identified by the text “Neoview Performance Analyzer” or “Neoview Query Viewer” in the APPLICATION_ID field. Query management actions against such queries are discouraged. For example, killing a query initiated by the Live View will cause Live View to lose its ODBC connection to the Neoview platform.
Threshold Rule Creation Wizard To open the Threshold Rule Creation Wizard, which provides various menu items to help with the creation or modification of thresholds, click on the New Rule or Edit Rule button in the Client Threshold Rule Manager . In order for a threshold to be completed, it must be applied to one or more workspaces and it must have a name. Notice the tabbed areas directly below the screen title: Workspaces, Conditions, and Actions.
Workspaces Tab The Workspaces tab is the first tab displayed when the Threshold Rule Creation Wizard is opened. The Workspaces tab lists the workspaces that are open in NPA and allows you to enable this threshold for specific workspaces by checking the box to the left of the workspace name. 'All Workspaces this session' means all currently open workspaces (for NPA only as this tool only allows multiple workspaces) and any future workspaces that may be opened for this client instance.
Space Fetched Queries will appear under the Conditions tab. These column headings can be used as conditions in a rule. When you select properties in the Conditions tab, the Rule description (right-pane) adds the condition. You edit the Rule description by clicking on the blue link. NOTE: Checked Conditions will not operate correctly until you configure the condition’s threshold value by clicking on the blue underlined text in the Rule description pane.
Monitoring Queries The right pane of the Live View provides a pop-in screen that allows each individual query's details to be viewed in a more user-friendly manner. To activate the query details pop-in screen, select a query, then click on the Query Details label on the right side of the screen. The Query Details tab is a toggle tab used to display or hide information.
The Query Details window provides a detailed view of the query and shows the query text and the metrics related to the query split into these categories: Category Description “Time-Based Metrics” (page 39) Indicates metrics relating to the time dimension. Depending on the version of the platform you are connected to, the set of information displayed will vary. See Time-Based Metrics.
The title corresponds to the unique query ID for the query, as well as the username and connection information, including the data source used to connect to the Neoview platform. Time-Based Metrics Time-based metrics provide information relating to the time dimension associated with the query. Depending on the version of the platform to which you are connected, the information displayed will vary.
Metric Description Query Elapsed Time The amount of time used by Neoview SQL to execute the query. Total Processor Time Total amount of processor (CPU) time utilized by the query. Display format is hh:mm:ss (hour, minute, and seconds). Last Interval Processor Time Total amount of processor (CPU) time utilized by the query in the last interval. Display format is hh:mm:ss (hour, minute, and seconds). This is the value from the previous refresh interval.
to which you are connected, the information displayed will vary. This document provides a short description for the metrics: Metric Description Estimated Cost Indicates estimated cost of the query. Cardinality Estimate Indicates estimated number of rows returned back from the query. CPU Time Estimate Indicates estimated total amount of processor (CPU) time. IO Time Estimate Indicates estimated total amount of IO time. Msg Time Estimate Indicates estimated total amount of messaging time.
Metric Description SQL Error Code Top level error code returned by the query. This is also an indicator that shows whether the query completed with warnings, errors or successfully. A positive number indicates a warning. A negative number indicates an error. Rows Accessed Number of rows accessed by the Executor in the disk process (EID). Used Rows Number of rows used. This is the number of rows returned by the Executor in the disk process (EID) after applying the predicates.
Metric Description EID Heap Used Total heap used by the Executor in the disk process. Total Memory Allocated Total memory allocated for the query. Miscellanous Facts This “catch-all” bucket contains other facts about the query. Depending on the version of the platform to which you are connected, the information displayed will vary. This document provides a short description about the metrics: Metric Description Query ID Query identifier. State State of the query.
Metric Description UsedRows Number of rows returned after applying the predicates. In a push down plan, all the used rows may not be returned. MessageCount Count of the messages. MessageBytes Count of the message bytes sent to access the tables in this statement. DiskIOs Number of disk reads for accessing the tables referenced in this statement. LockWaits Displays the number of times this statement had to wait on a conflicting lock.
On the upper right corner of the summary box is a magnifying glass icon, which allows you to drill down and display details about historical information of Neoview WMS queues. Clicking on this icon will bring up a detailed screen. See Statement and Service Counter Details. Service Counters Summary Information Service Counters provide summary information about the services configured on the Neoview platform (through Neoview WMS).
Statement and Service Counter Details To bring up a detailed window that provides current service configuration on the Neoview platform and historical details of statement counters retrieved by this Live View instance, click on the magnifying glass icon from either the Statement Counters summary (see Statement Counter Summary Information) or the Services summary (see Service Counters Summary Information).
The Service Details grid lists all the services configured in the current system. The Service Details also provides a view of the current state of all Neoview WMS services. Any service that exceeds the threshold set in the Add Service dialog is shown in red and the row containing the service is shaded. Below the grid are two buttons: Configure and Log to File. Configure enables administrative users to configure the Neoview WMS system.
Neoview WMS Configuration To make it easy to navigate to the WMS configuration user interface, a menu item and a corresponding toolbar called Configure Workload Management System is provided. This menu launches the Neoview WMS Configuration user interface: Corresponding toolbar icon: WMS Configuration Dialog This screen shows the Configure Workload Management System dialog: The left pane displays all available WMS configuration entities in a hierarchical tree structure.
• • • • • Click Apply to alter the system configuration. Refresh refreshes the screen by re-fetching the system configuration. Hold is enabled only if the system is in an ACTIVE state. Clicking on the Hold button places the system on HOLD. Release is enabled only if the system is in an HOLD state. Clicking on the Release button releases the system from hold and makes it ACTIVE. When you right click on the System node in the configuration tree, it displays these menus.
• • • • • • • service details and you can alter the service details. You can also double-click on a data grid row to navigate to the specific service. The Delete button and the delete service toolbar (X icon) are enabled if one or more rows are selected in the data grid and allow you to delete the selected services. In R2.4, only services that are in a stopped state are deleted. System-created services HP_DEFAULT_SERVICE, HPS_MANAGEABILITY, HPS_TRANSPORTER cannot be deleted.
This table describes the interface attributes: Attribute Description Service Name (max . 24 characters) Name of the service. Service names support Neoview character sets. Service Priority (URGENT, HIGH, Priority of the service. MEDIUM-HIGH, MEDIUM, LOW-MEDIUM, LOW) Override Datasource Priority Overrides the service data source priority (R2.4+ feature). Hidden for older versions. Max Processor Utilization (0-100) % Percentage of CPU utilized. Default from WMS system configuration.
Attribute Description SQL Text (True/False) Indicates if the WMS server processes should temporarily store the SQL Text. Comments (max. 256 characters) Optional, free-form text you can enter to describe the service. Click Add to add the service or Cancel to cancel the operation and exit the dialog. Alter an Existing WMS Service To alter an existing WMS service, in the Service Details summary screen, click Alter or double-click on a row in the services data grid.
Attibute Description Max Processor Utilization (0-100) % Percentage of CPU utilized. Default from WMS system configuration. Max Memory Usage (0-100) % Percentage of memory used. Default from WMS system configuration. Max Rows Fetched Maximum rows that can be returned by a query. Affects the size of the result set of queries that are workload managed. Default from WMS system configuration R2.4 feature. Hidden for R2.3 systems.
Stop a WMS Service To stop a WMS service, on the Service Summary screen, select one or more services in the data grid and click on the Stop button or the red square icon on the toolbar. System-created services HP_DEFAULT_SERVICE, HPS_MANAGEABILITY, HPS_TRANSPORTER cannot be stopped. A dialog box will popup to confirm the stop request: Choose Yes to continue with stop or No to cancel. You can also right click on a specific service in the configuration tree and select the Stop menu to stop the service.
Note this information about the Rules Summary: • When you select the Connection Rules tree node, only the connection rules are displayed in the right summary pane. • When you select the Compilation Rules tree node, only the compilation rules are displayed in the right summary pane. • When you select the Execution Rules tree node, only the execution rules are displayed in the right summary pane.
• • The Export configuration icon (save disk) exports the entire WMS configuration into a script as WMS commands which can then be executed using a command interface. See Save/Export WMS Configuration. Context menu (right click) for the Rules folder nodes. This applies to the Rules, Connection Rules, Execution Rules and Compilation Rules tree nodes. — Refresh – Refreshes the rules list in the tree and in the right summary pane. — Add Rule – Launches the Add Rule dialog.
When adding a rule, you can provide information in the following fields: Field Name Description Rule Name Name of the rule. Up to 24 characters (can be multi-bytes). Rule names support Neoview character sets.
Add a New Compilation Rule To add a compilation rule, on the Add Rule dialog, set the Rule Type radio button to Compilation. • Warn Level and Rule Action are optional attributes. You can specify as needed. If you select the EXEC action, you can specify a valid SQL statement that is or shall be executed when the rule is triggered. If NO-ACTION is specified for Rule Action, no action is taken.
When adding a rule, you can provide information in the following fields: Field Name Description Rule Name Name of the rule. Up to 24 characters (can be multi-bytes). Rule names support Neoview character sets.
case checking on the value. The Add Expr button adds the expression to the expressions list. • You can choose AND/OR logical operators for the expression. • For certain predefined properties, you need to specify an operator like the = sign and then specify a value. • If you select a % operator, you can specify a percentage value in the Percentage field. In the next screen, you can see that the expression checking for the current CPU_TIME is 200% of EST_CPU_TIME. • Add other expressions as needed.
Field Name Description Expression: Percentage Numeric Value Expression: Value EST_USED_ROWS, EST_ACCESSED_ROWS, EST_TOTAL_MEMORY, EST_CPU_TIME, Numeric values Expression 1 or more expressions from the property list Example: ADD RULE EXEC REXEC3 (AND, USED_ROWS >= EST_USED_ROWS, ELAPSED_TIME > 100, CPU_TIME 200 % EST_CPU_TIME, WARN-HIGH, CANCEL ); Alter Existing Rule To alter an existing rule, on the Rules Summary screen, select a rule in the grid and then click the Alter button or the pencil icon o
• • The Command Preview displays the WMS command that will be executed to apply rule associations to the system. Click on the Refresh button to reload the existing associations list from the system. Managing Admin Roles View Summary of All Admin Roles To view a summary of all admin roles, select the Admin Roles node in the configuration tree. The right pane displays a summary of all existing admin roles in the system.
• • • Select the Admin Role to delete in the data grid and click on the Delete button or the delete icon on the toolbar. Select the Admin Role to delete in the configuration tree and right click and select the Delete menu. The Delete menu is disabled for system-created admin roles, because they cannot be deleted. A confirmation dialog is displayed requesting you to confirm the delete operation.
• For each of the user-added admin roles, an ADD ADMIN command is generated. The system-created admin roles (SUPER.SUPER, SUPER.SERVICES, ROLE.DBA) are skipped. • The Save button opens a file save dialog and allows you to save the generated command script to a file. • The Done button closes the dialog. The exported configuration can now be propagated to another system by executing the exported script file using NCI. Character Set Support Both Rule and Service names support Neoview character sets.
6 Use the Triage Space Pane The Triage Space isolates queries you select in the Neoview view and customized views for analysis in the Query Workbench. Triage Space information comes from the Repository. The Triage Space Pane At a Glance The Triage Space pane contains several parts: Triage Space Pane Part Description Query Candlestick Graph Shows a graphical step graph view of the queries represented in the table grid.
Triage Space Pane Part Description Timeline Grid A timeline controller that shows the elapsed time for the graphs (the past 24–hour time period). The controller the same timeline displayed on the Neoview view and customized views. Triage Space Settings The Tools>Options>Triage Space Settings allow you to control the Triage Space and Live View behavior.
Query Candlestick Graph The query candlestick graph is a graphical representation of the way workloads and queries ran over a specific period of time. The list of queries in the query candlestick graph is the same that the queries in the Triage Space data grid. The candlestick graph is a visual representation of those workloads or queries. On the graph, the X-axis (horizontal) is the list of queries than ran or were running on the Neoview platform. The Y-axis (vertical) represents the start to end time.
Triage Space Button Panel The Triage Space button panel commands: Commands Description Preview SQL Shows a preview (three lines) of the SQL statement for each query. Show Hidden Shows previously hidden queries. Hide Hides queries that you highlight in the Triage Space data grid. You can hide multiple queries in the Triage Space data grid. If you hide queries in the Triage Space data grid, they are also hidden in the candlestick graph.
Dimensions: Filter Description Applications Select application IDs from the list. Click on the icon at the right side of the blank field for a list of IDs. Clients Select client IDs from the list. Click on the icon at the right side of the blank field for a list of IDs. DataSources Select data sources from the list. Click on the icon at the right side of the blank field for a list of data sources. TimePeriod Custom date and time field.
Triage Space Data Grid The Triage Space data grid is a tabular representation of the queries shown in the candlestick graph. The list of queries control how the candlestick graph is displayed. You can perform preliminary “what-if” analysis by using the Hide and Show buttons on the Triage Space button panel. The rows in the Triage Space data grid are color coded according to their state.
The Query Details provides this information: • Type: Metrics Field Description Estimated Cost Estimated cost of the query. Start Priority Query's starting priority. The priority of the NDCS connectivity server process when this query started execution. Disk Reads Number of disk reads performed by the Data Access Manager to access all the tables associated with this query.
• • 72 Field Description Rows Retrieved Number of rows retrieved. Num Rows Iud Total number of rows inserted, updated, or deleted in all tables accessed by the query. Total Executes Total number of rows executed. Segment Id Segment Id for the query. Node Id Node Id for the query. Pin Pin for the query. Entry Id Entry Id for the query. Sequence Num Sequence num for the query. Statement Order Begin/End based on query's execution state.
Field Description Total Execution Time Time it took for the Neoview platform to execute the query. Current System Time Current system time. Triage Space Statement Counters Statement counters appear at the bottom of the Triage Space pane and provide summary information about the queries that are shown in the Triage Space. These counters are indicator values of queries that are running, completed, or contained errors.
7 Use the Query Workbench Query Workbench Pane in NPA = Neoview Query Workbench Use the Query Workbench pane to analyze queries. Query Workbench is mostly the same as the standalone tool Neoview Query Workbench, except as noted in this document. The Query Workbench At a Glance The Query Workbench pane contains several frames: • • • • Query List shows a listing of queries you selected and loaded to the Query Workbench from the Triage Space.
Option Description Color Process Boundaries Enables or disables colored process boundaries for explain plans in the Query Workbench Query Details. When not checked, removes color from process boundaries. Sort Plan Grid by Levels Enables or disables the ability to sort explain plan grid rows by operator level. When not checked, sorts according to grid values. Query List The Query List frame helps you manage a set of queries. The combined items in the Query List are called a query set.
buttons below the Query List as described in Query List. Checked queries are stored internally for fast retrieval. You can saved checked queries. For example, if you check three queries in the Query List, the SQL statement for the first query will show in the SQL Text frame. You can view the SQL for each query by clicking on the query in the Query List. SQL Text The SQL Text frame contains the SQL statement for the query selected in the Query List.
Rename the Query You can rename the selected query in the Name box above the query field. Statistics The Statistics frame contains query statistics and details propagated from the query in the Triage Space. This frame is purely informational and has no interactive functionality. Statistics are shown only for queries that are loaded from the Triage Space. If a query is changed in the SQL Text frame and executed again, the statistics will be invalid.
you to in-depth information about the operators in the Neoview Query Guide. For information about the EXPLAIN statement, see the Neoview SQL Reference Manual. For information about improving query plans, see Tips for Query Plan Performance Improvements. Operator Groups Each operator is identified in the query tree by an icon that identifies the group type.
The default icon (question mark) is used for operators not represented by a group. Analyze the Explain Plan Through the Grid You can sort on the fields in the explain plan grid (middle section). Note that when you click on an operator in the query plan, the row is highlighted in the grid section. You can sort on the fields in the grid, so if you are interested in the total cost of an operator, you can sort them in ascending or descending cost.
can execute in the master executor or ESP processes, and groupbys can execute in the master executor, ESP, or ESAM processes. Certain scan operators always execute in the same process. For example, the scan operators, including FILE_SCAN and FILE_SCAN_UNIQUE, always execute in the ESAM process. The ROOT operator always executes in the master executor process. NPA plans only show the operators that represent the process boundaries and indicate the number of processes used or accessed.
Execute Output The Execute Output tab shows the executed query. When you select the Execute Output tab, you see the rows retrieved and column count information for the executed query.
8 Tips for Query Plan Performance Improvements Determine the Base Table Access Strategy Through the FILE_SCAN operator, the query plan provides the information you need to understand the type of access used to obtain data from a table. The FILE_SCAN operator always runs within the disk process. The scan_type field of the FILE_SCAN operator indicates the type of access made to the table partitions.
3. 4. 5. 6. If the table accessed is a fact table and if the scan is used for data aggregation, consider whether the use of a summary/aggregate table or materialized view could improve performance. Include additional predicate values in the query text for the leading primary key columns of the table to limit the scan, if possible. Limit the columns referenced only to those columns required for the query. Avoid the use of "select * ..." queries.
Significance of Primary Key Columns The primary key columns serve as the store-by columns for the table. In each partition, rows are stored in the order of the primary key columns. In addition, the primary key columns uniquely identify each row in the table. The presence or absence of predicates on the primary key columns in the query text can affect performance in these ways: • • • • • Predicates on all primary key columns result in a unique scan of exactly one row (see the FILE_SCAN_UNIQUE operator).
9 Keyboard Shortcuts General Shortcuts • • • • • • • • • • • • • • • • • Accept/OK => Enter or or Return Cancel/escape => Esc Close NPA=> Alt + F4 or Ctrl + q Close view/tab => Ctrl + w Connect => F5 Copy => Ctrl + c Cut => Ctrl + x Cycle through workspaces => Ctrl + Tab or Ctrl + F6 Delete => Delete Disconnect => Shift + F5 Focus to next tool => Comma or Ctrl + Comma Focus to previous tool => Period or Ctrl + period Next view/tab => Forward Arrow Paste => Ctrl + v Previous view/tab => Back Arrow Reco
• • • • • • 88 Saves As => Ctrl + s Select All => Ctrl + a Rename => Ctrl + r Load workbench => Ctrl + Alt + l Show/Fetch session => Ctrl + Alt + s Clear => Ctrl + Alt + r Keyboard Shortcuts
Glossary cardinality Estimated number of rows that will be returned by the current operator, based on table statistics, predicate values, and join criteria (if any) contained in the query. Represents the rows returned from the FILE_SCAN operator. Accurate statistics are necessary for obtaining reasonable cardinality estimates. data source A logical name that defines the information required to access data.
parallelism The ability of the Neoview database to execute large queries in parallel, using multiple CPUs simultaneously. partitioned parallelism A feature of the Neoview database that provides the ability to divide the data to be processed into partitions and work on each partition in parallel. Represented by the ESP_EXCHANGE and SPLIT_TOP operators. If your query plan does not contain either of these operators, parallel processing was not used in the plan.
Index actions on queries, 33 at a glance summary information, 44 Client Threshold Rule Manager, 33 configuration options, 32 customize, 32 defined, 31 kill selected queries, 32 monitor queries, 37 Neoview Query Viewer, 31 notification bubbles, 33 pause display, 33 platform status counters, 45 query details, 37 service counter summary, 45 statement and service counter details, 46 statement counter summary, 44 Threshold Rule Manager, 34 thresholds and alerts, 33 WMS services and rules, 37 WMS services configu
Query Workbench pane, 11 start, 13 Triage Space pane, 11 Neoview platform connect through ODBC, 21 Neoview Query Viewer Live View pane, 31 start, 14 Neoview Query Workbench Query Workbench pane, 75 start, 14 O Operators details, 81 ESP_EXCHANGE, 80 FILE_SCAN, 83 P Performance aggregate function, 83 base table access, 83 improving file_scan, 83 improving index_scan, 84 significance of hash key columns, 84 significance of primary key columns, 85 Process architecture described, 80 ESAM process, 80 ESP proces
add compilation rule, 58 add connection rule, 56 add execution rule, 59 add rule, 56 alter existing, 61 associate rules to services, 61 character set support, 64 delete admin role, 62 delete rule, 61 manage admin roles, 62 manager, 54 rules summary, 55 WMS services add new services, 50 alter, 52 delete, 53 hold, 54 manage, 49 manage services user, 49 release, 54 start, 53 stop, 54 view or alter system configuration, 48 WMS Services and Rules monitor queries, 37 WMS Services Configuration, 46 Workload metric
