Home » RDBMS Server » Performance Tuning » Query Takes More time To Run
Query Takes More time To Run [message #197775] Thu, 12 October 2006 11:18 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,
This query takes more time (more than 10 min )in our Production DB,it will update
just few rows say around 100, any clues or better way i can write this.
this query is in a Proc which runs every 4 hrs as a DBMS_JOB.

UPDATE alrt_ntfy
   SET snd_ts = sysdate
 WHERE alrt_ntfy.alrt_ntfy_id IN (
          SELECT  alrt_ntfy.alrt_ntfy_id
                     FROM alrt_ntfy, pymt_alrt_txn, alrt, txn, ext_bat_view
                    WHERE alrt_ntfy.alrt_ntfy_id = pymt_alrt_txn.alrt_ntfy_id
                      AND alrt_ntfy.alrt_id = alrt.alrt_id
                      AND pymt_alrt_txn.txn_id = txn.txn_id
                      AND txn.bat_id = ext_bat_view.bat_id
                      AND alrt.usr_id = ext_bat_view.usr_id
                      AND alrt.alrt_type = 1
                      AND alrt_ntfy.snd_ts IS NULL)

Explain Plan
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

UPDATE STATEMENT Optimizer Mode=CHOOSE		2  	 	2658  	 	      	             	 
  UPDATE	R1APP.ALRT_NTFY	  	 	 	 	      	             	 
    NESTED LOOPS		2  	52  	2658  	 	      	             	 
      VIEW	SYS.VW_NSO_1	2  	26  	2653  	 	      	             	 
        SORT UNIQUE		2  	344  	 	 	      	             	 
          CONCATENATION		  	 	 	 	      	             	 
            NESTED LOOPS		566  	60 K	1374  	 	      	             	 
              NESTED LOOPS		566  	53 K	242  	 	      	             	 
                NESTED LOOPS		6  	852  	2506  	 	      	             	 
                  NESTED LOOPS		1  	152  	2506  	 	      	             	 
                    NESTED LOOPS		176  	11 K	134  	 	      	             	 
                      NESTED LOOPS		176  	2 K	 	 	      	             	 
                        HASH JOIN		176  	4 K	39  	 	      	             	 
                          NESTED LOOPS OUTER		176  	7 K	121  	 	      	             	 
                            NESTED LOOPS		176  	6 K	95  	 	      	             	 
                              HASH JOIN		176  	12 K	190  	 	      	             	 
                                TABLE ACCESS FULL	R1APP.USR	26 K	236 K	55  	 	      	             	 
                                HASH JOIN		126  	10 K	210  	 	      	             	 
                                  TABLE ACCESS FULL	R1APP.LOCKBOX_ACL	18 K	202 K	19  	 	      	             	 
                                  HASH JOIN		1  	167  	2507  	 	      	             	 
                                    INDEX FAST FULL SCAN	R1APP.XPK_LOC	1  	5  	 	 	      	             	 
                                    TABLE ACCESS FULL	R1APP.LOCKBOX_CUST	1  	15  	1  	 	      	             	 
                              TABLE ACCESS BY INDEX ROWID	R1APP.USR	26 K	236 K	55  	 	      	             	 
                                INDEX UNIQUE SCAN	R1APP.XPK_USR	1  	 	 	 	      	             	 
                            TABLE ACCESS BY INDEX ROWID	R1APP.USR_ROLE	2 K	18 K	25  	 	      	             	 
                              INDEX RANGE SCAN	R1APP.XIF_USRROL_USRID	2  	 	 	 	      	             	 
                          TABLE ACCESS FULL	R1APP.ALRT	2 K	28 K	19  	 	      	             	 
                        TABLE ACCESS BY INDEX ROWID	R1APP.ALRT_NTFY	176  	2 K	19  	 	      	             	 
                          INDEX RANGE SCAN	R1APP.XIF_ALRNTF_ALRID	  	 	 	 	      	             	 
                      TABLE ACCESS BY INDEX ROWID	R1APP.CUST	6 K	129 K	12  	 	      	             	 
                        INDEX UNIQUE SCAN	R1APP.XPK_CUS	1  	 	 	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	R1APP.LOCKBOX_CUST	1  	10  	 	 	      	             	 
                      INDEX RANGE SCAN	R1APP.XIF_LOCCUS_CUSID	1  	 	 	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	R1APP.BAT	1  	33  	2  	 	      	             	 
                    INDEX RANGE SCAN	R1APP.XAK_BAT_LOCID_BATID	1  	 	1  	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	R1APP.PYMT_ALRT_TXN	51 K	548 K	31  	 	      	             	 
                  INDEX RANGE SCAN	R1APP.XIF_PYMALRTXN_ALRNTFID	4  	 	 	 	      	             	 
              INDEX UNIQUE SCAN	R1APP.XAK_TXN_BATID_TXNID	1  	12  	1  	 	      	             	 
            NESTED LOOPS		1  	172  	2507  	 	      	             	 
              NESTED LOOPS		1  	167  	2507  	 	      	             	 
                NESTED LOOPS		1  	152  	2506  	 	      	             	 
                  NESTED LOOPS		6  	852  	2506  	 	      	             	 
                    NESTED LOOPS		566  	60 K	1374  	 	      	             	 
                      HASH JOIN		566  	53 K	242  	 	      	             	 
                        HASH JOIN		126  	10 K	210  	 	      	             	 
                          HASH JOIN		176  	12 K	190  	 	      	             	 
                            HASH JOIN		176  	11 K	134  	 	      	             	 
                              HASH JOIN OUTER		176  	7 K	121  	 	      	             	 
                                HASH JOIN		176  	6 K	95  	 	      	             	 
                                  HASH JOIN		176  	4 K	39  	 	      	             	 
                                    TABLE ACCESS FULL	R1APP.ALRT_NTFY	176  	2 K	19  	 	      	             	 
                                    TABLE ACCESS FULL	R1APP.ALRT	2 K	28 K	19  	 	      	             	 
                                  TABLE ACCESS FULL	R1APP.USR	26 K	236 K	55  	 	      	             	 
                                TABLE ACCESS FULL	R1APP.USR_ROLE	2 K	18 K	25  	 	      	             	 
                              TABLE ACCESS FULL	R1APP.CUST	6 K	129 K	12  	 	      	             	 
                            TABLE ACCESS FULL	R1APP.USR	26 K	236 K	55  	 	      	             	 
                          TABLE ACCESS FULL	R1APP.LOCKBOX_ACL	18 K	202 K	19  	 	      	             	 
                        TABLE ACCESS FULL	R1APP.PYMT_ALRT_TXN	51 K	548 K	31  	 	      	             	 
                      TABLE ACCESS BY INDEX ROWID	R1APP.TXN	1  	12  	2  	 	      	             	 
                        INDEX UNIQUE SCAN	R1APP.XPK_TXN	1  	 	1  	 	      	             	 
                    TABLE ACCESS BY INDEX ROWID	R1APP.BAT	1  	33  	2  	 	      	             	 
                      INDEX UNIQUE SCAN	R1APP.XPK_BAT	1  	 	1  	 	      	             	 
                  INDEX UNIQUE SCAN	R1APP.XAK_LOCCUS_LOCID_CUSID	1  	10  	 	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	R1APP.LOCKBOX_CUST	1  	15  	1  	 	      	             	 
                  INDEX UNIQUE SCAN	R1APP.XAK_LOCCUS_LOCID_CUSID	1  	 	 	 	      	             	 
              INDEX UNIQUE SCAN	R1APP.XPK_LOC	1  	5  	 	 	      	             	 
      INDEX UNIQUE SCAN	R1APP.XPK_ALRNTF	1  	13  	 	 	      	             	 


Table Descriptions and Count
SQL> desc alrt_ntfy
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ALRT_NTFY_ID                              NOT NULL NUMBER
 ALRT_ID                                   NOT NULL NUMBER
 SND_TS                                             DATE
 CRE_DT                                    NOT NULL DATE
 ALRT_NTFY_DT                              NOT NULL DATE
 LAST_UPDT_TS                              NOT NULL DATE
 LAST_UPDT_USR_ID                          NOT NULL NUMBER
 TOT_RSLTS_NB                              NOT NULL NUMBER

SQL> desc pymt_alrt_txn
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ALRT_NTFY_ID                              NOT NULL NUMBER
 TXN_ID                                    NOT NULL NUMBER
 PYMT_ALRT_TXN_ID                          NOT NULL NUMBER
 CRE_DT                                    NOT NULL DATE
 LAST_UPDT_TS                              NOT NULL DATE
 LAST_UPDT_USR_ID                          NOT NULL NUMBER

SQL> desc txn
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TXN_ID                                    NOT NULL NUMBER(10)
 PROC_TIME_DT                              NOT NULL DATE
 TXN_SEQ_NB                                         NUMBER(5)
 BAT_ID                                             NUMBER(10)
 GP_NB                                              NUMBER(10)
 LAST_UPDT_TS                                       DATE
 LAST_UPDT_USR_ID                                   NUMBER(10)
 TXN_TYPE_ID                               NOT NULL NUMBER(10)
 OVNT_WLK_TX                                        CHAR(1)
 TXN_ARC_IN                                NOT NULL NUMBER(1)
 TXN_ARC_DT                                         DATE
 TXN_SHRT_TERM_IN                          NOT NULL NUMBER(1)
 ZIP_CD                                             VARCHAR2(10)
 TXN_PROC_SEQ_NB                                    NUMBER
 PROC_BAT_ID                               NOT NULL NUMBER
 DDA_ID                                             NUMBER
 PROC_DT                                   NOT NULL DATE
 ASSCN_IN                                           NUMBER(1)
 ASSCN_KEY                                          VARCHAR2(100)
 ASSCN_RMIT_NM                                      NVARCHAR2(50)
 ASSCN_CRCY_ID                                      NUMBER
 ASSCN_AM                                           NUMBER
 ASSCN_STS_CD                                       CHAR(1)
 SUPP_DATA_ENTRY_CD                                 CHAR(1)

SQL> desc alrt
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ALRT_ID                                   NOT NULL NUMBER(10)
 SRCH_CRI                                  NOT NULL VARCHAR2(2000)
 CRE_DT                                    NOT NULL DATE
 LAST_UPDT_USR_ID                                   NUMBER(10)
 USR_ID                                    NOT NULL NUMBER(10)
 LAST_UPDT_TS                                       DATE
 FRQ                                                NUMBER(10)
 ALRT_NM                                            VARCHAR2(35)
 STS_CD                                             CHAR(1)
 ALRT_TYPE                                 NOT NULL NUMBER(1)


CREATE OR REPLACE VIEW EXT_BAT_VIEW
(BAT_ID, BAT_NB, BAT_MODE_NB, LOCKBOX_ID, PROC_DT, 
 CRE_DT, CR_DT, LAST_UPDT_TS, LAST_UPDT_USR_ID, RPT_BAT_NB, 
 USR_ID, BAT_ARC_IN, CNTRCD_DEP_DT, BAT_SHRT_TERM_IN)
AS 
SELECT BAT.BAT_ID,
   BAT.BAT_NB,
   BAT.BAT_MODE_NB,
   BAT.LOCKBOX_ID,
   BAT.PROC_DT,
   BAT.CRE_DT,
   BAT.CR_DT,
   BAT.LAST_UPDT_TS,
   BAT.LAST_UPDT_USR_ID,
   BAT.RPT_BAT_NB,
   USR.USR_ID,
   BAT.BAT_ARC_IN,
   BAT.CNTRCD_DEP_DT,
   BAT.BAT_SHRT_TERM_IN
   FROM bat
      JOIN lockbox_cust lc ON lc.LOCKBOX_ID = bat.LOCKBOX_ID
      JOIN cust ON cust.cust_id = lc.cust_id
      JOIN usr ON usr.cust_id = cust.cust_id
      JOIN usr_lockbox ulb ON ulb.usr_id = usr.usr_id AND ulb.LOCKBOX_ID = BAT.LOCKBOX_ID
      LEFT JOIN usr_role on usr_role.usr_id = usr.usr_id AND usr_role.role_id = 12
   WHERE IS_VIEWABLE(BAT.CR_DT,
      BAT.PROC_DT,
      CUST.DATA_VIEW_DU,
      CUST.INTRADAY_SRV_NB,
      CUST.INTRADAY_STRT_NB,
	  CUST.INTRADAY_STRT_TM_ZON_ID,
      CUST.LONG_TRM_SRV_IN,
      CUST.ARC_EFF_DT,
      BAT.BAT_ARC_IN,
      BAT.BAT_SHRT_TERM_IN,
      USR_ROLE.role_id) = 1
/


SQL> select count(*) from alrt_ntfy;

  COUNT(*)
----------
     18283

SQL> select count(*) from pymt_alrt_txn;

  COUNT(*)
----------
     51099

SQL> select count(*) from txn;        

  COUNT(*)
----------
  40713596

SQL> select count(*) from alrt;

  COUNT(*)
----------
      7374

SQL>  select count(*) from usr;

  COUNT(*)
----------
     27143
      
 


Thanks for the Support.


[Updated on: Thu, 12 October 2006 11:19]

Report message to a moderator

Re: Query Takes More time To Run [message #197777 is a reply to message #197775] Thu, 12 October 2006 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
FROM bat
JOIN lockbox_cust lc ON lc.LOCKBOX_ID = bat.LOCKBOX_ID
JOIN cust ON cust.cust_id = lc.cust_id
JOIN usr ON usr.cust_id = cust.cust_id
JOIN usr_lockbox ulb ON ulb.usr_id = usr.usr_id AND ulb.LOCKBOX_ID = BAT.LOCKBOX_ID
LEFT JOIN usr_role on usr_role.usr_id = usr.usr_id AND usr_role.role_id = 12

Why do you have SIX tables in the FROM clause when only two tables
contribute columns to the SELECT clause?
I suspect that is you eliminate lockbox_cust, cust, usr_lockbox &
usr_role out of the FROM clause & subbordinate them into the WHERE
clause performance will improve.
Re: Query Takes More time To Run [message #197793 is a reply to message #197777] Thu, 12 October 2006 13:57 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks Anacedent for prompt reply,

Just confirmed with other developers we need that join condition because to get CUST id we need join on lockbox_cust with bat table and to get the user we need join with cust and lockbox_cust and on cust and user.


If any other changes you people recommend / notice on main Update statement please let me know.


Thanks
Re: Query Takes More time To Run [message #197798 is a reply to message #197775] Thu, 12 October 2006 14:39 Go to previous message
BlackSwan
Messages: 25047
Registered: January 2009
Location: SoCal
Senior Member
>Just confirmed with other developers we need that join condition because to get CUST id we need join on lockbox_cust with bat table and to get the user we need join with cust and lockbox_cust and on cust and user.
--------------------------------
The requirements above can be accomplished by using EXISTS within
the WHERE clause!
I am willing to give you big odds that if/when you subordinate
those 4 tables which return ZERO data into the WHERE clause,
you'll get a VERY noticable performance increase!
Previous Topic: Hint Index
Next Topic: Differences in query execution between two similar environments
Goto Forum:
  


Current Time: Wed Dec 07 14:21:59 CST 2016

Total time taken to generate the page: 0.10973 seconds