Home » RDBMS Server » Server Administration » Query executions (Oracle 10gRel2 (10.2.0.1) Windows 2003 Server)
Query executions [message #426295] |
Wed, 14 October 2009 15:09 |
marcossantos
Messages: 123 Registered: June 2008
|
Senior Member |
|
|
Hi,
the server is with huge consume of cpu. The task manager
show 100% of cpu used.
The enterpreise manager show 100% too.
I find out this result for AWR report.
DETAILED ADDM REPORT FOR TASK 'ADDM:3335237194_1_1674' WITH ID 11493
--------------------------------------------------------------------
Analysis Period: 06-OUT-2009 from 13:00:37 to 14:01:02
Database ID/Instance: 3335237194/1
Database/Instance Names: GEODBAM/geodbam
Host Name: SISCOM-AM
Database Version: 10.2.0.1.0
Snapshot Range: from 1673 to 1674
Database Time: 15382 seconds
Average Database Load: 4,2 active sessions
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
FINDING 1: 85% impact (13110 seconds)
-------------------------------------
O tempo gasto na CPU pela instância foi responsável por parte substancial do
tempo de banco de dados.
RECOMMENDATION 1: SQL Tuning, 100% benefit (17270 seconds)
ACTION: Execute o Supervisor de Ajuste SQL na instrução SQL com o SQL_ID
"6zd2zbjyn5111".
RELEVANT OBJECT: SQL statement with SQL_ID 6zd2zbjyn5111 and
PLAN_HASH 1181612595
SELECT /*+ NO_EXPAND */ rasterband_id, sequence_nbr, raster_id,
band_flags, eminx, eminy, emaxx, emaxy, cdate, mdate, band_width,
band_height, block_width, block_height, block_origin_x,
block_origin_y, band_types, name FROM SDE.SDE_BND_643 WHERE raster_id
IN (SELECT t.column_value FROM TABLE (CAST (:numtab AS
SDE.sdenumtab)) t) ORDER BY raster_id, sequence_nbr
ACTION: Investigue a instrução SQL com o SQL_ID "6zd2zbjyn5111" para
obter possíveis melhorias de desempenho.
RELEVANT OBJECT: SQL statement with SQL_ID 6zd2zbjyn5111 and
PLAN_HASH 1181612595
SELECT /*+ NO_EXPAND */ rasterband_id, sequence_nbr, raster_id,
band_flags, eminx, eminy, emaxx, emaxy, cdate, mdate, band_width,
band_height, block_width, block_height, block_origin_x,
block_origin_y, band_types, name FROM SDE.SDE_BND_643 WHERE raster_id
IN (SELECT t.column_value FROM TABLE (CAST (:numtab AS
SDE.sdenumtab)) t) ORDER BY raster_id, sequence_nbr
RATIONALE: A instrução SQL com SQL_ID "6zd2zbjyn5111" foi executada
25912004 vezes e apresentava um tempo médio decorrido de 0.0004
segundos.
RATIONALE: A média de CPU usada por execução foi de 0.0004 segundos
Can anybody help me, please.
Marcos Santos
|
|
|
Re: Query executions [message #426296 is a reply to message #426295] |
Wed, 14 October 2009 15:26 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Find out who has executed that SQL 25912004 times, and why. Possibly an application bug somewhere that got stuck in a loop.
You can find the user, oseruser, etc.. of the top CPU sessions with:
SELECT * FROM
(SELECT s.sid, p.spid, s.status,s.username,
s.osuser, a.sql_text , a.cpu_time
FROM v$sqlarea a, v$session s, v$process p
where a.hash_value = s.sql_hash_value
and s.paddr = p.addr ORDER BY a.cpu_time DESC)
WHERE rownum <= 10
Then end/kill the program of that user.
|
|
|
|
Re: Query executions [message #426299 is a reply to message #426295] |
Wed, 14 October 2009 16:02 |
marcossantos
Messages: 123 Registered: June 2008
|
Senior Member |
|
|
Hi,
I have a question about a quantity of times that query execute.
By report, was 25912004 times. This quantity correspond a period of time (for example, one hour) or all times that query execute since database was started?
Thanks,
|
|
|
Re: Query executions [message #426301 is a reply to message #426299] |
Wed, 14 October 2009 16:05 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
It should be inside the "Analysis Period". But I'm not quite sure, since I only speak maybe two words of Portuguese.
|
|
|
Goto Forum:
Current Time: Tue Dec 03 18:28:02 CST 2024
|