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  |
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 #197793 is a reply to message #197777] |
Thu, 12 October 2006 13:57  |
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 |
 |
BlackSwan
Messages: 26766
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!
|
|
|
|
Goto Forum:
Current Time: Wed Nov 27 05:50:19 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
