OraFAQ Forum: Performance Tuning » Time Differnce in Execution with / without Hint

Home » RDBMS Server » Performance Tuning » Time Differnce in Execution with / without Hint

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Time Differnce in Execution with / without Hint [message #251816]

Mon, 16 July 2007 15:01

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 #251893 is a reply to message #251816]

Tue, 17 July 2007 01:57

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You could post the phone book it would be as useful as your current post to help you.

Regards
Michel

Report message to a moderator

Re: Time Differnce in Execution with / without Hint [message #251964 is a reply to message #251893]

Tue, 17 July 2007 10:06

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Thanks Michel,

Instead of telling those words, you could have asked what more information you need to give any suggestion,
We appreciate all members there who is giving precious time, suggestions and tips and helping oracle users,

Thanks Agian Michel for atleast sparing time to look at this request.

Regards

[Updated on: Tue, 17 July 2007 10:13]

Report message to a moderator

Re: Time Differnce in Execution with / without Hint [message #251974 is a reply to message #251816]

Tue, 17 July 2007 10:31

Art Trifonov
Messages: 11
Registered: June 2007
Location: Boston

Junior Member

How many rows does the query return?

Report message to a moderator

Re: Time Differnce in Execution with / without Hint [message #251988 is a reply to message #251964]

Tue, 17 July 2007 10:58

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Do you think that 50 steps plan without any indenting is readable?
Use dbms_xplan package to display it.

With FIRST_ROW Oracle goes to a plan that gives you the first row as fast as possible.
Without it (that is most likely ALL_ROWS) Oracle goes to a plan that gives you ALL the rows as fast as possible but you may wait longer to get the first one.
So FIRST_ROW goes to NESTED LOOPs and ALL_ROWS to HASH JOINs.

Regards
Michel

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

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

Report message to a moderator

Re: Time Differnce in Execution with / without Hint [message #252141 is a reply to message #252062]

Tue, 17 July 2007 23:16

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

I would be surprised if a query that large returned 12K rows in 1 second.

What you are experiencing is that it takes 1 sec to return the FIRST row, not all rows.

To more accurately test the performance difference between 2 SQLs, read this article.

Ross Leishman

Report message to a moderator

Previous Topic:	how to speedup mentioned SQL query
Next Topic:	Deletion on an partition table is very slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jun 24 08:49:43 CDT 2024