| tuning SUM reproting statement [message #130676] |
Mon, 01 August 2005 22:40  |
mjschwenger
Messages: 137
Registered: July 2005
Location: US
|
Senior Member |
|
|
SELECT SUM(DECODE(stt.effect_on_account, '-', st.mem_amt*-1, st.mem_amt*1)) ACCT_BALANCE
FROM s_transaction st, s_trans_type stt
WHERE st.appl_area = stt.appl_area
AND st.trans_type_code = stt.trans_type_code;
This is one of the statements I have in one of my reports, together with 2 more. If 10 people run the same report in the same time, there's deadlocks all over the place and this statement is one of the longest to complete - no wonder - the joint between the 2 tables returns 5,691,591 rows and there's no limiting condition. In this case the optimizer take full table scan for cheaper then using any indexex. Right now I cannot change the report - no access, but in the future, we'll rewrite all of them.
For the moment I would like to try to tune this query to improve the performance.
Indexing:
s_trans_type stt - 1 component index on appl_area & trans_type_code
s_transaction st - 2 single indexes on both columns.
I was thinking if I'll gain performance if create component index in s_transaction - the way it's in s_trans_type?
The other thing coming to my mind is to create a covered index...
Please, advise what can I do if there's something at all...
Thanks a lot in advance, mj
|
|
|
|
| Re: tuning SUM reproting statement [message #130785 is a reply to message #130676] |
Tue, 02 August 2005 08:50  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
please post your oracle version.
Did you try increasing SORT_AREA_SIZE / pga_aggregate_target ( in 9i, pga_aggregate_target is enough. No need to set sort_area_size).
If changing pga_aggregate_target, set your workarea_size_policy to manual. Since the CBO is already using a FTS, we can help it to do it more and better by increasing db_file_multiblock_read_count etc.
But before doing all these, first post your autotrace /tracefile with 10046 enabled in level 8 ,the tkprof output / statspack.
[Updated on: Tue, 02 August 2005 08:51] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
