RE: ** can two processes use the same rollback extent
1)rollback segment will not shrink. Correct, assuming you do not shrink it manually. 2)It will keep growing till tablespace fills up. Correct assuming that you have a SQL statement that causes the rollback segment to grow in such a way and that you have no constraints on the rollback segments to prevent unconstrained growth (e.g. maxextents). Note that this may not be a good thing... if you have one transaction that causes one rbs to grow really large, that may prevent growth of other rbs's. Unless you are manually assigning rollback segments to every transaction, that could have negative consequences. It is, IMHO, best to have use automated undo, if possible, then you don't need to worry about these things. If you can't use automated undo, then create a nice big tablespace, but also use optimal, setting it to a reasonable size. 3)And there will be no 'snapshot too old' errors? Wrong. Even with large rollback segments, 1555's are possible depending on a number of things. Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 630.819.9077 Cell (It's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message-From: A Joshi [mailto:[EMAIL PROTECTED]Sent: Sunday, December 28, 2003 12:39 AMTo: Multiple recipients of list ORACLE-LSubject: Re: ** can two processes use the same rollback extent Hi, A simple rollback segment question. If I do not set a optimal thencan I assume following 1)rollback segment will not shrink. 2)It will keep growing till tablespace fills up. 3)And there will be no 'snapshot too old' errors? ThanksA Joshi [EMAIL PROTECTED] wrote: Hi, One rollback segment can be used by multiple transactions/processes but what about the extents? Is it safe to assume that only one transaction/process can use one extent? What is a good way to size the extent for warehousing tasks? Or guess the rollback generated and size needed. Thank You Do you Yahoo!?Protect your identity with Yahoo! Mail AddressGuard Do you Yahoo!?Protect your identity with Yahoo! Mail AddressGuard
RE: ** can two processes use the same rollback extent
1.) Correct, except that the DBA can always shrink it manually. 2.) Well, that's true, but a rollback segment w/ optimal set could conceivably grow to fill the tablespace as well. It depends on the size of the rollback tablespace and the size(s) of your transaction(s). The reason that it's perceived that not having optimal set exacerbates it is probably due to the fact that without optimal, the rollback segments never shrink. So, one occurrance of a very large or runaway transaction can bloat a particular rollback segment. That permanently decreases the free space in the tablespace. Over time, multiple rollback segments could become bloated, and eventually, you run out of space. In the case where optimal is set, this is less likely to happen, because the rollback segments will shrink, and the cumulative effect never occurs. 3.) Not at all. ORA-1555s can occur whenever the size or number of rollback segments is inadequate. Systems can be constructed where ORA-1555 is inevitable. (For example processes doing lots of DML concurrent with processes that execute long running queries.) There's been a *LOT* written on ORA-1555 over the years. If you have specific problems or questions, start w/ MetaLink if you have access. If not, check out these URLs: http://www.jlcomp.demon.co.uk/faq/snapshot.html http://www.jlcomp.demon.co.uk/faq/settrans.html Hope that helps, -Mark -Original Message- From: A Joshi [mailto:[EMAIL PROTECTED] Sent: Sun 12/28/2003 1:39 AM To: Multiple recipients of list ORACLE-L Cc: Subject:Re: ** can two processes use the same rollback extent Hi, A simple rollback segment question. If I do not set a optimal then can I assume following 1) rollback segment will not shrink. 2) It will keep growing till tablespace fills up. 3) And there will be no 'snapshot too old' errors? Thanks A Joshi [EMAIL PROTECTED] wrote: Hi, One rollback segment can be used by multiple transactions/processes but what about the extents? Is it safe to assume that only one transaction/process can use one extent? What is a good way to size the extent for warehousing tasks? Or guess the rollback generated and size needed. Thank You - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard winmail.dat
Re: On the front page of OTN
Yes, saw that 2. SED rules! Oh Yeah! ;D Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - Quite a surprise to find this on the front page of OTN http://otn.oracle.com/pub/articles/dulaney_sed.html -- 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: Exporting a partition with transport tablespace
The need to set primary and unique constraints to NOVALIDATE when doing an exchange partition. (It still doesn't help with problems of parent/child tables when dropping partitions though). 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: Saturday, December 27, 2003 11:49 PM Jonathan, Which exact behaviour were you talking about? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, December 27, 2003 11:34 PM Good news ! That bug has been fixed in 9.2.0.4 -- 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: any single serial session will never get more than 5% of pga
Notes in-line 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, December 28, 2003 12:39 AM Hi Jonathan, I'm not sure what you really think about this new feature! I view the feature as a positive step forward. Instead of a DBA having to guess an artificially low limit on the sort_area_size because (say) 1200 users might be connected to a machine with 4GB of memory, you now give Oracle a directive like: I have 1.5GB available for sort operations; please be as generous as you can when the demand for memory is low, and ration it carefully when the demand is high. In theory, this ensures that more processes get in-memory sorting because there is a known spare capacity - in practice, the algorithms and options for over-ride will, no doubt, evolve over time. Are you saying that Oracle is capable now of releasing the extra memory something it was not capable of before? Yes If yes, then what does it have to do with the work policy? Nothing - but since the O/S used to take care of the problem by paging out unused memory there was little point in fixing something which wasn't totally broken. On the other hand, if you are trying to operate a policy of maximising the amount of memory you give to a session, based on your estimates of expected data volume, it makes sense to use code that allows a session to de-allocate memory properly. I see this feature useful (not really) for a database application that hosts N concurrent sessions while the amount of available resources is capable of running only N / m sessions. Where m is any integer. In different words, it's the choice when we don't have the required resources to run the app efficiently without restriction to the performance and by using it, it will be able to torture any session that is asking for memory and give it enough guilt not to ask for it again and just try to get the job done by any means :) Now, if the techies on Redwood Shores could get the concepts of hungry and greedy into the code, perhaps we wouldn't have to do any more tuning ever again ;) Regards, Waleed -- 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: pga_aggregate_target
Yes, they are in kilobytes, as I wrote in my message before. If you check the source of v$pgastat you see that for statistics in bytes then QESMMSGAVL (value) column is multiplied by QESMMSGAMU which is 1024 (to get bytes from kilobytes). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 28, 2003 7:44 AM It would appear that the values for _smm_max_size and _smm_px_max_size are specified in K, though not explicitly. In a test 9.2.0.4 database: pga_aggregate_target = 25165824 _smm_max_size = 1228 _smm_px_max_size = 7371 Anyone know this for sure? Jared On Sat, 2003-12-27 at 08:59, Tanel Poder wrote: Also, consider that any single serial session will never get more than 5% of pga_aggregate_target. For parallel operations, total is limited to 30%. The maximum can be controlled using _smm_max_size parameter which states how many kilobytes a serial session can use for its workarea operations. The default is 5% from pga_aggregate_target. _smm_px_max_size controls max workarea for parallel slaves, respectfully (default is 30% from pga_aggregate_target). These parameters are unsupported (as you know) and have no effect when set at session level - alter system has to be used for changing them (tested on 9.2.0.4 on W2k). 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: Exporting a partition with transport tablespace
Thanks, I haven't hit this problem before. Actually, in my post I recommended to use novalidate option for exchanging required partition back from temporary transport table, that way Oracle won't check the contents in the partition (should be used only when this partition doesn't change in the meantime). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 28, 2003 12:10 PM The need to set primary and unique constraints to NOVALIDATE when doing an exchange partition. (It still doesn't help with problems of parent/child tables when dropping partitions though). 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: Saturday, December 27, 2003 11:49 PM Jonathan, Which exact behaviour were you talking about? Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, December 27, 2003 11:34 PM Good news ! That bug has been fixed in 9.2.0.4 -- 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). -- 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: TNSPING VS. REGULAR PING..! WHY SUCH A DIFFERENCE
Possibly due initially to the fact that ping is ICMP and runs very low in the TCP/IP stack that is in the network layer or the third level up from the hardware and TNSPING is application layer which puts it up at the top of the stack or two more layers higher. This alone can contribute to the performance or response time differences. Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = If people did not sometimes do silly things, nothing intelligent would ever get done. - Ludwig Wittgenstein = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Tanel Poder [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 28/12/2003 06:19 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: TNSPING VS. REGULAR PING..! WHY SUCH A DIFFERENCE I agree that this difference might be only because sqlnet is much more fat that ICMP. But anyway, could some overhead be added be because the failover load balancing clauses that require extra work? Also, if listener logs every connection, this might add some extra IO time as well (if writes for log file aren't write buffered). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, December 23, 2003 3:29 AM I have recently noticed in this one situation that there is a great difference between a tnsping vs a regular ping to the same server. for example this tnsping took about 270 ms which is strange and its consistent Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADD RESS_LIST = (load_balance = on) (failover = on) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost1.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = myhost2.com)( PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = xyzdb) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 20) (DELAY = 15 OK (270 msec) and a ping to the same host Ping statistics for x.x.x.x: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), Approximate round trip times in milli-seconds: Minimum = 61ms, Maximum = 70ms, Average = 67ms Why could there be such a difference? Do you Yahoo!? Yahoo! Photos - Get your photo on the big screen in Times Square -- This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please notify the sender and delete the transmission. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. -- 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: Exporting a partition with transport tablespace
So your 'novalidate' referred to the without validation clause of exchange; I thought you were referring to the workaround for uk/pk exchanges where even if you did including indexes without validate on the exchange, Oracle still did a horrendous check of the UK and PK constraints by doing a massive MINUS and INTERSECTION across the entire partitioned table. 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, December 28, 2003 11:34 AM Thanks, I haven't hit this problem before. Actually, in my post I recommended to use novalidate option for exchanging required partition back from temporary transport table, that way Oracle won't check the contents in the partition (should be used only when this partition doesn't change in the meantime). Tanel. -- 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).
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: Exporting a partition with transport tablespace
Yep, I didn't remember the exact clause in the exchange partition syntax. Tanel. So your 'novalidate' referred to the without validation clause of exchange; I thought you were referring to the workaround for uk/pk exchanges where even if you did including indexes without validate on the exchange, Oracle still did a horrendous check of the UK and PK constraints by doing a massive MINUS and INTERSECTION across the entire partitioned table. 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, December 28, 2003 11:34 AM Thanks, I haven't hit this problem before. Actually, in my post I recommended to use novalidate option for exchanging required partition back from temporary transport table, that way Oracle won't check the contents in the partition (should be used only when this partition doesn't change in the meantime). Tanel. -- 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). -- 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
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: pga_aggregate_target
So you did, guess I was skimming too fast. :) Jared On Sun, 2003-12-28 at 03:29, Tanel Poder wrote: Yes, they are in kilobytes, as I wrote in my message before. If you check the source of v$pgastat you see that for statistics in bytes then QESMMSGAVL (value) column is multiplied by QESMMSGAMU which is 1024 (to get bytes from kilobytes). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 28, 2003 7:44 AM It would appear that the values for _smm_max_size and _smm_px_max_size are specified in K, though not explicitly. In a test 9.2.0.4 database: pga_aggregate_target = 25165824 _smm_max_size = 1228 _smm_px_max_size = 7371 Anyone know this for sure? Jared On Sat, 2003-12-27 at 08:59, Tanel Poder wrote: Also, consider that any single serial session will never get more than 5% of pga_aggregate_target. For parallel operations, total is limited to 30%. The maximum can be controlled using _smm_max_size parameter which states how many kilobytes a serial session can use for its workarea operations. The default is 5% from pga_aggregate_target. _smm_px_max_size controls max workarea for parallel slaves, respectfully (default is 30% from pga_aggregate_target). These parameters are unsupported (as you know) and have no effect when set at session level - alter system has to be used for changing them (tested on 9.2.0.4 on W2k). 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: order by
[EMAIL PROTECTED] wrote: Hello list, Can someone please explain to me why the following order by clauses are valid and yield the same results : select empno, deptno from emp order by sqrt (1) ; and select empno, deptno from emp order by sqrt ( 3.14234 ) ; The docs say that in the order by clause you could specify only (a) column names or (b) positional parameters or (c) expressions involving the columns A constant falls under the c) category. It's an expression, which involves anything you want. That said, I fail to see any practical use . -- Regards, Stephane Faroult Oriole Software -- 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 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
