nVision Performance Tuning

2008-03-31T13:05:00.000-07:00

As many PeopleSoft developers and managers I have suffered through my share of nVision Tuning efforts and until very recent we could not get our reports to run under 4 hours, until the recent change...

In any case here are a few points from my experience:

We experienced an almost almost 5 fold increase in performance when we did a PS_LEDGER reorg with PSCLEDGER index option as follows:

db2 "reorg table PSFSSYS.PS_LEDGER index PSFSSYS.PSCLEDGER allow read access indexscan"
db2 "RUNSTATS ON TABLE PSFSSYS.PS_LEDGER FOR INDEX PSFSSYS.PSCLEDGER"
db2 "reorgchk current statistics on table PSFSSYS.PS_LEDGER"

Also it is great to cleanup treeselectors, to make sure that static selectors did not get out of sync with trees, this can be done on a regular basis, see a sample script below. Please note that the number in the end of the treeselector name xx - corresponds to the chartfield length:

-- Delete TreeSelectors
Delete from PSFSSYS.PSTREESELECT05;
Delete from PSFSSYS.PSTREESELECT06;
Delete from PSFSSYS.PSTREESELECT10;

-- Delete Tree Control table for the respective selectors above
delete from PSFSSYS.pstreeselctl
where length in (5,6,10);

For details about PeopleSoft nVision architecture and structure please make sure to read the PeopleSoft nVision Red Paper:

Here is a almost a complete document from PeopleSoft that talks about key options and structure of nVision. I will try and post the actual doc with images as soon as I get hosting for the website or get the link to PS Document.

Topic: nVision Performance

Date: 06/09/2003

Tools Release: 8.1x & 8.4x

Purpose

The purpose of this document is

· To help understand the way nVision works so that it could be easier to tune and maintain nVision reports, for a better and improved performance.

· To perform as a one-stop document for nVision performance issues.

Scope

· Understanding PS/nVision for better performance.

· Where to set nVision performance options.

· PS/nVision Performance Tuning Sequence.

· What could be done to improve performance

· Creating good nVision layouts.

· Scenarios.

· Quick checklist.

· Where to look for more information.

Content

The document was prepared from various documents collected overtime from Development, PeopleSoft Consultants, PeopleBooks, Customer’s and GSC Resolution database.

Fine-tuning nVision Layouts may require very extensive analysis from customers and may sometimes result in lot of work to correct the root of the problem. Additionally, this is not the type of tuning/monitoring that only happens once. As time progresses, reporting needs change, the data may grow, new queries and criteria can be added without the knowledge of impact on performance. There is a need to monitor large nVision reports consistently, weigh the cost of reporting time, tuning efforts, disk space and maintainability.

Understanding PS/nVision for better performance

Overview of Tree Tables Used by PS/nVision:

Before attempting to tune an nVision layout, it is beneficial to understand the structure of various tree tables and how PS/nVision uses them. The main tables used are PSTreeDefn, PSTreeLeaf, PSTreeSelCtl, and PSTreeSelectnn.

PSTREEDEFN: Tree Definition

Defines an effective-dated version of a tree—i.e., it contains one effective dated row for each tree.

PSTREELEAF: Tree Leaf

Defines the data value ranges that compose the leaves of a tree. For each “leaf node” (nodes without children) one or more ranges define the detail values that correspond to that node.

PSTREESELECTnn: Tree Select Work-Size nn

Defines Selectors used by PS/nVision to speed tree-based data selection. A selector table is defined for every possible detail field length (nn = 01-30); thus this description applies to tables named PSTREESELECT01, PSTREESELECT02, and so on, through PSTREESELECT30.

PSTREESELNUM: Tree Select Control Number

PS/nVision uses this table to assign a unique SELECTOR_NUM value to each tree selector as it is built. This table has only one row.

PS/nVision is a Microsoft Excel based reporting tool that generates dynamic SQL based on the structure of the report layout. Note that it is not possible to rewrite the queries generated by nVision; but it is possible to affect the number of joins, create slightly more efficient joins, and limit the selection criteria to specific portions of a tree.

PS/nVision includes three options to enhance the performance of generated SQL.

These are:

Selector Type

nVision uses selectors to speed retrieval of data through the trees. A selector associates

