Anthony,
The statement is kind of long and at first looks like it can be rewritten. First of, I cannot touch it until certain point in the project, and secondly the complexity is because of the equally complex business requirements.
I am not crazy about using partitioning for such a small table (40K rows) But given the circumstances, have to rely on transparent solution of some sort...
Anyway, the query is
<<Document.txt>>
The execution plan is something like this
SELECT STATEMENT
NESTED LOOPS OUTER
VIEW
FILTER
NESTED LOOPS OUTER
NESTED LOOPS OUTER
TABLE ACCESS BY INDEX ROWID T_CURRENT_LOANS
INDEX RANGE SCAN I_CURRENT_LOANS_1
TABLE ACCESS BY INDEX ROWID T_LOAN2PACKAGE
INDEX RANGE SCAN I_LOAN2PACKAGE_1
VIEW PUSHED PREDICATE
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID T_LOAN2PACKAGE
INDEX RANGE SCAN I_LOAN2PACKAGE_1
TABLE ACCESS BY INDEX ROWID T_INVESTOR
INDEX UNIQUE SCAN SYS_C0061841
I tried various indexes plus hints combinations, and although the execution plan changes, actual time either stays around the same or gets worse.
(every time I changed indexes, I regenerated statistics).
David Hansen ,
about your suggestion:
"Make sure you have a large enough sort_area_size set so the data doesn't go
to disk (temp tablespace) while sorting."
Here's what I find in my DB:
SQL> show parameter sort_area_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sort_area_size integer 65536
Does this look acceptable to you?
Thanks all for any suggestions.
-----Original Message-----
From: Anthony Molinaro [mailto:oracledba-ezmlmshield-x27645840.[Email address protected]
Sent: Friday, January 27, 2006 5:37 PM
To: LazyDBA Discussion
Subject: RE: sql tuning.
Jenny,
Since it's only one query can you post the query and some stats?
Maybe we can tweak it a bit before you go and partition your tables
and try parallel sql.
Regards,
Anthony
-----Original Message-----
From: Jenny
[mailto:oracledba-ezmlmshield-x96208125.[Email address protected]
Sent: Friday, January 27, 2006 5:33 PM
To: LazyDBA Discussion
Subject: sql tuning.
Hi, gurus!
Any one had a positive experience with using partitioning/parallel
select on a small-ish databases? I am getting desperate ...
Must improve performance of one query, which performs decently, but I am
asked to "improve", and seems like no matter what I try I am hitting a
wall.
I know my question is too generic, and it is all too
environment-dependent...so I am looking for an inspiration! Any war
stories appreciated.
Thanks,
Jenny
Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses. The sender therefore
does not accept liability for any errors or omissions in the contents
of this message which arise as a result of e-mail transmission. If
verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html
ELECT
loan.loan_num,
loan.loan_nm,
loan.loan_face_am,
loan.lob_cd,
null,--Pkg_Mosg_Cf_Util.numToDate(loan.expire_date_num),
curr_status_cd,
super_num,
'N',
loan.investor_cd,
ti.inv_corp_nm,
update_user_cd,
update_ts,
null,--Pkg_Mosg_Cf_Util.numToDate(loan.orig_fund_date_num),
loan_state_cd,
coupon_am,
package_id,
package_ds,
request_status_cd
FROM
(SELECT
loan_nm,
loan_num,
loan.super_num,
loan_face_am,
lob_cd,
COALESCE(submit_loans.expire_date_num, loan.expire_date_num) expire_date_num,
COALESCE(submit_loans.commitment_num_cd, loan.commitment_num_cd) commitment_num,
COALESCE(submit_loans.contract_num_cd, loan.contract_num_cd) contract_num_cd,
loan.curr_status_cd,
COALESCE(submit_loans.loan_state_cd, 'NONE') loan_state_cd,
COALESCE(submit_loans.investor_cd, loan.investor_cd) investor_cd,
loan.update_user_cd,
loan.update_ts,
loan.orig_fund_date_num,
coupon_am,
COALESCE(submit_loans.package_id, saved_loans.package_id) package_id,
COALESCE(submit_loans.package_ds, saved_loans.package_ds) package_ds,
COALESCE(submit_loans.request_status_cd, saved_loans.request_status_cd) request_status_cd
FROM t_current_loans loan
LEFT OUTER JOIN
(SELECT
--mosg_user_id,
l2p.custnum_cd,
l2p.super_num,
l2p.package_id,
l2p.package_ds,
l2p.request_status_cd,
l2p.investor_cd,
l2p.commitment_num_cd,
l2p.contract_num_cd,
l2p.expire_date_num,
l2p.loan_state_cd
FROM t_loan2package l2p
WHERE
l2p.custnum_cd = 'XXXXXXXX' AND
l2p.request_status_cd = 'SUBMIT' AND
l2p.loan_state_cd IN ('DHY_SUBMIT', 'CNV_SUBMIT', 'SHP_SUBMIT', 'SHP_DONE') AND
l2p.last_package_fl = 'Y') submit_loans ON loan.super_num = submit_loans.super_num and
submit_loans.custnum_cd = loan.custnum_cd
LEFT OUTER JOIN
(SELECT
l2p.custnum_cd,
l2p.super_num,
l2p.package_id,
l2p.package_ds,
l2p.request_status_cd
FROM t_loan2package l2p
WHERE
l2p.custnum_cd ='GM069370' AND
l2p.request_status_cd ='SAVE') saved_loans
ON loan.super_num = saved_loans.super_num
WHERE loan.custnum_cd = 'XXXXXXXX' AND --loan.active_fl = 'Y' AND
UPPER(COALESCE(submit_loans.investor_cd, loan.investor_cd, '*')) = UPPER(COALESCE(trim(BOTH FROM ''),submit_loans.investor_cd,loan.investor_cd,'*')) AND
COALESCE(UPPER(loan_nm), '*') = COALESCE(trim(BOTH FROM UPPER('')), UPPER(loan_nm), '*') AND
COALESCE(UPPER(loan.loan_num), '*') = COALESCE(trim(BOTH FROM UPPER('')), loan.loan_num) AND
UPPER(COALESCE(submit_loans.commitment_num_cd,loan.commitment_num_cd,'*')) = UPPER(COALESCE(trim(BOTH FROM ''),submit_loans.commitment_num_cd, loan.commitment_num_cd,'*')) AND
UPPER(COALESCE(submit_loans.contract_num_cd, loan.contract_num_cd,'*')) =
UPPER(COALESCE(trim(BOTH FROM ''),submit_loans.contract_num_cd,loan.contract_num_cd,'*')) AND
(('' IS NULL) OR
('' = '' AND loan_face_am > COALESCE(0, loan_face_am - 1)) OR
(0 = 0 AND loan_face_am = COALESCE(0, loan_face_am)) OR
(0 = 0 AND loan_face_am < COALESCE(0, loan_face_am + 1))))
loan
LEFT OUTER JOIN t_investor ti ON ti.investor_cd = loan.investor_cd
Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses. The sender therefore
does not accept liability for any errors or omissions in the contents
of this message which arise as a result of e-mail transmission. If
verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities or
related financial instruments.
Oracle LazyDBA home page