16 bit sqlplus cannot connect to PO8i
List, i'm trying to make a connect to 8i database (installed under orawin95) from a 16 bit sqlplus (installed under orawin) i tried to configure the IPC in listener, the sqlplus in orawin95 is able to connect, but the 16 bit sqlplus returns error...(cant get server error message) +-+-+-++-- LISTENER.ORA LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC) (KEY= oracle.world) ) (ADDRESS= (PROTOCOL= IPC) (KEY= ORCL) ) ) +-+-+-++-- TNSNAMES.ORA local.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = BEQ) (PROGRAM = oracle80) (ARGV0 = oracle80ORCL) (ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') ) ) (CONNECT_DATA = (SID = ORCL) ) ) this is win95 and PO805 TIA Regards Rahul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rahul 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: V$SESSION.OSUSER returns NT-Domain\NT-User ?
Marc, This looks like bug 1741378, not yet fixed. HTH, -- Anita --- Blum, Marc [EMAIL PROTECTED] wrote: Hi, like many others we use V$SESSION.OSUSER to determine the NT-User, who is connected to the instance. At a customers site we faced the problem, that OSUSER is of the format NT-Domain\NT-User. A behaviour we never faced in our developement environement or at various customer sites. Can anyone explain, what happened? How can we avoid this? Many thanks Mit freundlichen Grüßen i.A. Marc Blum SOPTIM GmbH Grüner Weg 22-24 D-52070 Aachen Telefon: +49 241 / 9 18 79-33 Fax: +49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc 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! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen 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: Steve Adams's Removal
I agree with you Venkat, I'm afraid that we lost tuning expert, C.S.Venkata Subramanian [EMAIL PROTECTED] 5/28 1:15p Hi, I was not able to get head or tail out of this posting. But from the subject I figured out that Steve Adams is removed from this list. If so, Still must reconsider the removal. Steve has guided us so many times with his expert advice and he has also shared his vast and past experiences. Regards Venkat Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: C.S.Venkata Subramanian 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: Bambang Setiawan 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: Database slowdown after 100 days uptime
Hi, There is an alert (Note:118228.1) ALERT: Hang During Startup/Shutdown on Unix When System Uptime 248 Days. But, you are using NT. do you have other errors in alert.log before crash or is there core.log ? "Reardon, Bruce (CALBBAY)" wrote: Hi, We're running Oracle 8.1.5.1.1 on NT 4. On Saturday our database started getting much slower - to me, there didn't seem to be any major events showing up in v$session_wait, v$session_event Restarting the database instance and service solved the performance problems. Today, when reviewing logs of the queries I noticed that the database had been up for 100 days when it started slowing. The 100 days uptime may be unrelated, but has anyone seen this behaviour on any platform? For information, the server itself has been up for 242 days (240 days when the problem started occurring). Thanks, Bruce Reardon mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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: Danisment Gazi Unal (Unal Bilisim) 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: Creating a sorted table
Alternately on earlier versions where the order by can't be used, is to select from the table in indexed order using a hint... hth connor --- Regina Harter [EMAIL PROTECTED] wrote: Well, it won't work in all cases, but I have on occasion used as a shortcut: INSERT INTO ... SELECT DISTINCT transaction_date, ... since the distinct will order it for you, beginning with the first item in the select. A more reliable way would be to use pl/sql, select the ordered data into a cursor, then insert one by one. At 02:45 PM 5/25/01 -0800, you wrote: We have un-ordered data in a table that needs to be inserted into a transaction table in order of the date that the transaction took place. Oracle does not allow INSERT . AS SELECT . ORDER BY. or CREATE TMP_TABLE . AS SELECT . ORDER BY.. Is there a method by which I can accomplish this. Thank you in advance Darren Browett Sys Admin City of Coquitlam -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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: Regina Harter 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!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie -- 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: Steve Adams's Removal
Folks, Please stop this thread This thread started on lazydba list and is relevant on only on that list and not on ORACLE-L. We don't need to write about Steve. He is a person, who doesn't need an introduction in the world of Oracle. Steve, we know you are here and will remain here to help us resolve our Oracle Tuning and Internal issues. Thanks all, Rajesh -Original Message- Setiawan Sent: Monday, May 28, 2001 1:20 PM To: Multiple recipients of list ORACLE-L I agree with you Venkat, I'm afraid that we lost tuning expert, C.S.Venkata Subramanian [EMAIL PROTECTED] 5/28 1:15p Hi, I was not able to get head or tail out of this posting. But from the subject I figured out that Steve Adams is removed from this list. If so, Still must reconsider the removal. Steve has guided us so many times with his expert advice and he has also shared his vast and past experiences. Regards Venkat Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: C.S.Venkata Subramanian 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: Bambang Setiawan 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: Rajesh Dayal 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: Creating a sorted table
There is a very good reason for having data approximately in physical order - it can dramatically improve your buffer hit rates. IOT's are great for this, but if you're on an earlier version then the occasional job to pseudo-cluster the data can be a very good thing... Cheers Connor --- [EMAIL PROTECTED] wrote: Whyever would you want data inserted in order? There is no guarantee that Oracle will actually store the records in order, there is no performance gain, and you can always retrieve the records in order by using an order by statement -- if you really need ordered data, you could use a index-organized table with all of your columns, with the date as the first column. But methinks this would be dangerous for a heavy transaction table. (Gurus, please correct me if I'm wrong here) However, if you are still keen, you could do this through a PL/SQL block, something like the following: declare cursor get_data is select col1, col2, col3, ... from unordered_table order by whatever; begin for dataRec in get_data loop insert into ordered_table (col1, col2, col3, ...) values (dataRec.col1, dataRec.col2, dataRec.col3, ...) end loop; end; / Cheers! Diana Browett, Darren [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] lam.bc.ca cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: Creating a sorted table 05/25/2001 06:45 PM Please respond to ORACLE-L We have un-ordered data in a table that needs to be inserted into a transaction table in order of the date that the transaction took place. Oracle does not allow INSERT . AS SELECT . ORDER BY. or CREATE TMP_TABLE . AS SELECT . ORDER BY.. Is there a method by which I can accomplish this. Thank you in advance Darren Browett Sys Admin City of Coquitlam -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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). = 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!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie
RE: Function based index - insufficient priveleges
hi i found this on metalink the proper privilege required to create function-based indexes. Connect as dba and provide the user with the privileges required to create a function based index. If the user is creating indexes in their own schema: SVRMGR grant query rewrite to ; If the user is creating indexes in schemas other than their own: SVRMGR grant global query rewrite to ; -Oorspronkelijk bericht- Van: Andor, Gyula [SMTP:[EMAIL PROTECTED]] Verzonden:maandag 28 mei 2001 12:45 Aan: Multiple recipients of list ORACLE-L Onderwerp:Function based index - insufficient priveleges Hi Gurus ! Please help me. I can't create function based index because it says: ERROR at line 1: ORA-01031: insufficient privileges I have a table which contains a column called lang_name. I try to create function based index on the table with the following sql statement: create index func_idx_lang_name on lang (upper(lang_name)); I have create any index privilege Enviroment: Oracle 8.1.6 on WinNT Thanks in advance. Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor, Gyula 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).
Function based index - insufficient priveleges
Hi Gurus ! Please help me. I can't create function based index because it says: ERROR at line 1: ORA-01031: insufficient privileges I have a table which contains a column called lang_name. I try to create function based index on the table with the following sql statement: create index func_idx_lang_name on lang (upper(lang_name)); I have "create any index privilege" Enviroment: Oracle 8.1.6 on WinNT Thanks in advance. Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor, Gyula 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 - insufficient priveleges
When in doubt, check the manual ;) Manuals are available in pdf format from metalink and in html and pdf format from http://technet.oracle.com (free registration). Per the SQL Reference manual on the create index statement: To create a function-based index in your own schema on your own table, in addition to the prerequisites for creating a conventional index, you must have the QUERY REWRITE system privilege. To create the index in another schema or on another schema's table, you must have the GLOBAL QUERY REWRITE privilege. In both cases, the table owner must also have the EXECUTE object privilege on the function(s) used in the function-based index. In addition, in order for Oracle to use function-based indexes in queries, the QUERY_REWRITE_ENABLED parameter must be set to true, and the QUERY_REWRITE_INTEGRITY parameter must be set to trusted. Also see Note: 66277.1 Oracle 8i: Concepts and Usage of Function Based Indexes HTH, -- Anita --- Andor, Gyula [EMAIL PROTECTED] wrote: Hi Gurus ! Please help me. I can't create function based index because it says: ERROR at line 1: ORA-01031: insufficient privileges I have a table which contains a column called lang_name. I try to create function based index on the table with the following sql statement: create index func_idx_lang_name on lang (upper(lang_name)); I have create any index privilege Enviroment: Oracle 8.1.6 on WinNT Thanks in advance. Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor, Gyula 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! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen 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: Steve Adams's Removal
Relax, folks. Nobody has removed Steve from the ORACLE-L list. Not that I'm aware of, anyways. He's been asked to leave another completely different list. Some sort of personal clash. Because some posters here confused the address lines, we got copied on their messages: they use both lists. Nothing to do with ORACLE-L. Correct, Jared? Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den - Original Message - I agree with you Venkat, I'm afraid that we lost tuning expert, C.S.Venkata Subramanian [EMAIL PROTECTED] 5/28 1:15p Hi, I was not able to get head or tail out of this posting. But from the subject I figured out that Steve Adams is removed from this list. If so, Still must reconsider the removal. Steve has guided us so many times with his expert advice and he has also shared his vast and past experiences. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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: Musings on tuning and the optimizer
- Original Message - Have run across some interesting things while reading up on the optimizer. Same here! :-) Always thought that the RBO joins your tables in the order found in the FROM clause? Think again. Actually, I found one in Metalink that says with RBO, it's the reverse order of the FROM clause. Which I was aware of. But it also says: if there are NO STATS whatsoever and you use a hint that forces the CBO to be used or it is the default, the order of tables is left to right. Like what you get when using the hint ORDERED. Also, a few other interesting rules: - FIRST_ROWS tends to favour NESTED LOOPS. - ALL_ROWS tends to favour HASH and table scans. old hat in the above EXCEPT (!) if the CBO finds a table with no stats in a join. Then it's most likely hash, for both settings above. And optimizer_mode is CHOOSE and there is a mix of tables in the join with/without stats, then ALL_ROWS is the result. Speaking of the ORDERED hint, it can greatly reduce parse times when joining many tables. Obvious when you think about it. That, it certainly does! My experience too. It never hurts to help the optimizer do its job. All kinds of interesting stuff when you Read The Fine Manual. ;) And a few others. I found out the problem I reported a while ago with CBO suddenly going South on hash scan joins and completely ignoring nested loops or indexes is actually an introduced problem due to a change in CBO rules after 8.0.4. It first affected SAP users. It used to be fixed by a couple of events which due to their usefulness, became the two new optimizer cost adjust parameters in later versions of 8.0 and some of the 8.1. groan... roll on 9i, I've had enough of this optimizer! Cheers Nuno Souto [EMAIL PROTECTED] http://www.users.bigpond.net.au/the_Den -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nuno Souto 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 - insufficient priveleges
Thank you ! This solved the problem. Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor, Gyula 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).
Buffer Hit Ratio 10% on UNIX HP/UX 11.0
Hi list, I have a curious problem with my buffer hit ratio on Oracle 8.0.5, Unix HP UX 11.0. When I run the following select, I get a hit Rati about 8 %. select sum(decode(NAME, 'consistent gets',VALUE, 0)) ConsistentGets, sum(decode(NAME, 'db block gets',VALUE, 0))DBBlockGets, sum(decode(NAME, 'physical reads',VALUE, 0)) PhysicalReads, round((sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0)) - sum(decode(name, 'physical reads',value, 0))) / (sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0))) * 100,2) HitRatio from sys.v_$sysstat; Output: CONSISTENTGETS DBBLOCKGETS PHYSICALREADS HITRATIO 37104235 166477 35109886 5,8 I know the application which works with this instance. I know that hit ratio have to be higher, cause application is tuned and there is not much data in the database. In other environments (Windows NT) I get buffer hit ratios about 90 - 99 %. So my question: Is there something on OS-level (HP UX 11.0) I have to configure to get higher buffer hit ratios? Here is the environment: Oracle 8.0.5 on HP UX 11.0 512 RAM INIT.ORA parameters: compatible 8.0.0 cpu_count1 db_block_size4096 db_block_buffers 5 shared_pool_size 7000 hash_area_size 131072 sort_area_size 131072 large_pool_size 0 log_buffer 163840 TIA Volker Schön E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Schoen Volker 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 - insufficient priveleges
Oracle manual states that in order to use function based indexes in queries the query_rewrite_enabled parameter needs to be set to 'true', and the query_rewrite_integrity parameter to 'trusted', apart from granting the query rewrite privilege. rgds amar -Original Message- Sent: Monday, May 28, 2001 3:00 PM To: Multiple recipients of list ORACLE-L hi i found this on metalink the proper privilege required to create function-based indexes. Connect as dba and provide the user with the privileges required to create a function based index. If the user is creating indexes in their own schema: SVRMGR grant query rewrite to ; If the user is creating indexes in schemas other than their own: SVRMGR grant global query rewrite to ; -Oorspronkelijk bericht- Van: Andor, Gyula [SMTP:[EMAIL PROTECTED]] Verzonden:maandag 28 mei 2001 12:45 Aan: Multiple recipients of list ORACLE-L Onderwerp:Function based index - insufficient priveleges Hi Gurus ! Please help me. I can't create function based index because it says: ERROR at line 1: ORA-01031: insufficient privileges I have a table which contains a column called lang_name. I try to create function based index on the table with the following sql statement: create index func_idx_lang_name on lang (upper(lang_name)); I have create any index privilege Enviroment: Oracle 8.1.6 on WinNT Thanks in advance. Gyula -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andor, Gyula 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: Amar Kumar Padhi 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 - insufficient priveleges
You need to set a couple of parameters in the init.ora, these are query_rewrite_enabled = true query_rewrite_integrity = trusted and you need to grant the privilege QUERY_REWRITE to the user. HTH. Zabair _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zabair ahmed 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: Steve Adams's Removal
PLEASE stop bringing this up on this list. Steve Adams is a valuable resource on this list, and in no way shape or fashion has he been removed, nor have I even contemplated removing him. Jared Still ( the list owner ) On Sunday 27 May 2001 23:10, C.S.Venkata Subramanian wrote: Hi, I was not able to get head or tail out of this posting. But from the subject I figured out that Steve Adams is removed from this list. If so, Still must reconsider the removal. Steve has guided us so many times with his expert advice and he has also shared his vast and past experiences. Regards Venkat Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- 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).
How can I query the SID name from the database
Hi, can I query a database's server's maschine name and SID (not database name) through SQL*Plus? TIA, Tamas Szecsy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Szecsy Tamas 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: 16 bit sqlplus cannot connect to PO8i
If by 16 bit sqlplus you mean version 1 of SqlNet, you can't connect to an 8i database with it. Some version numbers here would help. platform: version of platform: version of Oracle: etc... Jraed On Monday 28 May 2001 00:40, Rahul wrote: List, i'm trying to make a connect to 8i database (installed under orawin95) from a 16 bit sqlplus (installed under orawin) i tried to configure the IPC in listener, the sqlplus in orawin95 is able to connect, but the 16 bit sqlplus returns error...(cant get server error message) +-+-+-++-- LISTENER.ORA LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC) (KEY= oracle.world) ) (ADDRESS= (PROTOCOL= IPC) (KEY= ORCL) ) ) +-+-+-++-- TNSNAMES.ORA local.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = BEQ) (PROGRAM = oracle80) (ARGV0 = oracle80ORCL) (ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') ) ) (CONNECT_DATA = (SID = ORCL) ) ) this is win95 and PO805 TIA Regards Rahul -- 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).
redo copy latch low??
Hi Friends My redo copy latch showing 16.7% (Hit Ratio). How can I increase this one?? Any suggestions?? I got this from utlb/utle stats. Thanks Raghu. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota 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: Musings on tuning and the optimizer
On Monday 28 May 2001 04:45, Nuno Souto wrote: Always thought that the RBO joins your tables in the order found in the FROM clause? Think again. Actually, I found one in Metalink that says with RBO, it's the reverse order of the FROM clause. Which I was aware of. But it also says: if there are NO STATS whatsoever and you use a hint that forces the CBO to be used or it is the default, the order of tables is left to right. Like what you get when using the hint ORDERED. The RBO may consider them in reverse order of how they are listed in the FROm clause, but the docs are quite clear that this is not necessarily the order they will be joined in. And a few others. I found out the problem I reported a while ago with CBO suddenly going South on hash scan joins and completely ignoring nested loops or indexes is actually an introduced problem due to a change in CBO rules after 8.0.4. It first affected SAP users. It used to be fixed by a couple of events which due to their usefulness, became the two new optimizer cost adjust parameters in later versions of 8.0 and some of the 8.1. I think a lot of us have been bitten by that. It has caused a lot of extra work for me when upgrading. Jared -- 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).
RE: 16 bit sqlplus cannot connect to PO8i
Hey Jraed, Why did you change your name? Sorry, I am bored. Management decided to move us around so I am unpacking. -Original Message- Sent: Monday, May 28, 2001 10:20 AM To: Multiple recipients of list ORACLE-L If by 16 bit sqlplus you mean version 1 of SqlNet, you can't connect to an 8i database with it. Some version numbers here would help. platform: version of platform: version of Oracle: etc... Jraed On Monday 28 May 2001 00:40, Rahul wrote: List, i'm trying to make a connect to 8i database (installed under orawin95) from a 16 bit sqlplus (installed under orawin) i tried to configure the IPC in listener, the sqlplus in orawin95 is able to connect, but the 16 bit sqlplus returns error...(cant get server error message) +-+-+-++-- LISTENER.ORA LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC) (KEY= oracle.world) ) (ADDRESS= (PROTOCOL= IPC) (KEY= ORCL) ) ) +-+-+-++-- TNSNAMES.ORA local.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = BEQ) (PROGRAM = oracle80) (ARGV0 = oracle80ORCL) (ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') ) ) (CONNECT_DATA = (SID = ORCL) ) ) this is win95 and PO805 TIA Regards Rahul -- 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: Kimberly Smith 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: How can I query the SID name from the database
machine_name is found in v$session. You can't query the server for the SID, as it is simply an environment variable. Jared On Monday 28 May 2001 10:10, Szecsy Tamas wrote: Hi, can I query a database's server's maschine name and SID (not database name) through SQL*Plus? TIA, Tamas Szecsy -- 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).
Re: 16 bit sqlplus cannot connect to PO8i
On Monday 28 May 2001 11:00, Kimberly Smith wrote: Hey Jraed, Why did you change your name? Sorry, I am bored. Management decided to move us around so I am unpacking. I should change it, as I find Jread much easier to type than Jared. Jraed -Original Message- Sent: Monday, May 28, 2001 10:20 AM To: Multiple recipients of list ORACLE-L If by 16 bit sqlplus you mean version 1 of SqlNet, you can't connect to an 8i database with it. Some version numbers here would help. platform: version of platform: version of Oracle: etc... Jraed On Monday 28 May 2001 00:40, Rahul wrote: List, i'm trying to make a connect to 8i database (installed under orawin95) from a 16 bit sqlplus (installed under orawin) i tried to configure the IPC in listener, the sqlplus in orawin95 is able to connect, but the 16 bit sqlplus returns error...(cant get server error message) +-+-+-++-- LISTENER.ORA LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC) (KEY= oracle.world) ) (ADDRESS= (PROTOCOL= IPC) (KEY= ORCL) ) ) +-+-+-++-- TNSNAMES.ORA local.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = BEQ) (PROGRAM = oracle80) (ARGV0 = oracle80ORCL) (ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') ) ) (CONNECT_DATA = (SID = ORCL) ) ) this is win95 and PO805 TIA Regards Rahul -- 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).
Re: redo copy latch low??
Take a look at this article from Steve Adams: http://www.ixora.com.au/tips/tuning/redo_latches.htm Jared On Monday 28 May 2001 10:25, Raghu Kota wrote: Hi Friends My redo copy latch showing 16.7% (Hit Ratio). How can I increase this one?? Any suggestions?? I got this from utlb/utle stats. Thanks Raghu. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- 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).
RE: How can I query the SID name from the database
THNX Tamas -Original Message- Sent: Monday, May 28, 2001 8:20 PM To: Multiple recipients of list ORACLE-L machine_name is found in v$session. You can't query the server for the SID, as it is simply an environment variable. Jared On Monday 28 May 2001 10:10, Szecsy Tamas wrote: Hi, can I query a database's server's maschine name and SID (not database name) through SQL*Plus? TIA, Tamas Szecsy -- 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: Szecsy Tamas 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: redo copy latch low??
Just pulled up this info from Oracle Docs Well, If you could give some inputs as to the performance issues that u r facing bacause of this - it would be useful. Also, what type of application are u running? Here is the info: * Reducing Contention for Redo Copy Latches On multiple-CPU computers, multiple redo copy latches allow multiple processes to copy entries to the redo log buffer concurrently. The default value of LOG_SIMULTANEOUS_COPIES is the number of CPUs available to your Oracle instance. If you observe contention for redo copy latches, add more latches by increasing the value of LOG_SIMULTANEOUS_COPIES. It can help to have up to twice as many redo copy latches as CPUs available to your Oracle instance. ** Rajaram. -Original Message- From: Raghu Kota [SMTP:[EMAIL PROTECTED]] Sent: Monday, May 28, 2001 1:26 PM To: Multiple recipients of list ORACLE-L Subject:redo copy latch low?? Hi Friends My redo copy latch showing 16.7% (Hit Ratio). How can I increase this one?? Any suggestions?? I got this from utlb/utle stats. Thanks Raghu. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota 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). NetZero Platinum No Banner Ads and Unlimited Access Sign Up Today - Only $9.95 per month! http://www.netzero.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rajaram 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: redo copy latch low??
Thanks jared!! and who responded. From: Jared Still [EMAIL PROTECTED] To: [EMAIL PROTECTED], Raghu Kota [EMAIL PROTECTED] Subject: Re: redo copy latch low?? Date: Mon, 28 May 2001 10:22:41 -0700 Take a look at this article from Steve Adams: http://www.ixora.com.au/tips/tuning/redo_latches.htm Jared On Monday 28 May 2001 10:25, Raghu Kota wrote: Hi Friends My redo copy latch showing 16.7% (Hit Ratio). How can I increase this one?? Any suggestions?? I got this from utlb/utle stats. Thanks Raghu. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raghu Kota 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: How can I query the SID name from the database
Concerning the SID, which I tend to find synonymous with instance name (trying hard to figure out a way to have an instance name different from the SID, but cannot come out with anything), 8i contains a column NAME in V$INSTANCE (which has nothing in common but the name with the Oracle7 V$INSTANCE). With Oracle 7, you can't get it, for the reason that Jared says - OS stuff, not Oracle stuff. The closer you can get to it is SELECT VALUE FROM V$PARAMETER WHERE NAME = 'db_name' assuming, somewhat optimistically, that people usually give the same name to their database as to the instance when there is no parallel server running (this is what I usually do but I have very recently met databases which were created with different conventions). I have noticed, though, with 7.3 a V$ACTIVE_INSTANCE view with a NAME column which only seems to contain something when the parallel server is running (was not my case). That said, you need to relate a name to an instance # and I have not the slightest clue about how to get your instance # in this case since userenv('INSTANCE') only works with Oracle8. Jared Still wrote: machine_name is found in v$session. You can't query the server for the SID, as it is simply an environment variable. Jared On Monday 28 May 2001 10:10, Szecsy Tamas wrote: Hi, can I query a database's server's maschine name and SID (not database name) through SQL*Plus? TIA, Tamas Szecsy -- 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). -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 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 Faroult 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: How can I query the SID name from the database
This is correct - $ORACLE_SID is a host environmental variable and cannot be obtained from the data dictionary. However, you can get the instance name from v$instance.instance_name in 8.x or from v$thread.instance in v7. -Don Granaman [certifiable OraSaurus] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, May 28, 2001 1:20 PM machine_name is found in v$session. You can't query the server for the SID, as it is simply an environment variable. Jared On Monday 28 May 2001 10:10, Szecsy Tamas wrote: Hi, can I query a database's server's maschine name and SID (not database name) through SQL*Plus? TIA, Tamas Szecsy -- 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: 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).
Ray Lane on 9i
http://www.oracle.com/features/9ibetahigh.html http://www.oracle.com/features/9ibetahigh.html If you click on the discussion thread at the bottom of this URL, you will see that Ray Lane (who left Oracle) put in a plug for 9i. Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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: Ray Lane on 9i
Ray Lane (who left Oracle) put in a plug for 9i. Ray Lane said: Oracle database is number one and will remain number one. I can say that from my experience with Oracle. But then again, there's a short message posted from Larry Ellison too: My company rocks ! The Big L Me thinks someone is spoofing. -- 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).
Error Message - Database is Already Up
After Installing Additionally Only the PQO Component of ORA 7.3.4.5 , When Finally Attempting to RE-Link the Oracle Exe , Following Error Message ( of sorts ) is Displayed :- Database is Already Up . Shut it Down First . Hence Re-Link of Oracle Exe Failing NOTE - 1) NO Database whatsoever is UP 2) ORACLE_SID is NOT Set to Any Value 3) NO sgadef$ORACLE_SID File Exists in $ORACLE_HOME/dbs Dir 4) NO $ORACLE_SID_struct File Exists in $ORACLE_HOME/dbs Dir -- 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: Error Message - Database is Already Up
Hello Vivek, Check followings: - If I'm not wrong there should be lk* file 7.3.3 and onwards. remove it. - check shared memory/semaphores by ipcs. if They still exist, remove them by ipcrm if problem still exist, you are most probably hitting a bug. - debug your process by truss/trace in OS system call level. check last lines of truss/trace output. I'm not sure last system call is flushed to file. check it. - set event="error code trace name errorstack forever, level 10" in init.ora. re-produce error. then send us trace file created under udump. regards... VIVEK_SHARMA wrote: After Installing Additionally Only the PQO Component of ORA 7.3.4.5 , When Finally Attempting to RE-Link the Oracle Exe , Following Error Message ( of sorts ) is Displayed :- Database is Already Up . Shut it Down First . Hence Re-Link of Oracle Exe Failing NOTE - 1) NO Database whatsoever is UP 2) ORACLE_SID is NOT Set to Any Value 3) NO sgadef$ORACLE_SID File Exists in $ORACLE_HOME/dbs Dir 4) NO $ORACLE_SID_struct File Exists in $ORACLE_HOME/dbs Dir -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danisment Gazi Unal (Unal Bilisim) 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 and Red Hat
All, Over the past few days, there have been postings which stated that Oracle had withdrawn certification for Red Hat Linux. The first indicated that Oracle had withdrawn certification for Apps 11i on Red Hat Linux. Subsequent postings further indicated that Red Hat would not be a supported OS platform for upcoming releases of the RDBMS as well. I did find one forum posting on Metalink which indicated that Red Hat had withdrawn from the Oracle certification process, but have found no other references to the above. Does anyone know of any relevant articles, or support/desupport notices regarding this? Any info will be greatly appreciated. Thanks, Chuck -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charles Wolfe 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: Buffer Hit Ratio 10% on UNIX HP/UX 11.0
For one thing, which you did not mention, when was the last time this instance was restarted? It is normal to see low cache hit ratios shortly after a startup. Second did someone do something, like select * from the largest table, that could have caused the cache to completely flush? Also do you have something going on that could cause a lot of full table scane, like bad statistics? There are a lot of other user level things like this that can cause problems. I have DB's that stay up for a year at a time don't have low cache hit ratios without good cause. Dick Goulet -- Reply Separator -- Author: Schoen Volker [EMAIL PROTECTED] Date: 5/28/01 4:15 AM Hi list, I have a curious problem with my buffer hit ratio on Oracle 8.0.5, Unix HP UX 11.0. When I run the following select, I get a hit Rati about 8 %. select sum(decode(NAME, 'consistent gets',VALUE, 0)) ConsistentGets, sum(decode(NAME, 'db block gets',VALUE, 0))DBBlockGets, sum(decode(NAME, 'physical reads',VALUE, 0)) PhysicalReads, round((sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0)) - sum(decode(name, 'physical reads',value, 0))) / (sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0))) * 100,2) HitRatio from sys.v_$sysstat; Output: CONSISTENTGETS DBBLOCKGETS PHYSICALREADS HITRATIO 37104235 166477 35109886 5,8 I know the application which works with this instance. I know that hit ratio have to be higher, cause application is tuned and there is not much data in the database. In other environments (Windows NT) I get buffer hit ratios about 90 - 99 %. So my question: Is there something on OS-level (HP UX 11.0) I have to configure to get higher buffer hit ratios? Here is the environment: Oracle 8.0.5 on HP UX 11.0 512 RAM INIT.ORA parameters: compatible 8.0.0 cpu_count1 db_block_size4096 db_block_buffers 5 shared_pool_size 7000 hash_area_size 131072 sort_area_size 131072 large_pool_size 0 log_buffer 163840 TIA Volker Schön E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Schoen Volker 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).
OT
Jared (Still the list owner) - I hope this one is correct :-) - Bhat -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 29, 2001 1:26 AM To: Multiple recipients of list ORACLE-L Subject:Re: Steve Adams's Removal PLEASE stop bringing this up on this list. Steve Adams is a valuable resource on this list, and in no way shape or fashion has he been removed, nor have I even contemplated removing him. Jared Still ( the list owner ) -- 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: 16 bit sqlplus cannot connect to PO8i
Jkstill, i'm using sql*net 2, and can connect to other 8i database on the AIX machine, but then i installed PO 805 (in another home) , and cannot use the sqlplus to connect to the local DB. (the sqlplus installed under the same home as the database can connect without problems) Regards PS: infact i did mention the platform and oracle version in my earlier mail also. -- From: Jared Still[SMTP:[EMAIL PROTECTED]] Reply To: [EMAIL PROTECTED] Sent: Tuesday, May 29, 2001 1:20 AM To: Multiple recipients of list ORACLE-L Subject: Re: 16 bit sqlplus cannot connect to PO8i On Monday 28 May 2001 11:00, Kimberly Smith wrote: Hey Jraed, Why did you change your name? Sorry, I am bored. Management decided to move us around so I am unpacking. I should change it, as I find Jread much easier to type than Jared. Jraed -Original Message- Sent: Monday, May 28, 2001 10:20 AM To: Multiple recipients of list ORACLE-L If by 16 bit sqlplus you mean version 1 of SqlNet, you can't connect to an 8i database with it. Some version numbers here would help. platform: version of platform: version of Oracle: etc... Jraed On Monday 28 May 2001 00:40, Rahul wrote: List, i'm trying to make a connect to 8i database (installed under orawin95) from a 16 bit sqlplus (installed under orawin) i tried to configure the IPC in listener, the sqlplus in orawin95 is able to connect, but the 16 bit sqlplus returns error...(cant get server error message) +-+-+-++-- LISTENER.ORA LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC) (KEY= oracle.world) ) (ADDRESS= (PROTOCOL= IPC) (KEY= ORCL) ) ) +-+-+-++-- TNSNAMES.ORA local.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = BEQ) (PROGRAM = oracle80) (ARGV0 = oracle80ORCL) (ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') ) ) (CONNECT_DATA = (SID = ORCL) ) ) this is win95 and PO805 TIA Regards Rahul -- 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: Rahul 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: redo copy latch low??
Can I know how can I get the ratio redo copy latch from TOAD ? -Original Message- Sent: Tuesday, May 29, 2001 2:20 AM To: Multiple recipients of list ORACLE-L Take a look at this article from Steve Adams: http://www.ixora.com.au/tips/tuning/redo_latches.htm Jared On Monday 28 May 2001 10:25, Raghu Kota wrote: Hi Friends My redo copy latch showing 16.7% (Hit Ratio). How can I increase this one?? Any suggestions?? I got this from utlb/utle stats. Thanks Raghu. _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- 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: Raymond Lee Meng Hong 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: 16 bit sqlplus cannot connect to PO8i
Try using TCP protocol and then connect. I have connected so many times using the same. -- On Mon, 28 May 2001 19:45:54 Rahul wrote: Jkstill, i'm using sql*net 2, and can connect to other 8i database on the AIX machine, but then i installed PO 805 (in another home) , and cannot use the sqlplus to connect to the local DB. (the sqlplus installed under the same home as the database can connect without problems) Regards PS: infact i did mention the platform and oracle version in my earlier mail also. -- From:Jared Still[SMTP:[EMAIL PROTECTED]] Reply To:[EMAIL PROTECTED] Sent:Tuesday, May 29, 2001 1:20 AM To: Multiple recipients of list ORACLE-L Subject: Re: 16 bit sqlplus cannot connect to PO8i On Monday 28 May 2001 11:00, Kimberly Smith wrote: Hey Jraed, Why did you change your name? Sorry, I am bored. Management decided to move us around so I am unpacking. I should change it, as I find Jread much easier to type than Jared. Jraed -Original Message- Sent: Monday, May 28, 2001 10:20 AM To: Multiple recipients of list ORACLE-L If by 16 bit sqlplus you mean version 1 of SqlNet, you can't connect to an 8i database with it. Some version numbers here would help. platform: version of platform: version of Oracle: etc... Jraed On Monday 28 May 2001 00:40, Rahul wrote: List, i'm trying to make a connect to 8i database (installed under orawin95) from a 16 bit sqlplus (installed under orawin) i tried to configure the IPC in listener, the sqlplus in orawin95 is able to connect, but the 16 bit sqlplus returns error...(cant get server error message) +-+-+-++-- LISTENER.ORA LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL= IPC) (KEY= oracle.world) ) (ADDRESS= (PROTOCOL= IPC) (KEY= ORCL) ) ) +-+-+-++-- TNSNAMES.ORA local.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = BEQ) (PROGRAM = oracle80) (ARGV0 = oracle80ORCL) (ARGS = '(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))') ) ) (CONNECT_DATA = (SID = ORCL) ) ) this is win95 and PO805 TIA Regards Rahul -- 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: Rahul 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). Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: C.S.Venkata Subramanian 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: How can I query the SID name from the database
U can get the instance name from V$instance and v$database. If u have set the instance name and sid as same in init.ora, then instance name is the SID. BTW SID=system identifier for the OS to identify the Oracle to allocate resource. U can have multiple instance running in same OS. HTH -- On Mon, 28 May 2001 10:20:25 Jared Still wrote: machine_name is found in v$session. You can't query the server for the SID, as it is simply an environment variable. Jared On Monday 28 May 2001 10:10, Szecsy Tamas wrote: Hi, can I query a database's server's maschine name and SID (not database name) through SQL*Plus? TIA, Tamas Szecsy -- 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). Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: C.S.Venkata Subramanian 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: Musings on tuning and the optimizer
On Monday 28 May 2001 04:45, Nuno Souto wrote: And a few others. I found out the problem I reported a while ago with CBO suddenly going South on hash scan joins and completely ignoring nested loops or indexes is actually an introduced problem due to a change in CBO rules after 8.0.4. It first affected SAP users. It used to be fixed by a couple of events which due to their usefulness, became the two new optimizer cost adjust parameters in later versions of 8.0 and some of the 8.1. groan... roll on 9i, I've had enough of this optimizer! Nuno, here's an interesting bit in the tuning manual that may take care of the hash join problem. For a view with multiple base tables on the right side of an outer join, the optimizer can push the join predicate into the view (see Pushing the Predicate into the View) if the initialization parameter _PUSH_JOIN_PREDICATE is set to TRUE or the accessing query contains the PUSH_PRED hint. Pushing a join predicate is a cost-based transformation that can enable more efficient access path and join methods, such as transforming hash joins into nested loops joins, and full table scans to index scans. I haven't had a chance to try this, as I don't currently have access to a database with real data in it, but this could be the way to prevent those hash join problems when migrating to 8i from 7.x. Jared -- 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).