Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Unanswered: Which user is stressing my oracle

    hi every bodey

    i have 20 users in my oracle server -
    haw i could know which one of them is stressing my oracle without open tha trace option in the initora

    thanx in advance

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    1. Find the top hitting sql statements that are causing problems
    2. Use top sessions to see the most IO's
    3. Here's a script (I didn't write this one) that I use to see problem areas


    /**********************************************/
    /* Disk IO and CPU utilization by process */
    /* Change order by for disk -vs- cpu */
    /**********************************************/

    column v_thread heading 'Thread' format 99999
    column v_Term heading 'Terminal' format a12
    column v_user heading 'Username' format a10
    column v_program heading 'Program' format a28

    set linesize 95
    select p.spid thread, substr(P.TERMINAL,1,12) term, substr(s.username,1,10) v_user,
    substr(decode(nvl(p.background,0),1,bg.description ,
    s.program ),1,28) v_program,
    ss.value/100 CPU,physical_reads disk_io
    from v$process p,
    v$session s,
    v$sesstat ss,
    v$sess_io si,
    v$bgprocess bg
    where s.paddr=p.addr
    and ss.sid=s.sid
    and ss.statistic#=12
    and si.sid=s.sid
    and bg.paddr(+)=p.addr
    -- order by ss.value desc
    order by physical_reads desc
    ;

    set linesize 80

    4. What are the current waits on the database ???

    ---- Current Waits ----
    select sid,substr(event,1,40),wait_time,seconds_in_wait,s tate from v$session_wait;

    HTH
    Gregg

  3. #3
    Join Date
    Sep 2003
    Posts
    12

    Thanks

    Thanks my friend

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    No problem ... Hope it helps ...

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •