This discussion is archived
9 Replies Latest reply: Feb 18, 2013 6:32 AM by Dom Brooks RSS

Tuning a query - "A-rows" are wrong

Chiwatel Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    O, thanks Dom.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points