Hi,

I have a main view from where all the columns are displayed at front
end.
This view has 6 subqueries in it, of which in one of the view, if i
comment a function to get an amount based on a input value, it brings
records in 10-15 secs. If i dont comment, then it takes about 2 mins.

The same function i have used in other views but only in this view it
is taking a long time. This view retrieves more than 2500 records but
the others has very few records(say about 10-15 records at the max.)

Can anybody give me any suggestions to solve the problem?

Note:
I have used cost based optimizer. All my tables are analyzed and have
statistics collected.

Thanks
Regards
Sujatha.k

Sujatha wrote:
No SQL
No Explain Plan
No version

No Advice.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Sujatha wrote:
Sounds like a classic case where tuning is needed. If the view returns
10-15 records, the function lets results be returned in 10-15 seconds.
Yet if the view returns 2,500 records, the run time approaches 2
minutes. There are two things to think about here....One, tune the
underlying SQL of the view. Two, tune the function. Your function likely
contains queries within the function. Have you tuned those queries in
the function? Have you looked to ensure that your PL/SQL logic in the
function is not detrimental to the performance of the function? It
sounds like most of your returns will be gained by tuning the function.

HTH,
Brian

--
================================================== =================

Brian Peasland
dba (AT) nospam (DOT) peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Hi Brian Peasland,

Thanks for your analysis. Your analysis is right.The function uses 4
cursors and it has a parameter value. When i pass the parameter in the
function(get_dm_amount) say for example get_dm_amount(debit memo
number), and for that debit memo number, it has to first find what is
the type and based on that value it determines which cursor is right
and then calls the cursor and return the value.
For 2500 records, if it has to do this, it will definetly take time.
But i dont know how to tune the function since it has cursors..I
thought of creating a table for the function but dont have any idea of
how to? Can you suggests something?

Thanks
Regards
Sujatha.k

Brian Peasland wrote:

Sujatha wrote:
Your cursors are SQL statements in their own right. Have you taken those
SQL statements and tuned them to ensure that they are optimal?

Cheers,
Brian

--
================================================== =================

Brian Peasland
dba (AT) nospam (DOT) peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Sujatha wrote:
Switching between SQL and PL/SQL generally is bad idea at least from
performance viewpoint. The best thing is to incorporate the logic into
the very sql statement. On the other hand just because your query
returns 1 row that really doesn't mean that function is called once, it
can be called zillion times and only later some other filters applied.
So check how many times your function is applied. Trace could be a tool
here. If you cannot incorporate function logic into the sql statement
then generally you'd like to apply function as late as possible after
all other filters are applied.

Gints Plivna
http://www.gplivna.eu/

Hi Brian,

As you said, i tried tuning my cursors in the function. here is my
function.
CREATE OR REPLACE FUNCTION GET_DM_AMOUNT (P_DM_NUMBER NUMBER
,P_MFR_ID NUMBER DEFAULT NULL
) RETURN NUMBER
IS
vn_amount Number;
vr_eb EXP_BATCH%ROWTYPE;
vr_dm EXP_DEBIT_MEMO%ROWTYPE;
CURSOR mfr_phy_returns_cur IS
SELECT SUM(extended_price) price
FROM exp_order_lines
WHERE order_line_id IN
(SELECT order_line_id FROM exp_contents_of_bag WHERE
container_id IN
(SELECT container_id FROM exp_containers
WHERE manufacturer_id = vr_dm.tc_id
AND batch_id = vr_dm.batch_id
AND exp_dea_group = vr_dm.EXP_DEA_GROUP
AND container_type_id = 1
AND parent_id IS NOT NULL
AND NVL(ship_via, 'F') = NVL(vr_dm.RETURN_VIA,'F')))
;

CURSOR mfr_risw_returns_cur IS
SELECT SUM(ol.extended_price) price
FROM exp_order_lines ol, exp_order_headers oh
WHERE oh.batch_id = vr_dm.batch_id
AND oh.order_header_id = ol.order_header_id
AND ol.return_waste_indate_flag = 'R'
AND ol.cap_line = 'Y'
AND ol.return_in_system_indicator = 'Y'
AND NVL(ol.cla_rx_code_drug_code, 'RX') IN
(SELECT dea_class_code FROM exp_dea_class WHERE
exp_dea_group = vr_dm.EXP_DEA_GROUP)
AND ol.manufacturer_name IN
(SELECT manufacturer_name FROM mfrs_in_debit_memo WHERE
exp_dm_number = vr_dm.exp_dm_number) ;

CURSOR retDepot_phy_returns_cur IS
SELECT SUM(extended_price) price
FROM exp_order_lines ol
WHERE order_line_id IN
(SELECT order_line_id FROM exp_contents_of_bag WHERE
container_id IN
(SELECT container_id FROM exp_containers
WHERE manufacturer_id = vr_dm.tc_id
AND batch_id = vr_dm.batch_id
AND exp_dea_group = vr_dm.exp_dea_group
AND container_type_id = 1
AND parent_id IS NOT NULL
AND NVL(ship_via, 'F') = NVL(vr_dm.RETURN_VIA, 'F')))

AND manufacturer_name IN (SELECT manufacturer_name FROM
mfrs_in_debit_memo mfrs --, exp_trading_community tc
WHERE mfrs.exp_dm_number =
vr_dm.exp_dm_number
AND parent_mfr_id = P_MFR_ID);