nodes to a single tree, and is represented by the rows in the PSTREESELECTnn table having a single SELECTOR_NUM value. Selectors can be static or dynamic.

Static Selectors

§ Static selectors are created once and retained until the underlying tree has changed.

§ Static selectors represent an entire tree. This means that a report must specify node criteria for the desired data.

§ Static selectors use ranges.

Node criteria and ranges produced by static selectors can often produce

complicated query predicates that pose more of a ‘ challenge’ to the optimizer.

Dynamic Selectors

§ PS/nVision can build dynamic selectors “on the fly” when preparing to execute a report.

§ Each dynamic tree selector is built for a specific set of criteria (such as a set of rows or the current instance node), so that a selector (SELECTOR_NUM value) has exactly the nodes needed for a group of rows or columns to be retrieved with a SELECT.

§ Dynamic selectors eliminate the need for the often cumbersome selection criteria that PS/nVision generates for a static selector: TREE_NODE_NUM BETWEEN x AND y OR TREE_NODE_NUM BETWEEN...

Selector Options

Single Values

§ Use single values in conjunction with dynamic selectors.

§ Using individual values from the detail table specified in the tree structure, PS/nVision will build dynamic selectors that cover the detail ranges of the selected nodes.

§ Tree ranges are joined to the underlying detail table and the individual values are inserted to the RANGE_FROM_nn column of the appropriate PSTREESELECTnn table at layout run-time.

§ Single-value (dynamic) selectors enable a more efficient equi-join between PSTREESELECTnn.RANGE_FROM_nn and the criteria field in the ledger table.

§ Combining the dynamic and single-value selector techniques drastically improves the performance of PS/nVision in the majority of cases where trees are used.

Ranges of Values

A tree is considered to have ranges if it contains at least one detail range with different low and high values. When a tree has large ranges containing many detail values, single value selectors can cover many rows. In this case, the use of single value selectors may be inappropriate.

In cases such as this, use the Ranges of Values option. >= <= Syntax

This produces syntax similar to the following when joining a field to a ranged selector:

WHERE … A.ACCOUNT >= L.RANGE_FROM_06 AND A.ACCOUNT <= L.RANGE_TO_06 …

Between Syntax

This produces syntax similar to the following when joining a field to a ranged

selector:

WHERE … A.ACCOUNT BETWEEN L.RANGE_FROM_06 AND L.RANGE_TO_06 …

Two syntax options are provided because the possibility exists that one could out-perform the other on certain database platforms. In most cases, there was no measurable difference in performance when these options were tested. In general, start performance tuning and benchmarking with the between syntax.

Non-Specific Node Criteria (Above 2 Billion)

This option makes sense only when used with ranged static selectors. Note that it is disabled unless the Static Selector button has been pressed.

Suppress Join

§ The “suppress join” technique eliminates joins by retrieving the detail ranges associated with the selected node and coding them in the SELECT statement. The best use of this technique is often in conjunction with a scope field where each instance represents a moderate level of summarization.

§ The suppress join technique cannot be used where PS/nVision needs to group the answer set by tree node number because these numbers are not available without joining the data to the tree.

If nVision cannot suppress a join because the result set must be grouped by tree node, the suppress join option will be ignored, and the selected join method (dynamic or static) will be used. This is why the proper selector method should always be checked whether or not you are attempting to use the Suppress Join option.

Please refer to People Books for more detail description on Options.

Where to set nVision Performance options

Access the Tree Performance tab by selecting nVision menu once you launch nVision, then Open Layout, Layout Options, then select the Tree Performance Tab.

§ Remember that these options must be set for each individual tree that is accessed in each individual layout. Note that the same options may not be appropriate for all trees, and that the same combination of options is not necessarily appropriate for all reports. Optimum performance is sometimes achieved using different options for different trees, depending on the nature of the tree and the manner in which the tree is used in the report.

§ Select the tree in the Tree Name list box. Set the proper options, and hit the apply button. Continue to the next tree. Hit the apply button after options have been set for this tree. Save the layout when you have set options for all trees.

§ If you do not specify the tree performance options for a tree used in a report layout, and no performance options are defined in Tree Manager from that trees, PS/nVision uses the same SQL Techniques used in the past on your database platform.

