Home » RDBMS Server » Performance Tuning » Time Differnce in Execution with / without Hint
Time Differnce in Execution with / without Hint [message #251816] |
Mon, 16 July 2007 15:01 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
faiz_hyd
Messages: 294 Registered: February 2006
|
Senior Member |
|
|
Hi,
This query takes 1 sec with FIRST_ROW Hint and the PLAN also shows that RMIT table is not undergoing FTS,
where as if i remove Hint it takes around 2 min and does a FTS on RMIT table, this table has 150000 rows, not to
big but still time differnce is much more, Any clues what must be wrong, and why using HINT has this much time difference,
Oracle is 9i Rel 2, and STATS are updated.There has been no major change in Data or code, every thing is same,
SELECT /*+ FIRST_ROWS */ p.rmit_nm, r.rmit_cust_id_tx, p.rte_nb_tx, p.acct_nb_tx,
tt.txn_type_desc_tx, p.rmit_dt, p.pymt_nb_tx, p.dol_am, l.lockbox_nb,
b.bat_id, b.bat_nb, t.txn_id, t.txn_seq_nb, p.pymt_id, p.pymt_seq_nb,
site_id, p.crcy_id, b.proc_dt, p.gp_seq_nb, p.int_rte_nb_tx,
sc.setl_ch_nm
FROM pymt_view p,
txn t,
txn_type tt,
ext_bat_view b,
lockbox l,
rmit r,
setl_ch sc
WHERE t.txn_id = p.txn_id(+)
AND tt.txn_type_id = t.txn_type_id
AND p.setl_ch_id = sc.setl_ch_id(+)
AND b.bat_id = t.bat_id
AND b.proc_dt = t.proc_dt AND b.lockbox_id = l.lockbox_id
AND b.usr_id = 41055 -- :p_usr_id
AND b.cr_dt = '16-JUL-2007' -- :p_cr_dt
AND p.rmit_id = r.rmit_id(+)
GROUP BY p.rmit_nm,
r.rmit_cust_id_tx,
p.rte_nb_tx,
p.acct_nb_tx,
tt.txn_type_desc_tx,
p.rmit_dt,
p.pymt_nb_tx,
p.dol_am,
l.lockbox_nb,
b.bat_id,
b.bat_nb,
t.txn_id,
t.txn_seq_nb,
p.pymt_id,
p.pymt_seq_nb,
site_id,
p.crcy_id,
b.proc_dt,
p.payee_nm,
p.gp_seq_nb,
p.int_rte_nb_tx,
sc.setl_ch_nm
ORDER BY b.proc_dt,
l.lockbox_nb,
b.bat_nb,
t.txn_seq_nb,
p.pymt_seq_nb,
p.crcy_id
-- PLAN WITHOUT FTS ,since Hint is used
Plan
SELECT STATEMENT HINT: FIRST_ROWSCost: 124 Bytes: 377 Cardinality: 1
51 SORT GROUP BY Cost: 124 Bytes: 377 Cardinality: 1
50 NESTED LOOPS OUTER Cost: 108 Bytes: 377 Cardinality: 1
47 NESTED LOOPS OUTER Cost: 107 Bytes: 370 Cardinality: 1
44 NESTED LOOPS OUTER Cost: 106 Bytes: 305 Cardinality: 1
34 NESTED LOOPS Cost: 103 Bytes: 181 Cardinality: 1
31 NESTED LOOPS Cost: 102 Bytes: 173 Cardinality: 1
27 NESTED LOOPS Cost: 99 Bytes: 145 Cardinality: 1
24 NESTED LOOPS Cost: 98 Bytes: 131 Cardinality: 1
21 NESTED LOOPS Cost: 20 Bytes: 1,196 Cardinality: 13
18 NESTED LOOPS Cost: 10 Bytes: 410 Cardinality: 5
16 NESTED LOOPS Cost: 10 Bytes: 385 Cardinality: 5
13 NESTED LOOPS Cost: 6 Bytes: 124 Cardinality: 2
10 NESTED LOOPS OUTER Cost: 4 Bytes: 51 Cardinality: 1
8 NESTED LOOPS Cost: 4 Bytes: 43 Cardinality: 1
5 NESTED LOOPS Cost: 3 Bytes: 20 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID R1APP.USR Cost: 2 Bytes: 10 Cardinality: 1
1 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_USR Cost: 1 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID R1APP.USR Cost: 1 Bytes: 10 Cardinality: 1
3 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_USR Cardinality: 1
7 TABLE ACCESS BY INDEX ROWID R1APP.CUST Cost: 1 Bytes: 23 Cardinality: 1
6 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_CUS Cardinality: 1
9 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_USRROLE_USRIDROLID Bytes: 8 Cardinality: 1
12 TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX_ACL Cost: 2 Bytes: 22 Cardinality: 2
11 INDEX RANGE SCAN UNIQUE R1APP.XAK_LOCACL_USRID_LOCACLID Cost: 1 Cardinality: 2
15 TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX_CUST Cost: 2 Bytes: 45 Cardinality: 3
14 INDEX RANGE SCAN NON-UNIQUE R1APP.XIF_LOCCUS_CUSID Cost: 1 Cardinality: 3
17 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_LOC Bytes: 5 Cardinality: 1
20 TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX_CUST Cost: 2 Bytes: 30 Cardinality: 3
19 INDEX RANGE SCAN NON-UNIQUE R1APP.XIF_LOCCUS_CUSID Cost: 1 Cardinality: 3
23 TABLE ACCESS BY GLOBAL INDEX ROWID R1APP.BAT Cost: 6 Bytes: 39 Cardinality: 1 Partition #: 30
22 INDEX RANGE SCAN UNIQUE R1APP.XAK_BAT_LOCID_BATID Cost: 5 Cardinality: 1
26 TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX Cost: 1 Bytes: 14 Cardinality: 1
25 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_LOC Cardinality: 1
30 PARTITION RANGE ITERATOR Partition #: 34
29 TABLE ACCESS BY LOCAL INDEX ROWID R1APP.TXN Cost: 3 Bytes: 28 Cardinality: 1 Partition #: 34
28 INDEX RANGE SCAN NON-UNIQUE R1APP.XIE_TXN_BATID_PRCDT_GPNB_P Cost: 2 Cardinality: 1 Partition #: 34
33 TABLE ACCESS BY INDEX ROWID R1APP.TXN_TYPE Cost: 1 Bytes: 8 Cardinality: 1
32 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_TXNTYP Cardinality: 1
43 VIEW PUSHED PREDICATE R1APP.PYMT_VIEW Cost: 3 Bytes: 124 Cardinality: 1
42 NESTED LOOPS OUTER Cost: 6 Bytes: 124 Cardinality: 1
36 TABLE ACCESS BY GLOBAL INDEX ROWID R1APP.PYMT Cost: 4 Bytes: 88 Cardinality: 1 Partition #: 41
35 INDEX RANGE SCAN NON-UNIQUE R1APP.XIE_PYM_TXNID_SETID Cost: 3 Cardinality: 1
41 VIEW PUSHED PREDICATE R1APP.RMIT_ACTV_VIEW Cost: 2 Bytes: 36 Cardinality: 1
40 NESTED LOOPS OUTER Cost: 2 Bytes: 45 Cardinality: 1
38 TABLE ACCESS BY INDEX ROWID R1APP.RMIT Cost: 2 Bytes: 42 Cardinality: 1
37 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_RMT Cost: 1 Cardinality: 1
39 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_CUSCLS Bytes: 3 Cardinality: 1
46 TABLE ACCESS BY INDEX ROWID R1APP.SETL_CH Cost: 1 Bytes: 65 Cardinality: 1
45 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_SETCH Cardinality: 1
49 TABLE ACCESS BY INDEX ROWID R1APP.RMIT Cost: 1 Bytes: 7 Cardinality: 1
48 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_RMT Cardinality: 1
-- FTS if I removed the Hint
Plan
SELECT STATEMENT CHOOSECost: 124 Bytes: 377 Cardinality: 1
50 SORT GROUP BY Cost: 124 Bytes: 377 Cardinality: 1
49 NESTED LOOPS OUTER Cost: 108 Bytes: 377 Cardinality: 1
46 NESTED LOOPS OUTER Cost: 107 Bytes: 370 Cardinality: 1
43 NESTED LOOPS OUTER Cost: 106 Bytes: 305 Cardinality: 1
34 NESTED LOOPS Cost: 103 Bytes: 181 Cardinality: 1
31 NESTED LOOPS Cost: 102 Bytes: 173 Cardinality: 1
27 NESTED LOOPS Cost: 99 Bytes: 145 Cardinality: 1
24 NESTED LOOPS Cost: 98 Bytes: 131 Cardinality: 1
21 NESTED LOOPS Cost: 20 Bytes: 1,196 Cardinality: 13
18 NESTED LOOPS Cost: 10 Bytes: 410 Cardinality: 5
16 NESTED LOOPS Cost: 10 Bytes: 385 Cardinality: 5
13 NESTED LOOPS Cost: 6 Bytes: 124 Cardinality: 2
10 NESTED LOOPS OUTER Cost: 4 Bytes: 51 Cardinality: 1
8 NESTED LOOPS Cost: 4 Bytes: 43 Cardinality: 1
5 NESTED LOOPS Cost: 3 Bytes: 20 Cardinality: 1
2 TABLE ACCESS BY INDEX ROWID R1APP.USR Cost: 2 Bytes: 10 Cardinality: 1
1 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_USR Cost: 1 Cardinality: 1
4 TABLE ACCESS BY INDEX ROWID R1APP.USR Cost: 1 Bytes: 10 Cardinality: 1
3 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_USR Cardinality: 1
7 TABLE ACCESS BY INDEX ROWID R1APP.CUST Cost: 1 Bytes: 23 Cardinality: 1
6 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_CUS Cardinality: 1
9 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_USRROLE_USRIDROLID Bytes: 8 Cardinality: 1
12 TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX_ACL Cost: 2 Bytes: 22 Cardinality: 2
11 INDEX RANGE SCAN UNIQUE R1APP.XAK_LOCACL_USRID_LOCACLID Cost: 1 Cardinality: 2
15 TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX_CUST Cost: 2 Bytes: 45 Cardinality: 3
14 INDEX RANGE SCAN NON-UNIQUE R1APP.XIF_LOCCUS_CUSID Cost: 1 Cardinality: 3
17 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_LOC Bytes: 5 Cardinality: 1
20 TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX_CUST Cost: 2 Bytes: 30 Cardinality: 3
19 INDEX RANGE SCAN NON-UNIQUE R1APP.XIF_LOCCUS_CUSID Cost: 1 Cardinality: 3
23 TABLE ACCESS BY GLOBAL INDEX ROWID R1APP.BAT Cost: 6 Bytes: 39 Cardinality: 1 Partition #: 30
22 INDEX RANGE SCAN UNIQUE R1APP.XAK_BAT_LOCID_BATID Cost: 5 Cardinality: 1
26 TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX Cost: 1 Bytes: 14 Cardinality: 1
25 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_LOC Cardinality: 1
30 PARTITION RANGE ITERATOR Partition #: 34
29 TABLE ACCESS BY LOCAL INDEX ROWID R1APP.TXN Cost: 3 Bytes: 28 Cardinality: 1 Partition #: 34
28 INDEX RANGE SCAN NON-UNIQUE R1APP.XIE_TXN_BATID_PRCDT_GPNB_P Cost: 2 Cardinality: 1 Partition #: 34
33 TABLE ACCESS BY INDEX ROWID R1APP.TXN_TYPE Cost: 1 Bytes: 8 Cardinality: 1
32 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_TXNTYP Cardinality: 1
42 VIEW PUSHED PREDICATE R1APP.PYMT_VIEW Cost: 3 Bytes: 124 Cardinality: 1
41 HASH JOIN OUTER Cost: 206 Bytes: 124 Cardinality: 1
36 TABLE ACCESS BY GLOBAL INDEX ROWID R1APP.PYMT Cost: 4 Bytes: 88 Cardinality: 1 Partition #: 41
35 INDEX RANGE SCAN NON-UNIQUE R1APP.XIE_PYM_TXNID_SETID Cost: 3 Cardinality: 1
40 VIEW R1APP.RMIT_ACTV_VIEW Cost: 201 Bytes: 1,354,320 Cardinality: 37,620
39 NESTED LOOPS OUTER Cost: 201 Bytes: 1,692,900 Cardinality: 37,620
37 TABLE ACCESS FULL R1APP.RMIT Cost: 201 Bytes: 1,580,040 Cardinality: 37,620
38 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_CUSCLS Bytes: 3 Cardinality: 1
45 TABLE ACCESS BY INDEX ROWID R1APP.SETL_CH Cost: 1 Bytes: 65 Cardinality: 1
44 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_SETCH Cardinality: 1
48 TABLE ACCESS BY INDEX ROWID R1APP.RMIT Cost: 1 Bytes: 7 Cardinality: 1
47 INDEX UNIQUE SCAN UNIQUE R1APP.XPK_RMT Cardinality: 1
Thanks
[Updated on: Tue, 17 July 2007 10:03] Report message to a moderator
|
|
|
|
|
|
|
Re: Time Differnce in Execution with / without Hint [message #252062 is a reply to message #251974] |
Tue, 17 July 2007 14:14 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
faiz_hyd
Messages: 294 Registered: February 2006
|
Senior Member |
|
|
Thanks for follow up,
Query will return 12574 rows.
Michel i agree that First rows will get first rows as fast as possible ,but here it's getting all 12574 rows in 1 sec itself,
still not getting for getting these many rows the time difference is 1 sec - around 3(+) minutes,
Any other suggestion to look into this query , any how we have added this Hint in Production and it's running in same 1 sec,
but i was not able to get exact solution to avoid this FTS.
Thanks
|
|
|
|
Goto Forum:
Current Time: Thu Sep 12 08:13:43 CDT 2024
|