Tracing session
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Tracing session

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

    Tracing session

    Hi,

    One of my application teams running a shell script which does some DML operations in the beginning then calls a function and does some DDL & DML inside the function.

    The script is hanging when it call the function. I have traced the session using dbms_system.set_sql_trace_in_session, tkprof it but nothing is showing in the tkprof log.

    This is the only session connected to DB. Log switch (50MB) is happening once in every two minutes but nothing is showing in sql trace.

    Can some one shed some light?

    Thanks,
    Vijay Tummala

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

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    forgot to mention my environment. Here it is ...

    OS - SUN SOL 5.10
    Oracle - 10.2.0.3

    Thanks,
    Vijay Tummala

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

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    I assume total time for both recursive and non recursive is less.
    Then problem is not in database. Ask them is it also connecting to some other server except this D/B?

    BTW u traced for how long? if it's for a small time increase trace time and see what u got.
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thanks Malay.

    It's not connecting to any other server except DB.

    I have traced the session for more than 2 hours and find nothing in the trace file.

    Some thing is happening in the DB which I am not able to figure out. Because, log switch is happening once in every 2 minutes and undo records used and undo blocks used are keep growing.

    when i query dba_rollback_segs, Undo segments 12$ and 13$ are offline since long time but 11$ is missing and 10$ is growing steadily.

    SQL> select owner,tablespace_name,segment_name,status from
    dba_rollback_segs;

    OWNER TABLESPACE_NAME SEGMENT_NAME
    STATUS
    ------ ------------------------------ ------------------------------
    ----------------
    SYS SYSTEM SYSTEM
    ONLINE
    PUBLIC UNDOTBS1 _SYSSMU1$
    ONLINE
    PUBLIC UNDOTBS1 _SYSSMU2$
    ONLINE
    PUBLIC UNDOTBS1 _SYSSMU3$
    ONLINE
    PUBLIC UNDOTBS1 _SYSSMU4$
    ONLINE
    PUBLIC UNDOTBS1 _SYSSMU5$
    ONLINE
    PUBLIC UNDOTBS1 _SYSSMU6$
    ONLINE
    PUBLIC UNDOTBS1 _SYSSMU7$
    ONLINE
    PUBLIC UNDOTBS1 _SYSSMU8$
    ONLINE
    PUBLIC UNDOTBS1 _SYSSMU9$
    ONLINE
    PUBLIC UNDOTBS1 _SYSSMU10$
    ONLINE
    PUBLIC UNDOTBS1 _SYSSMU12$
    OFFLINE
    PUBLIC UNDOTBS1 _SYSSMU13$
    OFFLINE

    13 rows selected.
    I am suspecting some thing wrong with my undo table space. does it make sense if i switch the system to new undo table space?

    Thanks,
    Vijay Tummala

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

  5. #5
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    did you see anything in v$session_longops?
    http://www.perf-engg.com
    A performance engineering forum

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    No, i was querying v$session_longops for the sessions running more than 5 seconds but it didn't show any thing.

    The tables involved in the script are very small; not even 10k rows in each table but undo records used crossed more than 15M at one time. Now, i killed the session and recovery is going on. Once recovery is complete then I am planning to create new undo table space and switch.

    Thanks,
    Vijay Tummala

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

  7. #7
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Seems like undo recreation should resolve this issue.
    keep us posted the outcome.
    http://www.perf-engg.com
    A performance engineering forum

  8. #8
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Unfortunately, undo TS recreation also didn't work. Tomorrow, I need to look into each individual object in the function. I will update the forum if I find some thing interesting.

    Thanks,
    Vijay Tummala

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

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Couple of questions...

    How does this script behaves on Test database?
    Is this the first time this script runs against production?
    ... if not, has script changed since last succesful run?

    Last but not least, have you taken a look at the function's code? ... what it does?

    Check any piece of logic that may induce a never ending loop condition.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thanks PAVB.

    Same script and same function are running good in other 3 environments and it was good in the environment where is it problematic now. No changes have been made from DB side since quite a long time.

    This script and function are the inbuilt code by Oracle (for ASAP application) and no one modify the code in it. More over the function has few delete blocks in it and returns some value. Not too complicated function.

    I have just analyzed all the objects in the schema and I am going to drop / create the function with the code from the other environment.

    Though it sounds silly, but i have very few options to fix this issue.

    Thanks,
    Vijay Tummala

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

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