Bug in 9.2.0.4
Hi Listers Any idea about this Bug in 9.2.0.4. If you try "select 'Hello' from dual order by 1 desc;" you may run into the bug. It creates a dump in udump and could disconnect your session. Oracle is creating a one-off patch to fix. The bug is for any order by x, where x is a number, desc and the column is a constant. It does not happen on my instance. Any Details will be appreciated. Regards Munish Bajaj
RE: IBM AIX 32-Bit
Title: IBM AIX 32-Bit Hi, Sorry for intruding, But just curious that how can a 64 bitapplication run on a 32 bit OS. I always thought that it was never possible. Can u please explain how is this possible Regards Munish -Original Message-From: Shiva Subramaniam [mailto:[EMAIL PROTECTED]Sent: Friday, September 19, 2003 13:10To: Multiple recipients of list ORACLE-LSubject: RE: IBM AIX 32-Bit Yes it can be done - 64 bit oracle can be run in 32 bit os mode. Regards Shiva Subramaniam -Original Message-From: Daiminger, Helmut [mailto:[EMAIL PROTECTED] Sent: Thursday, September 18, 2003 11:30 PMTo: Multiple recipients of list ORACLE-LSubject: IBM AIX 32-Bit Hi there! I got a question: Can I run Oracle 64 Bit on IBM AIX 32 Bit? I know that AIX 64 Bit can run either Oracle 32 Bit or 64 Bit. Does it also work the other way round? Thanks, Helmut
DataBase Sizing Guide
Hi Gurus, Can anyone suggest me a good book or links for material on how to estimate and size tablespaces and database, before creating the same. I need to plan my database before I actually go down to create it. Thanks to all in advance Best Regards Munish Bajaj
Using external Authentication with LDAP
Hi Listers, I am presently working on Oracle 9iR2 latest patchset. I'd like to configure user authentication using external LDAP server. Do I need to Install Oracle9iAS to achive this??? Any Resources/Documentation/Links for reference Any kind of help will be appreciated. Thanks to all Regards Munish Bajaj
RE: Redo Logs Problem
Thanks to all who have replied But the problem has been resolved. It was due to a background scheduled process which was deleting records from log tables. I was able to find out this by joining v$session,v$transaction and v$sql dynamic performance views. However thanks again to all for your enlightening answers. This list is great. Regards Munish Bajaj -Original Message-From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED]Sent: Monday, August 04, 2003 11:19To: Multiple recipients of list ORACLE-LSubject: RE: Redo Logs Problem Convert Tablespaces to LMT (Locally Managed) if in 8i , to reduce excessive Redo generation -Original Message-From: Munish Bajaj [mailto:[EMAIL PROTECTED] Sent: Friday, August 01, 2003 4:04 PMTo: Multiple recipients of list ORACLE-LSubject: Redo Logs Problem Hi Listers, One of my remote Clients is facing a problem with Redo Logs. The Redo Logs and the Archive logs in turn have suddenly started to generate at an alarming Rate. This has suddenly started from the last 1 week without any changes to Database Configuration or any other system settings (as per client). Can anyone please help me and let me know all the reasons that could be responsible for this behavior. Any Help from u will be appreciated. Regards Munish Bajaj
Redo Logs Problem
Hi Listers, One of my remote Clients is facing a problem with Redo Logs. The Redo Logs and the Archive logs in turn have suddenly started to generate at an alarming Rate. This has suddenly started from the last 1 week without any changes to Database Configuration or any other system settings (as per client). Can anyone please help me and let me know all the reasons that could be responsible for this behavior. Any Help from u will be appreciated. Regards Munish Bajaj
RE: Redo Logs Problem
I've Checked this in both the v$backup table and v$datafile_headers table (fuzzy column). But the v$backup table shows "Not Achive" and Fuzzy is Null. Any Other Advice. Regards Munish Bajaj -Original Message-From: Naveen Nahata [mailto:[EMAIL PROTECTED]Sent: Friday, August 01, 2003 16:44To: Multiple recipients of list ORACLE-LSubject: RE: Redo Logs Problem Redo is not effected by database configuration but by database activity. Has the no. of transactions increased significantly? If the activity is the same,a wild guess -Has someone issued a command "ALTER TABLESPACE name BEGIN BACKUP" and forgot to issue the command "ALTER TABLESPACE name END BACKUP"??? You can check that using the view sys.v_$backup Regards Naveen -----Original Message-From: Munish Bajaj [mailto:[EMAIL PROTECTED]Sent: Friday, August 01, 2003 4:04 PMTo: Multiple recipients of list ORACLE-LSubject: Redo Logs Problem Hi Listers, One of my remote Clients is facing a problem with Redo Logs. The Redo Logs and the Archive logs in turn have suddenly started to generate at an alarming Rate. This has suddenly started from the last 1 week without any changes to Database Configuration or any other system settings (as per client). Can anyone please help me and let me know all the reasons that could be responsible for this behavior. Any Help from u will be appreciated. Regards Munish Bajaj DISCLAIMER:This message (including attachment if any) is confidential and may be privileged. Before opening attachments please check them for viruses and defects. MindTree Consulting Private Limited (MindTree) will not be responsible for any viruses or defects or any forwarded attachments emanating either from within MindTree or outside. If you have received this message by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change and MindTree shall not be liable for any improper, untimely or incomplete transmission.
RE: Extproc setup Q?
Title: Extproc setup Q? Please add the line in red to your listener.ora file. Extproc has been made secure in Oracle 9i. The following line has to be added to execute your own external procedures. LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_agent)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = plsextproc) (ORACLE_HOME = /oracle/app/product/9.2.0) (PROGRAM = extproc) (ENVS="EXTPROC_DLLS=ANY") ) (SID_DESC = (GLOBAL_DBNAME = Ora92) (ORACLE_HOME = /oracle/app/product/9.2.0) (SID_NAME = ora92) ) ) Regards Munish Bajaj -Original Message-From: Jack van Zanen [mailto:[EMAIL PROTECTED]Sent: Thursday, July 24, 2003 16:24To: Multiple recipients of list ORACLE-LSubject: Extproc setup Q? Hi All, I am trying to setup EXTPROC but keep getting ORA-28575 (Check your tnsnames.ora listener.ora) Attached are my files that look OK to me Anybody?? [EMAIL PROTECTED] admin]$ cat tnsnames.ora # TNSNAMES.ORA Network Configuration File: /oracle/app/product/9.2.0/network/admin/tnsnames.ora ORA92 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = Ora92) ) ) extproc_connection_data = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_agent)) (CONNECT_DATA = (SID = plsextproc)) ) ) [EMAIL PROTECTED] admin]$ cat listener.ora # LISTENER.ORA Network Configuration File: /oracle/app/product/9.2.0/network/admin/listener.ora LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Linux)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = extproc_agent)) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = plsextproc) (ORACLE_HOME = /oracle/app/product/9.2.0) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = Ora92) (ORACLE_HOME = /oracle/app/product/9.2.0) (SID_NAME = ora92) ) ) TIA Jack van Zanen
RE: do I have block corruption?
I think that the table is synonym created in your schema on some other table on other schema. You don't have rights on this table except select rights. This is my opinion. Please check again.. Regards Munish Bajaj -Original Message- Sent: Thursday, July 24, 2003 19:34 To: Multiple recipients of list ORACLE-L 1 CREATE INDEX myIndex 2 ON myTab (myCol) 3PCTFREE 1 4STORAGE ( 5 INITIAL 5M 6 NEXT5M 7 MINEXTENTS 1 8 MAXEXTENTS UNLIMITED 9* PCTINCREASE 0) 10 / ON POS (ACCT_NO) * ERROR at line 2: ORA-08103: object no longer exists i try to create a table with a join off of this column and I get the same error. I ran alter table validate structure and didnt get any errors(do these errors log to a table? I didnt see any docs on OTN about this?) not much on metalink I was able to do a 'create table as' and create a duplicate of the table which suprised me... -- 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: Munish Bajaj 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: OEM Repository Problem
Thanks Wolfgang, The Solution provided by u was great and has worked. Regards Munish Bajaj -Original Message- Sent: Tuesday, July 15, 2003 18:00 To: Multiple recipients of list ORACLE-L This is a shot in the dark, but try and delete (or rename) Oracle_Home\sysman\config\omsconfig.properties At 10:14 PM 7/14/2003 -0800, you wrote: Hi Listers, I had OEM repository on my database installed on my PC. Accidentally I dropped the database. Now I have recreated the database. But an not able to use this database to create New OEM repository as the OEM config assistant says that the database already had a repository installed. If I try to drop the repository it gives me error as the repository is not actually present. Please tell me if anyone of u knows how to solve this problem. Do I have to reinstall the Oracle Software again. Wolfgang Breitling Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Munish Bajaj 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: Transparent database objects after merging
I think I understand ur problem. What u can do is Create New Users for both types of form users and create private synonymsin both user schema for the respective tables and grant relevant permissions. For e.g for Customers_A table in User_A schema create private synonym with the Name Customer. Then when u connect to this user with the forms the Customer_A table will be used and likewise u can do for Customers_B and User_B. Hope this Helps. Regards Munish Bajaj -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, July 16, 2003 09:59To: Multiple recipients of list ORACLE-LSubject: Re: Transparent database objects after mergingThank you for your idea, Mark, To explain more about the problem Before we have 2 databases which they both have same sturcture and same database objects names. We have 2 set of D2K forms and reports to access to each database. After merging, we looking for move data from database B to database A but still be able to access by using the different of forms and reports. However the tables which be move from database B to datbase A, will be rename to table_B. The developer just throw me a fire ball that what-if they don't modify the forms and reposts, is it possible to have one set of the forms access the table_B UK "Mark Richard" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 07/16/2003 12:14 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Transparent database objects after mergingAlso, depending on your requirements (which I don't fully understand)another approach may involve using views:The CUSTOMERS view could point to either CUSTOMERS_A, CUSTOMERS_B or eventhe merge of both tables (select * from customers_a union all select * fromcustomers_b) if required. Of course you may introduce primary and foreignkey problems, but like I said - I don't fully understand your requirementso I'm just throwing ideas up in the air.Regards, Mark. "Arup Nanda" [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: Sent by: Subject: Re: Transparent database objects after merging [EMAIL PROTECTED] .com 16/07/2003 13:59 Please respond to ORACLE-L You can drop or rename CUSTOMERS to, say, CUSTOMERS_A, and then createsynonym CUSTOMERS pointing to CUSTOMERS_B. Or rename CUSTOMERS_B toCUSTOMERS.HTH.Arup Nanda- Original Message -From: [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-LSent: Tuesday, July 15, 2003 11:29 PMSubject: Transparent database objects after mergingHello Gurus,I'm working merging 2 databases which have sane structure ofdatabase objects into one database. However, after merging, the businesspolicies and regulations force us to have 2 set of database objects; forexample :Database A has CUSTOMERS table.Database B has CUSTOMERS table.After merging database A will have CUSTOMERS and CUSTOMERS_B (data fromdatabase B). I just wonder that is there any way that we don't need tochange our script in forms and reports to point into a new table name butstill can access to the data in the new table ( CUSTOMERS_B) notCUSTOMERS.Thank you for any comment and suggestion.Ukrit K, Privileged/Confidential information may be contained in this message.If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone.In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to
OEM Repository Problem
Hi Listers, I had OEM repository on my database installed on my PC. Accidentally I dropped the database. Now I have recreated the database. But an not able to use this database to create New OEM repository as the OEM config assistant says that the database already had a repository installed. If I try to drop the repository it gives me error as the repository is not actually present. Please tell me if anyone of u knows how to solve this problem. Do I have to reinstall the Oracle Software again. Thanks to all in advance Regards Munish Bajaj
Moving LOB Storage and Index
Hi Listers, I created a table containing a LOB Column (icon) with the following storage clause : LOB (ICON) STORE AS (TABLESPACE QUARKDMS_BLOB STORAGE(INITIAL 1M NEXT 5M) CHUNK 4K PCTVERSION 10 NOCACHE LOGGING INDEX (TABLESPACE QUARKDMS_INDEX STORAGE (INITIAL 1M NEXT 1M)) DISABLE STORAGE IN ROW); I want to move only the lob-Index from tablespace Quarkdms_index to a new tablespace. Please let me know whether this is possible and the command for the same. Thanks to all Best Regards Munish Bajaj
Oracle Verison Information
Hi Listers, I have a peculiar problem. I want to get the version of oracle software with all the patches that are installed without opening the database. Is there any file form which i can extract this information. This is required for both Windows and Solaris Platforms. Any help provided will be appreciated. Thanks and Best Regards Munish Bajaj Blank Bkgrd.gif
Help Needed regarding partitioning
Hi Listers, Please let me know whether I can convert an existing heavy table into a partitioned table and how? I need this to improve my query performance. Thanks and Best Regards Munish Bajaj Blank Bkgrd.gif
RE: Performance Problems Solaris Vs Windows
When I specify the STAR Hint in the Query the Query runs OK. Regards Munish Bajaj-Original Message-From: Munish Bajaj [mailto:[EMAIL PROTECTED]Sent: Thursday, June 12, 2003 11:44To: Multiple recipients of list ORACLE-LSubject: RE: Performance Problems Solaris Vs Windows Both the Tables have been analyzed. I'm trying to find the difference between init parameters. Regards Munish Bajaj -Original Message-From: Ishwar Tewari [mailto:[EMAIL PROTECTED]Sent: Thursday, June 12, 2003 00:30To: Multiple recipients of list ORACLE-LSubject: RE: Performance Problems Solaris Vs Windows Do u analyze both sets of tables on the different platforms at the same regular intervals? -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Munish BajajSent: Wednesday, June 11, 2003 12:35 PMTo: Multiple recipients of list ORACLE-LSubject: Performance Problems Solaris Vs Windows Hi Gurus, I'm facing a weird problem. I'm running a same query on windows as well as on Solaris both having Oracle 8.0.5 database using CBO optimizer. The Query runs fine on Windows (takes 20-30 Secs) while the same hangs on Solaris and takes 4-6 hrs to return the results. Both the databases have approx same number of rows and indexes. The Query is : SELECT COUNT(*) FROM mam_assets a, mam_asset_attr_domain_values dmv65549 WHERE a."ID" = dmv65549.asset_id AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) Explain Plan on Solaris 16 SELECT STATEMENT 15 SORT (AGGREGATE) 14 NESTED LOOPS 12 NESTED LOOPS 10 MERGE JOIN (CARTESIAN) 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 9 SORT (JOIN) 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 11 INDEX (UNIQUE SCAN), AST_PK (GMASTER) 13 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (GMASTER) Explain Plan on Windows 15 SELECT STATEMENT 14 SORT (AGGREGATE) 13 NESTED LOOPS 11 HASH JOIN 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 10 NESTED LOOPS 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 9 INDEX (UNIQUE SCAN),
Performance Problems Solaris Vs Windows
Hi Gurus, I'm facing a weird problem. I'm running a same query on windows as well as on Solaris both having Oracle 8.0.5 database using CBO optimizer. The Query runs fine on Windows (takes 20-30 Secs) while the same hangs on Solaris and takes 4-6 hrs to return the results. Both the databases have approx same number of rows and indexes. The Query is : SELECT COUNT(*) FROM mam_assets a, mam_asset_attr_domain_values dmv65549 WHERE a."ID" = dmv65549.asset_id AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) Explain Plan on Solaris 16 SELECT STATEMENT 15 SORT (AGGREGATE) 14 NESTED LOOPS 12 NESTED LOOPS 10 MERGE JOIN (CARTESIAN) 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 9 SORT (JOIN) 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 11 INDEX (UNIQUE SCAN), AST_PK (GMASTER) 13 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (GMASTER) Explain Plan on Windows 15 SELECT STATEMENT 14 SORT (AGGREGATE) 13 NESTED LOOPS 11 HASH JOIN 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 10 NESTED LOOPS 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 9 INDEX (UNIQUE SCAN), AST_PK (QUARKDMS) 12 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (QUARKDMS) As u can clearly see that on Solaris the Oracle does a Merge Join (Cartesian) which is very expensive and hence takes a lot of time. Please help me understand this and provide any solution if possible. Thanks to One and all Best Regards Munish Bajaj Blank Bkgrd.gifThe previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. sql.gifThe previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. blueLineL.GIFThe previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. sort.gifThe previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary attachments are not appropriate for mailing lists. If you want a copy of the attachment which was removed, contact the sender directly and ask for it to be sent to you by private E-mail. This warning is inserted into all messages containing binary attachments which have been removed by ListGuru. If you have questions about this message, contact [EMAIL PROTECTED] for clarification. empty.GIFThe previous attachment was filtered out by the ListGuru mailing software at fatcity.com because binary a
RE: Performance Problems Solaris Vs Windows
Both the Tables have been analyzed. I'm trying to find the difference between init parameters. Regards Munish Bajaj -Original Message-From: Ishwar Tewari [mailto:[EMAIL PROTECTED]Sent: Thursday, June 12, 2003 00:30To: Multiple recipients of list ORACLE-LSubject: RE: Performance Problems Solaris Vs Windows Do u analyze both sets of tables on the different platforms at the same regular intervals? -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Munish BajajSent: Wednesday, June 11, 2003 12:35 PMTo: Multiple recipients of list ORACLE-LSubject: Performance Problems Solaris Vs Windows Hi Gurus, I'm facing a weird problem. I'm running a same query on windows as well as on Solaris both having Oracle 8.0.5 database using CBO optimizer. The Query runs fine on Windows (takes 20-30 Secs) while the same hangs on Solaris and takes 4-6 hrs to return the results. Both the databases have approx same number of rows and indexes. The Query is : SELECT COUNT(*) FROM mam_assets a, mam_asset_attr_domain_values dmv65549 WHERE a."ID" = dmv65549.asset_id AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) Explain Plan on Solaris 16 SELECT STATEMENT 15 SORT (AGGREGATE) 14 NESTED LOOPS 12 NESTED LOOPS 10 MERGE JOIN (CARTESIAN) 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 9 SORT (JOIN) 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (GMASTER) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (GMASTER) 11 INDEX (UNIQUE SCAN), AST_PK (GMASTER) 13 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (GMASTER) Explain Plan on Windows 15 SELECT STATEMENT 14 SORT (AGGREGATE) 13 NESTED LOOPS 11 HASH JOIN 4 VIEW 3 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 1 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 10 NESTED LOOPS 8 VIEW 7 SORT (UNIQUE) 6 TABLE ACCESS (BY INDEX ROWID), MAM_ASSET_ATTR_DOMAIN_VALUES (QUARKDMS) 5 INDEX (RANGE SCAN), ATRVALDOM_DOMVAL_FK_I (QUARKDMS) 9 INDEX (UNIQUE SCAN), AST_PK (QUARKDMS) 12 INDEX (RANGE SCAN), ATRVALDOM_AST_FK_I (QUARKDMS) As u can clearly see that on Solaris the Oracle does a Merge Join (Cartesian) which is very expensive and hence takes a lot of time. Please help me understand this and provide any solution if possible. Thanks to One and all Best Regards Munish Bajaj
32 to 64 Bit Upgrade -- need info
Hi All, I'd like to get some information on how to upgrade a 9iR2 32 Bit Installation to 9iR2 64 Bit. Can I Upgrade just the software and use the same datafiles and control file etc. or do I need to create a new database again and do export/import of the old database. Thanks and Best Regards Munish Bajaj Blank Bkgrd.gif
ORA-27101: shared memory realm does not exist
Hi Listers, What could be the problem?? I'm getting the following error while connecting to the database on Solaris 8. ERROR:ORA-01034: ORACLE not availableORA-27101: shared memory realm does not existSVR4 Error: 2: No such file or directory Thanks to all Best Regards Munish Bajaj Blank Bkgrd.gif
IN or Exists --- performance issue
Hi Listers I have a unique performance problem. As a general rule by oracle while writing SQL scriptsEXISTS should be used in place of IN. I'm having 2 sql for comparison using IN and EXISTS operators. With IN operator SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ COUNT(1) FROM mam_assets a WHERE 1 = 1 AND a.is_current_version = 1 AND a."ID" IN (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) With Exists Operator SELECT /*+ PUSH_SUBQ USE_NL (a) INDEX (a) */ COUNT(1) FROM mam_assets a WHERE 1 = 1 AND a.is_current_version = 1 AND EXISTS (SELECT dmv3.asset_id FROM mam_asset_attr_domain_values dmv3 WHERE a."ID" = dmv3.asset_id AND dmv3.domain_value_id = 71 AND dmv3.asset_attribute_xid = 3 AND dmv3.domain_xid = 7) The Statement having exists is taking more time than the one with IN operator. IN operator statement time = 3sec and the Exists operator statement time = 12 sec. After analysis I have come to know that theEXISTS statement is causing more logical block reads that IN statement, approx 4 times and hence the delay. I have a index onall the predicates mentioned in the where clause. and the explain plan shows a index range search. Can anyone please help me to reduce these high Logical reads which result when I use the EXISTS operator. Thanks to all Best Regards Munish Bajaj Blank Bkgrd.gif
Need to Log on 2000 users
Hi Gurus, I am facing a problem. I need to log on 2000 users to my database via dedicated server connection on Oracle 9iR2 running on Windows 2000 Advanced server. Please guide me as to what all parameters need to be tuned to achieve the same. The Server is a single CPU server with 3G RAM. I need just to logon 2000 users. This is a load test that I need to perform. Thanks to all Regards Munish Bajaj Blank Bkgrd.gif