2 Replies Latest reply: Nov 26, 2012 11:37 AM by Dom Brooks RSS

    which one is better

    francy77
      Hi,
      i've set autotrace to traceonly, and run 2 query on two different table that contain the same data.But i'm not able to understand which query is better and why?
      in the first query i get "*825 consistent gets*" and in the second one i get "813 consistent gets", but what is the meaning?
      the first query "161932 bytes sent via SQL*Net to client" but in the second one "598324 bytes sent via SQL*Net to client". Why???



      I report the sql*plus output:
      SQL> select * from eventi_pm where fl_stato_accodamento='0';
      
      Selezionate 5107 righe.
      
      
      Piano di esecuzione
      ----------------------------------------------------------
      Plan hash value: 2338566045
      
      ------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
      ------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                   |                   |     1 |   119 |    10   (0)| 00:00:01 |       |       |
      |   1 |  PARTITION HASH ALL                |                   |     1 |   119 |    10   (0)| 00:00:01 |     1 |     4 |
      |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| EVENTI_PM         |     1 |   119 |    10   (0)| 00:00:01 |     1 |     4 |
      |*  3 |    INDEX RANGE SCAN                | EVENTI_PM_IDX_003 |     1 |       |     9   (0)| 00:00:01 |     1 |     4 |
      ------------------------------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - access("FL_STATO_ACCODAMENTO"='0')
      
      
      Statistiche
      ----------------------------------------------------------
                0  recursive calls
                0  db block gets
              825  consistent gets
                0  physical reads
                0  redo size
           161932  bytes sent via SQL*Net to client
             4117  bytes received via SQL*Net from client
              342  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
             5107  rows processed
      the second query:
      SQL> select * from eventi_pm2 where fl_stato_accodamento='0';
      
      Selezionate 5107 righe.
      
      
      Piano di esecuzione
      ----------------------------------------------------------
      Plan hash value: 2814083279
      
      --------------------------------------------------------------------------------------------------
      | Id  | Operation                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |                    |  1486 |   172K|    39   (0)| 00:00:01 |
      |   1 |  TABLE ACCESS BY INDEX ROWID| EVENTI_PM2         |  1486 |   172K|    39   (0)| 00:00:01 |
      |*  2 |   INDEX RANGE SCAN          | EVENTI_PM_IDX_0032 |  1486 |       |     8   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("FL_STATO_ACCODAMENTO"='0')
      
      
      Statistiche
      ----------------------------------------------------------
                0  recursive calls
                0  db block gets
              813  consistent gets
                0  physical reads
                0  redo size
           598324  bytes sent via SQL*Net to client
             4117  bytes received via SQL*Net from client
              342  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
             5107  rows processed
      Please help me!!!!!
      thanks.
      Francesco
        • 1. Re: which one is better
          vijayrsehgal-Oracle
          Francesco,

          the difference in consistent gets seems to be due to first query using partitioned table and second query using non partitioned table. Consistent gets is read from memory. Also for the difference in bytes sent via sql*net please go through the below url may be it applies in your case.


          http://jonathanlewis.wordpress.com/2010/05/07/sqlnet-compression/
          • 2. Re: which one is better
            Dom Brooks
            http://mwidlake.wordpress.com/2009/06/02/what-are-consistent-gets/
            which is better
            Statements which say that they will return 1 row but actually return 5000 are generally not great.
            But perhaps this is not a fair comparison? Have you gathered statistics appropriately for both?

            The test is simple enough though so there's no reason to think that accurate statistics would make any difference to this particular test.

            Assuming the first table has 4 hash partitions, you're going to have to effectively do four individual index range scans of your local index.
            Whereas an index on a non partitioned table is a single index range scan of a bigger index.
            So, it's reasonable to expect the latter to be marginally more efficient.

            This test case is not one to highlight any advantage of hash partitioning - you're not partitiong pruning, you're not doing anything in parallel that could scan the partitions independently, etc.

            However, if the main benefit of the proposed partitioning was something else, then this test could be used to show that the impact of partitioning on this area (not the prime beneficiary) was minor.