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 Go to next message
marcossantos
Messages: 114
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 Go to previous messageGo to next message
ThomasG
Messages: 3186
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 #426298 is a reply to message #426295] Wed, 14 October 2009 15:37 Go to previous messageGo to next message
marcossantos
Messages: 114
Registered: June 2008
Senior Member
Thanks for answer,

Marcos Santos
Re: Query executions [message #426299 is a reply to message #426295] Wed, 14 October 2009 16:02 Go to previous messageGo to next message
marcossantos
Messages: 114
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 Go to previous message
ThomasG
Messages: 3186
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. Very Happy
Previous Topic: how to synchronize between tow schema separated on tow databases (merged 4)
Next Topic: terminating instance due to error (merged 3)
Goto Forum:
  


Current Time: Sat Oct 01 04:09:08 CDT 2016

Total time taken to generate the page: 0.07592 seconds