4 Replies Latest reply: Jun 26, 2013 7:30 AM by 1016493 RSS

    Query plan normalization in statspack

    1016493

      Hello,

      I understand statspack (from Oracle) can display query plan information with snap level 6. I understand that if there is a variation in plan statistics then the same query plan will have multiple entries (same hash value) for same query plan in the statspack report, for the given time period betwen snapshots.

      If there are two query plans which are identical but have differences in where clause, are they considered different in statspack or "normalized" to one plan? If they are normalized, then is every value of plan node (estimated and actual cost) averaged in normalized plan?

      Thank you Sameer

        • 1. Re: Query plan normalization in statspack
          Jonathan Lewis

          44849b9b-03d8-4edd-bb18-e7f3e4fa4c6d wrote:

           

          If there are two query plans which are identical but have differences in where clause, are they considered different in statspack or "normalized" to one plan? If they are normalized, then is every value of plan node (estimated and actual cost) averaged in normalized plan?

           

           

          Statspack (and AWR) accumulate SQL statistics by sql_id (hash_value) and plan_hash_value. If two child cursors for a query have the same plan_hash_value their results will be aggregated.  In fact, if you're using statspack, it used to aggregate ALL the child cursor statistics - even if there were multiple different plans. (I haven't checked that for the latest versions of Oracle, though).

           

          Regards

          Jonathan Lewis

          • 2. Re: Query plan normalization in statspack
            1016493

            I am sorry to respond again so late. I was experimenting a bit with statspack on Oracle 11g XE.

            In my simple experiment:

            I have table Orders which returns rows for 2 queries which should be normalized i.e.

            select * from orders where order_name='Chicken';

            select * from orders where order_name='Fish';

             

            So i execute these 2 queries between 2 snaphots. In the view provided by spreport i can see the old hash value for both the queries is different. When i use sprepsql using old hash value of each query i get two reports of query plans.

             

            In each report the query plan hash value is the same. This i understand as the plans are normalized.

             

            I also see that numbers related to operations are exactly the same as well as shown below

             

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

            | Operation                      | PHV/Object Name     | Rows | Bytes|   Cost |

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

            |SELECT STATEMENT                |----- 1275100350 ----|       | |     16 |

            |TABLE ACCESS FULL               |ORDERS               |    10K| 126K|     16 |

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

             

            I thought they would be aggregated (added up) or is this the aggregated numbers i am seeing?

             

            regards

            Sameer

            • 3. Re: Query plan normalization in statspack
              Jonathan Lewis

              The queries are different, so they have different values for sql_id; so their execution plans are captured separately, and it's simply a coincidence that their plans are identical.  Remember, two queries can have the same plan but do hugely different amounts of work, so for trouble-shooting after the event you don't want to sum across plans, you want to sum across the combination of query and plan.

               

              Regards

              Jonathan Lewis

               


              • 4. Re: Query plan normalization in statspack
                1016493

                Thank you for the prompt response.

                Another question:

                Consider the SQL select * from orders where order_name='Chicken';  and following scanrio

                 

                1. 1. take starting snapshot
                2. 2.execute this SQL,
                3. 3. then thousands of rows with Chicken are added i.e. statistics regarding this SQL change which results in change in query plan
                4. 4. execute this SQL, again
                5. 5. take ending snapshot

                 

                For the query plan view for this given SQL ID will i see multiple plan hash values? And for each plan hash value i will see corresponding entry tables for operation costs?

                1. i.e.

                First First Last           Plan

                Snap Id     Snap Time      Active Time    Hash Value        Cost

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

                       26 24-Jun-13 14:57 24-Jun-13 14:56 1275100350        16

                       26 24-Jun-13 14:57 24-Jun-13 14:56 1275100351        56

                 

                and

                 

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

                | Operation | PHV/Object Name     |  Rows | Bytes| Cost |

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

                |SELECT STATEMENT |----- 1275100350 ----| |      |     16 |

                |TABLE ACCESS FULL |ORDERS |    10K|  126K|     16 |

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

                 

                 

                 

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

                | Operation | PHV/Object Name     |  Rows | Bytes| Cost |

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

                |SELECT STATEMENT |----- 1275100351 ----| |      |     56 |

                |TABLE ACCESS FULL |ORDERS |    100K|  1260K|     56 |

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

                 

                Also, will the SQL statistics will show the usual per execute (avg between the 2 executions) and total values?

                 

                % Snap

                Statement Total      Per Execute   Total

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

                     

                regards

                Sameer