1 2 Previous Next 22 Replies Latest reply: Jun 17, 2014 3:28 AM by Ramin Hashimzadeh Go to original post RSS
      • 15. Re: Reg: finding the root cause step-by-step...
        ranit B

        Hi Mohamed,

        And as Dom Brooks has already pointed it out, the CBO is not correctly estimating operation 3  (TABLE ACCESS FULL   | gpts) as far as it is predicting 400K while this operation produced only 934 rows

        As I was telling to Hemant, I stopped the query fetch at 191 rows. Might be that is the reason for the difference of count in Estimate-rows and Available-rows.

         

        Just a guess.

        • 16. Re: Reg: finding the root cause step-by-step...
          Hemant K Chitale

          You need to fetch upto the last row.  sqlplus does this by default.

          You can use SET TIMING ON.

          However, with usage of the GATHER_PLAN_STATISTICS hint you can run

          select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

          after the query is executed.

           

          NOTE : When fetching all the rows, the elapsed time (which people seem to believe is execution time) also includes the overhead to transfer all the rows over the network and the overhead to display the rows on terminal and/or spool to a disk file on the client (IDE or sqlplus).


          Hemant K Chitale

           

          Message was edited by: Hemant K Chitale Added the note about elapsed time

          • 17. Re: Reg: finding the root cause step-by-step...
            ranit B

            However, with usage of the GATHER_PLAN_STATISTICS hint you can run

            select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

            after the query is executed.

            I believe in this case also, we need to fetch all rows first and then execute the above, right? Else it will be the same case as 191 rows.

            Hemant K Chitale wrote:

             

            You need to fetch upto the last row.  sqlplus does this by default.

            You can use SET TIMING ON.

             

            NOTE : When fetching all the rows, the elapsed time (which people seem to believe is execution time) also includes the overhead to transfer all the rows over the network and the overhead to display the rows on terminal and/or spool to a disk file on the client (IDE or sqlplus).

             

            Good point, Hemant. Thanks.

             

            So, can i do this on SQL*Plus:

            SQL> set autotrace trace
            SQL>
            SQL>
            SQL>
            SQL> select user from dual;

            Elapsed: 00:00:01.38

             

            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 1388734953

            -----------------------------------------------------------------
            | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
            -----------------------------------------------------------------
            |   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
            |   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
            -----------------------------------------------------------------


            Statistics
            ----------------------------------------------------------
                      0  recursive calls
                      0  db block gets
                      0  consistent gets
                      0  physical reads
                      0  redo size
                    350  bytes sent via SQL*Net to client
                    363  bytes received via SQL*Net from client
                      2  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      1  rows processed

            I am not displaying the rows on IDE and elapsed time can be seen with TIMING ON.

            But one concern - It is just not displaying on IDE, I guess, but is being fetched over the network.

             

            Right?

            • 18. Re: Reg: finding the root cause step-by-step...
              Hemant K Chitale

              >I believe in this case also, we need to fetch all rows first and then execute the above, right?

              Yes, of course.

               

              >But one concern - It is just not displaying on IDE

              Then your elapsed time doesn't include that overhead  (which may be present when using sqlplus unless you use SET TERMOUT OFF in sqlplus)

               

               

              Hemant K Chitale


              • 19. Re: Reg: finding the root cause step-by-step...
                ranit B

                Then your elapsed time doesn't include that overhead  (which may be present when using sqlplus unless you use SET TERMOUT OFF in sqlplus)

                Nice, didn't know about this. I always used SET AUTOTRACE TRACE. Thanks Hemant.

                 

                Now, how do you make out that the optimizer is not making correct estimates?

                AFAIK -

                1) If the A-rows doesn't match with A-rows.

                2) If the cardinality (i.e. "rows" columns) shows very less no of rows than is present in table or index. So, it might require gathering of statistics.

                 

                3) When do you feel the need to look into advanced things like ASH, AWR, etc.

                 

                Please advise.

                • 20. Re: Reg: finding the root cause step-by-step...
                  Hemant K Chitale

                  >how do you make out that the optimizer is not making correct estimates?

                  If there is a mismatch between expected rows and actual rows

                   

                  >So, it might require gathering of statistics.

                  There persists a myth that updating statistics results in accurate cardinality.  Not necessarily true.  Inter-column dependencies, complex conditions, multiple predicates, nulls, functions, bound-conditions etc can result in cardinality estimates being different from actuals.  You have to know that sometimes you can't expect cardinality estimates to be accurate.

                   

                   

                  Hemant K Chitale


                  • 21. Re: Reg: finding the root cause step-by-step...
                    ranit B

                    Sorry for the late response Ramin.

                     

                    Yes, I am planning to create an index on GPTS.OCC_LNO. But doesn't have access to database currently.

                     

                    So, was trying to understand the psychology behind tracking query performance related issues. Please share your thoughts regarding the same.

                     

                    There are so many features and parameters to consider for performance related issues, and all of them are confusing me somehow. Trying to understand the correct way and steps that experts follow to diagnose them.

                    • 22. Re: Reg: finding the root cause step-by-step...
                      Ramin Hashimzadeh

                      ranit B wrote:

                       

                      Sorry for the late response Ramin.

                       

                      Yes, I am planning to create an index on GPTS.OCC_LNO. But doesn't have access to database currently.

                       

                      So, was trying to understand the psychology behind tracking query performance related issues. Please share your thoughts regarding the same.

                       

                      There are so many features and parameters to consider for performance related issues, and all of them are confusing me somehow. Trying to understand the correct way and steps that experts follow to diagnose them.

                      I gave you suggestion in my first post to which way you should look

                      Re: Reg: finding the root cause step-by-step...

                      and from your execution plan clearly seen that there is not enough index for GPTS.OCC_LNO.
                      But after I did not continue to follow the topic and lost discussion.


                      ----

                      Ramin Hashimzade

                      1 2 Previous Next