RE: Why compiling pkg hangs???
You can also check v$access and v$session for any active sessions accessing that package. Jay Miller -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 5:34 PM To: Multiple recipients of list ORACLE-L This was discussed on 31-October and look in the archives for the details, but: What did you check for locking - if dba_locks or standard utllockt.sql then it won't show. You could use Steve Adam's script Executing_packages.sql at http://www.ixora.com.au/scripts/misc.htm to see what packages are executing and who is executing them. Also, can use dba_lock_internal to look at what is being blocked: based on Oracle-L script by Diego Cutrone [mailto:[EMAIL PROTECTED] (Friday, 29 August 2003 7:54 AM) COLUMN lock_id2 FORMAT A30 select to_char(SESSION_ID,'999') sid , substr(LOCK_TYPE,1,30) Type, substr(lock_id1,1,45) Object_Name, substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ, lock_id2 lock_addr FROM dba_lock_internal WHERE mode_requested 'None' and mode_requested mode_held ; and use inverse of this with a given object_name to find who has the internal locks. (I know Yong will suggest this query needs a predicate in the where clause but it works for me and I haven't had time to test alterations.) HTH, Bruce Reardon mailto:[EMAIL PROTECTED] NOTICE: This e-mail and any attachments are private and confidential and may contain legally privileged information. If you are not an authorised recipient, the copying or distribution of this e-mail and any attachments is prohibited and you must not read, print or act in reliance on this e-mail or attachments. This notice should not be removed. -Original Message- Sent: Wednesday, 19 November 2003 9:15 AM To: Multiple recipients of list ORACLE-L Hi all, Our app is written using pl/sql's packages. When I tried to display one form and it runs forever. So I closed the form, and changed the sql in the package that generates the form, and recompiled the pkg, and the recompiling hangs. I checked the database, there is no locking there. What shall I do? (Oracle9i on Linux RedHat, 9iAS.) Thanks. Janet __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy 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: Reardon, Bruce (CALBBAY) 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: 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: Valid Oracle Passwords
But if you don't use double quotes the only symbols I've gotten to work are #, $ and _ -Original Message- Sent: Tuesday, November 11, 2003 5:59 AM To: Multiple recipients of list ORACLE-L In article [EMAIL PROTECTED], Stephane Faroult [EMAIL PROTECTED] writes Ron, Passwords follow the same rules as identifiers, they are internally managed exactly as identifiers. Maximum length is 30 and anthing goes between double quotes. ^^ Hi apart from the fact that the letters a-z are not case sensitive. See my recent test case in the thread passwords case sensitive in this list. kind regards Pete -- Pete Finnigan email:[EMAIL PROTECTED] Web site: http://www.petefinnigan.com - Oracle security audit specialists Book:Oracle security step-by-step Guide - see http://store.sans.org for details. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pete Finnigan 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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Separate Indexes and Data
I assume that what Rachel is referring to is the fact that indexes will generally not release much space when the underlying rows are deleted. They just keep growing, so if you have a large indexed table that frequently deletes and inserts the indexes can grow to fairly ridiculous sizes over a period of time. We just went through the exercise of rebuilding indexes on a db supporting a 3rd party app and reclaimed about 70% of the allocated index space. Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Sunday, October 12, 2003 7:39 AM To: Multiple recipients of list ORACLE-L Hi Rachael, You have me a little confused here. What do you mean by We over allocate space ? To the index segments or to the tablespace ? Why the need to rebuild the indexes ? How are they using more space than required ? What do you mean that you adjust the pctfree so you can determine how small you can resize them to ? You seem to go to a lot of trouble, I'm just failing to see what it all achieves ??? Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 4:34 AM Nuh uh, not me... I have never used or experimented with auto-allocate. I separate indexes and tables so that I can reclaim space by rebuilding the indexes into smaller space. I've just completed writing the scripts for the following: we have a data warehouse, partitioned on the biggest table on date by month. There are 10 or 11 indexes on this table. We overallocate space when we create the new partition for the next month. Data is loaded daily. The hosting company has an automated procedure to add space to the datafile if the used space percentage is greater than some number (we get charged each time they do this, and they never allocate enough space so they do it over and over towards the end of the month). since the indexes are increasing on a daily basis, we overallocate the space. The next month, I go out, determine the partition/tablespace/datafiles that need to be resized (naming standards rule in this case), rebuild the indexes into an interim tablespace, rebuild them back to the original one with a smaller pctfree and then determine how small I can resize them down to. If there were table data in these tablespaces, I'd be out of luck on trying to reclaim space --- [EMAIL PROTECTED] wrote: the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar experience(could be wrong). Due to even the 'small' chance of flaws in auto-allocate, Im thinking of waiting for version 10g before using it. Just to be safe. Not worth risking a defrag on a production system. From: MacGregor, Ian A. [EMAIL PROTECTED] Date: 2003/09/30 Tue PM 01:34:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Separate Indexes and Data I'd be very interested to know how many people have their index tablespaces on a different backup schedule from their data tablespaces. If so how different? What happens when a media failure occurs and you must restore from backup? You would need to have on hand and apply more redo logs to make the database current. I understand the argument proffered is separating indexes and data can mean that when physical corruption of the file happens to an index tablespace then all one needs do is to offline, drop, drop and rebuild the index tablespace. I admit I have not tried off-lining the tablespace first, but you cannot normally drop a tablespace which is being used to enforce referential integrity. If off-lining the tablespace first does work, I can see someone trying to do the rebuild with the database available and having duplicate records in the parent tables and records without parents in the child tables. On the size of the segments: The paper entitled How To Start Defragmenting and Start Living or something like that strongly advocated uniform extent sizes, the suggestion sizes were 128K, 4M, 128M, and 4G as I recall. However the paper Never mentioned what to do when an object that used
RE: Table not reusing deleted space
Hi Leng, I had this problem a while ago and it drove me crazy. What is your PCTFREE? I eventually resolved the problem by reducing my PCTFREE to 5 reasoning that this would put more blocks on the freelist. But I have a really really big table now which I'm finally going to be able to truncate at the end of the month (and regain many gigs of space). Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Friday, October 03, 2003 4:04 AM To: Multiple recipients of list ORACLE-L Hi Daniel, Thank you so much for solving part of the mystery. This is the best explanation I've got yet. I think you may be right. Our avg. row len. is more than 5K and our database is 8k block. I can't believe that the 1G is spread EVERY single 4G block. So how does Oracle determine which 5 blocks to check first before giving up, or is it just a random sample and it happens be those half filled? We may have to build a special 16K block database just for this table in the end. Now I see why we need 9I :-} But upgrading is probably not an option at the moment. Thank you!! Leng. --- From: Daniel Fink [EMAIL PROTECTED] Date: Wed, 01 Oct 2003 07:53:14 -0600 Subject: Re: Table not reusing deleted space This is a multi-part message in MIME format. --D366F924536517C4833C5849 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Leng, I recall a similar scenario some months ago. It had to do with the average row size (quite large) and the block size. The average row size was just under 1/2 of the block size, so the chances of a new row finding a spot in an existing block was slim. Add in that there is a limit (5 I think) of blocks on the freelist that a transaction will attempt to allocate space in before it says I can't find a block with enough free space so I'm going to allocate a new extent.. I think we looked at dba_tables.avg_row_len and dba_tables.avg_space_freelist_blocks. (?) A quick calculation (1048576k /20) indicates that your average row length is over 5k. If you have 8k blocks, this means an average of 1 row per block (perhaps less depending on the variance in row length). Daniel Fink Kaing, Leng wrote: Hello everyone, Env: 8.1.7.4, SunOs 5.8 64 Bit We seem to hitting bug 1262161. The bug seems to imply that tables with triggers behind them do not reuse blocks on the freelist. We have a table that should only use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It is subject to high inserts, deletes and updates. But the resultant number of rows is around 200K rows. The insert is just a normal insert, no APPEND hint is used. Updates do not really expand the rows. We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse the deleted space. In trying to prove this error in our environment I've created 5 test scenarios but was never able to reproduce the problem. It only exists on our production database. I'm stumped. Has anyone encountered this problem? Or can someone explain to me why our production database is not reusing the space deleted and placed back on the free list? I should also add that the table in question is a master table of a snapshot. TIA, Leng. -- Leng Kaing Email: [EMAIL PROTECTED] Phone: +61-3-9203-7589 Mobile: +61-417-371-348 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kaing, Leng 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: 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: Table not reusing deleted space
Actually, that probably wasn't the reasoning (I'm too tired to recollect it at the moment), but it worked. We have PCTFREE 5 and PCTUSED 40. Good luck! Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Friday, October 03, 2003 8:27 PM To: '[EMAIL PROTECTED]' Hi Leng, I had this problem a while ago and it drove me crazy. What is your PCTFREE? I eventually resolved the problem by reducing my PCTFREE to 5 reasoning that this would put more blocks on the freelist. But I have a really really big table now which I'm finally going to be able to truncate at the end of the month (and regain many gigs of space). Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Friday, October 03, 2003 4:04 AM To: Multiple recipients of list ORACLE-L Hi Daniel, Thank you so much for solving part of the mystery. This is the best explanation I've got yet. I think you may be right. Our avg. row len. is more than 5K and our database is 8k block. I can't believe that the 1G is spread EVERY single 4G block. So how does Oracle determine which 5 blocks to check first before giving up, or is it just a random sample and it happens be those half filled? We may have to build a special 16K block database just for this table in the end. Now I see why we need 9I :-} But upgrading is probably not an option at the moment. Thank you!! Leng. --- From: Daniel Fink [EMAIL PROTECTED] Date: Wed, 01 Oct 2003 07:53:14 -0600 Subject: Re: Table not reusing deleted space This is a multi-part message in MIME format. --D366F924536517C4833C5849 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Leng, I recall a similar scenario some months ago. It had to do with the average row size (quite large) and the block size. The average row size was just under 1/2 of the block size, so the chances of a new row finding a spot in an existing block was slim. Add in that there is a limit (5 I think) of blocks on the freelist that a transaction will attempt to allocate space in before it says I can't find a block with enough free space so I'm going to allocate a new extent.. I think we looked at dba_tables.avg_row_len and dba_tables.avg_space_freelist_blocks. (?) A quick calculation (1048576k /20) indicates that your average row length is over 5k. If you have 8k blocks, this means an average of 1 row per block (perhaps less depending on the variance in row length). Daniel Fink Kaing, Leng wrote: Hello everyone, Env: 8.1.7.4, SunOs 5.8 64 Bit We seem to hitting bug 1262161. The bug seems to imply that tables with triggers behind them do not reuse blocks on the freelist. We have a table that should only use 1G (num_rows * avg_row_len), but is actually using 4.1G and growing in size!! It is subject to high inserts, deletes and updates. But the resultant number of rows is around 200K rows. The insert is just a normal insert, no APPEND hint is used. Updates do not really expand the rows. We've changed PCTUSED from 40 to 70 to no avail. The table does not seem to reuse the deleted space. In trying to prove this error in our environment I've created 5 test scenarios but was never able to reproduce the problem. It only exists on our production database. I'm stumped. Has anyone encountered this problem? Or can someone explain to me why our production database is not reusing the space deleted and placed back on the free list? I should also add that the table in question is a master table of a snapshot. TIA, Leng. -- Leng Kaing Email: [EMAIL PROTECTED] Phone: +61-3-9203-7589 Mobile: +61-417-371-348 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kaing, Leng 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: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Separate Indexes and Data
But those holes of exactly the right size for new objects to fit into. Since you'll presumably move it once it gets about 1,000 extents or so that isn't a huge amount of space that's being wasted. Jay Miller Sr. Oracle DBA -Original Message- Sent: Tuesday, September 30, 2003 4:45 PM To: Multiple recipients of list ORACLE-L My criticism of the defrag paper was that it did not address what to do when a segment grew large enough to belong in a tablespace with a larger uniform extent size. Moving the segment creates holes in its original tablespace which may close only in the fullness of time. Physical backups of the files comprising the original tablespace include this wasted space, this is compounded by how many days backup you keep available, and the number of copies of backups. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Reality check for filesystem/disk layout
This database is required to be 7x24 so we have to run in archivelog mode (no cold backups). My concern with SAME is that we may very well end up with a lot of io contention when we're having large data loads and large sorts happening at the same time. And would you really put data files on RAID5 when our main problem on the database is the amount of time it takes to do our monthly loads? Wouldn't that slow down all the inserts? Unfortunately putting redo on dedicated disks will not be approved due to the wasted space. I'd love to do it but... You're probably right about not bothering to put the archive logs on the outer portion of the disk. Thinking it over that doesn't seem necessary. Comments from anyone else on what might profitably inhabit that valuable disk real estate? Rollback segments perhaps? Temp? Or just not bother to specify anything? Jay Miller Sr. Oracle DBA -Original Message- Sent: Saturday, September 27, 2003 4:50 AM To: Multiple recipients of list ORACLE-L Hi, Since it seems that your data can be loaded again easily via night batch load, why not consider noarchivelog mode? SAME is better than your disk partition policy ,I think. You have limited number of disk, seperate your limited number of disks for dedicated redo/archive maybe is not a good idear. And using outer part of the disk maybe become much more complicated when raid is in use.Do you know the underlying raid policy? Another possible solution I will consider is put redo on mirrored disk(2 disks) and everything else on raid5. This max the daily read performance and does not affect the nightly loading.Archive log is not relavant to performance of read and loading,unless archive process is unable to catch the speed of redo generation.I won't put valuable disk resource to archivelog. Zhu Chao. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Re[2]: Cary's book -- Out of stock !
I just ordered from Bookpool. It's in stock according to their website. Glad I took a few minutes to start cleaning up the listserv email, didn't realize it was out yet. Really looking forward to this one... Jay Miller Sr. Oracle DBA -Original Message- Sent: Wednesday, September 24, 2003 1:15 AM To: Multiple recipients of list ORACLE-L I believe this should answer your question: Barnes Noble.com Your Order Has Been Shipped Dear MLADEN GOGALA: Your order has been shipped, and the details appear below. Within 24 hours of receiving this email, you may track the delivery status of your order at http://www.dropshipexpress.com/tracking/tracking.asp?DCN=420066119102126 The book, which isn't available, according to Amazon, has been shipped by B N. It will probably be a big surprise, but my recommendation is definitely Barnes Noble. On 2003.09.23 23:34, Prem Khanna J wrote: 24-09-2003 00:19:47, Mercadante, Thomas F [EMAIL PROTECTED] wrote: I just read the first chapter on-line. Now I gotta order the book. Where Tom ? is it on Amazon or bookpool or BN ... ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- 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: 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).
Reality check for filesystem/disk layout
Title: Message We have the luxury of moving a 300G database to a new box that's being built and choosing the specifications, disk layout, striping, etc. After spendingthe morning poring over Cary Millsap's wonderful VLDB paper this is what we're thinking of but I'd appreciate any comments. One of my maingoals going in was separating redo logs into 2 sets of disks and archive logs on a third. We have 16 disks to play with and seem to be winning the 1+0 battle against some SAs who don't understand why we wouldn't want to use RAID5. The database has minimal write activity during the day (other than sorts to the temp tablespace) but huge batch write activity at night and especially at the end of the month (the data load time is enough of a problem that the few partitioned tables we can easily reload are doing unrecoverable loads). There is a lot of read activity during the day, both single row queries from front ends that are rolled out to several thousand people and reports that can do some large sort/merge joins. Here's what we were thinking: 1st Disk Set - 4 72M disks RAID 1+0 1st and 3rd redo log on outside Misc. Datafiles in middle Misc scripts and files used by other departments in center 2nd Disk Set - 6 72M disks RAID 1+0 Archive logs on outside Temp tablespace and misc. datafiles in middle Text files used for loading in center 3rd Disk Set - 6 72M disks RAID 1+0 2nd and 4th redo logs on outside Rollback tablespace and misc datafiles in middle /oracle (executables and some scripts) in center I was debating if there was any advantage in varying stripe sizes across thedifferent disk sets (since I know Cary says redo logs like fine grained stripe sizes) but given the mix of uses for each that doesn't seem viable. Comments, suggestions or even productive questioning of my sanity would be appreciated. Thanks,Jay Miller
RE: Reality check for filesystem/disk layout
Sure. Our only real problem on this box comes from our large batch loads, especially at the end of the month when we get huge amounts of redo (it usually takes about a week to finish loading the month's data - the only time batch processing doesn't finish overnight). To make matters worse as soon as some tables are loaded reports start being generated off them while other tables are still being loaded. Redo activity is pretty constant at that time with frequent log switches. Since as soon a redo log fills up a) the previous redo log is read b) an archive log is written separating out the archive logs and ever other redo log seems like the best way to minimize io contention for redo. Of course ideally they'd be on their own disks but that's not feasible. I'm playing around a little more by putting the temp filesystem separate from the redo logs just because I know the large reports are a sore point with our production department that runs the data loads and I think this will reduce the delays for end of month loads/reports. Since the outer part of the disk is fastest I put the stuff that's acessed most often there (a trick I learned from a consultant SA we had a few years ago who was the most database/oracle knowledgeable unix SA I've ever met - I really regretted it when the company went through a cost savings period and cancelled his contract). Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Friday, September 26, 2003 5:20 PM To: Multiple recipients of list ORACLE-L Jay, I'd like to see (for my enlightenment) a brief rationale for your decisions, if you have time. Thanks! --- [EMAIL PROTECTED] wrote: We have the luxury of moving a 300G database to a new box that's being built and choosing the specifications, disk layout, striping, etc. After spending the morning poring over Cary Millsap's wonderful VLDB paper this is what we're thinking of but I'd appreciate any comments. One of my main goals going in was separating redo logs into 2 sets of disks and archive logs on a third. We have 16 disks to play with and seem to be winning the 1+0 battle against some SAs who don't understand why we wouldn't want to use RAID5. The database has minimal write activity during the day (other than sorts to the temp tablespace) but huge batch write activity at night and especially at the end of the month (the data load time is enough of a problem that the few partitioned tables we can easily reload are doing unrecoverable loads). There is a lot of read activity during the day, both single row queries from front ends that are rolled out to several thousand people and reports that can do some large sort/merge joins. Here's what we were thinking: 1st Disk Set - 4 72M disks RAID 1+0 1st and 3rd redo log on outside Misc. Datafiles in middle Misc scripts and files used by other departments in center 2nd Disk Set - 6 72M disks RAID 1+0 Archive logs on outside Temp tablespace and misc. datafiles in middle Text files used for loading in center 3rd Disk Set - 6 72M disks RAID 1+0 2nd and 4th redo logs on outside Rollback tablespace and misc datafiles in middle /oracle (executables and some scripts) in center I was debating if there was any advantage in varying stripe sizes across the different disk sets (since I know Cary says redo logs like fine grained stripe sizes) but given the mix of uses for each that doesn't seem viable. Comments, suggestions or even productive questioning of my sanity would be appreciated. Thanks, Jay Miller = Paul Baumgartel Transcentive, Inc. www.transcentive.com __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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: 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
RE: JDBC application causes intermittent high load and latch wait
Don't really know. I'll pass this on to the developer and the main DBA on that box. Thanks! Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Wednesday, July 23, 2003 6:39 PM To: Multiple recipients of list ORACLE-L Jay - In Java parlance, bind variables == PreparedStatement. Is it possible that the Web server received a bunch of requests and flooded your DB? Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, July 23, 2003 5:15 PM To: Multiple recipients of list ORACLE-L Oracle 8.1.5.1 Solaris 2.6 Hi there, For the last few days one of our servers will suddenly experience ridiculously high load averages (50-65) and all applications grind to a halt. Restarting the database solves the problem and shutting down a specific apps servers solves the problem. This apps server generally has about 2-3 JDBC connections to the database. I just started looking at it today (it's not one of my databases) and when it recurred I started checking wait events. The top 10 wait events for about a 2 minute period were as follows: EVENTDelta Waits Delta Timeouts latch free 317376 185591 SQL*Net message from client 66435 0 SQL*Net message to client 66419 0 db file sequential read 21709 0 SQL*Net more data to client18066 0 file open 12230 rdbms ipc message 576 170 log file sync 519 1 log file parallel write 374 0 log file sequential read 343 0 It looks like a latch contention problem. By the time I drilled down to specific sessions that were experiencing the latch free wait the apps server had been restarted. My query showed most waiting on the process allocation latch but I don't know for sure that this was the latch that was causing the problem during the high load period. I prepared this query to run the next time the problem occurs in order to identify which latches are being waited on: select s.sid,s.username,s.program,s.status, se.event,se.total_waits,se.total_timeouts, se.time_waited,se.average_wait,sw.p2,v.name from v$session s, v$session_event se, v$session_wait sw,v$latch v where s.sid=se.sid and s.sid=sw.sid and sw.p2=l.latch# and s.status='ACTIVE' AND S.USERNAME IS NOT NULL and se.event='latch free'; Does this make sense? Any suggestions for other things to check? Some reading suggests that latch contention is often caused by not using bind variables and that increasing db_block_lru_latches (currently = 2) can alleviate the problem while the code is being fixed. But the question is why this problem suddenly happens and the problem doesn't go away until the apps server is shut down. The old version of the application (which didn't use JDBC) did not have this problem. I'm theorizing that perhaps JDBC handles its connections differently and after waiting for some period of time resends the transaction and keeps resending it faster than Oracle can clean things up? Has anyone experienced a similar problem or have other suggestions of where to go from here? Thanks, Jay Miller -- 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: 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).
JDBC application causes intermittent high load and latch waits
Oracle 8.1.5.1 Solaris 2.6 Hi there, For the last few days one of our servers will suddenly experience ridiculously high load averages (50-65) and all applications grind to a halt. Restarting the database solves the problem and shutting down a specific apps servers solves the problem. This apps server generally has about 2-3 JDBC connections to the database. I just started looking at it today (it's not one of my databases) and when it recurred I started checking wait events. The top 10 wait events for about a 2 minute period were as follows: EVENT Delta Waits Delta Timeouts latch free 317376 185591 SQL*Net message from client 66435 0 SQL*Net message to client 66419 0 db file sequential read 21709 0 SQL*Net more data to client 18066 0 file open 1223 0 rdbms ipc message 576 170 log file sync 519 1 log file parallel write 374 0 log file sequential read 343 0 It looks like a latch contention problem. By the time I drilled down to specific sessions that were experiencing the latch free wait the apps server had been restarted. My query showed most waiting on the process allocation latch but I don't know for sure that this was the latch that was causing the problem during the high load period. I prepared this query to run the next time the problem occurs in order to identify which latches are being waited on: select s.sid,s.username,s.program,s.status, se.event,se.total_waits,se.total_timeouts, se.time_waited,se.average_wait,sw.p2,v.name from v$session s, v$session_event se, v$session_wait sw,v$latch v where s.sid=se.sid and s.sid=sw.sid and sw.p2=l.latch# and s.status='ACTIVE' AND S.USERNAME IS NOT NULL and se.event='latch free'; Does this make sense? Any suggestions for other things to check? Some reading suggests that latch contention is often caused by not using bind variables and that increasing db_block_lru_latches (currently = 2) can alleviate the problem while the code is being fixed. But the question is why this problem suddenly happens and the problem doesn't go away until the apps server is shut down. The old version of the application (which didn't use JDBC) did not have this problem. I'm theorizing that perhaps JDBC handles its connections differently and after waiting for some period of time resends the transaction and keeps resending it faster than Oracle can clean things up? Has anyone experienced a similar problem or have other suggestions of where to go from here? Thanks, Jay Miller
RE: RULE hint
You also might try out first_rows hint instead of rule. Oracle keeps threatening to take the rule hint away. Jay -Original Message- Sent: Wednesday, July 09, 2003 7:24 PM To: Multiple recipients of list ORACLE-L Hi! It's generally good idea to review optimizer_index_caching and optimizer_index_cost_adj in CBO environments, but since you got parsing performance problem currently, I'd not look at them first. You can get elaborate information about those if you search for Tim Gorman's The Search for Intelligent Life in the Cost-Based Optimizer document (his site www.evdbt.com was down for some reason). optimizer_index_* parameters affect costs for index-accesses, but changing them shouldn't make much difference in parsing time (only case I can think of is that when making indexes very sexy using above parameters, then it might allow execution plan early elimination to cut off some plans earlier). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, July 10, 2003 1:59 AM John, I did not change these parameters from its defaults : They are currently set to : optimizer_index_caching = 0 optimizer_index_cost_adj = 100 optimizer_max_permutations = 8 Thanks, Bala. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 09, 2003 4:14 PM what are your optimizer_index_caching optimizer_index_cost_adj optimizer_max_permutations set to? [EMAIL PROTECTED] 7/9/2003 3:39:31 PM Few sql queries run fast (less than a second) when I use RULE hint and without RULE hint it takes 30 seconds. The queries are not that complicated, but joins 10 - 12 tables. I compared the plans between RULE hint and witout RULE hint, they are almost the same except join methods. Also, I checked the trace with 10046 event, and found out that without RULE hint, the parsing takes almost 30 seconds (when I run the same query for the second/third time it takes less than a second). Our developers ruled out of using bind variables as the roguewave's SourcePro C++ (Edition 5) libraries have serious bugs when they use bind variables (Please let me know if some of your developers have any fixes for this). I'm able to tune most of the queries by re-writing using UNION/EXISTS/in-line-view etc and get them to less than a second, for the rest I'm pushing RULE hint to the developers. I hate to use any hints as they are not scalable between versions/OS/patches etc. Do you guys have any idea of reducing parsing time or any suggestions= -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bala Regupathy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
Upgrading from 8.1.7 32 bit to 9.2.0.3 64 bit
Hi all, Has anyone done the upgrade from 8.1.7 32 bit to 9.2.0.3 64 bit? I'm trying to determine whether it's necessary to perform any other steps than the regular upgrade process. Note 62290.1 seems to imply that no other steps are required, but this is contradicted elsewhere and it's proving almost impossible to get a straight answer from Oracle. There's one hilarious Forum exchange on metalink (search on 32 bit Sahoo) where the tech manages to avoid saying anything other than read the documentation. So, any advice, experience, or pointers to other articles out there? Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Upgrading from 8.1.7 32 bit to 9.2.0.3 64 bit
Sorry, should have mentioned that I'm on Solaris 2.8. Thanks, Jay -Original Message- Sent: Friday, June 20, 2003 10:19 AM To: '[EMAIL PROTECTED]' Cc: Miller, Jay Jay, Just make sure that ur OS is 64-bit as well and go ahead as per the documentation. If ur OS is HP-UX, do not forget to set ur SHLIB_PATH and CLASSPATH as well. Cheers Samir Samir Sarkar Oracle DBA SchlumbergerSema Email : [EMAIL PROTECTED] Phone : +44 (0) 115 - 957 6028 EPABX : +44 (0) 115 - 957 6418 Ext. 76028 Fax : +44 (0) 115 - 957 6018 -Original Message- Sent: 20 June 2003 15:50 To: Multiple recipients of list ORACLE-L Hi all, Has anyone done the upgrade from 8.1.7 32 bit to 9.2.0.3 64 bit? I'm trying to determine whether it's necessary to perform any other steps than the regular upgrade process. Note 62290.1 seems to imply that no other steps are required, but this is contradicted elsewhere and it's proving almost impossible to get a straight answer from Oracle. There's one hilarious Forum exchange on metalink (search on 32 bit Sahoo) where the tech manages to avoid saying anything other than read the documentation. So, any advice, experience, or pointers to other articles out there? Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: Cannot allocate new log - checkpoint not complete
Correct. The only potential disadvantage is that recovery will take longer when bringing up the database after a crash. Jay Miller -Original Message- Sent: Thursday, April 03, 2003 9:39 AM To: Multiple recipients of list ORACLE-L I dissagree, they will be bigger but there will be less of them. If the amount of processed data does not change, I do not think changing the size of the redolog files should affect the total amount fo bytes to be backeup up Thanks for the recommendation anyway ;) Cheers, Fermin. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Stefan Jahnke Enviado el: jueves, 03 de abril de 2003 15:44 Para: Multiple recipients of list ORACLE-L Asunto: AW: Cannot allocate new log - checkpoint not complete Hi I would suggest to increase the redo log size. Doesn't effect you during daily operation, but prevents the database from hanging during nightly batches. No side effects I can think of (except for the fact that, of course, it will take you longer to backup the archived logs since the files are bigger, duh ;). Good luck Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Fermin Bernaus Berraondo [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 3. April 2003 10:04 An: Multiple recipients of list ORACLE-L Betreff: Cannot allocate new log - checkpoint not complete I think I am having problems with my redologs. Under normal circumstances no errors arise, but if I do a massive import of data as I was doing last night, this is what alertSID.log shows from time to time: Wed Apr 2 23:29:52 2003 Thread 1 advanced to log sequence 557295 Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:11 2003 Thread 1 cannot allocate new log, sequence 557296 Checkpoint not complete Current log# 3 seq# 557295 mem# 0: /baandata/oradata/baan/redobaan03.log Wed Apr 2 23:31:50 2003 In that exact time, everything freezes and the database is dead until a new redolog can be used. I have 3 redologs 50 Mb each. I've read that the error is because too much data is trying to get into the redologs and all of them are full, Oracle does not have the time to reuse a redolog and has to wait until the redolog is ready to be reused. So the solution seems to make these redolog files bigger or to create new ones. What are the side effects of one or the other? will performance under normal work be penalised? .. Fermín Bernaus Berraondo Dpto. de Informática SAMMIC, S.A. [EMAIL PROTECTED] http://www.sammic.com Telf. +34 - 943 157 331 Fax +34 - 943 151 276 .. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fermin Bernaus Berraondo 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: Stefan Jahnke 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
RE: dbshut script - shutdown or shutdown immediate
Here's how we do it: We have 2 cron jobs, one of which runs 10 minutes after the first. The first does a shutdown immediate. The second checks if oracle is running and, if so, does a shutdown abort. -Original Message- Sent: Thursday, April 03, 2003 2:24 PM To: Multiple recipients of list ORACLE-L -Original Message- From: Pardee, Roy E [ mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] ] I would have guessed that optimally, you'd try immediate first then abort if immediate takes too long. I've read that some people on the list have done this. I am curious as to how this is implemented. How long is too long? And how is this coded? I'm trying to think how you would write this with shell scripts for example. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Imaginary indexes created when setting CLOBs to null?
We've been testing a new application in development using CLOBS. The developer contacted me with following error: 1]: (Error): ORA-01654: unable to extend index YURIY.SYS_IL185466C00023$$ by 256 in tablespace TS_EMAIL_CLOB The odd things here are that a) It occurred while updating the CLOB column to null (why would setting something to null *increase* the size of an index?) b) The named index does not exist (implying it was something Oracle might have created on the fly?) I increased the tablespace size and the problem went away but I'm trying to figure out what happened. The tablespace is one assigned to the CLOB segment. Oracle 8.1.7.2 Solaris 2.6 Thanks, Jay Miller x48355 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: drop table vs truncate table -- performance
Interesting question. I'll throw out another guess that Oracle has extra checking to do to either retain the first extent or perhaps recreate it (whereas drop just gets rid of all extents)? Comparing the results of a SQL Trace or Event 10046 on drop and truncate could be interesting. Jay Miller -Original Message- Sent: Monday, March 10, 2003 5:21 PM To: Multiple recipients of list ORACLE-L Is it because, truncate has to reset the HWM? GovindanK --- gmei [EMAIL PROTECTED] wrote: Hi: Oracle 8173 on Sun Solaris 2.8. I am running a program that creates and drops tables repeatedly in a schema. The table dropping part looks like this: drop table ypd.t1 cascade constraints; drop table ypd.t2 cascade constraints; drop table ypd.t3 cascade constraints; drop table ypd.t4 cascade constraints; I thought I might be able to use truncate table ... and then drop table ... to make this process a bit faster. However the tests I did today showed otherwise: SQL select bytes from dba_segments where segment_name='SEQUENCE'; BYTES -- 1949810688 SQL create table ypd_owner.SEQUENCE1 2 tablespace ypd nologging as select * from mt.SEQUENCE; Table created. Elapsed: 00:04:272.50 SQL drop table ypd_owner.SEQUENCE1; Table dropped. Elapsed: 00:00:05.19 SQL create table ypd_owner.SEQUENCE1 2 tablespace ypd nologging as select * from mt.SEQUENCE; Table created. Elapsed: 00:04:275.85 SQL truncate table ypd_owner.SEQUENCE1; Table truncated. Elapsed: 00:00:06.99 SQL drop table ypd_owner.SEQUENCE1; Table dropped. Elapsed: 00:00:00.70 --- Tablspace ypd is LMT. Similar test on smaller table showed the same result drop table ... is faster than truncate table Any explanation? TIA. Guang -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: gmei 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). __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Govindan=20K?= 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: 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).
SQL*Net message to client Wait event with no SQL*Net connection
A developer/analyst was running 4 reports on our datawarehouse, connecting locally using SQL Plus from a telnet session on the Unix box. He then started 4 queries, spooling the output to the unix server (again, local). He was curious as to why the sessions were both taking so long and why they alternated between showing as Active and Inactive in v$session. When I checked v$session_wait for his sessions I saw that they were a mix of SQL*Net message from client and SQL*Net message to client Since he's not connecting through the listener, why would this wait event show up? There should be no network activity at all (I double checked that the SQL is not using any database links). Any ideas? Oracle 8.1.6.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Net message to client Wait event with no SQL*Net connec
Thanks Tom, I never knew that. Amazing how one can work with totally false assumptions for years and never have them trip you up until you expose your ignorance to the whole world :). Thanks! Jay -Original Message- Sent: Monday, March 10, 2003 1:57 PM To: '[EMAIL PROTECTED]' Cc: Miller, Jay connec tion Jay, from what I remember, *all* connections go thru SqlNet - even those that reside on the server. Oracle made this change a few years back (actually, quite a few years back) in preparing themselves for the internet world. and also so that a new release of the database does not require a complete re-compile of everything. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, March 10, 2003 1:04 PM To: Multiple recipients of list ORACLE-L connection A developer/analyst was running 4 reports on our datawarehouse, connecting locally using SQL Plus from a telnet session on the Unix box. He then started 4 queries, spooling the output to the unix server (again, local). He was curious as to why the sessions were both taking so long and why they alternated between showing as Active and Inactive in v$session. When I checked v$session_wait for his sessions I saw that they were a mix of SQL*Net message from client and SQL*Net message to client Since he's not connecting through the listener, why would this wait event show up? There should be no network activity at all (I double checked that the SQL is not using any database links). Any ideas? Oracle 8.1.6.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQL*Net message to client Wait event with no SQL*Net connec
Thanks Lisa, but I already checked that. I used to think this as well. But according to Rachel and Tom the listener is apparently used even if the @sid is not present. Glad I wasn't the only one laboring under that misapprehension :) Jay -Original Message- Sent: Monday, March 10, 2003 1:54 PM To: [EMAIL PROTECTED]; Miller, Jay connection Jay, if the connection was made with @ORACLE_SID (sqlplus user/[EMAIL PROTECTED]) then the listener is used. If the connection was made with the sid defined in the environment variable ORACLE_SID and without @ (sqlplus user/pw) the listener is not used. List please correct me if I'm wrong. Lisa Koivu Oracle Database Monkey Mama Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 Office: 954-935-4117 Fax:954-935-3639 Cell:954-683-4459 -Original Message- Sent: Monday, March 10, 2003 1:04 PM To: Multiple recipients of list ORACLE-L connection A developer/analyst was running 4 reports on our datawarehouse, connecting locally using SQL Plus from a telnet session on the Unix box. He then started 4 queries, spooling the output to the unix server (again, local). He was curious as to why the sessions were both taking so long and why they alternated between showing as Active and Inactive in v$session. When I checked v$session_wait for his sessions I saw that they were a mix of SQL*Net message from client and SQL*Net message to client Since he's not connecting through the listener, why would this wait event show up? There should be no network activity at all (I double checked that the SQL is not using any database links). Any ideas? Oracle 8.1.6.2 Solaris 2.6 Thanks, Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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: 8.1.7 Instance not creating an SGADEF file
It sounds like you're using code that checks for the existence of the sgadef file to see whether the instance was running. As part of our upgrade process we changed our code that did that (there was a fair amount of it) to check for the SMON process instead. HTH, Jay Miller -Original Message- Sent: Tuesday, February 18, 2003 9:59 AM To: Multiple recipients of list ORACLE-L Ron, This is not a problem due to the absence of sgadef file. This file was obsolete with 8i. Your problem seems to be Oracle environment /or Sql*Net related. Please check tnsnames.ora, sqlnet.ora, service names etc. on the client. Can you connect to any other instance from this client? FWIW: The sgadef file had following text in 8.0.x versions: This file is now obsolete. It will be removed in a future release. To determine whether an instance is up, you should check for the existence of the PMON process associated with the instance. HTH, - Kirti -Original Message- Sent: Tuesday, February 18, 2003 8:14 AM To: Multiple recipients of list ORACLE-L I have an 8.1.7 Oracle instance on a server that does not create an SGADEF file when it is started up. There are no errors in the log or trace files. As a result of this file not being created, I cannot connect to the database from a remote client. I get an error that says the instance is not running. I can connect to the instance from SVRMGRL on while logged onto the server. Any ideas why the file would not get created? Thanks! Ron Smith -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: 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: Count(*) last 30 seconds
Does it take the same amount of time if you run the count(*) a second time (i.e., might it reading the entire table from disk the first time)? Jay Miller -Original Message- Sent: Tuesday, February 11, 2003 12:09 PM To: Multiple recipients of list ORACLE-L Hi, 1. create unique index or primary key AND update statistics of the table 2. use count(1) instead of count(*) JP On Tuesday 11 February 2003 17:19, you wrote: Hi list, I issue a select count(*) from mytable and last 30 seconds. The table has 1,466,196 records and were loaded with a batch process, so they are in a countinous space. I consider that time exagerated. The TBS is LMT with a Uniform size of 128 MB. The block size is 8MB, version 9.2.0.1.0 in Windows 2000. Where should I start looking ??? TIA Ramon E. Estevez [EMAIL PROTECTED] 809-565-3121 -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Pruner 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: 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 connections hanging--Critical Production issue.
I thought this was fixed by version 8i, but this sounds a lot like an old bug that occurred when otrace was set on and the *.dat files got too big in $ORACLE_HOME/otrace/admin. You might want to check the date and filesize for these files. Good luck! Jay Miller x48355 -Original Message- Sent: Wednesday, January 29, 2003 3:29 AM To: Multiple recipients of list ORACLE-L I am running an Oracle v 8.1.7.3.0 version db on Sun solaris 2.6 and the problem I face is that all of a sudden all new connections to the database just hang [ the conn is not made ] and if at all a connection can be established using svrngrl [ only this ] I find that no queries can be executed [ all of them hang ].No alert messages, no trace files generated and the machine is low on usage.A database reboot solves the problem for a few hours .The number of processes accessing the database at point of problem is about 300 and this is well less than the processes parameter value.The machine has 4 G of Ram an SGA size close to 1G. Any ideas would be appreciated. -- This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Girijan Puthran 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: 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: Take Care of your DBAs
Ah, but do you play with other DBAs? Also 42 here, and enjoy physical exercise that doesn't require much in the way of coordination :). Hiking or gym - good. Skiing - well, I never tried since I have visions of myself in one of those full body casts... -Original Message- Sent: Thursday, January 30, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Average age for a DBA? I'm 42 and I am a jogger (3-4 miles a day), I play hoops. Sometimes I go rollerskating to Central Park, in New York City or skating. Did I mention that I enjoy playing Ping-Pong? -Original Message- From: Glenn Stauffer [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 30, 2003 1:01 PM To: Multiple recipients of list ORACLE-L Subject: RE: Take Care of your DBAs On Thu, 2003-01-30 at 10:09, Gogala, Mladen wrote: It's nice to have four seasons. As long as the temperature doesn't drop under 60 during any of those four seasons. Four feet of snow? Here in CT, we had few inches and the traffic collapsed. What do you drive? Sherman tank? -- If the temperature doesn't drop below 60, where do you go cross country skiing? To some, summer is a space between winters. Now, summer does make for a better commute - my hour and fifteen can quickly turn into three in winter! As for DBA's not exercising, I'd be interested in knowing if it is a generational thing. Depending on the season, I bicycle, kayak (sea), or ski for fun and, sometimes, exercise and do so relatively often. I probably see more people engaged in similar activities who are in their mid-late 30's, 40's and 50's than in their 20's and early 30's. I wonder what the average age is for Oracle DBA's? -- Glenn Stauffer Swarthmore College Swarthmore, PA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Glenn Stauffer 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: Gogala, Mladen 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: 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: books recommendations
Very strong recommendation for the Guy Harrison book. It's pretty much *the* SQL tuning book. -Original Message- Sent: Tuesday, January 28, 2003 5:19 PM To: Multiple recipients of list ORACLE-L We are buying books for DBA group and developers . It will be great if u can provide ur feedback about following books: Oracle SQL High-Performance Tuning (2nd Edition) by Guy Harrison High-Performance Oracle: Proven Methods for Achieving Optimum Performance and Availability [DOWNLOAD: ADOBE READER] by Geoff Ingram Oracle9i High-Performance Tuning with STATSPACK by Donald K. Burleson, Don Burleson Oracle9i RMAN Backup Recovery by Robert G. Freeman, Matthew Hart Oracle 9i New Features by Robert G. Freeman Oracle9i DBA Handbook by Kevin Loney, Marlene Theriault Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Harvinder Singh 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: 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: Take Care of your DBAs
shooting hoops? Just out of curiosity, how many people on the list have a group of DBAs at their company that they shoot hoops with? Some good points, some odd ones. I'll echo Patrice's sigh (as someone who enjoys both parts of the job). Jay -Original Message- Sent: Wednesday, January 29, 2003 7:34 AM To: Multiple recipients of list ORACLE-L Here's that development DBA alias again. sigh. Pat. -Original Message- Sent: Wednesday, January 29, 2003 7:54 AM To: Multiple recipients of list ORACLE-L Thanks for this article. I will forward to damagement. Dave -Original Message- Sent: Tuesday, January 28, 2003 3:24 PM To: Multiple recipients of list ORACLE-L http://careerlink.devx.com/articles/hc0199/hc0199.asp http://careerlink.devx.com/articles/hc0199/hc0199.asp Interesting article I stumbled across. Best quote: Stay Out of your DBA's Face!WELL PUT! Lisa Koivu Oracle Database Administrator Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
