Home » RDBMS Server » Performance Tuning » tuning SUM reproting statement
tuning SUM reproting statement [message #130676] Mon, 01 August 2005 22:40 Go to next message
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.
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10702
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

Re: tuning SUM reproting statement [message #131237 is a reply to message #130676] Thu, 04 August 2005 22:44 Go to previous message
Messages: 19
Registered: June 2005
Junior Member
Cover index should be good enough for this specific query.
Previous Topic: Help me in tune my database PLZ
Next Topic: ORA-04045: errors during recompilation/revalidation of HPQ_IF.java/lang/String
Goto Forum:

Current Time: Fri May 27 16:41:12 CDT 2022