| IN Sub-query select performing poorly [message #279011] |
Tue, 06 November 2007 10:51  |
TLegend33
Messages: 203
Registered: March 2005
|
Senior Member |
|
|
I have a query that performs well when id's are specified and the same query performs poorly when id's are generated through a sub-query select.
First the results of the sub-query:
SQL> set timing on
SQL> select clickid from click where campaignid = 12696;
CLICKID
-----------
131286
131287
138795
138793
138794
138792
6 rows selected.
Elapsed: 00:00:00.09
SQL> set autotrace traceonly
SQL> select clickid from click where campaignid = 12696;
6 rows selected.
Elapsed: 00:00:00.96
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'CLICK'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1398 consistent gets
1392 physical reads
0 redo size
593 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
SQL> select count(*) from click;
COUNT(*)
----------
155039
As you can see, the results return quickly. Next, the query that specifies the id's:
SQL> set timing on
SQL> set autotrace traceonly
SQL> select to_char(activitydate,'YYYY-MM-DD') clickdate, 300 act, count(*) click
from activity
where clickid in (131286,131287,138795,138793,138794,138792)
and to_char(activitydate,'YYYY-MM') = to_char(sysdate-1,'YYYY-MM')
group by to_char(activitydate,'YYYY-MM-DD')
/
Elapsed: 00:00:00.98
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=396 Card=41 Bytes=53
3)
1 0 SORT (GROUP BY) (Cost=396 Card=41 Bytes=533)
2 1 INLIST ITERATOR
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'ACTIVITY' (Cost
=394 Card=41 Bytes=533)
4 3 INDEX (RANGE SCAN) OF 'ACTIVITY_CLICKID_IDX
' (NON-UNIQUE) (Cost=1 Card=4104)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1845 consistent gets
63 physical reads
0 redo size
749 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
You can see how quickly this query executes. Also, the ACTIVITY_CLICKID_IDX index is utilized. Below, is the poorly performing query:
SQL> set timing on
SQL> set autotrace traceonly
SQL> select to_char(activitydate,'YYYY-MM-DD') clickdate, 300 act, count(*) click
from activity
where clickid in (select clickid from click where campaignid = 12696)
and to_char(activitydate,'YYYY-MM') = to_char(sysdate-1,'YYYY-MM')
group by to_char(activitydate,'YYYY-MM-DD')
/
Elapsed: 00:08:43.94
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4747 Card=5091 Bytes
=198549)
1 0 SORT (GROUP BY) (Cost=4747 Card=5091 Bytes=198549)
2 1 HASH JOIN (Cost=4729 Card=5091 Bytes=198549)
3 2 SORT (UNIQUE)
4 3 TABLE ACCESS (FULL) OF 'CLICK' (Cost=56 Card=744
Bytes=19344)
5 2 TABLE ACCESS (FULL) OF 'ACTIVITY' (Cost=4666 Card
=672086 Bytes=8737118)
Statistics
----------------------------------------------------------
92 recursive calls
0 db block gets
121944 consistent gets
86671 physical reads
0 redo size
749 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
5 rows processed
As demonstrated at the beginning of this message, the sub-query will return the 6 id values quickly. However, when I attempt to execute the main query without manually specifying the id's it takes over 8 minutes.
I believe the sub-query is executing for every row that's found in the main query. How can I improve the performance of this query/sub-query without manually specifying the id's?
Thanks.
|
|
|
|
|
|
| Re: IN Sub-query select performing poorly [message #279022 is a reply to message #279017] |
Tue, 06 November 2007 11:31  |
TLegend33
Messages: 203
Registered: March 2005
|
Senior Member |
|
|
SQL> desc click
Name Null? Type
----------------------------------------- -------- ----------------------------
CLICKID NOT NULL NUMBER
URL VARCHAR2(800)
DESCRIPTION VARCHAR2(255)
CLICKTYPE VARCHAR2(20)
CATEGORYID NUMBER
CLICKCOUNT NOT NULL NUMBER
WEBSITEID NUMBER
CAMPAIGNID NUMBER |
|
|
|
|
|
| Re: IN Sub-query select performing poorly [message #279042 is a reply to message #279011] |
Tue, 06 November 2007 13:08 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
|
Not possible to tell for sure, but looks like you are relying on dynamic sampling instead of computing statistics on the involved tables directly. You first query doesn't even have any costs in the explain plan. Try the stats, then try your query again.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
