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 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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.
Re: Avoiding FTS, Any better Way to write this [message #210703 is a reply to message #210633] Thu, 21 December 2006 20:18 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you are talking about the RMIT table, the CBO thinks that table has 7 rows. If it really has 3819, then gather fresh statistics.

Ross Leishman
Previous Topic: Which One is better... VARCHAR or Number
Next Topic: Oracle Database Slow
Goto Forum:
  


Current Time: Fri Dec 09 04:10:28 CST 2016

Total time taken to generate the page: 0.12517 seconds