Re: ioug-a question
This is from grandpa's memory: The wait interface (v$system_event, v$session_event, v$session_wait) were introduced in 7.0.12. So if my memory works correctly at this early hour, that was 1992. In 1995, I wrote the Oracle7 wait events and enqueue paper, after Jeff Needham explained a couple of events in the 7.1/7.2 performance and tuning guide. I saw that and decide that we need to do them all. In 1996 I got involved in tuning one of the largest Oracle installations in the world and had to convince the developers that they were doing the wrong thing. So I came up with YAPP (actually even to day that site is still using the initial scripts that we developed for them based on YAPP). I also started to give talks about this way of tuning, I gave talks to support and consultant goups (1997 and later). Then some one in support decided that a white paper was needed and that became the YAPP white paper, published 1998. So a couple of dates and names: 1992 - Juan Loaiza (designed the wait interface) 1994 - Jeff Needham (documented 4/5 events in the performance and tuning guide of 7.1/7.2) 1995/1996 - Oracle7 wait events and enqueue papers (Anjo Kolk) 1996 - YAPP developed onsite at one of the largest OLTP OPS sites in the world 1997/1998 - people insite of Oracle are getting exposed to the wait interface tuning (Mogens Norgaard, Cary Millsap, Shari Yamaguchi) 1998 - white paper was published 1998 - oraperf website 2000 - other companies start to work with the wait interface (Hotsos/Miracle) 2001 - a lot of books are published with wait interface / YAPP methodology 2003 - Richard Niemic rewrites his book and throws out the buffer cache hit ratio and introduces the wait interface, like it was invented yesterday ;-) A couple of things that I have noted over the years: 1) DBAs want tricks (what parameter should I set) to solve performance problems, not a methodology to solve performance problems (learn a man how to fish instead of feeding him) 2) response time tuning is not new, the problem is that most tools out there don't use it and most books don't mention it. It is a complete paradigm shift for most DBA. 3) YAPP/response time tuning is not perfect (by any means), but it is so much better than hit ratio and short list tuning. 4) Response time tuning will become more important as there are now products coming out that do end-to-end response time tuning (actually there already a couple). Watch this space, as things will evolve over the next couple of months/years ! Anjo. Jared Still wrote: Nice post. The 'revolution' is indeed not that new, more of an underground guerilla movement. And it wasn't televised. :) Jared On Sunday 21 April 2002 10:33, Don Granaman wrote: There seems to be a lot of interest in the tuning revolution here, so... The basics revolve around the views v$system_event, v$session_event, and v$session_wait, and v$event_name - and the 10046 event. Rather than try to paraphrase/summarize/expound upon the details, here are some of the best sites for researching the topic. (I'm sure there are other excellent issites, but these are the ones I know about that most certainly qualify.) Historical note: A year ago at the IOUG-A conference, this was considered revolutionary. This year it was the most widely presented and discussed topic at the conference. In fact, the technique has been around for a long time, it just wasn't widely known or accepted. My initiation to it was after an Oracle consultant came out and left something called APS7 installed on an Oracle7 system - in 1997! (Aside: Did APS8 ever exist?). APS7 was written by Milsap's group at Oracle and some of it uses wait-based tuning techniques. Poking around and looking at the scripts opened the door to profound revelation. Motivational scenario: Cast: DBA (you) and PHB (Pointy-haired boss) PHB: This tuning report (or GUI tool) shows the cache-hit ratio as too low. You should tune the database DBA: That is to be expected. Batch manipulated 100 GB of data last night. Cache-hit ratio is a meaningless metric anyway. PHB: But this book says it should be 95%. Besides, we are on the English system - we don't use metric! www.oraperf.com - Anjo Kolk Anjo Kolk's YAPP paper (a pioneering work on the topic). Consider it as prerequisite background reading and the departure point for your journey. www.hotsos.com - Cary Milsap Requires (free) registration. Click on Knowledge On-line. There are acres of papers here that are at the core of modern tuning techniques, including Oracle Kernel Event Documentation Index, Oracle System Performance Analysis Using Event 10046, Why 99% Database Buffer Cache Hit Ratio is NOT OK, Performance Management Myths and Facts, Why You Shoud Focus on LIOs Instead of PIOs, ad infinitum... www.orapub.com - Craig Shallahamer Requires (free) registration. Click on Technical Papers at the top . Related items
AW: Free Open source PL/SQL Editors / DB management tools
Hi TOra ist one of the best tools available. It's kind of a mixture of toad and navigator. Offers PL/SQL editor / debugger and a lot of server info stuff. URL: www.globecom.net/tora regards, Stefan -Ursprüngliche Nachricht- Von: Joe Raube [mailto:[EMAIL PROTECTED]] Gesendet: Montag, 22. April 2002 02:33 An: Multiple recipients of list ORACLE-L Betreff: Re: Free Open source PL/SQL Editors / DB management tools go to freshmeat.net and do a search. Also check out Oracletool and Orasnap. -Joe --- Andrey Bronfin [EMAIL PROTECTED] wrote: Dear list! are U aware of any free open source PL/SQL Editors or freeware/open source tools for database management ? If so , would u please post an URL of a such . Thanks a lot . Andre. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ioug-a question
2001 - a lot of books are published with wait interface / YAPP methodology Tuning 101 gets a lot of play here, and they devote a chapter to it. Other than that, what books cover waits in a significant way? Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
AW: data cleansing question
Hi I've got a question regarding your migration host - oracle, since I'll have the same problem in the near future. How do you deal with the EBCDIC to ASCII problem ? Do you migrate from VSAM or DB2 ? Which version of Oracle are you on ? Regards, Stefan -Ursprüngliche Nachricht- Von: Koivu, Lisa [mailto:[EMAIL PROTECTED]] Gesendet: Sonntag, 21. April 2002 12:48 An: Multiple recipients of list ORACLE-L Betreff: RE: data cleansing question Hi Yechiel, I'm aware of the NULLIF clause. However my strategy has been to bring the entire file in, character based, so I can take a look at the data prior to modifying it. I'm using NULLIF column_name is equal to spaces. I made an even better decision - the users decided they didn't want all these fields, so suddenly my loads scream and my table is much smaller. whew It would be nice if I knew exactly what the SQL against the table would be. Unfortunately the users don't really know either. (Say it over and over again: I love my job, I love my job...) Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. Ft. Lauderdale, FL, USA -Original Message- From: Yechiel Adar [SMTP:[EMAIL PROTECTED]] Sent: Sunday, April 21, 2002 5:33 AM To: Multiple recipients of list ORACLE-L Subject: Re: data cleansing question Hello There are a few considerations: 1) If you import the data using the loader you can use the NULLIF clause so it will import all zero field as null. 2) Check carefully for usage of the fields in the WHERE part of the SQL. Putting nulls instead of zero can cause the users to do outer joins. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 18, 2002 12:43 AM For those of you who have had to deal with data cleansing - I am working on importing mainframe data into Oracle. This unglamorous job involves validating (and sometimes compensating for) what is bad data in Oracle's eyes but not on the mainframe - crazy crap like a date = 22/22/2022. I also find that the mainframe programs are padding null fields with 0's. I wonder how many of you take the route of removing the zeros and storing null in that field? Some of the important numeric fields I think I'll leave that way (past_due_amt, etc.) but several others in this 218-field table are full of zeroes. My gut feel is to null out the insignificant 0'd out fields - that I have pushed to the bottom of the table - to not only save space, but for data integrity. 0 in 75 fields means nothing to me or to anyone else, as far as I can tell. Thoughts? Thanks everyone Lisa Koivu Oracle Database Hormone Dispenser. Fairfield Resorts, Inc. 954-935-4117 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego,
RE: ora-4031- Advice on what to look at next time
- Original Message - From: Reardon, Bruce (CALBBAY) [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sun, 21 Apr 2002 22:23:18 Our production instance started getting ora-4031 errors around 6pm on Frida= y 19th.=0D I was called by our users around 9am on Sunday 21st and problem persisted a= nd I decided to restart the instance around 11:30am Sunday 21st.=0D =0D What I would like is some advice on what other information I should collect= next and advice on any settings that may need altering.=0D Bruce, You mention that you have all of your application objects (I presume you mean 'packages') and all Oracle standard stuff kept in shared memory. AFAIK it's precisely by pinning packages in memory that you avoid the problem you have. However, pinning EVERYTHING is perhaps a bit too much - you must pin only the biggest chunks to limit fragmentation. If I were you, what I'd check first would probably be V$ROWCACHE to check what is anchored and what is following the ebb, try to spot what causes the problem (what is reloaded most often?) and either pin it too or possibly unkeep some not-so-necessary stuff to get some breathing space. Stephane Faroult Oriole Corporation Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: System applying grants
The old hack is via definers rights procedures (as SYS etc) create or replace procedure THE_OWNER.do_sql(m varchar2) is begin execute immediate m; end; exec THE_OWNER.do_sql('grant ...'); drop procedure THE_OWNER.do_sql hth connor --- Jared Still [EMAIL PROTECTED] wrote: That won't give SYS the privileges you think it does. For any user, including SYS, to be allowed to grant direct privileges on another users objects, that user must have explicity direct grants with the 'with grant option'. e.g. to allow SYS to grant privileges on the table EMP owned by SCOTT, you must log in as SCOTT and issue this command: GRANT ALL ON EMP TO SYS; The user SYS will now be able to grant privilege on SCOTT.EMP to other users. Jared On Friday 19 April 2002 19:23, [EMAIL PROTECTED] wrote: Hi gurus, I created a script to assign some grants automatically to some roles and users. As the user sys, I assigned to user system the GRANT ANY PRIVILIGE, GRANT ANY ROLE, roles and when run the script get the error of insufficient privileges. What I dont want is to connect to each user to apply the grants. How can achieve that ? TIA Ramon -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ioug-a question
Yes, I forgot to mention Gaja's book, and there is a book out there Oracle DBA 101, that has a complete section (2nd or 3rd) about tuning by wait interface/YAPP. Anjo. Greg Moore wrote: 2001 - a lot of books are published with wait interface / YAPP methodology Tuning 101 gets a lot of play here, and they devote a chapter to it. Other than that, what books cover waits in a significant way? Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-03113
3113 is a tough beast to resolve. We have some java processes that feed out spores to our clients. Recently at about 5:30pm and 6pm they all started throwing 3113 errors (with corresponding Exception 11 dump files on the server bdumps and cores too). Mind it well, the server trace file just tells me that it is Exception 11 , no other error is mentioned in the trace file. What we found that flushing the shared pool helped, and it isn't that the shared pool is insufficient, it is about 700M, and usually the system never exceeds 60%. We have plans to scale it down though ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 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. *2
Anything new from IOUG?
Hi All that recently attended IOUG. If you don't already know - I sell tools for Oracle. (delete this now if you want to DG! ;P) I was just wondering if anybody at IOUG had any feedback on any new tools that were launched, or any tools that made a significant impact at IOUG? This is purely for vendor awareness for myself, as I like to keep up to date on anything new in and around our particular market place.. If anybody saw something and thought wow!, I'd be interested in hearing about it. If you would like to contact me directly about this - please feel free, though I feel the list *could* also benefit from this.. Cheers Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: executing a stored procedure at a certain time
Title: executing a stored procedure at a certain time I do it in this way .. Firstly , dbms_job.submit giving the interval .. then dbms_job.next_date... Bunyamin K. Karadeniz Oracle DBA / DeveloperCivilian IT DepartmentHavelsan A.S. Eskisehir yolu 7.km Ankara TurkeyPhone: +90 312 2873565 / 1217Mobile : +90 535 3357729 The degree of normality in a database is inversely proportional to that of its DBA. - Original Message - From: Igor Neyman To: Multiple recipients of list ORACLE-L Sent: Friday, April 19, 2002 11:13 PM Subject: Re: executing a stored procedure at a certain time Read on DBMS_JOB oracle-supplied package in docs. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Nelson Flores To: Multiple recipients of list ORACLE-L Sent: Friday, April 19, 2002 3:53 PM Subject: executing a stored procedure at a certain time Hello everyone, i was wondering if someone could tell me how set up an oracle internal mechanism that allows me to execute an oracle stored proc. at a certain time every day. thanks. Atte. Nelson Flores P. Intec Project Manager [EMAIL PROTECTED] --- Centro de tecnologías de Información http://cti.intec.cl Corporación de investigación Tecnológica - Intec http://www.intec.cl ---
RE: refcursor rowcount check
-Madhu How about the following: create or replace PROCEDURE Get_Emp_Rows (EmpCur IN OUT GenPack.GenCurTyp, Nstr Varchar2) IS cname Emp.Name%type; rec_count number; -- == I added this BEGIN select count(*) into rec_count -- == I added these FROM Emp where name = Nstr; -- OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; -- FETCH EmpCur into cname; --DBMS_OUTPUT.PUT_LINE(cname); --displays first row for test IF rec_count = 0 then --- EmpCur%rowcount=0 then-- I changed this OPEN EmpCur FOR SELECT 'W001' from dual; ELSE OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; End If; END Get_Emp_Rows; Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Saturday, April 20, 2002 3:18 AM To: Multiple recipients of list ORACLE-L List, I'm having a small problem while checking row count parameter in a refcursor. A stored procedure accepts parameters and returns refcursors; if no candidate rows are found, then an error code is returned to the calling program. The same cursor variable is used to retrun the rowset or error code. To check if any rows are returned, I use the ROWCOUNT attribute of the cursor variable. Rowcount is not available till I do the first fetch. However the fetch removes the first row from the recordset, in case any rows are present. The 'OUT' variable returned to the calling program has one row less than actual. How to prevent this? Is there any other better way to check if rows are present? Presently, I work around by opening the cursor again. But surely this won't hold out for bigger data sets and complex queries. create or replace PROCEDURE Get_Emp_Rows (EmpCur IN OUT GenPack.GenCurTyp, Nstr Varchar2) IS cname Emp.Name%type; BEGIN OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; FETCH EmpCur into cname; DBMS_OUTPUT.PUT_LINE(cname); --displays first row for test IF EmpCur%rowcount=0 then OPEN EmpCur FOR SELECT 'W001' from dual; ELSE OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; End If; END Get_Emp_Rows; Thanks for your time. regards -Madhu _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
White papers on industry trends
Title: White papers on industry trends Are there any white papers on industry trends for architecture including programming languages. I'm working on a project that is in the early stages of a redesign. The current application uses C code entirely including user interface. The client is sold on Oracle Forms/Reports but is reluctant to trash the C code and start from scratch. This is the first such redesign in 17 years. Does a 3 tier architecture using iAS minimize or eliminate the cost in performance of the external procedure call to the C program from a stored procedure? Any information will be helpful... Thank in advance, Val Valerie H. Webber Management Systems Designers, Inc Database Administrator [EMAIL PROTECTED] 704-566-5321
Execution plan optimizer Question
8.1.6 on NT The product that I support is still Rule based. I am planning to bring it over to Cost based in the near future. What I wanted to do was to set the init parameter to 'RULE' and then run statistics on the database. My boss says we can't do this because the execution plan was different for the set to rule and have statistics vs. set to choose and no statistics. I haven't heard this before - can anybody point me to the Fine Manual to confirm or refute this. Perhaps this is another of those fine urban legends? For the time being I am starting out with a small test database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Oracle 8i Post Migration - dba_free_space Output
Step 1 - Migration id Database from Oracle 7.3.4.5 to Oracle 8.1.7.2 . Step 2 - After Completion of Migration used execute dbms_space_admin.tablespace_migrate_to_local('Tablespace name'); to Convert the Dictionary Managed Tablespace to LOCALLY Managed Tablespace Step 3 Added a Datafile of 1000MB RESULT sysdba_free_space Shows 2 Rows of 496 MB instead of a Single Row of 1000MB NOTE - Creation of a Dummy Table with Initial Extent Size of 900MB in the Respective Datafile Succeeds Qs. When Adding a Datafile of 1000MB Should the Size Clause be Specified as size 1000M or size 1024M or size 1G ? Qs. Could there be a Bug with dba-free_space ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ioug-a question
[EMAIL PROTECTED] wrote: The paper is a condensed version of the book Oracle Performance Tuning 101 published by Osborne (ISBN 0-07-213145-4). This is an EXCELLENT book and worth every penny that it costs. actually i think it's worth more than it costs, but that's just because it made me look like a genus [and yes that is *eztreemly* hard to do.;-)] in front of a couple of vendors. also made them not too happy because i was able to show the problems were with their SQL.;-) -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. No people do so much harm as those who go about doing good. - Mandell Creighton (1843-1901) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: data cleansing question
Hi Stefan, The EBCDIC-ASCII conversion is handled on the mainframe for me. I am sorry I don't know much about the mainframe environment here, I want to say it's VSAM, there definately is no database. It is so old, it's the type of mainframe where everything is on TAPE. Oracle version: 8.1.7.3 on Windows 2000 sp2 HTH Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. Ft. Lauderdale, FL, USA -Original Message- From: Stefan Jahnke [SMTP:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 4:48 AM To: Multiple recipients of list ORACLE-L Subject: AW: data cleansing question Hi I've got a question regarding your migration host - oracle, since I'll have the same problem in the near future. How do you deal with the EBCDIC to ASCII problem ? Do you migrate from VSAM or DB2 ? Which version of Oracle are you on ? Regards, Stefan -Ursprüngliche Nachricht- Von: Koivu, Lisa [mailto:[EMAIL PROTECTED]] Gesendet: Sonntag, 21. April 2002 12:48 An: Multiple recipients of list ORACLE-L Betreff: RE: data cleansing question Hi Yechiel, I'm aware of the NULLIF clause. However my strategy has been to bring the entire file in, character based, so I can take a look at the data prior to modifying it. I'm using NULLIF column_name is equal to spaces. I made an even better decision - the users decided they didn't want all these fields, so suddenly my loads scream and my table is much smaller. whew It would be nice if I knew exactly what the SQL against the table would be. Unfortunately the users don't really know either. (Say it over and over again: I love my job, I love my job...) Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. Ft. Lauderdale, FL, USA -Original Message- From: Yechiel Adar [SMTP:[EMAIL PROTECTED]] Sent: Sunday, April 21, 2002 5:33 AM To: Multiple recipients of list ORACLE-L Subject:Re: data cleansing question Hello There are a few considerations: 1) If you import the data using the loader you can use the NULLIF clause so it will import all zero field as null. 2) Check carefully for usage of the fields in the WHERE part of the SQL. Putting nulls instead of zero can cause the users to do outer joins. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 18, 2002 12:43 AM For those of you who have had to deal with data cleansing - I am working on importing mainframe data into Oracle. This unglamorous job involves validating (and sometimes compensating for) what is bad data in Oracle's eyes but not on the mainframe - crazy crap like a date = 22/22/2022. I also find that the mainframe programs are padding null fields with 0's. I wonder how many of you take the route of removing the zeros and storing null in that field? Some of the important numeric fields I think I'll leave that way (past_due_amt, etc.) but several others in this 218-field table are full of zeroes. My gut feel is to null out the insignificant 0'd out fields - that I have pushed to the bottom of the table - to not only save space, but for data integrity. 0 in 75 fields means nothing to me or to anyone else, as far as I can tell. Thoughts? Thanks everyone Lisa Koivu Oracle Database Hormone Dispenser. Fairfield Resorts, Inc. 954-935-4117 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Koivu, Lisa
Re: ORA-03113
are you sure that you set ORACLE_SID appropriately? sqlplus /nolog connect sys/change_on_install@XXX as sysdba startup (nomount) replace XXX with your SID, typically ORCL. if this doesn't help, try: use O9i on RH7.2 twice, SunOS 5.8 twice, W2k twice- they all run fine. you need a workaround to get it started, esp the binutils must be downgraded for installation. (rpm -Uhv --nodeps --force binutils-2.10.0.18-1.i386.rpm from release RH7.0). after installation of oracle you can upgrade again. (rpm -Uhv binutils-2.11.90.0.8-9.i386.rpm) reinstall it. I happened to install it this weekend twice and had no problems- but I used kernel 2.4.7-10smp. br marc ltiu schrieb: Hello Guys, Linux 2.4.18 Pentium III both Oracle 9i and 8i used netca and netmgr to configure listener listener is configured and listening off port 1521 init.ora is ready and in under OracleHome/dbs in sqlplus, the command startup nomount gives me this error: ORA-03113 end-of-file on communication channel I've read the 9i ErrorCodes documentation, it explains what the problem could be but not a solution. Could someone on this list email be some suggestions on how to fix this problem. Even better if someone here has encountered this same problem and was able to fix it, could you email me the solution? Thanks. ltiu -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: ltiu INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Saving down time on Install
Hi All, Before we go through the tests I thought I might save myself some time: We are about to install Oracle 8.1.7 into a seperate Oracle Home on a machine already running 8.0.5 We will not be upgrading our databases yet, just creating new ones from scratch. Our thinking is that apart from rootpre.sh (loading new kernel extension) nothing interferes with the 8.0.5 database/install. So we feel that we should be able to install the new version while other databases are up and running (restarted after rootpre.sh is run). We are on AIX 4.3.3 Anybody done this/info??? TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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:White papers on industry trends
Valerie, C is still a very heavily used language, although for a complete application I'd probably want to use a C++ variant since they come with screen painter tools. Migrating from C to Forms/Reports would not be unthinkable, but don't underestimate the learning curve. As far as eliminating the external procedure call, if that's the way the application is written, then your stuck and no it will not be eliminated. What may then be more efficient is to take that C code break it up into what is database only and what is C only. Then re-code the application as PL/SQL (or stored Java if your so inclined) keeping in pure C only that which cannot be done otherwise. The industry trend I would have to say is headed towards thin clients and three tier applications which serve internal and external customers and Java. While I agree with the trend in many cases there are too many times that the trend does not really fit the needs. In many a case we end up just moving the bottle neck from one place to the other sometimes making it worse. Case in point is PeopleSoft. There is a panel in the stock room maintenance that updates several tables and rows. Now that is a two tier problem since a lot of data is moving from the server to the client, being processed, and then sent back. But we can id the bottleneck here in that the end user NEEDS a beefy PC. Now you move that into a three tier mess the bottle neck gets harder to find since data moves from the database to the app server, to the client, gets processed sent back to the apps server which does more processing, and then back to the database. Result, you still need the beefy PC on the client side, but you almost need a one to one setup on the app server as well. So then each client actually needs two beefy PC's to do the job in a reasonable manner. YUCK!! Who said thin client was easier cheaper? Must have been some ignorant sales droid at the app server vendor. I note that your address is with the IRS, has the idea of doing Ada cropped up yet? OH, bad joke, it's suppose to be the government's standard programing language as declared by Congress back in the 80's. Then, PL/SQL is the Son of Ada!! Dick Goulet Reply Separator Author: Webber Valerie H [EMAIL PROTECTED] Date: 4/22/2002 5:28 AM Are there any white papers on industry trends for architecture including programming languages. I'm working on a project that is in the early stages of a redesign. The current application uses C code entirely including user interface. The client is sold on Oracle Forms/Reports but is reluctant to trash the C code and start from scratch. This is the first such redesign in 17 years. Does a 3 tier architecture using iAS minimize or eliminate the cost in performance of the external procedure call to the C program from a stored procedure? Any information will be helpful... Thank in advance, Val Valerie H. Webber Management Systems Designers, Inc Database Administrator [EMAIL PROTECTED] 704-566-5321 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=ISO-8859-1 META NAME=Generator CONTENT=MS Exchange Server version 5.5.2653.12 TITLEWhite papers on industry trends/TITLE /HEAD BODY PFONT COLOR=#80 FACE=Comic Sans MSAre there any white papers on industry trends for architecture including programming languages. I'm working on a project that is in the early stages of a redesign. The current application uses C code entirely including user interface. The client is sold on Oracle Forms/Reports but is reluctant to trash the C code and start from scratch. This is the first such redesign in 17 years./FONT/P PFONT COLOR=#80 FACE=Comic Sans MSDoes a 3 tier architecture using iAS minimize or eliminate the cost in performance of the external procedure call to the C program from a stored procedure?/FONT/P PFONT COLOR=#80 FACE=Comic Sans MSAny information will be helpful.../FONT BRFONT COLOR=#80 FACE=Comic Sans MSThank in advance,/FONT BRFONT COLOR=#80 FACE=Comic Sans MSVal/FONT /P PBFONT COLOR=#800080 FACE=GeorgiaValerie H. Webber/FONT/BBR FONT COLOR=#800080 FACE=GeorgiaManagement Systems Designers, Inc/FONT BRFONT COLOR=#800080 FACE=GeorgiaDatabase AdministratorBR [EMAIL PROTECTED]BR 704-566-5321 /FONT /P BR /BODY /HTML -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
Re: Execution plan optimizer Question
If you set optimizer_mode to rule, it will be rule independent of stats unless sql's explicitly do something to invoke the cbo (eg with a hint etc). Your greatest risk would be any SQL's that have a CHOOSE hint in them. But its worth the risk - rule based is a dead end street. hth connor --- Shaw John-P55297 [EMAIL PROTECTED] wrote: 8.1.6 on NT The product that I support is still Rule based. I am planning to bring it over to Cost based in the near future. What I wanted to do was to set the init parameter to 'RULE' and then run statistics on the database. My boss says we can't do this because the execution plan was different for the set to rule and have statistics vs. set to choose and no statistics. I haven't heard this before - can anybody point me to the Fine Manual to confirm or refute this. Perhaps this is another of those fine urban legends? For the time being I am starting out with a small test database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Connor McDonald http://www.oracledba.co.uk (mirrored at http://www.oradba.freeserve.co.uk) Some days you're the pigeon, some days you're the statue __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Execution plan optimizer Question
In general, if you set init.ora to RULE and gather statistics, unless you have hints in your SQL, the statistics will be ignored. This may or may not be different than setting to CHOOSE and having no statistics - it depends on your SQL. -Joe --- Shaw John-P55297 [EMAIL PROTECTED] wrote: 8.1.6 on NT The product that I support is still Rule based. I am planning to bring it over to Cost based in the near future. What I wanted to do was to set the init parameter to 'RULE' and then run statistics on the database. My boss says we can't do this because the execution plan was different for the set to rule and have statistics vs. set to choose and no statistics. I haven't heard this before - can anybody point me to the Fine Manual to confirm or refute this. Perhaps this is another of those fine urban legends? For the time being I am starting out with a small test database. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaw John-P55297 INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Free Open source PL/SQL Editors / DB management tools
Hi are U aware of any free open source PL/SQL Editors or freeware/open source tools for database management ? If so , would u please post an URL of a such . Depends on what you want for Database management - if you need to document schemas or re-create them elsewhere the Free DBATool might be of interest to you. http://www.databee.com/dt_home.htm The DBATool is designed to read an Oracle export file and create executable DDL from it which can rebuild the database - its simple to use and quite useful for DBA's and developers. The DBATool can also strip off (or substitute) storage clauses and other DDL components and pull out as DDL all dependencies (indexes, FK's, grants etc) for a selected group of tables. One of the more popular features is the DBATools ability to dump the schema structure out to a linked HTML tree - instant documentation. An example can be seen at: http://www.databee.com/dt_htmltree/db=TESTDB1+sc=DT_TEST.htm Regards Dale Edgar Net 2000 Ltd. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dale Edgar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Currval and buffer gets
If you are using PL/sql then try to reference the sequence next value in the update/insert statement itself. Aso the update/insert can return the value of the sequence to a PL/SQL memory variable. regards, Waleed -Original Message- To: Multiple recipients of list ORACLE-L Sent: 4/21/02 1:23 PM I did two statspack snapshots, one hour and forty minutes apart. Then I generated a report and loaded it into oraperf.com. In the report I saw that the two SQL statements that where executed the most times where: Select .currval from dual; Select .nextval from dual;. Each one was executed about 90,000 times with 5 buffer gets per execution. The net result was about 950,000 buffer get for nextval and currval. My question is: Why should there be about 5 buffer gets per execution? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ioug-a question
There is always Steve Adams' Oracle8i Internal Services - for Waits, Latches, Locks and Memory from O'Reilly (undoubtably the all-time record holder for information density). It is best considered as Foundations for Advanced Tuning - as described on the cover. Chapter 2 is devoted to waits, but other sections also have wait-related information. I think the best advanced material is still in whitepapers at this point though. Aside: In the migration to wait-based tuning, don't forget that even waits alone don't cover everything. Service time is the other significant component. I like Craig Shallahamer's anallogy - in the grocery store, one waits in line, then gets serviced by the checkout clerk. Even if the wait is moderate, overall response time (grocery store checkout line enqueue to checkout completed) may be poor because of a slow clerk! Don Granaman [OraSaurus - Honk if you remember UFI!] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 22, 2002 3:18 AM 2001 - a lot of books are published with wait interface / YAPP methodology Tuning 101 gets a lot of play here, and they devote a chapter to it. Other than that, what books cover waits in a significant way? Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ioug-a question
Rather than a genus, how about a species ? Thank you, Paul Sherman DBA voice - 781-501-4143 (office) fax- 781-278-8341 (office) email - [EMAIL PROTECTED] -Original Message- Sent: Monday, April 22, 2002 10:08 AM To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wrote: The paper is a condensed version of the book Oracle Performance Tuning 101 published by Osborne (ISBN 0-07-213145-4). This is an EXCELLENT book and worth every penny that it costs. actually i think it's worth more than it costs, but that's just because it made me look like a genus [and yes that is *eztreemly* hard to do.;-)] in front of a couple of vendors. also made them not too happy because i was able to show the problems were with their SQL.;-) -- -- Bill Shrek Thater ORACLE DBA [EMAIL PROTECTED] You gotta program like you don't need the money, You gotta compile like you'll never get hurt, You gotta run like there's nobody watching, It's gotta come from the heart if you want it to work. No people do so much harm as those who go about doing good. - Mandell Creighton (1843-1901) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: bill thater INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Sherman, Paul R. INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
obsolete parameter in 9i
According to migration doc, JOB_QUEUE_INTERVAL is obsolete in 9i. So, does anyone know, how often each of SNP background processes wakes up (without this parameter being specified)? Igor Neyman, OCP DBA [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
[no subject]
Hi all! I need a solution about calling sql*loader from pl/sql. I have a version now with external dlls, but actually I don't know the platform so it not seems a good choice. I would like something native oracle solution with oracle's packages or something like that. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
SQL giving problem over the database link
Greetings, Here is the scenario. We are trying to insert records into a remote table via database link after selecting the data locally. This query hangs for ever: -- INSERT INTO [EMAIL PROTECTED] SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956' : This query returns only one row. SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (.) : This select returns 15 rows. If I replace the subquery as follows then it works great : -- INSERT INTO [EMAIL PROTECTED] SELECT * FROM FWLOT_PN2M WHERE fromid = '0e31.900fb406.37bc1803.3074.2026'; I tried to do the same insert locally and it works great : --- INSERT INTO fwlot_pn2m_gene SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); For some reason the combination of database link and the subquery in the select clause is going for full table scan for each occurence of formid. I am wondering if this is a bug or something against the rule. WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); Any explanation or help is appreciated. Thanks, Ashoke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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:
upgrade to Oracle 9i and use external tables. -Original Message- Sent: Monday, April 22, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Hi all! I need a solution about calling sql*loader from pl/sql. I have a version now with external dlls, but actually I don't know the platform so it not seems a good choice. I would like something native oracle solution with oracle's packages or something like that. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Free Open source PL/SQL Editors / DB management tools - THANX !
THANKS a lot to all who replied ! DBAndrey * 03-9254520 * 058-548133 * mailto:[EMAIL PROTECTED] -Original Message- Sent: Mon, April 22, 2002 5:33 PM To: Multiple recipients of list ORACLE-L Hi are U aware of any free open source PL/SQL Editors or freeware/open source tools for database management ? If so , would u please post an URL of a such . Depends on what you want for Database management - if you need to document schemas or re-create them elsewhere the Free DBATool might be of interest to you. http://www.databee.com/dt_home.htm The DBATool is designed to read an Oracle export file and create executable DDL from it which can rebuild the database - its simple to use and quite useful for DBA's and developers. The DBATool can also strip off (or substitute) storage clauses and other DDL components and pull out as DDL all dependencies (indexes, FK's, grants etc) for a selected group of tables. One of the more popular features is the DBATools ability to dump the schema structure out to a linked HTML tree - instant documentation. An example can be seen at: http://www.databee.com/dt_htmltree/db=TESTDB1+sc=DT_TEST.htm Regards Dale Edgar Net 2000 Ltd. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dale Edgar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Andrey Bronfin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: data cleansing question
Lisa, I'll assume the mainframe application is Cobol or some derivative. Are the original file descriptors available to you? If so they should hold some real clues whether it is a good idea to null out the fields that are all zeroes. If the field is really a number and meant to be used in a calculation, I'd generally leave it as a zero. On the other hand, if the field is described by a list of values for some sort of status, and zero is not listed as a valid code, then I'd null it in a heartbeat. For example, I would not null this field: * SERVICE QUANTITY PASSED BY CLIENT APPLICATION V1.00 * SHOULD BE MINUTES FOR ANESTHESIA V1.00 * 03 CLHC-DET-SERV-QTY PIC 9(4). On the other hand I would certainly null this one: * * OVERALL DISPOSITION OF A CLAIM RETURNED BY IMPULSE * 02 RT-DISPOSITION-CODE PIC 9. 88 RT-DISP-REPRICED VALUE '1'. 88 RT-DISP-NON-REPRICED VALUE '2'. 88 RT-DISP-PENDING VALUE '3'. 88 RT-DISP-ADJUSTED VALUE '4'. 88 RT-DISP-BACK-OUT VALUE '5'. 88 RT-DISP-RE-REPRICEVALUE '6'. 88 RT-DISP-PEND-TIMEOUT VALUE '7'. -rje LK The EBCDIC-ASCII conversion is handled on the mainframe for me. I am sorry LK I don't know much about the mainframe environment here, I want to say it's LK VSAM, there definately is no database. It is so old, it's the type of LK mainframe where everything is on TAPE. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Robert Eskridge INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: refcursor rowcount check
Tom, Thanks for taking time off to reply. I had wanted two things - To check rowcount (to enable returning a code for no-rows-found) and secondly, to avoid hitting the database more than once for the same kind of query. I hope you agree that your method also hits db twice. (I open the cursor twice, you do a count once and then open the cursor). This method would not help me scale for bigger data sets and more complex queries. I posted the same question to Thomas Kyte (asktom.oracle.com) and he advises to pass on 'No-rows-found' checking to the calling program. This would avoid any redundant db hits and help scalability. As of now I have decided to adopt this approach - * Perform validation of input parameter * Do a normal fetch of all candidate rows into a temporary table * check the temporary table for count. (this would be a comparitively smaller set) * return ref cursor with either error code or result set. This would allow me to (a) avoid redundant hits (b) adhere to the pre-agreed interface of passing either recordsets or business-rule-error-codes. Thanks again. Madhu From: Mercadante, Thomas F [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: refcursor rowcount check Date: Mon, 22 Apr 2002 05:03:22 -0800 -Madhu How about the following: create or replace PROCEDURE Get_Emp_Rows (EmpCur IN OUT GenPack.GenCurTyp, Nstr Varchar2) IS cname Emp.Name%type; rec_count number; -- == I added this BEGIN select count(*) into rec_count -- == I added these FROM Emp where name = Nstr; -- OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; -- FETCH EmpCur into cname; --DBMS_OUTPUT.PUT_LINE(cname); --displays first row for test IF rec_count = 0 then --- EmpCur%rowcount=0 then-- I changed this OPEN EmpCur FOR SELECT 'W001' from dual; ELSE OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; End If; END Get_Emp_Rows; Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Saturday, April 20, 2002 3:18 AM To: Multiple recipients of list ORACLE-L List, I'm having a small problem while checking row count parameter in a refcursor. A stored procedure accepts parameters and returns refcursors; if no candidate rows are found, then an error code is returned to the calling program. The same cursor variable is used to retrun the rowset or error code. To check if any rows are returned, I use the ROWCOUNT attribute of the cursor variable. Rowcount is not available till I do the first fetch. However the fetch removes the first row from the recordset, in case any rows are present. The 'OUT' variable returned to the calling program has one row less than actual. How to prevent this? Is there any other better way to check if rows are present? Presently, I work around by opening the cursor again. But surely this won't hold out for bigger data sets and complex queries. create or replace PROCEDURE Get_Emp_Rows (EmpCur IN OUT GenPack.GenCurTyp, Nstr Varchar2) IS cname Emp.Name%type; BEGIN OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; FETCH EmpCur into cname; DBMS_OUTPUT.PUT_LINE(cname); --displays first row for test IF EmpCur%rowcount=0 then OPEN EmpCur FOR SELECT 'W001' from dual; ELSE OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; End If; END Get_Emp_Rows; Thanks for your time. regards -Madhu _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL giving problem over the database link
Ashoke - Sympathy, but no firm answers here. I have seen this type of behavior before. Someone mentioned that the underlying problem is that the Oracle optimizer doesn't have enough information to make an intelligent decision in some database link situations. Sometimes I've given up in frustration and just pulled the entire table to the other side of the link. I haven't tried this, but I wonder if a hint might help its behavior. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 22, 2002 10:48 AM To: Multiple recipients of list ORACLE-L Greetings, Here is the scenario. We are trying to insert records into a remote table via database link after selecting the data locally. This query hangs for ever: -- INSERT INTO [EMAIL PROTECTED] SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956' : This query returns only one row. SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (.) : This select returns 15 rows. If I replace the subquery as follows then it works great : -- INSERT INTO [EMAIL PROTECTED] SELECT * FROM FWLOT_PN2M WHERE fromid = '0e31.900fb406.37bc1803.3074.2026'; I tried to do the same insert locally and it works great : --- INSERT INTO fwlot_pn2m_gene SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); For some reason the combination of database link and the subquery in the select clause is going for full table scan for each occurence of formid. I am wondering if this is a bug or something against the rule. WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); Any explanation or help is appreciated. Thanks, Ashoke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Saving down time on Install
I've done this on Solaris without any problems, including creating a new 8.1.7 database instance with an 8.0.6 instance running. The key is separate ORACLE_HOME's and being certain you're environment is set for the correct version. Jack van Zanen wrote: Hi All, Before we go through the tests I thought I might save myself some time: We are about to install Oracle 8.1.7 into a seperate Oracle Home on a machine already running 8.0.5 We will not be upgrading our databases yet, just creating new ones from scratch. Our thinking is that apart from rootpre.sh (loading new kernel extension) nothing interferes with the 8.0.5 database/install. So we feel that we should be able to install the new version while other databases are up and running (restarted after rootpre.sh is run). We are on AIX 4.3.3 Anybody done this/info??? TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Suzy Vordos INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Execution plan optimizer Question
There are a few conditions which make Oracle use cost based optimisation on a statement even when your system is set to RULE based, or even when you have a RULE hint. To date the list is (I think) limited to statements containing at least one of the following: An IOT A partitioned table The SAMPLE clause A parallel enabled table or index. ANSI outer joins (v9) If any of your queries contain these types of objects, then in the absence of any statistics Oracle will use some default values - so when you get proper values in place the paths may change (in theory for the better). Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 22 April 2002 14:39 |8.1.6 on NT |The product that I support is still Rule based. I am planning to bring it |over to Cost based in the near future. What I wanted to do was to set the |init parameter to 'RULE' and then run statistics on the database. My boss |says we can't do this because the execution plan was different for the set |to rule and have statistics vs. set to choose and no statistics. |I haven't heard this before - can anybody point me to the Fine Manual to |confirm or refute this. Perhaps this is another of those fine urban legends? |For the time being I am starting out with a small test database. |-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Currval and buffer gets
Thank all of you for the replies. Unfortunately the program is in c++. Gaja, I will forward your suggestion to the development team. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, April 21, 2002 8:28 PM Hi Yechiel, Any full-table-scan in Oracle 8i (or below) consumes 4 LIOs to the segment header. This number has reduced to 2 in 9i. Given that the 1 row that you are going after is in 1 data block, there is 1 LIO for the data block itself, given you a total of 5 LIOs. You can verify this by setting 10046 for the session and looking at the trace output. The workaround is to reference x$dual in your application. Alternatively, you can create a view on x$dual, create a synonym for it and then go from there. You will incur some I/O for the first access of the query (with the synonym), but subsequent accesses will incur 0 LIOs against x$dual. Cheers, Gaja --- Yechiel Adar [EMAIL PROTECTED] wrote: I did two statspack snapshots, one hour and forty minutes apart. Then I generated a report and loaded it into oraperf.com. In the report I saw that the two SQL statements that where executed the most times where: Select .currval from dual; Select .nextval from dual;. Each one was executed about 90,000 times with 5 buffer gets per execution. The net result was about 950,000 buffer get for nextval and currval. My question is: Why should there be about 5 buffer gets per execution? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ioug-a question
I don't think anyone who reads the original YAPP paper will miss this. If I recall correctly, one of it's opening statements is the classic formula: response time = service time plus wait time. My own mantra puts in rather less scientific terms: If you have a performance problem, either your database is working too hard, or it's not being allowed to work. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- | |Aside: In the migration to wait-based tuning, don't forget that even waits |alone don't cover everything. Service time is the other significant |component. | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: MySQL vs. Oracle database
SAP official history is at http://www.sapdb.org/history.htm Amusingly, they have blanked out what SAPDB was originally called. Of personal interest to me is the Cincom connection in that I worked with their software for much of the 80's... Kip Bryant |Um, no, not really. |SAPDB is Sybase, pure and simple. |Jared |On Thursday 18 April 2002 12:21, Gordon, Emery {PDBI~Palo Alto} wrote: | There is a new alternative. SAPDB is open source but supported by SAP. Whan | used outside of SAP applications it is free but charged when used inside of | SAP. The support is from a major software company and the features are much | closer to the Oracle feature set. | | Emery Gordon | | -Original Message- | Sent: Wednesday, April 17, 2002 12:32 PM | To: Multiple recipients of list ORACLE-L | | | | | -- Weaver, Walt [EMAIL PROTECTED] | | I don't think you're wrong. MySQL gets dissed frequently on this list, | but it's really a nice little product. IMHO it's much closer to Oracle | than Access. | | It works well for us. Doesn't scale like Oracle, but works well. | | In some ways it scales better than Oracle. For load+query | (a.k.a., warehouse) operations it can be faster than | Oracle because it doesn't get tangled up with rollbacks, | etc. On systms with many instances it also can be much | simpler to administer. | | -- | Steven Lembark 2930 W. Palmer | Workhorse Computing Chicago, IL 60647 | +1 800 762 1582 |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Jared Still | INET: [EMAIL PROTECTED] |Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 |San Diego, California-- Public Internet access / Mailing Lists | |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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL giving problem over the database link
Ashoke, Can you try using an in-line view like this: INSERT INTO [EMAIL PROTECTED] SELECT * FROM FW.FWLOT_PN2M,( select fw.fwlot.sysid sysid from fw.fwlot where fw.fwlot.appid = '205956') WHERE FW.FWLOT_PN2M.fromid = sysid; Havn't tried this, but it is worth a shot. good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 22, 2002 1:00 PM To: Multiple recipients of list ORACLE-L Ashoke - Sympathy, but no firm answers here. I have seen this type of behavior before. Someone mentioned that the underlying problem is that the Oracle optimizer doesn't have enough information to make an intelligent decision in some database link situations. Sometimes I've given up in frustration and just pulled the entire table to the other side of the link. I haven't tried this, but I wonder if a hint might help its behavior. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 22, 2002 10:48 AM To: Multiple recipients of list ORACLE-L Greetings, Here is the scenario. We are trying to insert records into a remote table via database link after selecting the data locally. This query hangs for ever: -- INSERT INTO [EMAIL PROTECTED] SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956' : This query returns only one row. SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (.) : This select returns 15 rows. If I replace the subquery as follows then it works great : -- INSERT INTO [EMAIL PROTECTED] SELECT * FROM FWLOT_PN2M WHERE fromid = '0e31.900fb406.37bc1803.3074.2026'; I tried to do the same insert locally and it works great : --- INSERT INTO fwlot_pn2m_gene SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); For some reason the combination of database link and the subquery in the select clause is going for full table scan for each occurence of formid. I am wondering if this is a bug or something against the rule. WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); Any explanation or help is appreciated. Thanks, Ashoke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: refcursor rowcount check
Madhu, I agree that the suggestion I proposed performs two queries. And I'm glad you have found a work-around (having your application do what it should do). I am guessing that my proposal would not cost very much to run. If you think about, the first query (select count(*)) would certainly use any indexes it could (and, as a by-product, they index segments would be sitting in the SGA). The second query would then re-use these same index segments. Since they have already been loaded in the SGA, their re-use would not cost all that much - unless of course, you are returning *millions* of rows. Most on-line applications do not return that much data to the screen, so I would guess that it would work just fine. Glad I could help in any (small) way. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 22, 2002 12:55 PM To: Multiple recipients of list ORACLE-L Tom, Thanks for taking time off to reply. I had wanted two things - To check rowcount (to enable returning a code for no-rows-found) and secondly, to avoid hitting the database more than once for the same kind of query. I hope you agree that your method also hits db twice. (I open the cursor twice, you do a count once and then open the cursor). This method would not help me scale for bigger data sets and more complex queries. I posted the same question to Thomas Kyte (asktom.oracle.com) and he advises to pass on 'No-rows-found' checking to the calling program. This would avoid any redundant db hits and help scalability. As of now I have decided to adopt this approach - * Perform validation of input parameter * Do a normal fetch of all candidate rows into a temporary table * check the temporary table for count. (this would be a comparitively smaller set) * return ref cursor with either error code or result set. This would allow me to (a) avoid redundant hits (b) adhere to the pre-agreed interface of passing either recordsets or business-rule-error-codes. Thanks again. Madhu From: Mercadante, Thomas F [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: refcursor rowcount check Date: Mon, 22 Apr 2002 05:03:22 -0800 -Madhu How about the following: create or replace PROCEDURE Get_Emp_Rows (EmpCur IN OUT GenPack.GenCurTyp, Nstr Varchar2) IS cname Emp.Name%type; rec_count number; -- == I added this BEGIN select count(*) into rec_count -- == I added these FROM Emp where name = Nstr; -- OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; -- FETCH EmpCur into cname; --DBMS_OUTPUT.PUT_LINE(cname); --displays first row for test IF rec_count = 0 then --- EmpCur%rowcount=0 then-- I changed this OPEN EmpCur FOR SELECT 'W001' from dual; ELSE OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; End If; END Get_Emp_Rows; Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Saturday, April 20, 2002 3:18 AM To: Multiple recipients of list ORACLE-L List, I'm having a small problem while checking row count parameter in a refcursor. A stored procedure accepts parameters and returns refcursors; if no candidate rows are found, then an error code is returned to the calling program. The same cursor variable is used to retrun the rowset or error code. To check if any rows are returned, I use the ROWCOUNT attribute of the cursor variable. Rowcount is not available till I do the first fetch. However the fetch removes the first row from the recordset, in case any rows are present. The 'OUT' variable returned to the calling program has one row less than actual. How to prevent this? Is there any other better way to check if rows are present? Presently, I work around by opening the cursor again. But surely this won't hold out for bigger data sets and complex queries. create or replace PROCEDURE Get_Emp_Rows (EmpCur IN OUT GenPack.GenCurTyp, Nstr Varchar2) IS cname Emp.Name%type; BEGIN OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; FETCH EmpCur into cname; DBMS_OUTPUT.PUT_LINE(cname); --displays first row for test IF EmpCur%rowcount=0 then OPEN EmpCur FOR SELECT 'W001' from dual; ELSE OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr; End If; END Get_Emp_Rows; Thanks for your time. regards -Madhu _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT
RE: White papers on industry trends
Title: RE: White papers on industry trends Dick, Thanks for the information. You make a good point about learning curve (which is a concern the client has too) and about breaking up the C code. Yes, I am a contractor with the IRS but this project was always written in C. Never in Ada.. thank goodness... Currently the system runs on an Informix database but will be converted to Oracle since it is the new IRS system of choice. (Good move) I was just concerned that keeping the C would be keeping a dinosaur in the backyard out of fear of a learning curve. We have the time to convert it and deal with the learning curve. The C code is pretty much spaghetti code after 17 years of band-aids and duct tape. It desperately needs to be reworked/redesigned not to mention adding Pro*C. We have a sister project that chucked all their C code and rewrote everything in Java. It was tough but the payoff was great. We may have to decide on a module-by-module basis. Ada... now there's a dinosaur if I ever heard of one... :) Val -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 10:33 AM To: Multiple recipients of list ORACLE-L Subject: Re:White papers on industry trends Valerie, C is still a very heavily used language, although for a complete application I'd probably want to use a C++ variant since they come with screen painter tools. Migrating from C to Forms/Reports would not be unthinkable, but don't underestimate the learning curve. As far as eliminating the external procedure call, if that's the way the application is written, then your stuck and no it will not be eliminated. What may then be more efficient is to take that C code break it up into what is database only and what is C only. Then re-code the application as PL/SQL (or stored Java if your so inclined) keeping in pure C only that which cannot be done otherwise. The industry trend I would have to say is headed towards thin clients and three tier applications which serve internal and external customers and Java. While I agree with the trend in many cases there are too many times that the trend does not really fit the needs. In many a case we end up just moving the bottle neck from one place to the other sometimes making it worse. Case in point is PeopleSoft. There is a panel in the stock room maintenance that updates several tables and rows. Now that is a two tier problem since a lot of data is moving from the server to the client, being processed, and then sent back. But we can id the bottleneck here in that the end user NEEDS a beefy PC. Now you move that into a three tier mess the bottle neck gets harder to find since data moves from the database to the app server, to the client, gets processed sent back to the apps server which does more processing, and then back to the database. Result, you still need the beefy PC on the client side, but you almost need a one to one setup on the app server as well. So then each client actually needs two beefy PC's to do the job in a reasonable manner. YUCK!! Who said thin client was easier cheaper? Must have been some ignorant sales droid at the app server vendor. I note that your address is with the IRS, has the idea of doing Ada cropped up yet? OH, bad joke, it's suppose to be the government's standard programing language as declared by Congress back in the 80's. Then, PL/SQL is the Son of Ada!! Dick Goulet Reply Separator Author: Webber Valerie H [EMAIL PROTECTED] Date: 4/22/2002 5:28 AM Are there any white papers on industry trends for architecture including programming languages. I'm working on a project that is in the early stages of a redesign. The current application uses C code entirely including user interface. The client is sold on Oracle Forms/Reports but is reluctant to trash the C code and start from scratch. This is the first such redesign in 17 years. Does a 3 tier architecture using iAS minimize or eliminate the cost in performance of the external procedure call to the C program from a stored procedure? Any information will be helpful... Thank in advance, Val Valerie H. Webber Management Systems Designers, Inc Database Administrator [EMAIL PROTECTED] 704-566-5321 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=ISO-8859-1 META NAME=Generator CONTENT=MS Exchange Server version 5.5.2653.12 TITLEWhite papers on industry trends/TITLE /HEAD BODY PFONT COLOR=#80 FACE=Comic Sans MSAre there any white papers on industry trends for architecture including programming languages. I'm working on a project that is in the early stages of a redesign. The current application uses C code entirely including user interface. The client is sold on Oracle Forms/Reports but is reluctant to trash the C code and start from scratch. This is the first such redesign in 17 years./FONT/P PFONT COLOR=#80 FACE=Comic Sans
Install oracle on redhat 7.2 on pentium IV
Hi, I am attempting to install oracle 8.1.7 on a pentium IV machine using red hat 7.2. I cannot get the oracle universal installer to start. I have discovered problems noted on metalink about pentium IV machines using windows. Does this problem also occur with linux? I have also found that red hat 7.2 is not certified with oracle 8.1.7, only 7.1. Are there any problems and/or workarounds to use 7.2? Thanks, Blake Wilson begin:vcard n:Wilson;Blake tel;fax:(519) 661-3486 tel;work:(519) 661-2111 ext 85549 x-mozilla-html:FALSE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] fn:Blake Wilson end:vcard
RE: SQL giving problem over the database link
How about this ... INSERT INTO [EMAIL PROTECTED] SELECT a.* FROM FW.FWLOT_PN2M a, fw.fwlot b WHERE a.fromid = b.sysid AND b.appid = '205956'; HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! ***1 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 ESPN at (860) 766-2000 and delete this e-mail message from your computer, Thank you. ***1
RE: ioug-a question
One needs to keep in mind that Waits are the symptoms, and not a problem. - Kirti -Original Message- Sent: Monday, April 22, 2002 12:13 PM To: Multiple recipients of list ORACLE-L I don't think anyone who reads the original YAPP paper will miss this. If I recall correctly, one of it's opening statements is the classic formula: response time = service time plus wait time. My own mantra puts in rather less scientific terms: If you have a performance problem, either your database is working too hard, or it's not being allowed to work. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- | |Aside: In the migration to wait-based tuning, don't forget that even waits |alone don't cover everything. Service time is the other significant |component. | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Currval and buffer gets
Hello Gaja I could not find x$dual. Did select on all_objects got zip. Oracle 8.1.6.3.4 on NT. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, April 21, 2002 8:28 PM Hi Yechiel, Any full-table-scan in Oracle 8i (or below) consumes 4 LIOs to the segment header. This number has reduced to 2 in 9i. Given that the 1 row that you are going after is in 1 data block, there is 1 LIO for the data block itself, given you a total of 5 LIOs. You can verify this by setting 10046 for the session and looking at the trace output. The workaround is to reference x$dual in your application. Alternatively, you can create a view on x$dual, create a synonym for it and then go from there. You will incur some I/O for the first access of the query (with the synonym), but subsequent accesses will incur 0 LIOs against x$dual. Cheers, Gaja --- Yechiel Adar [EMAIL PROTECTED] wrote: I did two statspack snapshots, one hour and forty minutes apart. Then I generated a report and loaded it into oraperf.com. In the report I saw that the two SQL statements that where executed the most times where: Select .currval from dual; Select .nextval from dual;. Each one was executed about 90,000 times with 5 buffer gets per execution. The net result was about 950,000 buffer get for nextval and currval. My question is: Why should there be about 5 buffer gets per execution? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: MySQL vs. Oracle database
- Original Message - SAP official history is at http://www.sapdb.org/history.htm Amusingly, they have blanked out what SAPDB was originally called. Of personal interest to me is the Cincom connection in that I worked with their software for much of the 80's... this is from the users' manual: Development of the independent SAP DB software was begun in 1997 on the basis of the ADABAS D software. also if u search for SAP DB History on google and get a cached copy of the same history page u'll see more details: Installed ADABAS D customer base gets serviced by SAP AG SAP AG resells ADABAS D under the name SAP DB Fork of the ADABAS D code base seems someone is ashamed to mention ADABAS... Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ioug-a question
Well absolutely true, but then everything the database does is a symptom of the fact that we throw workload at it ;-) Deshpande, Kirti wrote: One needs to keep in mind that Waits are the symptoms, and not a problem. - Kirti -Original Message- Sent: Monday, April 22, 2002 12:13 PM To: Multiple recipients of list ORACLE-L I don't think anyone who reads the original YAPP paper will miss this. If I recall correctly, one of it's opening statements is the classic formula: response time = service time plus wait time. My own mantra puts in rather less scientific terms: If you have a performance problem, either your database is working too hard, or it's not being allowed to work. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- | |Aside: In the migration to wait-based tuning, don't forget that even waits |alone don't cover everything. Service time is the other significant |component. | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Currval and buffer gets
returning_clause is supported in most languages. Also you might need to encapsulate some of your logic using stored procedures. Good luck. Waleed -Original Message- Sent: Monday, April 22, 2002 1:06 PM To: Multiple recipients of list ORACLE-L Thank all of you for the replies. Unfortunately the program is in c++. Gaja, I will forward your suggestion to the development team. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, April 21, 2002 8:28 PM Hi Yechiel, Any full-table-scan in Oracle 8i (or below) consumes 4 LIOs to the segment header. This number has reduced to 2 in 9i. Given that the 1 row that you are going after is in 1 data block, there is 1 LIO for the data block itself, given you a total of 5 LIOs. You can verify this by setting 10046 for the session and looking at the trace output. The workaround is to reference x$dual in your application. Alternatively, you can create a view on x$dual, create a synonym for it and then go from there. You will incur some I/O for the first access of the query (with the synonym), but subsequent accesses will incur 0 LIOs against x$dual. Cheers, Gaja --- Yechiel Adar [EMAIL PROTECTED] wrote: I did two statspack snapshots, one hour and forty minutes apart. Then I generated a report and loaded it into oraperf.com. In the report I saw that the two SQL statements that where executed the most times where: Select .currval from dual; Select .nextval from dual;. Each one was executed about 90,000 times with 5 buffer gets per execution. The net result was about 950,000 buffer get for nextval and currval. My question is: Why should there be about 5 buffer gets per execution? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re[2]: MySQL vs. Oracle database
Knowing what I do about SAP support I'd not want to get into a project with SAPDB! Those good German engineers would chew your head off when calling tech support. Whatever caused the error MUST be your fault! Dick Goulet Reply Separator Author: [EMAIL PROTECTED] Date: 4/22/2002 9:30 AM SAP official history is at http://www.sapdb.org/history.htm Amusingly, they have blanked out what SAPDB was originally called. Of personal interest to me is the Cincom connection in that I worked with their software for much of the 80's... Kip Bryant |Um, no, not really. |SAPDB is Sybase, pure and simple. |Jared |On Thursday 18 April 2002 12:21, Gordon, Emery {PDBI~Palo Alto} wrote: | There is a new alternative. SAPDB is open source but supported by SAP. Whan | used outside of SAP applications it is free but charged when used inside of | SAP. The support is from a major software company and the features are much | closer to the Oracle feature set. | | Emery Gordon | | -Original Message- | Sent: Wednesday, April 17, 2002 12:32 PM | To: Multiple recipients of list ORACLE-L | | | | | -- Weaver, Walt [EMAIL PROTECTED] | | I don't think you're wrong. MySQL gets dissed frequently on this list, | but it's really a nice little product. IMHO it's much closer to Oracle | than Access. | | It works well for us. Doesn't scale like Oracle, but works well. | | In some ways it scales better than Oracle. For load+query | (a.k.a., warehouse) operations it can be faster than | Oracle because it doesn't get tangled up with rollbacks, | etc. On systms with many instances it also can be much | simpler to administer. | | -- | Steven Lembark 2930 W. Palmer | Workhorse Computing Chicago, IL 60647 | +1 800 762 1582 |-- |Please see the official ORACLE-L FAQ: http://www.orafaq.com |-- |Author: Jared Still | INET: [EMAIL PROTECTED] |Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 |San Diego, California-- Public Internet access / Mailing Lists | |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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ioug-a question
Then it depends on the workload and its quality quantity ;=) because, the database does what it is asked to do ;) - Kirti -Original Message- Sent: Monday, April 22, 2002 1:04 PM To: Multiple recipients of list ORACLE-L Well absolutely true, but then everything the database does is a symptom of the fact that we throw workload at it ;-) Deshpande, Kirti wrote: One needs to keep in mind that Waits are the symptoms, and not a problem. - Kirti -Original Message- Sent: Monday, April 22, 2002 12:13 PM To: Multiple recipients of list ORACLE-L I don't think anyone who reads the original YAPP paper will miss this. If I recall correctly, one of it's opening statements is the classic formula: response time = service time plus wait time. My own mantra puts in rather less scientific terms: If you have a performance problem, either your database is working too hard, or it's not being allowed to work. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- | |Aside: In the migration to wait-based tuning, don't forget that even waits |alone don't cover everything. Service time is the other significant |component. | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Deshpande, Kirti INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Currval and buffer gets
Hello Yechiel, X$DUAL is an Oracle-internal table in the SGA and will not be shown in an ALL_OBJECTS listing. Obviously, you need to be SYS to see this. You can do a describe as SYS and you will see it. Which is the reason why I recommended creating a view and a public synonym on the view, so that the application may reference it without any issues. Cheers, Gaja --- Yechiel Adar [EMAIL PROTECTED] wrote: Hello Gaja I could not find x$dual. Did select on all_objects got zip. Oracle 8.1.6.3.4 on NT. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, April 21, 2002 8:28 PM Hi Yechiel, Any full-table-scan in Oracle 8i (or below) consumes 4 LIOs to the segment header. This number has reduced to 2 in 9i. Given that the 1 row that you are going after is in 1 data block, there is 1 LIO for the data block itself, given you a total of 5 LIOs. You can verify this by setting 10046 for the session and looking at the trace output. The workaround is to reference x$dual in your application. Alternatively, you can create a view on x$dual, create a synonym for it and then go from there. You will incur some I/O for the first access of the query (with the synonym), but subsequent accesses will incur 0 LIOs against x$dual. Cheers, Gaja --- Yechiel Adar [EMAIL PROTECTED] wrote: I did two statspack snapshots, one hour and forty minutes apart. Then I generated a report and loaded it into oraperf.com. In the report I saw that the two SQL statements that where executed the most times where: Select .currval from dual; Select .nextval from dual;. Each one was executed about 90,000 times with 5 buffer gets per execution. The net result was about 950,000 buffer get for nextval and currval. My question is: Why should there be about 5 buffer gets per execution? Yechiel Adar Mehish -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). = Gaja Krishna Vaidyanatha Director, Storage Management Products, Quest Software, Inc. Co-author - Oracle Performance Tuning 101 http://www.osborne.com/database_erp/0072131454/0072131454.shtml __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gaja Krishna Vaidyanatha INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051
RE: Saving down time on Install
Jack, I asked this question last week. I got one reply saying that it was okay to install and build new databases with another version running in a different Oracle home (from Rachel I think). I assume she had tried it out but I don't know for sure. Ben -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ben Poels INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ora-4031- Advice on what to look at next time
Does your event by any chance produce a tracefile that could be used for anything? Mogens Stephane Faroult wrote: - Original Message -From: "Reardon, Bruce (CALBBAY)"[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L[EMAIL PROTECTED]Sent: Sun, 21 Apr 2002 22:23:18Our production instance started getting ora-4031errors around 6pm on Frida=y 19th.=0DI was called by our users around 9am on Sunday 21stand problem persisted a=nd I decided to restart the instance around 11:30amSunday 21st.=0D=0DWhat I would like is some advice on what otherinformation I should collect=next and advice on any settings that may needaltering.=0D Bruce, You mention that you have all of your application objects (I presume you mean 'packages') and all Oracle standard stuff kept in shared memory. AFAIK it's precisely by pinning packages in memory that you avoid the problem you have. However, pinning EVERYTHING is perhaps a bit too much - you must pin only the biggest chunks to limit fragmentation. If I were you, what I'd check first would probably be V$ROWCACHE to check what is anchored and what is following the ebb, try to spot what causes the problem (what is reloaded most often?) and either pin it too or possibly unkeep some not-so-necessary stuff to get some breathing space.Stephane FaroultOriole CorporationPerformance Tools Free Scripts--http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs--- ---
RE: Compare (diff) Oracle DB and MS-SQLServer DB
I will check this out, it sounds like it could work for me. However, if anyone has used DBArtisan extensively, do send me some details (send it directly to me please), so I dont have to go through the entire exercise of downloading, configuring, Simply put, I am looking for something (almost a script, but GUI), that will do an A--B compare and pump out the diffs, no repositories, no setup, no models, no headaches, no nonsense. Thanks Mark, Ray From: Mark Leith [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Compare (diff) Oracle DB and MS-SQLServer DB Date: Fri, 19 Apr 2002 01:23:22 -0800 Ray, Have you looked at Embarcardero's DBArtisan tool? I'm not sure if it will do an active compare of the schemas etc. but it certainly works on both Oracle and SQLServer concurrently, and is in the ballpark that you are looking for.. May be worth checking it out: www.embarcadero.com HTH Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Sent: 18 April 2002 21:24 To: Multiple recipients of list ORACLE-L Mostly schema (tables, views, indexes, ...) and if possible data (reference tables). Now, ERWin... I dont think so...not a good idea to use an ER tool. We would end up unnecessarily managing a model within ERWin, and we would have to retrofit the model (complete compare) each time before running the compare. Such a pain, and I would have to hire another person and keep paying another salary just to do this. I want to reduce my headcount not increase it. What we need is a very simple utility. Something like TOAD. But TOAD only works for ORACLE and does not let you set conditions, so it will be useless here. While I am dreaming... lets continue. We need something like an ADVANCED TOAD, i.e. which not only identifies the diffs but shows the deltas exactly, and which allows us to set conditions for the compare (eg: ignore tablespace, ignore INITIAL, ignore datatype, etc.). Ray From: Joe Raube [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Compare (diff) Oracle DB and MS-SQLServer DB Date: Thu, 18 Apr 2002 10:42:37 -0800 Diff of what? Data? Schema? Stored procs? Depends on what you need -- ERwin or another ER tool may be able to help find schema diffs... --- Ray Gordon [EMAIL PROTECTED] wrote: One of our teams started programming in SQLServer, but now we are live in a production ORACLE environment. Due to several issues, such as licensing,skills, etc., the development must stay on SQLServer. Is there a tool or whatever to find out the deltas between a SQLServerDB and an Oracle DB? Or any suggestions. Ray _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Gordon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Raube INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray
OT: Windows Patches and Oracle
Hello everyone, I asked our sysadmin to keep me updated on all OS patches he plans to apply to our test and production servers, to allow ample testing and research prior to implementation in production. (Testing patches is a new idea to him.) Below is a list of all the patches he wants to install. I already nixed having IIS on this server - who needs the headache?? I am now questioning the need for DirectX and .NET. I am not going to argue with him over the security patches, but can someone verify that DirectX and .NET indeed are extraneous to a server that is dedicated to running an Oracle database? I just want to keep this server as clean as possible and I feel like a ton of useless windows crap is ending up on it, taking up disk and resources. However, I can't confirm that. I am borderline clueless when it comes to Windows. To keep traffic down, please reply to me directly. Thank you very much... Q319733: Internet Information Services Security Roll-up Package Q311967: Security Update Security Update, March 28, 2002 (Internet Explorer 5.01 SP2) Security Update, March 7, 2002 Security Update, March 4, 2002 Security Update, February 22, 2002 Security Update, February 14, 2002 (Internet Explorer 5.01) Security Update, February 12, 2002 Security Update, November 20, 2001 Windows 2000 Security Rollup Package, January, 2002 Windows 2000 Service Pack 2 Microsoft Internet Explorer 6 Q318593: Security Update (Windows 2000 Domain Controller) COM+ Rollup Package 18.1 Euro Conversion Tool Windows Critical Update Notification 3.0 Microsoft .NET Framework DirectX 8.1 Root Certificates Update Internet Explorer Error Reporting Windows 2000 Compatibility Updates High Encryption Pack for Windows 2000 Security Update, March 28, 2002 (Internet Explorer 6) Microsoft .NET Framework Service Pack 1 (English) Security Update, February 14, 2002 (Internet Explorer 6) Security Update, February 13, 2002 (MSXML 3.0) Lisa Koivu Oracle Database Batgirl. Fairfield Resorts, Inc. Ft. Lauderdale, FL, USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Koivu, Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Kernal Parameters for HP-UX 11.0 (Oracle 8.1.7)
All, What are the recommended Kernal Parameters (Semaphore and Shared memory) for HP-UX 11.0 (Oracle 8.1.7). I could not find any good document from metalink site. Also, I could not find any thing from Oracle 8.1.7 documentation CD. Thanks, Bob __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bob Robert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Re[2]: MySQL vs. Oracle database
Plus, I had some experience with a Supra-ized Cincom database. It was a nightmare. We wound up reverting to their more primitive (but stable) database - Total. There was a newer pass at Supra in the early '90s that was supposed to turn a Total database into a relational database and this looked promising...but I moved on to the Oracle world... Kip Bryant |Knowing what I do about SAP support I'd not want to get into a project with |SAPDB! Those good German engineers would chew your head off when calling tech |support. Whatever caused the error MUST be your fault! |Dick Goulet |Reply Separator |Author: [EMAIL PROTECTED] |Date: 4/22/2002 9:30 AM |SAP official history is at http://www.sapdb.org/history.htm |Amusingly, they have blanked out what SAPDB was originally called. Of |personal interest to me is the Cincom connection in that I worked with their |software for much of the 80's... |Kip Bryant ||Um, no, not really. ||SAPDB is Sybase, pure and simple. ||Jared ||On Thursday 18 April 2002 12:21, Gordon, Emery {PDBI~Palo Alto} wrote: || There is a new alternative. SAPDB is open source but supported by SAP. Whan || used outside of SAP applications it is free but charged when used inside of || SAP. The support is from a major software company and the features are much || closer to the Oracle feature set. || || Emery Gordon || || -Original Message- || Sent: Wednesday, April 17, 2002 12:32 PM || To: Multiple recipients of list ORACLE-L || || || || || -- Weaver, Walt [EMAIL PROTECTED] || || I don't think you're wrong. MySQL gets dissed frequently on this list, || but it's really a nice little product. IMHO it's much closer to Oracle || than Access. || || It works well for us. Doesn't scale like Oracle, but works well. || || In some ways it scales better than Oracle. For load+query || (a.k.a., warehouse) operations it can be faster than || Oracle because it doesn't get tangled up with rollbacks, || etc. On systms with many instances it also can be much || simpler to administer. || || -- || Steven Lembark 2930 W. Palmer || Workhorse Computing Chicago, IL 60647 || +1 800 762 1582 ||-- ||Please see the official ORACLE-L FAQ: http://www.orafaq.com ||-- ||Author: Jared Still || INET: [EMAIL PROTECTED] ||Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 ||San Diego, California-- Public Internet access / Mailing Lists || ||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.com |-- |Author: | INET: [EMAIL PROTECTED] |Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 |San Diego, California-- Public Internet access / Mailing Lists | |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.com |-- |Author: | INET: [EMAIL PROTECTED] |Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 |San Diego, California-- Public Internet access / Mailing Lists | |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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ioug-a question
It's on page 3, line 38. That includes the front page. Never figured out why you waste a lot of words before getting to the point, Anjo? Mogens Jonathan Lewis wrote: I don't think anyone who reads the original YAPP paper will miss this. If I recall correctly, one of it's opening statements is the classic formula: response time = service time plus wait time. My own mantra puts in rather less scientific terms: If you have a performance problem, either your database is working too hard, or it's not being allowed to work. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- | |Aside: In the migration to wait-based tuning, don't forget that even waits |alone don't cover everything. Service time is the other significant |component. | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?ISO-8859-1?Q?N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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:Oracle licensing
Humm, Can you say 'AW Sht!!!' Dick Goulet Reply Separator Author: Witold Iwaniec [EMAIL PROTECTED] Date: 4/19/2002 8:08 AM Hi There have been some postings related to Oracle licensing. An interesting article: http://www.sacbee.com/content/politics/story/2219532p-2613285c.html Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Install oracle on redhat 7.2 on pentium IV
Blake - There is a known problem with the Pentium IV, at least for the Windows install. See Note 131299.9 on Metalink. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 22, 2002 12:31 PM To: Multiple recipients of list ORACLE-L Hi, I am attempting to install oracle 8.1.7 on a pentium IV machine using red hat 7.2. I cannot get the oracle universal installer to start. I have discovered problems noted on metalink about pentium IV machines using windows. Does this problem also occur with linux? I have also found that red hat 7.2 is not certified with oracle 8.1.7, only 7.1. Are there any problems and/or workarounds to use 7.2? Thanks, Blake Wilson -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ioug-a question
Indeed. When looking at R = S + W I truly get scared when I see stuff like 400 = 399 + 1. Doesn't leave much room for database work, does it? Anjo Kolk wrote: Well absolutely true, but then everything the database does is a symptom ofthe fact that we throw workload at it ;-)"Deshpande, Kirti" wrote: One needs to keep in mind that Waits are the symptoms, and not a problem.- Kirti-Original Message-Sent: Monday, April 22, 2002 12:13 PMTo: Multiple recipients of list ORACLE-LI don't think anyone who reads the original YAPPpaper will miss this. If I recall correctly, one ofit's opening statements is the classic formula:response time = service time plus wait time.My own mantra puts in rather less scientific terms:If you have a performance problem, either your databaseis working too hard, or it's not being allowed to work.Jonathan Lewishttp://www.jlcomp.demon.co.ukAuthor of:Practical Oracle 8i: Building Efficient DatabasesNext Seminar - Australia - July/Augusthttp://www.jlcomp.demon.c o.uk/seminar.htmlHost to The Co-Operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html-Original Message-||Aside: In the migration to wait-based tuning, don't forget that evenwaits|alone don't cover everything. Service time is the other significant|component.|--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Deshpande, Kirti INET: [EMAIL PROTECTED]Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, s end an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: ioug-a question
Is that the one where they just copied your whole YAPP paper in without mentioning you at all? I seem to recall seing it at IOUG-A last year, but my memory has always been and will always be bad. Mogens Anjo Kolk wrote: Yes,I forgot to mention Gaja's book, and there is a book out there Oracle DBA 101,that has a complete section (2nd or 3rd) about tuning by wait interface/YAPP.Anjo.Greg Moore wrote: 2001 - a lot of books are published with wait interface / YAPP methodology Tuning 101 gets a lot of play here, and they devote a chapter to it. Otherthan that, what books cover waits in a significant way? Thanks.--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Greg Moore INET: [EMAIL PROTECTED]Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: SQL giving problem over the database link
Tom, With this I get the following errors INSERT INTO [EMAIL PROTECTED] * ERROR at line 1: ORA-00913: too many values Any more suggestions. Thanks, Ashoke -Original Message- Sent: Monday, April 22, 2002 12:31 PM To: Multiple recipients of list ORACLE-L Ashoke, Can you try using an in-line view like this: INSERT INTO [EMAIL PROTECTED] SELECT * FROM FW.FWLOT_PN2M,( select fw.fwlot.sysid sysid from fw.fwlot where fw.fwlot.appid = '205956') WHERE FW.FWLOT_PN2M.fromid = sysid; Havn't tried this, but it is worth a shot. good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 22, 2002 1:00 PM To: Multiple recipients of list ORACLE-L Ashoke - Sympathy, but no firm answers here. I have seen this type of behavior before. Someone mentioned that the underlying problem is that the Oracle optimizer doesn't have enough information to make an intelligent decision in some database link situations. Sometimes I've given up in frustration and just pulled the entire table to the other side of the link. I haven't tried this, but I wonder if a hint might help its behavior. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 22, 2002 10:48 AM To: Multiple recipients of list ORACLE-L Greetings, Here is the scenario. We are trying to insert records into a remote table via database link after selecting the data locally. This query hangs for ever: -- INSERT INTO [EMAIL PROTECTED] SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956' : This query returns only one row. SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (.) : This select returns 15 rows. If I replace the subquery as follows then it works great : -- INSERT INTO [EMAIL PROTECTED] SELECT * FROM FWLOT_PN2M WHERE fromid = '0e31.900fb406.37bc1803.3074.2026'; I tried to do the same insert locally and it works great : --- INSERT INTO fwlot_pn2m_gene SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); For some reason the combination of database link and the subquery in the select clause is going for full table scan for each occurence of formid. I am wondering if this is a bug or something against the rule. WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); Any explanation or help is appreciated. Thanks, Ashoke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California--
RE: SQL giving problem over the database link
Hi Raj, I tried this and still it hangs. Thanks, Ashoke -Original Message- Sent: Monday, April 22, 2002 12:31 PM To: Multiple recipients of list ORACLE-L How about this ... INSERT INTO [EMAIL PROTECTED] SELECT a.* FROM FW.FWLOT_PN2M a, fw.fwlot b WHERE a.fromid = b.sysid AND b.appid = '205956'; HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ioug-a question
Ah, good point about Jeff Needham who's now partner with James Morle (ScaleAbilities). I hear rumors that Jeff might also be going to the Database Forum in Sydney. We should have a historical gathering there where we wait in line while everybody else get serviced. Mogens Anjo Kolk wrote: This is from grandpa's memory:The wait interface (v$system_event, v$session_event, v$session_wait) wereintroduced in 7.0.12. So if my memory works correctly at this early hour, thatwas 1992. In 1995, I wrote the Oracle7 wait events and enqueue paper, after JeffNeedham explained a couple of events in the 7.1/7.2 performance and tuningguide. I saw that and decide that we need to do them all. In 1996 I gotinvolved in tuning one of the largest Oracle installations in the world and hadto convince the developers that they were doing the wrong thing. So I came upwith YAPP (actually even to day that site is still using the initial scriptsthat we developed for them based on YAPP). I also started to give talks aboutthis way of tuning, I gave talks to support and consultant goups (1997 andlater). Then some one in support decided that a white paper was needed and thatbecame the YAPP white paper, published 1998.S o a couple of dates and names:1992 - Juan Loaiza (designed the wait interface)1994 - Jeff Needham (documented 4/5 events in the performance and tuning guideof 7.1/7.2)1995/1996 - Oracle7 wait events and enqueue papers (Anjo Kolk)1996 - YAPP developed onsite at one of the largest OLTP OPS sites in the world1997/1998 - people insite of Oracle are getting exposed to the wait interfacetuning (Mogens Norgaard, Cary Millsap, Shari Yamaguchi)1998 - white paper was published1998 - oraperf website2000 - other companies start to work with the wait interface (Hotsos/Miracle)2001 - a lot of books are published with wait interface / YAPP methodology2003 - Richard Niemic rewrites his book and throws out the buffer cache hitratio and introduces the wait interface, like it was invented yesterday ;-)A couple of things that I have noted over the years:1) DBAs want tricks (what parameter should I set) to solve perform ance problems,not a methodology to solve performance problems(learn a man how to fish instead of feeding him)2) response time tuning is not new, the problem is that most tools out theredon't use it and most books don't mention it. It is acomplete paradigm shift for most DBA.3) YAPP/response time tuning is not perfect (by any means), but it is so muchbetter than hit ratio and short list tuning.4) Response time tuning will become more important as there are now productscoming out that do end-to-end response time tuning (actually there already acouple).Watch this space, as things will evolve over the next couple of months/years !Anjo.Jared Still wrote: Nice post. The 'revolution' is indeed not that new, moreof an underground guerilla movement.And it wasn't televised. :)JaredOn Sunday 21 April 2002 10:33, Don Granaman wrote: There seems to be a lot of interest in the "tuning revolution" here, so...The basics revolve around the views v$system_event, v$session_event, andv$session_wait, and v$event_name - and the 10046 event. Rather than try toparaphrase/summarize/expound upon the details, here are some of the bestsites for researching the topic. (I'm sure there are other excellentissites, but these are the ones I know about that most certainly qualify.)Historical note: A year ago at the IOUG-A conference, this was considered"revolutionary". This year it was the most widely presented and discussedtopic at the conference. In fact, the technique has been around for a longtime, it just wasn't widely known or accepted. My initiation to it wasafter an Oracle consultant came out and left something called APS7installed on an Oracle7 system - in 1997! (Aside: Did APS8 ever exist?).APS7 was written by Milsap's group at Oracle a nd some of it uses wait-basedtuning techniques. Poking around and looking at the scripts opened thedoor to profound revelation.Motivational scenario: Cast: DBA (you) and PHB (Pointy-haired boss)PHB: "This tuning report (or GUI tool) shows the cache-hit ratio as toolow. You should tune the database"DBA: "That is to be expected. Batch manipulated 100 GB of data last night.Cache-hit ratio is a meaningless metric anyway."PHB: "But this book says it should be 95%. Besides, we are on theEnglish system - we don't use metric!"www.oraperf.com - Anjo KolkAnjo Kolk's YAPP paper (a pioneering work on the topic). Consider it asprerequisite background reading and the departure point for your journey.www.hotsos.com - Cary MilsapRequires (free) registration. Click on "Knowledg e On-line". There areacres of papers here that are at the core of modern tuning techniques,including "Oracle Kernel Event Documentation Index", "Oracle SystemPerformance Analysis Using Event 10046", Why 99% Database Buffer Cache HitRatio is NOT OK", "Performance Management Myths and Facts", "Why You ShoudFocus
Re: Security Hole
It's even worse if British style humor is involved. Only Australians, Danes and crazy people will understand it, then. I still like the Grant Any Dictionary command, Connor. Let's try it at Oracle World in Copenhagen... Mogens [EMAIL PROTECTED] wrote: Khe, khe I would like to oppose a little bit1. healthy humor is always good2. If people are so inexperienced that they cannot understand thisparticular joke then there is no place for them in at least production dbGints PlivnaIT Sistçmas, Meríeïa 13, LV1050 Rîgahttp://www.itsystems.lv/gints/ "Dale Edgar"[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:Sent by: Subject: Re: Security Hole[EMAIL PROTECTED]2002.04.17 15:28Please respond to ORACLE-L The problem can be worked around by issuing:grant dba, select any table, select any dictionary topublic;Then the bug does not appear to be observed :-) A note of caution. One has to be a bit careful with this sort of jokearoundinexperienced people. The humor off the joke is largely based on themagnitude of the consequences and requires prior knowledge of thoseconsequences. Since these types of joke are largely said in mockseriousnessinexperienced people can miss the point and take it as real advice. Theproblem is even greater if your audience contains people for whom englishisa second language.For example, I once worked on an oil rig where the new guy was tasked withcleaning some grease off the deck. He enquired, quite innocently, as towhathe should use to help get the grease up. Someone replied "Oh just use theSodium Hydroxide, that'll get it good and clean". It was common knowledgethat Sodium Hydroxide (a strong Base) is one of the most corrosive thingsaround and to use it you get kitted out in all sorts of thick rubber gear and require special training. Its nasty nasty stuff and you would never useit for casual cleaning - which was the point of the joke. However, the newguy didn't know this and went ahead and used it - and lost most of bothhands.Just my $0.02- Dale--Check out the free DataBee DBATool - >http://www.databee.com/dt_home.htm--Please see the official ORACLE-L FAQ: http://www.orafaq.com--Author: Dale Edgar INET: [EMAIL PROTECTED]Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051San Diego, California-- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM
Title: DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM Hi everybody, I'm soliciting opinions on whether or not its a good practice to enable DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM. How much overhead is associated? TIA, Beth
Re: ioug-a question
Well Mogens, I clearly remember the point you about 1500 word a day people and 5000 word a day people at my kitchen table. Well I am typing now and my wife is on the phone ;-) Anjo. Mogens Nørgaard wrote: It's on page 3, line 38. That includes the front page. Never figured out why you waste a lot of words before getting to the point, Anjo? Mogens Jonathan Lewis wrote: I don't think anyone who reads the original YAPP paper will miss this. If I recall correctly, one of it's opening statements is the classic formula: response time = service time plus wait time. My own mantra puts in rather less scientific terms: If you have a performance problem, either your database is working too hard, or it's not being allowed to work. Jonathan Lewis http://www.jlcomp.demon.co.uk Author of: Practical Oracle 8i: Building Efficient Databases Next Seminar - Australia - July/August http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html -Original Message- | |Aside: In the migration to wait-based tuning, don't forget that even waits |alone don't cover everything. Service time is the other significant |component. | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mogens =?ISO-8859-1?Q?N=F8rgaard?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL giving problem over the database link
How many rows does it return ... ?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, April 22, 2002 3:14 PM To: Multiple recipients of list ORACLE-L Hi Raj, I tried this and still it hangs. Thanks, Ashoke *2 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. *2
RE: Saving down time on Install
Jack - I normally install the new Oracle version to a separate ORACLE_HOME on the production system. I have not encountered any problems. I would caution you to do the install first on a test system, preferably with Oracle configured as closely as possible to the production system. There is always a small chance that the new install will overwrite some file critical to the production instance, so you should check this as closely as possible. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 22, 2002 9:28 AM To: Multiple recipients of list ORACLE-L Hi All, Before we go through the tests I thought I might save myself some time: We are about to install Oracle 8.1.7 into a seperate Oracle Home on a machine already running 8.0.5 We will not be upgrading our databases yet, just creating new ones from scratch. Our thinking is that apart from rootpre.sh (loading new kernel extension) nothing interferes with the 8.0.5 database/install. So we feel that we should be able to install the new version while other databases are up and running (restarted after rootpre.sh is run). We are on AIX 4.3.3 Anybody done this/info??? TIA Jack === De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack van Zanen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL giving problem over the database link
It is supposed to return 15 rows. Thanks, Ashoke -Original Message- Sent: Monday, April 22, 2002 2:34 PM To: Multiple recipients of list ORACLE-L How many rows does it return ... ?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, April 22, 2002 3:14 PM To: Multiple recipients of list ORACLE-L Hi Raj, I tried this and still it hangs. Thanks, Ashoke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_BLOCK_CHECKING and DB_BLOCK_CHECKSUM
Beth - Are you asking because you are experiencing a corruption problem, or because you're having a paranoid Monday? ;-) I believe the overhead is enough that you wouldn't turn them on just because. But if you are experiencing occasional corruption, you could tolerate quite a bit of overhead. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 22, 2002 2:20 PM To: Multiple recipients of list ORACLE-L Hi everybody, I'm soliciting opinions on whether or not its a good practice to enable DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM. How much overhead is associated? TIA, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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:
Title: RE: There is a solution in Tom Kyte's Expert One on One book. It implements as SQL Loader in PL/SQL with UTL_FILE. Tony Aponte -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 11:38 AM To: Multiple recipients of list ORACLE-L Subject: Hi all! I need a solution about calling sql*loader from pl/sql. I have a version now with external dlls, but actually I don't know the platform so it not seems a good choice. I would like something native oracle solution with oracle's packages or something like that. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists 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_BLOCK_CHECKING and DB_BLOCK_CHECKSUM
Dennis, Are you saying, that I have to take care of turning it off, since in 9i the default value for DB_BLOCK_CHECKSUM is true? Is it that much of overhead? Then, why did Oracle change the default? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 22, 2002 4:02 PM Beth - Are you asking because you are experiencing a corruption problem, or because you're having a paranoid Monday? ;-) I believe the overhead is enough that you wouldn't turn them on just because. But if you are experiencing occasional corruption, you could tolerate quite a bit of overhead. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 22, 2002 2:20 PM To: Multiple recipients of list ORACLE-L Hi everybody, I'm soliciting opinions on whether or not its a good practice to enable DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM. How much overhead is associated? TIA, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
function based index
I have created a function based index on one column , but query is still noy using it . What should be the reason ? oracle 8.1.7 cost based optimizer table and index analyzed recently
Re: ORA-03113
Rajendra, The coredumping is ofcourse a bug in Oracle. However, not producing a stack trace and error stack also sounds like an bug to me. What is the response from Oracle Support on this ? You are using AIX and what version of Oracle ? Thanks, Anjo. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 22, 2002 1:28 PM 3113 is a tough beast to resolve. We have some java processes that feed out spores to our clients. Recently at about 5:30pm and 6pm they all started throwing 3113 errors (with corresponding Exception 11 dump files on the server bdumps and cores too). Mind it well, the server trace file just tells me that it is Exception 11 , no other error is mentioned in the trace file. What we found that flushing the shared pool helped, and it isn't that the shared pool is insufficient, it is about 700M, and usually the system never exceeds 60%. We have plans to scale it down though ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_BLOCK_CHECKING and DB_BLOCK_CHECKSUM
Thanks Dennis. Its a paranoid Monday question. Actually I came across an Oracle document which suggested that they always be enabled. I was skeptical so decided to ask the real experts instead :-) -Original Message- Sent: Monday, April 22, 2002 4:03 PM To: Multiple recipients of list ORACLE-L Beth - Are you asking because you are experiencing a corruption problem, or because you're having a paranoid Monday? ;-) I believe the overhead is enough that you wouldn't turn them on just because. But if you are experiencing occasional corruption, you could tolerate quite a bit of overhead. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 22, 2002 2:20 PM To: Multiple recipients of list ORACLE-L Hi everybody, I'm soliciting opinions on whether or not its a good practice to enable DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM. How much overhead is associated? TIA, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Scheduling for Statspack
Hi, I have a question with regard to the best scheduling for Statspack. I am running Oracle 8.1.6.2 on Hp UX11. I see that the oraperf site recommends no more than intervals of (5 to 15 minutes) to tune performance problems. Does setting up a schedule with 96 or 288 snaps at level 5 put a heavy load on the system? I am still trying to get some performance problems with disk i/o and latch contention sorted out, but I also want to keep baseline statistics to monitor how well my tuning effort is proceeding. What period would you recommend setting dbms jobs to monitor a busy production system? How about hourly for the snapshots and then use a 1 or 2 hour block to keep for historical data. I plan on purging every 14 days but I want to keep something for each day. If I do this how will I be able to tune for performance problems? Any advice will be greatly appreciated Sincerely, Jay Earle, BSc(CS) OCP, ([EMAIL PROTECTED]) DBA, Operations Group -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Earle (DBA) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: function based index
the query_rewrite_enabled init.ora parameter has to be set properly. -Original Message-From: Big Planet [mailto:[EMAIL PROTECTED]]Sent: Monday, April 22, 2002 4:15 PMTo: Multiple recipients of list ORACLE-LSubject: function based index I have created a function based index on one column , but query is still noy using it . What should be the reason ? oracle 8.1.7 cost based optimizer table and index analyzed recently
RE: function based index
Title: Message You should make sure the QUERY_REWRITE_ENABLED init.ora parameter is set to true. -Original Message-From: Big Planet [mailto:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 4:15 PMTo: Multiple recipients of list ORACLE-LSubject: function based index I have created a function based index on one column , but query is still noy using it . What should be the reason ? oracle 8.1.7 cost based optimizer table and index analyzed recently
Patch Install (8.1.7.3) and oraInventory
I am trying to install the 8.1.7.3 upgrade patch. Unfortunately, somewhere down the road I have lost my oraInventory directory. The installer says 'There are no patches to be applied. Metalink says that the only way to get your oraInventory back is to reinstall the current version of Oracle and all patches. Does anyone know of a way to get the patch to install without the inventory history? Or a way to rebuild the oraInventory directory? This is a cloned database and I already tried copying the oraInventory directory from another instance, but the files have the installation directory hardcoded and the oraInventory files are binary so I can't change them. If all else fails, I will reinstall Oracle. But that leads to another question. I want to install the same components that are installed in another instance (that it was cloned from). Is there a way to get a listing of installed components? I ran the installer on the original instance and selected 'Installed Products...' to get a listing, but when I saved it, it gave me a ton of information, most of which was duplicates. How do I weed through that listing? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: MySQL vs. Oracle database
Adabas eh? Oh well, I knew it was a clone of something I didn't care to work with. ( OK all you Adabas lovers, all mail with Adabas in it is now going to /dev/null ;) Jared Marin Dimitrov [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 04/22/2002 11:03 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: MySQL vs. Oracle database - Original Message - SAP official history is at http://www.sapdb.org/history.htm Amusingly, they have blanked out what SAPDB was originally called. Of personal interest to me is the Cincom connection in that I worked with their software for much of the 80's... this is from the users' manual: Development of the independent SAP DB software was begun in 1997 on the basis of the ADABAS D software. also if u search for SAP DB History on google and get a cached copy of the same history page u'll see more details: Installed ADABAS D customer base gets serviced by SAP AG SAP AG resells ADABAS D under the name SAP DB Fork of the ADABAS D code base seems someone is ashamed to mention ADABAS... Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL giving problem over the database link
Ashoke, sorry, try this: INSERT INTO [EMAIL PROTECTED] SELECT FW.FWLOT_PN2M.* FROM FW.FWLOT_PN2M,( select fw.fwlot.sysid sysid from fw.fwlot where fw.fwlot.appid = '205956') WHERE FW.FWLOT_PN2M.fromid = sysid; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 22, 2002 3:20 PM To: Multiple recipients of list ORACLE-L Tom, With this I get the following errors INSERT INTO [EMAIL PROTECTED] * ERROR at line 1: ORA-00913: too many values Any more suggestions. Thanks, Ashoke -Original Message- Sent: Monday, April 22, 2002 12:31 PM To: Multiple recipients of list ORACLE-L Ashoke, Can you try using an in-line view like this: INSERT INTO [EMAIL PROTECTED] SELECT * FROM FW.FWLOT_PN2M,( select fw.fwlot.sysid sysid from fw.fwlot where fw.fwlot.appid = '205956') WHERE FW.FWLOT_PN2M.fromid = sysid; Havn't tried this, but it is worth a shot. good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 22, 2002 1:00 PM To: Multiple recipients of list ORACLE-L Ashoke - Sympathy, but no firm answers here. I have seen this type of behavior before. Someone mentioned that the underlying problem is that the Oracle optimizer doesn't have enough information to make an intelligent decision in some database link situations. Sometimes I've given up in frustration and just pulled the entire table to the other side of the link. I haven't tried this, but I wonder if a hint might help its behavior. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 22, 2002 10:48 AM To: Multiple recipients of list ORACLE-L Greetings, Here is the scenario. We are trying to insert records into a remote table via database link after selecting the data locally. This query hangs for ever: -- INSERT INTO [EMAIL PROTECTED] SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956' : This query returns only one row. SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (.) : This select returns 15 rows. If I replace the subquery as follows then it works great : -- INSERT INTO [EMAIL PROTECTED] SELECT * FROM FWLOT_PN2M WHERE fromid = '0e31.900fb406.37bc1803.3074.2026'; I tried to do the same insert locally and it works great : --- INSERT INTO fwlot_pn2m_gene SELECT * FROM FW.FWLOT_PN2M WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); For some reason the combination of database link and the subquery in the select clause is going for full table scan for each occurence of formid. I am wondering if this is a bug or something against the rule. WHERE FW.FWLOT_PN2M.fromid = (select fw.fwlot.sysid from fw.fwlot where fw.fwlot.appid = '205956'); Any explanation or help is appreciated. Thanks, Ashoke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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
RE: ORA-03113
Anjo, me think it is something related to bind values and we use cursor_sharing=force. There is a bug already logged in and colleague of mine is working on it. In the mean time, I noted it here, because a shared_pool flush worked. I am on 9012 on AIX 4.3 64 bit. I think this is where I was told that the bug is fixed in main line 10 and a backport for 9013 would be available. But 9013 doesn't even compile (at-least we have had problems, I don't know the exact details though) and IBM+Oracle are still trying to resolve it. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 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. *2
What is truth?
My boss (one of those scarey-smart people) was taking some certification test, when someone mused, I wonder how many questions he will answer 'incorrectly' in order to get a higher score? This is because he knows how it really works unlike those that made up the test. Made me wonder how many misconceptions are ingrained in the OCP test stack. Do you know of any? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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_BLOCK_CHECKING and DB_BLOCK_CHECKSUM
Beth - Well, you can get ahead of the curve and report back to the rest of us. Since you mentioned both parameters, I'm assuming that you are considering turning DB_BLOCK_CHECKSUM=true and leaving DB_BLOCK_CHECKING=false. Igor - Thanks for pointing out that the DB_BLOCK_CHECKSUM parameter is turned on by default in 9i I guess my initial reaction was that of the crusty old DBA. I just get suspicious of something that will help me prevent a problem that I'm not experiencing. I've got bitten a few times on that one. I pasted the documentation in below in case anyone wants to see what these two parameters do. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] From the Oracle 9i Documentation: DB_BLOCK_CHECKSUM (default is true in 9i, false in 8i) determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read-only if this parameter is true and the last write of the block stored a checksum. In addition, Oracle gives every log block a checksum before writing it to the current log. If this parameter is set to false, DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces. Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. Turning on this feature typically causes only an additional 1% to 2% overhead. Therefore, Oracle Corporation recommends that you set DB_BLOCK_CHECKSUM to true. DB_BLOCK_CHECKING (default value is false) controls whether Oracle performs block checking for data blocks. When this parameter is set to true, Oracle performs block checking for all data blocks. When it is set to false, Oracle does not perform block checking for blocks in the user tablespaces. However, block checking for the SYSTEM tablespace is always turned on. Oracle checks a block by going through the data on the block, making sure it is self-consistent. Block checking can often prevent memory and data corruption. Block checking typically causes 1% to 10% overhead, depending on workload. The more updates or inserts in a workload, the more expensive it is to turn on block checking. You should set DB_BLOCK_CHECKING to true if the performance overhead is acceptable. -Original Message- Sent: Monday, April 22, 2002 3:25 PM To: Multiple recipients of list ORACLE-L Thanks Dennis. Its a paranoid Monday question. Actually I came across an Oracle document which suggested that they always be enabled. I was skeptical so decided to ask the real experts instead :-) -Original Message- Sent: Monday, April 22, 2002 4:03 PM To: Multiple recipients of list ORACLE-L Beth - Are you asking because you are experiencing a corruption problem, or because you're having a paranoid Monday? ;-) I believe the overhead is enough that you wouldn't turn them on just because. But if you are experiencing occasional corruption, you could tolerate quite a bit of overhead. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, April 22, 2002 2:20 PM To: Multiple recipients of list ORACLE-L Hi everybody, I'm soliciting opinions on whether or not its a good practice to enable DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM. How much overhead is associated? TIA, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Parallel load direct path
Hi. We are loading tables using Ab Initio and SQL*Loader in paralel in direct path mode. Now today the process failed. I thought that no records would be added to the table, but we seem to found about 64000 of them in the table. How/why would this happen? Thanks for any insight Gene __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: What is truth?
This is completely right! -Original Message- Sent: Monday, April 22, 2002 5:38 PM To: Multiple recipients of list ORACLE-L My boss (one of those scarey-smart people) was taking some certification test, when someone mused, I wonder how many questions he will answer 'incorrectly' in order to get a higher score? This is because he knows how it really works unlike those that made up the test. Made me wonder how many misconceptions are ingrained in the OCP test stack. Do you know of any? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL giving problem over the database link
If you've got a sql statement that expects more than one row back across a database link it is very likely to issue a ' select column,column,etc... from table_name' statement across the link resolve things locally via a temporary table, which is not indexed of course. Dick Goulet Reply Separator Author: Mandal; Ashoke [EMAIL PROTECTED] Date: 4/22/2002 11:53 AM It is supposed to return 15 rows. Thanks, Ashoke -Original Message- Sent: Monday, April 22, 2002 2:34 PM To: Multiple recipients of list ORACLE-L How many rows does it return ... ?? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Monday, April 22, 2002 3:14 PM To: Multiple recipients of list ORACLE-L Hi Raj, I tried this and still it hangs. Thanks, Ashoke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: ioug-a question
James Morle's Scaling Oracle8i is my favourite book on Oracle performance, and covers the wait interface excellently. Highly recommended. Paul - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 22, 2002 6:48 AM Yes, I forgot to mention Gaja's book, and there is a book out there Oracle DBA 101, that has a complete section (2nd or 3rd) about tuning by wait interface/YAPP. Anjo. Greg Moore wrote: 2001 - a lot of books are published with wait interface / YAPP methodology Tuning 101 gets a lot of play here, and they devote a chapter to it. Other than that, what books cover waits in a significant way? Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Anjo Kolk INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Paul Vallee INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: data cleansing question
Stefan, I've got a question regarding your migration host - oracle, since I'll have the same problem in the near future. How do you deal with the EBCDIC to ASCII problem ? Do you migrate from VSAM or DB2 ? Which version of Oracle are you on ? Can't reply to the second one, but take a look at the 'dd' UNIX command with the conv=ascii parameter for EBCDIC to ASCII conversions. Do be careful though: Stuff can be present in 7-bit and work differently... John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: What is truth?
Do these qualify? * Far too much emphasis on Oracle's GUI tools (OEM, DBCA, etc.) * Too little emphasis on understanding too much on knowledge (i.e. rote memorization) * Treating ratios as the holy grail of tuning * etc... Don Granaman [certifiable OraSaurus] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, April 22, 2002 4:38 PM My boss (one of those scarey-smart people) was taking some certification test, when someone mused, I wonder how many questions he will answer 'incorrectly' in order to get a higher score? This is because he knows how it really works unlike those that made up the test. Made me wonder how many misconceptions are ingrained in the OCP test stack. Do you know of any? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
char vs. varchar2
Is there any overhead (ie. internal conversion) in comparing a char to a varchar2? We found an instance where a primary key in one table is defined as char(2) and the foreign key referencing it from another table is varchar2(2). We are going to change it, but I'm curious what, if anything, happens when resolving the integrity? Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: What is truth?
Yes, performance tuning using hit ratios. In preparation for that test I studied hard on learning wrong answers to questions like: Q) Hit-ratio is 45%, what to you do? Ray Stell wrote: My boss (one of those scarey-smart people) was taking some certification test, when someone mused, I wonder how many questions he will answer 'incorrectly' in order to get a higher score? This is because he knows how it really works unlike those that made up the test. Made me wonder how many misconceptions are ingrained in the OCP test stack. Do you know of any? === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ray Stell INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Suzy Vordos INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Anything new from IOUG? + OWI Born!! (Anjo/Mogens, please n
Mark, This is from a first-timer at IOUG, so I may be way off here. A lot of marketing blurb was thrown out at IOUG (probably a lot less than usual, and *much* less than Oracle OpenWorld in any case!). As for tools, many vendors were flogging the same ones, improved versions maybe. One which did make us say 'wow' was StorageXpert from Quest. IMHO, this is an excellent tool, engineered by our very own Gaja. I believe details are at the Quest site at www.quest.com. If you have EMC disks and are facing performance problems, I believe there is the best there is. (Or even if you have other storage devices, it would still give you the hotspots). And NO, I do NOT work for Quest, nor have Quest stock! And for others, I believe this was a major turning point and an eye-opener as far as the Wait Interface goes (This has (un)officially been renamed to OWI as per Kirti's slides :-). Most attendees 'saw the light' as far as CHR (Cache Hit Ratio) goes, and there were two distinct camps after the first few days - the 'CHR' and 'OWI'. No prizes for guessing who won the day! The massive number of defections and the absolute absense of CHR-based-discussions at the Performance round tables was clear evidence that OWI is here to stay! (Mr. R might still rewrite that book sooner than later!) About 20 Listers met for dinner on Sunday night (and again in a larger group at the SeaWorld bash). The meeting was characterized by geek-talk such as 'Can you fit us all in one extent?' i.e. 'can we all sit at one table?'), 'Please coalesce' - 'please move in so that more people can fit into the aisle seats'. Oh Boy, that WAS a lot of fun! John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Grace - Getting something we don't deserve Mercy - NOT getting something we deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Mark Leith [mailto:[EMAIL PROTECTED]] Sent: Monday, April 22, 2002 4:58 AM To: Multiple recipients of list ORACLE-L Subject: Anything new from IOUG? Hi All that recently attended IOUG. If you don't already know - I sell tools for Oracle. (delete this now if you want to DG! ;P) I was just wondering if anybody at IOUG had any feedback on any new tools that were launched, or any tools that made a significant impact at IOUG? This is purely for vendor awareness for myself, as I like to keep up to date on anything new in and around our particular market place.. If anybody saw something and thought wow!, I'd be interested in hearing about it. If you would like to contact me directly about this - please feel free, though I feel the list *could* also benefit from this.. Cheers Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: White papers on industry trends
Val, For a real dinosaur, try Jovial. Created by Teledyne for the USAF to improve the performance of ECM equipment. Last used in the late 70's after a fitful 10 year lifespan. If I remember correctly there were about 50 people in the world who knew it. As for your C code, take a serious look at what is being done. I took a number of C programs and after disecting them re-wrote then in PL/SQL and C. In the end it was a lot less code the time to execute dropped from minutes to seconds. Dick Goulet Reply Separator Author: Webber Valerie H [EMAIL PROTECTED] Date: 4/22/2002 9:30 AM Dick, Thanks for the information. You make a good point about learning curve (which is a concern the client has too) and about breaking up the C code. Yes, I am a contractor with the IRS but this project was always written in C. Never in Ada.. thank goodness... Currently the system runs on an Informix database but will be converted to Oracle since it is the new IRS system of choice. (Good move) I was just concerned that keeping the C would be keeping a dinosaur in the backyard out of fear of a learning curve. We have the time to convert it and deal with the learning curve. The C code is pretty much spaghetti code after 17 years of band-aids and duct tape. It desperately needs to be reworked/redesigned not to mention adding Pro*C. We have a sister project that chucked all their C code and rewrote everything in Java. It was tough but the payoff was great. We may have to decide on a module-by-module basis. Ada... now there's a dinosaur if I ever heard of one... :) Val -Original Message- Sent: Monday, April 22, 2002 10:33 AM To: Multiple recipients of list ORACLE-L Valerie, C is still a very heavily used language, although for a complete application I'd probably want to use a C++ variant since they come with screen painter tools. Migrating from C to Forms/Reports would not be unthinkable, but don't underestimate the learning curve. As far as eliminating the external procedure call, if that's the way the application is written, then your stuck and no it will not be eliminated. What may then be more efficient is to take that C code break it up into what is database only and what is C only. Then re-code the application as PL/SQL (or stored Java if your so inclined) keeping in pure C only that which cannot be done otherwise. The industry trend I would have to say is headed towards thin clients and three tier applications which serve internal and external customers and Java. While I agree with the trend in many cases there are too many times that the trend does not really fit the needs. In many a case we end up just moving the bottle neck from one place to the other sometimes making it worse. Case in point is PeopleSoft. There is a panel in the stock room maintenance that updates several tables and rows. Now that is a two tier problem since a lot of data is moving from the server to the client, being processed, and then sent back. But we can id the bottleneck here in that the end user NEEDS a beefy PC. Now you move that into a three tier mess the bottle neck gets harder to find since data moves from the database to the app server, to the client, gets processed sent back to the apps server which does more processing, and then back to the database. Result, you still need the beefy PC on the client side, but you almost need a one to one setup on the app server as well. So then each client actually needs two beefy PC's to do the job in a reasonable manner. YUCK!! Who said thin client was easier cheaper? Must have been some ignorant sales droid at the app server vendor. I note that your address is with the IRS, has the idea of doing Ada cropped up yet? OH, bad joke, it's suppose to be the government's standard programing language as declared by Congress back in the 80's. Then, PL/SQL is the Son of Ada!! Dick Goulet Reply Separator Author: Webber Valerie H [EMAIL PROTECTED] Date: 4/22/2002 5:28 AM Are there any white papers on industry trends for architecture including programming languages. I'm working on a project that is in the early stages of a redesign. The current application uses C code entirely including user interface. The client is sold on Oracle Forms/Reports but is reluctant to trash the C code and start from scratch. This is the first such redesign in 17 years. Does a 3 tier architecture using iAS minimize or eliminate the cost in performance of the external procedure call to the C program from a stored procedure? Any information will be helpful... Thank in advance, Val Valerie H. Webber Management Systems Designers, Inc Database Administrator [EMAIL PROTECTED] 704-566-5321 !DOCTYPE HTML PUBLIC -//W3C//DTD HTML 3.2//EN HTML HEAD META HTTP-EQUIV=Content-Type CONTENT=text/html; charset=ISO-8859-1 META NAME=Generator CONTENT=MS Exchange Server version
Re: DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM
Title: DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM DB_BLOCK_CHECKSUM = no overhead worth mentioning DB_BLOCK_CHECKING = equivalent to 10210 (check data block integrity), 10211 (check index block integrity), and 10212 (check cluster integrity) events The former (DB_BLOCK_CHECKSUM) is useful if you suspect that something might be corrupting a database block while it is residing on disk. The checksum is calculated by the writing process (i.e. DBWR) upon write to datafile and re-calculated/verified by server processes upon read. It will raise an ORA-01578 error upon read... The latter (DB_BLOCK_CHECKING) is intended to cause additional integrity checks while the database block resides in a buffer in the SGA, so it could be useful if you believe that there is some form of memory corruption involving the Buffer Cache. Other events for checking for possible memory corruption include 10235 ("check memory manager internal structures") and 10049 ("Use the OS memory protection (if available) to protect library cache memory heaps that are pinned.")... There is an undocumented parameter ("_db_always_check_system_ts") which defaults to TRUE, causing the constant DB_BLOCK_CHECKING in the SYSTEM tablespace, from v8.1.6 onwards. Thiscaused some problems for those upgrading from v8.0.x and v8.1.5 to v8.1.6 when any kind of corruption exists in the SYSTEM tablespace, because a bug introduced late in 7.3.x (or early 8.0.x?) causes corruption in SYSTEM. Note #96117.1 on MetaLink has more info on this. - Original Message - From: Seefelt, Beth To: Multiple recipients of list ORACLE-L Sent: Monday, April 22, 2002 1:19 PM Subject: DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM Hi everybody, I'm soliciting opinions on whether or not its a good practice to enable DB_BLOCK_CHECKING and DB_BLOCK_CHECKSUM. How much overhead is associated? TIA, Beth
Re: function based index
QUERY_REWRITE must be enabled. Either for the instance (i.e. init.ora or alter system set), for the session (alter session set) orfor a single SQL statement(i.e. SQL hint "rewrite")... You'll also need to analyze the index with either ANALYZE or DBMS_STATS... - Original Message - From: Big Planet To: Multiple recipients of list ORACLE-L Sent: Monday, April 22, 2002 2:15 PM Subject: function based index I have created a function based index on one column , but query is still noy using it . What should be the reason ? oracle 8.1.7 cost based optimizer table and index analyzed recently
RE: Parallel load direct path
sqlldr has rows option: rows -- Number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all) Waleed -Original Message- Sent: Monday, April 22, 2002 6:07 PM To: Multiple recipients of list ORACLE-L Hi. We are loading tables using Ab Initio and SQL*Loader in paralel in direct path mode. Now today the process failed. I thought that no records would be added to the table, but we seem to found about 64000 of them in the table. How/why would this happen? Thanks for any insight Gene __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Hello Everybody ! I have found the List and looking foward to it
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Johnson, Michael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).