Re: Waht deos following exxplain plan mean?

From: joel garry <joel-garry_at_home.com>
Date: Wed, 19 May 2010 10:50:31 -0700 (PDT)
Message-ID: <81852b7c-904b-41b1-969f-d2f0985a9d13_at_h37g2000pra.googlegroups.com>

On May 19, 6:39 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> On May 18, 5:31 pm, zigzagdna <zigzag..._at_yahoo.com> wrote:
>
>
>
> > See plan below, Does plan  mean a FULL TABLE  SCAN was done first,
> > result was then obtained from RESULT_CACHE or a  “FULL TABLE CAN” was
> > done on RESULT_CACHE,
> > I have run following statement several times and each time it gives me
> > same plan, so when does it directly get data from RESULT CACHE without
> > FULL TABLE SCAN.  Explain plan is quite confusing.
> > SELECT COMPLAINT_NO  FROM COMPLAINT_INFO WHERE  SUSP_COMPLAINT_NO =
> > 2000664
>
> > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > +++++
> > ++++ Bind vars ++++
> > | Operation                       | Name               | Cardinality |
> > Bytes | Time |
> > ---------------------------------------------------------------------------­---
> > | SELECT STATEMENT                |                    |
> > |        ||
> > |  RESULT CACHE                   |6yhyd84j36s2k0ua8p1 |
> > |        ||
> > |   TABLE ACCESS FULL             |COMPLAINT_INFO      |      1 |
> > 11 |340|
> > ---------------------------------------------------------------------------­---
>

Here is a good brief intro: http://www.oracle-developer.net/display.php?id=503

"...Interestingly, the execution plan remains the same (this is to be expected because the SQL is not optimised a second time) but is now slightly misleading. None of the plan operations actually take place once we have a reusable resultset, but the presence of the RESULT CACHE operation should alert us to the fact that we might already have a cached set of results. In fact, we can use the information supplied in this plan to verify the existence of a cached resultset for ourselves..."

> What tool or SQL are you using to generate the explain plan?  If you
> are not using a dbms_xplan.display call then you should manually to
> the explain using this call and post the results.
>
> I would expect the fact that the line RESULT CACHE appears in the plan
> means the result was found in the SQL Result Cache.  The only way to
> really know is to look at the number of GETS performed to solve the
> query.

You can also see some clues in tracing, Alex Fatkulin on his Pythian blog has some examples. But first one needs to learn explain plan.

jg

--
_at_home.com is bogus.
http://blamescoble.com/?name=oratwitter&id=101&submit=Blame

Received on Wed May 19 2010 - 12:50:31 CDT