Home » RDBMS Server » Performance Tuning » IN Sub-query select performing poorly (Oracle DB 10.2.0.2 RHEL4)
IN Sub-query select performing poorly [message #279011] Tue, 06 November 2007 10:51 Go to next message
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 #279017 is a reply to message #279011] Tue, 06 November 2007 11:19 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
post the results from
SQL> DESC CLICK
Re: IN Sub-query select performing poorly [message #279022 is a reply to message #279017] Tue, 06 November 2007 11:31 Go to previous messageGo to next message
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 #279041 is a reply to message #279022] Tue, 06 November 2007 13:05 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Can you try the select thing with ...

- an UNNEST hint in the sub-query
- an MATERIALIZE hint in the sub-query
- an FIRST_ROWS hint in the main query?

Re: IN Sub-query select performing poorly [message #279042 is a reply to message #279011] Tue, 06 November 2007 13:08 Go to previous messageGo to next message
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.
Re: IN Sub-query select performing poorly [message #279043 is a reply to message #279022] Tue, 06 November 2007 13:11 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
How many rows match the condition
 where campaignid = 12696
in CLICK?
Re: IN Sub-query select performing poorly [message #279050 is a reply to message #279042] Tue, 06 November 2007 13:33 Go to previous message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
I computed the stats on the click table, then executed the query again. This time, instead of 8 minutes, it took .36 seconds.
Thanks for the help everyone!
Previous Topic: Performance Bottleneck!!!
Next Topic: Slow performance
Goto Forum:
  


Current Time: Thu Dec 08 08:44:07 CST 2016

Total time taken to generate the page: 0.13137 seconds