Re: sql tuning quandry

You are comparing apples to Oranges. First, you are comparing autotrace, which shows the expected execution plan, with tkprof off a sql_trace which shows the ACTUAL execution plan. Unfortunately, your tkprof does not show the row counts. Why is that? Make sure your entire session is traces from start to finish.
Secondly, you compare two different sql. You need to execute the same sql in sqlplus using bind variables as does discoverer, not literals:

var b1 varchar2(12)
var b2 number
exec :b1 := '00013103691'; :b2 := 1;

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 )

The difference could be simply the size of each fetch array but the ratio between the consistent reads is 600, so the fetch array for discoverer would have to be 600 times smaller than sqlplus' which is by default 15 rows, so the math doesn't add up.

Lastly, you could change the sql to the following and see what that does:

SELECT UNIT_PRICE
FROM PBM_DRUG_PRICE A WHERE A.DRUG_NDC = :b1 AND A.PRCLIST_ID = :b2 AND

   A.EFFECTIVE_BEGIN = (SELECT MAX(B.EFFECTIVE_BEGIN) FROM PBM_DRUG_PRICE B    WHERE B.DRUG_NDC = A.DRUG_NDC AND B.PRCLIST_ID = A.PRCLIST_ID ) At 03:31 PM 5/9/2003 -0800, you wrote:
>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).

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.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).