FAQ
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

On Wed, May 13, 2009 at 12:17 PM, Paresh Patel
wrote:
I have one procedure which contains no of DML statements. We have used
DBMS_SCHEDULER to kick off this procedure every night. This procedure gets
stuck intermittently and we tried to find out the exact SQL statement which
is getting stuck using data dictionary views but we couldn�t as Oracle
server doesn�t provide SQL_ID/SQL_HASH_VALUE/SQL_ADDRESS or in v$SESSION.
If anyone has any idea please do let me know.

What does 'stuck' actually mean?
Error messages?

Re the SQL: setup a job to run following that one that dumps v$sqltext,
v$sql

Appreciate your help�



*Thanks,*

*Paresh Patel,*
--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Jared Still at May 13, 2009 at 10:24 pm

    On Wed, May 13, 2009 at 12:17 PM, Paresh Patel wrote:

    I have one procedure which contains no of DML statements. We have used
    DBMS_SCHEDULER to kick off this procedure every night. This procedure gets
    stuck intermittently and we tried to find out the exact SQL statement which
    is getting stuck using data dictionary views but we couldn�t as Oracle
    server doesn�t provide SQL_ID/SQL_HASH_VALUE/SQL_ADDRESS or in v$SESSION.
    If anyone has any idea please do let me know.

    Hmm, hit the space bar and gmail thought it was ...
    I will try again.

    What does 'stuck' actually mean?

    Error messages?

    Is this a procedure you can modify?

    If so, then instrument it to do logging, and you can easily see where it
    dies.

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
  • Paresh Patel at May 13, 2009 at 10:39 pm
    What I meant here is, I want to know current DML statement processed by
    ORACLE server when ORACLE scheduler kicks off the job? So let say I have
    10 DML statements in procedure(which is kicked off by ORACLE scheduler), so far ORACLE server has processed first 4 statements and currently
    processing 5th one, is there any way I can find out ORACLE server is
    processing 5th one by looking at the data dictionary views?



    Thanks,

    Paresh Patel,



    From: Jared Still
    Sent: Wednesday, May 13, 2009 3:25 PM
    To: Paresh Patel
    Cc: oracle-l
    Subject: Re: question on DBMS_SCHEDULER.





    On Wed, May 13, 2009 at 12:17 PM, Paresh Patel
    wrote:

    I have one procedure which contains no of DML statements. We have used
    DBMS_SCHEDULER to kick off this procedure every night. This procedure
    gets stuck intermittently and we tried to find out the exact SQL
    statement which is getting stuck using data dictionary views but we
    couldn't as Oracle server doesn't provide
    SQL_ID/SQL_HASH_VALUE/SQL_ADDRESS or in v$SESSION. If anyone has any
    idea please do let me know.

    Hmm, hit the space bar and gmail thought it was ...

    I will try again.

    What does 'stuck' actually mean?

    Error messages?

    Is this a procedure you can modify?

    If so, then instrument it to do logging, and you can easily see where it
    dies.

    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
  • Martin Berger at May 14, 2009 at 10:25 am
    Hi Paresh,
    there are some directions you can investigate:
    To check, if the session really hangs, just sample
    SEQ#, EVENT FROM v$SESSION_WAIT WHERE SID=xxx
    only if both of them stay the same for all the samples, the session really
    hangs (or is in non-instrumented code-parts). Otherwise it is doing
    anything. (But just doesn't tell you, what).
    If your SQL_* columns in V$SESSION are empty, you might try to grab PREV_*
    columns instead to narrow the part where the session is currently working.

    As you mentioned in later posts, you have some DMLs, a ROLLBACK is a
    possibility all the time. maybe you want to check
    http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_30.shtml#IstheTransactionMovingForwardorRollingBack
    and
    sample v$transaction a little bit, just to check if you are within a
    rollback.

    v$open_cursor will give you a set of recently used cursors by the session,
    but as you have a pl/sql procedure (job) you might have pre-parsed any of
    your DMLs, so without knowing the exact code it might be misleading.

    If you are on 10g+ and proper licensed, v$active_session_history will give
    you a good information about the past minutes of your session (if the
    statements speed is not below the sampling interval).

    at the end, tracing (event 10046) is a possibility all the time, if you have
    no other way, but it conflicts with your initial goal to only use the data
    dictionary.

    I hope this gave you some starting vectors.
    br
    Martin
    On Wed, May 13, 2009 at 21:17, Paresh Patel wrote:

    Folks,



    I have one procedure which contains no of DML statements. We have used
    DBMS_SCHEDULER to kick off this procedure every night. This procedure gets
    stuck intermittently and we tried to find out the exact SQL statement which
    is getting stuck using data dictionary views but we couldn’t as Oracle
    server doesn’t provide SQL_ID/SQL_HASH_VALUE/SQL_ADDRESS or in v$SESSION.
    If anyone has any idea please do let me know.



    Appreciate your help



    *Thanks,*

    *Paresh Patel,*
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 13, '09 at 10:12p
activeMay 14, '09 at 10:25a
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase