Home » RDBMS Server » Performance Tuning » Any Better Way to Tune this Query
| Any Better Way to Tune this Query [message #224649] |
Wed, 14 March 2007 23:04  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi,
We have this issue of slow query in Production it's taking more than 2mins,
Is there some thing we can do here,I have attached PLAN and Number of rows, There are couple of Views in that, one of the View is calling a function IF there is any room we can modify this or tune this that will be really great,
SQL> select count(*) from txn;
COUNT(*)
----------
70491573
SQL> select count(*) from pymt_view;
COUNT(*)
----------
70043726
SQL> select count(*) from txn_Type;
COUNT(*)
----------
4
SQL> select count(*) from site;
COUNT(*)
----------
51
SQL> select count(*) from note;
COUNT(*)
----------
3287078
SQL> select count(*) from note_Txt;
COUNT(*)
----------
1986000
SQL> select count(*) from txn_inv_cnt_view;
COUNT(*)
----------
70491573
SELECT *
FROM (SELECT a.*, ROWNUM rnum
FROM (SELECT /*+ FIRST_ROWS */
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, pymt.crcy_id,
txn_inv_cnt_view.excp_count, asscn_sts_cd
FROM txn,
pymt_view pymt,
lockbox,
txn_type,
note,
note_txt,
site,
dda,
txn_inv_cnt_view,
ext_bat_view bat
WHERE pymt.pymt_nb_tx = 5169
AND dol_am = 14.34
AND bat.proc_dt >= '30-NOV-2006'
AND bat.usr_id = 22437
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(+)
AND txn.txn_id = txn_inv_cnt_view.txn_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
Elapsed: 00:01:10.08
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=42 Card=1
Bytes=849)
1 0 VIEW (Cost=42 Card=1 Bytes=849)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=42 Card=1 Bytes=836)
4 3 SORT (UNIQUE STOPKEY) (Cost=39 Card=1 Bytes=346)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'LOCKBOX_ACL' (Co
st=2 Card=1 Bytes=11)
6 5 NESTED LOOPS (Cost=36 Card=1 Bytes=346)
7 6 NESTED LOOPS (Cost=34 Card=1 Bytes=335)
8 7 NESTED LOOPS (Cost=33 Card=1 Bytes=317)
9 8 NESTED LOOPS (Cost=32 Card=1 Bytes=303)
10 9 NESTED LOOPS (Cost=32 Card=1 Bytes=298)
11 10 NESTED LOOPS (OUTER) (Cost=31 Card=1 B
ytes=291)
12 11 NESTED LOOPS (OUTER) (Cost=29 Card=1
Bytes=285)
13 12 NESTED LOOPS (OUTER) (Cost=27 Card
=1 Bytes=264)
14 13 NESTED LOOPS (OUTER) (Cost=26 Ca
rd=1 Bytes=252)
15 14 NESTED LOOPS (Cost=25 Card=1 B
ytes=213)
16 15 NESTED LOOPS (Cost=24 Card=1
Bytes=205)
17 16 NESTED LOOPS (Cost=21 Card
=1 Bytes=147)
18 17 NESTED LOOPS (Cost=16 Ca
rd=1 Bytes=114)
19 18 NESTED LOOPS (Cost=15
Card=1 Bytes=99)
20 19 NESTED LOOPS (Cost=9
Card=3 Bytes=180)
21 20 NESTED LOOPS (OUTE
R) (Cost=7 Card=1 Bytes=50)
22 21 NESTED LOOPS (Co
st=4 Card=1 Bytes=42)
23 22 NESTED LOOPS (
Cost=3 Card=1 Bytes=20)
24 23 TABLE ACCESS
(BY INDEX ROWID) OF 'USR' (Cost=2 Card=1 Bytes=10)
25 24 INDEX (UNI
QUE SCAN) OF 'XPK_USR' (UNIQUE) (Cost=1 Card=1)
26 23 TABLE ACCESS
(BY INDEX ROWID) OF 'USR' (Cost=1 Card=1 Bytes=10)
27 26 INDEX (UNI
QUE SCAN) OF 'XPK_USR' (UNIQUE)
28 22 TABLE ACCESS (
BY INDEX ROWID) OF 'CUST' (Cost=1 Card=1 Bytes=22)
29 28 INDEX (UNIQU
E SCAN) OF 'XPK_CUS' (UNIQUE)
30 21 TABLE ACCESS (BY
INDEX ROWID) OF 'USR_ROLE' (Cost=3 Card=1 Bytes=8)
31 30 INDEX (RANGE S
CAN) OF 'XIF_USRROL_USRID' (NON-UNIQUE) (Cost=1 Card=3)
32 20 TABLE ACCESS (BY I
NDEX ROWID) OF 'LOCKBOX_CUST' (Cost=2 Card=3 Bytes=30)
33 32 INDEX (RANGE SCA
N) OF 'XIF_LOCCUS_CUSID' (NON-UNIQUE) (Cost=1 Card=3)
34 19 TABLE ACCESS (BY GLO
BAL INDEX ROWID) OF 'BAT' (Cost=2 Card=1 Bytes=39)
35 34 INDEX (RANGE SCAN)
OF 'XAK_BAT_LBIDPRDTBTNB' (UNIQUE) (Cost=1 Card=17)
36 18 TABLE ACCESS (BY INDEX
ROWID) OF 'LOCKBOX_CUST' (Cost=1 Card=1 Bytes=15)
37 36 INDEX (UNIQUE SCAN)
OF 'XAK_LOCCUS_LOCID_CUSID' (UNIQUE)
38 17 TABLE ACCESS (BY GLOBAL
INDEX ROWID) OF 'TXN' (Cost=5 Card=14 Bytes=462)
39 38 INDEX (RANGE SCAN) OF
'XAK_TXN_BATID_TXNID' (UNIQUE) (Cost=3 Card=14)
40 16 TABLE ACCESS (BY GLOBAL IN
DEX ROWID) OF 'PYMT' (Cost=3 Card=1 Bytes=58)
41 40 INDEX (RANGE SCAN) OF 'X
IE_PYM_TXNID_DOLAMPYMNBTX' (NON-UNIQUE) (Cost=2 Card=1)
42 15 TABLE ACCESS (BY INDEX ROWID
) OF 'TXN_TYPE' (Cost=1 Card=1 Bytes=8)
43 42 INDEX (UNIQUE SCAN) OF 'XP
K_TXNTYP' (UNIQUE)
44 14 VIEW PUSHED PREDICATE OF 'RMIT
_ACTV_VIEW' (Cost=1 Card=1 Bytes=39)
45 44 NESTED LOOPS (OUTER) (Cost=2
Card=1 Bytes=69)
46 45 TABLE ACCESS (BY INDEX ROW
ID) OF 'RMIT' (Cost=2 Card=1 Bytes=56)
47 46 INDEX (UNIQUE SCAN) OF '
XPK_RMT' (UNIQUE) (Cost=1 Card=1)
48 45 INDEX (UNIQUE SCAN) OF 'XP
K_CUSCLS' (UNIQUE)
49 13 TABLE ACCESS (BY INDEX ROWID) OF
'DDA' (Cost=1 Card=1 Bytes=12)
50 49 INDEX (UNIQUE SCAN) OF 'XPK_DD
' (UNIQUE)
51 12 TABLE ACCESS (BY GLOBAL INDEX ROWI
D) OF 'NOTE' (Cost=2 Card=1 Bytes=21)
52 51 INDEX (UNIQUE SCAN) OF 'XAK_NOT_
TXNID' (UNIQUE) (Cost=1 Card=1)
53 11 INDEX (RANGE SCAN) OF 'XAK_NOTTXT_NO
TID_NOTETXTID' (UNIQUE) (Cost=2 Card=1 Bytes=6)
54 10 INDEX (UNIQUE SCAN) OF 'XPK_TXN' (UNIQ
UE) (Cost=1 Card=1 Bytes=7)
55 9 INDEX (UNIQUE SCAN) OF 'XPK_LOC' (UNIQUE
)
56 8 TABLE ACCESS (BY INDEX ROWID) OF 'LOCKBOX'
(Cost=1 Card=1 Bytes=14)
57 56 INDEX (UNIQUE SCAN) OF 'XPK_LOC' (UNIQUE
)
58 7 TABLE ACCESS (BY INDEX ROWID) OF 'SITE' (Cos
t=1 Card=1 Bytes=18)
59 58 INDEX (UNIQUE SCAN) OF 'XPK_SIT' (UNIQUE)
60 6 INDEX (RANGE SCAN) OF 'XAK_LOCACL_USRID_LOCACL
ID' (UNIQUE) (Cost=1 Card=1)
Statistics
----------------------------------------------------------
950 recursive calls
0 db block gets
27429 consistent gets
657 physical reads
0 redo size
1050 bytes sent via SQL*Net to client
859 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
0 rows processed
Another questions is what does DEGREE column in USER_INDEXES mean for . searching documentaion
it gave this but not clear " Number of threads per instance for scanning the index "
And Logging option when to use and not, does it has any thing to do with Archivle log shipping and
to Standby Server, DOC says Specify whether the creation of the index will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file. This setting also determines whether subsequent Direct Loader (SQL*Loader) and direct-path INSERT operations against the index are logged or not logged. LOGGING is the default.
If index is nonpartitioned, this clause specifies the logging attribute of the index.
If index is partitioned, this clause determines:
The default value of all partitions specified in the CREATE statement (unless you specify the logging_clause in the PARTITION description clause) The default value for the segments associated with the index partitions
The default value for local index partitions or subpartitions added implicitly during subsequent ALTER TABLE ... ADD PARTITION operations The logging attribute of the index is independent of that of its base table.
Thanks
|
|
|
|
| Re: Any Better Way to Tune this Query [message #226316 is a reply to message #224649] |
Fri, 23 March 2007 04:33  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Anytime you do 'order by' it costs some performance.
I do not understand the ROWNUM <= 1000 line. How can you get the count into the millions and such like you did. I am not sure 'first_rows' helps you, especially with order by. The costs do not look too far out of line.
I would never turn off logging for any reason.
I am not positve but in past versions ddl (such as your index logging) is not captured in the archicelogs. Every time you change the structure of the database, such as creating a new tablespace, you must take a full backup.
|
|
|
|
| Re: Any Better Way to Tune this Query [message #226470 is a reply to message #226316] |
Fri, 23 March 2007 22:06 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Remove the FIRST_ROWS hint. You may just want the fastest performance to get the first 1000 rows, but the DISTINCT and ORDER BY forces Oracle to return the entire result set (millions of rows) before it picks off the first 1000.
FIRST_ROWS is forcing it to use Nested Loops joins and Indexed Access. But with the quantities of data you are accessing, you want ful scans and hash joins.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Sat Nov 30 05:08:03 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
