Re: How much resource/CPU usage reduction to expect?
- From: Christo Kutrovsky <kutrovsky.oracle@xxxxxxxxx>
- To: zhuchao@xxxxxxxxx
- Date: Wed, 20 Jul 2005 17:59:41 +0300
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
