Home » RDBMS Server » Performance Tuning » Explain Plan - Query Performance Tuning
Explain Plan - Query Performance Tuning [message #64953] Wed, 17 March 2004 04:25 Go to next message
Patrick Tahiri
Messages: 119
Registered: January 2004
Senior Member
Hi,

I have this query to tune:
SELECT  s1.service_id, s1.subscription_id, is_number_sql(s2.dest_addr)
FROM subscription_event s1, subscriber s2
WHERE s1.egm_id = (select egm_id from event_group_map egm where egm.map_name =
'score24_44')
                                AND s1.event = '14'
                                AND s1.event_param IS NULL
                   AND NVL(s1.defunct, 0) = 0
                                AND NVL(s1.active, 0) != 0
                                AND s1.subscriber_id = s2.subscriber_id
ORDER BY s1.service_id


10 rows selected.





Elapsed: 00:00:00.03



 

Execution Plan



----------------------------------------------------------



   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=39)



   1    0   SORT (ORDER BY) (Cost=22 Card=1 Bytes=39)



   2    1     FILTER



   3    2       NESTED LOOPS (Cost=20 Card=1 Bytes=39)



   4    3         TABLE ACCESS (FULL) OF 'SUBSCRIPTION_EVENT' (Cost=19



           Card=1 Bytes=26)



 

   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'SUBSCRIBER' (Cost=



          1 Card=247731 Bytes=3220503)



 

   6    5           INDEX (UNIQUE SCAN) OF 'SUBSCRIBER_ID_PK' (UNIQUE)



   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'EVENT_GROUP_MAP' (Co



          st=1 Card=1 Bytes=11)



 

   8    7         INDEX (UNIQUE SCAN) OF 'EG_MAPNAME_UK' (UNIQUE)



 

Statistics



----------------------------------------------------------



         17  recursive calls



         44  db block gets



        355  consistent gets



          0  physical reads



          0  redo size



        789  bytes sent via SQL*Net to client



        599  bytes received via SQL*Net from client



          3  SQL*Net roundtrips to/from client



          4  sorts (memory)



          0  sorts (disk)



         10  rows processed



 

I can see that we have a full table scan on SUBSCRIPTION_EVENT.

Then I studied SUBSCRIPTION_EVENT and SUBSCRIBER:



select
count(*) from subscriber -- 247731 rows

select count(*) from subscription_event -- 40997 rows

select count(*) from subscription_event where event = '14' -- 28 rows

select count(*) from subscription_event where defunct is null  -- 0 rows
select count(*) from subscription_event where defunct = 0  -- 18329 rows

select count(*) from subscription_event where active is null -- 0 rows
select count(*) from subscription_event where active = 0 -- 22682 rows

select count(*) from subscription_event where event_param is null  -- 41016 rows

select egm_id from event_group_map egm where egm.map_name = 'score24_44' -- 1 row


alter
table SUBSCRIPTION_EVENT
add constraint SE_SUBSCRIPTION_ID_PK primary key (SUBSCRIPTION_ID)
using index

alter table SUBSCRIPTION_EVENT
add constraint SE_EVENT_UK unique (SERVICE_ID,SUBSCRIBER_ID,EVENT,EVENT_PARAM,DEFUNCT)
using index

create index IDX_SUBSCREVENT_SUBSCRIBER on SUBSCRIPTION_EVENT (SUBSCRIBER_ID)

WHY doesnt the CBO choose to use on of the index instead of using a FULL table scan on SUBSCRIPTION_EVENT? On the subscriber_id join condition...


* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Here, I have tried to suppress the NVL function on the left side of the where condition clause which could cause index unvalidation: BUT it's the same, I have a full table scan...

 

SQL> set timing on



SQL> set autotrace on



SQL> SELECT s1.service_id, s1.subscription_id, is_number_sql(s2.dest_addr)



  2  FROM subscription_event s1, subscriber s2



  3  WHERE s1.egm_id = (select egm_id from event_group_map egm where egm.map_name =



  4  'score24_44')



  5    AND s1.event = '14'



  6    AND s1.event_param IS NULL



  7      AND s1.defunct = 0



  8    AND s1.active != 0



  9    AND s1.subscriber_id = s2.subscriber_id



 10  ORDER BY s1.service_id;



 

 

10 rows selected.



Elapsed: 00:00:00.06



 

Execution Plan



