Re: sql tuning quandry

Steve McClure 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
>

Steve,

   If you have run tkprof as

       tkprof blahblah explain=username/password

beware that the plan is the output of the 'EXPLAIN' command when tkprof runs, which is not necessarily the same as the actual one which was executed (and for which you get the stats). It may be that stats were perhaps out of date or an index missing when the trace file was generated.
Generally speaking, I prefer this kind of query to be written

 SELECT UNIT_PRICE
 FROM
  PBM_DRUG_PRICE WHERE (DRUG_NDC, PRCLIST_ID, EFFECTIVE_BEGIN) =   (SELECT DRUG_NDC, PRCLIST_ID, MAX(EFFECTIVE_BEGIN) FROM PBM_DRUG_PRICE
   WHERE DRUG_NDC = :b1 AND PRCLIST_ID = :b2    GROUP BY DRUG_NDC, PRCLIST_ID) It shouldn't change much in your case, but it tells Oracle a bit more about the way I want it to do it.
And I am no great fan of queries in functions. Sooner or later you find people to use your function in such a way that it's called for each row of a mega-table scan. Ouch.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).