Hp Neoview Release 2.5 Software Manuel d'utilisateur

HP Neoview Performance Analysis ToolsUser GuideHP Part Number: 613731-001Published: July 2010Edition: HP Neoview Release 2.5

10

Execute OutputThe Execute Output tab shows the executed query. When you select the Execute Output tab,you see the rows retrieved and column count info

9 Tips for Query Plan Performance ImprovementsNOTE: Refer to the course Neoview Performance Analyzer and Query Plan Workshop, course ID01105317, for a

1. Ensure that tables have been reorganized after loads/inserts of large amounts of data.Reorganizing tables keeps data blocks full with rows and phys

NOTE: The columns used for hash keys appear in the PARTITION_ACCESS operator. TheFILE_SCAN operator does not show the hash keys for the table.The pres

104

10 Keyboard ShortcutsGeneral Shortcuts• Accept/OK => Enter or <CR> or Return• Cancel/escape => Esc• Close NPA=> Alt + F4 or Ctrl + q• C

• Saves As => Ctrl + s• Select All => Ctrl + a• Rename => Ctrl + r• Load workbench => Ctrl + Alt + l• Show/Fetch session => Ctrl + Alt

Glossaryadministrator A user who belongs to a role with administrative privileges. By default, these roles areadministrators:• super.super• super.serv

MDAM MultiDimensional Access Method. An optimized scan method that provides improvedperformance for queries that contain missing predicates on key col

the users who are granted a role are granted the privileges associated with the role. The Neoviewplatform can have a maximum of 255 roles. See also da

1 Install and Start the HP Neoview Performance AnalysisToolsWhat are the HP Neoview Performance Analysis Tools?The Neoview Performance Analysis Tools

110

IndexAAdmin roleadd, 75delete, 75Aggregate functions, 27for tools, 27performance, 101Aggregated statisticsexpected execution time interval, 67frequenc

exit, 14install, 13Neoview Performance Analyzer, 11Neoview Query Viewer, 12, 35Neoview Query Workbench, 12, 89Neoview Performance AnalyzerLive View pa

WWMSworkload aggregation statistics, 51WMS configuration, save or export, 75WMS Service Rulesadd admin role, 75add compilation rule, 70add connection

• Neoview Query Viewer — Live operational view of queries running on the Neoviewplatform. Standalone product. Same as Live View in Neoview Performance

Similarly, the Query Workbench pane in NPA is mostly the same as the standalone tool NeoviewQuery Workbench. Functionality for both tools is described

NOTE: You must connect to the Neoview data warehousing platform through the client driverto use the full capabilities and features of the Neoview Quer

2 Use the WorkspaceNeoview Performance Analyzer WorkspaceThe workspace is the heart of the Neoview Performance Analyzer and all activity occurs within

The Neoview PaneThe Neoview pane allows you to define which system metrics and workload metrics you wantto display in the workspace content area. Thro

Define the Layout for the WorkspaceTo define the layout of your workspace, drag the tools you want to use from the WorkspaceToolbox. The Timeline Cont

The shortcut menu provides options for properties of the view. To access the shortcut menu andchange these properties for the workspace, right click w

Windows, and Help. For Neoview Query Viewer and Neoview Query Workbench, the menubar commands are Workspace, Edit, Tools, Display, and Help.For More I

© Copyright 2010 Hewlett-Packard Development Company, L.P.Confidential computer software. Valid license from HP required for possession, use or copyin

Use the Tools Menu OptionsThe Tools menu options are:DescriptionMenu OptionSets global or workspace specific options.OptionsReserved for internal use.

Task ToolbarThe task toolbar provides quick access to standard features as well as NPA-specific tasks:Workspace ToolboxThe workspace toolbox appears a

DescriptionOptionDescribed in “Adjust Time Settings” (page 32), allow you to changetime-related values for NPA.Time SettingsDescribed in “Query Workbe

This table shows reference information for the Connect dialog box:DescriptionExampleFieldSpecifies the name of the data source.If no data source is sp

DescriptionExampleFieldNeoview role. Note theseconsiderations:• If you are a platform user, leave thisfield blank.• If you are a database user and you

Disconnect/Reconnect to the Neoview PlatformTo go offline from the Neoview platform, select the Disconnect icon (red “X”). To reconnect tothe Neoview

26

3 Use the Workspace ToolboxThe Workspace Toolbox At a GlanceThe workspace toolbox is a panel of tools that you can use to graphically map the state of

System Metrics ToolThe System Metrics tool is a shortcut to select the default system metric tools in a single view.When selected, these tools are add

the Workload Metric. For more information about filter definition, see “Filter the Queries”(page 80).2. To save the filter, select Save at the top of

Table of ContentsLearn About This Document...7Publishing H

30

