query is running slow--cpu utilization 100%

query is running slow.


select sid,opname,target,round(sofar/totalwork*100,2) as percent_done,start_time,last_update_time,time_remaining
from v$session_longops;

select sid,inst_id,opname,totalwork,sofar,start_time,time_remaining

from gv$session_longops

where totalwork<>sofar

/

SQL> select event
from v$session
where sid =  2    3  1071;

EVENT
----------------------------------------------------------------
db file sequential read

SQL> select p1,p2 from v$session where sid=1071;

        P1         P2
---------- ----------
         9  108379037

SQL> select SID, state, event, p1, p2,username from v$session where sid=1071;

       SID STATE
---------- -------------------
EVENT                                                                    P1
---------------------------------------------------------------- ----------
        P2
----------
USERNAME
--------------------------------------------------------------------------------
      1071 WAITED SHORT TIME
db file sequential read                                                   9
 108379059
OMIRO


SQL> select owner, segment_name
from dba_extents
where file_id = 9
and 108379059 between block_id
and block_id + blocks;  2    3    4    5

OWNER
--------------------------------------------------------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
OMSS
CSW_SY_WHSE_TRACE_FACTS_M02




SQL> select event from v$session where sid=1071;

EVENT
----------------------------------------------------------------
PGA memory operation

select * from v$system_event where event = 'db file sequential read';

For session-level diagnosis, query the V$SESSION_EVENT view and identify the live session that registers a significant amount of time on this event using the query below. Once the session is identified, the DBA can take the necessary steps to find the root cause of this symptom.

select *
from   v$session_event
where  event = 'db file sequential read'
order by time_waited;

select segment_name, partition_name, segment_type, tablespace_name
from   dba_extents a, v$session_wait b
where  b.p2 between a.block_id and (a.block_id + a.blocks - 1)
and    a.file_id  = b.p1
and    b.event    = 'db file sequential read';

The SQL statement associated with this event can be obtained using this query:

select a.sid, a.serial#, a.username, a.osuser, b.sql_text
from   v$session a, v$sqltext b
where  a.sql_hash_value = b.hash_value
and    a.sql_address    = b.address
and    a.sid in (select sid
                 from   v$session_wait
                 where  event = 'db file sequential read')
order by a.sid, b.hash_value, b.piece;