Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Exclamation Unanswered: fight against idle time in oracle

    Hi everybody,

    I have a little problem that maybe someone can give me some help.

    Im running a sql statement (SELECT) over jdbc on a ORACLE 8.1.7 DB this query will only retrieves to memory the result set.
    The problem is that some days ago the query run smoothly in my environment and now it have great periods of idle time.
    So I'm checking the v$sesstat view waiting that the query finish monitoring the disc acess, cpu usage and buffer gets, but only in long periods the query wakes up after the last_call_et raise highvalues (around 20 min of inactivity).
    The execution plan is the expected and it have run just fine two days ago.
    What can be the problem ?
    Is there any way to found what the query is doing in this inactive periods ?
    Thanks

  2. #2
    Join Date
    Dec 2003
    Location
    India
    Posts
    7

    Re: fight against idle time in oracle

    Try using a PL/SQL block or a stored procedure.
    PL/SQL blocks complete the transaction in just one call and thereby reducing the idle time.

    Originally posted by hortar
    Hi everybody,

    I have a little problem that maybe someone can give me some help.

    Im running a sql statement (SELECT) over jdbc on a ORACLE 8.1.7 DB this query will only retrieves to memory the result set.
    The problem is that some days ago the query run smoothly in my environment and now it have great periods of idle time.
    So I'm checking the v$sesstat view waiting that the query finish monitoring the disc acess, cpu usage and buffer gets, but only in long periods the query wakes up after the last_call_et raise highvalues (around 20 min of inactivity).
    The execution plan is the expected and it have run just fine two days ago.
    What can be the problem ?
    Is there any way to found what the query is doing in this inactive periods ?
    Thanks

  3. #3
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    PL/SQL or SQL - basically the same work must be performed. I would suggest increasing the trace level on your session to see what's happening.

    Trust me, there's a lot more going on than just disk, cpu and buffer gets :-)

    By your post, I imagine you don't need a lot of pointing in the right direction, I would guess that you can find your own way.... take a look here, and then follow your nose....

    http://www.adp-gmbh.ch/ora/misc/events.html

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    ALTER SESSION SET SQL_TRACE=TRUE;
    The run theresultant trace file thru TKPROF.

  5. #5
    Join Date
    Dec 2003
    Posts
    3
    thanks for the suport.

Posting Permissions

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