Re: Snapshot too old in undo tablespace in 9i?
Daniel, I have just finished reading your document on UNdo Internals and Tims Cats, Dogs and ORA-1555s. Thanks for the documents they were both great. There is something I don't understand and I am not sure about it. You have said below: When a transaction is bound to an undo segment, it allocates a slot in the tx table. I thought that transactions were no longer bound to UNDO segments and this was one of the improvements in 9i. I have pasted an extract from Tims document: Into the future: Oracle9i UNDO tablespaces. As you may have observed, one of the reasons space management for rollback segments is so difficult is due to the fact that a transaction is assigned irrevocably to a single rollback segment. Each rollback segment can only handle a finite number of transactions (due to block-level contention for the transaction table in the header block), so there must be multiple rollback segments to handle potentially large numbers of transactions. UNDO tablespaces in Oracle9i allow an entire tablespace to become a single, large pool of undo blocks for use by any and all transactions. Instead of having available space carved up into many smaller rollback segments, a single transaction can utilize all of the space in the UNDO tablespace, if necessary. Many, many transactions can share that space also, because the controlling transaction table is no longer contained in a single database block, avoiding contention for this important resource. I guess I am jumping to the wrong assumption in Tims extract - can you clarify it for me. thanks, stephen Phone: 01737 27 5564 [EMAIL PROTECTED] Daniel Fink [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] un.com cc: Sent by: Subject: Re: Snapshot too old in undo tablespace in 9i? [EMAIL PROTECTED] ity.com 05/08/03 22:29 Please respond to ORACLE-L Abraham, Setting the retention time may not solve the problem. One of the ways that an ORA-1555 can be triggered is when the transaction table slot is overwritten. This is caused by having many small, serial transactions in the database while the export is running. In each undo segment (or rollback segment), there is a structure called the transaction(tx) table. This contains transaction - undo segment binding/status information. The number of slots is block-size dependent. I don't recall the exact numbers. When a transaction is bound to an undo segment, it allocates a slot in the tx table. This provides the links between the data/index block and the undo entries. If the data block points to a slot that has been reused, there is not a way to reconstruct the data, so it throws a 1555. The undo information may be preserved in the segments, but the link necessary has been lost. For illustration purposes, let's say you have 10 undo segments and each of them has a transaction table containing 40 slots. You have 400 slots available. If you have 100 transactions per minute (no more than 10 concurrently (so as to prevent new undo segments being created)), a slot will be reused every 4 minutes. If I have not sufficiently bored you, more detail can be found at www.optimaldba.com/library.html. Look for the documents on Undo Internals
Re: Snapshot too old in undo tablespace in 9i?
Daniel, that make sense , thanks. Stephen Hodgkinson Oracle DBA Total Gas Power Ltd Phone: 01737 27 5564 [EMAIL PROTECTED] Daniel Fink [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] un.com cc: Sent by: Subject: Re: Snapshot too old in undo tablespace in 9i? [EMAIL PROTECTED] ity.com 15/08/03 15:26 Please respond to ORACLE-L Stephen, Tim's statement is correct, but can be construed incorrectly if you read it and think of TEMP segments. AUM still uses undo segments (same basic structure as rollback segments). However, one of the space management steps is to allow an undo segment to 'steal' extents from another undo segment. This means that all extents (other than those currently in use or extent 0 (and perhaps 1)) are available to any other segment should it require them. A single transaction cannot start in undo segment #1, allocate space in it and then move to undo segment #2. However, the transaction can cause undo segment #1 to allocate space currently allocated to #2. I hope this clears up the disparity between the statements. Daniel [EMAIL PROTECTED] wrote: Daniel, I have just finished reading your document on UNdo Internals and Tims Cats, Dogs and ORA-1555s. Thanks for the documents they were both great. There is something I don't understand and I am not sure about it. You have said below: When a transaction is bound to an undo segment, it allocates a slot in the tx table. I thought that transactions were no longer bound to UNDO segments and this was one of the improvements in 9i. I have pasted an extract from Tims document: Into the future: Oracle9i UNDO tablespaces. As you may have observed, one of the reasons space management for rollback segments is so difficult is due to the fact that a transaction is assigned irrevocably to a single rollback segment. Each rollback segment can only handle a finite number of transactions (due to block-level contention for the transaction table in the header block), so there must be multiple rollback segments to handle potentially large numbers of transactions. UNDO tablespaces in Oracle9i allow an entire tablespace to become a single, large pool of undo blocks for use by any and all transactions. Instead of having available space carved up into many smaller rollback segments, a single transaction can utilize all of the space in the UNDO tablespace, if necessary. Many, many transactions can share that space also, because the controlling transaction table is no longer contained in a single database block, avoiding contention for this important resource. I guess I am jumping to the wrong assumption in Tims extract - can you clarify it for me. thanks, stephen Phone: 01737 27 5564 [EMAIL PROTECTED] ** This insert confirms that this email message and all associated attachments have been swept by TotalFinaElf using Network Associates VirusScan for the presence of computer viruses during the receipt of this message. ** (See attached file: daniel.fink.vcf
Re: 9i - Dynamic SGA - SGA_MAX_SIZE
Oracle have changed there response when I pointed out that ask Tom indicated that different operating systems appear to perform differently Oracle's response is below and from all your replies this would appear to be the case. Thanks, for your help. stephen ** *** Yes, on most platforms the shared memory would be pre-allocated for the SGA. equivalent to SGFA_MAX_SIZE. Solaris with Dynamic ISM enabled is one exception. There may be others. It's not 100% obvious if this is really wasting resources. If the memory is never being referenced it may not have to be resident in physical memory (though some platforms do try to keep the SGA pinned in memory). ** *** Phone: 01737 27 5564 [EMAIL PROTECTED] Mladen Gogala [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] hia.net cc: Sent by: Subject: Re: 9i - Dynamic SGA - SGA_MAX_SIZE [EMAIL PROTECTED] ity.com 03/08/03 05:19 Please respond to ORACLE-L Kirti, problem is in the malloc mechanism. The subroutine to free the memory doesn't return it to the OS, it returns it to the allocation pool instead. As a consequence, when oracle does free, its address space is not going to go down. You realize, of course, that malloc, calloc, realloc and free are the only options, because of their portability. There are low level allocation routines like brk() to increase the address space, but unaware of any routine to decrease the address space. Here is the manual page for the library subroutine called mallopt, which is mostly obsolete but still exists on HP-UX. The mechanism remained the same, but the control was taken away from the users. Manual page does illustrate the mechanism, though. malloc(3C) malloc(3C) NAME malloc(), free(), realloc(), calloc(), mallopt(), mallinfo(), memorymap() - main memory allocator SYNOPSIS #include stdlib.h void *malloc(size_t size); void *calloc(size_t nelem, size_t elsize); void *realloc(void *ptr, size_t size); void
9i - Dynamic SGA - SGA_MAX_SIZE
Hi, does anybody have any experience with setting the SGA_MAX_SIZE in 9i. I assumed the purpose of this parameter was that SGA would grow as requested to that limit. Example: You could configure your SGA to be 80M Set the SGA_MAX_SIZE to be 250M. I would have expected oracle to acquire 80M of memory from the UNIX machine. In fact using ipcs you can see that oracle will always acquire the value of SGA_MAX_SIZE. It acquires the extra space in the Variable Size of the SGA Sga_max_size=75m. Show sga: Total System Global Area 77041728 bytes Fixed Size 733248 bytes Variable Size 41943040 bytes Database Buffers 33554432 bytes Redo Buffers 811008 bytes sga_max_size=110M and keep everything else the same. show sga: Total System Global Area 114790680 bytes Fixed Size 733464 bytes Variable Size 79691776 bytes Database Buffers 33554432 bytes Redo Buffers 811008 bytes Database mounted. Database opened. I have raised a lengthy call on Metalink and the consultants are convinced this is normal behaviour and what you would expect. Do people agree with the metalink consultants? Maybe my expectations were to high but I thought a dynamic sga would mean I could change the amount of memory acquired by the UNIX box. All opinions welcome. I am on tru64 platform - 9.2.0.3.0 Thanks, Stephen ** This email contains information which is confidential. It is for the exclusive use of the addressee. If you are not an addressee please contact us immediately on 01737 275500. Please note that any distribution, copying or use of this communication or its contents is prohibited. This footnote also confirms that this email message and all associated attachments have been swept by Network Associates VirusScan for the presence of computer viruses. ** -- 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: 9i - Dynamic SGA - SGA_MAX_SIZE
Tanel, I have done this. ipcs -m would not give me the size of the memory acquired on my system so I used ipcs -a I have also looked into the ps vax Which gives more detail: Currently this shows 199M reserved to the SGA EM9I /oracle/app/oracle/product/9.2.0/dbs ps avx|head -1 PID TTY S TIME SL PAGEIN VSZ RSS %CPU %MEM COMMAND OEM9I /oracle/app/oracle/product/9.2.0/dbs ps avx|grep OEM9I 472016 ?? S0:00.252 3 199M 6.2M 0.0 0.4 ora_dbw0_OEM9I 481099 ?? I0:00.20 32 2 198M 5.2M 0.0 0.3 ora_d000_OEM9I 481163 ?? S0:00.292 33 196M 3.8M 0.0 0.2 ora_pmon_OEM9I 481141 ?? S0:00.182 8 196M 3.6M 0.0 0.2 ora_s000_OEM9I 480555 ?? S0:01.49 19125 195M 2.9M 0.0 0.2 ora_qmn0_OEM9I 481126 ?? I0:00.66 184 41 195M 2.9M 0.0 0.2 ora_smon_OEM9I 481159 ?? S0:00.253 34 199M 2.8M 0.0 0.2 ora_lgwr_OEM9I 480962 ?? S0:00.601 5 195M 2.7M 0.0 0.2 ora_cjq0_OEM9I 481151 ?? I0:00.20 748 2 195M 2.7M 0.0 0.2 ora_reco_OEM9I 481148 ?? S0:00.432 2 195M 2.5M 0.0 0.2 ora_ckpt_OEM9I 481909 pts/22 S + 0:00.010 0 2.28M 232K 0.0 0.0 grep OEM9I I reduce the SGA_MAX_SIZE by 30M (keep all other parameters the same ) and the memory acquired on the OS drops by 30M 481979 ?? S0:00.252 0 167M 6.4M 0.0 0.4 ora_dbw0_OEM9I 481988 ?? S0:00.19 19 0 166M 5.2M 0.0 0.3 ora_d000_OEM9I 482000 ?? S0:00.191 0 164M 3.8M 0.0 0.2 ora_pmon_OEM9I 482040 ?? S0:00.17 19 0 164M 3.6M 0.0 0.2 ora_s000_OEM9I 481976 ?? S0:00.667 0 163M 2.9M 0.3 0.2 ora_smon_OEM9I 482004 ?? S0:00.222 1 167M 2.8M 0.0 0.2 ora_lgwr_OEM9I 481946 ?? S0:00.202 0 163M 2.7M 0.1 0.2 ora_cjq0_OEM9I 482024 ?? S0:00.172 0 163M 2.5M 0.0 0.2 ora_ckpt_OEM9I 481998 ?? S0:00.16 17 0 163M 2.4M 0.0 0.2 ora_reco_OEM9I 482030 ?? S0:00.16 19 0 163M 2.4M 0.0 0.2 ora_qmn0_OEM9I Oracle says this is what you would expect. It will grab the entire value of SGA_MAX_SIZE. This certainly seems to be correct I just think it is stupid. Why grab the memory from the os but never use it. stephen. [EMAIL PROTECTED] Tanel Poder tanel.poder.00 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: Sent by: Subject: Re: 9i - Dynamic SGA - SGA_MAX_SIZE [EMAIL PROTECTED] ty.com 01/08/03 12:19 Please respond to ORACLE-L Hi! You should check mem usage from OS instead. Using ipcs -m for example. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 01, 2003 12:54 PM Hi, does anybody have any experience with setting the SGA_MAX_SIZE in 9i. I assumed the purpose of this parameter was that SGA would grow as requested to that limit.
RE: 9i - Dynamic SGA - SGA_MAX_SIZE
Venu, this does help and this is what the guys on metalink said. What I do not understand - what a waste. If I have to grab 100M initially on the OS side I may aswell use it. In my metalink tar I quoted many references in the documentation: E.G. In the Database Administration guide: Chapter 2: 'The size of the SGA is dynamic, and can grow or shrink by dynamically altering these parameters.' Now I would say grabing the SGA_MAX_SIZE for the entire uptime of the database is not what I would call dynamic. I can't see any growing or shrinking in SGA size. Stephen Hodgkinson Oracle DBA TotalFina Elf Gas Power Ltd Phone: 01737 27 5564 [EMAIL PROTECTED] Venu Gopal [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] pro.com cc: Sent by: Subject: RE: 9i - Dynamic SGA - SGA_MAX_SIZE [EMAIL PROTECTED] ity.com 01/08/03 12:14 Please respond to ORACLE-L Stephen, That's the way it's meant to work. This is how Oracle guarantees that you can grow upto the value of SGA_MAX_SIZE. Look at the case below: DAY 1: you have your sga_max set to 100m but you are currently using only 50m of it. DAY 2: you want to use all 100m and you try to increase the sga size, but some other app has used rest of the physical memory and there is no sufficient memory available, at this stage oracle has to error out saying 'insufficient memory', but in reality it does not!! In reality: On DAY 1, Oracle tries to occupy all 100m in the memory so that it doesn't have any issues in future... this way you have 100m reserved for oracle, use it whenever you want to use it. Hope this helps you... ~Venu -Original Message- [EMAIL PROTECTED] Sent: Friday, August 01, 2003 3:24 PM To: Multiple recipients of list ORACLE-L Hi, does anybody have any experience with setting the SGA_MAX_SIZE in 9i. I assumed the purpose of this parameter was that SGA would grow as requested to that limit. Example: You could configure your SGA to be 80M Set the SGA_MAX_SIZE to be 250M. I would have expected oracle to acquire 80M of memory from the UNIX machine. In fact using ipcs you can see that oracle will always acquire the value of SGA_MAX_SIZE. It acquires the extra space in the Variable Size of the SGA Sga_max_size=75m. Show sga: Total System Global Area 77041728 bytes Fixed Size 733248 bytes
SQL question - crosstab in oracle
Hi I have a table with the following structure. CHECK_DATE DATE PHYRDS NUMBER(20) PHYWRTSNUMBER(20) PHYBLKRD NUMBER(20) PHYBLKWRT NUMBER(20) FILE# NUMBER FILE_NAME VARCHAR2(120) This records the IO on the different datafiles every 3 minutes. I want to run a report on this table but I want the output to display on file# as a heading.Similar to iostat on UNIX. So my output might be: Check DATE FIle1 File2 File3 etc... PHYRDS PHYWRTS PHYRDS PHYWRTS 11:14:341270820218 14 11:18:261303821118 14 11:33:441429024318 14 11:40:451500824518 14 11:45:371545924918 14 11:48:381577625018 14 11:51:371586425118 14 11:54:371602925418 14 11:57:391621025418 14 12:00:401631125418 14 12:03:401643925618 14 I could do this as a bit of plsq but Is it possible to generate this report with one bit of sql. (views,inline views, decodes. etc) thanks stephen ** This email contains information which is confidential. It is for the exclusive use of the addressee. If you are not an addressee please contact us immediately on 01737 275500. Please note that any distribution, copying or use of this communication or its contents is prohibited. This footnote also confirms that this email message and all associated attachments have been swept by Network Associates VirusScan for the presence of computer viruses. ** -- 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: Performance
17:00 get there for 17:50 Stephen Hodgkinson Oracle DBA TotalFina Elf Gas Power Ltd Phone: 01737 27 5564 [EMAIL PROTECTED] ** This email contains information which is confidential. It is for the exclusive use of the addressee. If you are not an addressee please contact us immediately on 01737 275500. Please note that any distribution, copying or use of this communication or its contents is prohibited. This footnote also confirms that this email message and all associated attachments have been swept by Network Associates VirusScan for the presence of computer viruses. ** -- 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).
