Enable session trace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Enable session trace

  1. #1
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865

    Enable session trace

    Hi,

    Is there any thing wrong with the below syntax?

    EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 667, serial_num => 53739, waits => TRUE, binds => TRUE);

    I have used this many times earlier but in the current site it is not generating any trace files in udump.

    Please let me know if i am doing any thing wrong.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,460

    Cool Que chavienda

    Did you check the AUDIT_TRAIL parameter?
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi LKB -

    AUDIT_TRAIL parameter is set to DB.

    Could you please explain how does it related to session trace?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,460

    Cool Ooops...

    Ooops, yes. I was thinking about auditing, not tracing...Need coffee.


    PS: Are you logon as DBA?
    "DBMS_MONITOR package can only be invoked by a user with the DBA role..."
    Last edited by LKBrwn_DBA; 07-20-2010 at 01:23 PM.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  5. #5
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi LKB,

    hope you had a good coffee ...

    Yes, I logged in DBA. However, the trace files started generating after half an hour. Looks like this site had severe I/O issue. Why I am thinking in that front is ...

    1. OS level file copy from one FS to other FS is very slow
    2. at least two db writer processes are in the top list (when using unix top)
    3. at least one archiver processes is in the top list
    4. often check point will be first in the top consuming 80% cpu
    5. log writer always complaints that can not allocate new log sequence
    6. sequential reads are damn slow ... and so on

    This Oracle 10.2.0.4 db is on HP RX 8640 with 24 cores and 48 GB memory. Storage is Hitachi FC SAN.

    I am working on to prove with evidence that there an I/O issue.

    Any suggestions from your side is much appretiated.

    P.S - due to license issues I can not use AWR & ADDM here. I can only work on Statspack.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  6. #6
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Vijay,
    Why don't you execute sar/vmstat whaetever and check whether I/O is really issue?
    http://www.perf-engg.com
    A performance engineering forum

  7. #7
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi Malay,

    Sure. At the moment no load in the DB. I will do that during next batch load time.

    By the way, is there any recommended switch to get more information about disk I/O in vmsat or sar?

    I usually use "vmstat 2 100" or "vmstat -du 2 100" or "sar 2 100" like that. Any suggestion will be much appreciated?

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  8. #8
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    sar or vmstat whatedver u mentioned should be good enough for initial I/O investigation. Then you may get into details using iostat or similar command and at same time get the pid track it what it's doing.
    http://www.perf-engg.com
    A performance engineering forum

Posting Permissions

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



Click Here to Expand Forum to Full Width