Home » RDBMS Server » Performance Tuning » Can We Reduced the Time
| Can We Reduced the Time [message #170867] |
Fri, 05 May 2006 10:53  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hello,
The query and Plan are from Production, STATS are updated, it takes around 35-40 seconds to run this can i reduced it,
it will get 1 row.Indexes are there and are used also,
on the first where clause upper(trim(pymt_nb_tx)='444444' is a varchar column and a composite index like
pymt(txn_id,pymt_nb_tx) is it because of this the index is not used since it is the second column but later in the query pymt.txn_id(+) is used , if i shift the this line on top of where clause before upper(trim(pymt_nb_tx)='444444' , will it help or index will use.
The requirement is to get it down 3-5 seconds.
SELECT *
FROM (SELECT a.*,
ROWNUM rnum
FROM (SELECT DISTINCT txn.txn_id,
txn.txn_seq_nb,
txn.bat_id,
txn.txn_type_id,
txn.gp_nb,
pymt.pymt_id,
pymt.pymt_nb_tx,
NVL(pymt.dol_am, 0) dol_am,
pymt.rmit_nm,
bat.cr_dt,
bat.proc_dt,
bat.bat_nb,
bat.lockbox_id,
lockbox.lockbox_nb,
txn_type.txn_type_desc_tx,
note.note_id,
note.asgn_to_nb,
note.excp_cd,
pymt.rte_nb_tx,
pymt.acct_nb_tx,
note_txt.note_id note_txt_note_id,
note.note_sts_in,
site.site_nm,
site.site_cd,
txn.txn_arc_in,
txn.txn_shrt_term_in,
pymt.rtn_id,
dda.dda_nb
FROM txn,
pymt,
lockbox,
txn_type,
note,
note_txt,
site,
dda,
ext_bat_view bat
WHERE UPPER(TRIM(pymt.pymt_nb_tx)) = '454562' -- :p1 is a bind variable
AND bat.usr_id = 6578 --P2 is bind variable
AND txn.bat_id = bat.bat_id
AND bat.lockbox_id = lockbox.lockbox_id
AND txn.txn_id = pymt.txn_id (+)
AND txn.txn_type_id = txn_type.txn_type_id
AND txn.txn_id = note.txn_id (+)
AND note.note_id = note_txt.note_id (+)
AND lockbox.site_id = site.site_id
AND txn.dda_id = dda.dda_id (+)
ORDER BY bat.cr_dt DESC,
lockbox.lockbox_nb,
site.site_cd,
bat.bat_nb,
txn.txn_seq_nb) a
WHERE ROWNUM <= 1000)
WHERE rnum >= 1
Plan
SELECT STATEMENT CHOOSE Cost : 65 Bytes : 421 Cardinality : 1
53 VIEW R1APP. Cost : 65 Bytes : 421 Cardinality : 1
52 COUNT STOPKEY
51 VIEW R1APP. Cost : 65 Bytes : 408 Cardinality : 1
50 SORT UNIQUE STOPKEY Cost : 62 Bytes : 271 Cardinality : 1
49 TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX_ACL Cost : 2 Bytes : 22 Cardinality : 2
48 NESTED LOOPS Cost : 59 Bytes : 271 Cardinality : 1
46 FILTER
45 NESTED LOOPS OUTER
42 NESTED LOOPS OUTER Cost : 54 Bytes : 210 Cardinality : 1
40 NESTED LOOPS OUTER Cost : 52 Bytes : 205 Cardinality : 1
37 NESTED LOOPS OUTER Cost : 49 Bytes : 186 Cardinality : 1
34 NESTED LOOPS Cost : 48 Bytes : 175 Cardinality : 1
31 NESTED LOOPS Cost : 47 Bytes : 167 Cardinality : 1
28 NESTED LOOPS Cost : 44 Bytes : 135 Cardinality : 1
25 NESTED LOOPS Cost : 43 Bytes : 117 Cardinality : 1
22 NESTED LOOPS Cost : 42 Bytes : 104 Cardinality : 1
19 NESTED LOOPS Cost : 14 Bytes : 469 Cardinality : 7
17 NESTED LOOPS Cost : 14 Bytes : 441 Cardinality : 7
14 NESTED LOOPS Cost : 8 Bytes : 153 Cardinality : 3
11 NESTED LOOPS OUTER Cost : 6 Bytes : 43 Cardinality : 1
8 NESTED LOOPS Cost : 4 Bytes : 35 Cardinality : 1
5 NESTED LOOPS Cost : 3 Bytes : 18 Cardinality : 1
2 TABLE ACCESS BY INDEX ROWID R1APP.USR Cost : 2 Bytes : 9 Cardinality : 1
1 INDEX UNIQUE SCAN R1APP.XPK_USR Cost : 1 Cardinality : 1
4 TABLE ACCESS BY INDEX ROWID R1APP.USR Cost : 1 Bytes : 9 Cardinality : 1
3 INDEX UNIQUE SCAN R1APP.XPK_USR Cardinality : 1
7 TABLE ACCESS BY INDEX ROWID R1APP.CUST Cost : 1 Bytes : 17 Cardinality : 1
6 INDEX UNIQUE SCAN R1APP.XPK_CUS Cardinality : 1
10 TABLE ACCESS BY INDEX ROWID R1APP.USR_ROLE Cost : 2 Bytes : 8 Cardinality : 1
9 INDEX RANGE SCAN R1APP.XIF_USRROL_USRID Cost : 1 Cardinality : 2
13 TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX_CUST Cost : 2 Bytes : 24 Cardinality : 3
12 INDEX RANGE SCAN R1APP.XAK_LOCCUS_CUSID_LOCCUSID Cost : 1 Cardinality : 3
16 TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX_CUST Cost : 2 Bytes : 36 Cardinality : 3
15 INDEX RANGE SCAN R1APP.XAK_LOCCUS_CUSID_LOCCUSID Cost : 1 Cardinality : 3
18 INDEX UNIQUE SCAN R1APP.XPK_LOC Bytes : 4 Cardinality : 1
21 TABLE ACCESS BY INDEX ROWID R1APP.BAT Cost : 4 Bytes : 37 Cardinality : 1
20 INDEX RANGE SCAN R1APP.XAK_BAT_LOCID_BATID Cost : 3 Cardinality : 1
24 TABLE ACCESS BY INDEX ROWID R1APP.LOCKBOX Cost : 1 Bytes : 13 Cardinality : 1
23 INDEX UNIQUE SCAN R1APP.XPK_LOC Cardinality : 1
27 TABLE ACCESS BY INDEX ROWID R1APP.SITE Cost : 1 Bytes : 18 Cardinality : 1
26 INDEX UNIQUE SCAN R1APP.XPK_SIT Cardinality : 1
30 TABLE ACCESS BY INDEX ROWID R1APP.TXN Cost : 3 Bytes : 288 Cardinality : 9
29 INDEX RANGE SCAN R1APP.XAK_TXN_BATID_TXNID Cost : 2 Cardinality : 9
33 TABLE ACCESS BY INDEX ROWID R1APP.TXN_TYPE Cost : 1 Bytes : 8 Cardinality : 1
32 INDEX UNIQUE SCAN R1APP.XPK_TXNTYP Cardinality : 1
36 TABLE ACCESS BY INDEX ROWID R1APP.DDA Cost : 1 Bytes : 11 Cardinality : 1
35 INDEX UNIQUE SCAN R1APP.XPK_DD Cardinality : 1
39 TABLE ACCESS BY INDEX ROWID R1APP.NOTE Cost : 3 Bytes : 19 Cardinality : 1
38 INDEX RANGE SCAN R1APP.XAK_NOT_TXNID_NOTID Cost : 2 Cardinality : 1
41 INDEX RANGE SCAN R1APP.XAK_NOTTXT_NOTID_NOTETXTID Cost : 2 Bytes : 5 Cardinality : 1
44 TABLE ACCESS BY INDEX ROWID R1APP.PYMT Cost : 3 Bytes : 50 Cardinality : 1
43 INDEX RANGE SCAN R1APP.XAK_PYM_TXNID_PYMID Cost : 2 Cardinality : 1
47 INDEX RANGE SCAN R1APP.XAK_LOCACL_USRID_LOCACLID Cost : 1 Cardinality : 2
Any help on this.
Thanks
[Updated on: Fri, 05 May 2006 12:29] Report message to a moderator |
|
|
|
| Re: Can We Reduced the Time [message #172502 is a reply to message #170867] |
Tue, 16 May 2006 21:03  |
krystian.zieja
Messages: 12
Registered: May 2006
Location: Poland
|
Junior Member |
|
|
Did you try creating function based index on pymt_nb_tx – it seems to be good column to limit the number of rows used in joins. Please look at below example, it might help you.
mob@MOB92.WORLD> select index_name , table_name from dba_indexes where table_name = 'B1';
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
B1_OBJECT_NAME B1
B1_OBJECT_TYPE B1
mob@MOB92.WORLD> exec dbms_stats.gather_Table_stats(user,'B1', cascade => TRUE)
PL/SQL procedure successfully completed.
mob@MOB92.WORLD> explain plan for select object_name, object_type, created from b1 where object_name = 'BFG';
Explained.
mob@MOB92.WORLD> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
--------------------
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 54 | 2214 | 55 |
| 1 | TABLE ACCESS BY INDEX ROWID| B1 | 54 | 2214 | 55 |
|* 2 | INDEX RANGE SCAN | B1_OBJECT_NAME | 54 | | 3 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B1"."OBJECT_NAME"='BFG')
Note: cpu costing is off
15 rows selected.
mob@MOB92.WORLD> truncate table plan_table;
Table truncated.
mob@MOB92.WORLD> explain plan for select object_name, object_type, created from b1 where lower(object_name) = 'abc';
Explained.
mob@MOB92.WORLD> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
--------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 400K| 1375 |
|* 1 | TABLE ACCESS FULL | B1 | 10000 | 400K| 1375 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LOWER("B1"."OBJECT_NAME")='abc')
Note: cpu costing is off
14 rows selected.
mob@MOB92.WORLD> drop index b1_object_name;
Index dropped.
mob@MOB92.WORLD> create index b1_object_name_lower on b1(lower(object_name));
Index created.
mob@MOB92.WORLD> explain plan for select object_name, object_type, created from b1 where lower(object_name) = 'abc';
Explained.
mob@MOB92.WORLD> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
--------------------
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 400K| 2 |
| 1 | TABLE ACCESS BY INDEX ROWID| B1 | 10000 | 400K| 2 |
|* 2 | INDEX RANGE SCAN | B1_OBJECT_NAME_LOWER | 4000 | | 1 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(LOWER("B1"."OBJECT_NAME")='abc')
Note: cpu costing is off
15 rows selected.
Best Regards
Krystian Zieja / mob
|
|
|
|
| Re: Can We Reduced the Time [message #172680 is a reply to message #172502] |
Wed, 17 May 2006 11:06 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hello
Thanks for responding,
Yes i created a Function based on upper(column_name)
it didn't helped, I removed the outer Select * and rownum <=1
it took 10 seconds more.
Not getting ideas, still what to do on this to reduce time.
Thanks.
|
|
|
|
| Re: Can We Reduced the Time [message #172875 is a reply to message #170867] |
Thu, 18 May 2006 07:51 |
raji_kb02
Messages: 19
Registered: March 2006
|
Junior Member |
|
|
Hi,
I assumed the txn_type,note_txt, dda are lookup tables. You are joining to this table to get the description. Am I right?
So I replaced the join with the scalar subquery.
Order of the join or where clause doesn't matter as you are using CBO.
If you are interested in first rows you can hint the query by FIRST_ROWS hint.
Regarding function based index you have to create upper(trim(pymt.pymt_nb_tx)) not just upper(pymt.pymt_nb_tx)
>>
The query and Plan are from Production, STATS are updated, it takes around 35-40 seconds to run this can i reduced it,
it will get 1 row
>>
If this query is going to return single row why are you fetching 1000 rows. I didn't understand the reason behind rownum <= 1000 and rnum >= 1. Why outer select?
scott@ORA9.2> create table t as select * from all_objects where rownum <=5;
Table created.
scott@ORA9.2> select rownum rn, owner, object_name from t where rownum < 3;
RN OWNER OBJECT_NAME
---------- ------------------------------ ------------------------------
1 SYS AGGXMLIMP
2 SYS AGGXMLINPUTTYPE
scott@ORA9.2> select * from
2 (select rownum rn, owner, object_name from t where rownum < 3)
3 where rn >=1;
RN OWNER OBJECT_NAME
---------- ------------------------------ ------------------------------
1 SYS AGGXMLIMP
2 SYS AGGXMLINPUTTYPE
Modified query using scalar subquery so that you can avoid outer join on lookup(master) tables
SELECT *
FROM (SELECT a.*, ROWNUM rnum
FROM (SELECT DISTINCT txn.txn_id, txn.txn_seq_nb,txn.bat_id,
txn.txn_type_id, txn.gp_nb,
txn.txn_arc_in,txn.txn_shrt_term_in,
pymt.pymt_id,pymt.pymt_nb_tx,NVL(pymt.dol_am, 0) dol_am,pymt.rmit_nm,
pymt.rte_nb_tx,pymt.acct_nb_tx,pymt.rtn_id,
bat.cr_dt,bat.proc_dt,bat.bat_nb, bat.lockbox_id,
lockbox.lockbox_nb,
note.note_id,note.asgn_to_nb,note.excp_cd,note.note_sts_in,
site.site_nm,site.site_cd,
(select txn_type.txn_type_desc_tx from txn_type where txn_type.txn_type_id = txn.txn_type_id),
(select note_txt.note_id note_txt_note_id from note_txt where note.note_id = note_txt.note_id),
(select dda.dda_nb from dda where txn.dda_id = dda.dda_id)
FROM txn,
pymt,
lockbox,
note,
site,
ext_bat_view bat
WHERE UPPER(TRIM(pymt.pymt_nb_tx)) = '454562' -- :p1 is a bind variable
AND bat.usr_id = 6578 --P2 is bind variable
AND txn.bat_id = bat.bat_id
AND bat.lockbox_id = lockbox.lockbox_id
AND txn.txn_id = pymt.txn_id (+)
AND txn.txn_id = note.txn_id (+)
AND lockbox.site_id = site.site_id
ORDER BY bat.cr_dt DESC,
lockbox.lockbox_nb,
site.site_cd,
bat.bat_nb,
txn.txn_seq_nb) a
WHERE ROWNUM <= 1000)
WHERE rnum >= 1
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 13:57:37 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
