How much resource/CPU usage reduction to expect?
- From: zhu chao <zhuchao@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 20 Jul 2005 22:37:30 +0800
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:
