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>

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jul 20 2005 - 09:40:02 CDT