----------------------------------------------------------



   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=39)



   1    0   SORT (ORDER BY) (Cost=22 Card=1 Bytes=39)



   2    1     FILTER



   3    2       NESTED LOOPS (Cost=20 Card=1 Bytes=39)



   4    3         TABLE ACCESS (FULL) OF 'SUBSCRIPTION_EVENT' (Cost=19



           Card=1 Bytes=26)



 

   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'SUBSCRIBER' (Cost=



          1 Card=247731 Bytes=3220503)



 

   6    5           INDEX (UNIQUE SCAN) OF 'SUBSCRIBER_ID_PK' (UNIQUE)



   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'EVENT_GROUP_MAP' (Co



          st=1 Card=1 Bytes=11)



 

   8    7         INDEX (UNIQUE SCAN) OF 'EG_MAPNAME_UK' (UNIQUE)



 

Statistics



----------------------------------------------------------



         17  recursive calls



         44  db block gets



        355  consistent gets



          0  physical reads



          0  redo size



        789  bytes sent via SQL*Net to client



        585  bytes received via SQL*Net from client



          3  SQL*Net roundtrips to/from client



          4  sorts (memory)



          0  sorts (disk)



         10  rows processed



 

SQL>



 

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Here, I tried to force the use of the index with a HINT but still full table scan...

 

SQL> set timing on



SQL> set autotrace on



SQL> SELECT /*+ INDEX (subscription_event idx_subscrevent_subscriver) */ s1.service_id, s1.subscript



ion_id, is_number_sql(s2.dest_addr)



  2  FROM subscription_event s1, subscriber s2



  3  WHERE s1.egm_id = (select egm_id from event_group_map egm where egm.map_name =



  4  'score24_44')



  5    AND s1.event = '14'



  6    AND s1.event_param IS NULL



  7      AND NVL(s1.defunct, 0) = 0



  8    AND NVL(s1.active, 0) != 0



  9    AND s1.subscriber_id = s2.subscriber_id



 10  ORDER BY s1.service_id;



 

10 rows selected.



 

Elapsed: 00:00:00.04



 

Execution Plan



----------------------------------------------------------



   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=1 Bytes=39)



   1    0   SORT (ORDER BY) (Cost=22 Card=1 Bytes=39)



   2    1     FILTER



   3    2       NESTED LOOPS (Cost=20 Card=1 Bytes=39)



   4    3         TABLE ACCESS (FULL) OF 'SUBSCRIPTION_EVENT' (Cost=19



           Card=1 Bytes=26)



 

   5    3         TABLE ACCESS (BY INDEX ROWID) OF 'SUBSCRIBER' (Cost=



          1 Card=247731 Bytes=3220503)



 

   6    5           INDEX (UNIQUE SCAN) OF 'SUBSCRIBER_ID_PK' (UNIQUE)



   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'EVENT_GROUP_MAP' (Co



          st=1 Card=1 Bytes=11)



 

   8    7         INDEX (UNIQUE SCAN) OF 'EG_MAPNAME_UK' (UNIQUE)



 

Statistics



----------------------------------------------------------



         17  recursive calls



         44  db block gets



        355  consistent gets



          0  physical reads



          0  redo size



        789  bytes sent via SQL*Net to client



        663  bytes received via SQL*Net from client



          3  SQL*Net roundtrips to/from client



          4  sorts (memory)



          0  sorts (disk)



         10  rows processed



 

SQL>



 

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *



 

Here, I tried to suppress the sub-query pluss I have created a new index covering my needs (kind of covering query) and see if it's help, but still the same...

CREATE INDEX IDX_TEST ON SUBSCRIPTION_EVENT (service_id, subscription_id, egm_id, event, subscriber_id) 

 

ANALYSE TABLE ....

 

SQL> set timing on



SQL> set autotrace on



SQL> SELECT  s1.service_id, s1.subscription_id, is_number_sql(s2.dest_addr)



  2  FROM subscription_event s1, subscriber s2, event_group_map egm



  3  WHERE s1.event = '14'



  4    AND s1.event_param IS NULL



  5      AND s1.defunct = 0



  6    AND s1.active != 0



  7    AND s1.subscriber_id = s2.subscriber_id



  8        AND s1.egm_id = egm.egm_id



  9        AND egm.map_name = 'score24_44'



 10  ORDER BY s1.service_id;



 

10 rows selected.



 

Elapsed: 00:00:00.05



 

Execution Plan



