1 / 20

NVision Performance Tuning

2. nVision. Performance OptionsMatch Indexes to Analysis CriteriaOracle: choice of optimiser. 3. PS/nVision. ledger analysis - special processingdrill down unroll roll-up reportinganalysis by attributeattribute held in a treeTREESELECTnngenerate matrix. 4. nVision ->options. Show Report SQL

stuart
Download Presentation

NVision Performance Tuning

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

    1. 1 nVision Performance Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk

    2. 2 nVision Performance Options Match Indexes to Analysis Criteria Oracle: choice of optimiser Performance Options - configuration options built into nVision that alter at least some of the SQL that is built If you analyse you data in a particular way then you likely to need to match the indexing to the analysis criteria For Oracle only, we tend to find that the cost based optimiser works better. Ill explain at least why I think that is the case.Performance Options - configuration options built into nVision that alter at least some of the SQL that is built If you analyse you data in a particular way then you likely to need to match the indexing to the analysis criteria For Oracle only, we tend to find that the cost based optimiser works better. Ill explain at least why I think that is the case.

    3. 3 PS/nVision ledger analysis - special processing drill down unroll roll-up reporting analysis by attribute attribute held in a tree TREESELECTnn generate matrix nVision is used very heavily within GL in Financials, but it is also used in other areas of the financials product and it can be used in HR too. In Financials a major use of trees in nVision. Typically you have report, usually of ledger data, and at least initially each value of data is a summation of various rows of data. And so you drill into the data, expanding a row or a column into the next layer of detail.. You expand a row or a column into a whole matrix of additional values. This is unrolling the roll up tree. The matrix of values is actually obtained by a few, fairly straightforward SQL statement. It isnt one statement per cell. Sometimes it is one statement per column. But one statement can produce a whole block of values. More of the financial reports are based on LEDGERs and so nVision builds the SQL a little differently.nVision is used very heavily within GL in Financials, but it is also used in other areas of the financials product and it can be used in HR too. In Financials a major use of trees in nVision. Typically you have report, usually of ledger data, and at least initially each value of data is a summation of various rows of data. And so you drill into the data, expanding a row or a column into the next layer of detail.. You expand a row or a column into a whole matrix of additional values. This is unrolling the roll up tree. The matrix of values is actually obtained by a few, fairly straightforward SQL statement. It isnt one statement per cell. Sometimes it is one statement per column. But one statement can produce a whole block of values. More of the financial reports are based on LEDGERs and so nVision builds the SQL a little differently.

    4. 4 nVision ->options Show Report SQL See each SQL before it is executed SQL Trace Oracle Trace 3-tier PSQRYSRV from PT 7.54, 7.05 performance is nearly always about SQL performance, and nVision is no exception As ever with Tuning the challenge is to see the SQL that is hitting the database. Under nVision options click the show report SQL You could also use the PeopleTools trace You could enable Oracle trace for the session If you are using 3-tier all the queries submitted by nVision go the PSQRYSRV (from PeopleTools 7.54 and 7.05)performance is nearly always about SQL performance, and nVision is no exception As ever with Tuning the challenge is to see the SQL that is hitting the database. Under nVision options click the show report SQL You could also use the PeopleTools trace You could enable Oracle trace for the session If you are using 3-tier all the queries submitted by nVision go the PSQRYSRV (from PeopleTools 7.54 and 7.05)

    5. 5 Show Report SQL Window appears just before the SQL that is contains is executed. A windows with the report SQL appears just before the SQL is executedA windows with the report SQL appears just before the SQL is executed

    6. 6 SQL Statement SELECT L1.TREE_NODE_NUM, A.DEPTID, SUM(A.POSTED_TOTAL_AMT) FROM PS_LEDGER A, PSTREESELECT06 L, PSTREESELECT06 L1 WHERE A.LEDGER='ACTUALS' AND A.FISCAL_YEAR=1995 AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 AND A.BUSINESS_UNIT='M04' AND L.SELECTOR_NUM=214 AND A.ACCOUNT>= L.RANGE_FROM_06 AND A.ACCOUNT <= L.RANGE_TO_06 AND ( L.TREE_NODE_NUM BETWEEN 1156779659 AND 1158898302 OR L.TREE_NODE_NUM BETWEEN 1224576269 AND 1288135590) AND L1.SELECTOR_NUM=216 AND A.PRODUCT=L1.RANGE_FROM_06 AND L1.TREE_NODE_NUM BETWEEN 658854164 AND 2000000000 AND A.DEPTID IN ('21200', '21300', '21401', '31000') AND A.CURRENCY_CD=USD' AND A.STATISTICS_CODE=' ' GROUP BY L1.TREE_NODE_NUM, A.DEPTID (Bu m64 - UK Operations - revenue) Here is a typical SQL statement. Produced on the demo database using BU M04 and report REVENUE (Bu m64 - UK Operations - revenue) Here is a typical SQL statement. Produced on the demo database using BU M04 and report REVENUE

    7. 7 Which trees SELECT L1.TREE_NODE_NUM, A.DEPTID, SUM(A.POSTED_TOTAL_AMT) FROM PS_LEDGER A, PSTREESELECT06 L, PSTREESELECT06 L1 WHERE A.LEDGER='ACTUALS' AND A.FISCAL_YEAR=1995 AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 AND A.BUSINESS_UNIT='M04' AND L.SELECTOR_NUM=214 AND A.ACCOUNT>= L.RANGE_FROM_06 AND A.ACCOUNT <= L.RANGE_TO_06 AND ( L.TREE_NODE_NUM BETWEEN 1156779659 AND 1158898302 OR L.TREE_NODE_NUM BETWEEN 1224576269 AND 1288135590) AND L1.SELECTOR_NUM=216 AND A.PRODUCT=L1.RANGE_FROM_06 AND L1.TREE_NODE_NUM BETWEEN 658854164 AND 2000000000 AND A.DEPTID IN ('21200', '21300', '21401', '31000') AND A.CURRENCY_CD=USD' AND A.STATISTICS_CODE=' ' GROUP BY L1.TREE_NODE_NUM, A.DEPTID The emboldened parts show where trees are introduced and used. Each tree select table indicates a roll-up reporting tree. Both trees have nodes with six character names. L is account, notice that it is selector number 214 L1 is product, it is selector number 216 Both of these are 6 character columns on ledger. The emboldened parts show where trees are introduced and used. Each tree select table indicates a roll-up reporting tree. Both trees have nodes with six character names. L is account, notice that it is selector number 214 L1 is product, it is selector number 216 Both of these are 6 character columns on ledger.

    8. 8 Which trees Use selector numbers from query to identify trees select * from pstreeselctl where selector_num in(214,216) SETID TREE_NAME EFFDT VERSION SELECTOR_NUM SELECTOR_ T LENGTH ----- ------------------ --------- ---------- ------------ --------- - ---------- MFG MFG_PRODUCTS 01-JAN-00 44787 216 03-AUG-99 R 6 MFG ACCTROLLUP 01-JAN-00 45057 214 02-AUG-99 R 6 This is how you relate selector numbers to a specfic tree This is how you relate selector numbers to a specfic tree

    9. 9 Without performance options This diagram illustrates the joins involved in the query. Its quite simple. The ledger table is in the middle and it is joined to the TREESELECT tables for each analysis attribute. If the attribute is 4 characters in length then it uses TREESELECT04. The PSLEDGER box has been represented by a large box because LEDGER can often be a massive table, and that is the source of the problem. The Rule Based Optimiser executes the query by joining the ledger table to one of the TREESELECT tables and the then the other. You would cut the query on the ledger table down faster by reading both of the tree tables first, and then reading the rows from ledger which match that criteria, which is what the CBO does when there are sufficiently large data volumnes. You also need to build an index on PSLEDGER which matches the query conditions. The nVision performance options assist this processThis diagram illustrates the joins involved in the query. Its quite simple. The ledger table is in the middle and it is joined to the TREESELECT tables for each analysis attribute. If the attribute is 4 characters in length then it uses TREESELECT04. The PSLEDGER box has been represented by a large box because LEDGER can often be a massive table, and that is the source of the problem. The Rule Based Optimiser executes the query by joining the ledger table to one of the TREESELECT tables and the then the other. You would cut the query on the ledger table down faster by reading both of the tree tables first, and then reading the rows from ledger which match that criteria, which is what the CBO does when there are sufficiently large data volumnes. You also need to build an index on PSLEDGER which matches the query conditions. The nVision performance options assist this process

    10. 10 With performance options The performance options break the query up and do the select from the tree first and take the results from that query and build them into the second query on the ledger table.The performance options break the query up and do the select from the tree first and take the results from that query and build them into the second query on the ledger table.

    11. 11 Security for performance options In order to use the nVision performance you will need the relevant security access.In order to use the nVision performance you will need the relevant security access.

    12. 12 nVision Performance Options Select the nVision performance options under the performance tab in excel. Select the tree that you identified For Oracle at lease Use literal values where feasible. use static selectors - dynamic selectors mean that the tree select table is rebuilt at every execution. Use the between option. The SQL is shorter and neater. On Informix between used to, although it may not any longer, cause problems. There we used <= and >=. Makes absolutely no difference on OracleSelect the nVision performance options under the performance tab in excel. Select the tree that you identified For Oracle at lease Use literal values where feasible. use static selectors - dynamic selectors mean that the tree select table is rebuilt at every execution. Use the between option. The SQL is shorter and neater. On Informix between used to, although it may not any longer, cause problems. There we used <= and >=. Makes absolutely no difference on Oracle

    13. 13 Resultant SQL SELECT L1.TREE_NODE_NUM, A.DEPTID, SUM(A.POSTED_TOTAL_AMT) FROM PS_LEDGER A, PSTREESELECT06 L1 WHERE A.LEDGER='ACTUALS' AND A.FISCAL_YEAR=1995 AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 AND A.BUSINESS_UNIT='M04' AND ( A.ACCOUNT='400000' OR A.ACCOUNT BETWEEN '401000' AND '403000' OR A.ACCOUNT='410000' OR A.ACCOUNT='420000' OR A.ACCOUNT='499999') AND L1.SELECTOR_NUM=216 AND A.PRODUCT=L1.RANGE_FROM_06 AND L1.TREE_NODE_NUM BETWEEN 658854164 AND 2000000000 AND A.DEPTID IN ('21200', '21300', '21401', '31000') AND A.CURRENCY_CD='USD' AND A.STATISTICS_CODE=' ' GROUP BY L1.TREE_NODE_NUM, A.DEPTID The effect is to remove the account rollup tree. You dont see the addition query unless you use one of the traces, but on large data volumes, where the ledger table is appropriately indexed I have seen reports run 60 times faster. I keep talking about what is an appropriate index. Look at the join columns on PSLEDGERThe effect is to remove the account rollup tree. You dont see the addition query unless you use one of the traces, but on large data volumes, where the ledger table is appropriately indexed I have seen reports run 60 times faster. I keep talking about what is an appropriate index. Look at the join columns on PSLEDGER

    14. 14 Appropriate Index LEDGER single value FISCAL_YEAR single value ACCOUNTING_PERIOD range of values BUSINESS_UNIT one value ACCOUNT some values/ranges PRODUCT equi-joined to L1 DEPTID some values CURRENCY_CD one value STATISTICS_CODE one value grouped by L1.TREE_NODE_NUM, DEPTID I keep talking about what is an appropriate index. Look at the join columns on PSLEDGER These are the join columns, currently in the same order as in the queryI keep talking about what is an appropriate index. Look at the join columns on PSLEDGER These are the join columns, currently in the same order as in the query

    15. 15 Appropriate Index FISCAL_YEAR single value DEPTID some values LEDGER single value BUSINESS_UNIT single value PRODUCT equi-joined to L1 ACCOUNT some values/ranges CURRENCY_CD single value STATISTICS_CODE single value ACCOUNTING_PERIOD range of values grouped by L1.TREE_NODE_NUM, DEPTID Here I have changed the order. Fiscal year has moved to the top because it is queried by a single value, and in future years that key will become more important. From Oracle 8 you can partition a table by a particular column. Fiscal year is the best candidate. There will not be so many partitions that you cannot manage them. It makes it easy to archive old data. You just drop the partition. You always have a single year query. If you do partition by fiscal_year, you no longer need to index by it, because each partition will contain only a single value. DEPTID came next, the in list is treated as a number of individual values, and the query is grouped by DEPTID LEDGER has only a few values, most reports use the ACTUALS ledger, which contains the most rows. Doesnt cut the query down Same issues with BUSINESS_UNIT. Most of the business in one BU. Product equi-joined to TREESELECT06. Effectively a single value ACCOUNT limited number of values, but some are ranges Currency code and statistic code are not usually useful keys. Exceptions will be reporting in non-base currency. In EURO countries during the change over period, when dual currency reporting, currency code could become useful. Accounting period, might keep at back of index if doing quarterly reports. For single period reports another index with ACCOUNTING_PERIOD in second position.Here I have changed the order. Fiscal year has moved to the top because it is queried by a single value, and in future years that key will become more important. From Oracle 8 you can partition a table by a particular column. Fiscal year is the best candidate. There will not be so many partitions that you cannot manage them. It makes it easy to archive old data. You just drop the partition. You always have a single year query. If you do partition by fiscal_year, you no longer need to index by it, because each partition will contain only a single value. DEPTID came next, the in list is treated as a number of individual values, and the query is grouped by DEPTID LEDGER has only a few values, most reports use the ACTUALS ledger, which contains the most rows. Doesnt cut the query down Same issues with BUSINESS_UNIT. Most of the business in one BU. Product equi-joined to TREESELECT06. Effectively a single value ACCOUNT limited number of values, but some are ranges Currency code and statistic code are not usually useful keys. Exceptions will be reporting in non-base currency. In EURO countries during the change over period, when dual currency reporting, currency code could become useful. Accounting period, might keep at back of index if doing quarterly reports. For single period reports another index with ACCOUNTING_PERIOD in second position.

    16. 16 Oracle Optimiser Mode Queries are flat - no correlated sub-queries Rule based optimiser follows the links Cost based optimiser, sometimes doesnt GL nVision queries perform well under Cost Base Optimiser Choice of Oracle Optimiser The queries are flat. There are no correlated sub-queries, which can confuse the cost based optimiser. Rule based optimiser follows the links. Consider PSLEDGER is a big matrix. One tree defines rows the other defines columns. If you start with one tree then you have to get a whole row and remove the cell you are interested in with the other tree. If you evaluated both trees before going to ledger, you would have co-ordinates of the one cell you are interested in. Obviously that is faster because you dont have to fetch data only to throw it away.Choice of Oracle Optimiser The queries are flat. There are no correlated sub-queries, which can confuse the cost based optimiser. Rule based optimiser follows the links. Consider PSLEDGER is a big matrix. One tree defines rows the other defines columns. If you start with one tree then you have to get a whole row and remove the cell you are interested in with the other tree. If you evaluated both trees before going to ledger, you would have co-ordinates of the one cell you are interested in. Obviously that is faster because you dont have to fetch data only to throw it away.

    17. 17 If using Oracle Rule Based Optimiser Rename PSTREESELECTnn to PSTREESELECTnn_TBL CREATE VIEW PSTREESELECTnn AS SELECT /*+ALL_ROWS*/ * FROM PSTREESELECTnn_TBL Rename the table PSTREESELECTnn, and create a view of the same name pointing to the new name. You can put a cost based optimiser hint in the view. Any SQL that references the view will run under the cost based optimiser. The view is based on only one table, so you can update via the view. I would not use this trick on PS_LEDGER, that might affect all sorts of other processes. This may need to be done for more than one PSTREESELECTnn table.Rename the table PSTREESELECTnn, and create a view of the same name pointing to the new name. You can put a cost based optimiser hint in the view. Any SQL that references the view will run under the cost based optimiser. The view is based on only one table, so you can update via the view. I would not use this trick on PS_LEDGER, that might affect all sorts of other processes. This may need to be done for more than one PSTREESELECTnn table.

    18. 18 If using Oracle 8.x The Cost Based Optimiser in invoked by Parallelism Partitioning If you specify a degree of parallelism on a table, ie you say that you want the table to be scanned by a number of parallel query processes, from Oracle 8 this forces use of the cost based optimiser, you cannot do anything about this, even a rule hint is overridden by parallelism. From Oracle 8.0 you can range partition a table. The tables is broken into different physical units, but logically it remains a single table. Partitioning is another form of parallelism. If you partition a table into n physical segments, then you could use n parallel query processes to scan it. Development on the rule based optimiser stopped with Oracle 7.3. A partitioned table can only be handled by the cost based optimiser. If you are going to partition PSLEDGER, I would recommend that you partition it by FISCAL_YEAR. One fiscal year per partition. Then if you only query one fiscal year, and you have seen that the typical nVision queries do exactly that. Oracle performs partition elimination, and only scans the partition with that FISCAL_YEAR. In which case there is no longer any need to index FISCAL_YEAR in the non-unique indexes.If you specify a degree of parallelism on a table, ie you say that you want the table to be scanned by a number of parallel query processes, from Oracle 8 this forces use of the cost based optimiser, you cannot do anything about this, even a rule hint is overridden by parallelism. From Oracle 8.0 you can range partition a table. The tables is broken into different physical units, but logically it remains a single table. Partitioning is another form of parallelism. If you partition a table into n physical segments, then you could use n parallel query processes to scan it. Development on the rule based optimiser stopped with Oracle 7.3. A partitioned table can only be handled by the cost based optimiser. If you are going to partition PSLEDGER, I would recommend that you partition it by FISCAL_YEAR. One fiscal year per partition. Then if you only query one fiscal year, and you have seen that the typical nVision queries do exactly that. Oracle performs partition elimination, and only scans the partition with that FISCAL_YEAR. In which case there is no longer any need to index FISCAL_YEAR in the non-unique indexes.

    19. 19 Non-Ledger queries Query is defined in PS/Query For non nVision ledger queries the queries are simply defined in PS/Query, and as you can see the SQL is the same. Except for the highlighted bit. That is added by nVision. Where the rows in the query are in the part of the tree that you are interested in. You cannot change the correlated sub-query. Ive added an index on PSTREESELECT10 that leads on the columns reference in the sub-query. Consequently the performance options have no meaning here. These queries can be based on views, can contain sub-queries, and then nVision adds a correlated sub-query. None of what has just been discussed will necessarily work under these situations. If you use the technique on the previous slide then the whole of this query will also run under cost. That may not be appropriate. But, at least you can change the query, you can add hints, including a rule hint. All the usual SQL optimisation techniques are open to you because you can change the SQL.For non nVision ledger queries the queries are simply defined in PS/Query, and as you can see the SQL is the same. Except for the highlighted bit. That is added by nVision. Where the rows in the query are in the part of the tree that you are interested in. You cannot change the correlated sub-query. Ive added an index on PSTREESELECT10 that leads on the columns reference in the sub-query. Consequently the performance options have no meaning here. These queries can be based on views, can contain sub-queries, and then nVision adds a correlated sub-query. None of what has just been discussed will necessarily work under these situations. If you use the technique on the previous slide then the whole of this query will also run under cost. That may not be appropriate. But, at least you can change the query, you can add hints, including a rule hint. All the usual SQL optimisation techniques are open to you because you can change the SQL.

    20. 20 nVision Performance Options Match Indexes to Analysis Criteria Oracle: choice of optimiser non-GL queries are different Performance Options - configuration options built into nVision that alter at least some of the SQL that is built If you analyse you data in a particular way then you likely to need to match the indexing to the analysis criteria For Oracle only, we tend to find that the cost based optimiser works better. Ill explain at least why I think that is the case. For non-GL queries none of the above is still guaranteed.Performance Options - configuration options built into nVision that alter at least some of the SQL that is built If you analyse you data in a particular way then you likely to need to match the indexing to the analysis criteria For Oracle only, we tend to find that the cost based optimiser works better. Ill explain at least why I think that is the case. For non-GL queries none of the above is still guaranteed.

    21. 21 nVision Performance Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk Fell free to e-mail me if you wish. Mobile phone number +44(0)7771-760660Fell free to e-mail me if you wish. Mobile phone number +44(0)7771-760660

More Related