Be prepared for some trial and error to find the best settings for your data and reporting requirements. Performance options specified in the tree will be overwritten by Layout Performance options.

PeopleSoft nVision Options Panel

This panel helps in tuning nVision reports. Go thru the following section to know it’s functionality

To find the panel, go to the nVision drop-down menu and select ‘ Options’ .

§ The ‘ Runtime Options’ will take effect when the layout executes.

§ ?Show Warning Messages – will display a message box(es) with any warning messages as the layout executes.

§ ?Show Report SQL – will write each query that is generated to a text box before execution. Note that report execution will not continue until you have responded by hitting the ‘ Ok’ push button. The ‘ Cancel’ push button will terminate execution of the report.

§ ?Suppress Amount Retrieval – instructs nVision not to pass the query off for execution. You will see the generated queries, but they will not be executed.

§ ?Show Row and Column Criteria allows you to see the contents of Row1 and Column A. This area is reserved by nVision for data selection criteria when the layout is selected. Selecting this option will allow you see which Trees are used in the report.

PS/nVision Performance Tuning Sequence

1. Use ‘ Show Row and Column Criteria’ to determine the Trees that are used in the layout. Or, set a PeopleSoft SQL trace, and search through the trace file looking for the names of all trees used in the layout. Search on the string ‘ PSTREEDEFN’ , and look for the name of each tree. The tree name should appear as one of the ‘ values’ in the line(s) immediately following the select from the PSTREEDEFN table.

Note that any tree that is used by a layout through a scope, may not appear in Row 1 or Column A of the layout. Unless you know specifically which trees are used in layouts and are applied via specific scopes, the SQL trace is your best option for determining all trees referenced in an nVision layout.

2. Set performance options in the Tree Performance Options dialog.

3. Set ‘ Show Report SQL and Suppress Amount Retrieval’ together to obtain queries for access path analysis. Review the queries that are produced based on the performance options that you have selected.

4. Cut and paste each query into your SQL Tool to generate an Explain for the access path. You can step through each query executed by the layout in this manner.

5. Use the Explain and your familiarity with your database optimizer to change the performance options (if necessary) and/or design indexes to enhance layout performance.

What could be done to improve performance

Now that the problem(s) have been identified some or all of the following may apply:

1. Decrease the size of the Summary Ledger

§ Reduce the number of ChartFields on the Summary Ledger

§ Reduce the number of values for each ChartField by using summary ChartFields

§ Compress the Summary Ledger with summary nodes (use summary trees)

§ Summarize data at a level that will satisfy a number of reports

§ Take advantage of Drill Down functionality if you need Detail Ledger or Journal data.

2. Tree considerations

The goals when using a Tree in the selection criteria (ultimately a Select stmt);

The 1st Tree select should yield data that will reduce the results as greatly as possible. Control joins, indexes and steps

§ Using more than 2 or 3 trees causes problems for some database platforms.

§ Multiple Levels within a tree have no impact on nVision performance (it actually ignores them), volumes of data do impact nVision performance.

§ If any of the trees contain only a few values used in this report, consider using Selected Detail Values to reduce the number of tables joined.

§ Trees without ranges: on some platforms (e.g., Oracle), the SQL we generate for a tree with ranges runs slower than what we use for a tree with no ranges. Some customers have made a point of using only single detail values in selected trees to get this performance benefit. But there's a tradeoff: We don't recommend doing this if it will greatly increase the number of rows in the tree selector, or impose a maintenance burden for users. On the DB2 platform there have actually been performance improvements using ranges!

We recommend not to make changes to trees while nVision reports are running, this can result in the report not reflecting those changes

3. Tune indexes

The goal of optimizing SQL and indexes is to reduce the number of compares needed to work through the access plan. This is best achieved by cutting the number of qualifying rows in the first JOIN step, usually by utilizing an index that combines literal values (such as Fiscal_Year and Ledger) with the most selective tree criteria field (often ACCOUNT). A long run time and high number of "Gets" indicates too many qualifying rows early in the plan, which has a multiplier effect on the plan as a whole. Note the importance of identifying the most selective ChartField.

While we cannot control what and when Tree Selector tables are used in an nVision created SQL statement, you can influence the optimizer by seeding indexes.

