Re: help needed in tuning query

From: Jan <janik_at_pobox.sk>
Date: 12 Aug 2004 01:56:47 -0700
Message-ID: <81511301.0408120056.31398ee8@posting.google.com>

1. Why did you create a date function which return a char? Then you (Oracle) have to convert all the date values in a table before evaluating the condition.
2. Why do you use SELECT FROM DUAL in your function? Instead of pure PL/SQL?

BTW your function is so simple that I would not use it - instead I would put this simple logic directly into SQL. Remember that each PL/SQL call from SQL make it slow.

3) If you insist on using PL/SQL function - then do this trick:

   .. WHERE my_date_col >= (SELECT your_date_function FROM DUAL) ...

   since your function return the same value for each row - you want to call it just once per whole query instead of once per row

Jan      

sharkdba_at_yahoo.com (Sharkie) wrote in message news:<423b5ab1.0408041009.2fabaa51_at_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 Thu Aug 12 2004 - 03:56:47 CDT