Re: High CPU usage

From: Subodh Deshpande <deshpande.subodh_at_gmail.com>
Date: Mon, 18 Jun 2012 06:59:04 +0530
Message-ID: <CAJsOtB5P9SfeEGaJ9Bw6-zayr_X36fRCH08qrF6bCR+440JpDQ_at_mail.gmail.com>

can you rebuild indexes online..it should a lesser time. if the size of these tables and indexes are bulk then it may take time, and rebuild on line will take lesser than dropping and recreating

cpu 16 and memory usage is 97%, is whole of available memory is been allocated to SGA !
check pga size, are their any running long queries/sessions/transactions/Cartesian queries

On 13 June 2012 09:40, Gerry Miller <gerry_at_millerandbowman.com> wrote:

> Hi
> I am performing regression tests on index creation on Oracle Standard
> Edition10.2.0.4 in RHEL 5.3. The first time I created the 450 indexes it
> took 16 hours. A few days later I dropped them and recreated them and it
> took 30 hours. I did it a third time and it is still running after 50
> hours,with only 320 indexes created. I enabled tracing during the second
> runand have found that the time is virtually all down to CPU usage.
> Buffer
> gets are about the same and a resource profile show that CPU accounts for
> 86% of resource usage and scattered reads 8%. There are no other
> significant
> waits and the 16 CPUs on the server are running at around 85% idle all the
> time, while memory usage is quite high at 97%.
>
> Furthermore, in comparing my regression test results I have found that
> CPU
> usage is consistently and significantly higher in one result set than
> others. For example, consider this one tkprof output:
> First Run: call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 0.00 0.00 0 0 0
> 0
> Fetch 5 0.00 0.00 0 55 0
> 50
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 7 0.00 0.00 0 55 0
> 50
>
> Second Run: call count cpu elapsed disk query
> current rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 1 0.00 0.00 0 0 0
> 0
> Execute 1 32.74 31.98 0 0 0
> 0
> Fetch 5 0.00 0.00 0 55 0
> 50
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 7 32.74 31.99 0 55 0
> 50
>
> This is for the same query, with the same execution plan, no ORDER BY
> clause
> and the only waits events are 5 'SQL*Net message to/from client'
> corresponding to the FETCHes. Why would it take 32 seconds the second
> time,
> all down to CPU, when it is doing no sorting or extra buffer gets?
>
> In my test results find hundreds of SQL statements that have the same
> execution plans and comparable buffer get counts but the CPU time is MUCH
> higher in one run than the other.
>
> Does anyone have any suggestions as to what might be the cause of this
> behaviour?
>
> Thanks and Regards
>
> Gerry Miller
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
=============================================
This Gmail Account will be deactivated  in One Months Time
=============================================


--
http://www.freelists.org/webpage/oracle-l

Received on Sun Jun 17 2012 - 20:29:04 CDT