Home » RDBMS Server » Performance Tuning » Avoiding FTS, Any better Way to write this
| Avoiding FTS, Any better Way to write this [message #210400] |
Wed, 20 December 2006 10:54  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi,
I am getting couple of FTS, is there any better way to write this or tune this query.
This is still in DEVelopment so number of rows are less i am afriad like once it goes in Production this might effect the performance, Currently in DEV it takes 2 seconds to do this.
These are the tables and count(*) which are undergoing FTS
SQL> select count(*) from inv_opn_file;
COUNT(*)
----------
7840
SQL> select count(*) from inv_sts;
COUNT(*)
----------
6
SQL> select count(*) from inv;
COUNT(*)
----------
81
SQL> select count(*) from LOCKBOX_DDA;
COUNT(*)
----------
6111
SQL> select count(*) from rmit;
COUNT(*)
----------
437
SQL> set autotrace traceonly
SQL> SELECT
(SBSD.SBSD_NB_TX ||'-'||SBSD.SBSD_NM) AS SUBSIDIARY,
INV_OPN_FILE.INV_NB_TX,
INV_OPN_FILE.INV_AM,
INV_OPN_FILE.CRE_DT,
INV_OPN_FILE.INV_DUE_DT,
INV_OPN_FILE.INV_ISSU_DT,
NVL(INV_STS.INV_STS_NM,'OPEN') AS STATUS,
T.PAYMENT AS PAYMENT,
REMITTER.REMITTERNAME,
INV_OPN_FILE.INV_ID,
INV_OPN_FILE.RMIT_CUST_ID_TX,
INV_OPN_FILE.INV_OPN_FILE_ID
FROM
INV_OPN_FILE,
SBSD,
INV,
INV_STS ,
CUST ,
(SELECT T.INV_ID, STRING_CONCAT(PYMT.PYMT_NB_TX) AS PAYMENT
FROM (
SELECT INV_STS_HIST.INV_ID AS INV_ID , MAX(INV_STS_HIST.TXN_ID) AS TXN_ID
FROM INV_STS_HIST ,INV_OPN_FILE , SBSD ,CUST
WHERE
INV_OPN_FILE.INV_ID = INV_STS_HIST.INV_ID
AND INV_OPN_FILE.SBSD_ID = SBSD.SBSD_ID
AND SBSD.CUST_ID = CUST.PARNT_CUST_ID
AND CUST.CUST_ID = 6831
GROUP BY INV_STS_HIST.INV_ID ) T, PYMT
WHERE T.TXN_ID = PYMT.TXN_ID
GROUP BY T.INV_ID) T,
(SELECT INV_OPN_FILE.INV_OPN_FILE_ID,
(CASE WHEN COUNT(INV_OPN_FILE.INV_OPN_FILE_ID) = 1 THEN MAX(RMIT.RMIT_NM)
WHEN COUNT(INV_OPN_FILE.INV_OPN_FILE_ID) > 1 THEN 'MULTIPLE REMITTERS FOUND'
ELSE ''
END) AS REMITTERNAME
FROM INV_OPN_FILE , RMIT, LOCKBOX_CUST, LOCKBOX_DDA , CUST, SBSD
WHERE INV_OPN_FILE.RMIT_CUST_ID_TX = RMIT.RMIT_CUST_ID_TX
AND RMIT.LOCKBOX_DDA_ID = LOCKBOX_DDA.LOCKBOX_DDA_ID
AND LOCKBOX_CUST.LOCKBOX_ID = LOCKBOX_DDA.LOCKBOX_ID
AND LOCKBOX_CUST.CUST_ID = CUST.CUST_ID
AND INV_OPN_FILE.SBSD_ID = SBSD.SBSD_ID
AND SBSD.CUST_ID = CUST.PARNT_CUST_ID
AND CUST.CUST_ID = 6831
GROUP BY INV_OPN_FILE.INV_OPN_FILE_ID
) REMITTER
WHERE
INV_OPN_FILE.SBSD_ID = SBSD.SBSD_ID
AND INV_OPN_FILE.INV_OPN_FILE_ID = REMITTER.INV_OPN_FILE_ID(+)
AND INV_OPN_FILE.INV_ID = INV.INV_ID (+)
AND INV.INV_STS_ID = INV_STS.INV_STS_ID (+)
AND SBSD.CUST_ID = CUST.PARNT_CUST_ID
AND INV_OPN_FILE.INV_ID = T.INV_ID (+)
AND CUST.CUST_ID = 6831
ORDER BY
SUBSIDIARY,
inv_opn_file.CRE_DT desc ,
inv_opn_file.INV_AM desc ,
inv_opn_file.INV_DUE_DT desc ,
inv_opn_file.INV_ISSU_DT desc ,
inv_opn_file.INV_NB_TX desc ,
STATUS desc ,
PAYMENT desc ,
REMITTERNAME desc ;
2683 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=61 Card=44 Bytes=949
96)
1 0 SORT (ORDER BY) (Cost=61 Card=44 Bytes=94996)
2 1 HASH JOIN (OUTER) (Cost=44 Card=44 Bytes=94996)
3 2 HASH JOIN (OUTER) (Cost=35 Card=44 Bytes=6732)
4 3 HASH JOIN (OUTER) (Cost=32 Card=44 Bytes=6204)
5 4 HASH JOIN (OUTER) (Cost=29 Card=44 Bytes=5852)
6 5 HASH JOIN (Cost=9 Card=44 Bytes=3388)
7 6 NESTED LOOPS (Cost=3 Card=1 Bytes=30)
8 7 TABLE ACCESS (BY INDEX ROWID) OF 'CUST' (Cos
t=2 Card=1 Bytes=6)
9 8 INDEX (UNIQUE SCAN) OF 'XPK_CUS' (UNIQUE)
(Cost=1 Card=1)
10 7 TABLE ACCESS (BY INDEX ROWID) OF 'SBSD' (Cos
t=1 Card=1 Bytes=24)
11 10 INDEX (RANGE SCAN) OF 'XIF_SBS_CUSID' (NON
-UNIQUE)
12 6 TABLE ACCESS (FULL) OF 'INV_OPN_FILE' (Cost=5
Card=2703 Bytes=127041)
13 5 VIEW (Cost=19 Card=1 Bytes=56)
14 13 SORT (GROUP BY) (Cost=19 Card=1 Bytes=68)
15 14 HASH JOIN (Cost=17 Card=1 Bytes=68)
16 15 NESTED LOOPS (Cost=11 Card=1 Bytes=57)
17 16 HASH JOIN (Cost=11 Card=32 Bytes=1536)
18 17 MERGE JOIN (CARTESIAN) (Cost=6 Card=32
Bytes=1216)
19 18 NESTED LOOPS (Cost=3 Card=1 Bytes=13
)
20 19 TABLE ACCESS (BY INDEX ROWID) OF '
CUST' (Cost=2 Card=1 Bytes=6)
21 20 INDEX (UNIQUE SCAN) OF 'XPK_CUS'
(UNIQUE) (Cost=1 Card=1)
22 19 TABLE ACCESS (BY INDEX ROWID) OF '
SBSD' (Cost=1 Card=1 Bytes=7)
23 22 INDEX (RANGE SCAN) OF 'XIF_SBS_C
USID' (NON-UNIQUE)
24 18 BUFFER (SORT) (Cost=5 Card=51 Bytes=
1275)
25 24 TABLE ACCESS (FULL) OF 'RMIT' (Cos
t=3 Card=51 Bytes=1275)
26 17 TABLE ACCESS (FULL) OF 'LOCKBOX_DDA' (
Cost=4 Card=6105 Bytes=61050)
27 16 INDEX (UNIQUE SCAN) OF 'XAK_LOCCUS_LOCID
_CUSID' (UNIQUE)
28 15 TABLE ACCESS (FULL) OF 'INV_OPN_FILE' (Cos
t=5 Card=2703 Bytes=29733)
29 4 TABLE ACCESS (FULL) OF 'INV' (Cost=2 Card=66 Bytes
=528)
30 3 TABLE ACCESS (FULL) OF 'INV_STS' (Cost=2 Card=6 Byte
s=72)
31 2 VIEW (Cost=9 Card=1 Bytes=2006)
32 31 SORT (GROUP BY) (Cost=9 Card=1 Bytes=32)
33 32 NESTED LOOPS (Cost=9 Card=1 Bytes=32)
34 33 VIEW (Cost=8 Card=1 Bytes=17)
35 34 SORT (GROUP BY) (Cost=8 Card=1 Bytes=27)
36 35 TABLE ACCESS (BY INDEX ROWID) OF 'INV_STS_HI
ST' (Cost=1 Card=2 Bytes=20)
37 36 NESTED LOOPS (Cost=6 Card=1 Bytes=27)
38 37 NESTED LOOPS (Cost=5 Card=1 Bytes=17)
39 38 NESTED LOOPS (Cost=3 Card=1 Bytes=13)
40 39 TABLE ACCESS (BY INDEX ROWID) OF 'CU
ST' (Cost=2 Card=1 Bytes=6)
41 40 INDEX (UNIQUE SCAN) OF 'XPK_CUS' (
UNIQUE) (Cost=1 Card=1)
42 39 TABLE ACCESS (BY INDEX ROWID) OF 'SB
SD' (Cost=1 Card=1 Bytes=7)
43 42 INDEX (RANGE SCAN) OF 'XIF_SBS_CUS
ID' (NON-UNIQUE)
44 38 TABLE ACCESS (BY INDEX ROWID) OF 'INV_
OPN_FILE' (Cost=2 Card=1 Bytes=4)
45 44 INDEX (FULL SCAN) OF 'XIF_INVOPNFIL_
INVID' (NON-UNIQUE) (Cost=1 Card=8)
46 37 INDEX (RANGE SCAN) OF 'XIF_INVSTSHIS_INV
ID' (NON-UNIQUE)
47 33 INDEX (RANGE SCAN) OF 'XIE_PYM_TXNID_PYMNBTX' (N
ON-UNIQUE) (Cost=1 Card=1 Bytes=15)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
976 consistent gets
379 physical reads
0 redo size
89820 bytes sent via SQL*Net to client
1911 bytes received via SQL*Net from client
180 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
2683 rows processed
Thanks.
|
|
|
|
| Re: Avoiding FTS, Any better Way to write this [message #210450 is a reply to message #210400] |
Wed, 20 December 2006 19:51  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The development plan is no indication of what will happen in Prod. Very small tables are often full scanned. If the entire table can be stored in a single block, then why would you want to read an index to locate that block? It's just more IO.
Run the Explain Plan on Production, or export the Production table statistics to development with DBMS_STATS to get a more indicative plan.
Ross Leishman
|
|
|
|
| Re: Avoiding FTS, Any better Way to write this [message #210633 is a reply to message #210450] |
Thu, 21 December 2006 10:47 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Ross
Yes you arr right, plans are different, i tried to get it from PROD it's better but no rows since it is still in dev no data in PROD it's new change any how for your view i am pasting the plan from PROD, still i table is getting FTS and count(*) from that FTS table gives me 3819 rows
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 23
SORT ORDER BY 1 6 K 23
HASH JOIN OUTER 1 6 K 20
HASH JOIN OUTER 1 4 K 15
NESTED LOOPS OUTER 1 4 K 2
NESTED LOOPS OUTER 1 4 K 2
NESTED LOOPS 1 4 K 2
NESTED LOOPS 1 2 K 2
TABLE ACCESS BY INDEX ROWID R1APP.CUST 1 8 2
INDEX UNIQUE SCAN R1APP.XPK_CUS 1 1
TABLE ACCESS BY INDEX ROWID R1APP.SBSD 1 2 K
INDEX RANGE SCAN R1APP.XIF_SBS_CUSID 1
TABLE ACCESS BY INDEX ROWID R1APP.INV_OPN_FILE 1 2 K
INDEX RANGE SCAN R1APP.XIF_INVOPNFIL_SBSID 1
TABLE ACCESS BY INDEX ROWID R1APP.INV 1 26
INDEX UNIQUE SCAN R1APP.XPK_INV 1
TABLE ACCESS BY INDEX ROWID R1APP.INV_STS 1 12
INDEX UNIQUE SCAN R1APP.XPK_INVSTS 1
VIEW 1 65 12
SORT GROUP BY 1 200 12
NESTED LOOPS 1 200 9
NESTED LOOPS 1 190 9
NESTED LOOPS 1 180 8
MERGE JOIN CARTESIAN 1 52 8
NESTED LOOPS 1 34 2
TABLE ACCESS BY INDEX ROWID R1APP.CUST 1 8 2
INDEX UNIQUE SCAN R1APP.XPK_CUS 1 1
TABLE ACCESS BY INDEX ROWID R1APP.SBSD 1 26
INDEX RANGE SCAN R1APP.XIF_SBS_CUSID 1
BUFFER SORT 7 126 8
TABLE ACCESS FULL R1APP.RMIT 7 126 6
TABLE ACCESS BY INDEX ROWID R1APP.INV_OPN_FILE 1 128
INDEX RANGE SCAN R1APP.XIF_INVOPNFIL_SBSID 1
TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX_DDA 1 10 1
INDEX UNIQUE SCAN R1APP.XPK_LOCDD 1
INDEX UNIQUE SCAN R1APP.XAK_LOCCUS_LOCID_CUSID 1 10
VIEW 1 1 K 4
SORT GROUP BY 1 39 4
NESTED LOOPS 1 39 4
VIEW 1 26 2
SORT GROUP BY 1 94 2
NESTED LOOPS 1 94 2
NESTED LOOPS 1 68 2
NESTED LOOPS 1 42 2
TABLE ACCESS BY INDEX ROWID R1APP.CUST 1 8 2
INDEX UNIQUE SCAN R1APP.XPK_CUS 1 1
TABLE ACCESS BY INDEX ROWID R1APP.INV_STS_HIST 1 26
INDEX FULL SCAN R1APP.XIF_INVSTSHIS_INVID 1
TABLE ACCESS BY INDEX ROWID R1APP.INV_OPN_FILE 1 26
INDEX RANGE SCAN R1APP.XIF_INVOPNFIL_INVID 1
TABLE ACCESS BY INDEX ROWID R1APP.SBSD 1 26
INDEX UNIQUE SCAN R1APP.XPK_SBS 1
INDEX RANGE SCAN R1APP.XIE_PYM_TXNID_PYMNBTX 1 13 2
Thanks Again for your response.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 16 21:23:55 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