4 Use the Neoview PaneNeoview and Customized ViewsThe Neoview and customized views allow you to define which system and workload metricsyou want to di

Select Content to AnalyzeIn the Neoview or customized views, you have full control over the performance metrics to bedisplayed. You can use the worksp

NOTE: You cannot delete the Neoview pane.Other Tasks on GraphsPlace the cursor in a graph and right click to show the shortcut menu. The shortcut menu

34

5 Use the Live View PaneThe Live View pane provides a point-in-time display. It displays queries that are currentlyrunning on the system (or completed

are connected to the Neoview platform, Live View automatically downloads a list of currentlyrunning queries and refreshes itself on a pre-configured i

Abnormally Terminated. Queries with the Abnormally Terminated state will age outand be removed from the display.Actions on Live View QueriesThe Live V

Threshold Rule Creation WizardTo open the Threshold Rule Creation Wizard, which provides various menu items to help withthe creation or modification o

Workspaces TabThe Workspaces tab is the first tab displayed when the Threshold Rule Creation Wizard isopened. The Workspaces tab lists the workspaces

Disconnect/Reconnect to the Neoview Platform...253 Use the Workspace To

Running Threshold RulesAfter a threshold rule has been created and has been enabled for one or more workspaces, therule will perform the indicated act

6 Neoview Workload Management Services (WMS)ConfigurationConfiguring Neoview WMS Services and Rules With Live ViewNeoview Performance Analysis Tools i

When you click on [Monitor Query], Live View brings up the Query Details window for thequery that allows you to watch query executions. You can watch

DescriptionCategoryStatistics on a per-table basis. These are the individual statistics foreach table (per-table) involved in the query. See “Per-Tabl

The Session Statistics window appears:This list provides a short description of the Session Statistics fields:DescriptionStatisticClient application n

DescriptionStatisticClient data source name used at the time of theconnection.DatasourceTime in microseconds spent on communication with theexternal a

DescriptionStatisticTotal number of SQL statement execution errors detectedby the NDCS SQL server.Total ErrorsTotal number of SQLExecDirect calls.Tota

DescriptionMetricThe amount of time for which a query ran (or is running). This is thetime since the start of the query until the current time for run

DescriptionMetricIndicates the difference (current – previous interval samples) inthe number of rows inserted, updated, or deleted.Delta IUDProvides t

DescriptionMetricIndicates an estimate of the number of rows accessed in the diskprocess.Compilation Disk Process Rows AccessedIndicates an estimate o

Run-Time Metrics...49Miscellanous

DescriptionMetricNumber of Executor server processes created.Processes CreatedProcessor (CPU) utilization by the disk process.Disk Process BusyTimePro

DescriptionMetricID of the parent query. If there is no parent query ID associated withthe query, NONE is displayed.Parent Query IDProcess priority of

Rule WarningsRule warnings display any warnings of Neoview WMS rule violations. The query’s WARN_LEVEL(Warning Level) state is an indicator of rule wa

To bring up a detailed window that provides current service configuration on the Neoviewplatform, click on the magnifying glass icon from either the S

configured and active services and the number of services that have exceeded their thresholdsettings.On the upper right corner of the summary box is a

Statement and Service Counter DetailsTo bring up a detailed window that provides current service configuration on the Neoviewplatform and historical d

"Mon, 24 May 2010 12:25:56 GMT", 5/24/2010 12:25:56 PM, 0.263803899288177, 20.2562503814697, , HPS_MANAGEABILITY, ACTIVE, HIGH, 0, 0, 0, FRO

The left pane displays all available WMS configuration entities in a hierarchical tree structure.The entities include the WMS system configuration, WM

DescriptionFieldInterval, in minutes, after a query starts executing whenWMS starts evaluating the execution rules. Hidden forreleases prior to R2.5.R

This table describes the attributes of the WMS services that appear in the Services Summarypane.DescriptionFieldName of the serviceSERVICE_NAMEState o

Triage Space Query Details...82Triage

DescriptionFieldPriority of the service relative to other services in WMS:• URGENT• HIGH• MEDIUM-HIGH• MEDIUM• LOW-MEDIUM• LOWNOTE: The service priori

be stopped. If one of these system-created services is in the selected list, it will be ignoredand informational message will be displayed.• [Hold] an

This table describes the interface attributes:DescriptionAttributeName of the service. Service names support Neoview character sets.Service Name (max

The following attributes are displayed:DescriptionAttibuteName of the service. This is a read-only field.Service Name (max . 24 characters)Priority of

DescriptionAttibuteMaximum time interval a query can be in the holding state beforethe query automatically times out. Default from WMS systemconfigura

Selecting Stop Immediately cancels all executing queries in the service, instead of allowing thequeries to finish executing. Waiting queries are rejec

Note this information about the Rules Summary:• When you select the Connection Rules tree node, only the connection rules are displayedin the right su

NOTE: System-generated rule names start with an HPS_ prefix. You cannot create a rulewith the HPS_ prefix. It is reserved for system rules.• [Associat

Compilation Rules, or Execution Rules tree node and right click and select the Add Rule menuitem.Add a New Connection RuleTo add a connection rule, on

When adding a rule, you can provide information in the following fields:DescriptionField NameName of the rule. Up to 24 characters (can be multi-bytes

Learn About This DocumentThis guide describes how to use the Neoview Performance Analysis Tools to monitor and analyzequeries.Publishing HistoryThis g

Add a New Compilation RuleTo add a compilation rule, on the Add Rule dialog, set the Rule Type radio button to Compilation.• Warn Level and Rule Actio

When adding a rule, you can provide information in the following fields:DescriptionField NameName of the rule. Up to 24 characters (can be multi-bytes

For detailed information about the fields in the description, see the Neoview Workload ManagementServices Guide.Add a New Execution RuleTo add an exec

When adding a rule, you can provide information in the following fields:DescriptionField NameName of the rule. Up to 24 characters (can be multi-bytes

• Select a rule from the list. Next, select a service from the Services list and click [Associate].The association should appear in the Associated Rul

• [Add] at the bottom of the screen and the plus sign icon on the toolbar are always enabledand launch the Add Admin Role dialog box that lets you add

This list describes the Export WMS Configuration actions:• For the WMS system, an ALTER WMS command is generated.• For each of the user-added services

7 Use the Triage Space PaneThe Triage Space isolates queries you select in the Neoview and customized views for analysisin the Query Workbench. Triage

DescriptionTriage Space Pane PartShows the properties filters. Click on the Workload Filter bar to the leftside of the table grid to show the filters.

Query Candlestick GraphThe query candlestick graph is a graphical representation of the way workloads and queries ranover a specific period of time. T

Neoview Customer LibraryThe manuals in the Neoview customer library are listed here for your convenience.• AdministrationInformation about security fe

Triage Space Button PanelThe Triage Space button panel commands:DescriptionButtonsShows a preview (three lines) of the SQL statement for each query.[P

Dimensions:DescriptionFilterSelect application IDs from the list. Click on the icon at the rightside of the blank field for a list of IDs.Applications

Triage Space Data GridThe Triage Space data grid is a tabular representation of the queries shown in the candlestickgraph. The list of queries control

The Query Details tab provides this information:• Type: MetricsDescriptionFieldQuery's starting priority. The priority of the NDCS connectivityse

DescriptionFieldIndicates estimated total number of rows that will be used. Thisis the compiler estimate of the number of rows that the Executorreturn

DescriptionFieldNumber of Executor server processes created.Processes CreatedSegment Id for the query.Segment IdEntry Id for the query.Entry IdSequenc

• Type: TimeDescriptionFieldIndicates the time when the query was submitted into theNeoview system.Start TimeIndicates the time when the query complet

Triage Space Statement CountersStatement counters appear at the bottom of the Triage Space pane and provide summaryinformation about the queries that

88

8 Use the Query WorkbenchQuery Workbench Pane in NPA = Neoview Query WorkbenchUse the Query Workbench pane to analyze queries. Query Workbench is most

• ConnectivityReference information about the HP Neoview JDBC Type 4 Driver API.Neoview JDBC Type 4 Driver APIReferenceInformation about using the HP

• SQL Text shows the SQL statement for the query selected in the Query List. For moreinformation, see “SQL Text Box” (page 93).• Statistics shows the

• Table Statistics shows details about all the tables and statistics about all columns in thetables used in the current Explain query plan. For more i

All boxes in the Query Workbench can be moved by selecting the box to move it, or resize byclicking the box in the upper right corner of the box.Query

queries are handled differently. See “Difference Between Checked or Selected Queries” (page 93).Notice the icons at the top of the Query List box:Desc

• “Execute or Explain the Selected Query” (page 94).• “Enter a New Query” (page 94) or paste in a query from somewhere else, then execute orexplain th

Rename the QueryYou can rename the selected query in the Name text box above the query field.StatisticsThe Statistics box contains query statistics an

The columns shown in the Table Statistics include:DescriptionColumnUnique entry counts.UECColumn names in the table.Column NamesHistogram ID.HistIDHis

Operator GroupsEach operator is identified in the query tree by an icon that identifies the group type. These arethe operator groups:File ScanRootExch

Analyze the Explain Plan Through the GridYou can sort on the fields in the Explain plan grid (middle section). Note that when you click onan operator

can execute in the master executor or ESP processes, and groupbys can execute in the masterexecutor, ESP, or ESAM processes.Certain scan operators alw