Oracle-L: RE: sql tuning quandry

From: Kevin Toepke <ktoepke_at_rlcarriers.com>
Date: Mon, 12 May 2003 05:11:59 -0800
Message-ID: <F001.005968A0.20030512051159@fatcity.com>

Steve

The first thing to notice is that the queries are non-identical. The one you run in sql-plus uses static values and one in the function uses bind variables. The optimizer will make some assumtions about the bound value that might be different than the actual values bound.

I'd guess that if you rewrote your sqlplus query to use bind variables you would get identical execution plans to the version in the function.

kevin

-----Original Message-----
Sent: Friday, May 09, 2003 7: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: Kevin Toepke
  INET: ktoepke_at_rlcarriers.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 Mon May 12 2003 - 08:11:59 CDT