Transcript nVision Performance Tuning David Kurtz Go-Faster Consultancy Ltd. [email protected] www.go-faster.co.uk nVision • Performance Options • Match Indexes to Analysis Criteria • Oracle: choice of optimiser PS/nVision • ledger analysis -

nVision Performance Tuning
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
1
nVision
• Performance Options
• Match Indexes to Analysis Criteria
• Oracle: choice of optimiser
2
PS/nVision
• ledger analysis - special processing
• drill down
– unroll roll-up reporting
• analysis by attribute
– attribute held in a tree
• TREESELECTnn
• generate matrix
3
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
4
Show Report SQL
• Window appears just
before the SQL that is
contains is executed.
5
SQL Statement
SELECT
FROM
WHERE
AND
AND
AND
AND
AND
AND
AND
L1.TREE_NODE_NUM, A.DEPTID, SUM(A.POSTED_TOTAL_AMT)
PS_LEDGER A, PSTREESELECT06 L, PSTREESELECT06 L1
A.LEDGER='ACTUALS'
A.FISCAL_YEAR=1995
A.ACCOUNTING_PERIOD BETWEEN 1 AND 12
A.BUSINESS_UNIT='M04'
L.SELECTOR_NUM=214
A.ACCOUNT>= L.RANGE_FROM_06
A.ACCOUNT <= L.RANGE_TO_06
(
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
6
Which trees
SELECT
FROM
WHERE
AND
AND
AND
AND
AND
AND
AND
L1.TREE_NODE_NUM, A.DEPTID, SUM(A.POSTED_TOTAL_AMT)
PS_LEDGER A, PSTREESELECT06 L, PSTREESELECT06 L1
A.LEDGER='ACTUALS'
A.FISCAL_YEAR=1995
A.ACCOUNTING_PERIOD BETWEEN 1 AND 12
A.BUSINESS_UNIT='M04'
L.SELECTOR_NUM=214
A.ACCOUNT>= L.RANGE_FROM_06
A.ACCOUNT <= L.RANGE_TO_06
(
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
• Use selector numbers from query to identify
trees
select * from pstreeselctl where selector_num
in(214,216)
SETID
----MFG
MFG
TREE_NAME
-----------------MFG_PRODUCTS
ACCTROLLUP
EFFDT
VERSION SELECTOR_NUM SELECTOR_ T
LENGTH
--------- ---------- ------------ --------- - ---------01-JAN-00
44787
216 03-AUG-99 R
6
01-JAN-00
45057
214 02-AUG-99 R
6
8
Without performance options
9
With performance options
PSTREESELECTnn L
Literal
Values
PS_LEDGER
PSTREESELECTnn L1
10
Security for performance options
11
nVision Performance Options
12
Resultant SQL
SELECT
FROM
WHERE
AND
AND
AND
AND
L1.TREE_NODE_NUM, A.DEPTID, SUM(A.POSTED_TOTAL_AMT)
PS_LEDGER A, PSTREESELECT06 L1
A.LEDGER='ACTUALS'
A.FISCAL_YEAR=1995
A.ACCOUNTING_PERIOD BETWEEN 1 AND 12
A.BUSINESS_UNIT='M04'
(
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
13
Appropriate Index
LEDGER
FISCAL_YEAR
ACCOUNTING_PERIOD
BUSINESS_UNIT
ACCOUNT
PRODUCT
DEPTID
CURRENCY_CD
STATISTICS_CODE
single value
single value
range of values
one value
some values/ranges
equi-joined to L1
some values
one value
one value
grouped by L1.TREE_NODE_NUM, DEPTID
14
Appropriate Index
FISCAL_YEAR
DEPTID
LEDGER
BUSINESS_UNIT
PRODUCT
ACCOUNT
CURRENCY_CD
STATISTICS_CODE
ACCOUNTING_PERIOD
single value
some values
single value
single value
equi-joined to L1
some values/ranges
single value
single value
range of values
grouped by L1.TREE_NODE_NUM, DEPTID
15
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
PSTREESELECTnn L
PS_LEDGER
PSTREESELECTnn L1
16
If using Oracle Rule Based
Optimiser
• Rename PSTREESELECTnn to
PSTREESELECTnn_TBL
CREATE VIEW PSTREESELECTnn
AS SELECT /*+ALL_ROWS*/ * FROM PSTREESELECTnn_TBL
17
If using Oracle 8.x
• The Cost Based Optimiser in invoked by
– Parallelism
– Partitioning
18
Non-Ledger queries
• Query is defined in PS/Query
19
nVision
•
•
•
•
Performance Options
Match Indexes to Analysis Criteria
Oracle: choice of optimiser
non-GL queries are different
20
nVision Performance Tuning
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk
21