Re: Oracle on Sun vs Tru64
We moved from a T64 (ES40 4 CPU) to a Sun (68002 blades 8 CPU) recently. There were no problems with the export/import of the 1/2 TB database. One big thing was that the max block size on Sun for 8174 is 16K. We had used 32K on T64. So we had to rethink our multi_block_read_counts, etc. FTS became kind of slower but I cant say it was due to this move. We were undertaking a big change that time anyway and we tuned the SQL to get a better performance. One another thing to keep in mind is are you going to change the underlying filesystem also - meaning are you planning to move from Compaq Storage Works to some other ? We did that (moved from StorageWorks to Hitachi) and took a performance beating initially. The problem was due to a misconfiguration on the Hitachi side and once the rectified it it has become acceptable. Still I dont see the IO rates the StorageWorks used to deliver..:-( Babu - Original Message - From: Jake Johanssen To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 15, 2003 10:54 AM Subject: Oracle on Sun vs Tru64 Our CIO is pushing a platform change from Tru64to Sun. We are currently running a VLDB (~1.5 TB) on Tru64. Does anyone have feedback on the following: 1) Experiences with either/both, preferred platform? 2) Experiences with platform changes? (Time required for migration?) 3) Any other thoughts? Thanks for your feedback. I am just starting to research the issue so I am interested in any input. Do you Yahoo!?The New Yahoo! Shopping - with improved product search
Re: Oracle on Sun vs Tru64
Dennis I dont have the MEtalink note that says this but here is a clip from one of the forums begin quote Jonathan has given good advice. Also, on Solaris, Oracle db_block_size is limited to 16K. There are some other unix platforms that you can use 32K, but not Solaris. Regards Tom Villane Oracle Support Metalink Analyst end quote What version of Oracle are u talking abt ? I was talking abt 8.1.7.4 Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 4:44 PM Babu - I created a 32K block size database on Solaris. What is the issue? Jake - We are doing the same. Nothing big to report. In our case we saw this coming so we hadn't put any resources in the T64 in several years. The new Solaris system has newer technology, so it really screams. I had asked list members for suggestions on rapid data movement to reduce downtime. It turns out the new box can actually import much faster than the old box can export. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 15, 2003 11:39 AM To: Multiple recipients of list ORACLE-L We moved from a T64 (ES40 4 CPU) to a Sun (6800 2 blades 8 CPU) recently. There were no problems with the export/import of the 1/2 TB database. One big thing was that the max block size on Sun for 8174 is 16K. We had used 32K on T64. So we had to rethink our multi_block_read_counts, etc. FTS became kind of slower but I cant say it was due to this move. We were undertaking a big change that time anyway and we tuned the SQL to get a better performance. One another thing to keep in mind is are you going to change the underlying filesystem also - meaning are you planning to move from Compaq Storage Works to some other ? We did that (moved from StorageWorks to Hitachi) and took a performance beating initially. The problem was due to a misconfiguration on the Hitachi side and once the rectified it it has become acceptable. Still I dont see the IO rates the StorageWorks used to deliver.. :-( Babu - Original Message - To: Multiple mailto:[EMAIL PROTECTED] recipients of list ORACLE-L Sent: Wednesday, October 15, 2003 10:54 AM Our CIO is pushing a platform change from Tru64 to Sun. We are currently running a VLDB (~1.5 TB) on Tru64. Does anyone have feedback on the following: 1) Experiences with either/both, preferred platform? 2) Experiences with platform changes? (Time required for migration?) 3) Any other thoughts? Thanks for your feedback. I am just starting to research the issue so I am interested in any input. _ Do you Yahoo!? The http://shopping.yahoo.com/?__yltc=s%3A15443%2Cd%3A22708228%2Cslk%3Atext %2Csec%3Amail New Yahoo! Shopping - with improved product search -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle-L INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: complete refresh
Sai Complete refresh always truncates the table and then does an insert. However there is trick - if you create a refresh group (even if it contains just one snapshot) and refresh the group instead of the snapshot then it will not truncate but it will delete the rows. Babu - Original Message - From: Sai Selvaganesan To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 15, 2003 1:54 PM Subject: complete refresh hi does complete refresh of mvs always do a truncate of the table and then does an insert from the master site or is there a possibility when it deletes the records in the table and does a insert of the records from master site. one more question is whether this functionality has changed from 7 to 8i/9i. some here in my team say in 7 versioncomplete refresh used to delete records and not truncate the snapshot. thx sai
Re: Table Size
no, it should get the correct size allocated to all the partitions select sum(bytes)/(1024*1024*1024) from dba_segments where segment_name = table_name; SUM(BYTES)/(1024*1024*1024) --- 18 select partition_name, sum(bytes)/(1024*1024*1024) from dba_segments where segment_name = table_name group by partition_name; PARTITION_NAME SUM(BYTES)/(1024*1024*1024) -- --- TAB_2002_1Q 2.375 TAB_2002_2Q 2.25 TAB_2002_3Q 2 TAB_2002_4Q 2.25 TAB_2003_1Q 2.625 TAB_2003_2Q 2.125 TAB_2003_3Q 2 TAB_2003_4Q .125 TAB_HIST2.125 TAB_MAX1 .125 Babu - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 15, 2003 3:04 PM Well, if the table is partitioned you'll get a nice fat zero On 10/15/2003 03:49:36 PM, Stephen Lee wrote: How about: select sum(bytes) from dba_segments where segment_name = 'TABLE_NAME'; -Original Message- Hi All, Could somebody help me in finding the actual size of an oracle table in GB. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle-L INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Database just stops
Title: Message When you mean by "stops" - do u mean you are not able to connect any more from sql*net ? or even the current sessions hang ? Are you running this in archivelog mode ? Is it possible that the database is in archivelog more and the archiver is not running ? Babu - Original Message - From: Smith, Ron L. To: Multiple recipients of list ORACLE-L Sent: Tuesday, October 14, 2003 3:14 PM Subject: Database just stops I have an Oracle 8.1.7.0 database running on an MS2000 server. A few times a day the database just stops running for no reason. There are no messages in the alert or trace logs. Nothing in the Event logs. It just stops. When I restart the database it goes through crash recovery but comes up fine. Anyone have any ideas? Thanks! Ron Smith
CBO Puzzles
All We have a 8.0.4 database using choose optimizer. We have a query that access a single table using index. If we analyze the table and indexes - query takes 8 min to complete If we analyze table and then delete stats on indexes - query runs in two minutes. Explain plan is the same in both cases. I did a event 10053 trace but could not find any difference between the choices the CBO makes except when the indexes are not analyzed it thinks the cost is low. Any ideas why the query would execute faster - with the same explain plan - if the CBO thinks its cost is low ? PS : We ruled out the effects of caching in the buffer by reversing the order of the tests.. TIA Babu -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle-L INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: How to find the session holding the library cache pin.
with the 'handle address' of the object in METHOD 1. You will see that at least one lock for the session has KGLLKREQ 0 which means this is a REQUEST for a lock (thus, the session is waiting). If we now match the KGLLKHDL with the handles of other sessions in X$KGLLK that should give us the address of the blocking session since KGLLKREQ=0 for this session, meaning it HAS the lock. SELECT * FROM X$KGLLK LOCK_A WHERE KGLLKREQ = 0 AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */ AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL AND KGLLKREQ 0); If we look a bit further we can then again match KGLLKSES with SADDR in v$session to find further information on the blocking session: SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION WHERE SADDR in (SELECT KGLLKSES FROM X$KGLLK LOCK_A WHERE KGLLKREQ = 0 AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */ AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL AND KGLLKREQ 0) ); In the same way we can also find all the blocked sessions: SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION WHERE SADDR in (SELECT KGLLKSES FROM X$KGLLK LOCK_A WHERE KGLLKREQ 0 AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKING SESSION */ AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL AND KGLLKREQ = 0) ); - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, August 28, 2003 3:24 PM dba_blockers this doesnt install by default. i cant remember which script runs it. check metalink. warning... its a VERY slow view. From: Murali_Pavuloori/[EMAIL PROTECTED] Date: 2003/08/28 Thu PM 04:14:26 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: How to find the session holding the library cache pin. Gurus: One of the developers has changed his java code and wants to load the class into the db. He did this on production db while users are accessing the application...and then complained that his session is just sitting in idle state I queried the v$session_wait and found that his session is waiting for the library cache pinquestion is how to tell which session is holding the enqueue? Thanks in advance for your help. Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle-L INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: convert number to word ???
SELECT TO_CHAR(TO_DATE(2003,'J'),'JSP') FROM DUAL; TO_CHAR(TO_DATE(20 -- TWO THOUSAND THREE - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 22, 2003 4:19 PM Hi, Is there a function that can conver number to word? ef: 100 is ONE HUNDRED, 2003 is TWO THOUSAND AND THREE. Thank you in advanced!!! __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Andrea Oracle 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: Oracle-L INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
