Re: How much resource/CPU usage reduction to expect?

What do you do between the select and update ?

You could use update - returning clause, depending on what you are doing. 

What capacity bottleneck are you hitting? CPU or IO ?

-- 
Christo Kutrovsky
Database/System Administrator
The Pythian Group


On 7/20/05, zhu chao <zhuchao@xxxxxxxxx> wrote:
> 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.I
> think we can combine the two SQL into one as on OTN there is sample code. 
> 
> Thanks. 
> -- 
> Regards
> Zhu Chao
> www.cnoug.org
> 
> 


-- 
Christo Kutrovsky
Database/System Administrator
The Pythian Group
--
http://www.freelists.org/webpage/oracle-l

Other related posts: