1 / 21

nVision Performance Tuning

nVision Performance Tuning. David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk. nVision. Performance Options Match Indexes to Analysis Criteria Oracle: choice of optimiser. PS/nVision. ledger analysis - special processing drill down

jarah
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. nVision Performance Tuning David Kurtz Go-Faster Consultancy Ltd. david@go-faster.co.uk www.go-faster.co.uk

  2. nVision • Performance Options • Match Indexes to Analysis Criteria • Oracle: choice of optimiser

  3. PS/nVision • ledger analysis - special processing • drill down • unroll roll-up reporting • analysis by attribute • attribute held in a tree • TREESELECTnn • generate matrix

  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

  5. Show Report SQL • Window appears just before the SQL that is contains is executed.

  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

  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

  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

  9. Without performance options

  10. With performance options

  11. Security for performance options

  12. nVision Performance Options

  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

  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

  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

  16. Oracle Optimiser Mode • Queries are flat - no correlated sub-queries • Rule based optimiser follows the links • Cost based optimiser, sometimes doesn’t • GL nVision queries perform well under Cost Base Optimiser

  17. If using Oracle Rule Based Optimiser • Rename PSTREESELECTnn to PSTREESELECTnn_TBL CREATE VIEW PSTREESELECTnn AS SELECT /*+ALL_ROWS*/ * FROM PSTREESELECTnn_TBL

  18. If using Oracle 8.x • The Cost Based Optimiser in invoked by • Parallelism • Partitioning

  19. Non-Ledger queries • Query is defined in PS/Query

  20. nVision • Performance Options • Match Indexes to Analysis Criteria • Oracle: choice of optimiser • non-GL queries are different

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

More Related