Check the explain plan of high resource consuming processes

Run the top command to find the prcoess taking high memeory on os level
#top

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
24498 oracle    19   0 55.3g  71m  42g R 98.4  0.1 149:24.89 oracle

you got the PID of the process which is taking high memeory/CUP/Time

Connect to database and by using above pid find the sql_id of the process

 SELECT
        s.sid,
        s.serial#,
        p.spid,
        s.username,
        s.program,sql_id,PREV_SQL_ID
 FROM   v$session s
        JOIN v$process p ON p.addr = s.paddr
 WHERe p.spid=  24498
SID    SERIAL# SPID                     USERNAME      PROGRAM                SQL_ID        PREV_SQL_ID
---------- ---------- ------------------------ ------------------------------ ----------------------------
105      5747 24498                     SCOTT         JDBC Thin Client   26fw6v1uiorkq3  bunvvs908ynf57

Now can find the explain plan of the current sql_id

select * from table(dbms_xplan.display_cursor('26fw6v1uiorkq3',0));

If you find the sql_id of processes which consuming high resource
you can get explain plan by running below procedure

SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('bunvvs908ynf57'));
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s