14 Replies Latest reply: Aug 4, 2014 10:51 AM by rp0428 RSS

    Confused with dbms_xplan.display

    Chinnu379

      I'm using oracle 11g. Just started practicing index. For this i just created a table with 10000000 rows. With out creating index i searched for 4568754th record, in the explain plan it was displaying 28 rows only.

      My doubt:

      Oracle will do sequential search if we wont' create any index on the table. In the above example searched rows should be 4568743 but why it was showing 28 only?

      After creating unique index it has searched only 1 row. I can see the diff in %CPU and time taken to execute but number of rows scanned only the confusion for me.

      Can anyone please explain..If my understanding was wrong?

        • 1. Re: Confused with dbms_xplan.display
          SomeoneElse

          Wow, it would help if you posted the actual code you tried.  The sql statements as well as the explain plans.

          • 2. Re: Confused with dbms_xplan.display
            rp0428
            I'm using oracle 11g. Just started practicing index. For this i just created a table with 10000000 rows. With out creating index i searched for 4568754th record, in the explain plan it was displaying 28 rows only.

            There is no concept of a '4568754th row in a table unless you created a column that have sequential numbers in it. Even then Oracle does NOT necessary store rows in any particular order so what did you do?

            Oracle will do sequential search if we wont' create any index on the table. In the above example searched rows should be 4568743 but why it was showing 28 only?

            Oracle will do a FULL TABLE SCAN if there are no indexes since there is no other way to look for the data. If you did NOT collect any stats Oracle has no way of knowing how many rows are in the table.

            After creating unique index it has searched only 1 row. I can see the diff in %CPU and time taken to execute but number of rows scanned only the confusion for me.

            Of course - a unique index means ONE AND ONLY ONE row exists for a given key value.

            Have you ever used a telephone book? If the telephone book was in RANDOM order and had NO index or names at the top of each page how many pages would you have to look at to find a given person?

             

            Now if there was an index that gave the page number and line number of each person how many pages would you have to look at?

            • 3. Re: Confused with dbms_xplan.display
              Chinnu379

              Thanks for your reply.

               

              I understood the index concept but my confusion was,  I had created a table with 1000000 rows(all are unique) .  i'm searching for 987654record with out creating index.

              In this case it should search entire table for that record . I mean in the explain no.of rows scanned should be 1000000. but why was it displaying 28 rows.

               

               

               

              SQL> explain plan for select * from ss where num=987654;

               

               

              Explained.

               

               

              SQL> select * from table(dbms_xplan.display);

               

               

              PLAN_TABLE_OUTPUT

              --------------------------------------------------------------------------------

              Plan hash value: 3165684048

               

               

              --------------------------------------------------------------------------

              | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

              --------------------------------------------------------------------------

              |   0 | SELECT STATEMENT  |      |    28 |  1820 |   702   (3)| 00:00:09 |

              |*  1 |  TABLE ACCESS FULL| SS   |    28 |  1820 |   702   (3)| 00:00:09 |

              --------------------------------------------------------------------------

               

               

              Predicate Information (identified by operation id):

              ---------------------------------------------------

               

               

              PLAN_TABLE_OUTPUT

              --------------------------------------------------------------------------------

               

               

                 1 - filter("NUM"=987654)

               

               

              Note

              -----

                 - dynamic sampling used for this statement (level=2)

               

               

              17 rows selected.

              • 4. Re: Confused with dbms_xplan.display
                Chinnu379

                Thanks for your reply.

                 

                I understood the index concept but my confusion was,  I had created a table with 1000000 rows(all are unique) .  i'm searching for 987654record with out creating index.

                In this case it should search entire table for that record . I mean in the explain no.of rows scanned should be 1000000. but why was it displaying 28 rows.

                 

                 

                 

                SQL> explain plan for select * from ss where num=987654;

                 

                 

                Explained.

                 

                 

                SQL> select * from table(dbms_xplan.display);

                 

                 

                PLAN_TABLE_OUTPUT

                --------------------------------------------------------------------------------

                Plan hash value: 3165684048

                 

                 

                --------------------------------------------------------------------------

                | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

                --------------------------------------------------------------------------

                |   0 | SELECT STATEMENT  |      |    28 |  1820 |   702   (3)| 00:00:09 |

                |*  1 |  TABLE ACCESS FULL| SS   |    28 |  1820 |   702   (3)| 00:00:09 |

                --------------------------------------------------------------------------

                 

                 

                Predicate Information (identified by operation id):

                ---------------------------------------------------

                 

                 

                PLAN_TABLE_OUTPUT

                --------------------------------------------------------------------------------

                 

                 

                   1 - filter("NUM"=987654)

                 

                 

                Note

                -----

                   - dynamic sampling used for this statement (level=2)

                 

                 

                17 rows selected.rp0428

                • 5. Re: Confused with dbms_xplan.display
                  Gaff

                  I strongly suspect what you are seeing is that Oracle doesn't have enough information to come up with an accurate estimate.  If you haven't gathered statistics for the table then Oracle's guess as to how many rows, cardinality, etc. will be way off.  Hence, the introduction in 9i of Dynamic Sampling.

                   

                  So while your plan shows 20 rows and Oracle doing a full table scan, only the second half of that statement is accurate - assuming of course that you inserted all of the rows you think that you inserted.  You can either try doing what they did in the article I linked to (Alter session set optimizer_dynamic_sampling=4;) or gather statistics on the table.  If you gather statistics but create no indexes it'll still do a FTS but at least the plan should show the proper number of rows.

                  • 6. Re: Confused with dbms_xplan.display
                    SomeoneElse

                    > I mean in the explain no.of rows scanned should be 1000000. but why was it displaying 28 rows.

                     

                    That's not what that line means in the explain plan.

                     

                    It means the number of rows expected to be returned by that operation, not the number of rows "scanned".

                    • 7. Re: Confused with dbms_xplan.display
                      Gaff

                      To OP - the explain plan shows the execution plan that Oracle will use based on what it knows about the tables involved (which in your case is nothing or worse than nothing since no stats exist).  It's a "best guess" for a plan of attack based on what it thinks it knows about your tables and how much certain operations "cost" (it's a "cost based" optimizer).  If you turn on tracing then Oracle will log in the trace file what it actually did to get your data for you.

                      • 8. Re: Confused with dbms_xplan.display
                        rp0428
                        I strongly suspect what you are seeing is that Oracle doesn't have enough information to come up with an accurate estimate.

                        I strongly suspect that you are confused.

                         

                        OP is the one with the problem - you should direct your replies to them.

                        • 9. Re: Confused with dbms_xplan.display
                          Gaff

                          I was.  I was merely adding detail to your post!  No need to start from scratch!

                           

                          • 10. Re: Confused with dbms_xplan.display
                            Chinnu379

                            @@@@@@hi all,

                             

                            Could we please come to one conclusion.?

                             

                            First created table with 1000000 table (all are unique rec). not created any index on that. Did compute statistics. set the optimizer_dynamic_sampling to 4.

                             

                            Then started serching for record 987654. But in the explain plan it was showing 1 row

                             

                             

                             

                            PLAN_TABLE_OUTPUT

                            --------------------------------------------------------------------------------

                            Plan hash value: 3048397475

                            --------------------------------------------------------------------------------

                            ------

                            | Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time

                                 |

                            --------------------------------------------------------------------------------

                            ------

                            PLAN_TABLE_OUTPUT

                            --------------------------------------------------------------------------------

                            |   0 | SELECT STATEMENT  |                  |     1 |    11 |   629   (3)| 00:0

                            0:08 |

                             

                            |*  1 |  TABLE ACCESS FULL| BTREE_NON_UNIQUE |     1 |    11 |   629   (3)| 00:0

                            0:08 |

                            --------------------------------------------------------------------------------

                            ------

                            Predicate Information (identified by operation id):

                            PLAN_TABLE_OUTPUT

                            --------------------------------------------------------------------------------

                            ---------------------------------------------------

                             

                               1 - filter("NUM"=987654)

                             

                            rp0428PL/SQL

                             

                             

                            So in the explain plan rows means number of rows returned? not number of rows scanned? will it internally searches full table for the record given in where condition?

                            • 11. Re: Confused with dbms_xplan.display
                              Gaff

                              The "rows" shows how many rows were returned from the step.  You have just one step in this case, a FULL TABLE SCAN.  The FULL TABLE SCAN tells you what it is doing.  The "rows" is what you got back.

                              • 12. Re: Confused with dbms_xplan.display
                                Chinnu379

                                Thanks for ur reply..

                                 

                                I'm trying like this..

                                 

                                SQL> select count(*) from ss where num like '%87654';

                                  COUNT(*)

                                ----------

                                        10

                                SQL> explain plan for select * from ss where num like '%87654';

                                 

                                Explained.

                                 

                                SQL> select * from table(dbms_xplan.display());

                                 

                                PLAN_TABLE_OUTPUT

                                --------------------------------------------------------------------------------

                                Plan hash value: 3165684048

                                --------------------------------------------------------------------------

                                | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

                                --------------------------------------------------------------------------

                                |   0 | SELECT STATEMENT  |      |    66 |   726 |   710   (4)| 00:00:09 |

                                |*  1 |  TABLE ACCESS FULL| SS   |    66 |   726 |   710   (4)| 00:00:09 |

                                --------------------------------------------------------------------------

                                Predicate Information (identified by operation id):

                                ---------------------------------------------------

                                PLAN_TABLE_OUTPUT

                                --------------------------------------------------------------------------------

                                   1 - filter(TO_CHAR("NUM") LIKE '%87654')

                                 

                                Note

                                -----

                                   - dynamic sampling used for this statement (level=4)

                                 

                                 

                                As you told earlier. rows means no.of rows returned. But in the above case why it was showing 66 instead of 10..?

                                 

                                Could anyone please share any link to get a better understanding on explain plan   rp0428PL/SQLConfused with dbms_xplan.display

                                • 13. Re: Confused with dbms_xplan.display
                                  rp0428
                                  Could anyone please share any link to get a better understanding on explain plan 

                                  See this white paper 'Explain the explain plan' by Maria Colgan - former product manager for the optimizer group.

                                  http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf

                                  • 14. Re: Confused with dbms_xplan.display
                                    Mark D Powell

                                    Chinnu379, the explain plan rows column in NOT the actual number of rows returned.  It is an estimate of the operation cardinality which per the reference RP posted is "is the estimated number of rows that will be returned by each operation"

                                    - -

                                    The estimate is based on the dynamic sampling operation identified in you explain plan output.  Run dbms_stats using various sample sizes and run explain again after each and see how the number changes.

                                    - -

                                    HTH -- Mark D Powell --