How much resource/CPU usage reduction to expect?

Hi, 
We have an old system running 4+ years, with really simple but heavy 
application. Now it is running on Sun Netra12 and we are hitting its 
capacity. Database is around 2TB now and it is mostly one single table ,3 
index, and 4 SQL, like:
tablea (id, col1,col2,...,logo long raw, logo_length number);

4 SQL:
select length from tablea where id=:b1;
select * from tablea where id=:b1;

update tablea set expire_date=:b2 where id=:b3;
insert into tablea values( :b1,:b2,...); long raw column length is typical 
2-3kb.

and one batch job: delete from table a where expire_date < sysdate -45;

The first two SQL are using 90% of the system BUFFER_GETS/CPU/executions, 
and actually everytime the first SQL runs and then run the second SQL. Two 
SQL have the same buffer gets/executions. If we can combine the two SQL into 
one, we can reduce the system buffer gets by 40% and executions by 40%(but 
no disk read drop, as currently the first SQL used 90% of system disk IO and 
second SQL have no disk io as it just re-visit the blocks the first SQL 
visited, so all memory access).

My question is, how much resouce we can save, especially for CPU
usage.Ithink we can combine the two SQL into one as on OTN there is
sample code.

Thanks. 
-- 
Regards
Zhu Chao
www.cnoug.org <http://www.cnoug.org>

Other related posts: