Loading ...
Sorry, an error occurred while loading the content.

GATHER_PLAN_STATISTICS hint

Expand Messages
  • MCALLISTER, MICHAEL CTR AU Contractor AET
    I just came across a tuning / investigative method using the GATHER_PLAN_STATISTICS hint as described here:-
    Message 1 of 2 , Aug 1, 2012
    • 0 Attachment
      I just came across a tuning / investigative method using the
      GATHER_PLAN_STATISTICS hint as described here:-

      https://blogs.oracle.com/optimizer/entry/how_do_i_know_if

      Usually when I come across this, I find out that TOAD has this already baked
      in. Is this the case here? Is there some automated method in TOAD to have it
      run a query, add the hint, and get the estimated vs actual rows plan info
      back for me?

      Regards,

      Mike McAllister
      Principal Systems Engineer
      Decypher
      DSN: 487-3751
      Commercial: (210) 652-3751
      Cell: (512) 423-7447
      Email: michael.mcallister.2.ctr.au@...
    • MCALLISTER, MICHAEL CTR AU Contractor AET
      OK, I ve made some progress myself here, but still need a hint. Here s what I discovered:- 1. If I right mouse click in the Explain Plan window, I can select
      Message 2 of 2 , Aug 2, 2012
      • 0 Attachment
        OK, I've made some progress myself here, but still need a hint. Here's what
        I discovered:-

        1. If I right mouse click in the "Explain Plan" window, I can select the
        option to "Load cached plan if possible". If I then run the query and hit
        ctrl-E in the explain plan window, I get the cached (executed) plan rather
        than the estimated plan.

        2. If I right mouse click in the "Explain Plan" window I can also select the
        option "DBMS_XPlan Format" option. I selected this, and then checked off:-
        2.1 Level = All
        2.2 Stats = Allstats, Last
        2.3 All default on the fine grained control.

        With the options above selected, I added the hint /*+ GATHER_PLAN_STATISTICS
        */ to my query, executed it, waited for it to finish, and then hit ctrl-E in
        my explain plan window. I ended up with much more information, but no A-Rows
        column. Any ideas why? Here's a cut and paste of the headings from the plan
        so you can see what was and wasn't displayed:-

        | Id | Operation | Name | E-Rows
        |E-Bytes|E-Temp | Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |

        Regards

        Mike

        -----Original Message-----
        From: toad@yahoogroups.com [mailto:toad@yahoogroups.com] On Behalf Of
        MCALLISTER, MICHAEL CTR AU Contractor AETC AETC/A3IS
        Sent: Wednesday, August 01, 2012 3:47 PM
        To: toad@yahoogroups.com
        Subject: [toad] GATHER_PLAN_STATISTICS hint

        I just came across a tuning / investigative method using the
        GATHER_PLAN_STATISTICS hint as described here:-

        https://blogs.oracle.com/optimizer/entry/how_do_i_know_if

        Usually when I come across this, I find out that TOAD has this already baked
        in. Is this the case here? Is there some automated method in TOAD to have it
        run a query, add the hint, and get the estimated vs actual rows plan info
        back for me?

        Regards,

        Mike McAllister
        Principal Systems Engineer
        Decypher
        DSN: 487-3751
        Commercial: (210) 652-3751
        Cell: (512) 423-7447
        Email: michael.mcallister.2.ctr.au@...
      Your message has been successfully submitted and would be delivered to recipients shortly.