SlideServe
Télécharger Obtenir Pro
Menu
Accueil Pro
Préférences
ApparenceLight Aide et commentaires
© SlideServe · v2026

nVision Performance Tuning

carlow avatar
carlow
21 diapositives · 365 Views · Oct 01, 2014

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

#nvision performance tuning #nvision performance tuning ppt #powerpoint #presentation
nVision Performance Tuning - slide 1
01 / 21nVision Performance Tuning
1 / 17

Télécharger la présentation

nVision Performance Tuning
Une image / un lien ci-dessous est fourni (tel quel) pour télécharger la présentation

Politique de téléchargement : le contenu du site vous est fourni TEL QUEL pour votre information et votre usage personnel ; il ne peut être vendu, concédé sous licence ni partagé sur d’autres sites sans l’accord de son auteur.Le contenu vous est fourni TEL QUEL, pour votre information et votre usage personnel uniquement. Téléchargez la présentation en cliquant sur ce lien. Lors du téléchargement, si vous ne parvenez pas à télécharger une présentation, l’éditeur a peut-être supprimé le fichier de son serveur.Pendant le téléchargement, si vous ne parvenez pas à obtenir une présentation, le fichier a peut-être été supprimé par l’éditeur.

F I N

Plus de contenu associé

Transcription de la présentation

  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