| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: sql tuning quandry
I've done a bit testing and could practically reproduce your observations.
First, the plan that you get from tkprof when you use the function - the
one with the 0 row counts - is not the one that was executing but the
result of an explain. As Tim Gorman pointed out, pre Oracle 9 tkprof does
an explain and passes that off as the execution plan. Oracle 8 will also
format the STAT lines from the raw trace into an execution plan, provided
they are present. That is why with Oracle 8 tkprof you should get two plans
for each sql: one with a heading of "Row Source Operation" which comes from
the STAT data, has row counts, object_id numbers instead of index names and
is the actual execution plan. The other with a heading of "Execution Plan"
is present when you request "explain=user/password", is the result of an
explain plan for the statement, superimposes the first n row counts from
the STAT data (if found) as rows counts for this plan and has index names
rather than object_ids.
From what you posted, you only have the second, ergo not the real
execution plan. Your trace has no STAT data and therefor the real plan is
missing and the explained plan doesn't have row counts.
Let me guess. You use the parameters of the function directly as variables in the sql, something like:
function getdrugprice ( p_PRCLIST_ID in number, p_DRUG_NDC in varchar2)
return number as
L_UNIT_PRICE PBM_DRUG_PRICE.UNIT_PRICE%TYPE := null; begin
begin
SELECT A.UNIT_PRICE into L_UNIT_PRICE
FROM PBM_DRUG_PRICE A WHERE A.DRUG_NDC = p_DRUG_NDC
AND A.PRCLIST_ID = p_PRCLIST_ID
AND A.EFFECTIVE_BEGIN = (SELECT MAX(B.EFFECTIVE_BEGIN) FROM
PBM_DRUG_PRICE B
WHERE B.DRUG_NDC = p_DRUG_NDC AND B.PRCLIST_ID = p_PRCLIST_ID );
exception
when others then null;
end getdrugprice ;
If you can run the test in a test instance, flush the shared pool before to make sure the statement gets parsed and exit the session after the test, you should see the STAT data in the trace and I would say it is using a full table scan.
Provided I am correct, try changing your function as follows:
function getdrugprice ( p_PRCLIST_ID in number, p_DRUG_NDC in varchar2)
return number as
L_PRCLIST_ID PBM_DRUG_PRICE.PRCLIST_ID%TYPE := p_PRCLIST_ID; L_DRUG_NDC PBM_DRUG_PRICE.DRUG_NDC%TYPE := p_DRUG_NDC; L_UNIT_PRICE PBM_DRUG_PRICE.UNIT_PRICE%TYPE := null;begin
begin
SELECT A.UNIT_PRICE into L_UNIT_PRICE
FROM PBM_DRUG_PRICE A WHERE A.DRUG_NDC = L_DRUG_NDC
AND A.PRCLIST_ID = L_PRCLIST_ID
AND A.EFFECTIVE_BEGIN = (SELECT MAX(B.EFFECTIVE_BEGIN) FROM
PBM_DRUG_PRICE B
WHERE B.DRUG_NDC = L_DRUG_NDC AND B.PRCLIST_ID = L_PRCLIST_ID );
exception
when others then null;
end getdrugprice ;
i.e. cast the parameters into local variables of the exact type of the columns before using them in the sql and see what happens.
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
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).Received on Mon May 12 2003 - 23:36:37 CDT
 |
 |