RE: sql tuning quandry

OK after spending a good chunk of each day for three days playing with tkprof, and pondering the possibilities of why a query withing a PL/SQL function would perform SO differently than when it is run from sqlplus, I finally actually read the PL/SQL code. The data type of one of the cursor variables was mismatched. As a result, the proper index was unused, resulting in either a full table scan(if I used the developer's +0 code) or a range scan on a poor index. Thanks again for all the helpful hints, and guidance.

Steve McClure

-----Original Message-----
Breitling
Sent: Monday, May 12, 2003 6:57 PM
To: Multiple recipients of list ORACLE-L

But now in your rewritten function the sql is different from the one posted earlier and from the one executed in sqlplus:

SELECT UNIT_PRICE
FROM
PBM_DRUG_PRICE P WHERE DRUG_NDC = :b1 AND PRCLIST_ID + 0 = :b2 AND

   EFFECTIVE_BEGIN = (SELECT MAX(EFFECTIVE_BEGIN) FROM PBM_DRUG_PRICE    WHERE DRUG_NDC = P.DRUG_NDC AND PRCLIST_ID + 0 = P.PRCLIST_ID ) where do the "+0" in the prclist_id predicates suddenly come from? That of course precludes the full use of the index, resulting in range scans. I presume the DRGPRC_UK is on (DRUG_NDC, PRCLIST_ID) rather than (PRCLIST_ID, DRUG_NDC) otherwise Oracle couldn't use the index at all (not until 9 with skip-scan).
What are the statistics for the table (num_rows, blocks, avg_row_len), index (leaf_blocks, distinct_keys, leaf_blocks_per_key, data_blocks_per_key, clustering factor) and columns (num_distinct, density) involved? Any histograms?

The other option of course is to enable a 10053 trace and look what the optimizer does.

At 03:12 PM 5/12/2003 -0800, you wrote:
>OK First thanks for all the suggestions. Rather than respond to each
>individually I figure I will just continue the thread in a single response.
>
>In regards to using literals in my sqlplus example and not bind variables.
I
>actually did test it both with and without bind variables. I have done so
>again and have added the tkprof output from that test with bind variables
>below. The results were the same, 7 physical reads. The explain plan is
>identical to the previous examples.
>
>A few of you noted that my tkprof output didn't have values for the rows
>column. I have been unable to correct this issue. I am making sure the
>tracing is completed before analyzing the trace file. I have scripts that
>alter session to start and stop 10046 tracing. What I found is that if I
>look at the tkprof output of cursor executed at the sqlplus prompt, I get
an
>explain plan with the rows column populated. If I call this cursor from
>within a function, tkprof lists a plan with 0s in the row column.
>
>OK that said I was able to make a breakthrough of sorts. Using aliases to
>refer back from the sub query, as one of you suggested. I discovered that
>the query only caused 80 some fetches. Executing the rewritten using
>aliases to refer from sub query) query results in 8 reads. I am still
>confused as to what would be causing this, but am reasonably happy to see
>some progress in the right direction.
>
>Any clues as to what to look into next? I have included tkprof output from
>the session where I used bind variables in sqlplus as well as the tkprof of
>a session where I called the rewritten function. Sorry if this is too much
>info Jared. It amounts to about 400 lines. Please don't quote me when you
>respond.

Wolfgang Breitling
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).

-- 
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).