Oracle-L: RE: sql tuning quandry

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Sat, 10 May 2003 05:11:31 -0800
Message-ID: <F001.0059612D.20030510051131@fatcity.com>

Steve

Well, it looks as if you missed the best people, the ones that left at 5 on Friday, hopped in their Ferrari with the top down and headed to the beach house that doesn't even have a phone.

I'll just take a couple of guesses so take this with all suspicion. My first guess is that the difference between the two queries is that Oracle is performing an indexed lookup with the first and a full table scan with the second. This is why we often resort to tkprof, explain plan sometimes misleads. Now, why would Oracle switch access methods just because you put your query within a function? I dimly recall something mentioned with function-based indexes that if Oracle thinks it might get a null returned, it won't use that index. I'm wondering if your query might be hitting something like that.

If I were you, I'd:

Try putting a hint within your query to encourage it to use the index.
Check out the null thing. I searched a little but didn't get an easy result.
Hope that it rains at the beach so the best people will come back early and answer your question.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Friday, May 09, 2003 6:32 PM
To: Multiple recipients of list ORACLE-L

I have a strange tuning issue, that I have not been able to resolve. Since this is the first time I have had to progress further down the statement tuning road than 'explain plan', I am not even sure what to look at next. The situation is this. We are trying to query the most current price for an item(drug in our business). The query we typically use is fairly straight forward, and performs well when run directly. The sql and some autotrace output is included below. The statement performs well. All is well..then we needed to place this query in a function to be executed by Discoverer users, and performace hit bottom. The Tkprof output from that session is below. I will continue my question on down below that info.

autotrace output for query run in sqlplus

SELECT UNIT_PRICE
FROM
PBM_DRUG_PRICE WHERE DRUG_NDC ='00013103691' AND PRCLIST_ID = 1 AND EFFECTIVE_BEGIN = (SELECT MAX(EFFECTIVE_BEGIN) FROM PBM_DRUG_PRICE
WHERE DRUG_NDC = '00013103691' AND PRCLIST_ID = 1 ) 1 row selected.

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=24) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_DRUG_PRICE' (Cost=3

Card=1 Bytes=24)

   2    1     INDEX (UNIQUE SCAN) OF 'DRGPRC_UK' (UNIQUE) (Cost=2 Card
          =1)

   3    2       SORT (AGGREGATE)
   4    3         FIRST ROW (Cost=3 Card=1 Bytes=20)
   5    4           INDEX (RANGE SCAN (MIN/MAX)) OF 'DRGPRC_UK' (UNIQU
          E) (Cost=3 Card=1)

Statistics

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

DEMO-frodo-STEVE>spool off

End of output

tkprof of session executing select
pbmadm.common.getdrugprice(1,'00013103691') from dual

SELECT UNIT_PRICE
FROM
PBM_DRUG_PRICE WHERE DRUG_NDC = :b1 AND PRCLIST_ID = :b2 AND EFFECTIVE_BEGIN = (SELECT MAX(EFFECTIVE_BEGIN) FROM PBM_DRUG_PRICE WHERE DRUG_NDC = :b1 AND PRCLIST_ID = :b2 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ------

Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 1.73 2.12 4223 4227 58 1
------- ------ -------- ---------- ---------- ---------- ---------- ------

total 3 1.73 2.12 4223 4227 58 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 20 (USERNAME) (recursive depth: 1)

Rows Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
              'PBM_DRUG_PRICE'
      0    INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'DRGPRC_UK' (UNIQUE)
      0     SORT (AGGREGATE)
      0      FIRST ROW
      0       INDEX   GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF
                  'DRGPRC_UK' (UNIQUE)



****************************************************************************

The explain plans are identical. The performance issue is with the fetches and physical reads. Executing the query at sqlplus calls for 7 reads. Calling the function causes some 4200 reads. I have called the function both within a sql query, and from within a pl/sql anonymous block. The results are the same...4200 reads.

Any Idea where to look next?

Thanks in advance,
Steve McClure

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Steve McClure
  INET: smcclure_at_usscript.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sat May 10 2003 - 08:11:31 CDT