Home » RDBMS Server » Performance Tuning » Performance issue problem
| Performance issue problem [message #182831] |
Tue, 18 July 2006 06:17  |
bagulia_pilla
Messages: 25
Registered: July 2006
|
Junior Member |
|
|
Can we tune this query as this is taking more time
Plz help me ...
----
SELECT trx.transactionid, trx.status, trx.batchid, trx.referencecode,
trx.transactiontype, trx.originatoruid, trx.description,
trx.createtime, trx.VERSION, trx.accountid, trx.amount, trx.currency,
trx.beneficiaryname, trx.executetime, trx.ebankingrefno, trx.bankid,
trx.companyid, trx.makerid, MEMBER.first_name, MEMBER.last_name,
co.companyname,
DECODE (trx.status,
'ScheduleToSend', 'Sent',
'Accepted', 'Sent',
'Sent2JMS', 'Pending To Send',
'Sent2Host', 'Pending To Send',
trx.status
) user_shown_status,
DECODE
(trx.transactiontype,
'STOPCHQ', 'Stop Cheque',
'INFDTRFSAMECURRO', 'Inter Account Fund Transfer (Own)',
'INFDTRFSAMECURRT', 'Inter Account Fund Transfer (3rd)',
'INFDTR FDIFFCURRO', 'Inter Account Fund Transfer (Own)',
'INFDTRFDIFFCURRT', 'Inter Account Fund Transfer (3rd)',
'INFDTRFSAMECURROP', 'Inter Account Fund Transfer (Own) Post Dated',
'INFDTRFSAMECURRTP', 'Inter Account Fund Transfer (3rd) Post Dated',
'INFDTRFSAMECURROP_DSENT', 'Stop Sent Inter Account Fund Transfer (Own)',
'INFDT RFSAMECURRTP_DSENT', 'Stop Sent Inter Account Fund Transfer (3rd)',
'IBG', 'Inter Bank Funds Transfer',
'IBGP', 'Inter Bank Funds Transfer Post Dated',
'IBGP_DSENT ', 'Stop Sent Inter Bank Funds Transfer',
'BP', 'Bill Payment',
'TDP', 'SGD TD Placement',
'FCFDP', 'Time/ Fixed Deposit Placement',
'CO', 'Cashier''s Order',
'TT', 'Telegraphic Transfer',
'DD', 'Demand Draft',
'LC', 'LC Application',
'SL', 'Standby LC',
'LCA', 'LC Amendment',
'LC_CANCEL', 'LC Cancellation',
'SLC', 'Standby LC Am endment',
'SL_CANCEL', 'Standby LC Cancellation',
'SG', 'Shipping Guarantee',
'TR', 'Trust Receipt Payment',
'PG', 'Performance Guarantee',
'CN', 'Collection Notice',
'DN', 'Debit Note',
'BULKCORDR', 'Bulk Payment',
'BULKDDRFT', 'Bulk Payment',
'BULKCHEQU', 'Bulk Payment',
'BULKTELTR', 'Bulk Payment',
'BULKIBGWA', 'Bulk Payment',
'BULKIBGOA', 'Bulk Payment',
'UPLBCORDR', 'Bulk Payment',
'UPLBDDRFT', 'Bulk Payment',
'UPLBCHEQU', 'Bulk Payment',
'UPLBTELTR', 'Bulk Payment',
'UPLBIBGWA', 'Bulk P ayment',
'UPLBIBGSP', 'Bulk Payment',
'UPLBIBGST', 'Bulk Payment',
'BULKIBGPR', 'Bulk Payroll',
'UPLBIBGPR', 'Bulk Payroll',
'UPLBIBPSP', 'Bulk Payroll',
'BULKIBGCL', 'Bulk Collection',
'UPLBIBGCL', 'Bulk Collection',
'UPLBIBCSP', 'Bulk Collection',
'BULKCORDR_DSENT', 'Stop Sent Request Bulk Payment',
'BULKDDRFT_DSENT', 'Stop Se nt Request Bulk Payment',
'BULKCHEQU_DSENT', 'Stop Sent Request Bulk Payment',
'BULKTELTR_DSENT', 'Stop Sent Request Bulk Payment',
'BULKIBGWA_DSENT', 'Stop Sent R equest Bulk Payment',
'BULKIBGOA_DSENT', 'Stop Sent Request Bulk Payment',
'UPLBCORDR_DSENT', 'Stop Sent Request Bulk Payment',
'UPLBDDRFT_DSENT', 'Stop Sent Reque st Bulk Payment',
'UPLBCHEQU_DSENT', 'Stop Sent Request Bulk Payment',
'UPLBTELTR_DSENT', 'Stop Sent Request Bulk Payment',
'UPLBIBGWA_DSENT', 'Stop Sent Request B ulk Payment',
'UPLBIBGSP_DSENT', 'Stop Sent Request Bulk Payment',
'UPLBIBGST_DSENT', 'Stop Sent Request Bulk Payment',
'BULKIBGPR_DSENT', 'Stop Sent Request Bulk Payroll',
'UPLBIBGPR_DSENT', 'Stop Sent Request Bulk Payroll',
'UPLBIBPSP_DSENT', 'Stop Sent Request Bulk Payroll',
'BULKIBGCL_DSENT', 'Stop Sent Request Bulk Coll ection',
'UPLBIBGCL_DSENT', 'Stop Sent Request Bulk Collection',
'UPLBIBCSP_DSENT', 'Stop Sent Request Bulk Collection',
trx.transactiontype
) user_shown_trxtype,
TO_DATE (trx.executetime, 'dd/MM/yyyy') user_shown_exectime
FROM TRANSACTION trx,
companymember MEMBER,
company co
WHERE trx.companyid = co.companyid
AND co.companyid = MEMBER.companyid
AND trx.makerid = MEMBER.user_id
and Upper (companyloginid) LIKE '%BULKPAR%'
AND trx.transactiontype IN
('BP',
'CO',
'CN',
'DN',
'DD',
'FCFDP',
'INFDTRFSAMECURRO',
'INFDTRFSAMECURRT',
'INFDTRFDIFFCURRO',
'INFDTRFDIFFCURRT',
'INFDTRFSAMECURROP',
'INFDTRFSAMECURRTP',
'INFDTRFSAMECURROP_DSENT',
'INFDTRFSAMECURRTP_DSENT',
'IBG',
'IBGP',
'LC',
'LCA',
'PG',
'SG',
'SL',
'SLC',
'STOPCHQ',
'TT',
'TR',
'BULKCORDR',
'BULKDDRFT',
'BULKCHEQU',
'BULKTELTR',
'BULKIBGWA',
'BULKIBGOA',
'UPLBCORDR',
'UPLBDDRFT',
'UPLBCHEQU',
'UPLBTELTR',
'UPLBIBGWA',
'UPLBIBGSP',
'UPLBIBGST',
'BULKIBGPR',
'UPLBIBGPR',
'UPLBIBPSP',
'BULKIBGCL',
'UPLBIBGCL',
'UPLBIBCSP'
)
AND trx.status IN
('Sent',
'In-Process',
'Rejected',
'Issued',
'Completed',
'ScheduleToSend',
'Sent2JMS',
'Sent2Host',
'Accepted',
'Void',
'Stopped'
)
AND trx.VERSION > 0
ORDER BY ebankingrefno
Plan
-----------------------
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 60
SORT ORDER BY 1 433 60
NESTED LOOPS 1 433 58
NESTED LOOPS 1 373 57
TABLE ACCESS FULL EBN.TRANSACTION 12 3 K 45
TABLE ACCESS BY INDEX ROWID EBN.COMPANY 1 47 1
INDEX UNIQUE SCAN EBN.PK_COMPANY 20
TABLE ACCESS BY INDEX ROWID EBN.COMPANYMEMBER 1 60 1
INDEX UNIQUE SCAN EBN.PK_CMPMEMBER 1
-
Attachment: query.sql
(Size: 7.32KB, Downloaded 1172 times)
|
|
|
|
| Re: Performance issue problem [message #182835 is a reply to message #182831] |
Tue, 18 July 2006 06:28  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Looking at the plan, I'd say that either your stats are wrong, or you're missing an index.
Operation Object Name Rows Bytes Cost
SELECT STATEMENT Optimizer Mode=CHOOSE 1 60
SORT ORDER BY 1 433 60
NESTED LOOPS 1 433 58
NESTED LOOPS 1 373 57
TABLE ACCESS FULL EBN.TRANSACTION 12 3 K 45
TABLE ACCESS BY INDEX ROWID EBN.COMPANY 1 47 1
INDEX UNIQUE SCAN EBN.PK_COMPANY 20
TABLE ACCESS BY INDEX ROWID EBN.COMPANYMEMBER 1 60 1
INDEX UNIQUE SCAN EBN.PK_CMPMEMBER 1
According to this, the optimiser is only expecting 12 rows back from the TRANSACTION table, and yet it is having to do a FTS to get them.
Make sure you've got histograms on status and transactiontype.
Also, check to make sure that there is an index that the optimiser can use to access TRANSACTION if the number of required rows is small.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 03:51:01 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
