Re: High CPU usage
- From: Gerry Miller <gerry@xxxxxxxxxxxxxxxxxxx>
- To: Subodh Deshpande <deshpande.subodh@xxxxxxxxx>
- Date: Tue, 19 Jun 2012 09:05:05 +1000
Hi Subodh
I can't create or rebuild online as it is Oracle Standard Edition.
The memory is 30G and the SGA 20G.
When memory usage increases there is no perceptible increase in PGA usage.
I perhaps did not make it clear that this issue is not just with index
creation but also with any SQL being run; the tkprof example I gave below is
from a basic query, which is one of many that uses a lot more CPU during one
execution than another and yet performs no more buffer gets, has the same
wait events etc.
Thanks and regards
Gerry
Subodh Deshpande wrote: 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@xxxxxxxxxxxxxxxxxxx[1]> 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
--
//www.freelists.org/webpage/oracle-l[2]
--
=============================================
This Gmail Account will be deactivated in One Months Time
=============================================
--- Links ---
1 mailto:gerry@xxxxxxxxxxxxxxxxxxx
2 //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
