Home » RDBMS Server » Performance Tuning » Explain Plan - Query Performance Tuning
Explain Plan - Query Performance Tuning [message #64953] |
Wed, 17 March 2004 04:25 |
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 |
Frank Naude
Messages: 4580 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
|
|
|
Goto Forum:
Current Time: Fri Sep 27 13:56:18 CDT 2024
|