GATHER_PLAN_STATISTICS hint

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

GATHER_PLAN_STATISTICS hint

MCALLISTER, MICHAEL CTR AU Contractor AETC AETC/A3IS
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:                    [hidden email]



smime.p7s (7K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

RE: GATHER_PLAN_STATISTICS hint

MCALLISTER, MICHAEL CTR AU Contractor AETC AETC/A3IS
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: [hidden email] [mailto:[hidden email]] On Behalf Of
MCALLISTER, MICHAEL CTR AU Contractor AETC AETC/A3IS
Sent: Wednesday, August 01, 2012 3:47 PM
To: [hidden email]
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:                    [hidden email]



smime.p7s (7K) Download Attachment