Home » RDBMS Server » Performance Tuning » Index Performance - autotrace on explain
Index Performance - autotrace on explain [message #64840] Wed, 18 February 2004 08:24 Go to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Hi,

I was browsing some code done here by the developers, and I realised that a table with few rows (500 rows) was joined with a table containing nearly half a million rows without index on the joining column of the table with half million rows (please, see the join statement below)!!!

After adding the index (see CREATE INDEX idx_sop_price_group_id), my EXPLAIN PLAN gives me actually more bytes used and higher values of COST (see below)?? Howcome? Am I interpreting badly the EXPLAIN PLAN values? How can I see in my EXPLAIN PLAN output, that AFTER is better (after created the index)???

DESCRIPTION    OBJECT NAME  COST CARDINALITY BYTES
-----------    -----------  ---- ----------- -----    
BEFORE:
-------
SELECT STATEMENT, GOAL = CHOOSE     2 1  16
NESTED LOOPS       2 1  16
  TABLE ACCESS FULL   SERVICE_OP_PRICE 1 1  7
    TABLE ACCESS BY INDEX ROWID  PRICE_GROUP  1 16  144
  INDEX UNIQUE SCAN   PRGR_PRICE_GROUP_ID_PK  16 

------------------------------------------------------------------------------------------------
AFTER:
------
SELECT STATEMENT, GOAL = CHOOSE     4 1  18
NESTED LOOPS       4 1  18
  TABLE ACCESS BY INDEX ROWID  SERVICE_OP_PRICE 3 1  9
    INDEX UNIQUE SCAN   CONS_SOP_UK  2 1 
  TABLE ACCESS BY INDEX ROWID  PRICE_GROUP  1 16  144
    INDEX UNIQUE SCAN   PRGR_PRICE_GROUP_ID_PK  16 

 

SELECT p.price, p.ext_price_msg    
FROM    SERVICE_OP_PRICE sop, PRICE_GROUP p
WHERE   sop.mobile_op_id = 1 AND sop.service_id = -1
AND       sop.price_group_id = p.price_group_id

------------------------------------------------------------------------------------------------
Existing on SERVICE_OP_PRICE:
-----------------------------
alter table SERVICE_OP_PRICE
  add constraint CONS_SOP_UK unique (SERVICE_ID,MOBILE_OP_ID)
  using index

Existing on PRICE_GROUP:
------------------------
alter table PRICE_GROUP
  add constraint PRGR_PRICE_GROUP_ID_PK primary key (PRICE_GROUP_ID)
  using index

alter table PRICE_GROUP
  add constraint PRGR_PRGR_OP_UNIQUE unique (PRICE_GROUP_ID,MOBILE_OP_ID)
  using index

Just added (See After) index on SERVICE_OP_PRICE to Optimize the Join performance!
----------------------------------------------------------------------------------
CREATE INDEX idx_sop_price_group_id
ON SERVICE_OP_PRICE (price_group_id)

 

SERVICE_OP_PRICE ~500000 rows; PRICE_GROUP ~500 rows.

What if I drop the Indexes on SERVICE_OP_PRICE and add this new one??:
--------------------------------------------------------------------
CREATE INDEX idx_sop_price_group_id
ON SERVICE_OP_PRICE (mobile_op_id, service_id, price_group_id)

Many thanks for your answers.

Regards,

Patrick Tahiri.
Re: Index Performance - autotrace on explain [message #64841 is a reply to message #64840] Wed, 18 February 2004 10:35 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Patrick,
Could you also post the statistics and timings in both the cases. The one that has the lesser number of consistent gets,should be better.

Also note that the index you added ON SERVICE_OP_PRICE (price_group_id) is not used becos of the typical Nested Loops plan [[ ie where it does One single full table scan on one of the tables and probes the other table using an index]]. The 2nd is also Nested loops,but makes use of the unique index.

-Thiru
Re: Index Performance - autotrace on explain [message #64846 is a reply to message #64841] Thu, 19 February 2004 00:40 Go to previous messageGo to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Hi Thiru,

How do I turn on the statistics and the ellapsed time (timings)? How do I display the consistent gets? I'm not using SQL+, I'm using PLSQL Developer.

As a rule of thumb, isn't it "always" better to add index to the columns involved in a join?
SELECT p.price, p.ext_price_msg
FROM SERVICE_OP_PRICE sop, PRICE_GROUP p
WHERE sop.mobile_op_id = 1
AND sop.service_id = 1000
AND sop.price_group_id = p.price_group_id

Isn't better to have an index on sop.price_group_id (there is already one on p.price_group_id)?
Why don't Oracle use this new added Index to join the tables (I did analyzed the sop and the p table after creating that index) ? This sop table has 500 000 rows and the p table has 500 rows! It's better that the full table scan occurs on the p table! Does Oracle control automatically this? It's better that ther is only Index full scan and not full table scan...

Sorry my index added (EXPLAIN PLAN: AFTER) is:
ON SERVICE_OP_PRICE (mobile_op_id, service_id, price_group_id). So that every columns of the WHERE clause are in the INDEX!

Thank you so much for your help!

Regards,

Patrick.
Re: Index Performance - autotrace on explain [message #64848 is a reply to message #64846] Thu, 19 February 2004 03:29 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Patrick,
SQL tracing is covered in detail here --> All about SQL Tracing

SqlPlus is one of the best tools available for Oracle :) ,I rarely use others.

Timing can be turned on sqlplus using 'set timing on', before executing the queries ,although not as accurate as tracing and getting cpu/elapsed time.

"As a rule of thumb, isn't it "always" better to add index to the columns involved in a join?"

If the join involves PK or FK, you'll have the indexes already and its upto the CBO to use them depends on its plan. For eg, in Nested loops, it might decide to go for a full table scan on one of the tables (especially if you have parameters such db_file_multiblock_read_count to a high value) and probbe the other table using an index.

Its not always better to use an index ! If the cost evaluated in performing a full table scan using multiblock reads is cheaper than doing a series of index lookups(followed by table access by ROWID), then it will go for full table scan ,even on the bigger table.

for eg)
thiru@9.2.0:SQL>create table big as select * from all_objects;

Table created.

thiru@9.2.0:SQL>create index big_idx on big(object_name);

Index created.

thiru@9.2.0:SQL>create table small as select * from all_objects where rownum <= 100;

Table created.

thiru@9.2.0:SQL>create index small_idx on small(object_name);

Index created.

thiru@9.2.0:SQL>analyze table big compute statistics;

Table analyzed.

thiru@9.2.0:SQL>analyze table small compute statistics;

Table analyzed.

thiru@9.2.0:SQL>select table_name,num_rows,blocks from user_tables where table_name in ('BIG','SMALL');

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
BIG                                 29803        862
SMALL                                 100          6

thiru@9.2.0:SQL>select big.object_id from big,small where big.object_name=small.object_name;

 OBJECT_ID
----------
     17286
      7559
     22542
...
     20472
     23683
     16167
     18321
     10335
     15677
     14095

196 rows selected.

Elapsed: 00:00:00.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=136 Card=165 Bytes=9075)
   1    0   HASH JOIN (Cost=136 Card=165 Bytes=9075)
   2    1     TABLE ACCESS (FULL) OF 'SMALL' (Cost=3 Card=100 Bytes=2800)
   3    1     TABLE ACCESS (FULL) OF 'BIG' (Cost=132 Card=29803 Bytes=804681)

Statistics
----------------------------------------------------------
         12  recursive calls
          4  db block gets
        861  consistent gets
          0  physical reads
        828  redo size
       3195  bytes sent via SQL*Net to client
        642  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        196  rows processed

-- above, note the timing and consistent gets.

-- Lets ask the CBO to use the index ,now

thiru@9.2.0:SQL>select /*+ INDEX(big) FULL(small) */ big.object_id from big,small where big.object_name=small.object_name;

 OBJECT_ID
----------
     17286
     17287
      7559
      7560
     22542

 
....
     14094
     14095

196 rows selected.

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=303 Card=165 Bytes=9075)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'BIG' (Cost=3 Card=2 Bytes=54)
   2    1     NESTED LOOPS (Cost=303 Card=165 Bytes=9075)
   3    2       TABLE ACCESS (FULL) OF 'SMALL' (Cost=3 Card=100 Bytes=2800)
   4    2       INDEX (RANGE SCAN) OF 'BIG_IDX' (NON-UNIQUE) (Cost=2 Card=2)

Statistics
----------------------------------------------------------
         12  recursive calls
          4  db block gets
        412  consistent gets
          0  physical reads
        800  redo size
       2077  bytes sent via SQL*Net to client
        510  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        196  rows processed

-- the timing is little bit longer now,but consistent gets is lower.  But its pretty close, inspite of Index scan.This may not always be the case. Sometimes the consistent gets are drastically different .



If it can do a fast full scan of the index,then it will skip the FTS. If the cost of index scan is lesser than the FTS using multi block reads,then it goes for a index scan! ...it all depends. But the optimizer is not perfect. If you find you can benefit from indexes consistently,then you can hint the optimizer,although not recommended as a general rule.

Sure,you could try out the concatenated index and see if the overall number of consistent gets and timings are better.

Laters..
Thiru
Previous Topic: ora-0313 and XML
Next Topic: Locking Table
Goto Forum:
  


Current Time: Thu Mar 28 04:21:25 CDT 2024