CURSOR retDepot_risw_returns_cur IS
SELECT SUM(ol.extended_price) price
FROM exp_order_lines ol, exp_order_headers oh
WHERE oh.batch_id = vr_dm.batch_id
AND oh.order_header_id = ol.order_header_id
AND ol.return_waste_indate_flag = 'R'
AND ol.cap_line = 'Y'
AND ol.return_in_system_indicator = 'Y'
AND ol.wholesaler_fax_unipak_flag = (SELECT tc_initial FROM
exp_trading_community
WHERE tc_id = vr_dm.tc_id)
AND NVL(ol.cla_rx_code_drug_code, 'RX') IN
(SELECT dea_class_code FROM exp_dea_class WHERE
exp_dea_group = vr_dm.exp_dea_group)
AND ol.manufacturer_name IN (SELECT manufacturer_name FROM
mfrs_in_debit_memo
WHERE exp_dm_number =
vr_dm.exp_dm_number
AND parent_mfr_id = P_MFR_ID);
BEGIN
SELECT * INTO vr_dm FROM EXP_DEBIT_MEMO WHERE
EXP_DM_NUMBER=P_DM_NUMBER;
SELECT * INTO vr_eb FROM EXP_BATCH WHERE BATCH_ID=vr_dm.BATCH_ID;
IF vr_dm.BATCH_ID IS NOT NULL THEN -- CAP
IF NVL(vr_dm.DM_FOR_RISW_FLAG,'N') = 'N' AND vr_dm.TC_TYPE_ID =
1 THEN
FOR mfr_phy_returns_rec IN mfr_phy_returns_cur LOOP
vn_amount:=mfr_phy_returns_rec.price;
END LOOP;
ELSIF NVL(vr_dm.DM_FOR_RISW_FLAG,'N') = 'Y' AND vr_dm.TC_TYPE_ID
= 1 THEN
FOR mfr_risw_returns_rec IN mfr_risw_returns_cur LOOP
vn_amount:=mfr_risw_returns_rec.price;
END LOOP;
ELSIF NVL(vr_dm.DM_FOR_RISW_FLAG,'N') = 'N' AND vr_dm.TC_TYPE_ID
= 6 THEN
FOR retDepot_phy_returns_rec IN retDepot_phy_returns_cur LOOP
vn_amount:=retDepot_phy_returns_rec.price;
END LOOP;
ELSIF NVL(vr_dm.DM_FOR_RISW_FLAG,'N') = 'Y' AND vr_dm.TC_TYPE_ID
= 6 THEN
FOR retDepot_risw_returns_rec IN retDepot_risw_returns_cur
LOOP
vn_amount:=retDepot_risw_returns_rec.price;
END LOOP;
END IF;
ELSE -- Non-CAP
null;
END IF;
return NVL(vn_amount,0);
END GET_DM_AMOUNT;

The function is really big and hence for about 2500 records if it has
to go through the function each time, it will take time. But can
somebody suggests another method of doing this function.

thanks

Gints Plivna wrote:

Sujatha wrote:
Again...I ask...."Have you taken those SQL statements and tuned them to
ensure that they are optimal?"

Your first cursor is defined as:

CURSOR mfr_phy_returns_cur IS
SELECT SUM(extended_price) price
FROM exp_order_lines
WHERE order_line_id IN
(SELECT order_line_id FROM exp_contents_of_bag WHERE
container_id IN
(SELECT container_id FROM exp_containers
WHERE manufacturer_id = vr_dm.tc_id
AND batch_id = vr_dm.batch_id
AND exp_dea_group = vr_dm.EXP_DEA_GROUP
AND container_type_id = 1
AND parent_id IS NOT NULL
AND NVL(ship_via, 'F') = NVL(vr_dm.RETURN_VIA,'F')));

Have you tuned this SELECT query? It seems to me that one approach would
be to write this as a join instead of using subqueries:

SELECT SUM(o.extended_price) price
FROM exp_order_lines o, exp_contents_of_bag b, exp_containers c
WHERE o.order_line_id = b.order_line_id
AND b.container_id = c.container_id
AND c.manufacturer_id = vr_dm.tc_id
AND c.batch_id = vr_dm.batch_id
AND c.exp_dea_group = vr_dm.EXP_DEA_GROUP
AND c.container_type_id = 1
AND c.parent_id IS NOT NULL
AND NVL(c.ship_via, 'F') = NVL(vr_dm.RETURN_VIA,'F')));

Does that change the runtime of the cursor? The truth is that I do not
know...only you will know that. But you'll have to take each cursor's
query and tune that (as I have already said twice before).

And looking at your function's code, you have other SELECT statements
that are not inside a cursor. For instance, you have:

SELECT * INTO vr_dm FROM EXP_DEBIT_MEMO WHERE EXP_DM_NUMBER=P_DM_NUMBER;

Have you tuned that query?

Trying to be as polite as possible...I have already stated this three
times...I cannot wave a magic wand and tune your queries for you.
Querying tuning is a process sufficient to warrant an entire volume on
the subject. So I cannot state here in this forum an easy way to do that.

Cheers,
Brian



--
================================================== =================

Brian Peasland
dba (AT) nospam (DOT) peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Sujatha wrote:
Please stop top posting.

Brian is trying to help you. If you are not going to pay
attention and be responsive to what he's saying then YOYO.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org