RE: FW: pl/sql array processing?
There is no simple way, What is important for you ... value of the element or the
index of the element in the pl/sql table? Looks like the index of the element is
important for you.
Tell us again what is the problem? is it that you have too many array elements? where
does the time goes? it should be in the execution of the function .. not in navigating
from one element to next.
What version of oracle?
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
Guang Mei
Sent: Tuesday, January 27, 2004 2:59 PM
To: Multiple recipients of list ORACLE-L
Sorry I did not make it clear that the number I used here (1, 9, 15,99) are
just examples, the actual element index is a varible and they are not
continuous. Yes, refTbl can be defined into a package. I guess what I am
asking is if there is a way in pl/sql to do something like
-- FORALL array element indexes (they are non-continuous)
call a package function (parameter: element index)
-- end for
without looping the array.
-- orginal code:
declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
Guang
-Original Message-
Mladen Gogala
Sent: Tuesday, January 27, 2004 2:29 PM
To: Multiple recipients of list ORACLE-L
On 01/27/2004 02:09:25 PM, Jesse, Rich wrote:
Couldn't the declarations be put into a package? We've done this in
order
to maintain values for the life of the session.
Yes, they could, I didn't see it in this example.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Guang Mei
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Jamadagni, Rajendra
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
query plan is bad when it is run inside a pl/sql stored procedure
All, i have this query: SELECT count(1) FROM ats.emktg_members t1 WHERE NOT EXISTS ( SELECT 'x' FROM gcd_data_source_details t2 WHERE t2.universal_id = t1.universal_id AND t2.data_source_id = 13 ) AND upper(t1.email) NOT LIKE '%TATA.COM'; This query finishes in about 5 minutes. The plan is: Operation Object Name Rows Bytes Cost Object Node SELECT STATEMENT Hint=CHOOSE 1 14919 SORT AGGREGATE 1 75 HASH JOIN ANTI 272 K 19 M 14919 TABLE ACCESS FULL EMKTG_MEMBERS 274 K 14 M 1 TABLE ACCESS BY INDEX ROWID GCD_DATA_SOURCE_DETAILS 1 K 21 K 391 INDEX RANGE SCAN DSD_DSRC_FKI 23 K 27 However, when the same query is run from a stored procedure, it picks up a bad plan (with nested loops join) and does not complete even after 6 hours ! Giving HASH_AJ hint did not change the plan. Any ideas how we can fix this (without using stored outlines) ? The database is 9204 on sun solaris. regards, Sumant __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: S.Sarkar INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: query plan is bad when it is run inside a pl/sql stored procedure
It's a bad query that could probably be resolved throuh an analytic function but I don't normally delve into things like that before having finished my 2nd coffee. You can use hints, in particular, there is a hint to force hash join. On 01/27/2004 06:44:25 AM, S.Sarkar wrote: All, i have this query: SELECT count(1) FROM ats.emktg_members t1 WHERE NOT EXISTS ( SELECT 'x' FROM gcd_data_source_details t2 WHERE t2.universal_id = t1.universal_id AND t2.data_source_id = 13 ) AND upper(t1.email) NOT LIKE '%TATA.COM'; This query finishes in about 5 minutes. The plan is: Operation Object Name Rows Bytes Cost Object Node SELECT STATEMENT Hint=CHOOSE 1 14919 SORT AGGREGATE 1 75 HASH JOIN ANTI 272 K 19 M 14919 TABLE ACCESS FULL EMKTG_MEMBERS 274 K 14 M 1 TABLE ACCESS BY INDEX ROWID GCD_DATA_SOURCE_DETAILS 1 K 21 K 391 INDEX RANGE SCAN DSD_DSRC_FKI 23 K 27 However, when the same query is run from a stored procedure, it picks up a bad plan (with nested loops join) and does not complete even after 6 hours ! Giving HASH_AJ hint did not change the plan. Any ideas how we can fix this (without using stored outlines) ? The database is 9204 on sun solaris. regards, Sumant __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: S.Sarkar INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: query plan is bad when it is run inside a pl/sql stored
Is the sql really the same query is run from a stored procedure or is it perhaps using in place of the '%TATA.COM' a plsql variable (which is set to %TATA.COM)? At 04:44 AM 1/27/2004, you wrote: All, i have this query: SELECT count(1) FROM ats.emktg_members t1 WHERE NOT EXISTS ( SELECT 'x' FROM gcd_data_source_details t2 WHERE t2.universal_id = t1.universal_id AND t2.data_source_id = 13 ) AND upper(t1.email) NOT LIKE '%TATA.COM'; This query finishes in about 5 minutes. The plan is: Operation Object Name Rows Bytes Cost Object Node SELECT STATEMENT Hint=CHOOSE 1 14919 SORT AGGREGATE 1 75 HASH JOIN ANTI 272 K 19 M 14919 TABLE ACCESS FULL EMKTG_MEMBERS 274 K 14 M 1 TABLE ACCESS BY INDEX ROWID GCD_DATA_SOURCE_DETAILS 1 K 21 K 391 INDEX RANGE SCAN DSD_DSRC_FKI 23 K 27 However, when the same query is run from a stored procedure, it picks up a bad plan (with nested loops join) and does not complete even after 6 hours ! Giving HASH_AJ hint did not change the plan. Any ideas how we can fix this (without using stored outlines) ? The database is 9204 on sun solaris. regards, Sumant __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: S.Sarkar INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
FW: pl/sql array processing?
My following message did not seem to make it to oracle-l.freelists. Let me
try it again.
Guang
-
Hi,
I have the folliwng pl/sql code for oracle 8173. I am wondering if there is
a way to make it faster by not looping each array elements, but doing some
kind of forall opration to my_package.function?
declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: FW: pl/sql array processing?
Declare
type numTbl is table of number index by binary_integer;
refTbl numTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
forall i in refTbl.first..refTbllast
begin
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
end;
end;
/
Of course, it doesn't make sense because varchar2 variable string will
be lost after each iteration. May be an actual example that makes sense
would motivate more people to respond.
On 01/27/2004 12:54:29 PM, Guang Mei wrote:
My following message did not seem to make it to oracle-l.freelists.
Let me
try it again.
Guang
-
Hi,
I have the folliwng pl/sql code for oracle 8173. I am wondering if
there is
a way to make it faster by not looping each array elements, but doing
some
kind of forall opration to my_package.function?
declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Mladen Gogala
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: FW: pl/sql array processing?
Couldn't the declarations be put into a package? We've done this in order
to maintain values for the life of the session.
Rich
Rich JesseSystem/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
-Original Message-
Sent: Tuesday, January 27, 2004 12:39 PM
To: Multiple recipients of list ORACLE-L
Declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
forall i in refTbl.first..refTbllast
begin
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
end;
end;
/
Of course, it doesn't make sense because varchar2 variable string will
be lost after each iteration. May be an actual example that makes sense
would motivate more people to respond.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: FW: pl/sql array processing?
On 01/27/2004 02:09:25 PM, Jesse, Rich wrote: Couldn't the declarations be put into a package? We've done this in order to maintain values for the life of the session. Yes, they could, I didn't see it in this example. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: FW: pl/sql array processing?
If mypackage.function(i) is doing some DML operation on i, then the real
way to make it faster is to modify the signature of
mypackage.function(i) to take an array instead, and to do a forall ...
dml operation within mypackage.function(i).
forall is most useful when you want to minimize context switching
between the pl/sql and sql engines for an array. Using forall you'll be
switching context only once whereas using a regular for loop you'll be
switching context for every member of the array.
Regards,
Dave
[EMAIL PROTECTED] wrote:
Declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
forall i in refTbl.first..refTbllast
begin
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
end;
end;
/
Of course, it doesn't make sense because varchar2 variable string
will be lost after each iteration. May be an actual example that
makes sense
would motivate more people to respond.
On 01/27/2004 12:54:29 PM, Guang Mei wrote:
My following message did not seem to make it to oracle-l.freelists.
Let me
try it again.
Guang
-
Hi,
I have the folliwng pl/sql code for oracle 8173. I am wondering if
there is
a way to make it faster by not looping each array elements, but doing
some
kind of forall opration to my_package.function?
declare
type numTbl is table of number index by binary_integer;
refTbl numTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: David Hau
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: FW: pl/sql array processing?
Sorry I did not make it clear that the number I used here (1, 9, 15,99) are
just examples, the actual element index is a varible and they are not
continuous. Yes, refTbl can be defined into a package. I guess what I am
asking is if there is a way in pl/sql to do something like
-- FORALL array element indexes (they are non-continuous)
call a package function (parameter: element index)
-- end for
without looping the array.
-- orginal code:
declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
Guang
-Original Message-
Mladen Gogala
Sent: Tuesday, January 27, 2004 2:29 PM
To: Multiple recipients of list ORACLE-L
On 01/27/2004 02:09:25 PM, Jesse, Rich wrote:
Couldn't the declarations be put into a package? We've done this in
order
to maintain values for the life of the session.
Yes, they could, I didn't see it in this example.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Guang Mei
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: FW: pl/sql array processing?
forall should be used as follows:
forall index in lower_bound..upper_bound
sql statement;
Putting anything other than a sql statement (e.g. a pl/sql block) in a
forall statement defeats its purpose.
If you think about it, forall achieves its performance improvement by
binding arrays to the arguments of a sql statement, instead of binding
individual elements of the array to the sql statement. This is faster
because now the entire array is passed from the pl/sql engine to the sql
engine all in one shot, and so this minimizes context switching between
the pl/sql engine and the sql engine. This is analogous to the array
binding in OCI if you're familiar with OCI or Pro*C programming.
Putting anything other than a sql statement in a forall statement does
not achieve any benefit because you're not switching context to the sql
engine.
So Guang, I think what you should do is move the forall closest to where
you're doing the sql (DML) operation. If you're doing the DML within
mypackage.function(...) then pass the entire array into
mypackage.function(...) and then do the forall within mypackage.function.
Regards,
Dave
[EMAIL PROTECTED] wrote:
Sorry I did not make it clear that the number I used here (1, 9, 15,99) are
just examples, the actual element index is a varible and they are not
continuous. Yes, refTbl can be defined into a package. I guess what I am
asking is if there is a way in pl/sql to do something like
-- FORALL array element indexes (they are non-continuous)
call a package function (parameter: element index)
-- end for
without looping the array.
-- orginal code:
declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
Guang
-Original Message-
Mladen Gogala
Sent: Tuesday, January 27, 2004 2:29 PM
To: Multiple recipients of list ORACLE-L
On 01/27/2004 02:09:25 PM, Jesse, Rich wrote:
Couldn't the declarations be put into a package? We've done this in
order
to maintain values for the life of the session.
Yes, they could, I didn't see it in this example.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: David Hau
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: query plan is bad when it is run inside a pl/sql stored
it is the same. '%TATA.COM' is not a variable. sumant --- Wolfgang Breitling [EMAIL PROTECTED] wrote: Is the sql really the same query is run from a stored procedure or is it perhaps using in place of the '%TATA.COM' a plsql variable (which is set to %TATA.COM)? At 04:44 AM 1/27/2004, you wrote: All, i have this query: SELECT count(1) FROM ats.emktg_members t1 WHERE NOT EXISTS ( SELECT 'x' FROM gcd_data_source_details t2 WHERE t2.universal_id = t1.universal_id AND t2.data_source_id = 13 ) AND upper(t1.email) NOT LIKE '%TATA.COM'; This query finishes in about 5 minutes. The plan is: Operation Object Name Rows Bytes Cost Object Node SELECT STATEMENT Hint=CHOOSE 1 14919 SORT AGGREGATE 1 75 HASH JOIN ANTI 272 K 19 M 14919 TABLE ACCESS FULL EMKTG_MEMBERS 274 K 14 M 1 TABLE ACCESS BY INDEX ROWID GCD_DATA_SOURCE_DETAILS 1 K 21 K 391 INDEX RANGE SCAN DSD_DSRC_FKI 23 K 27 However, when the same query is run from a stored procedure, it picks up a bad plan (with nested loops join) and does not complete even after 6 hours ! Giving HASH_AJ hint did not change the plan. Any ideas how we can fix this (without using stored outlines) ? The database is 9204 on sun solaris. regards, Sumant __ Do you Yahoo!? Yahoo! SiteBuilder - Free web site building tool. Try it! http://webhosting.yahoo.com/ps/sb/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: S.Sarkar INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
[oracle-l] FW: pl/sql array processing?
My following message did not seem to make it to oracle-l.freelists. Let me
try it again.
Guang
-
Hi,
I have the folliwng pl/sql code for oracle 8173. I am wondering if there is
a way to make it faster by not looping each array elements, but doing some
kind of forall opration to my_package.function?
declare
type numTbl is table of number index by binary_integer;
refTblnumTbl;
i number;
str varchar2(30);
begin
refTbl (1) := 1;
refTbl (9) := 1;
refTbl(15) := 1;
refTbl(99) := 1;
i := refTbl.first;
while i is not null loop
dbms_output.put_line ('i=' || i);
str:= my_package.function(i);
i := refTbl.next(i);
end loop;
end;
/
TIA.
Guang
-
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-
To unsubscribe send email to: [EMAIL PROTECTED]
put 'unsubscribe' in the subject line.
-
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: fetching long variable in PL/SQL
Okey but how can I convert it -Original Message- Sent: Sunday, January 25, 2004 4:44 PM To: Multiple recipients of list ORACLE-L convert to a lob and use a substr. That might fix the problem. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, January 25, 2004 10:24 AM Hi all , I have a problem about fetching long variable in PL/SQL. I encountered with problem in following PL/SQL block. When I try to fetch long variable , I receive PL/SQL: numeric or value error ORA-06512. As I searched in metalink, it is releated to size of long variable. Probably it is greather than 32KB. I could not find a solution. Do you have any info or and advice that how I can fetch long variables greather than 32KB. May it can be divided into more than one variable but how. Thanks Arslan. DECLARE USER_TRIGGERS_COUNT NUMBER := 0; USER_TRIGGERS_COUNTER NUMBER := 0; CURSOR USER_TRIGGERS_CUR IS SELECT TRIGGER_NAME ,DESCRIPTION , trigger_BODY FROM USER_TRIGGERS where trigger_name = 'TRG_APPL_CONTRACTS_BIUD_R' ; USER_TRIGGERS_R USER_TRIGGERS_CUR%ROWTYPE; BEGIN select COUNT(*) INTO USER_TRIGGERS_COUNT from USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER'; OPEN USER_TRIGGERS_CUR; LOOP FETCH USER_TRIGGERS_CUR INTO l_TRIGGER_NAME ,l_DESCRIPTION ,l_BODY ; EXIT WHEN(USER_TRIGGERS_CUR%NOTFOUND ); NULL; END LOOP; CLOSE USER_TRIGGERS_CUR; END; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ryan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
fetching long variable in PL/SQL
Hi all , I have a problem about fetching long variable in PL/SQL. I encountered with problem in following PL/SQL block. When I try to fetch long variable , I receive PL/SQL: numeric or value error ORA-06512. As I searched in metalink, it is releated to size of long variable. Probably it is greather than 32KB. I could not find a solution. Do you have any info or and advice that how I can fetch long variables greather than 32KB. May it can be divided into more than one variable but how. Thanks Arslan. DECLARE USER_TRIGGERS_COUNT NUMBER := 0; USER_TRIGGERS_COUNTER NUMBER := 0; CURSOR USER_TRIGGERS_CUR IS SELECT TRIGGER_NAME ,DESCRIPTION , trigger_BODY FROM USER_TRIGGERS where trigger_name = 'TRG_APPL_CONTRACTS_BIUD_R' ; USER_TRIGGERS_R USER_TRIGGERS_CUR%ROWTYPE; BEGIN select COUNT(*) INTO USER_TRIGGERS_COUNT from USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER'; OPEN USER_TRIGGERS_CUR; LOOP FETCH USER_TRIGGERS_CUR INTO l_TRIGGER_NAME ,l_DESCRIPTION ,l_BODY ; EXIT WHEN(USER_TRIGGERS_CUR%NOTFOUND ); NULL; END LOOP; CLOSE USER_TRIGGERS_CUR; END; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: fetching long variable in PL/SQL
convert to a lob and use a substr. That might fix the problem. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, January 25, 2004 10:24 AM Hi all , I have a problem about fetching long variable in PL/SQL. I encountered with problem in following PL/SQL block. When I try to fetch long variable , I receive PL/SQL: numeric or value error ORA-06512. As I searched in metalink, it is releated to size of long variable. Probably it is greather than 32KB. I could not find a solution. Do you have any info or and advice that how I can fetch long variables greather than 32KB. May it can be divided into more than one variable but how. Thanks Arslan. DECLARE USER_TRIGGERS_COUNT NUMBER := 0; USER_TRIGGERS_COUNTER NUMBER := 0; CURSOR USER_TRIGGERS_CUR IS SELECT TRIGGER_NAME ,DESCRIPTION , trigger_BODY FROM USER_TRIGGERS where trigger_name = 'TRG_APPL_CONTRACTS_BIUD_R' ; USER_TRIGGERS_R USER_TRIGGERS_CUR%ROWTYPE; BEGIN select COUNT(*) INTO USER_TRIGGERS_COUNT from USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER'; OPEN USER_TRIGGERS_CUR; LOOP FETCH USER_TRIGGERS_CUR INTO l_TRIGGER_NAME ,l_DESCRIPTION ,l_BODY ; EXIT WHEN(USER_TRIGGERS_CUR%NOTFOUND ); NULL; END LOOP; CLOSE USER_TRIGGERS_CUR; END; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ryan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: fetching long variable in PL/SQL
You have to use dbms_sql ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Sunday, January 25, 2004 10:44 AM To: Multiple recipients of list ORACLE-L convert to a lob and use a substr. That might fix the problem. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, January 25, 2004 10:24 AM Hi all , I have a problem about fetching long variable in PL/SQL. I encountered with problem in following PL/SQL block. When I try to fetch long variable , I receive PL/SQL: numeric or value error ORA-06512. As I searched in metalink, it is releated to size of long variable. Probably it is greather than 32KB. I could not find a solution. Do you have any info or and advice that how I can fetch long variables greather than 32KB. May it can be divided into more than one variable but how. Thanks Arslan. DECLARE USER_TRIGGERS_COUNT NUMBER := 0; USER_TRIGGERS_COUNTER NUMBER := 0; CURSOR USER_TRIGGERS_CUR IS SELECT TRIGGER_NAME ,DESCRIPTION , trigger_BODY FROM USER_TRIGGERS where trigger_name = 'TRG_APPL_CONTRACTS_BIUD_R' ; USER_TRIGGERS_R USER_TRIGGERS_CUR%ROWTYPE; BEGIN select COUNT(*) INTO USER_TRIGGERS_COUNT from USER_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER'; OPEN USER_TRIGGERS_CUR; LOOP FETCH USER_TRIGGERS_CUR INTO l_TRIGGER_NAME ,l_DESCRIPTION ,l_BODY ; EXIT WHEN(USER_TRIGGERS_CUR%NOTFOUND ); NULL; END LOOP; CLOSE USER_TRIGGERS_CUR; END; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ryan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
pl/sql tables in pga and ora-4030 was pga_aggregate_target and a
I contined testing with pl/sql testprogram and found some interesting prove
about this 1 gb limit for pga with pat set.
All tests are done on hpux11.11 9.2.0.4
Testprogram
create or replace procedure testarray( psize number ) as
begin
declare
TYPE nAllotment_tabtypIS TABLE OF number
INDEX BY BINARY_INTEGER;
assarray nAllotment_tabtyp;
assarray2 nAllotment_tabtyp;
assarray3 nAllotment_tabtyp;
uitleg varchar2(100);
begin
uitleg := 'start loop';
for i in 1..psize loop
uitleg := 'insert i= ' || i;
assarray(i) := i;
/*
uitleg := 'insert i2= ' || i;
assarray2(i) := i;
*/
end loop;
/*
EXCEPTION
WHEN OTHERS THEN
dbms_output.enable(2);
dbms_output.put_line(' Exception raised ' || uitleg );
*/
end;
end;
Quotes from my last update to the tar:
When setting all manual
I see the pga going over 2Gb and the showing negative numbers by looking
at v$sessstat, os-level I only have top and like I mentioneed earlier you
see
that going up 2 Gb also and further to 4Gb not above this limit as
expected!!
Notice I am now testing with a hpux setting datasegment 4Gb (ulimit 4194303)
Test 1: workarea_size_policy=manual pat=0
After a few minutes running
NAME VALUE
--
session uga memory 81312
session uga memory max 112960
session pga memory 2132275152
session pga memory max 2132275152
Still monitoring this, the amount seem to stuck after 15 minutes or so at
this
4Gb (value of top)
end value:
NAME VALUE
--
session uga memory 81312
session uga memory max 112960
session pga memory -154903592
session pga memory max -154903592
After more then 30 minutes finally
it crashes agaIN
VU_2exec testarray( 1 );
begin testarray( 1 ); end;
*
ERROR at line 1:
ORA-06500: PL/SQL: storage error
ORA-06512: at VRIJ_UIT.TESTARRAY, line 14
ORA-06512: at line 1
U_2select pool, sum(bytes) from v$sgastat group by pool;
POOL SUM(BYTES)
--- --
large pool 218103808
shared pool 570425344
68143904
AME TYPE VALUE
---
--
pga_aggregate_target big integer 0
23:32:33 SQL show parameter workarea
It seems impossible that such a simple pl/sql can eat up 4Gb of memory.
Other
bugs like 3194895 and docid 3156574 are suggesting a 1Gb pga limit (which
might
be raised by changing data segment). There is mentioned a patch also
according
to 3194895 to lift this, can you find this patch and see if it might be
Text continued in next action...
23-JAN-04 22:40:10
Text continued from previous action...
appropriate?
Output from top
Memory: 3733508K (3051156K) real, 5720660K (4872688K) virtual, 70976K free
Page# 1/14
CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU
COMMAND
0 ? 23640 oracle 128 20 4116M 2436M sleep 10:00 2.54 2.53
oracleVU_2
New info : Test 2: workarea_size_policy=auto pat=200M same shared_pool of
500m
I am utterly convinced this is all done in pga outside shared pool so
enlarging this only gets me the same problem sooner
Ahh and now after already 2.5 minutes I get a similar problem
at the 1Gb limit
U_2exec testarray( 1 );
begin testarray( 1 ); end;
*
ERROR at line 1:
ORA-06500: PL/SQL: storage error
ORA-06512: at VRIJ_UIT.TESTARRAY, line 14
ORA-06512: at line 1
Elapsed: 00:02:32.62
23:42:57 SQL /
NAME VALUE
--
session uga memory 78464
session uga memory max 143872
session pga memory 1071096624
session pga memory max 1071096624
23:44:17 SQL /
NAME VALUE
--
session uga memory 78464
session uga memory max 143872
session pga memory 1071096624
session pga memory max 1071096624
23:45:08 SQL show parameter pga
NAME TYPE VALUE
---
--
pga_aggregate_target big integer 209715200
23:47:18 SQL show parameter workarea
NAME TYPE VALUE
---
--
workarea_size_policy string AUTO
So it looks workarea_size_policy is definitely limiting max pga available
but strange thing is that the 200Mb for pat is meant to be for sort_area and
we
are not using sort_area here just filling an array
I don't know why you don't get the same results but this is definitely weird
and looks familiar with other bugs filed
Regards,
Jeroen
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jeroen van Sluisdam
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from
ORA-06505 PL/SQL: variable requires more than 32767 bytes of contiguous memory
Dear DBAs,
Good Morning. Using PL/SQL procedure I am trying to spool out
Japanese Characters with VARCHR2(3600) size and I am getting this error.
Here is my code. For your information it is Japanese Characters
Spool c:\test.log
Declare
cursor c1 is select contact_details from test;
begin
DBMS_OUTPUT.ENABLE(100);
For x in c1 loop
DBMS_OUTPUT.PUT_LINE(''||x.contact_details||'');
End loop;
End;
/
Spool off
Could you please suggest for other ideas I tried with UTL_FILE
also.. Since my OS is English I am getting some Junk Characters
Thank You
Mudhalvan M.M
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mudhalvan, Moovarkku
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: ORA-06505 PL/SQL: variable requires more than 32767 bytes of contiguous memory - Thank You !!!!!
Dear Friends,
(B
(BThank you so much. Yes i was able to solve this issue..
(B
(BFYI ... I used CONVERT function
(B
(B
(BUTL_FILE.PUT_LINE(fh,convert('"$BD9=j%^%M!<%8%c!(J","$BEj;qL>(J","CAPID","$B
a quick pl/sql question
Hi: In pl/sql, I want to add chr(10) into a string in every 70th position. The string can be up to 2000 characters long. The follwoing code works. But is there an even FASTER way to do this? Thanks. Guang --- declare pos number := 1; len number; buf varchar2(2000); x varchar2(2100); begin buf := '012345678901234567890123456789012345678901234567890123456789012345678901234 5678901234567890123456789012345678901234567890123456789012345678901234567890 12345678901234567890123456789012345678901234567890123456789A'; len := length(buf); while pos=len loop x := x || substr(buf, pos, 70) || chr(10); pos := pos+70; end loop; end; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
How to remove PL/SQL successfully completed Message
Hi, I am trying to spool the result from PL/SQL procedure. At the end of result I am getting PL/SQL successfully completed. Could anyone tell me how to avoid that message?. This is how my sql looks like set linesize 200 set trimspool on set verify off echo off set serveroutput on spool c:\mudhalvan\test.txt declare begin end; / spool off Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to remove PL/SQL successfully completed Message
set feedback off Raj -Original Message- Sent: Tuesday, January 06, 2004 3:49 AM To: Multiple recipients of list ORACLE-L Hi, I am trying to spool the result from PL/SQL procedure. At the end of result I am getting PL/SQL successfully completed. Could anyone tell me how to avoid that message?. This is how my sql looks like set linesize 200 set trimspool on set verify off echo off set serveroutput on spool c:\mudhalvan\test.txt declare begin end; / spool off Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to remove PL/SQL successfully completed Message
SET FEEDBACK OFF Regards, Charu. -Original Message- Behalf Of Mudhalvan, Moovarkku Sent: 06 January 2004 14:19 To: Multiple recipients of list ORACLE-L Hi, I am trying to spool the result from PL/SQL procedure. At the end of result I am getting PL/SQL successfully completed. Could anyone tell me how to avoid that message?. This is how my sql looks like set linesize 200 set trimspool on set verify off echo off set serveroutput on spool c:\mudhalvan\test.txt declare begin end; / spool off Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). * Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. * Visit us at http://www.mahindrabt.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Charu Joshi INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to remove PL/SQL successfully completed Message
Dear Raj Thank you so much. Yeah it is working fine -Original Message- Sent: Tuesday, January 06, 2004 6:05 PM To: Multiple recipients of list ORACLE-L set feedback off Raj -Original Message- Sent: Tuesday, January 06, 2004 3:49 AM To: Multiple recipients of list ORACLE-L Hi, I am trying to spool the result from PL/SQL procedure. At the end of result I am getting PL/SQL successfully completed. Could anyone tell me how to avoid that message?. This is how my sql looks like set linesize 200 set trimspool on set verify off echo off set serveroutput on spool c:\mudhalvan\test.txt declare begin end; / spool off Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Mudhalvan, Moovarkku INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to remove PL/SQL successfully completed Message
Hi Mudhalvan , set feed off ; did u try this ? not sure though : ) Regards, Prem. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mudhalvan, Moovarkku Hi, I am trying to spool the result from PL/SQL procedure. At the end of result I am getting PL/SQL successfully completed. Could anyone tell me how to avoid that message?. This is how my sql looks like set linesize 200 set trimspool on set verify off echo off set serveroutput on -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to remove PL/SQL successfully completed Message
- From: Mudhalvan, Moovarkku [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tue, 06 Jan 2004 00:49:25 Hi, I am trying to spool the result from PL/SQL procedure. At the end of result I am getting PL/SQL successfully completed. Could anyone tell me how to avoid that message?. a) Make the procedure fail -- or -- b) set feedback off SF -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: How to remove PL/SQL successfully completed Message
set feedback off Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, January 06, 2004 3:49 AM To: Multiple recipients of list ORACLE-L Hi, I am trying to spool the result from PL/SQL procedure. At the end of result I am getting PL/SQL successfully completed. Could anyone tell me how to avoid that message?. This is how my sql looks like set linesize 200 set trimspool on set verify off echo off set serveroutput on spool c:\mudhalvan\test.txt declare begin end; / spool off Thank You Mudhalvan M.M -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mudhalvan, Moovarkku INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Goulet, Dick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: pl/sql open cursor question
Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: Functions: create or replace function a return varchar2 is begin for srec in (select dummy from ctest) loop return srec.dummy; end loop; return null; end; / create or replace function b return varchar2 is cursor c1 is select dummy from ctest; v_dummy varchar2(1) := null; begin open c1; fetch c1 into v_dummy; close c1; return v_dummy; end; / create or replace function b2 return varchar2 is v_dummy varchar2(1) := null; begin select dummy into v_dummy from ctest; return v_dummy; exception when no_data_found then return null; end; / Testrun: set serveroutput on size 2 exec runstats_pkg.rs_start; declare l_loop number := 0; l_dummy varchar2(1); begin for l_loop in 1..1000 loop l_dummy := a; end loop; end; / exec runstats_pkg.rs_middle; declare l_loop number := 0; l_dummy varchar2(1); begin for l_loop in 1..1000 loop l_dummy := b; end loop; end; / exec runstats_pkg.rs_stop(1); For the test of b2 b was simply replaced by b2) The results: a vs b: Run1 ran in 18 hsecs Run2 ran in 18 hsecs run 1 ran in 100% of the time Name Run1 Run2 Diff LATCH.enqueue hash chains 28 26 -2 LATCH.enqueues 28 26 -2 LATCH.library cache 2,067 2,069 2 LATCH.redo allocation 33 31 -2 LATCH.library cache pin 2,046 2,048 2 STAT...enqueue requests 16 14 -2 STAT...enqueue releases 16 14 -2 STAT...calls to get snapshot s 4,011 4,009 -2 STAT...active txn count during 16 8 -8 STAT...consistent gets - exami 16 8 -8 STAT...calls to kcmgcs 16 8 -8 STAT...cleanout - number of kt 16 8 -8 STAT...CPU used by this sessio 33 23 -10 STAT...consistent gets 3,026 3,016 -10 STAT...CPU used when call star 33 23 -10 STAT...redo entries 46 34 -12 LATCH.cache buffers chains 6,226 6,212 -14 STAT...db block changes 63 49 -14 STAT...db block gets 100 68 -32 STAT...session logical reads 3,126 3,084 -42 STAT...redo size 30,224 29,720 -504 STAT...recursive calls 1,001 2,001 1,000 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 11,543 11,525 -18 100.16% a vs b2: Run1 ran in 17 hsecs Run2 ran in 23 hsecs run 1 ran in 73.91% of the time Name Run1 Run2 Diff LATCH.enqueue hash chains 28 26 -2 LATCH.enqueues 28 26 -2 LATCH.library cache 2,067 2,069 2 STAT...bytes received via SQL* 984 986 2 LATCH.library cache pin 2,046 2,048 2 LATCH.redo allocation 34 31 -3 STAT...CPU used by this sessio 32 29 -3 STAT...enqueue releases 17 14 -3 STAT...enqueue requests 17 14 -3 STAT...CPU used when call star 32 29 -3 STAT...calls to get snapshot s 4,013 4,009 -4 STAT...active txn count during 17 8 -9 STAT...cleanout - number of kt 17 8 -9 STAT...calls to kcmgcs 17 8 -9 STAT...consistent gets - exami 17 8 -9 STAT...consistent gets 3,029 3,016 -13 STAT...recursive cpu usage 12 25 13 STAT...redo entries 49 34 -15 STAT...db block changes 69 48 -21 LATCH.cache buffers chains 6,235 6,207 -28 STAT...db block gets 111 66 -45 STAT...session logical reads 3,140 3,082 -58 STAT...redo size 30,648 29,660 -988 STAT...recursive calls 1,001 2,001 1,000 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 11,557 11,519 -38 100.33% (9.2.0.2/SuSE 8.1) I find it quite strange that results vary from run to run. E.g. in a vs b active txn count was 16 for a, whilst it was 17 for a vs. b2. Why this difference? B variants are consequently cheaper in redo size, session logical reads. Recursive calls is conseqently 1000 higher for b/b2. What counts is runtime, and a and b have no differences, b2, is appr. 25% slower. b/b2 consume slightly less latches, but the diff is 1 %. So, IMHO what remains is the question of taste. I started 22 years ago as software engineer, worked in RD departments (of commercial software firms) and built a 4GL, including a compiler and a universal interpreter for the code produced. Maybe bearing the burdens of my career as a stone-age 'C'-developer influences my 'taste' of beautiness of code (or the lack off it). Unless I've put some flaws in my testruns (which I'm glad to hear of), I rest my case. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === At 17:44 28-12-03 -0800, you wrote: Carel, It might seem that the loop construct would be more expensive, but it didn't appear that way on my test system. ( 9.2.0.4, RH 8.0 ) function a: create or replace function a return varchar2 is begin for srec in (select dummy from ctest) loop return srec.dummy; end loop; return null; end; / function b: create or replace function b return varchar2 is cursor c1 is select dummy from ctest; v_dummy varchar2(1) := null; begin open c1; fetch c1 into v_dummy; close
Re: pl/sql open cursor question
There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. Technically, if the implicit code and the explicit code were written to do exactly the same thing, then the implicit code should be faster because of a couple of under-cover optimisations. (This has been true for several years, I believe). Currently (9.2.0.X-ish) there is a bug that I recently found on metalink which says something about the FETCHes from an implicit cursor using more CPU than the FETCHes from an explicit cursor. Bottom line - test it in the environment where you are using it, and on the version you are running in production. In almost all cases, the difference will probably be imperceptible, anyway. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:49 AM Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: RE: large pl/sql table sucking up all memory on a server
i know about the limit clause. I just want to keep someone else from bringing down an instance. I think Ill get a taser and fry the next person who does it. :) From: zhu chao [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 10:34:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: RE: large pl/sql table sucking up all memory on a server I think Unix Kernel parameter limit should help in this case. It can prevent runaway process from consuming the whole machine resource. In most unix, there is kernel parameter(or ulimit) that restrict the maximum heap/data segment size.And the parameter name depend on the OS. Also, as other guys said, in oracle, there is also work around. You can use limit clause of bulk collect. Feature should be properly used. Regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 1:34 AM we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
Re: RE: large pl/sql table sucking up all memory on a server
That works. I prefer thumb presses, they worked for the Inquisition and they lasted 500 years... dr Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - I think Ill get a taser and fry the next person who does it. :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: pl/sql open cursor question
There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. That isn't a factor, as I never use the results from the first run for that very reason. Jared Jonathan Lewis [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/30/2003 03:29 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pl/sql open cursor question There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. Technically, if the implicit code and the explicit code were written to do exactly the same thing, then the implicit code should be faster because of a couple of under-cover optimisations. (This has been true for several years, I believe). Currently (9.2.0.X-ish) there is a bug that I recently found on metalink which says something about the FETCHes from an implicit cursor using more CPU than the FETCHes from an explicit cursor. Bottom line - test it in the environment where you are using it, and on the version you are running in production. In almost all cases, the difference will probably be imperceptible, anyway. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:49 AM Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: pl/sql open cursor question
I wasn't thinking of the boundary conditions, I was thinking of the totally different mechanisms that appear because you are running pl/sql rather than (say) a loop in Pro*C that sends a pure SQL statement 1,000 times to the database. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 5:49 PM There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. That isn't a factor, as I never use the results from the first run for that very reason. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: pl/sql open cursor question
At 09:49 30-12-03 -0800, you wrote: There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. That isn't a factor, as I never use the results from the first run for that very reason. Jared Neither did I. But what wonders me is that even after the firest run (preceded by a flush of the shared_pool) every subsequent run (whithout flush, of course) gave other figures. The ones I included in my message where the most representative figures of over 20 runs. I stopped processes like vmware etc to get the system as stable as possible, but differences stayed pretty significant. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === Jonathan Lewis [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/30/2003 03:29 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: Re: pl/sql open cursor question There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. Technically, if the implicit code and the explicit code were written to do exactly the same thing, then the implicit code should be faster because of a couple of under-cover optimisations. (This has been true for several years, I believe). Currently (9.2.0.X-ish) there is a bug that I recently found on metalink which says something about the FETCHes from an implicit cursor using more CPU than the FETCHes from an explicit cursor. Bottom line - test it in the environment where you are using it, and on the version you are running in production. In almost all cases, the difference will probably be imperceptible, anyway. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:49 AM Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
large pl/sql table sucking up all memory on a server
One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: large pl/sql table sucking up all memory on a server
Assign the developer a profile that would do good. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: large pl/sql table sucking up all memory on a server
Does he still have a job? :) Was it one session or many of them? How many rows got bulk processed? If it's one session that caused this, then it's either: vary badly designed, there is memory leak, or the system is already short in memory! Waleed -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Khedr, Waleed INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: RE: large pl/sql table sucking up all memory on a server
we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: RE: large pl/sql table sucking up all memory on a server
3 million records in a forall statement. we are bringing on temps and you know how that goes... Im hoping I can set a parameter somewhere to keep anyone from bringing down a server. such as 'memory for pl/sql table area limit hit' errors out what he is doing. i guess not :( From: Khedr, Waleed [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:29:32 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Does he still have a job? :) Was it one session or many of them? How many rows got bulk processed? If it's one session that caused this, then it's either: vary badly designed, there is memory leak, or the system is already short in memory! Waleed -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Khedr, Waleed INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: large pl/sql table sucking up all memory on a server
Ryan, First off, PL/SQL tables have nothing to do with the buffer cache. The buffer cache is part of the SGA (shared memory) and is used to buffer blocks of database datafiles. That's all that will ever be in the buffer cache. PL/SQL tables are memory constructs that are allocated from the PGA (process private memory). When you connect to an instance, (in dedicated server mode) the background process on the server side that's allocated to serve your connection has memory associated w/ it. That's your PGA (and UGA, for that matter.) The best way to deal with this is to educate the developers. Teach them that the LIMIT clause is their friend. Are you on 9i? PGA_AGGREGATE_TARGET may help. I'm not sure, I've never tried that experiment on 9i. What happens when PGA memory demand due to PL/SQL tables exceeds PGA_AGGREGATE_TARGET? I'll have to try that test Anyhow, hope that helps, -Mark PS In the future, if this happens again, you shouldn't have to bounce the server. Just kill the background process that's eating all the memory. When you do that, that developers session will die, and things should quickly return to normal. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Mon 12/29/2003 11:59 AM To: Multiple recipients of list ORACLE-L Cc: Subject:large pl/sql table sucking up all memory on a server One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). winmail.dat
Re: RE: large pl/sql table sucking up all memory on a server
it filled up the pga and then used 'swap' space on the hard drive. this filled up. didnt realize this was a feature. yeah, I know i can 'tell' them to do it. I was hoping to disallow it though. From: Bobak, Mark [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 01:24:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Ryan, First off, PL/SQL tables have nothing to do with the buffer cache. The buffer cache is part of the SGA (shared memory) and is used to buffer blocks of database datafiles. That's all that will ever be in the buffer cache. PL/SQL tables are memory constructs that are allocated from the PGA (process private memory). When you connect to an instance, (in dedicated server mode) the background process on the server side that's allocated to serve your connection has memory associated w/ it. That's your PGA (and UGA, for that matter.) The best way to deal with this is to educate the developers. Teach them that the LIMIT clause is their friend. Are you on 9i? PGA_AGGREGATE_TARGET may help. I'm not sure, I've never tried that experiment on 9i. What happens when PGA memory demand due to PL/SQL tables exceeds PGA_AGGREGATE_TARGET? I'll have to try that test Anyhow, hope that helps, -Mark PS In the future, if this happens again, you shouldn't have to bounce the server. Just kill the background process that's eating all the memory. When you do that, that developers session will die, and things should quickly return to normal. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Mon 12/29/2003 11:59 AM To: Multiple recipients of list ORACLE-L Cc: Subject: large pl/sql table sucking up all memory on a server One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). encoded content removed -- binaries not allowed by ListGuruContent-Type: application/ms-tnef; The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. The previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification.
Re: RE: large pl/sql table sucking up all memory on a server
Check profile option PRIVATE_SGA (available from 9i and needs resource_limit parameter to be true). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 7:34 PM we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Tanel Poder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: RE: large pl/sql table sucking up all memory on a server
Btw, PRIVATE_SGA only limits shared server SGA memory usage, for limiting PGA sizes you could use _pga_max_size (defaults to 200M), but this is getting kind of dirty and is unsupported (and works starting from 9i) Tanel. - Original Message - To: [EMAIL PROTECTED] Sent: Monday, December 29, 2003 10:03 PM Check profile option PRIVATE_SGA (available from 9i and needs resource_limit parameter to be true). Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 7:34 PM Subject: Re: RE: large pl/sql table sucking up all memory on a server we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858
Re: RE: large pl/sql table sucking up all memory on a server
FYI. The USPS delivery just (10 minutes ago) arrived with my copy of Mastering Oracle PL/SQL Practical Solutions, which I ordered from Book Pool, at: http://www.bookpool.com/.x/mzttmcaj4i/sm/1590592174 As you can see, its not available yet on Amazon: http://www.amazon.com/exec/obidos/tg/detail/-/1590592174 /qid=1072734291/sr=1-1/ref=sr_1_1/102-9815245-5757732?v=glances=books If you look on pg249, it discusses bulk collect and pga memory, e.g. I crashed my database session (and shortly thereafter my laptop) because insufficient memory was available to hold the set of 100 employee records. ... This is where a pipelined solution can help. I'm not sure if this will help in this case, but at least I hope this opens the discussion to include a 'new' reference on PL/SQL. Happy Holidays. Regards, Mike Thomas --- Tanel Poder [EMAIL PROTECTED] wrote: Btw, PRIVATE_SGA only limits shared server SGA memory usage, for limiting PGA sizes you could use _pga_max_size (defaults to 200M), but this is getting kind of dirty and is unsupported (and works starting from 9i) Tanel. - Original Message - To: [EMAIL PROTECTED] Sent: Monday, December 29, 2003 10:03 PM Check profile option PRIVATE_SGA (available from 9i and needs resource_limit parameter to be true). Tanel. - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 29, 2003 7:34 PM Subject: Re: RE: large pl/sql table sucking up all memory on a server we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY
Re: RE: large pl/sql table sucking up all memory on a server
This is probably old hat for you, but given it's Unix (Sun) and it's a client process, wouldn't you be able to use ulimit to stop memory allocation growing past a certain size? The other thing I'd try is to limit memory through the resource control in Oracle. But that is highly version dependent and I'm not sure which version you running. Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 5:39 AM it filled up the pga and then used 'swap' space on the hard drive. this filled up. didnt realize this was a feature. yeah, I know i can 'tell' them to do it. I was hoping to disallow it though. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: RE: large pl/sql table sucking up all memory on a server
I think Unix Kernel parameter limit should help in this case. It can prevent runaway process from consuming the whole machine resource. In most unix, there is kernel parameter(or ulimit) that restrict the maximum heap/data segment size.And the parameter name depend on the OS. Also, as other guys said, in oracle, there is also work around. You can use limit clause of bulk collect. Feature should be properly used. Regards Zhu Chao. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 1:34 AM we dont have that level of granularity. everyone developers out of the same DBA account(not my call). any parameter settings to limit the size of pl/sql tables? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/12/29 Mon PM 12:14:24 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: large pl/sql table sucking up all memory on a server Assign the developer a profile that would do good. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 29, 2003 12:00 PM To: Multiple recipients of list ORACLE-L One of our guys used a very large bulk collect into with a forall update. It sucked up all the swap space on our solaris box and noone could connect to it. So we had to bounce the server. I was under the impression that pl/sql tables go into the buffer cache and cannot go large than its size? Oracle typically holds your hand with memory usage issues. Are there any parameter settings I can use that limit the size of pl/sql tables? Or are they just dynamic arrays that can grow as large as you want. I know your supposed to use a 'limit' command on them. I didnt write it. I just dont want it to happen again. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
pl/sql open cursor question
I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: pl/sql open cursor question
cursor for loops automatically close cursors. dont use when others then null on code you are putting in an application. if you have a bug you will have a hard time finding it. Its a fundamental flaw. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 28, 2003 10:54 AM I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ryan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: pl/sql open cursor question
Hi if we assume it is implements this way (see below) there will only be one cursor since c_gid is a bind variable and there for the cursor will be sharded from call to call of the function. create or replace function XYZ (gid in number) return varchar2 is cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid; begin for x in cur1(gid) loop return x.c1; end loop; return null; exception when others then return null; end; It will only be one coursor Guang Mei wrote: I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Best regards/Venlig hilsen Peter Gram MiracleA/S Kratvej 2 DK - 2760 Mlv Cell:(+45) 2527 7107 Phone:(+45) 4466 8855 Fax:(+45) 4466 8856 Home:(+45) 3874 5696 Email:[EMAIL PROTECTED]
Re: pl/sql open cursor question
I thought just the execution plan was shared? I thought the definition of a cursor, was the memory area used to store the data. That data does not stay persistent in memory with a cursor for loop it closes. correct me if Im wrong? - Original Message - From: Peter Gram To: Multiple recipients of list ORACLE-L Sent: Sunday, December 28, 2003 12:39 PM Subject: Re: pl/sql open cursor question Hi if we assume it is implements this way (see below) there will only be one cursor since c_gidis a bind variable and there for the cursor will be sharded from call to call of the function.create or replace function XYZ (gid in number) return varchar2 is cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid;begin for x in cur1(gid) loop return x.c1; end loop; return null;exception when others then return null;end;It will only be one coursor Guang Mei wrote: I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Best regards/Venlig hilsen Peter Gram MiracleA/SKratvej 2DK - 2760 Måløv Cell:(+45) 2527 7107Phone:(+45) 4466 8855Fax:(+45) 4466 8856Home:(+45) 3874 5696Email:[EMAIL PROTECTED]
Re: pl/sql open cursor question
Hi: I thought in the orginal code (cursor cur1 is select C1 from tab1 where ID = gid;), gid is a parameter passed in so it is already a bind variable. I don't see any difference to what you proposed. Your method is just make cur1 take a paramter? Am I wrong here? Also what happens when your function is called from different sessions? Is cursor_shared = force need to be set in init.ora? Guang On Sun, 28 Dec 2003, Peter Gram wrote: Hi if we assume it is implements this way (see below) there will only be one cursor since c_gid is a bind variable and there for the cursor will be sharded from call to call of the function. create or replace function XYZ (gid in number) return varchar2 is cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid; begin for x in cur1(gid) loop return x.c1; end loop; return null; exception when others then return null; end; It will only be one coursor Guang Mei wrote: I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Best regards/Venlig hilsen /*Peter Gram*/ mailto:[EMAIL PROTECTED] Miracle A/S http://www.miracleas.dk/ Kratvej 2 DK - 2760 Måløv Cell: (+45) 2527 7107 Phone: (+45) 4466 8855 Fax: (+45) 4466 8856 Home: (+45) 3874 5696 Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: pl/sql open cursor question
What I don't understand is the loop construction: Actually only one (row) is read form the cursor, and then the function is left with a return. Because it's an unconditional return, the code within the loop will either execute once, or never. When no data is found NULL is returned. When an error occurs NULL is returned as well. So, why a loop? Wouldn't it be better to have something like: create or replace function XYZ(gid in number) return varchar2 is l_c1 tab1.C1%TYPE; /* local variable to store C1 */ begin select c1 into l_c1 from tab1 where id = gid; return l_c1; exception when no_data_found then return some_error_code; /* let the caller know that no data is found */ when others then return 'ERROR: '||TO_CHAR(SQLCODE); /* return the error-code, preceded by the text ERROR for identification */ end; Sure, a loop prevents an ORA-1422, but I don't think a loop construction should be abused for this. Just think about all loop controlling code that needs to be set up by the interpreter. tab1.ID should be unique, so a 1422 normally cannot occur. Robust programming however asks us to prevent any error. I would prefer to think about how a 1422 should be handled, and write some code accordingly. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === At 09:39 28-12-03 -0800, you wrote: Hi if we assume it is implements this way (see below) there will only be one cursor since c_gid is a bind variable and there for the cursor will be sharded from call to call of the function. create or replace function XYZ (gid in number) return varchar2 is cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid; begin for x in cur1(gid) loop return x.c1; end loop; return null; exception when others then return null; end; It will only be one coursor Guang Mei wrote: I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Best regards/Venlig hilsen Peter Gram Miracle A/S Kratvej 2 DK - 2760 Måløv Cell: (+45) 2527 7107 Phone: (+45) 4466 8855 Fax: (+45) 4466 8856 Home: (+45) 3874 5696 Email: [EMAIL PROTECTED]
Re: pl/sql open cursor question
dont use when others then null on code you are putting in an application. if you have a bug you will have a hard time finding it. Its a fundamental flaw. One place where I have found it justified, is in logon trigger where users must be able to log on, despite any errors which occur in a logon trigger... (continued) ..of course with some kind of error logging mechanism implemented. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: pl/sql open cursor question
dont use when others then null on code you are putting in an application. if you have a bug you will have a hard time finding it. Its a fundamental flaw. One place where I have found it justified, is in logon trigger where users must be able to log on, despite any errors which occur in a logon trigger... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: pl/sql open cursor question
Carel, It might seem that the loop construct would be more expensive, but it didn't appear that way on my test system. ( 9.2.0.4, RH 8.0 ) function a: create or replace function a return varchar2 is begin for srec in (select dummy from ctest) loop return srec.dummy; end loop; return null; end; / function b: create or replace function b return varchar2 is cursor c1 is select dummy from ctest; v_dummy varchar2(1) := null; begin open c1; fetch c1 into v_dummy; close c1; return v_dummy; end; / The resource consumption for a 1000 iterations of each: ( a is the first column ) 17:38:42 poirot.jks.com - [EMAIL PROTECTED] SQL @run_stats NAME RUN1 RUN2 DIFF -- -- -- LATCH.Consistent RBA 0 1 1 LATCH.cache buffers lru chain 1 0 -1 LATCH.lgwr LWN SCN0 1 1 LATCH.mostly latch-free SCN 0 1 1 LATCH.session idle bit0 1 1 STAT...calls to get snapshot scn: kcmgss 3012 3013 1 STAT...calls to kcmgcs7 6 -1 STAT...cleanout - number of ktugct calls 0 1 1 STAT...consistent gets - examination 0 1 1 STAT...session cursor cache hits 1 2 1 STAT...parse count (total)1 2 1 STAT...opened cursors current 1 2 1 STAT...opened cursors cumulative 1 2 1 STAT...messages sent 0 1 1 STAT...free buffer requested 1 0 -1 STAT...execute count 1003 1004 1 STAT...deferred (CURRENT) block cleanout 4 3 -1 applications STAT...calls to kcmgas0 1 1 STAT...user commits 0 1 1 STAT...active txn count during cleanout 0 1 1 LATCH.enqueues0 1 1 LATCH.dml lock allocation 0 2 2 LATCH.session allocation 0 2 2 STAT...db block changes 25 27 2 STAT...enqueue releases 0 2 2 STAT...consistent gets 3010 3012 2 LATCH.cache buffers chains 6130 6133 3 STAT...redo entries 17 20 3 STAT...recursive cpu usage4 7 3 STAT...db block gets 30 33 3 LATCH.redo writing0 3 3 LATCH.undo global data1 4 3 LATCH.library cache 7 4 -3 LATCH.enqueue hash chains 0 4 4 LATCH.redo allocation18 22 4 LATCH.library cache pin 7 3 -4 LATCH.messages0 5 5 STAT...session logical reads 3040 3045 5 STAT...commit cleanouts 0 7 7 STAT...commit cleanouts successfully com 0 7 7 pleted STAT...redo size 27184 27820636 STAT...recursive calls 2004 3007 1003 42 rows selected. The for loop actually appears to be somewhat less expensive in terms of database resources. Jared On Sun, 2003-12-28 at 11:39, Carel-Jan Engel wrote: What I don't understand is the loop construction: Actually only one (row) is read form the cursor, and then the function is left with a return. Because it's an unconditional return, the code within the loop will either execute once, or never. When no data is found NULL is returned. When an error occurs NULL is returned as well. So, why a loop? Wouldn't it be better to have something like: create or replace function XYZ(gid in number) return varchar2 is l_c1 tab1.C1%TYPE;/* local variable to store C1 */ begin select c1 into l_c1 fromtab1 where id = gid; return l_c1; exception when no_data_found then return some_error_code; /* let the caller know that no data is found */ when others then return 'ERROR: '||TO_CHAR(SQLCODE); /* return the error-code, preceded by
RE: 8i pl/sql question
Does myFunction1 often get called with the same arguments? In your example, the second argument is never repeated, but in the real thing, would the same second argument be likely to repeat? If myFunction1 gets the same arguments, will it always return the same value? If so, then it is a deterministic function, and you can declare it as one by putting the keyword DETERMINISTIC before the IS/AS in its header. This way the optimiser will know not to recalculate the function if it is called again with the same arguments, but will reuse the value it calculated before. -Original Message- Sent: Monday, December 15, 2003 7:54 PM To: Multiple recipients of list ORACLE-L can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Flack INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: 8i pl/sql question
The first argumant (myID) is a variable that is different every time the function gets called. The second argument is a hard code number (just as in my orginal message). So I guess I could not use DETERMINISTIC here. I have not heard of DETERMINISTIC before but I will take a look of this becuase it probably can be used at a couple of places if it works as you described. Thanks. Guang -Original Message- John Flack Sent: Tuesday, December 16, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Does myFunction1 often get called with the same arguments? In your example, the second argument is never repeated, but in the real thing, would the same second argument be likely to repeat? If myFunction1 gets the same arguments, will it always return the same value? If so, then it is a deterministic function, and you can declare it as one by putting the keyword DETERMINISTIC before the IS/AS in its header. This way the optimiser will know not to recalculate the function if it is called again with the same arguments, but will reuse the value it calculated before. -Original Message- Sent: Monday, December 15, 2003 7:54 PM To: Multiple recipients of list ORACLE-L can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: 8i pl/sql question
what does myfunction1() do? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 16, 2003 10:24 AM To: Multiple recipients of list ORACLE-L The first argumant (myID) is a variable that is different every time the function gets called. The second argument is a hard code number (just as in my orginal message). So I guess I could not use DETERMINISTIC here. I have not heard of DETERMINISTIC before but I will take a look of this becuase it probably can be used at a couple of places if it works as you described. Thanks. Guang -Original Message- John Flack Sent: Tuesday, December 16, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Does myFunction1 often get called with the same arguments? In your example, the second argument is never repeated, but in the real thing, would the same second argument be likely to repeat? If myFunction1 gets the same arguments, will it always return the same value? If so, then it is a deterministic function, and you can declare it as one by putting the keyword DETERMINISTIC before the IS/AS in its header. This way the optimiser will know not to recalculate the function if it is called again with the same arguments, but will reuse the value it calculated before. -Original Message- Sent: Monday, December 15, 2003 7:54 PM To: Multiple recipients of list ORACLE-L can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: 8i pl/sql question
Below is the code for myfunction1 in the package, It calls a bunch of other
functions, such as getBlastMatches, escapeGene, genes.gene2protein and
addItem.
Guang
---
type blastMatch is record (
geneName gene.name%type,
percent number
);
function myfunction1 (seqid in number,
sid in number,
secondsid in number default -1) return varchar2 is
tbl blastMatches;
cnt number;
item varchar2(256);
str varchar2(256);
begin
cnt := getBlastMatches(seqid, sid, tbl, secondsid);
if cnt1 then return null; end if;
for i in 1..cnt loop
item := escapeGene(sid, tbl(i).geneName,
genes.gene2protein(tbl(i).geneName, sid)) || ' ('
|| tbl(i).percent || '%)';
if not addItem(str, item, '; ', 80) then exit; end if;
end loop;
return str;
exception
when others then return null;
end myfunction1;
--
function getBlastMatches (seqid in number,
spid in number,
matchTable out blastMatches,
secondspid in number default -1) return number is
cursor bcur is
select queryid, subjid, 100.0*identity/matchlen pct
from blastresults
where ((subjspid in (spid,secondspid) and queryid = seqid) or
(queryspid in (spid,secondspid) and subjid = seqid)) and
(identity/matchlen = .200 or positive/matchlen = .400)
order by blast.pvalToNumber(pval) asc, score desc;
match number;
cnt number := 0;
gname gene.name%type;
begin
for bmatch in bcur loop
if bmatch.queryid=seqid then match := bmatch.subjid;
else match := bmatch.queryid; end if;
BEGIN
select name into gname from gene,seqtable
where geneid=gene.id and aaseqid = match and
gene.use = 'Y' and seqtable.use='Y';
EXCEPTION
when no_data_Found then gname := NULL;
END;
if gname is not null then
cnt := cnt + 1;
matchTable(cnt).geneName := gname;
matchTable(cnt).percent := round(bmatch.pct, 0);
if cnt = maxMatches then return cnt; end if;
end if;
end loop;
return cnt;
exception
when others then return 0;
end getBlastMatches;
-Original Message-
Jamadagni, Rajendra
Sent: Tuesday, December 16, 2003 10:45 AM
To: Multiple recipients of list ORACLE-L
what does myfunction1() do?
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
Sent: Tuesday, December 16, 2003 10:24 AM
To: Multiple recipients of list ORACLE-L
The first argumant (myID) is a variable that is different every time the
function gets called. The second argument is a hard code number (just as in
my orginal message). So I guess I could not use DETERMINISTIC here. I have
not heard of DETERMINISTIC before but I will take a look of this becuase it
probably can be used at a couple of places if it works as you described.
Thanks.
Guang
-Original Message-
John Flack
Sent: Tuesday, December 16, 2003 8:44 AM
To: Multiple recipients of list ORACLE-L
Does myFunction1 often get called with the same arguments? In your example,
the second argument is never repeated, but in the real thing, would the same
second argument be likely to repeat? If myFunction1 gets the same
arguments, will it always return the same value? If so, then it is a
deterministic function, and you can declare it as one by putting the keyword
DETERMINISTIC before the IS/AS in its header. This way the optimiser will
know not to recalculate the function if it is called again with the same
arguments, but will reuse the value it calculated before.
-Original Message-
Sent: Monday, December 15, 2003 7:54 PM
To: Multiple recipients of list ORACLE-L
can you return multiple values from a modified version of myfunction(1) ??
If so, then you can replace multiple calls by only one. And no, bulk binds
is only within dml/select statements.
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
Sent: Monday, December 15, 2003 4:34 PM
To: Multiple recipients of list ORACLE-L
Hi:
Oracle 8173 DB.
I have a package funtion, part of it is doing something like this:
utl_file.put_line(fpn, myFunction1(myID, 1));
utl_file.put_line(fpn, myFunction1(myID, 8));
utl_file.put_line(fpn, myFunction1(myID, 6));
utl_file.put_line(fpn, myFunction1(myID, 35));
utl_file.put_line(fpn, myFunction1(myID, 33));
utl_file.put_line(fpn, myFunction1(myID, 7));
utl_file.put_line(fpn, myFunction1(myID, 102));
utl_file.put_line(fpn,
8i pl/sql question
Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: 8i pl/sql question
can you return multiple values from a modified version of myfunction(1) ?? If so, then you can replace multiple calls by only one. And no, bulk binds is only within dml/select statements. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 15, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi: Oracle 8173 DB. I have a package funtion, part of it is doing something like this: utl_file.put_line(fpn, myFunction1(myID, 1)); utl_file.put_line(fpn, myFunction1(myID, 8)); utl_file.put_line(fpn, myFunction1(myID, 6)); utl_file.put_line(fpn, myFunction1(myID, 35)); utl_file.put_line(fpn, myFunction1(myID, 33)); utl_file.put_line(fpn, myFunction1(myID, 7)); utl_file.put_line(fpn, myFunction1(myID, 102)); utl_file.put_line(fpn, myFunction1(myID, 10)); utl_file.put_line(fpn, myFunction1(myID, 9)); utl_file.put_line(fpn, myFunction1(myID, 15)); utl_file.put_line(fpn, myFunction1(myID, 2)); myFunction1 (returns a varchar2 string) here is another function in the same package and it calls a bunch of other functions. Is there a way to speed up the performance of the above lines by using bulk bind? I am brain-dead now and can not seem to find if it can be done and/or how it can be done. TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re:Re: two oracle pl/sql programmers needed (50k/yr)
In Austin, Texas -- Original Message Date: Tue, 09 Dec 2003 06:14:30 -0800 System Manager: Where are these positions located geographically? Me - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 7:44 AM please send an email to me,if you're interested. _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: KENNETH JANUSZ INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
two oracle pl/sql programmers needed (50k/yr)
please send an email to me,if you're interested. _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: two oracle pl/sql programmers needed (50k/yr)
System Manager: Where are these positions located geographically? Me - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 7:44 AM please send an email to me,if you're interested. _ Free email with personality! Over 200 domains! http://www.MyOwnEmail.com Looking for friendships,romance and more? http://www.MyOwnFriends.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: system manager INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: KENNETH JANUSZ INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re[2]: Writing a delay in PL/SQL?
Saturday, November 29, 2003, 10:44:26 PM, Khedr, Waleed ([EMAIL PROTECTED]) wrote: KW I'm curious why? some testing? Yes. Testing. I want a Data Pump job to run long enough for me to be able to play around in interactive mode from several different clients. I only have 28MB of data, and the load runs too fast for me to do much of anything. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Writing a delay in PL/SQL?
As you've heard, dbms_lock.sleep(nn.nn) will do what you want. For short time intervals, it should do what you want - but bear in mind that it has a 2.4% error built in. Oracle Corp. seem to think that one second lasts 1024 milliseconds. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 1:34 AM I know this is going to sound rather crazy, but I want to write an INSERT trigger that imposes an arbitrary delay, say a half second, or maybe a full second, on each and every insert operation. Does anyone know offhand whether there's a built-in PL/SQL procedure to just wait for a specified period of time? Any suggestions on how I can go about implementing this trigger? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Re[2]: Writing a delay in PL/SQL?
Another way would have been to use not autoextending datafiles and resumable operations during data load... Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, November 30, 2003 4:54 PM Saturday, November 29, 2003, 10:44:26 PM, Khedr, Waleed ([EMAIL PROTECTED]) wrote: KW I'm curious why? some testing? Yes. Testing. I want a Data Pump job to run long enough for me to be able to play around in interactive mode from several different clients. I only have 28MB of data, and the load runs too fast for me to do much of anything. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Tanel Poder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re[4]: Writing a delay in PL/SQL?
Sunday, November 30, 2003, 10:34:25 AM, Tanel Poder ([EMAIL PROTECTED]) wrote: TP Another way would have been to use not autoextending datafiles and resumable TP operations during data load... I've actually done something similar, which worked for most of what I want to test. But now I want to observe a job actually running and doing something. Now I want to slow down the rate of insertion, but not stop it. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Writing a delay in PL/SQL?
I know this is going to sound rather crazy, but I want to write an INSERT trigger that imposes an arbitrary delay, say a half second, or maybe a full second, on each and every insert operation. Does anyone know offhand whether there's a built-in PL/SQL procedure to just wait for a specified period of time? Any suggestions on how I can go about implementing this trigger? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Writing a delay in PL/SQL?
Hi try DBMS_LOCK.SLEEP (seconds IN NUMBER); /peter Jonathan Gennick wrote: I know this is going to sound rather crazy, but I want to write an INSERT trigger that imposes an arbitrary delay, say a half second, or maybe a full second, on each and every insert operation. Does anyone know offhand whether there's a built-in PL/SQL procedure to just wait for a specified period of time? Any suggestions on how I can go about implementing this trigger? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Peter Gram comp : Miracle A/S Addr : Kratvej 2, 2760 Maaloev Phone : +45 2527 7107, Fax : +45 4466 8856, Home +45 3874 5696 mail : [EMAIL PROTECTED] - http://www.miracleas.dk Upcoming events: Miracle Master Class with Tom Kyte, 12-14 January 2004 Visit http://miracleas.dk/en/events.html#MasterClass Visit http://www.miracleas.dk fore news ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Gram INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Writing a delay in PL/SQL?
dbms_lock.sleep http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_lock2. htm#998469 - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 8:34 PM I know this is going to sound rather crazy, but I want to write an INSERT trigger that imposes an arbitrary delay, say a half second, or maybe a full second, on each and every insert operation. Does anyone know offhand whether there's a built-in PL/SQL procedure to just wait for a specified period of time? Any suggestions on how I can go about implementing this trigger? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Ryan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Writing a delay in PL/SQL?
DBMS_LOCK.SLEEP (30)-- It will sleep for 30 secs -Original Message- Jonathan Gennick Sent: Saturday, November 29, 2003 8:34 PM To: Multiple recipients of list ORACLE-L I know this is going to sound rather crazy, but I want to write an INSERT trigger that imposes an arbitrary delay, say a half second, or maybe a full second, on each and every insert operation. Does anyone know offhand whether there's a built-in PL/SQL procedure to just wait for a specified period of time? Any suggestions on how I can go about implementing this trigger? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Sami INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Writing a delay in PL/SQL?
sys.dbms_lock.sleep ( seconds_to_sleep ) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, November 29, 2003 7:34 PM I know this is going to sound rather crazy, but I want to write an INSERT trigger that imposes an arbitrary delay, say a half second, or maybe a full second, on each and every insert operation. Does anyone know offhand whether there's a built-in PL/SQL procedure to just wait for a specified period of time? Any suggestions on how I can go about implementing this trigger? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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 Perry INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Writing a delay in PL/SQL?
try DBMS_LOCK.SLEEP() Cheers Jeff Herrick On Sat, 29 Nov 2003, Jonathan Gennick wrote: I know this is going to sound rather crazy, but I want to write an INSERT trigger that imposes an arbitrary delay, say a half second, or maybe a full second, on each and every insert operation. Does anyone know offhand whether there's a built-in PL/SQL procedure to just wait for a specified period of time? Any suggestions on how I can go about implementing this trigger? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Jeff Herrick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re[2]: Writing a delay in PL/SQL?
Saturday, November 29, 2003, 8:59:34 PM, a whole bunch of people wrote: try DBMS_LOCK.SLEEP() Thanks all. I'll give that a try. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Writing a delay in PL/SQL?
I'm curious why? some testing? Regards, Waleed -Original Message- Sent: Saturday, November 29, 2003 8:34 PM To: Multiple recipients of list ORACLE-L I know this is going to sound rather crazy, but I want to write an INSERT trigger that imposes an arbitrary delay, say a half second, or maybe a full second, on each and every insert operation. Does anyone know offhand whether there's a built-in PL/SQL procedure to just wait for a specified period of time? Any suggestions on how I can go about implementing this trigger? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Khedr, Waleed INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Writing a delay in PL/SQL?
DBMS_LOCK.SLEEP On 2003.11.29 20:34, Jonathan Gennick wrote: I know this is going to sound rather crazy, but I want to write an INSERT trigger that imposes an arbitrary delay, say a half second, or maybe a full second, on each and every insert operation. Does anyone know offhand whether there's a built-in PL/SQL procedure to just wait for a specified period of time? Any suggestions on how I can go about implementing this trigger? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: SQL and PL/SQL tuning template document required urgently
Dennis, Excellent recommendation, Guy Harrison's book (2nd Edition) is excellent the best I've seen on SQL tuning. I've used it for a number of years. I had the 1st edition and then bought the 2nd when it came out. Cheers, Chris -Original Message- Sent: 29 October 2003 15:49 To: Multiple recipients of list ORACLE-L Ranganath Since you mentioned proactive and reactive query tuning, I think the philosophy with which one approaches the tuning exercise means everything. Wrong philosophy and you spend your time spinning your wheels. All of us have only a limited amount of time to devote, so the best approach will make the best use of that time. Get Optimizing Oracle Performance by Cary Millsap. It doesn't take long to read the important parts. Implement Cary's approach to locate the queries where you will get the most bang for the buck. Then use books like Guy Harrison's (Ryan's suggestion) for pointers on making those queries perform better. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 29, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Hi there, Does any body have a template for proactive and reactive query tuning which can be used as a guideline/report while tuning simple, medium complex and complex SQL queries and PL/SQL stored procedures? If so, can you please forward the same to me please? If not, can anybody suggest as to how to go about doing one? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ranganath K INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Dunscombe, Chris INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: ORA-1000 and pl/sql cursor cache
Jared
Thanks for the response. I've had a play and here are the conclusions: -
1) The cache controlled by session_cached_cursors is entirely separate from
the pl/sql static cursor cache. You can turn the former off by setting
session_cached_cursors to zero, but you can only turn the latter off by
logging out, dropping or recompiling the pl/sql block.
2) The latter cache only operates on *named* blocks: procedure, function or
package (not sure about triggers). Hence, your script will not show the
behaviour since it uses an anonymous block.
3) _close_cached_open_cursors does indeed close the cursors on commit.
Handy, but I don't really want to commit every time I run a query (plus it
starts with an underscore:-O).
--
David Lord
-Original Message-
From: Jared Still [mailto:[EMAIL PROTECTED]
Sent: 27 November 2003 20:05
To: Multiple recipients of list ORACLE-L
Subject: RE: ORA-1000 and pl/sql cursor cache
Try playing with alternately setting session_cached_cursors
to 0 and some non-zero value and run the following script.
Try setting _close_cached_open_cursors to both true and
false, changing which 'commit' is used, and omitting the
'commit' altogether.
On 9i I find that with session_cached_cursors = 0, and
a 'commit' following the pl/sql block, the only cursor
appearing in v$open_cursor is the final 'commit'.
HTH
Jared
--
alter session set _close_cached_open_cursors = false;
declare
xyz varchar2(30);
begin
select user into xyz from dual;
commit;
end;
/
--commit;
--
On Thu, 2003-11-27 at 01:09, Lord David wrote:
Barb
Thanks for the link. Unfortunately, it again hints at the
behaviour,
but doesn't really come out with it.
What's happening is that when you execute a *static* sql statement
such as 'select user into xyz from dual', the cursor
remains open (as
shown in
v$open_cursors) after the statement has finished. This
also happens with
explicit cursors, even if you close them! And it happens whether
session_cached_cursors is set to zero or not. I assume
that the cursors are
cached within the session context for the package or
procedure since it only
happens for named pl/sql blocks and they get closed if you
recompile the
block.
My guess is that it is a deliberate performance optimisation within
the pl/sql engine, but it does mean that to avoid ora-1000
errors, you
need to set open_cursors to be greater than the *total* number of
static sql statements that a session can open in its lifetime, not
just the number concurrently open. Of course you have also got to
include room for dynamic and recursive sql as well as
cursors cached
using session_cached_cursors.
--
David Lord
-Original Message-
From: Barbara Baker [mailto:[EMAIL PROTECTED]
Sent: 26 November 2003 16:49
To: Multiple recipients of list ORACLE-L
Subject: Re: ORA-1000 and pl/sql cursor cache
David:
I don't really know if this will help you, but it
might be worth a try. You could try setting
session_cached_cursors.
Bjørn Engsig's white paper Efficient use of bind variables,
cursor_sharing and related cursor parameters describes this
parameter a bit. It can be found at http://miracleas.dk in the
Technical Information section) ( Guy Harrison's tuning book
also talks about this parameter. )
good luck!
Barb
--- Lord David [EMAIL PROTECTED] wrote:
Hi
Does anyone know whether its possible to control the
size of the pl/sql
static cursor cache.
I'm running into ORA-01000: maximum number of open cursors
exceeded errors and part of the problem (apart from the usual
developers not closing
explicit cursors) is that _all_ static sql
statements in compiled pl/sql
units seem to be getting cached. I can't find any
documentation of
this feature apart from a few hints in the pl/sql and
application development
docs. Here's an example from an 8.1.7 database: -
SQLcreate or replace procedure foobar is
2 v_result varchar2(30);
3 begin
4 select user into v_result from dual;
5 end;
6 /
Procedure created.
SQL
SQLselect b.sql_text
2 from v$session a, v$open_cursor b
3 where a.sid = b.sid
4 and a.audsid = userenv('SESSIONID')
5 /
SQL_TEXT
SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
select b.sql_text from v$session a, v$open_cursor b
where a.
SQL
SQLexec foobar
PL/SQL procedure successfully completed.
SQL
SQLselect b.sql_text
2 from v$session a, v$open_cursor b
3 where a.sid = b.sid
4 and a.audsid = userenv('SESSIONID')
5 /
SQL_TEXT
RE: ORA-1000 and pl/sql cursor cache
Barb
Thanks for the link. Unfortunately, it again hints at the behaviour, but
doesn't really come out with it.
What's happening is that when you execute a *static* sql statement such as
'select user into xyz from dual', the cursor remains open (as shown in
v$open_cursors) after the statement has finished. This also happens with
explicit cursors, even if you close them! And it happens whether
session_cached_cursors is set to zero or not. I assume that the cursors are
cached within the session context for the package or procedure since it only
happens for named pl/sql blocks and they get closed if you recompile the
block.
My guess is that it is a deliberate performance optimisation within the
pl/sql engine, but it does mean that to avoid ora-1000 errors, you need to
set open_cursors to be greater than the *total* number of static sql
statements that a session can open in its lifetime, not just the number
concurrently open. Of course you have also got to include room for dynamic
and recursive sql as well as cursors cached using session_cached_cursors.
--
David Lord
-Original Message-
From: Barbara Baker [mailto:[EMAIL PROTECTED]
Sent: 26 November 2003 16:49
To: Multiple recipients of list ORACLE-L
Subject: Re: ORA-1000 and pl/sql cursor cache
David:
I don't really know if this will help you, but it
might be worth a try. You could try setting session_cached_cursors.
Bjørn Engsig's white paper Efficient use of bind
variables, cursor_sharing and related cursor
parameters describes this parameter a bit. It can be
found at http://miracleas.dk in the Technical
Information section) ( Guy Harrison's tuning book
also talks about this parameter. )
good luck!
Barb
--- Lord David [EMAIL PROTECTED] wrote:
Hi
Does anyone know whether its possible to control the
size of the pl/sql
static cursor cache.
I'm running into ORA-01000: maximum number of open
cursors exceeded errors
and part of the problem (apart from the usual
developers not closing
explicit cursors) is that _all_ static sql
statements in compiled pl/sql
units seem to be getting cached. I can't find any documentation of
this feature apart from a few hints in the pl/sql and
application development
docs. Here's an example from an 8.1.7 database: -
SQLcreate or replace procedure foobar is
2 v_result varchar2(30);
3 begin
4 select user into v_result from dual;
5 end;
6 /
Procedure created.
SQL
SQLselect b.sql_text
2 from v$session a, v$open_cursor b
3 where a.sid = b.sid
4 and a.audsid = userenv('SESSIONID')
5 /
SQL_TEXT
SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
select b.sql_text from v$session a, v$open_cursor b
where a.
SQL
SQLexec foobar
PL/SQL procedure successfully completed.
SQL
SQLselect b.sql_text
2 from v$session a, v$open_cursor b
3 where a.sid = b.sid
4 and a.audsid = userenv('SESSIONID')
5 /
SQL_TEXT
SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
select b.sql_text from v$session a, v$open_cursor b
where a.
SELECT USER FROM DUAL
TIA
--
David Lord
Senior DBA
Iron Mountain Europe
*** *** *** *** *** *** *** *** *** *** *** *** ***
*** *** *** *** ***
This e-mail and its attachments are intended
for the
author's addressee only and may be
confidential.
If they have come to you in error you must take
no
action based on them, nor must you copy or show
them to anyone; please reply to this e-mail and
highlight the error.
Please note that this e-mail has been created
in the
knowledge that Internet e-mail is not a 100%
secure
communications medium. We advise that you
understand and observe this lack of security
when
e-mailing us. Steps have been taken to ensure
this
e-mail and attachments are free from any virus,
but
advise the recipient to ensure they are
actually virus
free.
The views, opinions and judgments expressed in
this
message are solely those of the author. The
message
contents have not been reviewed or approved by
Iron
Mountain.
*** *** *** *** *** *** *** *** *** *** *** *** ***
*** *** *** *** ***
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Lord David
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (note EXACT spelling of
'ListGuru') and in
the message BODY, include
RE: ORA-1000 and pl/sql cursor cache
Try playing with alternately setting session_cached_cursors
to 0 and some non-zero value and run the following script.
Try setting _close_cached_open_cursors to both true and
false, changing which 'commit' is used, and omitting the
'commit' altogether.
On 9i I find that with session_cached_cursors = 0, and
a 'commit' following the pl/sql block, the only cursor
appearing in v$open_cursor is the final 'commit'.
HTH
Jared
--
alter session set _close_cached_open_cursors = false;
declare
xyz varchar2(30);
begin
select user into xyz from dual;
commit;
end;
/
--commit;
--
On Thu, 2003-11-27 at 01:09, Lord David wrote:
Barb
Thanks for the link. Unfortunately, it again hints at the behaviour, but
doesn't really come out with it.
What's happening is that when you execute a *static* sql statement such as
'select user into xyz from dual', the cursor remains open (as shown in
v$open_cursors) after the statement has finished. This also happens with
explicit cursors, even if you close them! And it happens whether
session_cached_cursors is set to zero or not. I assume that the cursors are
cached within the session context for the package or procedure since it only
happens for named pl/sql blocks and they get closed if you recompile the
block.
My guess is that it is a deliberate performance optimisation within the
pl/sql engine, but it does mean that to avoid ora-1000 errors, you need to
set open_cursors to be greater than the *total* number of static sql
statements that a session can open in its lifetime, not just the number
concurrently open. Of course you have also got to include room for dynamic
and recursive sql as well as cursors cached using session_cached_cursors.
--
David Lord
-Original Message-
From: Barbara Baker [mailto:[EMAIL PROTECTED]
Sent: 26 November 2003 16:49
To: Multiple recipients of list ORACLE-L
Subject: Re: ORA-1000 and pl/sql cursor cache
David:
I don't really know if this will help you, but it
might be worth a try. You could try setting session_cached_cursors.
Bjrn Engsig's white paper Efficient use of bind
variables, cursor_sharing and related cursor
parameters describes this parameter a bit. It can be
found at http://miracleas.dk in the Technical
Information section) ( Guy Harrison's tuning book
also talks about this parameter. )
good luck!
Barb
--- Lord David [EMAIL PROTECTED] wrote:
Hi
Does anyone know whether its possible to control the
size of the pl/sql
static cursor cache.
I'm running into ORA-01000: maximum number of open
cursors exceeded errors
and part of the problem (apart from the usual
developers not closing
explicit cursors) is that _all_ static sql
statements in compiled pl/sql
units seem to be getting cached. I can't find any documentation of
this feature apart from a few hints in the pl/sql and
application development
docs. Here's an example from an 8.1.7 database: -
SQLcreate or replace procedure foobar is
2 v_result varchar2(30);
3 begin
4 select user into v_result from dual;
5 end;
6 /
Procedure created.
SQL
SQLselect b.sql_text
2 from v$session a, v$open_cursor b
3 where a.sid = b.sid
4 and a.audsid = userenv('SESSIONID')
5 /
SQL_TEXT
SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
select b.sql_text from v$session a, v$open_cursor b
where a.
SQL
SQLexec foobar
PL/SQL procedure successfully completed.
SQL
SQLselect b.sql_text
2 from v$session a, v$open_cursor b
3 where a.sid = b.sid
4 and a.audsid = userenv('SESSIONID')
5 /
SQL_TEXT
SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
select b.sql_text from v$session a, v$open_cursor b
where a.
SELECT USER FROM DUAL
TIA
--
David Lord
Senior DBA
Iron Mountain Europe
*** *** *** *** *** *** *** *** *** *** *** *** ***
*** *** *** *** ***
This e-mail and its attachments are intended
for the
author's addressee only and may be
confidential.
If they have come to you in error you must take
no
action based on them, nor must you copy or show
them to anyone; please reply to this e-mail and
highlight the error.
Please note that this e-mail has been created
in the
knowledge that Internet e-mail is not a 100%
secure
communications medium. We advise that you
understand and observe this lack of security
when
e-mailing us. Steps have been taken to ensure
this
e-mail and attachments are free from any virus,
but
advise the recipient to ensure
ORA-1000 and pl/sql cursor cache
Hi
Does anyone know whether its possible to control the size of the pl/sql
static cursor cache.
I'm running into ORA-01000: maximum number of open cursors exceeded errors
and part of the problem (apart from the usual developers not closing
explicit cursors) is that _all_ static sql statements in compiled pl/sql
units seem to be getting cached. I can't find any documentation of this
feature apart from a few hints in the pl/sql and application development
docs. Here's an example from an 8.1.7 database: -
SQLcreate or replace procedure foobar is
2 v_result varchar2(30);
3 begin
4 select user into v_result from dual;
5 end;
6 /
Procedure created.
SQL
SQLselect b.sql_text
2 from v$session a, v$open_cursor b
3 where a.sid = b.sid
4 and a.audsid = userenv('SESSIONID')
5 /
SQL_TEXT
SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
select b.sql_text from v$session a, v$open_cursor b where a.
SQL
SQLexec foobar
PL/SQL procedure successfully completed.
SQL
SQLselect b.sql_text
2 from v$session a, v$open_cursor b
3 where a.sid = b.sid
4 and a.audsid = userenv('SESSIONID')
5 /
SQL_TEXT
SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
select b.sql_text from v$session a, v$open_cursor b where a.
SELECT USER FROM DUAL
TIA
--
David Lord
Senior DBA
Iron Mountain Europe
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
This e-mail and its attachments are intended for the
author's addressee only and may be confidential.
If they have come to you in error you must take no
action based on them, nor must you copy or show
them to anyone; please reply to this e-mail and
highlight the error.
Please note that this e-mail has been created in the
knowledge that Internet e-mail is not a 100% secure
communications medium. We advise that you
understand and observe this lack of security when
e-mailing us. Steps have been taken to ensure this
e-mail and attachments are free from any virus, but
advise the recipient to ensure they are actually virus
free.
The views, opinions and judgments expressed in this
message are solely those of the author. The message
contents have not been reviewed or approved by Iron
Mountain.
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Lord David
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: ORA-1000 and pl/sql cursor cache
David:
I don't really know if this will help you, but it
might be worth a try. You could try setting
session_cached_cursors.
Bjørn Engsig's white paper Efficient use of bind
variables, cursor_sharing and related cursor
parameters describes this parameter a bit. It can be
found at http://miracleas.dk in the Technical
Information section) ( Guy Harrison's tuning book
also talks about this parameter. )
good luck!
Barb
--- Lord David [EMAIL PROTECTED] wrote:
Hi
Does anyone know whether its possible to control the
size of the pl/sql
static cursor cache.
I'm running into ORA-01000: maximum number of open
cursors exceeded errors
and part of the problem (apart from the usual
developers not closing
explicit cursors) is that _all_ static sql
statements in compiled pl/sql
units seem to be getting cached. I can't find any
documentation of this
feature apart from a few hints in the pl/sql and
application development
docs. Here's an example from an 8.1.7 database: -
SQLcreate or replace procedure foobar is
2 v_result varchar2(30);
3 begin
4 select user into v_result from dual;
5 end;
6 /
Procedure created.
SQL
SQLselect b.sql_text
2 from v$session a, v$open_cursor b
3 where a.sid = b.sid
4 and a.audsid = userenv('SESSIONID')
5 /
SQL_TEXT
SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
select b.sql_text from v$session a, v$open_cursor b
where a.
SQL
SQLexec foobar
PL/SQL procedure successfully completed.
SQL
SQLselect b.sql_text
2 from v$session a, v$open_cursor b
3 where a.sid = b.sid
4 and a.audsid = userenv('SESSIONID')
5 /
SQL_TEXT
SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
select b.sql_text from v$session a, v$open_cursor b
where a.
SELECT USER FROM DUAL
TIA
--
David Lord
Senior DBA
Iron Mountain Europe
*** *** *** *** *** *** *** *** *** *** *** *** ***
*** *** *** *** ***
This e-mail and its attachments are intended
for the
author's addressee only and may be
confidential.
If they have come to you in error you must take
no
action based on them, nor must you copy or show
them to anyone; please reply to this e-mail and
highlight the error.
Please note that this e-mail has been created
in the
knowledge that Internet e-mail is not a 100%
secure
communications medium. We advise that you
understand and observe this lack of security
when
e-mailing us. Steps have been taken to ensure
this
e-mail and attachments are free from any virus,
but
advise the recipient to ensure they are
actually virus
free.
The views, opinions and judgments expressed in
this
message are solely those of the author. The
message
contents have not been reviewed or approved by
Iron
Mountain.
*** *** *** *** *** *** *** *** *** *** *** *** ***
*** *** *** *** ***
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Lord David
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Barbara Baker
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: ORA-1000 and pl/sql cursor cache
are you looking for the init.ora max_open_cursors(dont think i typed it exactly right).
even if the cursors are cached, they should not be counted as open. they doesnt make
sense from an oracle design standpoint.
From: Lord David [EMAIL PROTECTED]
Date: 2003/11/26 Wed AM 10:34:34 EST
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: ORA-1000 and pl/sql cursor cache
Hi
Does anyone know whether its possible to control the size of the pl/sql
static cursor cache.
I'm running into ORA-01000: maximum number of open cursors exceeded errors
and part of the problem (apart from the usual developers not closing
explicit cursors) is that _all_ static sql statements in compiled pl/sql
units seem to be getting cached. I can't find any documentation of this
feature apart from a few hints in the pl/sql and application development
docs. Here's an example from an 8.1.7 database: -
SQLcreate or replace procedure foobar is
2 v_result varchar2(30);
3 begin
4 select user into v_result from dual;
5 end;
6 /
Procedure created.
SQL
SQLselect b.sql_text
2 from v$session a, v$open_cursor b
3 where a.sid = b.sid
4 and a.audsid = userenv('SESSIONID')
5 /
SQL_TEXT
SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
select b.sql_text from v$session a, v$open_cursor b where a.
SQL
SQLexec foobar
PL/SQL procedure successfully completed.
SQL
SQLselect b.sql_text
2 from v$session a, v$open_cursor b
3 where a.sid = b.sid
4 and a.audsid = userenv('SESSIONID')
5 /
SQL_TEXT
SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL
select b.sql_text from v$session a, v$open_cursor b where a.
SELECT USER FROM DUAL
TIA
--
David Lord
Senior DBA
Iron Mountain Europe
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
This e-mail and its attachments are intended for the
author's addressee only and may be confidential.
If they have come to you in error you must take no
action based on them, nor must you copy or show
them to anyone; please reply to this e-mail and
highlight the error.
Please note that this e-mail has been created in the
knowledge that Internet e-mail is not a 100% secure
communications medium. We advise that you
understand and observe this lack of security when
e-mailing us. Steps have been taken to ensure this
e-mail and attachments are free from any virus, but
advise the recipient to ensure they are actually virus
free.
The views, opinions and judgments expressed in this
message are solely those of the author. The message
contents have not been reviewed or approved by Iron
Mountain.
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Lord David
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: pl/sql question and owa_pattern question
Guang,
I agree with your analysis, looping on characters is not the faster you can do,
simply because there is a significant overhead (compared to C code for instance) in a
language such as PL/SQL - which might be perfectly acceptable in some circumstances,
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C,
might improve performance. However, in my view the best performance gains you may get
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which
isn't by the way exclusive of native compiling). Using a function such as INSTR() will
be much more efficient than looping on characters.
I would suggest something such as :
- First use TRANSLATE() to replace all the characters you want to get rid of by a
single, well identified character, say # (use CHR() || ... for non printable
characters - you can build up the string of characters to translate in the
initialisation section of a package rather than typing it).
- Start with initializing your string to LTRIM(string, '#')
- Then as long as pos := INSTR(string, '#') isn't 0,
get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos
+ 1), '#') to string (very similar to what you were planning to do with owa).
This will be probably much faster than a character-by-character loop and calls to an
owa package.
HTH,
Stephane Faroult
- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55
Hi:
In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:
---
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');
-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is
much slower than direct
string manupilation. But I would like to try it in
this case if I could
easily get the wrods from the string. Any
suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: pl/sql question and owa_pattern question
Hi Stephane:
Thanks for your good suggestion. I compared the method you suggested and the orginal
one and it indeed boosted the performance (in my simple test). However the ONLY
problem I am having is that by doing TRANSLATE, I lost the original delimits. The new
method (you suggested) correctly extract the words (and sent for processing), But
after processing I need to put processed-words back to the orginal string with orginal
demilters un-changed. I tried to track to position of delimit from the orginal string
by doing
global_pos := global_pos + pos ;
in my while loop, but ltrim(substr(string, pos + 1), '#') will make global_pos
wrong when ltrim trims '#'. Any work-around?
TIA.
Guang
-Original Message-
Stephane Faroult
Sent: Friday, November 21, 2003 4:19 AM
To: Multiple recipients of list ORACLE-L
Guang,
I agree with your analysis, looping on characters is not the faster you can do,
simply because there is a significant overhead (compared to C code for instance) in a
language such as PL/SQL - which might be perfectly acceptable in some circumstances,
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C,
might improve performance. However, in my view the best performance gains you may get
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which
isn't by the way exclusive of native compiling). Using a function such as INSTR() will
be much more efficient than looping on characters.
I would suggest something such as :
- First use TRANSLATE() to replace all the characters you want to get rid of by a
single, well identified character, say # (use CHR() || ... for non printable
characters - you can build up the string of characters to translate in the
initialisation section of a package rather than typing it).
- Start with initializing your string to LTRIM(string, '#')
- Then as long as pos := INSTR(string, '#') isn't 0,
get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos
+ 1), '#') to string (very similar to what you were planning to do with owa).
This will be probably much faster than a character-by-character loop and calls to an
owa package.
HTH,
Stephane Faroult
- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55
Hi:
In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:
---
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');
-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is
much slower than direct
string manupilation. But I would like to try it in
this case if I could
easily get the wrods from the string. Any
suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Guang Mei
RE: pl/sql question and owa_pattern question
Perl is a good tool for text processing. But our program is already written
in pl/sql long time ago and there are intensive db calls in this pl/sql
program. (text processing is only part of it). So I can not change that.
BTW I did a comparison study a while ago for some of our pl/sql packages
(specifically for our application). When there are lots of db calls (select,
insert, update and delete), pl/sql package is faster than correponding perl
program (I made sure sqls are prepared once and used bind variables in perl.
All code were executed on the unix server, no other programs were running,
etc). That's why we stick to pl/sql because our app need the performance.
Others may have different results, it all depends on what the code does.
Guang
-Original Message-
Mladen Gogala
Sent: Thursday, November 20, 2003 11:14 PM
To: Multiple recipients of list ORACLE-L
I don't know about PL/SQL but here is how I would get separate words from a
big string:
#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}
There is something called DBI and it can be used to insert separated words
into the database, instead
of printing them. The bottom line is that perl is an excellent tool for
parsing strings and all sorts of string
manipulation.
On 2003.11.20 22:39, Guang Mei wrote:
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Guang Mei
INET: [EMAIL PROTECTED]
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
RE: pl/sql question and owa_pattern question
Guang,
Well you are almost there ... you need fifo structure namely a pl/sql array
1. create a local pl/sql array to store the delimiter (store the ascii value of the
delimiter to be safe) my_array (varchar2(5))
2. as you find a delimiter insert into the first position in the array and replace the
delimiting character with #
3. lather.rinse.repeat.
when it is time to put it back
use a loop
nIndex := 0;
nPos := 0;
loop
npos := instr(my_str,'#',1);
exit when npos := 0;
nIndex := nindex + 1;
my_str := substr(my_str,1,nPos-1) || chr(my_array(nIndex)) || sybstr(my_str, nPos+1);
end loop;
something like this should help, proof-read though ...
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
Sent: Friday, November 21, 2003 11:44 AM
To: Multiple recipients of list ORACLE-L
Hi Stephane:
Thanks for your good suggestion. I compared the method you suggested and the orginal
one and it indeed boosted the performance (in my simple test). However the ONLY
problem I am having is that by doing TRANSLATE, I lost the original delimits. The new
method (you suggested) correctly extract the words (and sent for processing), But
after processing I need to put processed-words back to the orginal string with orginal
demilters un-changed. I tried to track to position of delimit from the orginal string
by doing
global_pos := global_pos + pos ;
in my while loop, but ltrim(substr(string, pos + 1), '#') will make global_pos
wrong when ltrim trims '#'. Any work-around?
TIA.
Guang
-Original Message-
Stephane Faroult
Sent: Friday, November 21, 2003 4:19 AM
To: Multiple recipients of list ORACLE-L
Guang,
I agree with your analysis, looping on characters is not the faster you can do,
simply because there is a significant overhead (compared to C code for instance) in a
language such as PL/SQL - which might be perfectly acceptable in some circumstances,
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C,
might improve performance. However, in my view the best performance gains you may get
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which
isn't by the way exclusive of native compiling). Using a function such as INSTR() will
be much more efficient than looping on characters.
I would suggest something such as :
- First use TRANSLATE() to replace all the characters you want to get rid of by a
single, well identified character, say # (use CHR() || ... for non printable
characters - you can build up the string of characters to translate in the
initialisation section of a package rather than typing it).
- Start with initializing your string to LTRIM(string, '#')
- Then as long as pos := INSTR(string, '#') isn't 0,
get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos
+ 1), '#') to string (very similar to what you were planning to do with owa).
This will be probably much faster than a character-by-character loop and calls to an
owa package.
HTH,
Stephane Faroult
- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55
Hi:
In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:
---
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');
-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0
Re: pl/sql question and owa_pattern question
PL/SQL is the fastest thing of them all when it comes to executing
SQL commands, but there are things which simply aren't practical
in 9.2 PL/SQL. Regular expression processing is one of those things.
Fortunately, you can mix the two. Without DBI, perl scripts simply
woudn't be very useful. Of course, there are things that are faster
then even the fastest perl script. Lexer written in C is one of them
and you don't need much work to write one, either, but using OCI is
not easy. OCI is a library written to confuse the enemy, not to help
developer. Using plain and simple regex or PCRE within a C program
is the same thing as above, but slightly more complicated then a lexer.
For the specific task of manipulating patterns and resolving regular
expressions, I use perl almost exclusively because I find it an optimal
tradeoff between ease of use and performance. If performance is a
paramount, as in real time application processing, then you'll have to
resort to C and, possibly, write an external procedure and, thus,
enabling oracle to use C regex calls or even pcre. I was toying with the
idea of enabling oracle to use PCRE but I gave up when I read that 10g
will have that included.
On 11/21/2003 11:59:31 AM, Guang Mei wrote:
Perl is a good tool for text processing. But our program is already written
in pl/sql long time ago and there are intensive db calls in this pl/sql
program. (text processing is only part of it). So I can not change that.
BTW I did a comparison study a while ago for some of our pl/sql packages
(specifically for our application). When there are lots of db calls (select,
insert, update and delete), pl/sql package is faster than correponding perl
program (I made sure sqls are prepared once and used bind variables in perl.
All code were executed on the unix server, no other programs were running,
etc). That's why we stick to pl/sql because our app need the performance.
Others may have different results, it all depends on what the code does.
Guang
-Original Message-
Mladen Gogala
Sent: Thursday, November 20, 2003 11:14 PM
To: Multiple recipients of list ORACLE-L
I don't know about PL/SQL but here is how I would get separate words from a
big string:
#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}
There is something called DBI and it can be used to insert separated words
into the database, instead
of printing them. The bottom line is that perl is an excellent tool for
parsing strings and all sorts of string
manipulation.
On 2003.11.20 22:39, Guang Mei wrote:
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from
Re: pl/sql question and owa_pattern question
Would extproc_perl fit well enough, though, until 10g is here?
On Fri, 21 Nov 2003, Mladen Gogala wrote:
PL/SQL is the fastest thing of them all when it comes to executing
SQL commands, but there are things which simply aren't practical
in 9.2 PL/SQL. Regular expression processing is one of those things.
Fortunately, you can mix the two. Without DBI, perl scripts simply
woudn't be very useful. Of course, there are things that are faster
then even the fastest perl script. Lexer written in C is one of them
and you don't need much work to write one, either, but using OCI is
not easy. OCI is a library written to confuse the enemy, not to help
developer. Using plain and simple regex or PCRE within a C program
is the same thing as above, but slightly more complicated then a lexer.
For the specific task of manipulating patterns and resolving regular
expressions, I use perl almost exclusively because I find it an optimal
tradeoff between ease of use and performance. If performance is a
paramount, as in real time application processing, then you'll have to
resort to C and, possibly, write an external procedure and, thus,
enabling oracle to use C regex calls or even pcre. I was toying with the
idea of enabling oracle to use PCRE but I gave up when I read that 10g
will have that included.
On 11/21/2003 11:59:31 AM, Guang Mei wrote:
Perl is a good tool for text processing. But our program is already written
in pl/sql long time ago and there are intensive db calls in this pl/sql
program. (text processing is only part of it). So I can not change that.
BTW I did a comparison study a while ago for some of our pl/sql packages
(specifically for our application). When there are lots of db calls (select,
insert, update and delete), pl/sql package is faster than correponding perl
program (I made sure sqls are prepared once and used bind variables in perl.
All code were executed on the unix server, no other programs were running,
etc). That's why we stick to pl/sql because our app need the performance.
Others may have different results, it all depends on what the code does.
Guang
-Original Message-
Mladen Gogala
Sent: Thursday, November 20, 2003 11:14 PM
To: Multiple recipients of list ORACLE-L
I don't know about PL/SQL but here is how I would get separate words from a
big string:
#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}
There is something called DBI and it can be used to insert separated words
into the database, instead
of printing them. The bottom line is that perl is an excellent tool for
parsing strings and all sorts of string
manipulation.
On 2003.11.20 22:39, Guang Mei wrote:
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
Fat City
pl/sql question and owa_pattern question
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
Re: pl/sql question and owa_pattern question
I don't know about PL/SQL but here is how I would get separate words from a big string:
#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}
There is something called DBI and it can be used to insert separated words into the
database, instead
of printing them. The bottom line is that perl is an excellent tool for parsing
strings and all sorts of string
manipulation.
On 2003.11.20 22:39, Guang Mei wrote:
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
RE: Interesting PL/SQL Puzzle
You have not provided me with anything (I cannot post these things to public forums because of my email)! The only thing you said bad code, good code! I was not impressed the way the code works, this is why I had to research issue until I found the problem using LIKE. Of course it was easy for anybody to figure it out from here. The idea is to add value instead of waiting to have the final words! Regards, Waleed -Original Message- Sent: Monday, November 10, 2003 11:34 PM To: Multiple recipients of list ORACLE-L Khedr, Waleed wrote: The question was not if it's a good or bad code. The question was why? This is not the actual code that runs, just something that explains the issue :) I've provided a selfexplanatory fix of the 'bad' code, please review it. You code uses standard.like, and a lot isntances of booleans -- each IF condition, same could relate to out variables (ls), and I hope you understood why the package is used. I would suggest to consider some simple things: . standard Oracle and your application's package(s) dependencies . proper datatypes usage I cannot provide you with tech. details open the C (native) code and see. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Khedr, Waleed INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Interesting PL/SQL Puzzle
Khedr, Waleed wrote: You have not provided me with anything (I cannot post these things to public forums because of my email)! Please re-read my posts. The only thing you said bad code, good code! How should it be named? I was not impressed the way the code works, this is why I had to research issue until I found the problem using LIKE. Of course it was easy for anybody to figure it out from here. Check the dates of the posts. The idea is to add value instead of waiting to have the final words! Excuse me, but that just not polite. -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Question about pl/sql
Hallo, I am trying to create this statement but dont get it work. I would like to check if a field information from table one exists in table 2, and if that exists in table 2, then procedure test_proc would run, Please help me with this. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Interesting PL/SQL Puzzle
Yes, you are right, of course. Using large PL/SQL tables and string manipulation (INSTR,SUBSTR and alike) are known to be CPU intensive and there is no way around it. The only way to help an application which uses those functions extensively is to add a column which extracts portion of the original string and populate it with a trigger. On 11/09/2003 10:59:25 PM, Cary Millsap wrote: I agree as Step 1, but I expect that you'll find quickly that the issue is a big c value for the EXEC on the block. If you do find this, then it indicates exactly what's been suggested several times already: use DBMS_PROFILER to dig into the response time of the EXEC. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 11/19 Sydney, 12/16 Detroit - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Mladen Gogala Sent: Saturday, November 08, 2003 2:29 PM To: Multiple recipients of list ORACLE-L Let me give you a carystic advice: run your app with 10046, lev 8 and see what are you waiting on and how long the waits are. On 2003.11.08 13:09, Khedr, Waleed wrote: I have a weird problem. It seems that execution speed of pl/sql proc can slow down dramatically as the size of the proc goes up even if nothing gets executed. Let me explain: I have a proc that looks like: Proc test_1 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if condition1 then big block for string manipulation, two pages of code (substr, instr, etc) end if; if condition2 then another big block for string manipulation, two pages of code (substr, instr, etc) end if; end; If I change the proc to do nothing by altering it this way: Proc test_2 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then big block for string manipulation end if; if false then another big block for string manipulation end if; end; The execution speed goes up a little bit but is still at least 50 percent slower than if I change the proc by removing the code in the if clause, look below: Proc test_3 (p1 in out varchar2, p2 in out varchar2) as some declared variables begin if false then null; end if; if false then null; end if; end; proc test_3 ran 30 million times in 9 minutes while test_2 ran in 20 minutes. Also test_2 required more CPU resources while running. Also I tried native compilation, which did not do a lot (only 10 % faster). When I looked at the C code generated by the native compilation, I was not very pleased the way native compilation works. Does anybody have a clue why? I tried to include the proc in a package and pin it but there was no difference. Thanks Waleed -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Cary Millsap INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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
Pl/SQL-statement
Hallo, I would like to do the following with an sql( pl/sql) statement. I have table1 and table2 andtable3. I want to check whether field1 in table1 exists in table3. If so then I want an insert statement to be run...insert into table3. If it doesnt find that value then th escript will go to table2 and check if the vaules exists in that table, if it finds it then I want another insert statement to be run. Please help me with an easy example, i dont know if this is so simply but I cantget it right though. Thanks Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Pl/SQL-statement
RTFM RTFM RTFM RTFM Do your homework! Read the oracle documents, buy a book from Amazon/Bookpool/Softpro and put in a little effort. If has_done_homework = 'Y' then list_answer = 'Y'; else list_answer = 'NO!'; end if; [EMAIL PROTECTED] wrote: Hallo, I would like to do the following with an sql( pl/sql) statement. I have table1 and table2 andtable3. I want to check whether field1 in table1 exists in table3. If so then I want an insert statement to be run...insert into table3. If it doesnt find that value then th escript will go to table2 and check if the vaules exists in that table, if it finds it then I want another insert statement to be run. Please help me with an easy example, i dont know if this is so simply but I cantget it right though. Thanks Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Daniel Fink INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Interesting PL/SQL Puzzle
In article [EMAIL PROTECTED], Vladimir Begun [EMAIL PROTECTED] writes Less number of inexpensive instructions is everytime better (I'm not talking about lines of code). Trace it -- 10046/12 + dump instructions using appropriate event. ^ Hi Vladimir, what instructions? and what events do you refer to? kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
