Home » RDBMS Server » Performance Tuning » What is difference between 2 statements? (Oracle 10g, cross platform )
What is difference between 2 statements? [message #376916] Fri, 19 December 2008 02:53 Go to next message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi all!

I have one question, of course, I've searched in google and in orafaq_tuning_guide. However, I've not got the right answer, so that, I post this at here.

metatest@META> create table objects as
  2  select * from sys.all_objects;

Table created.

metatest@META> create index idx_obj_id on objects(object_id);

Index created.

metatest@META> create index idx_obj_type on objects(object_type);

Index created.


You will see the

metatest@META> set autotrace traceonly
metatest@META> select * from objects
  2  where object_type like '%ABLE';

1931 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 80137940

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |  2211 |   276K|   202   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| OBJECTS |  2211 |   276K|   202   (1)| 00:00:03 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_TYPE" LIKE '%ABLE')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        248  recursive calls
          0  db block gets
        940  consistent gets
          0  physical reads
          0  redo size
      96670  bytes sent via SQL*Net to client
       1808  bytes received via SQL*Net from client
        130  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1931  rows processed

metatest@META>


Yeap! Full table scan..

metatest@META> select * from objects
  2  where object_type like 'TAB%';

2079 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1205454014

--------------------------------------------------------------------------------
------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
| Time     |
--------------------------------------------------------------------------------
------------
|   0 | SELECT STATEMENT            |              |  2079 |   259K|    96   (0)
| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| OBJECTS      |  2079 |   259K|    96   (0)
| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | IDX_OBJ_TYPE |  2079 |       |     8   (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_TYPE" LIKE 'TAB%')
       filter("OBJECT_TYPE" LIKE 'TAB%')

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        498  consistent gets
         11  physical reads
          0  redo size
     105936  bytes sent via SQL*Net to client
       1918  bytes received via SQL*Net from client
        140  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2079  rows processed

metatest@META>

Index range scan...

Statistics:

FTS Idx RS
248 recursive calls vs 8 recursive call
940 consistent gets vs 498 consistent gets
0 physical reads vs 11 physical reads
96670 bytes sent via SQL*Net to client vs 105936 bytes sent via SQL*Net to client
130 SQL*Net roundtrips to/from client vs 140 SQL*Net roundtrips to/from client
1931 rows processed vs 2079 rows processed


Of course, in 2 statements, the Index_Range_Scan had little Cost CPU and elapsed times to the other.
However, Why the first statement did not use the index but the second did?
Why the second statement has had more physical reads than the first?
In one purpose, why did the first statement only got 1931 rows but the second scanned 2079 rows?


Full table scan is not always evil, of course, this is in the case that table does not contain more rows.


May you clarify more?

Thank you!

[Updated on: Fri, 19 December 2008 02:59]

Report message to a moderator

Re: What is difference between 2 statements? [message #376927 is a reply to message #376916] Fri, 19 December 2008 03:28 Go to previous messageGo to next message
yaseendba
Messages: 2
Registered: December 2008
Location: Chennai
Junior Member




I think Object Id having constraint so only the diff

check the two column what is the diff


u can itself understand why and where performance Lack ...


Thanks
Yaseen AR
Re: What is difference between 2 statements? [message #376928 is a reply to message #376916] Fri, 19 December 2008 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
However, Why the first statement did not use the index but the second did?

Take a dictionary, how can you find all words ending by ABLE without lookgin at all pages?
Now, take the same dictionary, you can directly go to the page containing the words starting with TAB, doesn't it.
This is the same thing for Oracle.

Regards
Michel
Re: What is difference between 2 statements? [message #377159 is a reply to message #376928] Sat, 20 December 2008 21:56 Go to previous message
trantuananh24hg
Messages: 744
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
oh, thank you, Michel.
You make me feeling as a sheep myself! That's mistake about my understanding.

Thanks again!
Previous Topic: poor performance of Exchange partition on composite range-hash tables
Next Topic: Query tuning
Goto Forum:
  


Current Time: Mon Dec 09 20:05:13 CST 2024