test mail -- plz ignore
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL and PL/SQL tuning template document required urgently
Dennis, Excellent recommendation, Guy Harrison's book (2nd Edition) is excellent the best I've seen on SQL tuning. I've used it for a number of years. I had the 1st edition and then bought the 2nd when it came out. Cheers, Chris -Original Message- Sent: 29 October 2003 15:49 To: Multiple recipients of list ORACLE-L Ranganath Since you mentioned proactive and reactive query tuning, I think the philosophy with which one approaches the tuning exercise means everything. Wrong philosophy and you spend your time spinning your wheels. All of us have only a limited amount of time to devote, so the best approach will make the best use of that time. Get Optimizing Oracle Performance by Cary Millsap. It doesn't take long to read the important parts. Implement Cary's approach to locate the queries where you will get the most bang for the buck. Then use books like Guy Harrison's (Ryan's suggestion) for pointers on making those queries perform better. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 29, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Hi there, Does any body have a template for proactive and reactive query tuning which can be used as a guideline/report while tuning simple, medium complex and complex SQL queries and PL/SQL stored procedures? If so, can you please forward the same to me please? If not, can anybody suggest as to how to go about doing one? Any help in this regard is very much appreciated. Thanks and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dunscombe, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-1000 and pl/sql cursor cache
Jared Thanks for the response. I've had a play and here are the conclusions: - 1) The cache controlled by session_cached_cursors is entirely separate from the pl/sql static cursor cache. You can turn the former off by setting session_cached_cursors to zero, but you can only turn the latter off by logging out, dropping or recompiling the pl/sql block. 2) The latter cache only operates on *named* blocks: procedure, function or package (not sure about triggers). Hence, your script will not show the behaviour since it uses an anonymous block. 3) _close_cached_open_cursors does indeed close the cursors on commit. Handy, but I don't really want to commit every time I run a query (plus it starts with an underscore:-O). -- David Lord -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED] Sent: 27 November 2003 20:05 To: Multiple recipients of list ORACLE-L Subject: RE: ORA-1000 and pl/sql cursor cache Try playing with alternately setting session_cached_cursors to 0 and some non-zero value and run the following script. Try setting _close_cached_open_cursors to both true and false, changing which 'commit' is used, and omitting the 'commit' altogether. On 9i I find that with session_cached_cursors = 0, and a 'commit' following the pl/sql block, the only cursor appearing in v$open_cursor is the final 'commit'. HTH Jared -- alter session set _close_cached_open_cursors = false; declare xyz varchar2(30); begin select user into xyz from dual; commit; end; / --commit; -- On Thu, 2003-11-27 at 01:09, Lord David wrote: Barb Thanks for the link. Unfortunately, it again hints at the behaviour, but doesn't really come out with it. What's happening is that when you execute a *static* sql statement such as 'select user into xyz from dual', the cursor remains open (as shown in v$open_cursors) after the statement has finished. This also happens with explicit cursors, even if you close them! And it happens whether session_cached_cursors is set to zero or not. I assume that the cursors are cached within the session context for the package or procedure since it only happens for named pl/sql blocks and they get closed if you recompile the block. My guess is that it is a deliberate performance optimisation within the pl/sql engine, but it does mean that to avoid ora-1000 errors, you need to set open_cursors to be greater than the *total* number of static sql statements that a session can open in its lifetime, not just the number concurrently open. Of course you have also got to include room for dynamic and recursive sql as well as cursors cached using session_cached_cursors. -- David Lord -Original Message- From: Barbara Baker [mailto:[EMAIL PROTECTED] Sent: 26 November 2003 16:49 To: Multiple recipients of list ORACLE-L Subject: Re: ORA-1000 and pl/sql cursor cache David: I don't really know if this will help you, but it might be worth a try. You could try setting session_cached_cursors. Bjørn Engsig's white paper Efficient use of bind variables, cursor_sharing and related cursor parameters describes this parameter a bit. It can be found at http://miracleas.dk in the Technical Information section) ( Guy Harrison's tuning book also talks about this parameter. ) good luck! Barb --- Lord David [EMAIL PROTECTED] wrote: Hi Does anyone know whether its possible to control the size of the pl/sql static cursor cache. I'm running into ORA-01000: maximum number of open cursors exceeded errors and part of the problem (apart from the usual developers not closing explicit cursors) is that _all_ static sql statements in compiled pl/sql units seem to be getting cached. I can't find any documentation of this feature apart from a few hints in the pl/sql and application development docs. Here's an example from an 8.1.7 database: - SQLcreate or replace procedure foobar is 2 v_result varchar2(30); 3 begin 4 select user into v_result from dual; 5 end; 6 / Procedure created. SQL SQLselect b.sql_text 2 from v$session a, v$open_cursor b 3 where a.sid = b.sid 4 and a.audsid = userenv('SESSIONID') 5 / SQL_TEXT SELECT SYS_CONTEXT(:b1,:b2) FROM SYS.DUAL select b.sql_text from v$session a, v$open_cursor b where a. SQL SQLexec foobar PL/SQL procedure successfully completed. SQL SQLselect b.sql_text 2 from v$session a, v$open_cursor b 3 where a.sid = b.sid 4 and a.audsid = userenv('SESSIONID') 5 / SQL_TEXT
Re: an article comparing Oracle to other databases
And since we all know that memory accesses are ~14k times faster than disk, these benchmarks just drive the point home. Of course you talk about raw memory access vs. raw disk access here... When you have several memory protection and disk caching mechanisms these figures will change.. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Semi-OT...dbazine
Anyone noticed www.dbazine.com... This domain has temporarily been disabled. To restore the domain, contact your Customer Support. Ooops... = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Download Yahoo! Messenger now for a chance to win Live At Knebworth DVDs http://www.yahoo.co.uk/robbiewilliams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Semi-OT...dbazine
Yep, and it has been like that for several days already Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, November 28, 2003 3:34 PM Anyone noticed www.dbazine.com... This domain has temporarily been disabled. To restore the domain, contact your Customer Support. Ooops... = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Download Yahoo! Messenger now for a chance to win Live At Knebworth DVDs http://www.yahoo.co.uk/robbiewilliams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: analyze after truncate
Title: RE: analyze after truncate I once took over an old database which hadn't been analyzed in nearly a year, so I started analyzing it every two weeks. For the most part things stayed the same or got faster; however, one procedure would sometimes, only sometimes, take a lot longer. Come to find out there was a large 'work' table to hold data for reports. Sometimes this table would have a few hundred thousand records in it and other times it would be empty. If I happen to kick off the analyze when the table was empty, the CBO would not use indexes even though the table was now populated with a lot of records. The Rule hint helped but still not perfect as this table had many indexes and the data was used different ways. In our case, having the table analyzed after populating the table as part of the procedure worked best. Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED] Hello All, We have a huge table both is terms of number of columns and rows. It is list partitioned. it has 7 mill rows and its avg row length is 600 bytes. One of the developers has come up with a method of truncating and reloading the table on a daily basis thorough sqlloader. everyday there are about 50,000 rows insert and 100,000 rows update. My question is do i need to reanalyze this table on a daily basis. Even though we truncate the table the statistics still stays on with the table and since the table goes thorugh only minimal change, is it advisable to analyze it on a daily basis or can i analyze it on a weekly basis. Please advice.. Thanks, Sat.
Re: Semi-OT...dbazine
maybe its running on sql server? From: Connor McDonald [EMAIL PROTECTED] Date: 2003/11/28 Fri AM 08:34:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Semi-OT...dbazine Anyone noticed www.dbazine.com... This domain has temporarily been disabled. To restore the domain, contact your Customer Support. Ooops... = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Download Yahoo! Messenger now for a chance to win Live At Knebworth DVDs http://www.yahoo.co.uk/robbiewilliams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: How to stop XDB
Tanel - Thanks! That is sort of what I did. I shut down the listener and let them start the other process that wanted port 8080. Then I restarted the listener and it griped because it couldn't have that port, but everything else seemed to come up okay. I don't like rude surprises just as I had my coat to leave for a holiday. I also don't like clever little features I don't use being added automatically. Especially clever little features that have security vulnerabilities posted. I like your idea of removing it from listener.ora. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, November 26, 2003 4:59 PM To: Multiple recipients of list ORACLE-L Hi! Check note 171658.1 from metalink, it has series of steps for removing XML stuff from database. Alternatively (if I recall correctly) you can just remove the XDB entry from listener.ora. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, November 27, 2003 12:24 AM My networking people just notified me that XDB has port 8080 locked. Since I wasn't aware that Oracle 9.2 had an XDB running, this quite has me baffled. Is this something I can stop? Oracle has a lot of documentation on how to configure it, etc., but I don't see a thing on stopping it. Any thoughts appreciated. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Cobol redefine in SQL
And I thought that all old people knew cobol ;-) I'll try to think of a solution that does involve creating many tables per redefine statement. Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 poste 7470 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- Jared Still Sent: 27 novembre, 2003 15:09 To: Multiple recipients of list ORACLE-L Carel-Jan, Well, go ahead and spoil all our fun. ;) Seriously though, thanks for posting which part of the fine manual to peruse. If I *knew* COBOL, perhaps I could offer something useful rather than just whining. Now if anyone needs to move from Data-Flex to Oracle, I can offer some ideas. Jared On Thu, 2003-11-27 at 11:44, Carel-Jan wrote: At 05:44 27-11-03 -0800, you wrote: I'm looking for a solution that is using only one table not multiple tables. In fact a dynamic column redefinition feature (cobol redefines) is what I need in Oracle SQL. Stephane Hi Stephane, Instead of flaming Cobol I'll try help you with a solution. Please download the utilities manual, as I advised last time, look at the SQL*Loader part and take case study 5. It will tell you exactly what you want. Redefines, Occurs, all is covered. I think this will solve your problem. You can have as many INTO TABLE clauses as you like, one for each redefine. Every INTO TABLE might refer to the same table. The WHEN clause helps you to determine what INTO TABLE clause to use. To the others in this thread, IMHO Cobol might not be state of the art, whithout it IT might not have been what it is now. I hate the language, actually never ever wrote a working program in it due to some mental blocks connected with the syntax, but I've done many data-conversions of files, based on the record-layouts. Redefines might be ugly, for those of you who do not remember the amazing discovery of a 5 MB (spelled MegaByte) harddisk, and spending 3 hours discussing with 4 programmers how to use this amazing amount of diskspace in a usefull way some hsitorical awareness might help. Cobol, coming from the early sixties AFAIK, doesn't support null-values. Actually, when they started using it, there were no disks at all. Costs of storage were sky-high, and every byte saved was welcome. So, redefines, and repeating groups (OCCURS 10 TIMES in a field definition) really helped people writing usefull programs, based on serial access to data stored on ticker tapes and magnetic tapes. It is about time to get some awareness of the history of our trade, before it all fades away. Visit Miracle's Oracle museum, running Oracle V5 on a 286 / Dos 6.0 system, or similar old-fashioned. Do you condemn the T-Ford as well? That doesn't meet today's standards, but was most important to the development of a certain country in this world. Regards, Carel-Jan -- There will allwasy be another 10 last bugs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Negative impact of SESSION_CACHED_CURSORS parameter
Hi List, In our DB we see huge number of softparses during the load test. So I thought of setting SESSION_CACHED_CURSORS parameter to positive number(100) to take advantage of SOFTER SOFT PARSE. But I am getting negative impact. Kindly advice me what is going on here. Version :8.1.7.3 OS: Sun Solaris tkprof output = SELECT /*+ cached cursors 0 */FIRST_NAME,LAST_NAME,CUSTOMERID,COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2000 1.76 1.77 0 0 0 0 Execute 2000 0.84 0.74 0 0 0 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.60 2.51 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation --- --- 0 HASH JOIN 0 INDEX FAST FULL SCAN (object id 76648) 0 HASH JOIN 0TABLE ACCESS FULL T2 0TABLE ACCESS FULL T1 SELECT /*+ cached cursors 100 */FIRST_NAME,LAST_NAME,CUSTOMERID, COUNTRYABBREV FROM T1 P,T2 E,T3 C WHERE P.T1ID = E.T1ID AND P.BUSINESS_COUNTRY_ID = C.COUNTRYABBREV call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 2000 2.05 1.99 0 0 0 0 Execute 2000 0.82 0.74 0 0 0 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 4000 2.87 2.73 0 0 0 0 Misses in library cache during parse: 0 Optimizer goal: CHOOSE Parsing user id: 165 (recursive depth: 1) Rows Row Source Operation --- --- 0 HASH JOIN 0 INDEX FAST FULL SCAN (object id 76648) 0 HASH JOIN 0TABLE ACCESS FULL T2 0TABLE ACCESS FULL T1 Program used to generate the above trace file. == alter session set SQL_TRACE=true; alter session set session_cached_cursors=0; declare type rc is ref cursor; C rc; n number :=0; begin n := dbms_utility.get_time; for i in 1 .. 2000 loop open C for select /*+ cached cursors 0 */ first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3 c where p.t1id=e.t1id and p.business_country_id=c.countryabbrev; close C; end loop; dbms_output.put_line( dbms_utility.get_time - n ); end; / alter session set session_cached_cursors=100; declare type rc is ref cursor; C rc; n number :=0; begin n := dbms_utility.get_time; for i in 1 .. 2000 loop --open C for select /*+ cached_cursors 100 */ * from dual; open C for select /*+ cached cursors 100 */ first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3 c where p.t1id=e.t1id and p.business_country_id=c.countryabbrev; close C; end loop; dbms_output.put_line( dbms_utility.get_time - n ); end; / SQL @x Session altered. Session altered. 394 PL/SQL procedure successfully completed. Session altered. 413 PL/SQL procedure successfully completed. SQL -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OS Level Defrag
Sorry can't help with the when did it happened question. But in defense of the SysAdmin, I have seen the benefits of defraging on both Unix and NT. In the NT case the defraging was caused by lots of small fragments caused by the datafile being auto extended in short increments, and that being mixed on the same file system with other file updates/additions/deletes. In the Unix case, I was doing a lot of restores to a testing system. When I did the restores I had 5 or 6 uncompress commands outputting to the same /u mount point. After doing this my wio times when through the roof. On the next restore I restricted my restore to one uncompress at a time to each of my /u mount points ... the wio problem went away. The last point I'd like to make is trust no one with your data. Always sum and/or cksum files before and after moving them around (there are gnu versions of these commands for non Unix systems). And 3 last words - backup backup backup. Sujatha Madan [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] tus.net.au cc: Sent by: Subject: OS Level Defrag [EMAIL PROTECTED] .com 11/27/2003 06:59 PM Please respond to ORACLE-L Hi, Does anyone here do an O/S level defrag of their Oracle filesystems??? Background: (Tru64/8.1.7.4) Sysadmin here were adamant that the Oracle domains were running out of extents and were highly fragmented (O/S level). DBA was adamant that the Oracle filesystems should not be defragmented. I lost the battle and the sysadmins are defragging the domains. I now have a corruption on a table partition with 100 million plus rows on a 50G datafile. I am wondering if the defrag has caused this corruption. The only way I can think of finding out is: Finding the approx date of the corruption using the query SELECT ROWID, LAST_COLUMN_OF_TABLE from TABLE_NAME(PARTITION); (which will do the full tablescan row by row). And then finding when the defrag utility was hitting the particular datafile that is corrupted. But this reasoning is flawed Does anyone have another method of trying to pinpoint if the O/S defrag caused the corruption Regards, Sujatha -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Who Wrote ...
Michael: You can find this layout in the book Oracle8i DBA Handbook by Kevin Loney Marlene Theriault. Chapter 4 discusses this scenario in detail. Going from the optimum of 22 disks to the minimum of 7. You can probably find it in other text books. Hope this helps, Ken Janusz, CPIM - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 2:19 PM I remember seeing a paper delivered at an IOUG wa back, that spoke of the 22-disk solution. I know it's old and outdated, but I'd like to find a copy. Does anyone remember who wrote and presented this paper? Thanks, and Happy Thanksgiving! Mike --- === Michael P. Vergara Oracle DBA Guidant Corporation -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vergara, Michael (TEM) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: KENNETH JANUSZ INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Cobol redefine in SQL
At 06:49 28-11-03 -0800, you wrote: And I thought that all old people knew cobol ;-) I'll try to think of a solution that does involve creating many tables per redefine statement. I thought you needed every redefine in the same table? So you mean 'many into table clauses' ISO 'many tables'? Regards, Carel-Jan -- There will allwasy be another 10 last bugs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: an article comparing Oracle to other databases
Perhaps you missed the sarcasm in my response. On Fri, 2003-11-28 at 03:14, Tanel Poder wrote: And since we all know that memory accesses are ~14k times faster than disk, these benchmarks just drive the point home. Of course you talk about raw memory access vs. raw disk access here... When you have several memory protection and disk caching mechanisms these figures will change.. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Parse Vs Execute
By using DBMS_SQL you can open a cursor and re-execute as many times as needed. You can't do that with execute immediate. On Wed, 2003-11-26 at 12:04, [EMAIL PROTECTED] wrote: i remember in tom kytes new book there is a 'softer parse' he was referring to using dbms_sql instead of execute immediate. Im not referring to using dbms_sql when you have to loop and use the same cursor repeatedly so you eliminate all parsing. he didnt go into great detail on this just gave benchmarks. do you know anymore? From: Cary Millsap [EMAIL PROTECTED] Date: 2003/11/26 Wed PM 02:39:39 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Parse Vs Execute Don't do this: Loop Parse Execute Fetch End loop Do this: Parse Loop Execute Fetch End loop If you parse inside your loop, then all that using bind variables will gain you is a reduced hard parse count. If you parse outside the loop (in which case, you MUST use bind variables), then you reduce your number of parse calls. A soft parse is a little cheaper than a hard parse. NO PARSE is a lot cheaper than a soft parse. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- [EMAIL PROTECTED] Sent: Wednesday, November 26, 2003 12:14 PM To: Multiple recipients of list ORACLE-L Hi List, Almost fro all SQLs I am getting Prase count is same as Execute count. How to reduce parse count? 1) We are using bind variable 2) session_cached_cursors set to 100 call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 11 0.01 0.02 0 0 0 0 Execute 11 0.00 0.00 0 0 0 0 Fetch 22 0.01 0.00 0 33 44 110 --- -- -- -- -- -- -- total 44 0.02 0.02 0 33 44 110 Any somebody give more hint on this? Thanks Jay -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
RE: Cobol redefine in SQL
Sorry, it was a typo, I meant a solution that does not involve creating many tables per redefine statement. Stéphane -Original Message- Carel-Jan Engel Sent: 28 novembre, 2003 11:49 To: Multiple recipients of list ORACLE-L At 06:49 28-11-03 -0800, you wrote: And I thought that all old people knew cobol ;-) I'll try to think of a solution that does involve creating many tables per redefine statement. I thought you needed every redefine in the same table? So you mean 'many into table clauses' ISO 'many tables'? Regards, Carel-Jan -- There will allwasy be another 10 last bugs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Paquette INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Cobol redefine in SQL
At 10:29 28-11-03 -0800, you wrote: Sorry, it was a typo, I meant a solution that does not involve creating many tables per redefine statement. Stéphane Allright, success! Regards, Carel-Jan -- There will allwasy be another 10 last bugs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carel-Jan Engel INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT - MVS / Java / Oracle 9iAS
Title: OT - MVS / Java / Oracle 9iAS I am a Java-idiot. I am hoping some of the brilliant people on this list can help me A developer came to me with a problem they are having. He is setting up a JVM to run under USS (Unix System Services) on OS/390 v2.10 He has java code that uses (RMI)IIOP to talk to a EJB on 9iAS running on HPUX This is failing with UFT-8 error. The mainframe code page is IBM-1047. JDK is IBM 1.3.1 HP code page is ISO88859-1 using JDK 1.4.1 This did not work, so he copied the JVM from Oracle download to replace the one in USS (6 libraries / files or something like that) and it still did not work. If any one has ANY ideas of what I can do to help him, where to look, etc TIA - Babette
Re: an article comparing Oracle to other databases
Well, all of those benchmarks could be summarized in a single sentence: Oracle is the best, ite missa est. On 11/28/2003 12:19:25 PM, Jared Still wrote: Perhaps you missed the sarcasm in my response. On Fri, 2003-11-28 at 03:14, Tanel Poder wrote: And since we all know that memory accesses are ~14k times faster than disk, these benchmarks just drive the point home. Of course you talk about raw memory access vs. raw disk access here... When you have several memory protection and disk caching mechanisms these figures will change.. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: migration sequence oci problem
It's solved. The oci-code must be changed when going from oracle 7 to Oracle 8 or 9. When a sequence.nextval is called using a float_type you get returned Duplicates. Not only the type of column returned must be changed but the buffer etc .. also Thanks, Jeroen -Oorspronkelijk bericht- Van: Jeroen van Sluisdam [mailto:[EMAIL PROTECTED]] Verzonden: donderdag 27 november 2003 19:09 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: migration sequence oci problem We could narrow it down to the value of the sequence exceeding 16777216 (2 to the power of 24) This looks familiar as described in bug 2573172 This bug describes to change the type in the odefin call to type 3 but this doesn't help. I know this is not really a dba issue now anymore but I'm desperate for any advice because I don't have any developers left with oci-knowledge Tnx, Jeroen -Oorspronkelijk bericht- Van: Jeroen van Sluisdam Verzonden: Wednesday, November 26, 2003 16:40 Aan: '[EMAIL PROTECTED]' Onderwerp: migration sequence oci problem Hi, We're testing an oracle 9.2.0.4 database with an oracle 7 client. This is a C++ client, using OCI to go to oracle. We see strange behaviour when using a sequence which worked nicely before. The sequence is not incremented when issueing select res_id.nextval from dual When I test this with an oracle 7 sqlplus client this works also as expected. Is there any known bug or issue known with migrating oci-applications. According to manuals it shouldn't be a problem and I cannot find any problems on metalink about this. Thanks in advance, Jeroen
Oracle to MS-SQL Replication
LG, Any pointers, white papers, URL, etc.on how to replicate data from Oracle 8i/9i to M$-SQL2K? Many thanks, Nick Khimani
RE: OS Level Defrag
Sujatha - If it helps, we have Tru64 and 8.1.6 here. My sys admin has defragged the disk quite a few times with no apparent ill effects. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 27, 2003 7:59 PM To: Multiple recipients of list ORACLE-L Hi, Does anyone here do an O/S level defrag of their Oracle filesystems??? Background: (Tru64/8.1.7.4) Sysadmin here were adamant that the Oracle domains were running out of extents and were highly fragmented (O/S level). DBA was adamant that the Oracle filesystems should not be defragmented. I lost the battle and the sysadmins are defragging the domains. I now have a corruption on a table partition with 100 million plus rows on a 50G datafile. I am wondering if the defrag has caused this corruption. The only way I can think of finding out is: Finding the approx date of the corruption using the query SELECT ROWID, LAST_COLUMN_OF_TABLE from TABLE_NAME(PARTITION); (which will do the full tablescan row by row). And then finding when the defrag utility was hitting the particular datafile that is corrupted. But this reasoning is flawed Does anyone have another method of trying to pinpoint if the O/S defrag caused the corruption Regards, Sujatha -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: an article comparing Oracle to other databases
Patrice - By technical, do you mean a feature comparison or a performance comparison. You might want to research the eWeek benchmark for the three a couple of years ago. They made extensive efforts to produce an objective performance analysis, but even at that they had another article later about the things they had overlooked. These are really complex, sophisticated products that are used in really complex, sophisticated ways at a wide variety of sites, so I think it is very difficult to say anything that applies to many situations. On a perverse note, it is interesting to note that almost every deficiency can be viewed from a different perspective. SQL Server has a reputation for costing less, which means many corporations view it as cheap. Oracle has a reputation of being harder to manage, which may mean that Oracle DBA salaries are a little higher. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 27, 2003 10:04 AM To: Multiple recipients of list ORACLE-L http://www.ecommercetimes.com/perl/story/32200.html I have yet to see an objective, detailed comparison of Oracle, DB2 and SQL Server. From a technical (i.e. what can it do) as well as from an organisational (i.e. how is it to manage) point of view. Even 3rd party think tanks seem to walk on egg shells when evaluating software from major vendors, possibly to avoid alienating any of them. Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Test - Please Ignore
Ignore -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: viraj2 INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Test - Please Ignore
ignore viraj2 wrote: Ignore -- Joseph S Testa Chief Technology Officer Data Management Consulting 614-791-9000 It's all about the CACHE -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).