----------------------------------------------------------



   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=1 Bytes=50)



   1    0   SORT (ORDER BY) (Cost=23 Card=1 Bytes=50)



   2    1     NESTED LOOPS (Cost=21 Card=1 Bytes=50)



   3    2       NESTED LOOPS (Cost=20 Card=1 Bytes=37)



   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'EVENT_GROUP_MAP' (



          Cost=1 Card=1 Bytes=11)



 

   5    4           INDEX (UNIQUE SCAN) OF 'EG_MAPNAME_UK' (UNIQUE)



   6    3         TABLE ACCESS (FULL) OF 'SUBSCRIPTION_EVENT' (Cost=19



           Card=1 Bytes=26)



 

   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'SUBSCRIBER' (Cost=1



          Card=247731 Bytes=3220503)



 

   8    7         INDEX (UNIQUE SCAN) OF 'SUBSCRIBER_ID_PK' (UNIQUE)



 

Statistics



----------------------------------------------------------



         10  recursive calls



         44  db block gets



        350  consistent gets



          0  physical reads



          0  redo size



        789  bytes sent via SQL*Net to client



        585  bytes received via SQL*Net from client



          3  SQL*Net roundtrips to/from client



          1  sorts (memory)



          0  sorts (disk)



         10  rows processed





* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

The same as above but forcing the newly created index with the INDEX HINT (IDX_TEST), but still failed...

 

SQL> SELECT  /*+ INDEX (subscription_event idx.test) */ s1.service_id, s1.subscription_id, is_number



_sql(s2.dest_addr)



  2  FROM subscription_event s1, subscriber s2, event_group_map egm



  3  WHERE s1.event = '14'



  4    AND s1.event_param IS NULL



  5      AND s1.defunct = 0



  6    AND s1.active != 0



  7    AND s1.subscriber_id = s2.subscriber_id



  8        AND s1.egm_id = egm.egm_id



  9        AND egm.map_name = 'score24_44'



 10  ORDER BY s1.service_id;

 

10 rows selected.



 

Elapsed: 00:00:00.04



 

Execution Plan



----------------------------------------------------------



   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=1 Bytes=50)



   1    0   SORT (ORDER BY) (Cost=23 Card=1 Bytes=50)



   2    1     NESTED LOOPS (Cost=21 Card=1 Bytes=50)



   3    2       NESTED LOOPS (Cost=20 Card=1 Bytes=37)



   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'EVENT_GROUP_MAP' (



          Cost=1 Card=1 Bytes=11)



 

   5    4           INDEX (UNIQUE SCAN) OF 'EG_MAPNAME_UK' (UNIQUE)



   6    3         TABLE ACCESS (FULL) OF 'SUBSCRIPTION_EVENT' (Cost=19



           Card=1 Bytes=26)



 

   7    2       TABLE ACCESS (BY INDEX ROWID) OF 'SUBSCRIBER' (Cost=1



          Card=247731 Bytes=3220503)



 

   8    7         INDEX (UNIQUE SCAN) OF 'SUBSCRIBER_ID_PK' (UNIQUE)



 

 

Statistics



----------------------------------------------------------



         10  recursive calls



         44  db block gets



        350  consistent gets



          0  physical reads



          0  redo size



        789  bytes sent via SQL*Net to client



        629  bytes received via SQL*Net from client



          3  SQL*Net roundtrips to/from client



          4  sorts (memory)



          0  sorts (disk)



         10  rows processed

 

Is there a way to avoid this FULL table scan on the joined table SUBSCRIPTION_EVENT and see what the use of index is giving to me? Something like an Index Join or Index Merge Join?

 

Thank you very much for your help!

 

Best regards,

 

Patrick Tahiri.
Re: Explain Plan - Query Performance Tuning [message #64964 is a reply to message #64953] Thu, 18 March 2004 04:39 Go to previous message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

Looks like you've got the index names wrong in your hints:

IDX_SUBSCREVENT_SUBSCRIBER:
SELECT /*+ INDEX (subscription_event <FONT COLOR=RED>idx_subscrevent_subscriver</FONT>)


IDX_TEST:
SELECT  /*+ INDEX (subscription_event <FONT COLOR=RED>idx.test</FONT>)


Best regards.

Frank
Previous Topic: Query taking too long to run
Next Topic: performance tuning novice
Goto Forum:
  


Current Time: Thu Mar 28 05:34:12 CDT 2024