SAN-Eva3000 experiences
Hi, We're in the middle of buying a storage solution that will probably be an eva3000. Because I'm new with these kind of storages and I will get implementation advice from consultants I would like to have some background with experiences in implementing an oracle database on an eva3000. Any do's and don'ts ?? Any advice on raid-levels to use? Thanks in advance, Jeroen
PL/SQL Help
encoded content removed -- binaries not allowed by ListGuru 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. winmail.dat
Re: USERENV('SESSIONID') on RAC
DBMS_SUPPORT.MYSID;
On 2003.10.09 23:09, Khedr, Waleed wrote:
They work fine for me on RAC 9.2.0.2
Does this help:
select unique sid from v$mystat
Waleed
-Original Message-
Sent: Thursday, September 25, 2003 12:20 PM
To: Multiple recipients of list ORACLE-L
Hey all,
Is there a way to get your own executing program from a 9.2.0.4 RAC node?
USERENV('SESSIONID') and SYS_CONTEXT('USERENV','SESSIONID') each return a
big fat zero on RAC.
My ultimate goal is to get the executing session's program, and the only
place I can find that info is in V$SESSION. And the only way I know to get
the current session's row from V$SESSION is to join it with
USERENV('SESSIONID'). If there's a better/different way to do this, I'm
listening.
Thanks!
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
--
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).
--
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).
use of TEMP tables to re-query HTML form fields
list, this might not be the appropriate forum for this question, but it's friday night and i have to come up with some solution... we have developed an application using java script to display a form, which the users will fill in , this form is submitted using JDBC THIS to oracle database (using 9iAS). we have back and forward buttons on this form, the form appears in 3 pages !! the problem: after the user fills in the first page, he/she will click next and the 2nd page would load, there a button back on the second page to go back to the 1st page, NOW if the user has filled in values in the second page and he navigates to the 1st page, and again goes forward by clicking next button the form, the 2nd page apprears blank !! all the earlier filled in values are lost !! , ok , this is the expected default behaviour , how can we make the values re-apprear on the second page ?? the solution: use of TEMP tables, we use temp table to hold the values, each tikme the user clicks next page we save the values to the temp table, and re-query to fill in the fields if the user comes back to the same page .. is my approach correct ??? is there another easier way to do this?? TIA -rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: rahul 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: Find an unprintable character inside a column....
Some people have requested this code, so I thought you might as well all have the chance to pick it to bits... Its a function called BAD_ASCII, and it hunts out for any ascii characters with an ascii value of less than 32 in a specified field. (Acknowledgments to my colleague Keith Holmes for help with this code.) Use it as follows: Where a field called DATA in a table TABLE_1 may contain an ascci character with a value less than 32 (ie a non-printing character), the following SQL will find the row in question: select rowid,DATA,dump(DATA) from TABLE_1 where BAD_ASCII(DATA) 0; You could use the PK of the table instead of rowid, of course. You will also note that I select the DATA field in both normal and ascii 'dump' mode, the better to locate where the corruption is located. peter edinburgh ... Source as follows: Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin -- V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; -- exception when others then return -1; end BAD_ASCII; / -- -Original Message- From: Prem Khanna J [mailto:[EMAIL PROTECTED] Sent: Thursday, October 09, 2003 10:49 AM To: Multiple recipients of list ORACLE-L Subject: Re: RE: Find an unprintable character inside a column Peter, i would be interested in that. can u mail it to me ? Jp. 09-10-2003 18:29:33, Robson, Peter [EMAIL PROTECTED] wrote: I have a small PL/SQL piece of code used to detect these things, if anyone wants it. -- 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). * This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the BGS. .http://www.bgs.ac.uk * -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robson, Peter 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).
Optimizer related init parameters
How Good/advisable are the following 4 parameters Values in a Hybrid Application? Are there any know ill-effects of the same? Application Banking (Hybrid) Solaris 9 Oracle 9.2 optimizer_max_permutations=8000 optimizer_index_cost_adj=10 optimizer_index_caching=50 optimizer_dynamic_sampling=4 Some INFO :- Database has 6000 Concurrent Users accessing We do ONLY INDEX Scans with exceptional FTS . FTS if present occur only on SMALL Tables (a few Hundred Rows) FTS if unchecked greatly harm our performance Stripe Unit Size 64K Oracle Block Size 8K Will Give any info required Thanks
RE: Using ' in Update statement
Naaa. He's an equal opportunity ball-buster. -Original Message- Sent: Thursday, October 09, 2003 5:54 PM To: Multiple recipients of list ORACLE-L Hamid started his question How can I user comma ' in my update statement? I am merely correcting his mistake and then providing a solution. Wow, what a concept on a Oracle Listserv site design for that very purpose. Just curious, Would you have answered in the same way if it had been asked by one of the elite? Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 05:09 PM Please respond to ORACLE-L I didn't mean quote, I meant RTFM. On Thu, 2003-10-09 at 16:34, [EMAIL PROTECTED] wrote: I assume you mean quote update tablea set fielda =' james'ste Camp 'first,'sec' ' update tablea set fielda =' james''ste Camp ''first,''sec'' '; Just use 2 single quotes Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 02:29 PM Please respond to ORACLE-L It's in the documentation. Start with the concepts manual, then SQL*Plus manual and SQL reference. I'm sure that you'll run across the answer because that's where I have found the answer to the very same question. On Thu, 2003-10-09 at 13:59, Hamid Alavi wrote: List, How can I user comma ' in my update statement? update tablea set fielda =' james'ste Camp 'first,'sec' ' Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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 -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL
DB Parameters
HI , I have noticed that both deleting and inserting is too slow (about five hours to insert 4 rows ), this may be because i have more than 40 INDEX ON THIS TWO tables,I will try to disable them before i run my insert statement . Thanks, ++ This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this message in error please delete it and any files transmitted with it, after notifying [EMAIL PROTECTED] Any opinions expressed in this message may be those of the author and not necessarily those of the Company. The Company accepts no responsibility for the accuracy or completeness of any information contained herein. This message is not intended to create legal relations between the Company and the recipient. Recipients should please note that messages sent via the internet may be intercepted and that caution should therefore be exercised before despatching to the Company any confidential or sensitive information. ++ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Walid Alkaakati 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: encrypt traffic across internet?
You need to run CMAN on the database server, and update the tnsnames entry to match. That prevents the redirect from occurring. -Original Message- Sent: Thursday, October 09, 2003 6:14 PM To: Multiple recipients of list ORACLE-L Richard , I have a question about using the port forwarding feature of ssh. The initial connection to the listener gets tunneled thru by ssh. But what happens if the os is windows and bequeathing the connection is not possible and a redirect must take place ? Listener sends a redirect message back to the client with the location of the dispatcher or dedicated server process. The client then connects directly to the dispatcher or dedicated server process. Now won't all the communication bypass the ssh port forwarding ? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 21:14 Or you can tunnel sqlplus and many other programs through ssh. -Original Message- Sent: Wednesday, October 08, 2003 10:40 AM To: Multiple recipients of list ORACLE-L The question was posed to me about a simple web browser (non ssl), accessing an oracle database and shooting the information across the internet to a client, this info is NOT encrypted i assume. is that also true for like remote sqlplus connections and if you wanted it to be for web, you need to go via SSL and for sqlplus what can be used? Is this where ASO comes in(i think thats is, advanced security option?) thanks, joe -- Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa 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: Richard Ji 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: Norris, Gregory T [ITS] 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: RE: Find an unprintable character inside a column....
Some people have requested this code, so I thought you might as well all have the chance to pick it to bits... Its a function called BAD_ASCII, and it hunts out for any ascii characters with an ascii value of less than 32 in a specified field. (Acknowledgments to my colleague Keith Holmes for help with this code.) Use it as follows: Where a field called DATA in a table TABLE_1 may contain an ascci character with a value less than 32 (ie a non-printing character), the following SQL will find the row in question: select rowid,DATA,dump(DATA) from TABLE_1 where BAD_ASCII(DATA) 0; You could use the PK of the table instead of rowid, of course. You will also note that I select the DATA field in both normal and ascii 'dump' mode, the better to locate where the corruption is located. peter edinburgh ... Source as follows: Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin -- V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; -- exception when others then return -1; end BAD_ASCII; / Peter, I think that you can make this code 25% faster when the data is clean (which hopefully is the general case) by using 'replace', more efficient than a PL/SQL loop, to check whether you have some rubbish (sort of). It will not tell you where the bad character is, however - which means that then you can loop to look for it. Here is what I would suggest : create or replace Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin if (replace(V_text, chr(0)||chr(1)||chr(2)||chr(3)|| chr(4)||chr(5)||chr(6)||chr(7)|| chr(8)||chr(9)||chr(10)||chr(11)|| chr(12)||chr(13)||chr(14)||chr(15)|| chr(16)||chr(17)||chr(18)||chr(19)|| chr(20)||chr(21)||chr(22)||chr(23)|| chr(24)||chr(25)||chr(26)||chr(27)|| chr(28)||chr(29)||chr(30)||chr(31), '') = V_text) then return 0; else V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; end if; -- exception when others then return -1; end BAD_ASCII; / Regards, Stephane Faroult Oriole -- 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: Using ' in Update statement
There is no elite here, but this was a trivial question which I answered by listing manuals in which the answer could be found. I was asked for clarification of my reply, so I provided the clarification. No need to get testy about it. On Fri, 2003-10-10 at 08:54, Mercadante, Thomas F wrote: Naaa. He's an equal opportunity ball-buster. -Original Message- Sent: Thursday, October 09, 2003 5:54 PM To: Multiple recipients of list ORACLE-L Hamid started his question How can I user comma ' in my update statement? I am merely correcting his mistake and then providing a solution. Wow, what a concept on a Oracle Listserv site design for that very purpose. Just curious, Would you have answered in the same way if it had been asked by one of the elite? Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 05:09 PM Please respond to ORACLE-L I didn't mean quote, I meant RTFM. On Thu, 2003-10-09 at 16:34, [EMAIL PROTECTED] wrote: I assume you mean quote update tablea set fielda =' james'ste Camp 'first,'sec' ' update tablea set fielda =' james''ste Camp ''first,''sec'' '; Just use 2 single quotes Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 02:29 PM Please respond to ORACLE-L It's in the documentation. Start with the concepts manual, then SQL*Plus manual and SQL reference. I'm sure that you'll run across the answer because that's where I have found the answer to the very same question. On Thu, 2003-10-09 at 13:59, Hamid Alavi wrote: List, How can I user comma ' in my update statement? update tablea set fielda =' james'ste Camp 'first,'sec' ' Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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 -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this
Direct I/O on Linux
Some time ago, I inquired on this list about direct I/O on Linux file systems. The idea was to specify direct I/O in init.ora parameter filesystemio_options. To be able to do that, file system has to support direct I/O, in much the same way like Veritas quick I/O feature. The first candidate I investigated was SGI XFS, but my numerous attempts to get a definitive answer whether XFS supports it or not, the matter is still unresolved. It is my pleasure to report that the other candidate was much more open and provided a swift answer: JFS does support direct I/O. Here is the answer from Mr. Steve Best, principal IBM's developer of JFS for Linux. Mladen Gogala wrote: Hi Steve! I apologize for contacting you directly, but I've been unable to get an answer to my question any other way. The question is: Does JFS on Linux support direct I/O, i.e. I/O that bypasses buffer cache and behaves as if the file was a raw device. In other words, does JFS on Linux has anything like Veritas Quick I/O?. Yes, the Linux kernel level has to be 2.4.15 or greater. On the 2.5.x kernel level direct I/O is there for JFS also. Thanks in advance and I apologize once again for contacting you like this. -- Mladen Gogala Oracle DBA Thanks, Steve I will test the relative performance of JFS and ext3 file systems this weekend, on my own machine. My prediction is that JFS with direct I/O turned on will be much better then ext3 for updates and index scans, while ext3 will be faster for full table scans (prefetch). The plan is to download the latest version of the driver and link it into 2.4.22 kernel, then generate a table with approximately 1,000,000 records and test full table scans, index scans, serial updates, index updates, deletes and inserts. -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- 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: DB Parameters
You may also want to check for any triggers tied to these tables as well... Rich -Original Message- Walid Alkaakati Sent: Friday, October 10, 2003 9:45 AM To: Multiple recipients of list ORACLE-L HI , I have noticed that both deleting and inserting is too slow (about five hours to insert 4 rows ), this may be because i have more than 40 INDEX ON THIS TWO tables,I will try to disable them before i run my insert statement . Thanks, ++ This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this message in error please delete it and any files transmitted with it, after notifying [EMAIL PROTECTED] Any opinions expressed in this message may be those of the author and not necessarily those of the Company. The Company accepts no responsibility for the accuracy or completeness of any information contained herein. This message is not intended to create legal relations between the Company and the recipient. Recipients should please note that messages sent via the internet may be intercepted and that caution should therefore be exercised before despatching to the Company any confidential or sensitive information. ++ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Walid Alkaakati 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: Rich Gesler 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).
Embedded SQL problem
Title: Embedded SQL problem We have a COBOL program using embedded SQL that is INSERTing thru the DRDA Gateway. -- Any INSERT stmt succeeds via embedded SQL if the fields are all CHAR. -- Any INSERT stmt succeeds via embedded SQL if any fields are VARCHAR2, so LONG AS THE LENGTH IS 74 CHARACTERS. The INSERT fails (ORA-02055) if the VARCHAR2(1069) field contains 75 characters or more. Note that any INSERT stmt succeeds via SQL*Plus if the fields are CHAR or VARCHAR2 (any length). The problem appears to be with an embedded SQL buffer reaching the gateway when VARCHAR2s are involved. Is anyone aware of any OCI issues with 9.2.0.3? The gateway is 9.2.0.4. If anyone wishes to email me privately, I can send you our compiler/sql directive files, the source code (small test program) and table descriptions. Thanx, Alan Martin DBA at Defense Logistics Info Service [EMAIL PROTECTED]
Re: Redos gone crazy--a job for audit?
Dan: Thanks for this -- I'll definitely tuck this away for future reference. Sadly, it's not going to help this time. I don't have a user generating redo, I have an application running amuck. The users (reporters) never log into the database. Some service (Solaris high availability service, I believe) logs a database user on 20 times, then buffers requests from the HA service to the database. A minute or two later, it logs the 20 sessions out and logs in 20 more. Between around 5:30 am and 3:00 am the following day, the database is rolling a new redo log about every 16 minutes. Pretty much new log file every 16 minutes like clockwork. Between 3:00 and 5:30, the HA service is disabled and some kind of maintenance is running. The entire database is about 4100 megs. We're generating more than 3 gigs of redo per day. I sure would like to know what's in those redo logs. Thanks for the help! Looks like another beautiful weekend to hang out on top of a mountain. Did you get to see the leaves turning this year?? Barb --- Daniel Fink [EMAIL PROTECTED] wrote: Barb, Every time I have run into this situation, I have used the following approach and it has always worked. I've never validated it in all cases, so take it with a grain of salt. Redo is generated by block changes. Find the session that is generating the most # of block changes (v$sess_io.block_changes). Then trace back to the session info, sql, etc. Also check for tablespaces in hot backup mode. Daniel Barbara Baker wrote: Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1 user in the database, so I only really need to audit 1 user. audit all by myuser by access; audit update table, insert table, delete table by myuser by access; Is there anything else that will be going to redo that I can capture with audit?? Thanks for any help. Barb __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.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). begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.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: Using ' in Update statement
Ahhh!!! there is no elite and the question was trivial. The great and powerful has spoken, perhaps all questions should be filtered your way for classification. I can assure nothing on this forum is worthy of becoming Testy, merely trying to provide a solution for someone who needed help and of course one must stand their ground when necessary. Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: RE: Using ' in Update statement [EMAIL PROTECTED] .com 10/10/2003 10:19 AM Please respond to ORACLE-L There is no elite here, but this was a trivial question which I answered by listing manuals in which the answer could be found. I was asked for clarification of my reply, so I provided the clarification. No need to get testy about it. On Fri, 2003-10-10 at 08:54, Mercadante, Thomas F wrote: Naaa. He's an equal opportunity ball-buster. -Original Message- Sent: Thursday, October 09, 2003 5:54 PM To: Multiple recipients of list ORACLE-L Hamid started his question How can I user comma ' in my update statement? I am merely correcting his mistake and then providing a solution. Wow, what a concept on a Oracle Listserv site design for that very purpose. Just curious, Would you have answered in the same way if it had been asked by one of the elite? Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 05:09 PM Please respond to ORACLE-L I didn't mean quote, I meant RTFM. On Thu, 2003-10-09 at 16:34, [EMAIL PROTECTED] wrote: I assume you mean quote update tablea set fielda =' james'ste Camp 'first,'sec' ' update tablea set fielda =' james''ste Camp ''first,''sec'' '; Just use 2 single quotes Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 02:29 PM Please respond to ORACLE-L It's in the documentation. Start with the concepts manual, then SQL*Plus manual and SQL reference. I'm sure that you'll run across the answer because that's where I have found the answer to the very same question. On Thu, 2003-10-09 at 13:59, Hamid Alavi wrote: List, How can I user comma ' in my update statement? update tablea set fielda =' james'ste Camp 'first,'sec' ' Thanks, Hamid Alavi Office
Re: Optimizer related init parameters
optimizer_index_cost_adj=10 optimizer_index_caching=50 Use of these two depends on a number of things, all of which are relative to your application and users. When you say 'hybrid', it seems to indicate that you have reporting schema in the same database. How these will effect the querying of the reporting tables depends on the kind of reporting system you have setup. If you have a star schema, I don't know how these options might effect it. Someone else on the list undoubtedly knows. If more of a traditional relational layout is used, these parameters could serve to slow down the reporting queries, as nested loop operations mightbe favored by the CBO where a hash join might be more efficient. optimizer_max_permutations=8000 If you have some very complex queries, this parameter can greatly decrease the parse time. You could probably go even lower - the default on 9i is 2000 IIRC. I've set to to 1000 on on 8i database that has some annoyingly complex views for use with INSTEAD OF triggers. optimizer_dynamic_sampling=4 I don't have any experience with this one yet. To answer your own questions properly, you need to consider a couple things: Which usage has a higher performance priority - the reporting side or OLTP side? Have acceptable performance levels been established for both? Quite a few more items to consider, I;m sure, much of it site dependent. This is all I can think of before the first cup of coffee. Jared On Fri, 2003-10-10 at 05:54, VIVEK_SHARMA wrote: How Good/advisable are the following 4 parameters' Values in a Hybrid Application? Are there any know ill-effects of the same? Application - Banking (Hybrid) Solaris 9 Oracle 9.2 optimizer_max_permutations=8000 optimizer_index_cost_adj=10 optimizer_index_caching=50 optimizer_dynamic_sampling=4 Some INFO :- Database has 6000 Concurrent Users accessing We do ONLY INDEX Scans with exceptional FTS . FTS if present occur only on SMALL Tables (a few Hundred Rows) FTS if unchecked greatly harm our performance Stripe Unit Size 64K Oracle Block Size 8K Will Give any info required Thanks -- 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).
any ever work with Use Cases to model a database?
What experiences have you had? If not what kind of requirements documents do you use? Im particularly interested in people who have worked on projects with relational back ends and object oriented front ends. It seems very difficult to get these two models to work together cohesively. -- 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: Some bug in 9.2
Waleed, If I remember correctly, I have seen this type of message in 7.3.4 database 4 years back. If request for parallel query slave exceeds number of parallel_max_servers parameter. Just check this aspect or reduce parallel degree in your code/hint or table/index degree. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 09 Oct 2003 18:19:25 -0800 This problem started really to be annoying. Suddenly some PQ processes die and the system is never able to bring them back. If some sql is submitted and was lucky enough to request a group of processes that include one of this dead processes, it ends up running without PQ at all. We have RAC 9.2.0.2 on Solaris 2.8. Here is the trace content: *** SESSION ID:(343.56680) 2003-10-09 21:14:17.807 kxfpg1srv could not start local P008 We get this trace in the udump, and nothing goes to bdump. Did any of you experience this problem? Thanks Waleed _ Instant message with integrated webcam using MSN Messenger 6.0. Try it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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: Using ' in Update statement
Rick, What's wrong with reading the manuals before asking the list? In the long run it will definitely help more than getting ready solution from someone on the list. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Friday, October 10, 2003 10:40 AM To: Multiple recipients of list ORACLE-L Ahhh!!! there is no elite and the question was trivial. The great and powerful has spoken, perhaps all questions should be filtered your way for classification. I can assure nothing on this forum is worthy of becoming Testy, merely trying to provide a solution for someone who needed help and of course one must stand their ground when necessary. Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: RE: Using ' in Update statement [EMAIL PROTECTED] .com 10/10/2003 10:19 AM Please respond to ORACLE-L There is no elite here, but this was a trivial question which I answered by listing manuals in which the answer could be found. I was asked for clarification of my reply, so I provided the clarification. No need to get testy about it. On Fri, 2003-10-10 at 08:54, Mercadante, Thomas F wrote: Naaa. He's an equal opportunity ball-buster. -Original Message- Sent: Thursday, October 09, 2003 5:54 PM To: Multiple recipients of list ORACLE-L Hamid started his question How can I user comma ' in my update statement? I am merely correcting his mistake and then providing a solution. Wow, what a concept on a Oracle Listserv site design for that very purpose. Just curious, Would you have answered in the same way if it had been asked by one of the elite? Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 05:09 PM Please respond to ORACLE-L I didn't mean quote, I meant RTFM. On Thu, 2003-10-09 at 16:34, [EMAIL PROTECTED] wrote: I assume you mean quote update tablea set fielda =' james'ste Camp 'first,'sec' ' update tablea set fielda =' james''ste Camp ''first,''sec'' '; Just use 2 single quotes Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 02:29 PM Please respond to ORACLE-L It's in the documentation. Start with the concepts manual, then SQL*Plus manual and SQL reference. I'm sure that you'll run across the answer because that's where I have found the answer to the very same question. On Thu, 2003-10-09 at 13:59, Hamid Alavi wrote: List, How can I user comma ' in my update statement? update tablea set fielda =' james'ste Camp 'first,'sec' ' Thanks, Hamid Alavi Office : 818-737-0526 Cell phone : 818-416-5095 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET:
Re: Redos gone crazy--a job for audit?
Barb, Even if you can't find the user, you can still find the session info and run a trace on the session. If it is consistent, you should be able to trace for a short amount of time and retrieve the statements that are generating redo. Then you can go back to the vendor and say This statement (update emp set empno = empno) is generating 3g of redo per day and it is not performing any work. Please consider this a P1 bug and we need a fix in 10 days. It is especially valuable if you can trace the 'old-good' app and compare it with the 'new-bad' app. Dan Barbara Baker wrote: Dan: Thanks for this -- I'll definitely tuck this away for future reference. Sadly, it's not going to help this time. I don't have a user generating redo, I have an application running amuck. The users (reporters) never log into the database. Some service (Solaris high availability service, I believe) logs a database user on 20 times, then buffers requests from the HA service to the database. A minute or two later, it logs the 20 sessions out and logs in 20 more. Between around 5:30 am and 3:00 am the following day, the database is rolling a new redo log about every 16 minutes. Pretty much new log file every 16 minutes like clockwork. Between 3:00 and 5:30, the HA service is disabled and some kind of maintenance is running. The entire database is about 4100 megs. We're generating more than 3 gigs of redo per day. I sure would like to know what's in those redo logs. Thanks for the help! Looks like another beautiful weekend to hang out on top of a mountain. Did you get to see the leaves turning this year?? Barb begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
RE: Using ' in Update statement
Here is the reason for that: this list would not be useful to me if it was devoted to answering beginner's questions. List would get flooded, I would stop reading as would many other people. It has already happened. This list is a very valuable resource to me and I would hate to lose it to the people asking things like how to set prompt in sqlplus. Usenet groups are the proper place for that. People can learn the basics by reading books and manuals and I don't have much sympathy for the people who don't want to read but post their questions to this list instead. I am trying to help when I think that help is needed, but I am also trying to discourage trivial questions asked for 10th time. Don't get me wrong, I'm not apologizing for my actions, I'm just explaining them. This is my last reply in this thread because I don't intend to create a flame war on this list. I'll surround myself with an SEP field and vanish from sight. the basic stuff by reading manuals On Fri, 2003-10-10 at 11:39, [EMAIL PROTECTED] wrote: Ahhh!!! there is no elite and the question was trivial. The great and powerful has spoken, perhaps all questions should be filtered your way for classification. I can assure nothing on this forum is worthy of becoming Testy, merely trying to provide a solution for someone who needed help and of course one must stand their ground when necessary. Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: RE: Using ' in Update statement [EMAIL PROTECTED] .com 10/10/2003 10:19 AM Please respond to ORACLE-L There is no elite here, but this was a trivial question which I answered by listing manuals in which the answer could be found. I was asked for clarification of my reply, so I provided the clarification. No need to get testy about it. On Fri, 2003-10-10 at 08:54, Mercadante, Thomas F wrote: Naaa. He's an equal opportunity ball-buster. -Original Message- Sent: Thursday, October 09, 2003 5:54 PM To: Multiple recipients of list ORACLE-L Hamid started his question How can I user comma ' in my update statement? I am merely correcting his mistake and then providing a solution. Wow, what a concept on a Oracle Listserv site design for that very purpose. Just curious, Would you have answered in the same way if it had been asked by one of the elite? Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 05:09 PM Please respond to ORACLE-L I didn't mean quote, I meant RTFM. On Thu, 2003-10-09 at 16:34, [EMAIL PROTECTED] wrote: I assume you mean quote update
question about less than values in a range partition
Good morning all
Im experimenting with partitioning a table and do not understand the
concept of the less than value
Say I have a end_date date field
01-JAN-00
01-JAN-01
01-JAN-02
and I create a range partition
(PARTITION SO00Q1 VALUES LESS THAN (TO_DATE('01-JAN-2000',
'DD-MON-')),
PARTITION SO01Q1 VALUES LESS THAN (TO_DATE('01-JAN-2001',
'DD-MON-')),
PARTITION SO02Q1 VALUES LESS THAN (TO_DATE('01-JAN-2002',
'DD-MON-'))...
My mind is thinking
SO00Q1 will have the desired jan 2000
But SO01Q1 would have jan 2001 and since the first partition is less
than 2000
It would include what is in partition 1
And partition 2 would have 1 and 0
Between would make more sence to me
How does the less than N work?
Thanks!
bob
Bob Metelsky Oracle Certified Professional
Phone 203-245-5089 ext113 Office 203-245-5000
Continuum Performance Systems Inc http://www.cps92.com
PGP Key http://cps92.com/cps109.gpg
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bob Metelsky
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: DB Parameters
I stand corrected: tracing probably won't work because the cr and cu values would wrap. Just kidding. :) BEFORE YOU TRY disabling indexes (or anything else for that matter), I strongly advise you to trace the program so you can see where it spends its time. Even if you have guessed the solution correctly, tracing will provide the enormous value of allowing you to quantify exactly how much ground you will have gained. Then there's always the chance that you haven't guessed the solution correctly, in which case you'll need the trace data to help you divine the actual right solution. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Walid Alkaakati Sent: Friday, October 10, 2003 8:45 AM To: Multiple recipients of list ORACLE-L HI , I have noticed that both deleting and inserting is too slow (about five hours to insert 4 rows ), this may be because i have more than 40 INDEX ON THIS TWO tables,I will try to disable them before i run my insert statement . Thanks, ++ This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this message in error please delete it and any files transmitted with it, after notifying [EMAIL PROTECTED] Any opinions expressed in this message may be those of the author and not necessarily those of the Company. The Company accepts no responsibility for the accuracy or completeness of any information contained herein. This message is not intended to create legal relations between the Company and the recipient. Recipients should please note that messages sent via the internet may be intercepted and that caution should therefore be exercised before despatching to the Company any confidential or sensitive information. ++ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Walid Alkaakati 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 information (like subscribing).
RE: question about less than values in a range partition
Look at it in terms of an IF statement. Once the criteria has been met,
you exit the IF and the record is assigned to that corresponding
partition.
-Original Message-
Sent: Friday, October 10, 2003 12:24 PM
To: Multiple recipients of list ORACLE-L
Good morning all
Im experimenting with partitioning a table and do not understand the
concept of the less than value
Say I have a end_date date field
01-JAN-00
01-JAN-01
01-JAN-02
and I create a range partition
(PARTITION SO00Q1 VALUES LESS THAN (TO_DATE('01-JAN-2000',
'DD-MON-')), PARTITION SO01Q1 VALUES LESS THAN
(TO_DATE('01-JAN-2001', 'DD-MON-')), PARTITION SO02Q1 VALUES LESS
THAN (TO_DATE('01-JAN-2002', 'DD-MON-'))...
My mind is thinking
SO00Q1 will have the desired jan 2000
But SO01Q1 would have jan 2001 and since the first partition is less
than 2000
It would include what is in partition 1
And partition 2 would have 1 and 0
Between would make more sence to me
How does the less than N work?
Thanks!
bob
Bob Metelsky Oracle Certified Professional
Phone 203-245-5089 ext113 Office 203-245-5000
Continuum Performance Systems Inc http://www.cps92.com
PGP Key http://cps92.com/cps109.gpg
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bob Metelsky
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: Rothouse, Michael
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: USERENV('SESSIONID') on RAC
Rich,
Is there a way to get your own executing program from a
9.2.0.4 RAC node?
USERENV('SESSIONID') and SYS_CONTEXT('USERENV','SESSIONID')
each return a
big fat zero on RAC.
Were you logged in as SYS on the RAC node? I believe the SESSIONID (which is
actually AUDSID) will be 0 for SYS/SYSDBA Internal connects...
04:45:52 SQL show user
USER is SYS (JK - Connected as SYSDBA)
04:45:55 SQL select SYS_CONTEXT('USERENV','SESSIONID') from dual;
SYS_CONTEXT('USERENV','SESSIONID')
0
04:46:04 SQL select USERENV('SESSIONID') from dual;
USERENV('SESSIONID')
0
04:46:17 SQL connect gl
Enter password:
Connected.
04:46:26 SQL select USERENV('SESSIONID') from dual;
USERENV('SESSIONID')
7077637
04:46:32 SQL select SYS_CONTEXT('USERENV','SESSIONID') from dual;
SYS_CONTEXT('USERENV','SESSIONID')
7077637
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)
Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Kanagaraj
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: Redos gone crazy--trying to use logminer
internal usually refers to index maintenance ... In my recent action, I did something like to get a better picture ... SELECT username, seg_owner, seg_name, seg_type ,CASE WHEN stmt LIKE 'UPDATE %' THEN SUBSTR(stmt, 1, INSTR(stmt,'',1,4)) WHEN stmt LIKE 'INSERT %' THEN SUBSTR(stmt, 1, INSTR(stmt,'',1,4)) END stmt_type ,CASE WHEN stmt LIKE 'UPDATE %' THEN SUBSTR(stmt, INSTR(stmt,'',1,4)+1) WHEN stmt LIKE 'INSERT %' THEN SUBSTR(stmt, INSTR(stmt,'',1,4)+1) END stmt_detail FROM ( SELECT UPPER(sql_redo) stmt FROM sys.tom_logmnr WHERE (sql_redo IS NOT NULL AND LOWER(sql_redo) NOT LIKE 'comm%' AND LOWER(sql_redo) NOT LIKE 'set%' AND LOWER(sql_redo) NOT LIKE 'rollba%' AND sql_redo NOT LIKE '%PERFSTAT%' AND sql_redo NOT LIKE 'Unsupported%')) / where tom_logmnr is the table I built using CTAS from v$lgmnr_contents 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: Thursday, October 09, 2003 8:14 PM To: Multiple recipients of list ORACLE-L Indeed log miner seems to be my only option for figuring this one out. Log miner is weird!! I had not used it before. interesting... I found this query on metalink, and here are the results. Is this particularly bizarre? This is for 5 redo logs, each of which filled up within 15 minutes. Shouldn't I have something populated for seg_name? Is it particularly screwed up that this field is empty?? Also the count for internal seems high. SQL select seg_name, count(*) from v$logmnr_contents group by seg_name; SEG_NAME COUNT(*) -- 1128417 SQL SQL SQL spool logminer_qry2.lis SQL set echo on SQL -- breakdown of transactions by table, and type SQL SQL select seg_name, operation, count(*) 2 from v$logmnr_contents group by seg_name, operation; SEG_NAME OPERATION COUNT(*) -- COMMIT 547 DELETE 1 INSERT 2204 INTERNAL 563760 START 548 UPDATE 561357 6 rows selected. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: log miner should give you what you want ... why not? On last friday something happened and in our database which usually averages about 100x100M archive logs, it started throwing 41 files between 2pm-3pm, 248 between 3pm-4pm, 95 between 4pm-5pm. Of course we couldn't analyze all files, but an analysis og a 10 minute interval at the beginning of archive franzy shows a clear set of 5 SQLS that repeated about 83000 times in 10 minutes. Once we gave it to development, they were able to identify the process which was using the code in question and it became easier. I'd start at-least half hour before the peak time and do a slow analysis. I have also found that instead of selecting from v$lgmnr_contents, I am more comfortable with doign a CTAS and then perform queries at my leisure for a detailed analysis. Go for log miner ... at-least it will tell you what caused the problem. HTH 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: Thursday, October 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the gems.) It's on Solaris 5.8, Oracle 8.1.7.2 The database suddenly went from kicking out 50 meg redo logs 2 or 3 times a day to churning them out every 15 minutes. The entire database is only about 6 gigs; we now sometimes generate 2 or 3 gigs of redo per day. Even tho this started when a small change was made by the vendor, the vendor is claiming that (ok, hold on to your hats) it was not their change!! I want to know what's in those redo logs. I initially thought about log miner. However, I'm not sure log miner will give me what I want. I tried these 2 audit commands. I'm not seeing much from them. Is there another audit command that might give me better info? There's only 1
Re: Redos gone crazy--a job for audit?
Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? --- Daniel Fink [EMAIL PROTECTED] wrote: Barb, Even if you can't find the user, you can still find the session info and run a trace on the session. If it is consistent, you should be able to trace for a short amount of time and retrieve the statements that are generating redo. Then you can go back to the vendor and say This statement (update emp set empno = empno) is generating 3g of redo per day and it is not performing any work. Please consider this a P1 bug and we need a fix in 10 days. It is especially valuable if you can trace the 'old-good' app and compare it with the 'new-bad' app. Dan Barbara Baker wrote: Dan: Thanks for this -- I'll definitely tuck this away for future reference. Sadly, it's not going to help this time. I don't have a user generating redo, I have an application running amuck. The users (reporters) never log into the database. Some service (Solaris high availability service, I believe) logs a database user on 20 times, then buffers requests from the HA service to the database. A minute or two later, it logs the 20 sessions out and logs in 20 more. Between around 5:30 am and 3:00 am the following day, the database is rolling a new redo log about every 16 minutes. Pretty much new log file every 16 minutes like clockwork. Between 3:00 and 5:30, the HA service is disabled and some kind of maintenance is running. The entire database is about 4100 megs. We're generating more than 3 gigs of redo per day. I sure would like to know what's in those redo logs. Thanks for the help! Looks like another beautiful weekend to hang out on top of a mountain. Did you get to see the leaves turning this year?? Barb begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: Avoiding full table scan
Downloaded and read last night.
An excellent paper, as Tim stated.
Now if I could just get an opportunity to do something like that...
The downside of working for a small company.
Jared
Tim Gorman [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
10/09/2003 06:09 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: Avoiding full table scan
Same author (Jeff Maresh) has also published a new paper on physical structure of data warehouses to accommodate the life cycle of data. It is fantastic.
I've published both papers ("Managing the Data Lifecycle" and "In Defense of FULL table scans") on my website at "http://www.evdbt.com/papers.htm". The "FULL table scan" paper is excellent, but I think the "Data Lifecycle" paper is ground-breaking, covering topics that have not yet been treated appropriately. I highly recommend them both...
on 10/9/03 10:54 AM, Goulet, Dick at [EMAIL PROTECTED] wrote:
Jack,
In a recent copy of SELECT magazine there is a discussion in defense of full table scans. I believe you might find it VERY interesting. Although I was aware of some of what the author spoke he put it in a vein that makes extreme sense.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-Original Message-
From: Jack van Zanen [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 09, 2003 10:49 AM
To: Multiple recipients of list ORACLE-L
Subject: Avoiding full table scan
Hi All,
I wish to avoid a full tablescan on the following data
V. Zanen
Zanen
Van Zanen
..
..
..
Lot's more data
Select * from table where upper(name) like '%ZANEN%'
I could create a function based index on upper(name) but this does not take care of the % and like operator.
Oracle has this (I believe it's called) context stuff that you can index varchar fields etc. Is this the (only possible?) way to go??
TIA
Jack
RE: Redos gone crazy--a job for audit?
Why? Did you have bad experiences with temp tables? I thought, using temp tables should reduce amount of redo. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? --- Daniel Fink [EMAIL PROTECTED] wrote: Barb, Even if you can't find the user, you can still find the session info and run a trace on the session. If it is consistent, you should be able to trace for a short amount of time and retrieve the statements that are generating redo. Then you can go back to the vendor and say This statement (update emp set empno = empno) is generating 3g of redo per day and it is not performing any work. Please consider this a P1 bug and we need a fix in 10 days. It is especially valuable if you can trace the 'old-good' app and compare it with the 'new-bad' app. Dan Barbara Baker wrote: Dan: Thanks for this -- I'll definitely tuck this away for future reference. Sadly, it's not going to help this time. I don't have a user generating redo, I have an application running amuck. The users (reporters) never log into the database. Some service (Solaris high availability service, I believe) logs a database user on 20 times, then buffers requests from the HA service to the database. A minute or two later, it logs the 20 sessions out and logs in 20 more. Between around 5:30 am and 3:00 am the following day, the database is rolling a new redo log about every 16 minutes. Pretty much new log file every 16 minutes like clockwork. Between 3:00 and 5:30, the HA service is disabled and some kind of maintenance is running. The entire database is about 4100 megs. We're generating more than 3 gigs of redo per day. I sure would like to know what's in those redo logs. Thanks for the help! Looks like another beautiful weekend to hang out on top of a mountain. Did you get to see the leaves turning this year?? Barb begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: Igor Neyman 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: Redos gone crazy--a job for audit?
Igor, Try running the following test: create table t6 (i int) ; create global temporary table t7 (i int) on commit delete rows; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Note the value here insert into t6 select obj# from sys.obj$ where rownum = 1; commit; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Compare the two. In my case it's almost 10-fold increase in the amount of redo generated. We ran into this not too long ago. Bug# 2874489. Fixed in 10.1.0.1 with some backports available for 9.2.0.3/4 on **some** platforms Note: Bug above doesn't affect direct path load Thanks, Boris. --- Igor Neyman [EMAIL PROTECTED] wrote: Why? Did you have bad experiences with temp tables? I thought, using temp tables should reduce amount of redo. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: SAN-Eva3000 experiences
Check out www.baarf.com regarding RAID levels. Check especially the Sane SAN paper by James Morle as to do's and dont's. EVA stuff is expensive, and some of our customers have had to spend much time and money on spare parts and consultants. Others have been happy with it. Mogens Jeroen van Sluisdam wrote: Hi, We're in the middle of buying a storage solution that will probably be an eva3000. Because I'm new with these kind of storages and I will get implementation advice from consultants I would like to have some background with experiences in implementing an oracle database on an eva3000. Any do's and don'ts ?? Any advice on raid-levels to use? Thanks in advance, Jeroen -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: Redos gone crazy--a job for audit?
I just repeated this test and found that for normal tables the redo size is 292848 for GTT the redo size is 1581888 (increase of 540%) Thank You Thank You Thank You Thank You Thank You Thank You Boris for mentioning this. 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, October 10, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Igor, Try running the following test: create table t6 (i int) ; create global temporary table t7 (i int) on commit delete rows; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Note the value here insert into t6 select obj# from sys.obj$ where rownum = 1; commit; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Compare the two. In my case it's almost 10-fold increase in the amount of redo generated. We ran into this not too long ago. Bug# 2874489. Fixed in 10.1.0.1 with some backports available for 9.2.0.3/4 on **some** platforms Note: Bug above doesn't affect direct path load Thanks, Boris. --- Igor Neyman [EMAIL PROTECTED] wrote: Why? Did you have bad experiences with temp tables? I thought, using temp tables should reduce amount of redo. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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).
Transportable Tablespaces
Anyone got a "1, 2, 3" for using transportable tablespaces? Got a client that thinks this might be a good solution to transfer large amounts of data. This is HP/UX and should be 8.1.7.4... Any gotchas that may kill this idea? Thanks. Michael Kline, Principal ConsultantBusiness To Business Solutions, LLCRichmond, VA804-744-1545
RE: Redos gone crazy--a job for audit?
Boris, I used your script (well, almost: in your script you create temporary global table, but never use it, so, I modified it). And it shows redo size increase substantially lower (~7 times) in case of using temp table. But, I was running script on 8.1.5. When, running on 9.2 it appears, that you are correct: temp table generates much more redo than permanent table. Both results are shown below: Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options PL/SQL Release 8.1.5.0.0 - Production SQL create table t6 (i int) ; Table created. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 8780 SQL SQL insert into t6 select obj# from sys.obj$ where rownum = 1; 1 rows created. SQL SQL commit; Commit complete. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 157964 SQL = The redo size increase with permanent table is: 157964 - 8780 = 149184 SQL create global temporary table t7 (i int) on commit 2 delete rows; Table created. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 162060 SQL SQL insert into t7 select obj# from sys.obj$ where rownum = 1; 1 rows created. SQL SQL commit; Commit complete. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 189264 SQL = The redo size increase with global temporary table is: 189264 - 162060 = 27204 Now, on 9.2: Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL create table t6 (i int) ; Table created. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 7204 SQL SQL insert into t6 select obj# from sys.obj$ where rownum = 1; 9038 rows created. SQL SQL commit; Commit complete. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 150252 SQL = The redo size increase with permanent table is: 150252 - 7204 = 143048 SQL create global temporary table t7 (i int) on commit 2 delete rows; Table created. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 154032 SQL SQL insert into t7 select obj# from sys.obj$ where rownum = 1; 9039 rows created. SQL SQL commit; Commit complete. SQL SQL select value from v$mystat m, v$statname s 2 where m.statistic#=s.statistic# and s.name = 'redo size'; VALUE -- 1287624 SQL = The redo size increase with global temporary table is: 1287624 - 154032 = 1133592 which is quite different from the testing results under 8.1.5. I don't have access to Metalink right now to check Bug# 2874489. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 1:39 PM To: Multiple recipients of list ORACLE-L Igor, Try running the following test: create table t6 (i int) ; create global temporary table t7 (i int) on commit delete rows; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Note the value here insert into t6 select obj# from sys.obj$ where rownum = 1; commit; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Compare the two. In my case it's almost 10-fold increase in the amount of redo generated. We ran into this not too long ago. Bug# 2874489. Fixed in 10.1.0.1 with some backports available for 9.2.0.3/4 on **some** platforms Note: Bug above doesn't affect direct path load Thanks, Boris. --- Igor Neyman [EMAIL PROTECTED] wrote: Why? Did you have bad experiences with temp tables? I thought, using temp tables should reduce amount of redo. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? __ Post your free ad now! http://personals.yahoo.ca -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boris Dali 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: RE: RE: Find an unprintable character inside a column....
I played with this a bit. First, I created some test data with one column corrupted with a single random character of 0-31 replacing a random char in that column 20% of the rows of the table. Peter's function correctly found all of the rows in 7.5 seconds. Stephane's function ran in 3.5 seconds, but didn't find any of the rows. I didn't attempt to correct the code. Then I tried a function based on owa_pattern.regex. My initial attempts didn't return the correct rows, as the regex pattern needed some tuning. I didn't attempt to fix it, as it was woefully slow, about 30 seconds. Regex in the WHERE clause in 10g will be nice. Jared Stephane Faroult [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/10/2003 07:09 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: RE: Find an unprintable character inside a column Some people have requested this code, so I thought you might as well all have the chance to pick it to bits... Its a function called BAD_ASCII, and it hunts out for any ascii characters with an ascii value of less than 32 in a specified field. (Acknowledgments to my colleague Keith Holmes for help with this code.) Use it as follows: Where a field called DATA in a table TABLE_1 may contain an ascci character with a value less than 32 (ie a non-printing character), the following SQL will find the row in question: select rowid,DATA,dump(DATA) from TABLE_1 where BAD_ASCII(DATA) 0; You could use the PK of the table instead of rowid, of course. You will also note that I select the DATA field in both normal and ascii 'dump' mode, the better to locate where the corruption is located. peter edinburgh ... Source as follows: Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin -- V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; -- exception when others then return -1; end BAD_ASCII; / Peter, I think that you can make this code 25% faster when the data is clean (which hopefully is the general case) by using 'replace', more efficient than a PL/SQL loop, to check whether you have some rubbish (sort of). It will not tell you where the bad character is, however - which means that then you can loop to look for it. Here is what I would suggest : create or replace Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin if (replace(V_text, chr(0)||chr(1)||chr(2)||chr(3)|| chr(4)||chr(5)||chr(6)||chr(7)|| chr(8)||chr(9)||chr(10)||chr(11)|| chr(12)||chr(13)||chr(14)||chr(15)|| chr(16)||chr(17)||chr(18)||chr(19)|| chr(20)||chr(21)||chr(22)||chr(23)|| chr(24)||chr(25)||chr(26)||chr(27)|| chr(28)||chr(29)||chr(30)||chr(31), '') = V_text) then return 0; else V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; end if; -- exception when others then return -1; end BAD_ASCII; / Regards, Stephane Faroult Oriole -- 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: Transportable Tablespaces
Title: Message You can reference the documentation at http://tinyurl.com/qhcl paying specific attention to the "Limitations". MetaLink documents 100693.1 and 77523.1 provide some helpful information as well. -Original Message-From: Michael Kline [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 3:14 PMTo: Multiple recipients of list ORACLE-LSubject: Transportable Tablespaces Anyone got a "1, 2, 3" for using transportable tablespaces? Got a client that thinks this might be a good solution to transfer large amounts of data. This is HP/UX and should be 8.1.7.4... Any gotchas that may kill this idea? Thanks. Michael Kline, Principal ConsultantBusiness To Business Solutions, LLCRichmond, VA804-744-1545
RE: Using ' in Update statement
You guys are mean !! Tom Kyte would have given me 10 ways of writing the statement, would have traced every one of them under different versions and on different platforms, pointed out the number of logical reads, elapsed time, et all, and told me which one is better. Regards Raj [EMAIL PROTECTED] disys.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Using ' in Update statement ity.com 10/10/2003 01:54 PM Please respond to ORACLE-L What he said. Mladen Gogala [EMAIL PROTECTED] To:Multiple recipients of Sent by: list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject:RE: Using ' in Update statement 10/10/2003 09:14 AM Please respond to ORACLE-L Here is the reason for that: this list would not be useful to me if it was devoted to answering beginner's questions. List would get flooded, I would stop reading as would many other people. It has already happened. This list is a very valuable resource to me and I would hate to lose it to the people asking things like how to set prompt in sqlplus. Usenet groups are the proper place for that. People can learn the basics by reading books and manuals and I don't have much sympathy for the people who don't want to read but post their questions to this list instead. I am trying to help when I think that help is needed, but I am also trying to discourage trivial questions asked for 10th time. Don't get me wrong, I'm not apologizing for my actions, I'm just explaining them. This is my last reply in this thread because I don't intend to create a flame war on this list. I'll surround myself with an SEP field and vanish from sight. the basic stuff by reading manuals On Fri, 2003-10-10 at 11:39, [EMAIL PROTECTED] wrote: Ahhh!!! there is no elite and the question was trivial. The great and powerful has spoken, perhaps all questions should be filtered your way for classification. I can assure nothing on this forum is worthy of becoming Testy, merely trying to provide a solution for someone who needed help and of course one must stand their ground when necessary. Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: RE: Using ' in Update statement [EMAIL PROTECTED] .com 10/10/2003 10:19 AM Please respond to ORACLE-L There is no elite here, but this was a trivial question which I answered by listing manuals in which the answer could be found. I was asked for clarification of my reply, so I provided the clarification. No need to get testy about it. On Fri, 2003-10-10 at 08:54, Mercadante, Thomas F wrote: Naaa. He's an equal opportunity ball-buster. -Original Message- Sent:
RE: Transportable Tablespaces
Title: Message Just be aware of an undocumented "feature" in 8i (8.1.7.2 for me). Once you transport a tablespace from DB-A to DB-B, you can't transport (the same TS) from DB-B to DB-C. There are some internal issues preventing the re-transport. I believe this is fixed in 9i. Regards, Alan Martin DBA - Defense Logistics Info Service -Original Message-From: Rothouse, Michael [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 3:49 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Transportable Tablespaces You can reference the documentation at http://tinyurl.com/qhcl paying specific attention to the "Limitations". MetaLink documents 100693.1 and 77523.1 provide some helpful information as well. -Original Message-From: Michael Kline [mailto:[EMAIL PROTECTED] Sent: Friday, October 10, 2003 3:14 PMTo: Multiple recipients of list ORACLE-LSubject: Transportable Tablespaces Anyone got a "1, 2, 3" for using transportable tablespaces? Got a client that thinks this might be a good solution to transfer large amounts of data. This is HP/UX and should be 8.1.7.4... Any gotchas that may kill this idea? Thanks. Michael Kline, Principal ConsultantBusiness To Business Solutions, LLCRichmond, VA804-744-1545
RE: Using ' in Update statement
Do you really not see the difference between the question we are discussing here and the ones Tom Kyte is answering? Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Friday, October 10, 2003 2:54 PM To: Multiple recipients of list ORACLE-L You guys are mean !! Tom Kyte would have given me 10 ways of writing the statement, would have traced every one of them under different versions and on different platforms, pointed out the number of logical reads, elapsed time, et all, and told me which one is better. Regards Raj [EMAIL PROTECTED] disys.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Using ' in Update statement ity.com 10/10/2003 01:54 PM Please respond to ORACLE-L What he said. Mladen Gogala [EMAIL PROTECTED] To:Multiple recipients of Sent by: list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject:RE: Using ' in Update statement 10/10/2003 09:14 AM Please respond to ORACLE-L Here is the reason for that: this list would not be useful to me if it was devoted to answering beginner's questions. List would get flooded, I would stop reading as would many other people. It has already happened. This list is a very valuable resource to me and I would hate to lose it to the people asking things like how to set prompt in sqlplus. Usenet groups are the proper place for that. People can learn the basics by reading books and manuals and I don't have much sympathy for the people who don't want to read but post their questions to this list instead. I am trying to help when I think that help is needed, but I am also trying to discourage trivial questions asked for 10th time. Don't get me wrong, I'm not apologizing for my actions, I'm just explaining them. This is my last reply in this thread because I don't intend to create a flame war on this list. I'll surround myself with an SEP field and vanish from sight. the basic stuff by reading manuals On Fri, 2003-10-10 at 11:39, [EMAIL PROTECTED] wrote: Ahhh!!! there is no elite and the question was trivial. The great and powerful has spoken, perhaps all questions should be filtered your way for classification. I can assure nothing on this forum is worthy of becoming Testy, merely trying to provide a solution for someone who needed help and of course one must stand their ground when necessary. Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: RE: Using ' in Update statement [EMAIL PROTECTED] .com 10/10/2003 10:19 AM Please respond to ORACLE-L There is no elite here, but this was a trivial question which I answered by listing manuals in which the answer could be found. I was asked for clarification of my reply, so I provided the clarification. No need to get testy about it. On Fri, 2003-10-10 at 08:54, Mercadante, Thomas F wrote: Naaa. He's an equal opportunity ball-buster. -Original Message- Sent: Thursday, October 09, 2003 5:54 PM To: Multiple recipients of list ORACLE-L Hamid started his question How can I user comma ' in my update statement? I am merely correcting his mistake and then providing a solution. Wow, what a concept on a Oracle Listserv site design for that very purpose. Just curious, Would you have answered in the same way if it had been asked by one of the elite? Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: Re: Using ' in Update statement [EMAIL PROTECTED] .com 10/09/2003 05:09 PM Please respond to ORACLE-L I didn't mean quote, I meant RTFM. On Thu, 2003-10-09 at 16:34, [EMAIL PROTECTED] wrote: I assume you mean quote update tablea set fielda =' james'ste Camp 'first,'sec' ' update tablea set fielda =' james''ste Camp
Re: Using ' in Update statement
Get over it and like it was stated earlier, go to usenet. joe [EMAIL PROTECTED] wrote: You guys are mean !! Tom Kyte would have given me 10 ways of writing the statement, would have traced every one of them under different versions and on different platforms, pointed out the number of logical reads, elapsed time, et all, and told me which one is better. Regards Raj [EMAIL PROTECTED] disys.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: RE: Using ' in Update statement ity.com 10/10/2003 01:54 PM Please respond to ORACLE-L What he said. Mladen Gogala [EMAIL PROTECTED] To:Multiple recipients of Sent by: list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject:RE: Using ' in Update statement 10/10/2003 09:14 AM Please respond to ORACLE-L Here is the reason for that: this list would not be useful to me if it was devoted to answering beginner's questions. List would get flooded, I would stop reading as would many other people. It has already happened. This list is a very valuable resource to me and I would hate to lose it to the people asking things like how to set prompt in sqlplus. Usenet groups are the proper place for that. People can learn the basics by reading books and manuals and I don't have much sympathy for the people who don't want to read but post their questions to this list instead. I am trying to help when I think that help is needed, but I am also trying to discourage trivial questions asked for 10th time. Don't get me wrong, I'm not apologizing for my actions, I'm just explaining them. This is my last reply in this thread because I don't intend to create a flame war on this list. I'll surround myself with an SEP field and vanish from sight. the basic stuff by reading manuals On Fri, 2003-10-10 at 11:39, [EMAIL PROTECTED] wrote: Ahhh!!! there is no elite and the question was trivial. The great and powerful has spoken, perhaps all questions should be filtered your way for classification. I can assure nothing on this forum is worthy of becoming Testy, merely trying to provide a solution for someone who needed help and of course one must stand their ground when necessary. Mladen Gogala [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: RE: Using ' in Update statement [EMAIL PROTECTED] .com 10/10/2003 10:19 AM Please respond to ORACLE-L There is no elite here, but this was a trivial question which I answered by listing manuals in which the answer could be found. I was asked for clarification of my reply, so I provided the clarification. No need to get testy about it. On Fri, 2003-10-10 at 08:54, Mercadante, Thomas F
RE: RE: RE: Find an unprintable character inside a column....
Actually, I was toying with the idea of writing an external procedure that would allow me to call pcre library (PCRE=Perl Compatible Regular Expressions) which would be nice, but then again, the whole perl is available through the set of external procedures, so it wouldn't be very useful. External procedures can be used in the where clause, provided they're declared as deterministic. Actually, it wouldn't be that hard to extend 9.2 database with regular expressions. On Fri, 2003-10-10 at 15:39, [EMAIL PROTECTED] wrote: I played with this a bit. First, I created some test data with one column corrupted with a single random character of 0-31 replacing a random char in that column 20% of the rows of the table. Peter's function correctly found all of the rows in 7.5 seconds. Stephane's function ran in 3.5 seconds, but didn't find any of the rows. I didn't attempt to correct the code. Then I tried a function based on owa_pattern.regex. My initial attempts didn't return the correct rows, as the regex pattern needed some tuning. I didn't attempt to fix it, as it was woefully slow, about 30 seconds. Regex in the WHERE clause in 10g will be nice. Jared Stephane Faroult [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/10/2003 07:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: RE: RE: RE: Find an unprintable character inside a column Some people have requested this code, so I thought you might as well all have the chance to pick it to bits... Its a function called BAD_ASCII, and it hunts out for any ascii characters with an ascii value of less than 32 in a specified field. (Acknowledgments to my colleague Keith Holmes for help with this code.) Use it as follows: Where a field called DATA in a table TABLE_1 may contain an ascci character with a value less than 32 (ie a non-printing character), the following SQL will find the row in question: select rowid,DATA,dump(DATA) from TABLE_1 where BAD_ASCII(DATA) 0; You could use the PK of the table instead of rowid, of course. You will also note that I select the DATA field in both normal and ascii 'dump' mode, the better to locate where the corruption is located. peter edinburgh ... Source as follows: Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin -- V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; -- exception when others then return -1; end BAD_ASCII; / Peter, I think that you can make this code 25% faster when the data is clean (which hopefully is the general case) by using 'replace', more efficient than a PL/SQL loop, to check whether you have some rubbish (sort of). It will not tell you where the bad character is, however - which means that then you can loop to look for it. Here is what I would suggest : create or replace Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin if (replace(V_text, chr(0)||chr(1)||chr(2)||chr(3)|| chr(4)||chr(5)||chr(6)||chr(7)|| chr(8)||chr(9)||chr(10)||chr(11)|| chr(12)||chr(13)||chr(14)||chr(15)|| chr(16)||chr(17)||chr(18)||chr(19)|| chr(20)||chr(21)||chr(22)||chr(23)|| chr(24)||chr(25)||chr(26)||chr(27)|| chr(28)||chr(29)||chr(30)||chr(31), '') = V_text) then return 0; else V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; end if; -- exception when others then return -1; end BAD_ASCII; / Regards, Stephane Faroult Oriole -- 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). -- Mladen Gogala Oracle DBA
RE: RE: RE: Find an unprintable character inside a column....
Definitely worth trying if you have a need for it. I don't, and it's more work than I want to do just because I can. Mladen Gogala [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/10/2003 01:44 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: RE: RE: Find an unprintable character inside a column Actually, I was toying with the idea of writing an external procedure that would allow me to call pcre library (PCRE=Perl Compatible Regular Expressions) which would be nice, but then again, the whole perl is available through the set of external procedures, so it wouldn't be very useful. External procedures can be used in the where clause, provided they're declared as deterministic. Actually, it wouldn't be that hard to extend 9.2 database with regular expressions. On Fri, 2003-10-10 at 15:39, [EMAIL PROTECTED] wrote: I played with this a bit. First, I created some test data with one column corrupted with a single random character of 0-31 replacing a random char in that column 20% of the rows of the table. Peter's function correctly found all of the rows in 7.5 seconds. Stephane's function ran in 3.5 seconds, but didn't find any of the rows. I didn't attempt to correct the code. Then I tried a function based on owa_pattern.regex. My initial attempts didn't return the correct rows, as the regex pattern needed some tuning. I didn't attempt to fix it, as it was woefully slow, about 30 seconds. Regex in the WHERE clause in 10g will be nice. Jared Stephane Faroult [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/10/2003 07:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: RE: RE: RE: Find an unprintable character inside a column Some people have requested this code, so I thought you might as well all have the chance to pick it to bits... Its a function called BAD_ASCII, and it hunts out for any ascii characters with an ascii value of less than 32 in a specified field. (Acknowledgments to my colleague Keith Holmes for help with this code.) Use it as follows: Where a field called DATA in a table TABLE_1 may contain an ascci character with a value less than 32 (ie a non-printing character), the following SQL will find the row in question: select rowid,DATA,dump(DATA) from TABLE_1 where BAD_ASCII(DATA) 0; You could use the PK of the table instead of rowid, of course. You will also note that I select the DATA field in both normal and ascii 'dump' mode, the better to locate where the corruption is located. peter edinburgh ... Source as follows: Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin -- V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; -- exception when others then return -1; end BAD_ASCII; / Peter, I think that you can make this code 25% faster when the data is clean (which hopefully is the general case) by using 'replace', more efficient than a PL/SQL loop, to check whether you have some rubbish (sort of). It will not tell you where the bad character is, however - which means that then you can loop to look for it. Here is what I would suggest : create or replace Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin if (replace(V_text, chr(0)||chr(1)||chr(2)||chr(3)|| chr(4)||chr(5)||chr(6)||chr(7)|| chr(8)||chr(9)||chr(10)||chr(11)|| chr(12)||chr(13)||chr(14)||chr(15)|| chr(16)||chr(17)||chr(18)||chr(19)|| chr(20)||chr(21)||chr(22)||chr(23)|| chr(24)||chr(25)||chr(26)||chr(27)|| chr(28)||chr(29)||chr(30)||chr(31), '') = V_text) then return 0; else V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; end if; -- exception when others then return -1; end BAD_ASCII; / Regards, Stephane Faroult Oriole -- 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,
RE: Using ' in Update statement
I don't know whether Tom does all of the asktom site on his own time, but I doubt it. His website doesn't do much to hurt sales of his book, so he also has a greater financial interest. Money is a good motivator. Tom is also a VP at Oracle, and does some pretty good PR for his employer via his web site. I imagine he is able to spend more time on the job answering questions than what would be practical for most people on this list. Many of us answer questions here for a variety of reasons. Here's my list of reasons: * it's an interesting topic * it's a topic that covers something I need to do * it's a topic that is not easily answered from the manuals, and the person posing the question could use some help. * it's a topic regarding something I have already learned to solve * I will learn something by participating The last one covers many more threads than I could possibly be involved in, so I try to limit it to those that will be of use to me somehow. Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/10/2003 12:54 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: Using ' in Update statement You guys are mean !! Tom Kyte would have given me 10 ways of writing the statement, would have traced every one of them under different versions and on different platforms, pointed out the number of logical reads, elapsed time, et all, and told me which one is better. Regards Raj [EMAIL PROTECTED] disys.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: Using ' in Update statement ity.com 10/10/2003 01:54 PM Please respond to ORACLE-L What he said. Mladen Gogala [EMAIL PROTECTED] To:Multiple recipients of Sent by: list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: Subject:RE: Using ' in Update statement 10/10/2003 09:14 AM Please respond to ORACLE-L Here is the reason for that: this list would not be useful to me if it was devoted to answering beginner's questions. List would get flooded, I would stop reading as would many other people. It has already happened. This list is a very valuable resource to me and I would hate to lose it to the people asking things like how to set prompt in sqlplus. Usenet groups are the proper place for that. People can learn the basics by reading books and manuals and I don't have much sympathy for the people who don't want to read but post their questions to this list instead. I am trying to help when I think that help is needed, but I am also trying to discourage trivial questions asked for 10th time. Don't get me wrong, I'm not apologizing for my actions, I'm just explaining them. This is my last reply in this thread because I don't intend to create a flame war on this list. I'll surround myself with an SEP field and vanish from sight. the basic stuff by reading manuals On Fri, 2003-10-10 at 11:39, [EMAIL PROTECTED] wrote: Ahhh!!! there is no elite and the question was trivial. The great and powerful has spoken, perhaps all questions should be filtered your way for classification. I can assure nothing on this forum is worthy of becoming Testy, merely trying to provide a solution for someone who needed help and of course one must stand their ground when necessary. Mladen Gogala [EMAIL PROTECTED]To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ng.com cc: Sent by: Subject: RE: Using ' in Update statement [EMAIL PROTECTED] .com 10/10/2003 10:19 AM Please respond to ORACLE-L There is no elite here, but this was a trivial question which I answered by listing manuals in which the answer could be found. I was asked for clarification of my reply, so I provided the clarification. No need to get testy about it. On Fri, 2003-10-10 at 08:54, Mercadante,
Re: FW: Oracle Performance Software from Veritas
May I just add that his real name is Gaja Vahatneyhatneyhatney. That is what I called him in BAARF. The Musical.. [EMAIL PROTECTED] wrote: Our good friend Gaja Vaidyanatha is now with Veritas, so this isn't really too surprising. :) Jared *David Wagoner [EMAIL PROTECTED]* Sent by: [EMAIL PROTECTED] 10/06/2003 01:59 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:FW: Oracle Performance Software from Veritas Just got this email from Veritas...apparently they are getting into the database performance business for Oracle (and SQL Server too I think). Best regards, *David B. Wagoner* Database Administrator Arsenal Digital Solutions the most trusted source for STORAGE MANAGEMENT SERVICES The contents of this e-mail message may be privileged and/or confidential. If you are not the intended recipient, any review, dissemination, copying, distribution or other use of the contents of this message or any attachment by you is strictly prohibited. If you receive this communication in error, please notify us immediately by return e-mail or by telephone (919-466-6700), and please delete this message and all attachments from your system. Thank you. -Original Message-* From:* VERITAS Software [mailto:[EMAIL PROTECTED] Sent:* Monday, October 06, 2003 3:40 PM* To:* David Wagoner* Subject:* Trial Software for Oracle environment /# *Do something about it.* *_Download_* http://www.veritas.com/offer?a_id=3851* a free trial of VERITAS Indepth(tm) for Oracle.* Easier said than done. Usually it's difficult, if not impossible, to pinpoint the root cause of performance slowdowns. Countless hours are spent troubleshooting and analyzing applications with few results to show for it. *That's about to change. *With VERITAS Indepth for Oracle, you can identify specific application bottlenecks, resolve them faster, and maintain promised service levels to users. Download VERITAS Indepth for Oracle to see how you can: * *Monitor* the Oracle environment continuously and capture performance data for current, short term, and long-term performance analysis. * *Drill down and identify* a performance problem caused by a resource bottleneck or a poorly written SQL statement. * *Resolve performance problems* faster with detailed steps and displays statistics relevant to each step in the Oracle access path. *_Download Now_* http://www.veritas.com/offer?a_id=3851 Why we contacted you and how to opt-out: We know your time is valuable and that we (and others) are placing increasing demands on it. We contacted you about this news because we believe that the content of this message would be interesting and valuable to you. If you do not wish to receive future VERITAS notifications, please click on the link below, and send us the e-mail: _mailto:[EMAIL PROTECTED] Please review our online _Privacy Policy_ http://www.veritas.com/privacypolicy/PolicyHome.jhtml and _Terms of Use_ http://www.veritas.com/privacypolicy/TermsOfUseHome.jhtml. © Copyright 2003 VERITAS Software. All rights reserved. VERITAS Software, 350 Ellis Street, Mountain View, CA 94043, United States. We welcome your comments. Send email to [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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).
CBO Puzzles
All We have a 8.0.4 database using choose optimizer. We have a query that access a single table using index. If we analyze the table and indexes - query takes 8 min to complete If we analyze table and then delete stats on indexes - query runs in two minutes. Explain plan is the same in both cases. I did a event 10053 trace but could not find any difference between the choices the CBO makes except when the indexes are not analyzed it thinks the cost is low. Any ideas why the query would execute faster - with the same explain plan - if the CBO thinks its cost is low ? PS : We ruled out the effects of caching in the buffer by reversing the order of the tests.. TIA Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle-L 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: Find an unprintable character inside a column....
[EMAIL PROTECTED] wrote: I played with this a bit. First, I created some test data with one column corrupted with a single random character of 0-31 replacing a random char in that column 20% of the rows of the table. Peter's function correctly found all of the rows in 7.5 seconds. Stephane's function ran in 3.5 seconds, but didn't find any of the rows. I didn't attempt to correct the code. Then I tried a function based on owa_pattern.regex. My initial attempts didn't return the correct rows, as the regex pattern needed some tuning. I didn't attempt to fix it, as it was woefully slow, about 30 seconds. Regex in the WHERE clause in 10g will be nice. Jared Stephane Faroult [EMAIL PROTECTED] To:Multiple Sent by: [EMAIL PROTECTED] recipients of list ORACLE-L [EMAIL PROTECTED] 10/10/2003 07:09 AM cc: Please respond to ORACLE-LSubject:RE: RE: RE: Find an unprintable character inside a column Some people have requested this code, so I thought you might as well all have the chance to pick it to bits... Its a function called BAD_ASCII, and it hunts out for any ascii characters with an ascii value of less than 32 in a specified field. (Acknowledgments to my colleague Keith Holmes for help with this code.) Use it as follows: Where a field called DATA in a table TABLE_1 may contain an ascci character with a value less than 32 (ie a non-printing character), the following SQL will find the row in question: select rowid,DATA,dump(DATA) from TABLE_1 where BAD_ASCII(DATA) 0; You could use the PK of the table instead of rowid, of course. You will also note that I select the DATA field in both normal and ascii 'dump' mode, the better to locate where the corruption is located. peter edinburgh ... Source as follows: Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin -- V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; -- exception when others then return -1; end BAD_ASCII; / Peter, I think that you can make this code 25% faster when the data is clean (which hopefully is the general case) by using 'replace', more efficient than a PL/SQL loop, to check whether you have some rubbish (sort of). It will not tell you where the bad character is, however - which means that then you can loop to look for it. Here is what I would suggest : create or replace Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin if (replace(V_text, chr(0)||chr(1)||chr(2)||chr(3)|| chr(4)||chr(5)||chr(6)||chr(7)|| chr(8)||chr(9)||chr(10)||chr(11)|| chr(12)||chr(13)||chr(14)||chr(15)|| chr(16)||chr(17)||chr(18)||chr(19)|| chr(20)||chr(21)||chr(22)||chr(23)|| chr(24)||chr(25)||chr(26)||chr(27)|| chr(28)||chr(29)||chr(30)||chr(31), '') = V_text) then return 0; else V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; end if; -- exception when others then return -1; end BAD_ASCII; / Jared, you're the scourge of people who just write things out of the top of their head and don't test them thoroughly :-). I had made my usual mistake of using REPLACE instead of TRANSLATE. Just tried it with 'regular' data, since this is the only case where it can be faster that Peter's routine. Works like Peter's routine with TRANSLATE, only somewhat faster. Ooops again. -- 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: Find an unprintable character inside a column....
DROP TABLE table_1;
CREATE TABLE table_1(data VARCHAR2(10));
INSERT INTO table_1 VALUES(CHR(1)||'ABC');
INSERT INTO table_1 VALUES('ABC'||CHR(25));
INSERT INTO table_1 VALUES(CHR(25)||'@'||CHR(30));
INSERT INTO table_1 VALUES(CHR(25)||'@'||CHR(31));
INSERT INTO table_1 VALUES('ABC');
COMMIT;
VARIABLE npc VARCHAR2(33);
VARIABLE np0 VARCHAR2(33);
VARIABLE np VARCHAR2(33);
BEGIN
:npc := '';
:np0 := '';
:np := '';
FOR i IN 0 .. 31
LOOP
:npc := :npc || CHR(i);
:np0 := :np0 || CHR(0);
END LOOP;
:np := '@' || :npc;
END;
/
COLUMN data FORMAT A10
COLUMN dump FORMAT A30
SELECT ROWID
, data
, DUMP(data) dump
, LENGTH(data) - LENGTH(TRANSLATE(data, :np, '@')) numer_of_np_chars
, INSTR(TRANSLATE(data, :npc, :np0), CHR(0)) first_position
FROM table_1
WHERE TRANSLATE(data, :np, '@') data
/
It's not for unicode. FBI could be used as well.
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
Robson, Peter wrote:
Some people have requested this code, so I thought you might as well all
have the chance to pick it to bits... Its a function called BAD_ASCII, and
it hunts out for any ascii characters with an ascii value of less than 32 in
a specified field. (Acknowledgments to my colleague Keith Holmes for help
with this code.)
Use it as follows:
Where a field called DATA in a table TABLE_1 may contain an ascci character
with a value less than 32 (ie a non-printing character), the following SQL
will find the row in question:
select rowid,DATA,dump(DATA) from TABLE_1
where BAD_ASCII(DATA) 0;
You could use the PK of the table instead of rowid, of course. You will also
note that I select the DATA field in both normal and ascii 'dump' mode, the
better to locate where the corruption is located.
peter
edinburgh
--
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).
Re: Find an unprintable character inside a column....
See notes, 113827.1, 119426.1, 154880.1. Could be done and done, but not to solve this particular task -- it would be an overkill. :) -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. [EMAIL PROTECTED] wrote: Definitely worth trying if you have a need for it. I don't, and it's more work than I want to do just because I can. *Mladen Gogala* Actually, I was toying with the idea of writing an external procedure that would allow me to call pcre library (PCRE=Perl Compatible Regular Expressions) which would be nice, but then again, the whole perl is available through the set of external procedures, so it wouldn't be very useful. External procedures can be used in the where clause, provided they're declared as deterministic. Actually, it wouldn't be that hard to extend 9.2 database with regular expressions. -- 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).
Re: Find an unprintable character inside a column....
Always glad to be of service. It works with translate(), about 53% faster. Stephane Faroult [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/10/2003 02:54 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Find an unprintable character inside a column [EMAIL PROTECTED] wrote: I played with this a bit. First, I created some test data with one column corrupted with a single random character of 0-31 replacing a random char in that column 20% of the rows of the table. Peter's function correctly found all of the rows in 7.5 seconds. Stephane's function ran in 3.5 seconds, but didn't find any of the rows. I didn't attempt to correct the code. Then I tried a function based on owa_pattern.regex. My initial attempts didn't return the correct rows, as the regex pattern needed some tuning. I didn't attempt to fix it, as it was woefully slow, about 30 seconds. Regex in the WHERE clause in 10g will be nice. Jared Stephane Faroult [EMAIL PROTECTED] To:Multiple Sent by: [EMAIL PROTECTED] recipients of list ORACLE-L [EMAIL PROTECTED] 10/10/2003 07:09 AMcc: Please respond to ORACLE-LSubject:RE: RE: RE: Find an unprintable character inside a column Some people have requested this code, so I thought you might as well all have the chance to pick it to bits... Its a function called BAD_ASCII, and it hunts out for any ascii characters with an ascii value of less than 32 in a specified field. (Acknowledgments to my colleague Keith Holmes for help with this code.) Use it as follows: Where a field called DATA in a table TABLE_1 may contain an ascci character with a value less than 32 (ie a non-printing character), the following SQL will find the row in question: select rowid,DATA,dump(DATA) from TABLE_1 where BAD_ASCII(DATA) 0; You could use the PK of the table instead of rowid, of course. You will also note that I select the DATA field in both normal and ascii 'dump' mode, the better to locate where the corruption is located. peter edinburgh ... Source as follows: Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin -- V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; -- exception when others then return -1; end BAD_ASCII; / Peter, I think that you can make this code 25% faster when the data is clean (which hopefully is the general case) by using 'replace', more efficient than a PL/SQL loop, to check whether you have some rubbish (sort of). It will not tell you where the bad character is, however - which means that then you can loop to look for it. Here is what I would suggest : create or replace Function BAD_ASCII (V_Text in char) return number is V_Int number; V_Count number; begin if (replace(V_text, chr(0)||chr(1)||chr(2)||chr(3)|| chr(4)||chr(5)||chr(6)||chr(7)|| chr(8)||chr(9)||chr(10)||chr(11)|| chr(12)||chr(13)||chr(14)||chr(15)|| chr(16)||chr(17)||chr(18)||chr(19)|| chr(20)||chr(21)||chr(22)||chr(23)|| chr(24)||chr(25)||chr(26)||chr(27)|| chr(28)||chr(29)||chr(30)||chr(31), '') = V_text) then return 0; else V_Int := 0; V_Count := 1; while V_Count=length(rtrim(V_Text)) and V_Int=0 loop if ascii(substr(V_Text, V_Count, 1))32 then V_Int := V_Count; end if; V_Count := V_Count + 1; end loop; return V_Int; end if; -- exception when others then return -1; end BAD_ASCII; / Jared, you're the scourge of people who just write things out of the top of their head and don't test them thoroughly :-). I had made my usual mistake of using REPLACE instead of TRANSLATE. Just tried it with 'regular' data, since this is the only case where it can be faster that Peter's routine. Works like Peter's routine with TRANSLATE, only somewhat faster.
Re: FW: Oracle Performance Software from Veritas
and how WAS the musical? jgps, mp3 files please --- Mogens_Nørgaard [EMAIL PROTECTED] wrote: May I just add that his real name is Gaja Vahatneyhatneyhatney. That is what I called him in BAARF. The Musical.. [EMAIL PROTECTED] wrote: Our good friend Gaja Vaidyanatha is now with Veritas, so this isn't really too surprising. :) Jared *David Wagoner [EMAIL PROTECTED]* Sent by: [EMAIL PROTECTED] 10/06/2003 01:59 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:FW: Oracle Performance Software from Veritas Just got this email from Veritas...apparently they are getting into the database performance business for Oracle (and SQL Server too I think). Best regards, *David B. Wagoner* Database Administrator Arsenal Digital Solutions the most trusted source for STORAGE MANAGEMENT SERVICES The contents of this e-mail message may be privileged and/or confidential. If you are not the intended recipient, any review, dissemination, copying, distribution or other use of the contents of this message or any attachment by you is strictly prohibited. If you receive this communication in error, please notify us immediately by return e-mail or by telephone (919-466-6700), and please delete this message and all attachments from your system. Thank you. -Original Message-* From:* VERITAS Software [mailto:[EMAIL PROTECTED] Sent:* Monday, October 06, 2003 3:40 PM* To:* David Wagoner* Subject:* Trial Software for Oracle environment /# *Do something about it.* *_Download_* http://www.veritas.com/offer?a_id=3851* a free trial of VERITAS Indepth(tm) for Oracle.* Easier said than done. Usually it's difficult, if not impossible, to pinpoint the root cause of performance slowdowns. Countless hours are spent troubleshooting and analyzing applications with few results to show for it. *That's about to change. *With VERITAS Indepth for Oracle, you can identify specific application bottlenecks, resolve them faster, and maintain promised service levels to users. Download VERITAS Indepth for Oracle to see how you can: * *Monitor* the Oracle environment continuously and capture performance data for current, short term, and long-term performance analysis. * *Drill down and identify* a performance problem caused by a resource bottleneck or a poorly written SQL statement. * *Resolve performance problems* faster with detailed steps and displays statistics relevant to each step in the Oracle access path. *_Download Now_* http://www.veritas.com/offer?a_id=3851 Why we contacted you and how to opt-out: We know your time is valuable and that we (and others) are placing increasing demands on it. We contacted you about this news because we believe that the content of this message would be interesting and valuable to you. If you do not wish to receive future VERITAS notifications, please click on the link below, and send us the e-mail: _mailto:[EMAIL PROTECTED] Please review our online _Privacy Policy_ http://www.veritas.com/privacypolicy/PolicyHome.jhtml and _Terms of Use_ http://www.veritas.com/privacypolicy/TermsOfUseHome.jhtml. © Copyright 2003 VERITAS Software. All rights reserved. VERITAS Software, 350 Ellis Street, Mountain View, CA 94043, United States. We welcome your comments. Send email to [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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
Re: CBO Puzzles
Until I have seen the 10053 traces I do not believe that the access plans are identical. Most of the time if the performance of a query changes when index statistics are deleted it is because the default statistics, especially clustering factor is drastically different than the real statistics. I have not worked with a 8.0 database in 4 years and can't remember if the version emits STAT entries to the trace with a 10046 trace. That would be the ultimate word on what access path was used. At 03:44 PM 10/10/2003, you wrote: All We have a 8.0.4 database using choose optimizer. We have a query that access a single table using index. If we analyze the table and indexes - query takes 8 min to complete If we analyze table and then delete stats on indexes - query runs in two minutes. Explain plan is the same in both cases. I did a event 10053 trace but could not find any difference between the choices the CBO makes except when the indexes are not analyzed it thinks the cost is low. Any ideas why the query would execute faster - with the same explain plan - if the CBO thinks its cost is low ? PS : We ruled out the effects of caching in the buffer by reversing the order of the tests.. TIA Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle-L 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 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).
