9 Replies Latest reply: Feb 18, 2013 8:32 AM by Dom Brooks RSS

    Tuning a query - "A-rows" are wrong

    Chiwatel
      Hi All,

      I am using 11gR2. I have 2 questions about a very simple query. My query is the following

      select * from fac_daily_trans_perform f, dim_performance p where f.performance_key = p.performance_key;

      The table fac_daily_trans_perform has 635893 rows (and it is partitioned by interval) and the table dim_performance has 5 rows.

      My 2 questions are the following:

      1. Even if statistics have been generated, the "A rows" are wrong. On the plan it is 50 but it should be 635 K (The Estimated rows are correct). Do you know why this column is always wrong when I generate a plan ?

      2. My second question is pretty general, what would be the best way to tune such a query i.e. to tune a simple join (but which returns many rows) ?

      Many thanks.
      "PLAN_TABLE_OUTPUT"
      "SQL_ID  a48hf3bhfbw25, child number 0"
      "-------------------------------------"
      "select * from fac_daily_trans_perform f, dim_performance p  where "
      "f.performance_key = p.performance_key"
      " "
      "Plan hash value: 3635464132"
      " "
      "-------------------------------------------------------------------------------------------------------------------------------------"
      "| Id  | Operation            | Name                    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |"
      "-------------------------------------------------------------------------------------------------------------------------------------"
      "|   0 | SELECT STATEMENT     |                         |      1 |        |     50 |00:00:00.01 |       6 |       |       |          |"
      "|*  1 |  HASH JOIN           |                         |      1 |    635K|     50 |00:00:00.01 |       6 |   843K|   843K|     1/0/0|"
      "|   2 |   TABLE ACCESS FULL  | DIM_PERFORMANCE         |      1 |      5 |      5 |00:00:00.01 |       2 |       |       |          |"
      "|   3 |   PARTITION RANGE ALL|                         |      1 |    635K|     50 |00:00:00.01 |       4 |       |       |          |"
      "|   4 |    TABLE ACCESS FULL | FAC_DAILY_TRANS_PERFORM |      3 |    635K|     50 |00:00:00.01 |       4 |       |       |          |"
      "-------------------------------------------------------------------------------------------------------------------------------------"
      " "
      "Predicate Information (identified by operation id):"
      "---------------------------------------------------"
      " "
      "   1 - access("F"."PERFORMANCE_KEY"="P"."PERFORMANCE_KEY")"
      " "
        • 1. Re: Tuning a query - "A-rows" are wrong
          Fran
          1. Even if statistics have been generated, the "A rows" are wrong. On the plan it is 50 but it should be 635 K (The Estimated rows are correct). Do you know why this column is always wrong when I generate a plan ?
          if you don't gather new statistics, you had the old ones. Oracle get statistics automatically, but if you want do it manually you must use "dbms_stats"
          for more info:
          http://docs.oracle.com/cd/E11882_01/server.112/e16638/stats.htm#i41282
          2. My second question is pretty general, what would be the best way to tune such a query i.e. to tune a simple join (but which returns many rows) ?
          Depends the excution plan, what does it say?
          usually "create index" should be the best way.
          Chiwatel     
                
                
          Handle:      Chiwatel  
          Status Level:      Newbie
          Registered:      Dec 31, 2012
          Total Posts:      24
          Total Questions:      10 (9 unresolved)
          Location      Paris
          why so many unresolved questions?
          remember close your threads marking them as answered when your question will be solved. keep clean the forum
          • 2. Re: Tuning a query - "A-rows" are wrong
            Chiwatel
            Hi Fran,

            Thanks for your answer.

            The statistics has been gathered with dbms_stats just before executing the query. So it was very "fresh".

            Any more ideas ?

            Thanks.

            PS : I will close the previous questions.
            • 3. Re: Tuning a query - "A-rows" are wrong
              Fran
              please, post step by step how you did it, and querys you have been used.
              The statistics has been gathered with dbms_stats just before executing the query
              You did it before the update? You should take new statistics after

              Edited by: Fran on 18-feb-2013 5:05
              • 4. Re: Tuning a query - "A-rows" are wrong
                Jonathan Lewis
                Chiwatel wrote:

                1. Even if statistics have been generated, the "A rows" are wrong. On the plan it is 50 but it should be 635 K (The Estimated rows are correct). Do you know why this column is always wrong when I generate a plan ?
                The A-rows can't be wrong - Oracle is simply counting the number of rows that passed through the rowsource. The fact that the value you see is 50 (a suspiciously round number) makes me think that your front-end tool fetched 50 rows and then stopped fetching.
                2. My second question is pretty general, what would be the best way to tune such a query i.e. to tune a simple join (but which returns many rows) ?
                Configure the network to allow the maximum throughput, configure SQL*Net to take advantage of the available network bandwidth, set the array fetch size to take best advantage of the SQL*Net configuration. Don't fetch columns you don't need, don't fetch rows you don't need. Check that you're using the optimum execution plan for the data you really need to fetch.

                Regards
                Jonathan Lewis
                • 5. Re: Tuning a query - "A-rows" are wrong
                  Chiwatel
                  Fran,

                  There is no updates. All the data are there and before running my query I executed the DBMS_STATS with this command for the 2 tables involved in my query :

                  BEGIN
                  dbms_stats.gather_table_stats(ownname => 'TOTO',
                  tabname => 'FAC_DAILY_TRANS_PERFORM',
                  estimate_percent => 100,
                  method_opt => 'for all columns size skewonly',
                  cascade => TRUE);
                  END;
                  /

                  So the statistics should be up to date and when I checked the column LAST_ANALYZED in the USER_TABLES, it shows today's date.

                  Thanks.
                  • 6. Re: Tuning a query - "A-rows" are wrong
                    Chiwatel
                    Hello Jonathan,

                    Thanks very much for your reply (I love your books!)

                    Actually, I used SQL Developer to run my query and I remember that the tool returns the first 50 rows ! So I guess this is why the "A-Rows" show 50 too. Thanks for clearing this up !

                    But now, as I want to check if my "A-rows" and my "E-rows" are pretty much the same, how can I get my "real" "A-rows" ?

                    Many thanks.
                    • 7. Re: Tuning a query - "A-rows" are wrong
                      Fran
                      I'm with Jonathan Lewis, A-row can't be wrong.
                      SQL> explain plan for select * from test1;
                      
                      Explicado.
                      
                      SQL> select * from table(dbms_xplan.display);
                      
                      PLAN_TABLE_OUTPUT
                      --------------------------------------------------------------------------------
                      Plan hash value: 4122059633
                      
                      ---------------------------------------------------------------------------
                      | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
                      ---------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT  |       |     5 |    40 |     3   (0)| 00:00:01 |
                      |   1 |  TABLE ACCESS FULL| TEST1 |     5 |    40 |     3   (0)| 00:00:01 |
                      ---------------------------------------------------------------------------
                      
                      8 filas seleccionadas.
                      
                      SQL> select * from test1;
                      
                              ID NAME
                      ---------- -------------------------
                               1 FRAN
                               2 JOHN
                               3 KING
                               4 STEVE
                               4 JOHN
                      SQL> insert into test1 values(7, 'MATHEW');
                      
                      1 fila creada.
                      
                      SQL> insert into test1 values(6, 'ANDY');
                      
                      1 fila creada.
                      
                      SQL> COMMIT;
                      
                      Confirmaci¾n terminada.
                      
                      SQL> begin
                        2  dbms_stats.gather_table_stats(ownname => 'FRAN', tabname => 'TEST1', estimate_percent => 100, cascade =>
                      TRUE);
                        3  end;
                        4  /
                      
                      Procedimiento PL/SQL terminado correctamente.
                      
                      SQL> explain plan for select * from test1;
                      
                      Explicado.
                      
                      SQL> select * from table(dbms_xplan.display);
                      
                      PLAN_TABLE_OUTPUT
                      --------------------------------------------------------------------------------
                      Plan hash value: 4122059633
                      
                      ---------------------------------------------------------------------------
                      | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
                      ---------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT  |       |     7 |    56 |     3   (0)| 00:00:01 |
                      |   1 |  TABLE ACCESS FULL| TEST1 |     7 |    56 |     3   (0)| 00:00:01 |
                      ---------------------------------------------------------------------------
                      
                      8 filas seleccionadas.
                      • 8. Re: Tuning a query - "A-rows" are wrong
                        Dom Brooks
                        But now, as I want to check if my "A-rows" and my "E-rows" are pretty much the same, how can I get my "real" "A-rows" ?
                        You need to fetch all the data.
                        In SQL Developer, you could F5 (run as script) rather than F9 (run)
                        • 9. Re: Tuning a query - "A-rows" are wrong
                          Chiwatel
                          O, thanks Dom.