Ledger Indexing Rule of Thumb:

1st use all columns that have equalities (Fiscal_Year = 1995, etc)

2nd use the most selective field that will be used in the range predicate

In General use the more efficient ChartFields (higher column cardinality) as indexes

Seeding: (seed after indexing to take advantage of the new indexes!) by adding rows to the most useful tree selector table (e.g, PSTREESELECT06) with SELECTOR_NUM values 1-100, then updating statistics, you can make the index on this table more attractive.

4. Rebuilding Tree Selectors for nVision

PSTREESELNUM should always contain a single row that represents the last assigned selector number. Zero is the correct value for a fresh database, provided there are no rows in PSTREESELCTL or PSTREESELECTxx (where xx is the width of the detail field over which the tree is built, i.e. a field size between 01 AND 30). The selector number is used to identify a row in PSTREESELCTL or a set of rows in the PSTREESELECTxx tables. The PSTREESELECTxx tables are join tables used for higher performance tree criteria processing.

PSTREESELCTL is the table that controls whether or not to refresh data in the selector tables based on the Tree effective date. If you modify a tree and save it, the pstreeselect tables will get updated when you run an nVision report that uses that tree

The PSTREESELECTnn tables are used to speed tree-based data selection. The PSTREESELECTnn table is populated when either Query or nVision first uses that tree after the tree is changed. The PSTREESELECTnn tables were originally created when it was discovered that some database platforms do not use indexes on joins between differently sized fields.

PSTREELEAF always uses 30-character RANGE_FROM and RANGE_TO fields, regardless of the size of the dimension field being rolled up by the tree. To get adequate performance we found it necessary to copy the leaf-rollup information into a join table with a correctly sized dimension field, which we could then join to repeatedly while generating a report. The selector tables should be thought of as tree cache information used by runtime tree criteria processing. The use of selector tables is currently pretty much limited to nVision and Query, though DataObjects will use them for caching.

nVision/Query detects the change as a difference in version number between the tree itself (the version number in its row in PSTREEDEFN) and the Selector Control Table (PSTREESELCTL). Of course, the first time a particular tree (SETID or SETCNTRLVALUE, TREE_NAME, EFFDT) is used in a report, we don't find it in PSTREESELCTL, and create selector rows and a control row for it. The first user to use the tree after a change builds the new selector, and other users (even just seconds later) use the updated selector. The PSTREESELCTL has a SELECTOR_NUM column that gives us the SELECTOR_NUM value for the set of rows representing this tree's selector in PSTREESELECTnn. Query/nVision, after finding a matching row with a current version number, or creating a new row and selector, uses this number in its SQL without checking if any matching rows exist in PSTREESELECTnn.

The following are 2 methods in rebuild tree selectors.

Method 1: nVision will automatically rebuild selectors if the tree is re-saved after deleting a node and re-adding it, or if the tree is copied to a new effective date.

Method 2: Delete the selector control entries associated with a for example Account trees, forcing nVision to rebuild them. The following SQL statement should do this, assuming that the ACCOUNT trees have a tree structure called ACCOUNT (if not, change the structure ID):

delete from pstreeselctl where tree_name in (select tree_name from pstreedefn

where tree_strct_id='ACCOUNT');

Creating good nVision layouts

Creating good layout design begins with a solid understanding of the PS/nVision SQL Combination Rules and Criteria Inheritance Rules. Knowing when PS/nVision can or cannot combine row and column selections into one SQL statement will help you understand how to minimize the number of SQL calls it will generate, and how complex the SQL will get.

PeopleSoft nVision tries to retrieve the data for each report instance with as few SELECTs as possible. Once a report request is launched, PS/nVision examines all row criteria to determine which are combinable. It does the same for column criteria. It then builds a SELECT statement to retrieve each intersection of a combined group of rows with a combined group of columns.

PS/nVision SQL Combination Rules

The following built-in rules should be understood when designing PS/nVision layouts:

§ Different Ledgers cannot be combined.

§ Different TimeSpans cannot be combined.

§ nPloded rows or columns cannot be combined with non-nPloded rows or columns.

§ To be combined, two or more rows or columns must have criteria for the same set of ChartFields, and each ChartField's criteria must be of the same type (e.g., selected tree nodes cannot be combined with selected detail values).

