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_at_gmail.com> 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