This discussion is archived
14 Replies Latest reply: Nov 18, 2013 5:45 AM by Richard Harrison . RSS

Is there a way to stop a query just after the cursor/plan is produced by CBO?

PaolFili Newbie
Currently Being Moderated

Following suggestions of Kerry Osborne’s Oracle Blog » Blog Archive » Explain Plan Lies – Kerry Osborn…

on the lies of "Explain plan" (and of "set autotrace on"  too) I'd like to try to stop a query/DML before actually it starts, just after the plan was produced and sql_id assigned.

Is there any CLEAN way (other than trying CTRL-C) to do that?

Thanks

 

Paolo

  • 1. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    if a query is sent to the database server then the only way to "stop" it is by sending an interrupt signal to that session (or killing it). Can you tell us more about the problem you are trying to solve? E.g. if you want to prevent a costly query from running you can set a limit on maximum estimated time.

     

    Best regards,

    Nikolay

  • 2. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    PaolFili Newbie
    Currently Being Moderated

    Thanks Nikolay.

    My problem is:

    there are a lot of very smart tools (i.e. sqlhc) that requires REAL run of a query (or at least a sql_id/plan  in the Shared Pool) .

    I don' t want to use "explain plan for" to do any analysis (becouse "explain plan" lies), but let the CBO make the transformation, cost estimation and choice of the plan (following the current statistics -and parameters)

    WITHOUT EXECUTING THE QUERY (or DML).

    Two more questions arise from your reply:

    i)is there a way to send  CRTL-C as soon as the (costed) query plan is created and SQL_ID assigned, and BEFORE the first fetch operation of the SQL is exploited?

    ii)What are you talking about where you say "limit maximum estimated time" (are you talking of Resource Manager, Plan Manager, o what?.. can you make an example, please?)

    If I set the limit=0.01 seconds can I found in the cursor in SGA the SQL_ID/plan for the query?

    Thanks

     

    Paolo

  • 3. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    in principle you could write a script that would monitor the session executing the SQL (e.g. if your version is >= 11g and you do have the Diagnostic and Tuning Pack license then you can use V$ACTIVE_SESSION_HISTORY.IN_HARD_PARSE) and send an interrupt signal as soon as the SQL is optimized. But it looks very complicated and it's not clear what goal it would serve.

     

    The main problem with explain plan is not that the actual plan might differ. It's that even if it doesn't, it doesn't tell you enough to troubleshoot a statement except in some trivial cases. And nothing can possibly give you enough information until you execute it either completely or partially. But your method doesn't address that problem.

     

    Best regards,

    Nikolay

  • 4. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    rp0428 Guru
    Currently Being Moderated
    I'd like to try to stop a query/DML before actually it starts, just after the plan was produced and sql_id assigned.

    Is there any CLEAN way (other than trying CTRL-C) to do that?

    Sure - let's do it in the SCOTT schema

     

    1. Open a new session

    2. Issue 'UPDATE EMP SET SAL = NULL'

    3. Open a second session

    4. Issue 'UPDATE EMP SET SAL = NULL'

     

    That second session's query was stopped 'before actually it starts'. ALL of the rows it wants to modify are already locked. It will just 'hang' until you kill it or issue a COMMIT/ROLLBACK in the first session.

     

    Wasn't that easy?

     

    So it all depends on what 'query' you are talking about (which, as usual, you never bothered to say).

     

    Queries are 'stopped' all of the time. That 'stoppage' is typically one of the performance issues that people ask about here in the forums.

     

    So for queries that need to lock a particular row just lock that row first in another session.

  • 5. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    Hoek Guru
    Currently Being Moderated

    OP wants an execution plan that doesn't lie and doesn't take the actual time needed to run the query.

    How does locking/hanging sessions come into play here?

  • 6. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    rp0428 Guru
    Currently Being Moderated
    OP wants an execution plan that doesn't lie and doesn't take the actual time needed to run the query.

    How does locking/hanging sessions come into play here?

    Oracle produces the execution plan and then the session hangs when it tries to execute the plan.

     

    There is no 'lie' involved and the 'actual time needed' never happens because of the hang.

  • 7. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    Hoek Guru
    Currently Being Moderated

    So, how to obtain an execution plan from such a scenario?

  • 8. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    rp0428 Guru
    Currently Being Moderated
    So, how to obtain an execution plan from such a scenario?

    Ok, coming from you that has to be a rhetorical question, right? But for others you obtain the plan the same way you always do.

     

    Since the 'hung' query is the last statement executed in the session you cancel the query and then just execute a query like this:

    select plan_table_output

        from table(dbms_xplan.display_cursor(null,null,'basic'));

    The 'display_cursor' function gives you the plan for the LAST statement executed in the session.

     

    Oracle simply can NOT execute a query without the optimizer first creating the plan to use.

     

    See Maria Colgan's (a developer on the optimizer dev team) article for other ways to obtain a plan.

    Displaying and reading the execution plans for a SQL statement

    http://optimizermagic.blogspot.com/2008/02/displaying-and-reading-execution-plans.html

  • 9. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    PaolFili Newbie
    Currently Being Moderated

    Thanks Nikolay.

    Can you make a "real"  example of using the "V$ACTIVE_SESSION_HISTORY.IN_HARD_PARSE and send an interrupt signal as soon as the SQL is optimized"?

  • 10. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    PaolFili Newbie
    Currently Being Moderated

    Thanks rp.

    I think my question is a little dofferent, but your reply give me an idea.(which has clear disadvantages , but can do the work).

     

    The problem is obtain in Lybrary Cache a plan,a SLQ_ID,PLAN_HASH for a query ( i.e. a 10days running query) that I cannot start-up.

    So my (suggested from your reply) idea is:

    1) to LOCK EXCLUSIVE (a table level) , **if it's possible** every table accessed in the query ( Yes, it can be really expansive in some production environment, but sometimes can be necessary ..)

      using :  LOCK TABLE table IN EXCLUSIVE MODE

    for each table accessed from query

     

    2) Startup the query that will be suspended form the lock on tables accessed + kill the sid,serial#,@Inst_id for the query.

    3) UNLOCK tables from EXCLUSIVE using "ROLLBACK"  in the session where LOCK TABLE.... was send.(to remake  tables to work for other queries)

     

    Any other ideas?

    Thanks

     

    Paolo

  • 11. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    Nikolay Savvinov Guru
    Currently Being Moderated


    Hi,

     

    Thanks Nikolay.

    Can you make a "real"  example of using the "V$ACTIVE_SESSION_HISTORY.IN_HARD_PARSE and send an interrupt signal as soon as the SQL is optimized"?

     

    Sorry, why would you think I'd be interested in doing something like that? You haven't shown a single reason why the Oracle community (or just you) would benefit from another explain plan.

     

     

    Best regards,

      Nikolay

  • 12. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi

     

    PaolFili wrote:

     

    Thanks rp.

    I think my question is a little dofferent, but your reply give me an idea.(which has clear disadvantages , but can do the work).

     

    The problem is obtain in Lybrary Cache a plan,a SLQ_ID,PLAN_HASH for a query ( i.e. a 10days running query) that I cannot start-up.

    So my (suggested from your reply) idea is:

    1) to LOCK EXCLUSIVE (a table level) , **if it's possible** every table accessed in the query ( Yes, it can be really expansive in some production environment, but sometimes can be necessary ..)

      using :  LOCK TABLE table IN EXCLUSIVE MODE

    for each table accessed from query

     

    2) Startup the query that will be suspended form the lock on tables accessed + kill the sid,serial#,@Inst_id for the query.

    3) UNLOCK tables from EXCLUSIVE using "ROLLBACK"  in the session where LOCK TABLE.... was send.(to remake  tables to work for other queries)

     

    Any other ideas?

    Thanks

     

    Paolo

     

    you're planning on using locks to stop a query and you think in order to do so you need exclusive locks  on every table accessed in the query? And you are prepared to do that on a production system?

    And all of this is needed to troubleshoot a query that was running for 10 days -- i.e. a query that was available for all kinds of diagnostics during 10 days?

     

    Sorry, I think it wasn't a good idea for you to read that Osborne's blog post -- you should've started with more basic things. Way more basic.

     

    Best regards,

      Nikolay

  • 13. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    Mihael Pro
    Currently Being Moderated

    You can set event 10237 in remote session.

  • 14. Re: Is there a way to stop a query just after the cursor/plan is produced by CBO?
    Richard Harrison . Expert
    Currently Being Moderated

    Hi,

    It might be possible to use the max_est_exec_time facility of resource manager to do this - not 100% sure if it would work.It surely has to parse everything fully to get a real time estimate but It should throw an error and not actually start doing anything

     

    Give it a try and tell us if it works.....

     

    DBMS_RESOURCE_MANAGER

     

    Cheers,

    Harry

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points