§ If criteria for a ChartField are specified by tree node, they can only be combined if they use the same tree.

§ If the combined rows or columns have identical criteria for a particular ChartField, the criteria are included in the WHERE clause, but no GROUP BY on that field is required. But if different rows/columns in the group have different criteria, PS/nVision adds this field (or the corresponding tree node number) to the GROUP BY clause to retrieve a value for use in posting the answer set to the appropriate rows/columns of the report.

§ A single SELECT can retrieve amounts for multiple combined rows and columns.

§ Different scope instances are retrieved with separate SELECTs.

§ Some additional rules apply for layouts defined using queries rather than ledgers:

Different queries are not combined.
References to different answer columns in the same query can be retrieved with a single SELECT, so long as they meet the above tests.

Criteria Inheritance Rules

Cells specified by intersection or by single-cell criteria inherit their selection criteria according to the following rules:

§ Criteria defined at the worksheet level are used to specify defaults for the entire worksheet. Criteria at the worksheet level are combined with criteria for columns, rows, and cells except where the row, column, or cell criteria give a different value for the same criterion. In this case, the worksheet criteria are overridden.

§ For the intersection of row and column criteria, the criteria are combined where possible. But, if a row and column conflict, the row overrides the column criteria.

§ Cell criteria are combined with row and column criteria unless the cell criteria conflict with the other criteria. In this case, the cell criteria override any other criteria.

§ If any field criteria are specified in an individual cell, all field criteria should be specified in that cell; no field criteria are inherited from the row, worksheet or column.

§ Report Scope criteria override all other criteria.

Tips for PS/nVision Layout Design

Use nPlosion to get details. nPlosion automatically crates individual rows or columns for each detail value defined in the criteria. When you enable nPlosion for a row, PS/nVision inserts the detail value rows immediately above the nPloded row. Detail value columns are inserted to the left of the nPloded column. Remember, you can nPlode Time Span too.

If possible, keep the number of different trees being used in the same layout to a minimum. Using more than 2 or 3 trees can slow performance for some database platforms.

§ If any of the trees in a layout contain only a few detail values, consider using Selected Detail Values instead to reduce the number of tables joined. Layout maintenance needs to be considered here.

§ Multiple Levels within a tree have no impact on PS/nVision performance (it actually ignores them). Data volumes do impact PS/nVision performance.

§ Take maximum advantage of report variables and relative TimeSpans (versus absolute) to minimize layout maintenance.

§ When creating trees, avoid using special characters in your tree node descriptors. Apostrophes can cause nVision to error out if the tree node is used as a detail value on a summary ledger.

§ If you have problem with “Initializing” Delays - “Initializing” includes a number of activities, primarily digesting the scope and the layout. A large layout takes much longer than small ones. If your layout has inactive cells with active rows & columns below them and to the right, creating a huge area for nVision & Excel to search for potential cell criteria. Some formatting operations at the row or column level can make the active part of the layout seem much bigger than it actually is. If you have a long Initialization delay during the run time, you can try to delete the unused cells from the sheet.

§ PS/nVision provides the ability to group multiple report requests into a single Report Book. Report Books can be scheduled to run on a high-powered, dedicated NT server at off-peak hour. This means that the running of Report Books can improve report performance, and frees up users’ client machines. Now you can run multiple nVision reports at the same time on your server.

Scenarios from different customers

Customer 1

“…performance team are usually established from all sections of IT and the business that control the PIA. They meet 1 week prior and 1 week post end of month to review and analyze performance recommendations.”

Customer 2

“Performance monitoring of nVision can be a time consuming process that needs to combine tools that can monitor and provide Explain plans on individual pieces of SQL, yet you also need to track and record Report Request times to gauge overall gains and losses. Once a system has gone live the complexity of reviewing Report Books, which can contain multiple Report Requests that use various Scopes running across multiple servers becomes even more complex.

It is for this reason that I turned to developing a MS Access system linked directly into the key nVision Report, Process Request, and Report Manager Records to monitor and store statistics on all processes run across the NT Report Servers. The database provides me with detailed analysis of performance by Report Book, Report Request, User, and Server, across any selected time period. It can provide a per Instance average time for Report Books and Report Requests. The functionality also includes a directly linked “Process Monitor” page that contains hyperlinks to show details of the current instances posted to the Report Manager as the job is processing and can also bring up the history of performance for a given Report Request or Report Book.”

