Waht deos following exxplain plan mean?
in [Oracle]
|
From: jimmyb on 18 May 2010 17:40 On May 18, 2:31 pm, zigzagdna <zigzag...(a)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| > ------------------------------------------------------------------------------ Where is the result cache information section of the explain plan? This will provide info on how the result cache was used. It appears it did a FTS of complaint_info and returned 1 row to the result cache. But the explain plan is missing key information.
From: Mark D Powell on 19 May 2010 09:39 On May 18, 5:31 pm, zigzagdna <zigzag...(a)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 --
From: joel garry on 19 May 2010 13:50 On May 19, 6:39 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > On May 18, 5:31 pm, zigzagdna <zigzag...(a)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 -- @home.com is bogus. http://blamescoble.com/?name=oratwitter&id=101&submit=Blame
|
Pages: 1 Prev: Waht deos following exxplain plan mean? Next: 10g, broker and read-only account |