Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2003
    Posts
    106

    Unanswered: Timeout for query execution in ORACLE

    I have a PL/SQL stored procedure on ORACLE which executes a dynamic query

    EXECUTE IMMEDIATE query

    where query is like INSERT INTO TAB1 AS SELECT COL1,COL2 FROM TAB2 <where conditions>

    This select statement fetches huge number of records and inserts into the table.

    But if the number of records is very huge this execute immediete statment takes lots of time and I need to set a time out for this.

    So that if it takes more than 3 min I need to abort the EXECUTE IMMEDIATE and throw an exception "Query take too much time"

    Can this be done with EXECUTE IMMEDIATE statment ?
    Or is there any other way by whiich we can execute a dynamic query with timeout.

    Pl. help
    Sachi

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suspect the lack of response is because what you want is not easily accomplished; if it can be done at all.

    I think your odds of success would be better if you were directed to teach a lamb how to fly as opposed to implementing a software interrupt (ab)using PL/SQL.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I think this should be possible with resource profiles:
    A profile which terminates the user after being connected for only 3 minutes,
    might prove to be problematic in a production environment.

    Nothing is impossible for the person who does not have to actually implement the requirements.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by anacedent
    >I think this should be possible with resource profiles:
    A profile which terminates the user after being connected for only 3 minutes,
    might prove to be problematic in a production environment.
    Well "problematic" is only relevant in conjunction with specific requirements.

    And - as far as I can tell - the resource profiles can also be defined based on resource (CPU, IO) consumption not only "elapsed connection time".
    e.g. if the query "consumed" more than X seconds of CPU, which is something different than being connected for 3 minutes.

    Whether that fits the OP's requirements is not something I can tell.
    And whether that makes sense is far beyond of what I can judge.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >So that if it takes more than 3 min I need to abort the EXECUTE IMMEDIATE and throw an exception "Query take too much time"

    I interpret the OP statement, as repeated above, to meant the query must be terminated after 3 minutes (elapsed time); regardless of any other metric.

    One "possible" approach might involve an autonomous transaction,
    but then the challenge changes to getting the results (data rows or error message) back to originating session.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    If you could rewrite the process with a cursor, then you can monitor time elapsed. The downside is that you might be turning a 3-minute data collection process into a 6-minute process, so, you might have to increase your 3-minute cut-off to accomodate.

    --=cf

Posting Permissions

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