PeopleSoft Consulting Services only has access to the MS Access tool.

Customer 3

“The runtime for a particular nVision layout degraded significantly when it used a new effective dated department rollup tree. Our question was whether or not adding a new level to the tree could have caused this and whether defining the tree details to be all at the same level would help performance.

… the insert of the new tree level plus additional detail values was not the cause of the performance change.

When we were updating the new tree version, we saved it with some "ranged" detail values in it. This set the TREE_HAS_RANGES flag on the PSTREEDEFN table to “Y". Subsequently, the customer broke out these ranges into single detail values (no more ranged values were in the tree). However, upon saving the tree again, the TREE_HAS_RANGES flag was not reset to "N" (it stayed as "Y"). This caused nVision to think there were ranged values in the tree, which triggered the sql syntax change to A.DEPTID>= L.RANGE_FROM_10 AND A.DEPTID <= L.RANGE_TO_10 (versus A.DEPTID=L.RANGE_FROM_10 if the flag is "N"). This was a much slower statement on the database.

Customer 4

“….rebuilding, deleting orphaned rows from selector tables improved our performance .”

Customer 5

“This customers scenario was very difficult to troubleshoot. A consultant had to be sent onsite to continuously monitor the system and to finally arrive at a solution

In the beginning nVision reports were running slowly after checking couple of things like indexes, selector tables it seemed to improve but then summary reports for manager were bombing out on 3 tier with memory error.

After tuning the layouts, it's was working, but not consistently. Some complex layouts were having reference memory error on 2/3, and web.

Customer purged SELECTOR table and control table. It worked for the beginning, then a few hours later the Dr. Watson errors happened again

By (1) tuning the PSTREESELECT10 and PSTREESELECT05 tables and (2) purging these 2 tables and their corresponding rows in PSTREESELCTL table regularly, they are able to get nVision layouts run in 2, 3, and 4 tier.

If the tables don't get purged after a few hours, when more than 2 sessions logged into 3 tier to run nVision, they could potentially get the Dr. Watson/memory error. Log files indicated that service is timing out due to the time it takes to run the sql's.

Eventually it turned out the issue was with SQL Server and not with nVision, customer had to upgrade their SQL server to sp4.

The server they are using is a dedicated database server. They have limited the amount of memory available to SQLserver to 1 GB (machine has 1.5GB). Even then, we don't understand why sqlserver uses up all the memory available. We asked the customer to reboot there database server machine and not run nVision or any other process against the server for the day, and then observe what happens at the end of the day. We noticed that within about 4 hours of rebooting, memory usage had MAXed out even before any process was run against the database. Then when the consultant tried to run a crystal report against the database, the process just froze (did not complete) and "insufficient memory" alarms were being posted by a diagnostic tool called spotlight by quest software. At this point, we came to a conclusion that the bulk of the problem seems to reside within the database server. “

Quick check list

Before calling GSC, make sure the following things are checked

Isolate the problem to see if it’s happening with one report or all the report.
If it’s happening with one report check trees, indexes, selectors, scope field values for that report.
If it’s happening to all the reports

§ Audit your trees

§ Verify Indexes on all the tables used by nVision.

§ See that you are up-to-date with the current patches on your database.

§ Compare two, three and web for performance time.

§ Delete orphaned rows from selector tables.

§ Check if any reports are using Queries and try to tune the queries.

§ Use the new logging feature to get more information.

§ Check Network Traffic.

§ Check for other Job’s, Report's, backup’s, background process that are running on the server hosing up the server resources.

§ Check for locks on tables being used by nVision. Exclusive table lock from other processes will cause that particular sql running at that time to time-out, nVision will then continue to run the next SQL.

§ If nVision reports are scheduled to run during the night make sure that they run after your Ledger update processes to avoid exclusive lock.

Where to look for more information

PeopleBooks

nVision Red Paper

GSC Resolution Database.

workingscripts

Windows Update high CPU usage fix

Adding Technorati

SQR - PRINT-IMAGE & IMAGE-SIZE