This discussion is archived
2 Replies Latest reply: Nov 26, 2012 9:37 AM by Dom Brooks RSS

which one is better

francy77 Newbie
Currently Being Moderated
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
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

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