help needed in tuning query

From: Sharkie <sharkdba_at_yahoo.com>
Date: 4 Aug 2004 11:09:03 -0700
Message-ID: <423b5ab1.0408041009.2fabaa51@posting.google.com>

I'm trying to tune a slow query (oracle 8.1.7 on Linux (dev) and Solaris (prod))
This query is very slow:

SQL> set autotrace traceonly

            SELECT contentintid
              FROM content
             WHERE contenttypeintid = 1
               AND statusintid in (1,2)
               AND to_char(content.adddate,'YYYYMMDD') >=
p_search.get_search_date('',1)
               AND to_char(content.adddate,'YYYYMMDD') <=

p_search.get_search_date('',2)
AND upper(content.searchstring) like '%ELECTRONIC%'  ORDER BY adddate desc
/
Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=174 Card=1 Bytes=1876)

   1 0 SORT (ORDER BY) (Cost=174 Card=1 Bytes=1876)

   2    1     INLIST ITERATOR
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T_CONTENT' (Cost=172
Card=1 Bytes=1876)
   4    3         INDEX (RANGE SCAN) OF 'IDX_T_CONTENT_ALL'

(NON-UNIQUE) (Cost=160 Card=1)
Statistics

---

      32250  recursive calls
     128972  db block gets
      34099  consistent gets
          0  physical reads
          0  redo size
        455  bytes sent via SQL*Net to client
        424  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          3  rows processed

The p_search.get_search_date function will simply return date as string:

SQL> select p_search.get_search_date('',1) from dual; 20040704
SQL> select p_search.get_search_date('',2) from dual; 20040804

But when I replace these functions with hardcoded values in the above query,
it behaves much faster right away:

            SELECT contentintid
              FROM content
             WHERE contenttypeintid = 1
               AND statusintid in (1,2)
               AND to_char(content.adddate,'YYYYMMDD') >= '20040704'
               AND to_char(content.adddate,'YYYYMMDD') <= '20040804'

AND upper(content.searchstring) like '%ELECTRONIC%'  ORDER BY adddate desc
/
Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=174 Card=1 Bytes=1876)

   1 0 SORT (ORDER BY) (Cost=174 Card=1 Bytes=1876)

   2    1     INLIST ITERATOR
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'T_CONTENT' (Cost=172
Card=1 Bytes=1876)
   4    3         INDEX (RANGE SCAN) OF 'IDX_T_CONTENT_ALL'

(NON-UNIQUE) (Cost=160 Card=1)
Statistics

---

          7  recursive calls
          0  db block gets
       1856  consistent gets
          0  physical reads
          0  redo size
        455  bytes sent via SQL*Net to client
        424  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          3  rows processed

Can anyone explain it??? How can a simple date function cause so much slow down?
The difference in time execution is tremendous. Any other ideas on what's causing all the recursive calls and db block gets?

Note that the content table has a compound index of (contenttypeintid, statusintid, to_char(adddate)) No index exist on upper(searchstring) since it's a varchar(4000) field and I get:
ORA-01450: maximum key length (3218) exceeded Received on Wed Aug 04 2004 - 13:09:03 CDT