Re: Waht deos following exxplain plan mean?

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 19 May 2010 06:39:08 -0700 (PDT)
Message-ID: <0ae88fca-f881-46d7-af20-f399fc830654_at_p17g2000vbe.googlegroups.com>

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|
> ---------------------------------------------------------------------------­---

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 could also query v$result-cache_objects to see how many rows of complaint_info are cached in '6yhyd84j36s2k0ua8p1' to help you guess. But I know of no way to be 100% certain unless the query run statistics provide the answer.

HTH -- Mark D Powell -- Received on Wed May 19 2010 - 08:39:08 CDT