RE: Shared Pool fragmentation
Humm, Missed the middle post here. It's a time & labor tracking system. They have what they call a large package (28K from Brian post) that they claim must be loaded multiple times & is giving them an intermittent ORA-4031 and/or ORA-4045 on the web server. Problem is that the wall clocks and the PowerBuilder admin tool don't have the problem. What I also need with this app is a good book on MicroSlop Transaction server and Distributed Transaction Coordinator. These folks are using it with the web server & they are clueless. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, January 14, 2004 4:30 PM To: Multiple recipients of list ORACLE-L Hmm..reminds me of a MetaLink thread where I actually saw an Oracle Support analyst claim that it's not uncommon for large OLTP systems to have 2-4GB shared poolsyes, that's 2-4 Gigabytes. I wasn't actually sure how to respond to that, so, I just sort of let it drop. I mean, what do you say? -Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Unknown -Original Message- Sent: Wednesday, January 14, 2004 4:09 PM To: Multiple recipients of list ORACLE-L wa! what kind of application is it? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 14, 2004 3:29 PM > John, > > THANKS A TON!!! I've got a vendor trying to convince my boss that their application needs to be on a separate server with a 1GB shared pool. Now I know these guys are blowing snow better than any SnowKing, but I needed some help proving it. > > BTW: For you southern, snow unaware, a SnowKing is a snow blower of the highest degree. > > Dick Goulet > Senior Oracle DBA > Oracle Certified 8i DBA > > -Original Message- > Sent: Tuesday, January 13, 2004 6:34 PM > To: Multiple recipients of list ORACLE-L > > > Rick, > > I forgot about shared_pool_reserved_size and the min_alloc parameter (hidden > since 8i). See Note 146599.1 Diagnosing and Resolving Error ORA-04031. > > John > > >-Original Message- > >From: John Kanagaraj [mailto:[EMAIL PROTECTED] > >Sent: Tuesday, January 13, 2004 2:59 PM > >To: Multiple recipients of list ORACLE-L > >Subject: RE: Shared Pool fragmentation > > > > > >Rick, > > > >I think the best answer is 'know thy application'. And in > >this, knowledge of > >bind var vs hardcoded value usage, looking at V$SQL and > >V$SQLAREA, the ratio > >(!!) of 'parse count (hard)' to 'parse count (total)', pinning of > >packages/sequences, etc., can help... > > > >You cannot actually 'catch' a 4031 before it occurs, but you can always > >straighten things out before it occurs. I have found that a > >combination of > >pinning Packages/Sequences followed by judicious (once in a > >while) use of > >shared pool flush helps. Of course, the shared pool has to be correctly > >sized - too much and you waste time latching and memory, too > >little and you > >_might_ run into 4031. Sizing shared pool is an art that has a little > >science behind it - science that involves understanding and > >using values > >from X$KGLOB and X$KSMSP and your application > > > >OTOH, I have seen good results with a flush shared pool during > >quiet times > >for non-bind hungry 3rd party apps... See below (script > >courtersy Steve!) - > >the number of chunks has dropped dramatically freeing up > >largish globs of > >shared pool that would otherwise have to be freed up when a > >largish object > >(in this case > 15456 bytes) has to load. As well, you will > >see that the > >number of 'freeabl' chunks (x$ksmsp.ksmchcls) comes down > >drastically as the > >system frees up 'freeable' chunks ahead of time, reducing the chance of > >4031s > > > >My (very limited) understanding is that when a package/cursor > >has to load > >and a large-enough chunk of shared pool memory is not free, > >then the kernel > >will try and flush out the 'freeable' (not in use) memory and > >merge adjacent > >free chunks. If this still does not staisfy the memory > >requirements, then a > >4031 is signalled/ The 'alter system flush shared pool' > >performs a manual > >flush instead, ahead of time and could (possibly) prevent a 4031 ... > > > >John Kanagaraj > >DB Soft Inc > >P
RE: Shared Pool fragmentation
Hmm..reminds me of a MetaLink thread where I actually saw an Oracle Support analyst claim that it's not uncommon for large OLTP systems to have 2-4GB shared poolsyes, that's 2-4 Gigabytes. I wasn't actually sure how to respond to that, so, I just sort of let it drop. I mean, what do you say? -Mark Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI "Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Unknown -Original Message- Sent: Wednesday, January 14, 2004 4:09 PM To: Multiple recipients of list ORACLE-L wa! what kind of application is it? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 14, 2004 3:29 PM > John, > > THANKS A TON!!! I've got a vendor trying to convince my boss that their application needs to be on a separate server with a 1GB shared pool. Now I know these guys are blowing snow better than any SnowKing, but I needed some help proving it. > > BTW: For you southern, snow unaware, a SnowKing is a snow blower of the highest degree. > > Dick Goulet > Senior Oracle DBA > Oracle Certified 8i DBA > > -Original Message- > Sent: Tuesday, January 13, 2004 6:34 PM > To: Multiple recipients of list ORACLE-L > > > Rick, > > I forgot about shared_pool_reserved_size and the min_alloc parameter (hidden > since 8i). See Note 146599.1 Diagnosing and Resolving Error ORA-04031. > > John > > >-Original Message- > >From: John Kanagaraj [mailto:[EMAIL PROTECTED] > >Sent: Tuesday, January 13, 2004 2:59 PM > >To: Multiple recipients of list ORACLE-L > >Subject: RE: Shared Pool fragmentation > > > > > >Rick, > > > >I think the best answer is 'know thy application'. And in > >this, knowledge of > >bind var vs hardcoded value usage, looking at V$SQL and > >V$SQLAREA, the ratio > >(!!) of 'parse count (hard)' to 'parse count (total)', pinning of > >packages/sequences, etc., can help... > > > >You cannot actually 'catch' a 4031 before it occurs, but you can always > >straighten things out before it occurs. I have found that a > >combination of > >pinning Packages/Sequences followed by judicious (once in a > >while) use of > >shared pool flush helps. Of course, the shared pool has to be correctly > >sized - too much and you waste time latching and memory, too > >little and you > >_might_ run into 4031. Sizing shared pool is an art that has a little > >science behind it - science that involves understanding and > >using values > >from X$KGLOB and X$KSMSP and your application > > > >OTOH, I have seen good results with a flush shared pool during > >quiet times > >for non-bind hungry 3rd party apps... See below (script > >courtersy Steve!) - > >the number of chunks has dropped dramatically freeing up > >largish globs of > >shared pool that would otherwise have to be freed up when a > >largish object > >(in this case > 15456 bytes) has to load. As well, you will > >see that the > >number of 'freeabl' chunks (x$ksmsp.ksmchcls) comes down > >drastically as the > >system frees up 'freeable' chunks ahead of time, reducing the chance of > >4031s > > > >My (very limited) understanding is that when a package/cursor > >has to load > >and a large-enough chunk of shared pool memory is not free, > >then the kernel > >will try and flush out the 'freeable' (not in use) memory and > >merge adjacent > >free chunks. If this still does not staisfy the memory > >requirements, then a > >4031 is signalled/ The 'alter system flush shared pool' > >performs a manual > >flush instead, ahead of time and could (possibly) prevent a 4031 ... > > > >John Kanagaraj > >DB Soft Inc > >Phone: 408-970-7002 (W) > > > >Listen to great, commercial-free christian music 24x7x365 at > >http://www.klove.com > > > >** The opinions and facts contained in this message are > >entirely mine and do > >not reflect those of my employer or customers ** > > > >08:35:00 SQL> @shared_pool_free_lists > > > >BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST > >-- -- --- -- > > 01089784 23488 46 76 > > 1 3941364656 84140 > > 2 6812843678 185268 > > 3 315504
Re: Shared Pool fragmentation
wa! what kind of application is it? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 14, 2004 3:29 PM > John, > > THANKS A TON!!! I've got a vendor trying to convince my boss that their application needs to be on a separate server with a 1GB shared pool. Now I know these guys are blowing snow better than any SnowKing, but I needed some help proving it. > > BTW: For you southern, snow unaware, a SnowKing is a snow blower of the highest degree. > > Dick Goulet > Senior Oracle DBA > Oracle Certified 8i DBA > > -Original Message- > Sent: Tuesday, January 13, 2004 6:34 PM > To: Multiple recipients of list ORACLE-L > > > Rick, > > I forgot about shared_pool_reserved_size and the min_alloc parameter (hidden > since 8i). See Note 146599.1 Diagnosing and Resolving Error ORA-04031. > > John > > >-Original Message- > >From: John Kanagaraj [mailto:[EMAIL PROTECTED] > >Sent: Tuesday, January 13, 2004 2:59 PM > >To: Multiple recipients of list ORACLE-L > >Subject: RE: Shared Pool fragmentation > > > > > >Rick, > > > >I think the best answer is 'know thy application'. And in > >this, knowledge of > >bind var vs hardcoded value usage, looking at V$SQL and > >V$SQLAREA, the ratio > >(!!) of 'parse count (hard)' to 'parse count (total)', pinning of > >packages/sequences, etc., can help... > > > >You cannot actually 'catch' a 4031 before it occurs, but you can always > >straighten things out before it occurs. I have found that a > >combination of > >pinning Packages/Sequences followed by judicious (once in a > >while) use of > >shared pool flush helps. Of course, the shared pool has to be correctly > >sized - too much and you waste time latching and memory, too > >little and you > >_might_ run into 4031. Sizing shared pool is an art that has a little > >science behind it - science that involves understanding and > >using values > >from X$KGLOB and X$KSMSP and your application > > > >OTOH, I have seen good results with a flush shared pool during > >quiet times > >for non-bind hungry 3rd party apps... See below (script > >courtersy Steve!) - > >the number of chunks has dropped dramatically freeing up > >largish globs of > >shared pool that would otherwise have to be freed up when a > >largish object > >(in this case > 15456 bytes) has to load. As well, you will > >see that the > >number of 'freeabl' chunks (x$ksmsp.ksmchcls) comes down > >drastically as the > >system frees up 'freeable' chunks ahead of time, reducing the chance of > >4031s > > > >My (very limited) understanding is that when a package/cursor > >has to load > >and a large-enough chunk of shared pool memory is not free, > >then the kernel > >will try and flush out the 'freeable' (not in use) memory and > >merge adjacent > >free chunks. If this still does not staisfy the memory > >requirements, then a > >4031 is signalled/ The 'alter system flush shared pool' > >performs a manual > >flush instead, ahead of time and could (possibly) prevent a 4031 ... > > > >John Kanagaraj > >DB Soft Inc > >Phone: 408-970-7002 (W) > > > >Listen to great, commercial-free christian music 24x7x365 at > >http://www.klove.com > > > >** The opinions and facts contained in this message are > >entirely mine and do > >not reflect those of my employer or customers ** > > > >08:35:00 SQL> @shared_pool_free_lists > > > >BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST > >-- -- --- -- > > 01089784 23488 46 76 > > 1 3941364656 84140 > > 2 6812843678 185268 > > 3 315504 875 360524 > > 449019527300 671 1036 > > 561588964099 1502 2060 > > 655465161966 2821 4048 > > 71125720 263 4280 7624 > > 8 989584 101 9797 15456 > > > >9 rows selected. > > > >08:35:29 SQL> alter system flush shared_pool; > > > >System altered. > > > >08:36:32 SQL> @shared_pool_free_lists > > > >BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST &
RE: Shared Pool fragmentation
John, THANKS A TON!!! I've got a vendor trying to convince my boss that their application needs to be on a separate server with a 1GB shared pool. Now I know these guys are blowing snow better than any SnowKing, but I needed some help proving it. BTW: For you southern, snow unaware, a SnowKing is a snow blower of the highest degree. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Tuesday, January 13, 2004 6:34 PM To: Multiple recipients of list ORACLE-L Rick, I forgot about shared_pool_reserved_size and the min_alloc parameter (hidden since 8i). See Note 146599.1 Diagnosing and Resolving Error ORA-04031. John >-Original Message- >From: John Kanagaraj [mailto:[EMAIL PROTECTED] >Sent: Tuesday, January 13, 2004 2:59 PM >To: Multiple recipients of list ORACLE-L >Subject: RE: Shared Pool fragmentation > > >Rick, > >I think the best answer is 'know thy application'. And in >this, knowledge of >bind var vs hardcoded value usage, looking at V$SQL and >V$SQLAREA, the ratio >(!!) of 'parse count (hard)' to 'parse count (total)', pinning of >packages/sequences, etc., can help... > >You cannot actually 'catch' a 4031 before it occurs, but you can always >straighten things out before it occurs. I have found that a >combination of >pinning Packages/Sequences followed by judicious (once in a >while) use of >shared pool flush helps. Of course, the shared pool has to be correctly >sized - too much and you waste time latching and memory, too >little and you >_might_ run into 4031. Sizing shared pool is an art that has a little >science behind it - science that involves understanding and >using values >from X$KGLOB and X$KSMSP and your application > >OTOH, I have seen good results with a flush shared pool during >quiet times >for non-bind hungry 3rd party apps... See below (script >courtersy Steve!) - >the number of chunks has dropped dramatically freeing up >largish globs of >shared pool that would otherwise have to be freed up when a >largish object >(in this case > 15456 bytes) has to load. As well, you will >see that the >number of 'freeabl' chunks (x$ksmsp.ksmchcls) comes down >drastically as the >system frees up 'freeable' chunks ahead of time, reducing the chance of >4031s > >My (very limited) understanding is that when a package/cursor >has to load >and a large-enough chunk of shared pool memory is not free, >then the kernel >will try and flush out the 'freeable' (not in use) memory and >merge adjacent >free chunks. If this still does not staisfy the memory >requirements, then a >4031 is signalled/ The 'alter system flush shared pool' >performs a manual >flush instead, ahead of time and could (possibly) prevent a 4031 ... > >John Kanagaraj >DB Soft Inc >Phone: 408-970-7002 (W) > >Listen to great, commercial-free christian music 24x7x365 at >http://www.klove.com > >** The opinions and facts contained in this message are >entirely mine and do >not reflect those of my employer or customers ** > >08:35:00 SQL> @shared_pool_free_lists > >BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST >-- -- --- -- > 01089784 23488 46 76 > 1 3941364656 84140 > 2 6812843678 185268 > 3 315504 875 360524 > 449019527300 671 1036 > 561588964099 1502 2060 > 655465161966 2821 4048 > 71125720 263 4280 7624 > 8 989584 101 9797 15456 > >9 rows selected. > >08:35:29 SQL> alter system flush shared_pool; > >System altered. > >08:36:32 SQL> @shared_pool_free_lists > >BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST >-- -- --- -- > 0 14364 330 43 76 > 1 6528 76 85140 > 6 3964 1 3964 3964 > 9 29580 129580 29580 >105028636 10348821 65436 >11 13860744 15092404 130872 >12 32192980 173 186086 261016 >13 64490864 172 374946 522764 >14 83609184 112 7465101048432 >15 79829220 57 14005122068384 >16 38149220 14
RE: Shared Pool fragmentation
Rick, I forgot about shared_pool_reserved_size and the min_alloc parameter (hidden since 8i). See Note 146599.1 Diagnosing and Resolving Error ORA-04031. John >-Original Message- >From: John Kanagaraj [mailto:[EMAIL PROTECTED] >Sent: Tuesday, January 13, 2004 2:59 PM >To: Multiple recipients of list ORACLE-L >Subject: RE: Shared Pool fragmentation > > >Rick, > >I think the best answer is 'know thy application'. And in >this, knowledge of >bind var vs hardcoded value usage, looking at V$SQL and >V$SQLAREA, the ratio >(!!) of 'parse count (hard)' to 'parse count (total)', pinning of >packages/sequences, etc., can help... > >You cannot actually 'catch' a 4031 before it occurs, but you can always >straighten things out before it occurs. I have found that a >combination of >pinning Packages/Sequences followed by judicious (once in a >while) use of >shared pool flush helps. Of course, the shared pool has to be correctly >sized - too much and you waste time latching and memory, too >little and you >_might_ run into 4031. Sizing shared pool is an art that has a little >science behind it - science that involves understanding and >using values >from X$KGLOB and X$KSMSP and your application > >OTOH, I have seen good results with a flush shared pool during >quiet times >for non-bind hungry 3rd party apps... See below (script >courtersy Steve!) - >the number of chunks has dropped dramatically freeing up >largish globs of >shared pool that would otherwise have to be freed up when a >largish object >(in this case > 15456 bytes) has to load. As well, you will >see that the >number of 'freeabl' chunks (x$ksmsp.ksmchcls) comes down >drastically as the >system frees up 'freeable' chunks ahead of time, reducing the chance of >4031s > >My (very limited) understanding is that when a package/cursor >has to load >and a large-enough chunk of shared pool memory is not free, >then the kernel >will try and flush out the 'freeable' (not in use) memory and >merge adjacent >free chunks. If this still does not staisfy the memory >requirements, then a >4031 is signalled/ The 'alter system flush shared pool' >performs a manual >flush instead, ahead of time and could (possibly) prevent a 4031 ... > >John Kanagaraj >DB Soft Inc >Phone: 408-970-7002 (W) > >Listen to great, commercial-free christian music 24x7x365 at >http://www.klove.com > >** The opinions and facts contained in this message are >entirely mine and do >not reflect those of my employer or customers ** > >08:35:00 SQL> @shared_pool_free_lists > >BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST >-- -- --- -- > 01089784 23488 46 76 > 1 3941364656 84140 > 2 6812843678 185268 > 3 315504 875 360524 > 449019527300 671 1036 > 561588964099 1502 2060 > 655465161966 2821 4048 > 71125720 263 4280 7624 > 8 989584 101 9797 15456 > >9 rows selected. > >08:35:29 SQL> alter system flush shared_pool; > >System altered. > >08:36:32 SQL> @shared_pool_free_lists > >BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST >-- -- --- -- > 0 14364 330 43 76 > 1 6528 76 85140 > 6 3964 1 3964 3964 > 9 29580 129580 29580 >105028636 10348821 65436 >11 13860744 15092404 130872 >12 32192980 173 186086 261016 >13 64490864 172 374946 522764 >14 83609184 112 7465101048432 >15 79829220 57 14005122068384 >16 38149220 14 27249443705320 > >11 rows selected. > >-Original Message- >Sent: Tuesday, January 13, 2004 9:34 AM >To: Multiple recipients of list ORACLE-L > > >Is there a way to catch shared_pool fragmentation before you >get the 4031 >errors? I have looked at Steve Adams site which has scripts >to show the >free lists chunks in the shared pool. At what point do I know >that it is >fragmented too much? I know that I can prevent this by using bind >variables,
RE: Shared Pool fragmentation
Rick, I think the best answer is 'know thy application'. And in this, knowledge of bind var vs hardcoded value usage, looking at V$SQL and V$SQLAREA, the ratio (!!) of 'parse count (hard)' to 'parse count (total)', pinning of packages/sequences, etc., can help... You cannot actually 'catch' a 4031 before it occurs, but you can always straighten things out before it occurs. I have found that a combination of pinning Packages/Sequences followed by judicious (once in a while) use of shared pool flush helps. Of course, the shared pool has to be correctly sized - too much and you waste time latching and memory, too little and you _might_ run into 4031. Sizing shared pool is an art that has a little science behind it - science that involves understanding and using values from X$KGLOB and X$KSMSP and your application OTOH, I have seen good results with a flush shared pool during quiet times for non-bind hungry 3rd party apps... See below (script courtersy Steve!) - the number of chunks has dropped dramatically freeing up largish globs of shared pool that would otherwise have to be freed up when a largish object (in this case > 15456 bytes) has to load. As well, you will see that the number of 'freeabl' chunks (x$ksmsp.ksmchcls) comes down drastically as the system frees up 'freeable' chunks ahead of time, reducing the chance of 4031s My (very limited) understanding is that when a package/cursor has to load and a large-enough chunk of shared pool memory is not free, then the kernel will try and flush out the 'freeable' (not in use) memory and merge adjacent free chunks. If this still does not staisfy the memory requirements, then a 4031 is signalled/ The 'alter system flush shared pool' performs a manual flush instead, ahead of time and could (possibly) prevent a 4031 ... John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** 08:35:00 SQL> @shared_pool_free_lists BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST -- -- --- -- 01089784 23488 46 76 1 3941364656 84140 2 6812843678 185268 3 315504 875 360524 449019527300 671 1036 561588964099 1502 2060 655465161966 2821 4048 71125720 263 4280 7624 8 989584 101 9797 15456 9 rows selected. 08:35:29 SQL> alter system flush shared_pool; System altered. 08:36:32 SQL> @shared_pool_free_lists BUCKET FREE_SPACE FREE_CHUNKS AVERAGE_SIZEBIGGEST -- -- --- -- 0 14364 330 43 76 1 6528 76 85140 6 3964 1 3964 3964 9 29580 129580 29580 105028636 10348821 65436 11 13860744 15092404 130872 12 32192980 173 186086 261016 13 64490864 172 374946 522764 14 83609184 112 7465101048432 15 79829220 57 14005122068384 16 38149220 14 27249443705320 11 rows selected. -Original Message- Sent: Tuesday, January 13, 2004 9:34 AM To: Multiple recipients of list ORACLE-L Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited. -- P
Re: Shared Pool fragmentation
dba_object_size only shows some fixed calculations from stored code (how big is the code segment etc..), but it doesn't show how much memory a loaded object actually uses in shared pool. If you want to know library cache usage, use v$db_object_cache or x$kglob directly. If you want to know shared pool usage, then you have to start taking shared pool heapdumps Tanel. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Tuesday, January 13, 2004 11:09 PM Subject: RE: Shared Pool fragmentation dba_object_size.parsed_size Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Goulet, Dick [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 13, 2004 1:20 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Shared Pool fragmentation On a side note, before I kill myself with the Friendly Manual, does anyone have a fast way to determine how much space in the shared pool a package is using? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Rick Stephenson [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 13, 2004 12:34 PMTo: Multiple recipients of list ORACLE-LSubject: Shared Pool fragmentation Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
RE: Shared Pool fragmentation
dba_object_size.parsed_size Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Goulet, Dick [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 13, 2004 1:20 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Shared Pool fragmentation On a side note, before I kill myself with the Friendly Manual, does anyone have a fast way to determine how much space in the shared pool a package is using? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Rick Stephenson [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 13, 2004 12:34 PMTo: Multiple recipients of list ORACLE-LSubject: Shared Pool fragmentation Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
RE: Shared Pool fragmentation
On a side note, before I kill myself with the Friendly Manual, does anyone have a fast way to determine how much space in the shared pool a package is using? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Rick Stephenson [mailto:[EMAIL PROTECTED]Sent: Tuesday, January 13, 2004 12:34 PMTo: Multiple recipients of list ORACLE-LSubject: Shared Pool fragmentation Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
Shared Pool fragmentation
Is there a way to catch shared_pool fragmentation before you get the 4031 errors? I have looked at Steve Adams site which has scripts to show the free lists chunks in the shared pool. At what point do I know that it is fragmented too much? I know that I can prevent this by using bind variables, and keeping objects, but until I can modify all the apps, I would like to know a little before these errors happen. Any ideas? Thanks, Rick Stephenson This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to which they are addressed. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, forwarding or otherwise distributing or taking any action in reliance on the contents of this information is strictly prohibited.
RE: LMT and Fragmentation
Niall, I "played" around with autoallocate on 8.1.7 a while back and came to the same conclusions as yourself. Chris -Original Message- Sent: 13 October 2003 21:54 To: Multiple recipients of list ORACLE-L A week or so ago Jesse (I think) suggested a test to see whether auto-allocate LMTs were susceptible to fragmentation, or whether the fact that under the hood every allocation unit was 64k made this irrelevant. The test below shows that under 9.2 creating 32 tables, extending them until each has a next extent of > 64k. Then we drop half the tables. Can Oracle allocate a new extent for a table. Looks like it can't and the old fun of fragmentation might remain. I'll be sticking with ULMTs but flames/corrections welcomed. SQL> set echo on SQL> select banner from v$version; BANNER Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production PL/SQL Release 9.2.0.3.0 - Production CORE9.2.0.3.0 Production TNS for 32-bit Windows: Version 9.2.0.3.0 - Production NLSRTL Version 9.2.0.3.0 - Production SQL> SQL> create tablespace auto_alloc_test 2 datafile 'c:\oracle\oradata\nl9iwk\auto_alloc.dbf' size 32832k 3 extent management local; Tablespace created. SQL> SQL> /* DOC>create the tables DOC>*/ SQL> SQL> begin 2 for i in 1..32 loop 3 execute immediate 'create table table'||i||'(col1 number,col2 number) tablespace auto_alloc_test'; 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. SQL> SQL> select sum(bytes)/1024 free_k from dba_free_space where tablespace_name='AUTO_ALLOC_TEST'; FREE_K -- 30720 SQL> SQL> begin 2 for i in 1..15 loop 3 for j in 1..32 loop 4 execute immediate 'alter table table'||j||' allocate extent'; 5 end loop; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. SQL> SQL> select sum(bytes)/1024/1024 free_M from dba_free_space where tablespace_name='AUTO_ALLOC_TEST'; FREE_M -- SQL> SQL> begin 2 for i in 1..32 loop 3 if i mod 2 = 0 then 4 execute immediate 'drop table table'||i; 5 end if; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. SQL> SQL> select sum(bytes)/1024/1024 free_mb from dba_free_space where tablespace_name='AUTO_ALLOC_TEST'; FREE_MB -- 16 SQL> SQL> alter table table1 allocate extent; alter table table1 allocate extent * ERROR at line 1: ORA-01653: unable to extend table NIALL.TABLE1 by 64 in tablespace AUTO_ALLOC_TEST -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield 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: Dunscombe, Chris 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).
LMT and Fragmentation
A week or so ago Jesse (I think) suggested a test to see whether auto-allocate LMTs were susceptible to fragmentation, or whether the fact that under the hood every allocation unit was 64k made this irrelevant. The test below shows that under 9.2 creating 32 tables, extending them until each has a next extent of > 64k. Then we drop half the tables. Can Oracle allocate a new extent for a table. Looks like it can't and the old fun of fragmentation might remain. I'll be sticking with ULMTs but flames/corrections welcomed. SQL> set echo on SQL> select banner from v$version; BANNER Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production PL/SQL Release 9.2.0.3.0 - Production CORE9.2.0.3.0 Production TNS for 32-bit Windows: Version 9.2.0.3.0 - Production NLSRTL Version 9.2.0.3.0 - Production SQL> SQL> create tablespace auto_alloc_test 2 datafile 'c:\oracle\oradata\nl9iwk\auto_alloc.dbf' size 32832k 3 extent management local; Tablespace created. SQL> SQL> /* DOC>create the tables DOC>*/ SQL> SQL> begin 2 for i in 1..32 loop 3 execute immediate 'create table table'||i||'(col1 number,col2 number) tablespace auto_alloc_test'; 4 end loop; 5 end; 6 / PL/SQL procedure successfully completed. SQL> SQL> select sum(bytes)/1024 free_k from dba_free_space where tablespace_name='AUTO_ALLOC_TEST'; FREE_K -- 30720 SQL> SQL> begin 2 for i in 1..15 loop 3 for j in 1..32 loop 4 execute immediate 'alter table table'||j||' allocate extent'; 5 end loop; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. SQL> SQL> select sum(bytes)/1024/1024 free_M from dba_free_space where tablespace_name='AUTO_ALLOC_TEST'; FREE_M -- SQL> SQL> begin 2 for i in 1..32 loop 3 if i mod 2 = 0 then 4 execute immediate 'drop table table'||i; 5 end if; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. SQL> SQL> select sum(bytes)/1024/1024 free_mb from dba_free_space where tablespace_name='AUTO_ALLOC_TEST'; FREE_MB -- 16 SQL> SQL> alter table table1 allocate extent; alter table table1 allocate extent * ERROR at line 1: ORA-01653: unable to extend table NIALL.TABLE1 by 64 in tablespace AUTO_ALLOC_TEST -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield 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: fragmentation
Index tablespace has the most wasted space and yes, there are several indices in those tablespaces. I need to keep it available or I'd just drop and recreate the entire index. good to know I wasn't hallucinating! --- DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: > Rachel - Right you are, as "Stop Defragmenting . . . " points out, > there are > several types of fragmentation. >- Is it your table or your index that contains wasted space? >- Are there multiple objects in each tablespace or just a single > object? >- Are you trying to keep the index available to users while you're > rebuilding? I'm assuming this is the reason you are looking at > rebuilding > the index twice. Or is it because rebuilding an index probably won't > cause a > large sort? > Overall it looks like a reasonable plan, though I haven't worked much > with > partitioned indexes myself. > > Dennis Williams > DBA, 80%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -Original Message- > Sent: Thursday, August 14, 2003 12:29 PM > To: Multiple recipients of list ORACLE-L > > > I never thought I would care about fragmentation again, especially > once > I built all my databases using LMTs. > > But I've actually found a reason to care, sort of. We have a data > warehouse with lots of "wasted" space in it. By that I mean, many of > the partitions (we partition by month) are way larger than is needed > for the data contained within them. Once all the data for a month is > loaded, that's it, it doesn't grow anymore. > > The oversized tablespaces are those associated with the indexes. Part > of the problem is that the hosting company we use has a threshhold of > 80% and when a tablespace is 80% full, they automatically expand the > datafiles. part of the problem is that for a time there were problems > with the loads and we had to delete/re-insert/delete/re-insert data. > > I want to shrink the datafiles, but they are "fragmented". yes, I > know > "disk is cheap", but having gone through a 3 month exercise in > frustration trying to get the data center management to spend a few > thousand dollars on more memory so that we could actually run > reports, > I'm not going there. > > I was planning on doing the following, just wanted a sanity check > from > the list: > > 1) create a very large holding tablespace, to use as a rebuilding > area > > then, on a partition by partition basis: > > a) rebuild the index partition into the holding tablespace (lots of > indexes to rebuild in each partition) > b) rebuild the index partition back into the original tablespace (my > hope is that this will effectively "compress" the index extents) > c) shrink the index partition datafiles > > > Does this make sense or am I overtired and not thinking? Is there a > better/faster/EASIER way to do what I wanted to do? > > Rachel > > > Rachel > > > > > > > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Rachel Carmichael > 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: 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software h
Re: fragmentation
Hi! >- Are you trying to keep the index available to users while you're > rebuilding? I'm assuming this is the reason you are looking at rebuilding > the index twice. Or is it because rebuilding an index probably won't cause a > large sort? Sort is still needed, even when rebuilding. It's just that less data is required to read when rebuilding from index than building from table. Rachel, you probably already planned using nologging and possibly parallel clause + setting sort_area_size and maybe db_file_multiblock_read_count, depending on your extent size. I would recommend you to rebuild one index partition as you planned and then build other, similarly sized partition from scratch. Then measure the time & IO difference. When building from scratch, you have to read lots of data from table, sort it (to temp), then copy it back from temp. For double rebuilding, you will have to read lesser amount of data, sort it to temp, copy it to your big tablespace. Then read it again, sort it to temp and copy it to your original database again. Of course, verify that there's no other extents in your original tablespace, otherwise you might not be able to resize your files smaller. Also, depending on your IO layout, if you got any spare disks for temporary use, you could make additional temp tablespace on them, set the index recreating user's temp ts to that one to avoid disk contention.. Tanel. -- 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).
RE: fragmentation
Rachel - Right you are, as "Stop Defragmenting . . . " points out, there are several types of fragmentation. - Is it your table or your index that contains wasted space? - Are there multiple objects in each tablespace or just a single object? - Are you trying to keep the index available to users while you're rebuilding? I'm assuming this is the reason you are looking at rebuilding the index twice. Or is it because rebuilding an index probably won't cause a large sort? Overall it looks like a reasonable plan, though I haven't worked much with partitioned indexes myself. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 14, 2003 12:29 PM To: Multiple recipients of list ORACLE-L I never thought I would care about fragmentation again, especially once I built all my databases using LMTs. But I've actually found a reason to care, sort of. We have a data warehouse with lots of "wasted" space in it. By that I mean, many of the partitions (we partition by month) are way larger than is needed for the data contained within them. Once all the data for a month is loaded, that's it, it doesn't grow anymore. The oversized tablespaces are those associated with the indexes. Part of the problem is that the hosting company we use has a threshhold of 80% and when a tablespace is 80% full, they automatically expand the datafiles. part of the problem is that for a time there were problems with the loads and we had to delete/re-insert/delete/re-insert data. I want to shrink the datafiles, but they are "fragmented". yes, I know "disk is cheap", but having gone through a 3 month exercise in frustration trying to get the data center management to spend a few thousand dollars on more memory so that we could actually run reports, I'm not going there. I was planning on doing the following, just wanted a sanity check from the list: 1) create a very large holding tablespace, to use as a rebuilding area then, on a partition by partition basis: a) rebuild the index partition into the holding tablespace (lots of indexes to rebuild in each partition) b) rebuild the index partition back into the original tablespace (my hope is that this will effectively "compress" the index extents) c) shrink the index partition datafiles Does this make sense or am I overtired and not thinking? Is there a better/faster/EASIER way to do what I wanted to do? Rachel Rachel __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: 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).
fragmentation
I never thought I would care about fragmentation again, especially once I built all my databases using LMTs. But I've actually found a reason to care, sort of. We have a data warehouse with lots of "wasted" space in it. By that I mean, many of the partitions (we partition by month) are way larger than is needed for the data contained within them. Once all the data for a month is loaded, that's it, it doesn't grow anymore. The oversized tablespaces are those associated with the indexes. Part of the problem is that the hosting company we use has a threshhold of 80% and when a tablespace is 80% full, they automatically expand the datafiles. part of the problem is that for a time there were problems with the loads and we had to delete/re-insert/delete/re-insert data. I want to shrink the datafiles, but they are "fragmented". yes, I know "disk is cheap", but having gone through a 3 month exercise in frustration trying to get the data center management to spend a few thousand dollars on more memory so that we could actually run reports, I'm not going there. I was planning on doing the following, just wanted a sanity check from the list: 1) create a very large holding tablespace, to use as a rebuilding area then, on a partition by partition basis: a) rebuild the index partition into the holding tablespace (lots of indexes to rebuild in each partition) b) rebuild the index partition back into the original tablespace (my hope is that this will effectively "compress" the index extents) c) shrink the index partition datafiles Does this make sense or am I overtired and not thinking? Is there a better/faster/EASIER way to do what I wanted to do? Rachel Rachel __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Fragmentation ?
Aaah - now I understand. The LRU rule does not hold good once the Goddess applies her personal touch and 'accesses' these blocks (sorry - books) :) So they need to stay in the DB B(l)ock buffer cache as they now migrate to the MRU end of the cache chain... The blocks that do need to go out of the (book) cache are actually those that have been updated! Couldn't resist the rambling - it is Friday! John > -Original Message- > From: Rachel Carmichael [mailto:[EMAIL PROTECTED] > Sent: Friday, June 13, 2003 12:45 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Fragmentation ? > > > your wife's rule wouldn't work in my case... every few years (usually > less than 10 but on occasion 10 works too) I go on a "re-reading > spree". back to old friends, "comfort food" of books. > > I'd have to buy all new copies if I threw books out. > > I do, on rare occasion, get rid of books. My oracle books that tell me > how to tune Version 7 are one example :) > > --- Niall Litchfield <[EMAIL PROTECTED]> wrote: > > I worked with a really smart guy once whom I won't name for obvious > > reasons. He had previously worked for a software co that said "Our > > product includes an archive routine". It didn't, they never had to > > write > > one because hey disks held 3 times the storage for half the price > > before > > anyone wanted to archive anything - at which point you just bought > > some > > more storage. > > > > I also probably ought to include the ongoing marital dispute that I > > am > > having regarding books, my wife maintains that anything I haven't > > accessed for a decade could be disposed of (think Tolkien, > Donaldson, > > Asimov, Shakespeare, Auden). *I* maintain "well we could always buy > > another bookcase". Logic tends to dictate my wifes approach, > > management > > I feel confident would say "ah well doesn't cost much lets buy > > another > > bookcase". > > > > In summary Niall's 2nd rule states that "data always goes in but > > never > > comes out". It's parkinsons law for databases > > > > Niall > > > > > -Original Message- > > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > > > Behalf Of Stephen Lee > > > Sent: 13 June 2003 18:45 > > > To: Multiple recipients of list ORACLE-L > > > Subject: RE: Fragmentation ? > > > > > > > > > > > > That's one thing good about the databases here. Tablespace > > > fragmentation is rarely a problem. Most of the database here > > > are a Database Roach Motel: "Data checks in. It doesn't > > > check out." Somehow, the data purge part of the application > > > -- that they intended to put in "one of these days" -- never > > > got written. > > > > > > > > > > > > (For non-USA dwellers, Roach Motel is a trap for roaches. It > > > has a sticky floor, and the sales motto is "Roaches check in. > > > They don't check out.") > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Stephen Lee > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > > and in the message BODY, include a line containing: UNSUB > > ORACLE-L (or the name of mailing list you want to be removed > > from). You may also send the HELP command for other > > information (like subscribing). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Niall Litchfield > 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 sen
RE: Fragmentation ?
your wife's rule wouldn't work in my case... every few years (usually less than 10 but on occasion 10 works too) I go on a "re-reading spree". back to old friends, "comfort food" of books. I'd have to buy all new copies if I threw books out. I do, on rare occasion, get rid of books. My oracle books that tell me how to tune Version 7 are one example :) --- Niall Litchfield <[EMAIL PROTECTED]> wrote: > I worked with a really smart guy once whom I won't name for obvious > reasons. He had previously worked for a software co that said "Our > product includes an archive routine". It didn't, they never had to > write > one because hey disks held 3 times the storage for half the price > before > anyone wanted to archive anything - at which point you just bought > some > more storage. > > I also probably ought to include the ongoing marital dispute that I > am > having regarding books, my wife maintains that anything I haven't > accessed for a decade could be disposed of (think Tolkien, Donaldson, > Asimov, Shakespeare, Auden). *I* maintain "well we could always buy > another bookcase". Logic tends to dictate my wifes approach, > management > I feel confident would say "ah well doesn't cost much lets buy > another > bookcase". > > In summary Niall's 2nd rule states that "data always goes in but > never > comes out". It's parkinsons law for databases > > Niall > > > -Original Message- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > > Behalf Of Stephen Lee > > Sent: 13 June 2003 18:45 > > To: Multiple recipients of list ORACLE-L > > Subject: RE: Fragmentation ? > > > > > > > > That's one thing good about the databases here. Tablespace > > fragmentation is rarely a problem. Most of the database here > > are a Database Roach Motel: "Data checks in. It doesn't > > check out." Somehow, the data purge part of the application > > -- that they intended to put in "one of these days" -- never > > got written. > > > > > > > > (For non-USA dwellers, Roach Motel is a trap for roaches. It > > has a sticky floor, and the sales motto is "Roaches check in. > > They don't check out.") > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Stephen Lee > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > > and in the message BODY, include a line containing: UNSUB > > ORACLE-L (or the name of mailing list you want to be removed > > from). You may also send the HELP command for other > > information (like subscribing). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Niall Litchfield > 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!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Fragmentation ?
I worked with a really smart guy once whom I won't name for obvious reasons. He had previously worked for a software co that said "Our product includes an archive routine". It didn't, they never had to write one because hey disks held 3 times the storage for half the price before anyone wanted to archive anything - at which point you just bought some more storage. I also probably ought to include the ongoing marital dispute that I am having regarding books, my wife maintains that anything I haven't accessed for a decade could be disposed of (think Tolkien, Donaldson, Asimov, Shakespeare, Auden). *I* maintain "well we could always buy another bookcase". Logic tends to dictate my wifes approach, management I feel confident would say "ah well doesn't cost much lets buy another bookcase". In summary Niall's 2nd rule states that "data always goes in but never comes out". It's parkinsons law for databases Niall > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of Stephen Lee > Sent: 13 June 2003 18:45 > To: Multiple recipients of list ORACLE-L > Subject: RE: Fragmentation ? > > > > That's one thing good about the databases here. Tablespace > fragmentation is rarely a problem. Most of the database here > are a Database Roach Motel: "Data checks in. It doesn't > check out." Somehow, the data purge part of the application > -- that they intended to put in "one of these days" -- never > got written. > > > > (For non-USA dwellers, Roach Motel is a trap for roaches. It > has a sticky floor, and the sales motto is "Roaches check in. > They don't check out.") > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephen Lee > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and in the message BODY, include a line containing: UNSUB > ORACLE-L (or the name of mailing list you want to be removed > from). You may also send the HELP command for other > information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield 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: Fragmentation ?
I think those guys work here now :-) -Original Message- Sent: Friday, June 13, 2003 1:45 PM To: Multiple recipients of list ORACLE-L That's one thing good about the databases here. Tablespace fragmentation is rarely a problem. Most of the database here are a Database Roach Motel: "Data checks in. It doesn't check out." Somehow, the data purge part of the application -- that they intended to put in "one of these days" -- never got written. (For non-USA dwellers, Roach Motel is a trap for roaches. It has a sticky floor, and the sales motto is "Roaches check in. They don't check out.") -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seefelt, Beth 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: Fragmentation ?
That's one thing good about the databases here. Tablespace fragmentation is rarely a problem. Most of the database here are a Database Roach Motel: "Data checks in. It doesn't check out." Somehow, the data purge part of the application -- that they intended to put in "one of these days" -- never got written. (For non-USA dwellers, Roach Motel is a trap for roaches. It has a sticky floor, and the sales motto is "Roaches check in. They don't check out.") -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Fragmentation ?
Well said. For people that can only comprehend a simple solution, it is much more comfortable to have a single answer. The old "reorganize to a single extent" was always easy to understand. Along the way as a side-effect it cured other types of fragmentation, but if the underlying causes of the fragmentation are understood, then fewer reorganizations would be needed. The paper "Stop Defragmenting . . ." isn't one of those you can skim and then set aside. It needs to be intensively studied. LMT isn't completely foolproof, so you need to understand the underlying premises. As to the types of fragmentation, read the introduction. It explains which chapter discusses which type of fragmentation. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Friday, June 13, 2003 9:15 AM To: Multiple recipients of list ORACLE-L there was a debate on here 2 weeks ago where it was concluded that until you get to thousands of extents it just doesnt matter how many you have. > > From: "VIVEK_SHARMA" <[EMAIL PROTECTED]> > Date: 2003/06/13 Fri AM 06:39:36 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: Fragmentation ? > > Dennis , List > > What may be the OTHER forms of fragmentation ? > > What Number of Extents may be considered Critical warranting RE-Organization for Manually Sized Objects existing in LMTs ? > > Thanks for the great paper . Had read it previously though . > > Thanks > > > -Original Message- > Sent: Wednesday, June 11, 2003 8:25 PM > To: Multiple recipients of list ORACLE-L > > > Vivek >Make sure you've read "How to Stop Defragmenting and Start Living" > http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049 > The authors point out that uniform extents stop fragmentation at the > tablespace level. However they point out that there are other forms of > fragmentation. > > Dennis Williams > DBA, 80%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -Original Message- > Sent: Wednesday, June 11, 2003 9:15 AM > To: Multiple recipients of list ORACLE-L > > > > Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE = > "UNIFORM" OVER dba_tablespaces.ALLOCATION_TYPE = "USER" ? > > With ALLOCATION_TYPE = "UNIFORM" , NEXT_EXTENT Size of the Object can NOT be > Manually defined in the Table Creation Script )> , > which is allowed when having allocation_type="USER" . > > Allocation_type="USER" allows Objects with Different NEXT_EXTENT Sizes to be Created > in the SAME LOCALLY managed Tablespace & thus reduces Total Number of Extents for > the respective Table. Our Application does have Objects of Dissimilar Sizes > Existing tin the Same Tablespace . > > Does ALLOCATION_TYPE = "UNIFORM" automatically imply NO Fragmentation > Irrespective of the Number of Extents of the Object (in a Locally Managed > Tablespace) ? Does it further imply NO further need to Look at Number of > Extents of an Object in a Locally Managed Tablespace ? > > NOTE Allocation_type can be made = "USER" by using the stored procedures :- > dbms_space_admin.tablespace_migrate_from_local / > dbms_space_admin.tablespace_migrate_to_local > > Am i still Lost in the World of Oracle 7 ? > > Thanks > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: VIVEK_SHARMA > 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: 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
RE: RE: Fragmentation ?
Title: RE: RE: Fragmentation ? Depends ... who you ask ... If you ask Microsoft 1. you are fragmented if you have at-least _one_ non-windows server in your corporation 2. Your thinking is fragmented if you are even _considering_ LINUX If you ask SCO 1. You are fragmented if you use AIX 2. You are fragmented if you read every line of GPL If you ask Oracle Experts 1. Some will say "Do you have a problem? if none, don't worry" 2. Some will say "More than x extents is bad" but X varies from 2 to 1024 to 4096 3. Some will advise use LMT with Uniform extents and live happily there after. I could think of some political ones, but this is a technical list ... TGIF Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! > > From: "VIVEK_SHARMA" <[EMAIL PROTECTED]> > Date: 2003/06/13 Fri AM 06:39:36 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: Fragmentation ? > > Dennis , List > What may be the OTHER forms of fragmentation ? > What Number of Extents may be considered Critical warranting RE-Organization for Manually Sized Objects existing in LMTs ? > Thanks for the great paper . Had read it previously though . > Thanks > *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
Re: RE: Fragmentation ?
there was a debate on here 2 weeks ago where it was concluded that until you get to thousands of extents it just doesnt matter how many you have. > > From: "VIVEK_SHARMA" <[EMAIL PROTECTED]> > Date: 2003/06/13 Fri AM 06:39:36 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: Fragmentation ? > > Dennis , List > > What may be the OTHER forms of fragmentation ? > > What Number of Extents may be considered Critical warranting RE-Organization for > Manually Sized Objects existing in LMTs ? > > Thanks for the great paper . Had read it previously though . > > Thanks > > > -Original Message- > Sent: Wednesday, June 11, 2003 8:25 PM > To: Multiple recipients of list ORACLE-L > > > Vivek >Make sure you've read "How to Stop Defragmenting and Start Living" > http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049 > The authors point out that uniform extents stop fragmentation at the > tablespace level. However they point out that there are other forms of > fragmentation. > > Dennis Williams > DBA, 80%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -Original Message- > Sent: Wednesday, June 11, 2003 9:15 AM > To: Multiple recipients of list ORACLE-L > > > > Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE = > "UNIFORM" OVER dba_tablespaces.ALLOCATION_TYPE = "USER" ? > > With ALLOCATION_TYPE = "UNIFORM" , NEXT_EXTENT Size of the Object can NOT be > Manually defined in the Table Creation Script )> , > which is allowed when having allocation_type="USER" . > > Allocation_type="USER" allows Objects with Different NEXT_EXTENT Sizes to be Created > in the SAME LOCALLY managed Tablespace & thus reduces Total Number of Extents for > the respective Table. Our Application does have Objects of Dissimilar Sizes > Existing tin the Same Tablespace . > > Does ALLOCATION_TYPE = "UNIFORM" automatically imply NO Fragmentation > Irrespective of the Number of Extents of the Object (in a Locally Managed > Tablespace) ? Does it further imply NO further need to Look at Number of > Extents of an Object in a Locally Managed Tablespace ? > > NOTE Allocation_type can be made = "USER" by using the stored procedures :- > dbms_space_admin.tablespace_migrate_from_local / > dbms_space_admin.tablespace_migrate_to_local > > Am i still Lost in the World of Oracle 7 ? > > Thanks > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: VIVEK_SHARMA > 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: 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: VIVEK_SHARMA > 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 -- Autho
RE: Fragmentation ?
Dennis , List What may be the OTHER forms of fragmentation ? What Number of Extents may be considered Critical warranting RE-Organization for Manually Sized Objects existing in LMTs ? Thanks for the great paper . Had read it previously though . Thanks -Original Message- Sent: Wednesday, June 11, 2003 8:25 PM To: Multiple recipients of list ORACLE-L Vivek Make sure you've read "How to Stop Defragmenting and Start Living" http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049 The authors point out that uniform extents stop fragmentation at the tablespace level. However they point out that there are other forms of fragmentation. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 11, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE = "UNIFORM" OVER dba_tablespaces.ALLOCATION_TYPE = "USER" ? With ALLOCATION_TYPE = "UNIFORM" , NEXT_EXTENT Size of the Object can NOT be Manually defined in the Table Creation Script )> , which is allowed when having allocation_type="USER" . Allocation_type="USER" allows Objects with Different NEXT_EXTENT Sizes to be Created in the SAME LOCALLY managed Tablespace & thus reduces Total Number of Extents for the respective Table. Our Application does have Objects of Dissimilar Sizes Existing tin the Same Tablespace . Does ALLOCATION_TYPE = "UNIFORM" automatically imply NO Fragmentation Irrespective of the Number of Extents of the Object (in a Locally Managed Tablespace) ? Does it further imply NO further need to Look at Number of Extents of an Object in a Locally Managed Tablespace ? NOTE Allocation_type can be made = "USER" by using the stored procedures :- dbms_space_admin.tablespace_migrate_from_local / dbms_space_admin.tablespace_migrate_to_local Am i still Lost in the World of Oracle 7 ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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: 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: VIVEK_SHARMA 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: Fragmentation ?
Vivek Make sure you've read "How to Stop Defragmenting and Start Living" http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049 The authors point out that uniform extents stop fragmentation at the tablespace level. However they point out that there are other forms of fragmentation. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 11, 2003 9:15 AM To: Multiple recipients of list ORACLE-L Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE = "UNIFORM" OVER dba_tablespaces.ALLOCATION_TYPE = "USER" ? With ALLOCATION_TYPE = "UNIFORM" , NEXT_EXTENT Size of the Object can NOT be Manually defined in the Table Creation Script )> which is allowed when having allocation_type="USER" . This allows Objects with Different NEXT_EXTENT Sizes to be Created in the SAME LOCALLY managed Tablespace & thus reduces Total Number of Extents for the respective Table. Our Application does have Objects of Dissimilar Sizes Existing tin the Same Tablespace . Does ALLOCATION_TYPE = "UNIFORM" automatically imply NO Fragmentation Irrespective of the Number of Extents of the Object (in a Locally Managed Tablespace) ? Does it further imply NO further need to Look at Number of Extents of an Object in a Locally Managed Tablespace ? NOTE Allocation_type can be made = "USER" by using the stored procedures :- dbms_space_admin.tablespace_migrate_from_local / dbms_space_admin.tablespace_migrate_to_local Am i still Lost in the World of Oracle 7 ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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: 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).
Fragmentation ?
Qs What is the advantage of having dba_tablespaces.ALLOCATION_TYPE = "UNIFORM" OVER dba_tablespaces.ALLOCATION_TYPE = "USER" ? With ALLOCATION_TYPE = "UNIFORM" , NEXT_EXTENT Size of the Object can NOT be Manually defined in the Table Creation Script )> which is allowed when having allocation_type="USER" . This allows Objects with Different NEXT_EXTENT Sizes to be Created in the SAME LOCALLY managed Tablespace & thus reduces Total Number of Extents for the respective Table. Our Application does have Objects of Dissimilar Sizes Existing tin the Same Tablespace . Does ALLOCATION_TYPE = "UNIFORM" automatically imply NO Fragmentation Irrespective of the Number of Extents of the Object (in a Locally Managed Tablespace) ? Does it further imply NO further need to Look at Number of Extents of an Object in a Locally Managed Tablespace ? NOTE Allocation_type can be made = "USER" by using the stored procedures :- dbms_space_admin.tablespace_migrate_from_local / dbms_space_admin.tablespace_migrate_to_local Am i still Lost in the World of Oracle 7 ? Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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: ROLLBACK SEGMENT FRAGMENTATION
Why do you think it is "fragmented"? Why do you think it is a problem? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, October 07, 2002 10:36 AM > Hi > My rollback tablespace is highly fragmented.I am thinking to do like > following? > -Create new rollback tablespace rbs1 > -Create rollback segments > -Offline all rollback segments from old rollback tablespace (rbs) > -Drop rollback segments from rbs tablespace > -Drop tablespace rbs > -Create RBS tablespace > -Create rollback segments on RBS tablespace > -Drop tablespace RBS1 > Is this way is good to manage? > Let me know if anythings are missing please? > Thx > -Seema > > > > _ > MSN Photos is the easiest way to share and print your photos: > http://photos.msn.com/support/worldwide.aspx > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Seema 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.com -- Author: Tim Gorman 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: ROLLBACK SEGMENT FRAGMENTATION
before you do all this by fragmented do you mean that there are a large number of free extents in the tablespace? If so, are those extents all the same size or at least a multiple of the same size? If so, why are you bothering to defragment? You would only need to defragment the rollback tablespace if you have rollback segments where the initial and next extent sizes are not multiples of each other and/or where each rollback segment has a differently sized initial and next extents that are not multiples of the extent sizes of the other rollback segments... otherwise you are not "fragmented", you just have a lot of free extents that are the right size for Oracle to use. this is NOT a bad thing --- Seema Singh <[EMAIL PROTECTED]> wrote: > Hi > My rollback tablespace is highly fragmented.I am thinking to do like > following? > -Create new rollback tablespace rbs1 > -Create rollback segments > -Offline all rollback segments from old rollback tablespace (rbs) > -Drop rollback segments from rbs tablespace > -Drop tablespace rbs > -Create RBS tablespace > -Create rollback segments on RBS tablespace > -Drop tablespace RBS1 > Is this way is good to manage? > Let me know if anythings are missing please? > Thx > -Seema > > > > _ > MSN Photos is the easiest way to share and print your photos: > http://photos.msn.com/support/worldwide.aspx > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Seema 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). __ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos & More http://faith.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
ROLLBACK SEGMENT FRAGMENTATION
Hi My rollback tablespace is highly fragmented.I am thinking to do like following? -Create new rollback tablespace rbs1 -Create rollback segments -Offline all rollback segments from old rollback tablespace (rbs) -Drop rollback segments from rbs tablespace -Drop tablespace rbs -Create RBS tablespace -Create rollback segments on RBS tablespace -Drop tablespace RBS1 Is this way is good to manage? Let me know if anythings are missing please? Thx -Seema _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema 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).
Question: system tablespace fragmentation.
I granted select on table to a user. This permission already existed in the database. The sql statement returned a Grant succeeded. I've read the fine manual and couldn't find any information about regranting a permission that already existed, so I ran a trace and the results are below: It appears to me Oracle just updates the objauth$ without checking if a permission already exists. This shouldn't (should it) cause fragmentation in the system tablespace since it updates an existing record with the same information. grant select on contract update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11, dataobj#=:13,flags=:14,oid$=:15 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null) and(subname=:12 or subname is null and :12 is null) call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 1 0.00 0.00 0 0 0 0Execute 1 0.01 0.01 0 2 1 1Fetch 0 0.00 0.00 0 0 0 0--- -- -- -- -- -- --total 2 0.01 0.01 0 2 1 1 Misses in library cache during parse: 1Misses in library cache during execute: 1Optimizer goal: CHOOSEParsing user id: SYS (recursive depth: 1) Rows Row Source Operation--- --- 1 UPDATE OBJ$ 2 INDEX RANGE SCAN (object id 34) update objauth$ set option$=decode(option$,null,decode(:1,0,null,:1),option$)where grantor#=:2 and obj#=:3 and privilege#=:4 and grantee#=:5 and nvl(col#,0)=:6 call count cpu elapsed disk query current rows--- -- -- -- -- -- --Parse 1 0.01 0.01 0 0 0 0Execute 1 0.00 0.00 0 2 2 1Fetch 0 0.00 0.00 0 0 0 0--- -- -- -- -- -- --total 2 0.01 0.01 0 2 2 1 Misses in library cache during parse: 1Misses in library cache during execute: 1Optimizer goal: CHOOSEParsing user id: SYS (recursive depth: 1) Rows Row Source Operation--- --- 1 UPDATE OBJAUTH$ 2 INDEX RANGE SCAN (object id 100)
Re: FRAGMENTATION QUESTION?
Seema, If you COPY the datafiles at the OS level then you are getting an exact image of the datafile. The same for the RMAN copy command. If you use SQL "insert ,, select " type of copying data from one server to another then the data will fill the extents and eliminate fragmentation. It will not remove unused area in the extents, only carefull planning will make it close to full. ROR mª¿ªm >>> [EMAIL PROTECTED] 03/06/02 02:12PM >>> Hi If one tablespace has 10 tables and 6 are fragmented table in that particular tablespace.If I use COPY command to move data from one server to another then is COPy command bring data with fragmented tables data? Thx -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: FRAGMENTATION QUESTION?
Hi Seema, define what you mean when you say 6 are fragmented. John [EMAIL PROTECTED] wrote: > Hi > If one tablespace has 10 tables and 6 are fragmented table in that > particular tablespace.If I use COPY command to move data from one > server to another then is COPy command bring data with fragmented > tables data? > Thx > -Seema > > > > _ > Get your FREE download of MSN Explorer at > http://explorer.msn.com/intl.asp. > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ora NT DBA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
FRAGMENTATION QUESTION?
Hi If one tablespace has 10 tables and 6 are fragmented table in that particular tablespace.If I use COPY command to move data from one server to another then is COPy command bring data with fragmented tables data? Thx -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Old Chestnut: Tablespace Fragmentation
Darren,
To check if extents are contiguous query DBA_EXTENTS.
select file_id, block_id, blocks, block_id+blocks-1 from dba_Extents
where segment_name = '&which_table'
and owner='&which_schema'
order by extent_id
Hemant K Chitale
Principal DBA
Chartered Semiconductor Manufacturing Ltd
"Browett, Darren" <[EMAIL PROTECTED]> 02/03/2002 12:48 AM
Sent by: [EMAIL PROTECTED]
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group)
Subject: RE: Old Chestnut: Tablespace Fragmentation
Is there anyway to check that the extents are contiguous ?
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: February 28, 2002 2:48 AM
To: Multiple recipients of list ORACLE-L
If you have only 1 Table in that Tablespace and only 1 File on that disk,
even if you had multiple extents, you might expect them to be contiguous.
Then, the only issue would be that the extent size should be a proper
multiple
of db_block_size*db_file_multiblock_read_count (or max_io_size).
Hemant K Chitale
Principal DBA
Chartered Semiconductor Manufacturing Ltd
Bill Buchan <[EMAIL PROTECTED]> 27/02/2002 08:43 PM
Sent by: [EMAIL PROTECTED]
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group)
Subject: Old Chestnut: Tablespace Fragmentation
I know this one has been done to death: use uniform extents to avoid
fragmentation; multiple extents don't hurt (within limits).
But what if:
Data Warehouse, one big table on a single disk, full table (batch) scan, no
concurrent transactions on the database (so no contention for the disk), no
fragmentation at the file system level, initially empty buffer cache
(startup), read-only operation so DBWR isn't doing anything on this
disk. Basically I want to read one data file from end to end. Surely it
would make sense to have the disk read moving smoothly from one end of the
disk to the other rather than bouncing about all over the place as it may
do with multiple extents "randomly" allocated.
Any thoughts?
Thanks
- Bill.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Buchan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Browett, Darren
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat
RE: Old Chestnut: Tablespace Fragmentation
rem
rem file: mapper.sql
rem location: $HOME/sql
rem parameters: the tablespace name being mapped
rem
rem Sample invocation:
rem @mapper SYSTEM
rem
rem This script generates a mapping of the space usage
rem (free space vs used) in a tablespace. It graphically
rem shows segment and free space fragmentation.
rem
set pagesize 66 linesize 132 verify off
ttitle 'Map of Tablespace ' &&1 right datevar skip 1
column substr(file_id,1,4) heading "File|Id"
column bytes format 999,999,999,999
column today noprint new_value datevar
SELECT
to_char(sysdate, 'MM/DD/YY') today,
'free space' owner, /*"owner" of free space*/
' ' object, /*blank object name*/
substr(file_id,1,4), /*file ID for the extent header*/
block_id, /*block ID for the extent header*/
blocks, /*length of the extent in blocks*/
bytes /*length of the extent in bytes*/
FROM sys.dba_free_space
WHERE tablespace_name = upper('&&1')
UNION
SELECT
to_char(sysdate, 'MM/DD/YY') today,
substr(owner,1,10), /*owner name (first 20 chars)*/
substr(segment_name,1,27), /*segment name (first 26 chars)*/
substr(file_id,1,4), /*file ID for extent header*/
block_id, /*block ID for block header*/
blocks, /*length of the extent in blocks*/
bytes /*length of the extent in bytes*/
FROM sys.dba_extents
WHERE tablespace_name = upper('&&1')
ORDER BY 4,5
/
undefine 1
"Browett,
Darren" To: Multiple recipients of list ORACLE-L
@city.coquitlcc:
am.bc.ca>Subject: RE: Old Chestnut: Tablespace
Sent by: rootFragmentation
03/01/2002
11:48 AM
Please
respond to
ORACLE-L
Is there anyway to check that the extents are contiguous ?
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: February 28, 2002 2:48 AM
To: Multiple recipients of list ORACLE-L
If you have only 1 Table in that Tablespace and only 1 File on that disk,
even if you had multiple extents, you might expect them to be contiguous.
Then, the only issue would be that the extent size should be a proper
multiple
of db_block_size*db_file_multiblock_read_count (or max_io_size).
Hemant K Chitale
Principal DBA
Chartered Semiconductor Manufacturing Ltd
Bill Buchan <[EMAIL PROTECTED]> 27/02/2002 08:43 PM
Sent by: [EMAIL PROTECTED]
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group)
Subject: Old Chestnut: Tablespace Fragmentation
I know this one has been done to death: use uniform extents to avoid
fragmentation; multiple extents don't hurt (within limits).
But what if:
Data Warehouse, one big table on a single disk, full table (batch) scan, no
concurrent transactions on the database (so no contention for the disk), no
fragmentation at the file system level, initially empty buffer cache
(startup), read-only operation so DBWR isn't doing anything on this
disk. Basically I want to read one data file from end to end. Surely it
would make sense to have the disk read moving smoothly from one end of the
disk to the other rather than bouncing about all over the place as it may
do with multiple extents "randomly" allocated.
Any thoughts?
Thanks
- Bill.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Bill Buchan
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of '
RE: Old Chestnut: Tablespace Fragmentation
Is there anyway to check that the extents are contiguous ? -Original Message- [mailto:[EMAIL PROTECTED]] Sent: February 28, 2002 2:48 AM To: Multiple recipients of list ORACLE-L If you have only 1 Table in that Tablespace and only 1 File on that disk, even if you had multiple extents, you might expect them to be contiguous. Then, the only issue would be that the extent size should be a proper multiple of db_block_size*db_file_multiblock_read_count (or max_io_size). Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd Bill Buchan <[EMAIL PROTECTED]> 27/02/2002 08:43 PM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group) Subject: Old Chestnut: Tablespace Fragmentation I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents "randomly" allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Old Chestnut: Tablespace Fragmentation
If you have only 1 Table in that Tablespace and only 1 File on that disk, even if you had multiple extents, you might expect them to be contiguous. Then, the only issue would be that the extent size should be a proper multiple of db_block_size*db_file_multiblock_read_count (or max_io_size). Hemant K Chitale Principal DBA Chartered Semiconductor Manufacturing Ltd Bill Buchan <[EMAIL PROTECTED]> 27/02/2002 08:43 PM Sent by: [EMAIL PROTECTED] Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: (bcc: CHITALE Hemant Krishnarao/IT/CHRT/ST Group) Subject: Old Chestnut: Tablespace Fragmentation I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents "randomly" allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Old Chestnut: Tablespace Fragmentation
Thanks to everyone for their comments on this. You've convinced me. I'll go away and worry about something else instead now! - Bill. At 04:43 27/02/02 -0800, you wrote: >I know this one has been done to death: use uniform extents to avoid >fragmentation; multiple extents don't hurt (within limits). > >But what if: > >Data Warehouse, one big table on a single disk, full table (batch) scan, >no concurrent transactions on the database (so no contention for the >disk), no fragmentation at the file system level, initially empty buffer >cache (startup), read-only operation so DBWR isn't doing anything on this >disk. Basically I want to read one data file from end to end. Surely it >would make sense to have the disk read moving smoothly from one end of the >disk to the other rather than bouncing about all over the place as it may >do with multiple extents "randomly" allocated. > >Any thoughts? > >Thanks >- Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Old Chestnut: Tablespace Fragmentation
Bill, I believe in that case your query will spend most time on data transfer and the percent of seeking time will be tiny provided that number of extents is reasonable. One thing to note: sizes of extents should be multiple of db_file_multiblock_read_count in order to minimize number of I/O operations required for FTS. Regards, Ed > > > I know this one has been done to death: use uniform extents to avoid > fragmentation; multiple extents don't hurt (within limits). > > But what if: > > Data Warehouse, one big table on a single disk, full table (batch) scan, no > concurrent transactions on the database (so no contention for the disk), no > fragmentation at the file system level, initially empty buffer cache > (startup), read-only operation so DBWR isn't doing anything on this > disk. Basically I want to read one data file from end to end. Surely it > would make sense to have the disk read moving smoothly from one end of the > disk to the other rather than bouncing about all over the place as it may > do with multiple extents "randomly" allocated. > > Any thoughts? > > Thanks > - Bill. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Bill Buchan > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Old Chestnut: Tablespace Fragmentation
Bill, I believe in that case your query will spend most time on data transfer and the percent of seeking time will be tiny provided that number of extents is reasonable. One thing to note: sizes of extents should be multiple of db_file_multiblock_read_count in order to minimize number of I/O operations required for FTS. Regards, Ed > > > I know this one has been done to death: use uniform extents to avoid > fragmentation; multiple extents don't hurt (within limits). > > But what if: > > Data Warehouse, one big table on a single disk, full table (batch) scan, no > concurrent transactions on the database (so no contention for the disk), no > fragmentation at the file system level, initially empty buffer cache > (startup), read-only operation so DBWR isn't doing anything on this > disk. Basically I want to read one data file from end to end. Surely it > would make sense to have the disk read moving smoothly from one end of the > disk to the other rather than bouncing about all over the place as it may > do with multiple extents "randomly" allocated. > > Any thoughts? > > Thanks > - Bill. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Bill Buchan > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Shevtsov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Old Chestnut: Tablespace Fragmentation
Bill, It sounds like you are describing an ideal situation. Is this scan being done by only one user at a time? Then you are describing a dedicated database to one user? Lets face it, the above is not even remotely probable in todays world. And furthur, if you decided that the above setup is what you want, then how do you apply new records to the table - a full reload every time? It seems way to much work in hopes that the resulting query might be faster. In my humble opinion, there seems to be *way* too much time devoted to worrying about table extents and disk access. Disk are soo much faster today, that I've decided that it really is not worth considering very much. Of course, I'm not currently working on a high-volume application right now, so my radar is focused on other things. Interesting idea, though. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, February 27, 2002 7:43 AM To: Multiple recipients of list ORACLE-L I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents "randomly" allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Old Chestnut: Tablespace Fragmentation
Bill - My Tech. Service Manager keeps reminding me that "disk isn't so simple anymore". You are probably on RAID for the higher read performance. Now your file is broken across several disks. Of course, to get the straight read, the controller can't service anyone else's requests while your scan continues uninterrupted. Just some other thoughts. By the way, yesterday I was able to reduce the full-table scan time on our data warehouse from over 2 minutes to below 10 seconds. I broke the table into 54 partitions so the most common queries were able to scan just the minimum amount of the table they need. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 27, 2002 9:04 AM To: Multiple recipients of list ORACLE-L IMHO, yes you're right, but the little bit of extra disk head movement is going to be insignificant because of the overall size of the transaction. In a perfect world, no tables would ever be fragmented. But the trade off is in maintenance. You're going to go through alot of work to keep your one large table always contiguous, keep your data files always contiguous to shave a few millseconds off a long transaction. Beth -Original Message- Sent: Wednesday, February 27, 2002 7:43 AM To: Multiple recipients of list ORACLE-L I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents "randomly" allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Old Chestnut: Tablespace Fragmentation
Unknown address, This address isn't in our system. This person is not employed here anymore, or has never been employed here. Please remove this address from your address book... Have A Nice Day Gerald T. Bray Email Administrator Handex Environmental, Inc. (352) 735-1800 Ext 145 E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 27, 2002 7:43 AM To: Multiple recipients of list ORACLE-L I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents "randomly" allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Babich , Sergey INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Old Chestnut: Tablespace Fragmentation
IMHO, yes you're right, but the little bit of extra disk head movement is going to be insignificant because of the overall size of the transaction. In a perfect world, no tables would ever be fragmented. But the trade off is in maintenance. You're going to go through alot of work to keep your one large table always contiguous, keep your data files always contiguous to shave a few millseconds off a long transaction. Beth -Original Message- Sent: Wednesday, February 27, 2002 7:43 AM To: Multiple recipients of list ORACLE-L I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents "randomly" allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seefelt, Beth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Old Chestnut: Tablespace Fragmentation
Your best best is to quantify this mathematically. Take the following example: Case 1: 100GB table, one extent Case 2: 100GB table, 1000 extents Assume: a) track to track seeks are 'free' b) random seeks are 20ms c) Block size is 16KB d) db_file_multiblock_read_count=16 e) multiblock read time=8.6ms (29MB/s conservative for 10k drives) f) total # reads=409600 g) one drive only (a very big one...) Case1: Time for FTS= 409600*8.6ms=3522s (~ 1 hour) Case2: Time for FTS= 3522s (as above) PLUS 1000*20ms= 20s - TOTAL=3542s The difference is minor in this case (0.5% greater elapsed time) and 1000 extents would put each at ~100MB in this case. If you had 1 million extents, it would be a different story - about 668% longer... Hope that helps - there's an infinite number of shades of grey, so it's important to do the math! Regards James Bill Buchan wrote: > > > I know this one has been done to death: use uniform extents to avoid > fragmentation; multiple extents don't hurt (within limits). > > But what if: > > Data Warehouse, one big table on a single disk, full table (batch) > scan, no concurrent transactions on the database (so no contention for > the disk), no fragmentation at the file system level, initially empty > buffer cache (startup), read-only operation so DBWR isn't doing > anything on this disk. Basically I want to read one data file from > end to end. Surely it would make sense to have the disk read moving > smoothly from one end of the disk to the other rather than bouncing > about all over the place as it may do with multiple extents "randomly" > allocated. > > Any thoughts? > > Thanks > - Bill. > -- James Morle Scale Abilities, Ltd http://www.scaleabilities.co.uk Author of "Scaling Oracle8i - Building Highly Scalable OLTP System Architectures" -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: James Morle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Old Chestnut: Tablespace Fragmentation
I know this one has been done to death: use uniform extents to avoid fragmentation; multiple extents don't hurt (within limits). But what if: Data Warehouse, one big table on a single disk, full table (batch) scan, no concurrent transactions on the database (so no contention for the disk), no fragmentation at the file system level, initially empty buffer cache (startup), read-only operation so DBWR isn't doing anything on this disk. Basically I want to read one data file from end to end. Surely it would make sense to have the disk read moving smoothly from one end of the disk to the other rather than bouncing about all over the place as it may do with multiple extents "randomly" allocated. Any thoughts? Thanks - Bill. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Buchan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: FRAGMENTATION ISSUE?
Shaibal is right on target. If you're going to go to the trouble to defragment then you may as well switch to uniform extent sizing so you don't have the problem in the future. If you have the room you can do it in stages. First create an index tablespace and rebuild all your indexes in that. Then create a table tablespace(s), then you can export all the tables, drop them and recreate the table structures with uniform extents or locally managed and import them. If you have tables of widely varying size then you could set up multiple tablespaces (ts_small, ts_medium, etc). Jay Miller -Original Message- Sent: Thursday, February 07, 2002 8:03 PM To: Multiple recipients of list ORACLE-L Seema, >From your posting, it is not clear to me about the content of the mentioned tablespace. If it contains all of one users schema you can just take export for that particular schema. The step when you are recreating the tablespace, create it with pct increase 0 and do create initial and next extents same size or you can create the tablespace as a locally managed tablespace - this is where your fragmentation is going to be stopped. The rest looks fine to me. Slightly off topic, it is not good practice to create everything belong to a owner in the same tablespace. For sure tables and indexes should have their seperate tablespaces. Shaibal >From: "Seema Singh" >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L >Subject: FRAGMENTATION ISSUE? >Date: Thu, 07 Feb 2002 13:07:10 -0800 > >Hi >one of my tablespace is highly fragmented and that tablespace >contains >almost all objects and its default tablespace of one of users .I >want to >correct it thru export/import.Let me correct if any thing missing >please. >-Full export of current instance. >-Drop that tablespace including contents.(Drop only one tablespace) >-Create that tablespace >-import the data thru fromuser touser option. >-compile the object if required >Is anything missing in this list.The above are overall steps.Let me >know >this will remove the fragmentation of that tablespace or not?What is >the >risk factor to do this? >Thx >-sEEMA > > > >_ >Chat with friends online, try MSN Messenger: >http://messenger.msn.com > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Seema Singh > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing >Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Send and receive Hotmail on your mobile device: Click Here <http://go.msn.com/bql/hmtag2_etl_EN.asp> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaibal Talukder INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Fragmentation of data dictionary
Title: Fragmentation of data dictionary Helmut, X$ tables are NEVER stored in the disk. THey are just memory structures in the SGA and the contents are zeroed (are reset) when you shutdown the database. THey will not cause data dictionary fragmentation. Best Regards,K GopalakrishnanBangalore, INDIA -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Daiminger, HelmutSent: Tuesday, February 12, 2002 5:53 AMTo: Multiple recipients of list ORACLE-LSubject: Fragmentation of data dictionary Hi! I was wondering whether the Oracle data dictionary gets fragmented and whether the dba needs to do something about it. E.g. if granting tons of rights through grants directly to a user (and later revoke them), does the data dictionary get fragmented (i.e. the x$ tables that hold that information)? Would it be beneficial to reorganise the dd (or at least rebuild the indexes)? Same thing with creating tons of temporary tables that are created during a session and get dropped at the end of a session. Does this fragment the dd? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Fragmentation of data dictionary
Title: Fragmentation of data dictionary Hi! I was wondering whether the Oracle data dictionary gets fragmented and whether the dba needs to do something about it. E.g. if granting tons of rights through grants directly to a user (and later revoke them), does the data dictionary get fragmented (i.e. the x$ tables that hold that information)? Would it be beneficial to reorganise the dd (or at least rebuild the indexes)? Same thing with creating tons of temporary tables that are created during a session and get dropped at the end of a session. Does this fragment the dd? This is 8.1.7 on Sun Solaris. Thanks, Helmut
Re: FRAGMENTATION ISSUE?
Seema, From your posting, it is not clear to me about the content of the mentioned tablespace. If it contains all of one users schema you can just take export for that particular schema. The step when you are recreating the tablespace, create it with pct increase 0 and do create initial and next extents same size or you can create the tablespace as a locally managed tablespace - this is where your fragmentation is going to be stopped. The rest looks fine to me. Slightly off topic, it is not good practice to create everything belong to a owner in the same tablespace. For sure tables and indexes should have their seperate tablespaces. Shaibal >From: "Seema Singh" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: FRAGMENTATION ISSUE? >Date: Thu, 07 Feb 2002 13:07:10 -0800 > >Hi >one of my tablespace is highly fragmented and that tablespace >contains >almost all objects and its default tablespace of one of users .I >want to >correct it thru export/import.Let me correct if any thing missing >please. >-Full export of current instance. >-Drop that tablespace including contents.(Drop only one tablespace) >-Create that tablespace >-import the data thru fromuser touser option. >-compile the object if required >Is anything missing in this list.The above are overall steps.Let me >know >this will remove the fragmentation of that tablespace or not?What is >the >risk factor to do this? >Thx >-sEEMA > > > >_ >Chat with friends online, try MSN Messenger: >http://messenger.msn.com > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Seema Singh > INET: [EMAIL PROTECTED] > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California -- Public Internet access / Mailing >Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). Send and receive Hotmail on your mobile device: Click Here -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaibal Talukder INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
FRAGMENTATION ISSUE?
Hi one of my tablespace is highly fragmented and that tablespace contains almost all objects and its default tablespace of one of users .I want to correct it thru export/import.Let me correct if any thing missing please. -Full export of current instance. -Drop that tablespace including contents.(Drop only one tablespace) -Create that tablespace -import the data thru fromuser touser option. -compile the object if required Is anything missing in this list.The above are overall steps.Let me know this will remove the fragmentation of that tablespace or not?What is the risk factor to do this? Thx -sEEMA _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-04031, how to prevent fragmentation
Thanx to all, who answered. Seems to be Bug 1397603 "MEMORY LEAKS OBSERVED WHEN RUNNING CONNECTION TESTS" as I can observe growing counts for 'state objects'. => apply patchset 8.1.7.2 or => set _db_handles_cached = 0 (only workaround!) Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon:+49 241 / 9 18 79-33 Fax:+49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-04031, how to prevent fragmentation
Thanx to all, who answered. Seems to be Bug 1397603 "MEMORY LEAKS OBSERVED WHEN RUNNING CONNECTION TESTS" as I can observe growing counts for 'state objects'. => apply patchset 8.1.7.2 or => set _db_handles_cached = 0 (only workaround!) Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon:+49 241 / 9 18 79-33 Fax:+49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: ORA-04031, how to prevent fragmentation?
Marc, If you haven't applied the 8.1.7.2 patchset, your ORA-4031 errors could be due to bug 1397603 "MEMORY LEAKS OBSERVED WHEN RUNNING CONNECTION TESTS" If this is the case, you should see a growth in the memory allocated to state objects: select to_char(sysdate,'mm/dd/yy hh24:mi:ss'),s.* from v$SGASTAT s where name = 'State objects'; Growth is normal after the db is started, but should stabilize after 20-30 minutes of normal activity. If you are seeing growth I would recommend applying the 8.1.7.2 patchet. As a workaround, you can set _db_handles_cached = 0 in the init.ora, just keep in mind that this will increase latch contention so you could experience performance degradation if you already have a problem with latch contention. HTH, -- Anita --- "Blum, Marc" <[EMAIL PROTECTED]> wrote: > Dear list, > > we have a small database with very few users, Oracle > 8.1.7, NT 4 SP5. A > NT-service holds a permanent connection and checks, > if some data export are > about to be done. The database had now an uptime of > several months and each > new connect raises ORA-04031. Seems to be a > fragmentation problem of the > shared pool. How can I prevent that? > > Thanx very much > > Mit freundlichen Grüßen > > i.A. Marc Blum > > SOPTIM AG > Grüner Weg 22-24 > D-52070 Aachen > > Telefon: +49 241 / 9 18 79-33 > Fax: +49 241 / 15 40 38 > > mailto:[EMAIL PROTECTED] > http://www.soptim.de __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: A. Bardeen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-04031, how to prevent fragmentation?
Regarding flushing the shared pool... A couple of weeks ago I had hard copies of 2 Metalink docs side by side on my desk and I highlighted paragraphs on each which were completely contradictory. One said flushing did absolutely no good and the other said it could help. In practice it doesn't seem to do much for severe 4031 type fragmentation. Pinning large stuff at db start up can help but if you've got a lot of SQL with literals then consider setting cursor_sharing = TRUE as well. With a small db just enlarging the shared pool may be enough. Of course if you have control over the code then I think using bind variables is still the preferred solution. Steve Orr -Original Message- Sent: Friday, October 26, 2001 6:05 AM To: Multiple recipients of list ORACLE-L You can either issue an "alter system flush shared pool", or increase the value of your shared_pool_size. Another option would be to pin some or your most used/larger packeges in to your shared pool. HTH Mark -Original Message- Sent: Friday, October 26, 2001 10:55 To: Multiple recipients of list ORACLE-L Dear list, we have a small database with very few users, Oracle 8.1.7, NT 4 SP5. A NT-service holds a permanent connection and checks, if some data export are about to be done. The database had now an uptime of several months and each new connect raises ORA-04031. Seems to be a fragmentation problem of the shared pool. How can I prevent that? Thanx very much Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon:+49 241 / 9 18 79-33 Fax:+49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: i need check fragmentation of index
heading ' 'col uu_meg_pct format 99.90 fold_after heading ' 'col bt_meg format 999.90 heading ' 'col bt_meg_pct format 99.90 fold_after heading ' 'col btu_meg format 999.90 heading ' 'col btu_meg_pct format 99.90 fold_after heading ' 'col btuu_meg format 999.90 heading ' 'col btuu_meg_pct format 99.90 fold_after heading ' 'col btuub_meg format 999.90 heading ' 'col btuub_meg_pct format 99.90 fold_after heading ' 'set pause onselect dummy_col_0,'Index Name.', NAME, ' ' dummy_col_1,'Leaf Rows..', LF_ROWS, ' ','Leaf Block Size', LF_BLK_LEN,'Deleted Leaf Rows..', DEL_LF_ROWS, ' ','Leaf Row Size..', LF_ROWS_LEN / LF_ROWS lf_row_size,'Branch Rows', BR_ROWS, ' ','Leaf Rows Per Block', LF_BLK_LEN / (LF_ROWS_LEN / LF_ROWS)lf_row_per_blk,'Distinct Keys..', DISTINCT_KEYS, ' ','Branch Block Size..', BR_BLK_LEN,'Max Common Key.', MOST_REPEATED_KEY, ' ','Branch Row Size', BR_ROWS_LEN / (BR_ROWS + 1) br_row_size,'Avg Common Key.', ROWS_PER_KEY, ' ','Branch Rows Per Block..', BR_BLK_LEN / ((BR_ROWS_LEN / (BR_ROWS + 1)) + 1)br_row_per_blk,'Height Of B-Tree...', HEIGHT,'Reads Per Access...', BLKS_GETS_PER_ACCESS,' ' dummy_col_2,'Index Meg', (BLOCKS * &BLOCK_SIZE_K) / 1024 meg,'Leaf Meg/Pct.', (LF_BLKS * &BLOCK_SIZE_K) / 1024 lf_meg, ' /', (LF_BLKS / BLOCKS) * 100 lf_meg_pct,'Branch Meg/Pct...', (BR_BLKS * &BLOCK_SIZE_K) / 1024 br_meg, ' /', (BR_BLKS / BLOCKS) * 100 br_meg_pct,'Unused Meg/Pct...', ((BLOCKS - (LF_BLKS + BR_BLKS)) *&BLOCK_SIZE_K) / 1024 uu_meg, ' /', ((BLOCKS - (LF_BLKS + BR_BLKS)) / BLOCKS) *100 uu_meg_pct,'B-Tree Meg/Pct...', ((LF_BLKS + BR_BLKS) * &BLOCK_SIZE_K) / 1024bt_meg, ' /', ((LF_BLKS + BR_BLKS) / BLOCKS) * 100bt_meg_pct,'B-Tree Used Meg/Pct..', (USED_SPACE / 1024) / 1024 btu_meg, ' /', PCT_USED btu_meg_pct,'B-Tree UnUsed Meg/Pct', ((BTREE_SPACE - USED_SPACE) / 1024) / 1024btuu_meg, ' /', 100 - PCT_USED btuu_meg_pct,'B-Tree UnUsable Meg/Pct..', (DEL_LF_ROWS_LEN / 1024) / 1024 btuub_meg, '/', (((DEL_LF_ROWS_LEN / 1024) / 1024) / (((LF_BLKS + BR_BLKS) * &BLOCK_SIZE_K) /1024)) * 100 btuub_meg_pctfrom index_stats;---HTHMark-Original Message-ThapliyalSent: Thursday, October 25, 2001 22:16To: Multiple recipients of list ORACLE-Lhi @lexone of the good ways to check this is to do a analyzeindex .. validate structure and look at the statisticsfrom index_statsDeepak--- Alexander Ordonez <[EMAIL PROTECTED]> wrote:> hi gurus, how check the fragmentation on index...??> ahy idea???> please i need your help!!>> @lex>> Lic. Alexander Ordsqez Arroyo> Caja Costarricense del Seguro Social>> Soporte Ticnico - Divisisn de Informatica>> Telefono: 295-2004, San Josi, Costa Rica>> [EMAIL PROTECTED] Icq# 30173325>>> The true is out there in WWW>>> --> Please see the official ORACLE-L FAQ:> http://www.orafaq.com> --> Author: Alexander Ordonez> INET: [EMAIL PROTECTED]>> Fat City Network Services -- (858) 538-5051 FAX:> (858) 538-5051> San Diego, California -- Public Internet> access / Mailing Lists>> To REMOVE yourself from this mailing list, send an> E-Mail message> to: [EMAIL PROTECTED] (note EXACT spelling of> 'ListGuru') and in> the message BODY, include a line containing: UNSUB> ORACLE-L> (or the name of mailing list you want to be removed> from). You may> also send the HELP command for other information> (like subscribing).__Do You Yahoo!?Make a great connection at Yahoo!
Re: ORA-04031, how to prevent fragmentation?
Hi, *Pin packages into the shared pool *enlarge the shared pool *flush the shared pool at regular intervals *use bind variables i.s.o hardcoded values or a combination of those Jack "Blum, Marc" <[EMAIL PROTECTED]>@fatcity.com on 26-10-2001 11:55:18 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Dear list, we have a small database with very few users, Oracle 8.1.7, NT 4 SP5. A NT-service holds a permanent connection and checks, if some data export are about to be done. The database had now an uptime of several months and each new connect raises ORA-04031. Seems to be a fragmentation problem of the shared pool. How can I prevent that? Thanx very much Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon:+49 241 / 9 18 79-33 Fax: +49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst & Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst & Young. Ernst & Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst & Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst & Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ORA-04031, how to prevent fragmentation?
You can either issue an "alter system flush shared pool", or increase the value of your shared_pool_size. Another option would be to pin some or your most used/larger packeges in to your shared pool. HTH Mark -Original Message- Sent: Friday, October 26, 2001 10:55 To: Multiple recipients of list ORACLE-L Dear list, we have a small database with very few users, Oracle 8.1.7, NT 4 SP5. A NT-service holds a permanent connection and checks, if some data export are about to be done. The database had now an uptime of several months and each new connect raises ORA-04031. Seems to be a fragmentation problem of the shared pool. How can I prevent that? Thanx very much Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon:+49 241 / 9 18 79-33 Fax:+49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: i need check fragmentation of index
99.90 fold_after heading ' ' col btuub_megformat 999.90 heading ' ' col btuub_meg_pctformat 99.90 fold_after heading ' ' set pause on select dummy_col_0, 'Index Name.', NAME, ' ' dummy_col_1, 'Leaf Rows..', LF_ROWS, ' ', 'Leaf Block Size', LF_BLK_LEN, 'Deleted Leaf Rows..', DEL_LF_ROWS, ' ', 'Leaf Row Size..', LF_ROWS_LEN / LF_ROWS lf_row_size, 'Branch Rows', BR_ROWS, ' ', 'Leaf Rows Per Block', LF_BLK_LEN / (LF_ROWS_LEN / LF_ROWS) lf_row_per_blk, 'Distinct Keys..', DISTINCT_KEYS, ' ', 'Branch Block Size..', BR_BLK_LEN, 'Max Common Key.', MOST_REPEATED_KEY, ' ', 'Branch Row Size', BR_ROWS_LEN / (BR_ROWS + 1) br_row_size, 'Avg Common Key.', ROWS_PER_KEY, ' ', 'Branch Rows Per Block..', BR_BLK_LEN / ((BR_ROWS_LEN / (BR_ROWS + 1)) + 1) br_row_per_blk, 'Height Of B-Tree...', HEIGHT, 'Reads Per Access...', BLKS_GETS_PER_ACCESS, ' ' dummy_col_2, 'Index Meg', (BLOCKS * &BLOCK_SIZE_K) / 1024 meg, 'Leaf Meg/Pct.', (LF_BLKS * &BLOCK_SIZE_K) / 1024 lf_meg, ' /', (LF_BLKS / BLOCKS) * 100 lf_meg_pct, 'Branch Meg/Pct...', (BR_BLKS * &BLOCK_SIZE_K) / 1024 br_meg, ' /', (BR_BLKS / BLOCKS) * 100 br_meg_pct, 'Unused Meg/Pct...', ((BLOCKS - (LF_BLKS + BR_BLKS)) * &BLOCK_SIZE_K) / 1024 uu_meg, ' /', ((BLOCKS - (LF_BLKS + BR_BLKS)) / BLOCKS) * 100 uu_meg_pct, 'B-Tree Meg/Pct...', ((LF_BLKS + BR_BLKS) * &BLOCK_SIZE_K) / 1024 bt_meg, ' /', ((LF_BLKS + BR_BLKS) / BLOCKS) * 100 bt_meg_pct, 'B-Tree Used Meg/Pct..', (USED_SPACE / 1024) / 1024 btu_meg, ' /', PCT_USED btu_meg_pct, 'B-Tree UnUsed Meg/Pct', ((BTREE_SPACE - USED_SPACE) / 1024) / 1024 btuu_meg, ' /', 100 - PCT_USED btuu_meg_pct, 'B-Tree UnUsable Meg/Pct..', (DEL_LF_ROWS_LEN / 1024) / 1024 btuub_meg, ' /', (((DEL_LF_ROWS_LEN / 1024) / 1024) / (((LF_BLKS + BR_BLKS) * &BLOCK_SIZE_K) / 1024)) * 100 btuub_meg_pct from index_stats; --- HTH Mark -Original Message- Thapliyal Sent: Thursday, October 25, 2001 22:16 To: Multiple recipients of list ORACLE-L hi @lex one of the good ways to check this is to do a analyze index .. validate structure and look at the statistics from index_stats Deepak --- Alexander Ordonez <[EMAIL PROTECTED]> wrote: > hi gurus, how check the fragmentation on index...?? > ahy idea??? > please i need your help!! > > @lex > > Lic. Alexander Ordsqez Arroyo > Caja Costarricense del Seguro Social > > Soporte Ticnico - Divisisn de Informatica > > Telefono: 295-2004, San Josi, Costa Rica > > [EMAIL PROTECTED]Icq# 30173325 > > > The true is out there in WWW > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Alexander Ordonez > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ORA-04031, how to prevent fragmentation?
Dear list, we have a small database with very few users, Oracle 8.1.7, NT 4 SP5. A NT-service holds a permanent connection and checks, if some data export are about to be done. The database had now an uptime of several months and each new connect raises ORA-04031. Seems to be a fragmentation problem of the shared pool. How can I prevent that? Thanx very much Mit freundlichen Grüßen i.A. Marc Blum SOPTIM AG Grüner Weg 22-24 D-52070 Aachen Telefon:+49 241 / 9 18 79-33 Fax:+49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: i need check fragmentation of index
hi @lex one of the good ways to check this is to do a analyze index .. validate structure and look at the statistics from index_stats Deepak --- Alexander Ordonez <[EMAIL PROTECTED]> wrote: > hi gurus, how check the fragmentation on index...?? > ahy idea??? > please i need your help!! > > @lex > > Lic. Alexander Ordóñez Arroyo > Caja Costarricense del Seguro Social > > Soporte Técnico - División de Informática > > Telefono: 295-2004, San José, Costa Rica > > [EMAIL PROTECTED]Icq# 30173325 > > > The true is out there in WWW > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Alexander Ordonez > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
i need check fragmentation of index
hi gurus, how check the fragmentation on index...?? ahy idea??? please i need your help!! @lex Lic. Alexander Ordóñez Arroyo Caja Costarricense del Seguro Social Soporte Técnico - División de Informática Telefono: 295-2004, San José, Costa Rica [EMAIL PROTECTED]Icq# 30173325 The true is out there in WWW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexander Ordonez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Tablespace fragmentation
Hi How to remove tablespace fragmentation? Export/drop/import? If I export the db and import user wise then is it solve the problem? What are the precausion should taken care when we do such activities. Thanks -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Table Fragmentation - Please Help - Urgent
Having many extents is not a bad thing. It is when the data is skewed all over extents that are scattered all over the disk. The query you sent does not address that, it just tells you how large the table is and how many extents. In reguards to extents, there are two things to worry about. The first is dictionary performance when using dictionary managed extents. This is reguards to quering the FET$ and UET$ table and C_TS# cluster. As for performance of the query at hand performance is almost compeletely uneffected. For locally managed extents, the problem is slightly different as a query of dba_extents cause a physical read across all the bitmaps in all the tablespaces. The other problem is without using uniform size of extents, you will have many unusable fragments and this may cause probems of wasted space and poor performance. This also comes up when ST (space transaction) enqueue is held for periods of time to clean up these problems. This enqeue is serial and will show up greatly with dictionary managed extents and smon processing. But the ammount of extents has no real bearing on the performance of a particular query in itself. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence OCP MCSE MCP A+ RAPTOR CNA Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Wednesday, August 08, 2001 7:11 AM To: Multiple recipients of list ORACLE-L SELECT SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024 Kbytes, EXTENTS FROM DBA_SEGMENTS / - Mensaje original - De: "Viraj Luthra" <[EMAIL PROTECTED]> Fecha: Miércoles, Agosto 8, 2001 11:21 am Asunto: Table Fragmentation - Please Help - Urgent > Hello All, > > Please help in sending me a script for getting to know whether a > table is fragmented or not? Also need to know whether I will need > to Analyse the table for getting accurate results, and if yes how > to do that? > > Thanks > > raja > > > Get 250 color business cards for FREE! > http://businesscards.lycos.com/vp/fastpath/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Viraj Luthra > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > --- > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L (or the > name of mailing list you want to be removed from). You may also send > the HELP command for other information (like subscribing). > ___ Date de alta en inicia y dispondrás de correo y espacio para tu página personal. http://inicia.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Table Fragmentation - Please Help - Urgent
SELECT SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024 Kbytes, EXTENTS FROM DBA_SEGMENTS / - Mensaje original - De: "Viraj Luthra" <[EMAIL PROTECTED]> Fecha: Miércoles, Agosto 8, 2001 11:21 am Asunto: Table Fragmentation - Please Help - Urgent > Hello All, > > Please help in sending me a script for getting to know whether a > table is fragmented or not? Also need to know whether I will need > to Analyse the table for getting accurate results, and if yes how > to do that? > > Thanks > > raja > > > Get 250 color business cards for FREE! > http://businesscards.lycos.com/vp/fastpath/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Viraj Luthra > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > --- > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > ___ Date de alta en inicia y dispondrás de correo y espacio para tu página personal. http://inicia.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Table Fragmentation - Please Help - Urgent
Hello All, Please help in sending me a script for getting to know whether a table is fragmented or not? Also need to know whether I will need to Analyse the table for getting accurate results, and if yes how to do that? Thanks raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Table fragmentation
Title: RE: Table fragmentation See All About Oracle Database Fragmentation by Craig A. Shallahamer at www.orapub.com. Click on RESEARCH/PAPERS and enjoy the bounty. -Original Message- From: Viraj Luthra [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 24, 2001 9:51 PM To: Multiple recipients of list ORACLE-L Subject: Table fragmentation Hello all, How do I come to know about table fragmentation, I know the info. on tablespace but I need TABLE fragmentation. Please advise as to what kinds of info. and corresponding script would be advantageous to know. rgds, raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Table fragmentation
Hello all, How do I come to know about table fragmentation, I know the info. on tablespace but I need TABLE fragmentation. Please advise as to what kinds of info. and corresponding script would be advantageous to know. rgds, raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Fragmentation & Locally Managed Tablespaces
And this only matters for queries for data dictionary views. I also beleave that number of extents matter if you have parallel processing. I saw something about it on Johnathan Lewis web site. Alex Hillman -Original Message- Sent: Thursday, June 28, 2001 8:36 AM To: Multiple recipients of list ORACLE-L Rachel, That maximum isn't a hard limit. Oracle themselves impose no limit of this kind. After you use all of the available space in the segment header block, any additional space map entries are overflowed into additional extent maps within the segment. This means that because the used extent information isn't cached in the data dictionary, queries against dba_extents etc. can cause many blocks to be read from LMTs if the map entries have overflowed in this manner. Therefore, the limitation is more of a recommendation to ensure that good performance is maintained. Regards, Mike |+---> || "Rachel | || Carmichael" | || | || | || 06/28/01 | || 10:46 AM | || Please | || respond to | || ORACLE-L | || | |+---> >---| | | | To: Multiple recipients of list ORACLE-L| | <[EMAIL PROTECTED]> | | cc: (bcc: Mike Hately/ETECH) | | Subject: Re: Fragmentation & Locally Managed Tablespaces| >---| so we are going back to the Oracle specified maximum extents for a particular blocksize? >From: Paul Drake <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Re: Fragmentation & Locally Managed Tablespaces >Date: Thu, 28 Jun 2001 00:15:50 -0800 > >VIVEK_SHARMA wrote: > > > > Is Fragmentation of Objects meaningless in Locally managed Tablespaces > > Assuming EXTENTS is 3,000 for Some of the Objects > > > > Or Do the Objects need to be DE-Fragmented using exp/imp ? > > > >Vivek, > >funny, I was just looking into this tonight. > >what is your block size? > >I would recommend a quick visit to our friend Steve Adams site: > >http://www.ixora.com.au/tips/creation/extents.htm > >"For this reason, we recommend that the number of extents per segment in >locally managed tablespaces be limited to the number of rows that can be >accommodated in the extent map within the segment header block >- that is, approximately (db_block_size / 16) - 7." > >for an 8 KB block size - that is 505 extents per segment. > >Those objects would be good candidates to move to a different (new?) >tablespace with a larger (uniform) extent size. > >Paul >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Paul Drake > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists --
Re: Fragmentation & Locally Managed Tablespaces
yep. what I was wondering was at what point for storage in general, are the "points of inflection" for # of segments per tablespace, # of extents per segment - where a performance hit occurs, when the header block overflows. I saw that a tablespace had > 34000 extents in it - but then saw that only 1 segment had > 505 extents - so I let it go. just didn't want the extra I/O fetch every time. Paul Rachel Carmichael wrote: > > Mike, > > Thanks, I didn't really believe Oracle would "take back" the maxextents > unlimited option, I just thought it was funny that the recommendation was > now going back to the original extent recommendations :) > > Rachel > > >From: [EMAIL PROTECTED] > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: Re: Fragmentation & Locally Managed Tablespaces > >Date: Thu, 28 Jun 2001 04:35:35 -0800 > > > > > > > >Rachel, > >That maximum isn't a hard limit. Oracle themselves impose no limit of this > >kind. > >After you use all of the available space in the segment header block, any > >additional space map entries are overflowed into additional extent maps > >within > >the segment. > >This means that because the used extent information isn't cached in the > >data > >dictionary, queries against dba_extents etc. can cause many blocks to be > >read > >from LMTs if the map entries have overflowed in this manner. > > > >Therefore, the limitation is more of a recommendation to ensure that good > >performance is maintained. > > > >Regards, > >Mike > > > > > _ > Get your FREE download of MSN Explorer at http://explorer.msn.com > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Rachel Carmichael > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Drake INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Fragmentation & Locally Managed Tablespaces
Mike, Thanks, I didn't really believe Oracle would "take back" the maxextents unlimited option, I just thought it was funny that the recommendation was now going back to the original extent recommendations :) Rachel >From: [EMAIL PROTECTED] >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Re: Fragmentation & Locally Managed Tablespaces >Date: Thu, 28 Jun 2001 04:35:35 -0800 > > > >Rachel, >That maximum isn't a hard limit. Oracle themselves impose no limit of this >kind. >After you use all of the available space in the segment header block, any >additional space map entries are overflowed into additional extent maps >within >the segment. >This means that because the used extent information isn't cached in the >data >dictionary, queries against dba_extents etc. can cause many blocks to be >read >from LMTs if the map entries have overflowed in this manner. > >Therefore, the limitation is more of a recommendation to ensure that good >performance is maintained. > >Regards, >Mike > > _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Fragmentation & Locally Managed Tablespaces
According to the Oracle documentation, it's the OS block size. --- VIVEK_SHARMA <[EMAIL PROTECTED]> a écrit : > > Thanks indeed for the Article's Excerpt , Paul > > Our db_block_size=8K > > > > -Original Message- > > From: Paul Drake [SMTP:[EMAIL PROTECTED]] > > Sent: Thursday, June 28, 2001 1:46 PM > > To: Multiple recipients of list ORACLE-L > > Subject:Re: Fragmentation & Locally Managed > Tablespaces > > > > VIVEK_SHARMA wrote: > > > > > > Is Fragmentation of Objects meaningless in > Locally managed > > Tablespaces > > > Assuming EXTENTS is 3,000 for Some of the > Objects > > > > > > Or Do the Objects need to be DE-Fragmented using > exp/imp ? > > > > > > > Vivek, > > > > funny, I was just looking into this tonight. > > > > what is your block size? > > > > I would recommend a quick visit to our friend > Steve Adams site: > > > > http://www.ixora.com.au/tips/creation/extents.htm > > > > "For this reason, we recommend that the number of > extents per segment > > in > > locally managed tablespaces be limited to the > number of rows that can > > be > > accommodated in the extent map within the segment > header block > > - that is, approximately (db_block_size / 16) - > 7." > > > > for an 8 KB block size - that is 505 extents per > segment. > > > > Those objects would be good candidates to move to > a different (new?) > > tablespace with a larger (uniform) extent size. > > > > Paul > > -- > > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > > -- > > Author: Paul Drake > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- (858) 538-5051 > FAX: (858) 538-5051 > > San Diego, California-- Public Internet > access / Mailing Lists > > > > > To REMOVE yourself from this mailing list, send an > E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > > the message BODY, include a line containing: UNSUB > ORACLE-L > > (or the name of mailing list you want to be > removed from). You may > > also send the HELP command for other information > (like subscribing). > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: VIVEK_SHARMA > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Fragmentation & Locally Managed Tablespaces
Rachel, That maximum isn't a hard limit. Oracle themselves impose no limit of this kind. After you use all of the available space in the segment header block, any additional space map entries are overflowed into additional extent maps within the segment. This means that because the used extent information isn't cached in the data dictionary, queries against dba_extents etc. can cause many blocks to be read from LMTs if the map entries have overflowed in this manner. Therefore, the limitation is more of a recommendation to ensure that good performance is maintained. Regards, Mike |+---> || "Rachel | || Carmichael" | || | || | || 06/28/01 | || 10:46 AM | || Please | || respond to | || ORACLE-L | || | |+---> >---| | | | To: Multiple recipients of list ORACLE-L| | <[EMAIL PROTECTED]> | | cc: (bcc: Mike Hately/ETECH) | | Subject: Re: Fragmentation & Locally Managed Tablespaces| >---| so we are going back to the Oracle specified maximum extents for a particular blocksize? >From: Paul Drake <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Re: Fragmentation & Locally Managed Tablespaces >Date: Thu, 28 Jun 2001 00:15:50 -0800 > >VIVEK_SHARMA wrote: > > > > Is Fragmentation of Objects meaningless in Locally managed Tablespaces > > Assuming EXTENTS is 3,000 for Some of the Objects > > > > Or Do the Objects need to be DE-Fragmented using exp/imp ? > > > >Vivek, > >funny, I was just looking into this tonight. > >what is your block size? > >I would recommend a quick visit to our friend Steve Adams site: > >http://www.ixora.com.au/tips/creation/extents.htm > >"For this reason, we recommend that the number of extents per segment in >locally managed tablespaces be limited to the number of rows that can be >accommodated in the extent map within the segment header block >- that is, approximately (db_block_size / 16) - 7." > >for an 8 KB block size - that is 505 extents per segment. > >Those objects would be good candidates to move to a different (new?) >tablespace with a larger (uniform) extent size. > >Paul >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Paul Drake > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Fragmentation & Locally Managed Tablespaces
so we are going back to the Oracle specified maximum extents for a particular blocksize? >From: Paul Drake <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: Re: Fragmentation & Locally Managed Tablespaces >Date: Thu, 28 Jun 2001 00:15:50 -0800 > >VIVEK_SHARMA wrote: > > > > Is Fragmentation of Objects meaningless in Locally managed Tablespaces > > Assuming EXTENTS is 3,000 for Some of the Objects > > > > Or Do the Objects need to be DE-Fragmented using exp/imp ? > > > >Vivek, > >funny, I was just looking into this tonight. > >what is your block size? > >I would recommend a quick visit to our friend Steve Adams site: > >http://www.ixora.com.au/tips/creation/extents.htm > >"For this reason, we recommend that the number of extents per segment in >locally managed tablespaces be limited to the number of rows that can be >accommodated in the extent map within the segment header block >- that is, approximately (db_block_size / 16) - 7." > >for an 8 KB block size - that is 505 extents per segment. > >Those objects would be good candidates to move to a different (new?) >tablespace with a larger (uniform) extent size. > >Paul >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Paul Drake > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Fragmentation & Locally Managed Tablespaces
Thanks indeed for the Article's Excerpt , Paul Our db_block_size=8K > -Original Message- > From: Paul Drake [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, June 28, 2001 1:46 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: Fragmentation & Locally Managed Tablespaces > > VIVEK_SHARMA wrote: > > > > Is Fragmentation of Objects meaningless in Locally managed > Tablespaces > > Assuming EXTENTS is 3,000 for Some of the Objects > > > > Or Do the Objects need to be DE-Fragmented using exp/imp ? > > > > Vivek, > > funny, I was just looking into this tonight. > > what is your block size? > > I would recommend a quick visit to our friend Steve Adams site: > > http://www.ixora.com.au/tips/creation/extents.htm > > "For this reason, we recommend that the number of extents per segment > in > locally managed tablespaces be limited to the number of rows that can > be > accommodated in the extent map within the segment header block > - that is, approximately (db_block_size / 16) - 7." > > for an 8 KB block size - that is 505 extents per segment. > > Those objects would be good candidates to move to a different (new?) > tablespace with a larger (uniform) extent size. > > Paul > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Paul Drake > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Fragmentation & Locally Managed Tablespaces
VIVEK_SHARMA wrote: > > Is Fragmentation of Objects meaningless in Locally managed Tablespaces > Assuming EXTENTS is 3,000 for Some of the Objects > > Or Do the Objects need to be DE-Fragmented using exp/imp ? > Vivek, funny, I was just looking into this tonight. what is your block size? I would recommend a quick visit to our friend Steve Adams site: http://www.ixora.com.au/tips/creation/extents.htm "For this reason, we recommend that the number of extents per segment in locally managed tablespaces be limited to the number of rows that can be accommodated in the extent map within the segment header block - that is, approximately (db_block_size / 16) - 7." for an 8 KB block size - that is 505 extents per segment. Those objects would be good candidates to move to a different (new?) tablespace with a larger (uniform) extent size. Paul -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Drake INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Fragmentation & Locally Managed Tablespaces
3000 extents seems a lot to me. I usually kept the number of extents for a segment below the number of blocks kept in the extent map, the extent map depends on the OS block size. For example if the block size is 8K you should not have more than 504 extents. --- VIVEK_SHARMA <[EMAIL PROTECTED]> a écrit : > > Is Fragmentation of Objects meaningless in Locally > managed Tablespaces > Assuming EXTENTS is 3,000 for Some of the Objects > > Or Do the Objects need to be DE-Fragmented using > exp/imp ? > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: VIVEK_SHARMA > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California-- Public Internet > access / Mailing Lists > > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Pour faire vos courses sur le Net, Yahoo! Shopping : http://fr.shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fragmentation & Locally Managed Tablespaces
Is Fragmentation of Objects meaningless in Locally managed Tablespaces Assuming EXTENTS is 3,000 for Some of the Objects Or Do the Objects need to be DE-Fragmented using exp/imp ? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: VIVEK_SHARMA INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Index fragmentation
Hi Gurus how to know indexes need to rebuild or Indexes are fragmented?Can you please send scripts if some one have? Thanks in advance -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Index fragmentation
Title: RE: Index fragmentation This old script might work: set verify off set pagesize 35 set linesize 132 set pause on set pause 'Hit enter to continue' set feedback off set showmode off set echo off set space 0 set heading off set termout off pause off column blsk new_value BLOCK_SIZE_K select value / 1024 blsk from v$parameter where name = 'db_block_size'; set termout on pause off PROMPT ACCEPT USER_INPUT1 CHAR PROMPT 'Please enter a index to analyze:>' ACCEPT USER_INPUT2 CHAR PROMPT 'Please enter a owner to analyze:>' PROMPT PROMPT Working, Please wait. PROMPT analyze index &USER_INPUT2..&USER_INPUT1 validate structure; col ROWS_PER_KEY format 999.99 heading ' ' col BLKS_GETS_PER_ACCESS format 99,999.99 heading ' ' select '**', ' Name of the index = ' || NAME, ' Height of the b-tree = ' || HEIGHT, ' Blocks allocated to the index = ' || BLOCKS, ' Number of leaf rows (values in the index) = ' || LF_ROWS, ' Number of leaf blocks in the b-tree = ' || LF_BLKS, ' Sum of the lengths of all the leaf rows = ' || LF_ROWS_LEN, ' Useable space in a leaf block = ' || LF_BLK_LEN, ' Number of branch rows = ' || BR_ROWS, ' Number of branch blocks in the b-tree = ' || BR_BLKS, 'Sum of lengths of all the branch blocks in the b-tree = ' || BR_ROWS_LEN, ' Useable space in a branch block = ' || BR_BLK_LEN, ' Number of deleted leaf rows in the index = ' || DEL_LF_ROWS, ' Total length of all deleted rows in the index = ' || DEL_LF_ROWS_LEN, ' Number of distinct keys in the index = ' || DISTINCT_KEYS, ' How many times the most repeated key is repeated = ' || MOST_REPEATED_KEY, ' Total space currently allocated in the b-tree = ' || BTREE_SPACE, 'Totl space that is currently being used in the b-tree = ' || USED_SPACE, '% of space allocated in the b-tree that is being used = ' || PCT_USED, ' Average number of rows per distinct key = ' || ROWS_PER_KEY, 'Expected number of consistent mode block gets per row = ' || BLKS_GETS_PER_ACCESS from index_stats; col NAME format a30 fold_after col dummy_col_0 fold_after col dummy_col_1 fold_after col dummy_col_2 fold_after col LF_ROWS format 9,999,990 heading ' ' col DEL_LF_ROWS format 9,999,990 heading ' ' col BR_ROWS format 9,999,990 heading ' ' col DISTINCT_KEYS format 9,999,990 heading ' ' col MOST_REPEATED_KEY format 9,999,990 heading ' ' col ROWS_PER_KEY format 9,999,990 heading ' ' col HEIGHT format 0 fold_after heading ' ' col BLKS_GETS_PER_ACCESS format 99,999.99 fold_after heading ' ' col LF_BLK_LEN format 9,990 fold_after heading ' ' col lf_row_size format 990 fold_after heading ' ' col lf_row_per_blk format 990 fold_after heading ' ' col BR_BLK_LEN format 9,990 fold_after heading ' ' col br_row_size format 990 fold_after heading ' ' col br_row_per_blk format 990 fold_after heading ' ' col meg format 999.90 fold_after heading ' ' col lf_meg format 999.90 heading ' ' col lf_meg_pct format 99.90 fold_after heading ' ' col br_meg format 999.90 heading ' ' col br_meg_pct format 99.90 fold_after heading ' ' col uu_meg format 999.90 heading ' ' col uu_meg_pct format 99.90 fold_after heading ' ' col bt_meg format 999.90 heading ' ' col bt_meg_pct format 99.90 fold_after heading ' ' col btu_meg format 999.90 heading ' ' col btu_meg_pct format 99.90 fold_after heading ' ' col btuu_meg format 999.90 heading ' ' col btuu_meg_pct forma
Index fragmentation
Hi Gurus How I can know about Index fragmentation? Thanks in advance for reply -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Need fragmentation reports
Nobody have a script for fragmantation report. - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Monday, April 09, 2001 4:45 PM > > We're trying to determine which partitioned tables > are good candidates for reorganization due to > fragmentation. > > Anybody got any good scripts that would detect > various kinds of fragmentation. Perhaps someone > could point me in the direction of some on various > web sites? > > Thanks in advance for your help. > > Cherie Machler > Gelco Information Network > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bunyamin K.Karadeniz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Need fragmentation reports
We're trying to determine which partitioned tables are good candidates for reorganization due to fragmentation. Anybody got any good scripts that would detect various kinds of fragmentation. Perhaps someone could point me in the direction of some on various web sites? Thanks in advance for your help. Cherie Machler Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Tuning, RAID5, and fragmentation and DBWR
Hey Dana, Are you kidding? A GS140 running OVMS would be my choice of OS/Hardware! OK, OS bigotry aside... ;) The RAID5 *WILL* give you performance problems! RAID5 is the worst performer for write operations. The LGWR,and associated slave processes (and to a somewhat lesser extent, the DBWR) WILL complain LOUDLY. I hope for your sake that the GS140 at least has a lotta memory. CPU-wise, it's 2.5-to-3 times faster than our 4100 5/400, depending on MHz. CPU's not going to be your bottleneck, though. But, if that's all you have to work with, try a few of these: 1) Make your LOG_BUFFER huge. In the 10s of MB, perhaps. Redo log buffer space requests STOP your DB from running, even if for only a very short time (milliseconds to seconds). And with the long write times you can expect, you'll probably need a large LOG_BUFFER to help prevent this. 2) Consider not duplexing your redo logs and control files. KNOW THAT THIS CAN ADD CONSIDERABLE RISK OF LOSS OF DATA! Basically, by not duplexing redo logs and control files you are relying solely on the RAID5 array and it's controllers to not trash your DB. But without duplicating the I/O, you will be saving a bunch on performance. You will have to weigh the risks and benefits. Personally, I'd wait for more drives before doing this, because it's my butt on the line if the data's gone, but that's just me. 3) I'm guessing that there's the potential that a really huge DB_BLOCK_BUFFERS and a BUFFER_POOL_KEEP with table/index caching could help. You know the data and your users better than I. 4) Once you have the SGA sized properly, make sure you make use of VMS's Resident Memory Registry for the DB's SGA. See the Oracle Install Guide for more info. Be prepared to reboot if you need to make changes to the RMR. 5) Don't use Oracle7. Get at least Oracle 8.0, preferrably 8i -- 8.1.6.2.0 with patches. 6) Get Spotlight on Oracle from http://www.quest.com to help you monitor your DB performance, so you can show your bosses/co-workers why the hell you can't run an entire DB on a single RAID5. Ideally, if you could get them to just keep your datafiles on the RAID5, but have at least 5 more JBOD (non-RAIDed) drives to put the other Oracle files on -- 1 for each of 2 control files, 1 for each of 2 sets of redo logs, and at least 1 more for the archives, preferrably 2 if you can, for duplexing them as well. Yes, that's one 9GB or 18GB drive to store a single 10-100MB control file. Drives SAs nuts, unless you happen to be an SA/DBA. :) If you can, have them get you the 15K-spin Seagates. Those puppies fly! Ahh, if only in a perfect world... HTH! Good luck! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA Disclaimer: This is just my opinion. Do what you want with it, but don't hold me, Quad/Graphics, it's subsidiaries or employees accountable for your (in)actions based on what's in this email! > -Original Message- > From: dana mn [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, February 27, 2001 11:51 > To: Multiple recipients of list ORACLE-L > Subject: Re: Tuning, RAID5, and fragmentation and DBWR > > > > Thanks Dave, Don, and Patrice. > > It's hardware RAID, a Compaq GS140 machine, and Oracle on VMS [not my > choice of OS/hardware]. Limited to one large RAID5 volume. > > I'd like to make the most of what's there, because for political > reasons nothing else will change. > > Does it make any sense to increase the number of database writer > processes on a system with nothing but RAID5 space? Looks like there's > a bit of slowdown on checkpoints. --- This message has been scanned for viruses with Trend Micro's Interscan VirusWall. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tuning, RAID5, and fragmentation and DBWR
Thanks Dave, Don, and Patrice. It's hardware RAID, a Compaq GS140 machine, and Oracle on VMS [not my choice of OS/hardware]. Limited to one large RAID5 volume. I'd like to make the most of what's there, because for political reasons nothing else will change. Does it make any sense to increase the number of database writer processes on a system with nothing but RAID5 space? Looks like there's a bit of slowdown on checkpoints. Thanks. - Dana __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dana mn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tuning, RAID5, and fragmentation and DBWR
dana mn wrote: > Thanks Dave, Don, and Patrice. > > It's hardware RAID, a Compaq GS140 machine, and Oracle on VMS [not my > choice of OS/hardware]. Limited to one large RAID5 volume. > > I'd like to make the most of what's there, because for political > reasons nothing else will change. > > Does it make any sense to increase the number of database writer > processes on a system with nothing but RAID5 space? Looks like there's > a bit of slowdown on checkpoints. > > Thanks. > > - Dana > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: dana mn > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). Good question - I have not worked with a single volume, however, I try to help myself a tad by making fewer redo members than I have disks. Of course I generally use 3 to 5 members per volume and always less than the number of drives per volume. I also generally allow the checkpoints to execute based on redo activity rather timed. I don't know if this will help your circumstance or not - hopefully. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Weber INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tuning, RAID5, and fragmentation
We get that here -- because the RAID5 volumes are large compared to our data the database winds up on one or two volumes. The hardware folks are fixated on throughput rather than multiplexing so I get mostly RAID5 devices. I have to prioritize my file load balancing -- I try to get TEMP and logfiles away from the rest of the data, at least, if I can get a different physical volume. They tried to tell me they could partition the volume, but if you think about it carefully, that's worse than lumping it all together (it adds blank spaces for the heads to fly over). For goodness' sake don't partition your RAID. Since everything is on one disk, number and location of data files is less important, and file i/o loading is largely irrelevant. Pretty much all other tuning principles still count, though. Fragmentation/coalescing is probably more important (to permit read-ahead) -- as well as optimizing muti-block io. You'll find that the touted efficiency of RAID for read is mostly bunk for this Oracle, as Oracle tends to do scattered reads that would go faster if the blocks were on separate drives. I've yet to meet the RAID controller that didn't need all the read heads on the same stripe to read a block. However, the fact that you can't distribute head latency among drives move the focus to multiblock i/o (for scanning) and caching (more RAM is always better). Consult with the hardware person to determine the stripe size for your volume and calculate the correct number (and size) of blocks to make db_file_multiblock_read_count and db_file_direct_io_count an integral multiple of the stripe, so that you cache all the data from the stripe you're reading, rather than just the first one or two layers (if you're moving the heads there, get all the data). That, and making sure the hardware guys are using enough I/O channels to saturate the controllers, is about all you can do for I/O under these conditions. Otherwise, I just try to make sure that there's enough cache for the working set and wonder aloud why these high-speed RAIDs always seem to be the bottleneck :-). dana mn wrote: > Presuming a DBA is forced to use RAID5, what elements of tuning become > irrelevant? (in the sense that if you're stuck with RAID5, warts and > all, then trying to tune X, Y, and Z would be a waste of time / > ineffective). > > Load balancing files would be one thing.. no way to put indexes and > tables on different disks (ditto redo log file members, etc) with one > massive RAID5 volume. > > What about fragmentation and coalescing? Are these still a concern for > tablespaces located on RAID5 volumes? > > Has anyone written an article about Oracle and "living with RAID5"? I'm > finding that a customer has several Oracle databases on systems with > nothing else but RAID5 storage for everything. > > Thanks very much. > > - Dana > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: dana mn > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). begin:vcard n:Jerman;Don tel;work:919.508.1886 x-mozilla-html:TRUE org:Information Technology version:2.1 email;internet:[EMAIL PROTECTED] title:Database Administrator adr;quoted-printable:;;Information Technology=0D=0A104 Fayetteville Street Mall;Raleigh;NC;27699-1521;USA x-mozilla-cpt:;-9536 fn:Don Jerman end:vcard
RE: Tuning, RAID5, and fragmentation
I would try to benchmark the system to show where the bottleneck(s) is(are). Probably I/O, possibly the CPU if you are using NT RAID5 instead of a hardware solution. If your machines aren't real servers, then the disk controller will slow things down as well, in many PCs there is one controller for everything, even if you have two slots for plugging devices like hard disks. It just flip flops between the two constantly. At least benchmarking will help get rid of the perception that "Oracle is slow". Is this on NT? If it is RAID5 implemented at the NT level, the MS SQL Server 7 Administration Training Kit manual, p. 128 says that its disadvantage is that is "uses system processing resources". So you may be overutilizing your CPU as well. RAID5 is good for reading data, but not for writing because the parity info has to be updated. To avoid having to defrag or coalesce your extents, just make sure all your extents are exactly the same size within the tablespace, and that the extents are a multiple of your db_block_size. There is an Oracle paper on this concept, if you want it send me an e-mail. I guess this isn't Oracle Enterprise Edition... Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> -Original Message- From: dana mn [SMTP:[EMAIL PROTECTED]] Sent: Sunday, February 25, 2001 6:20 PM To: Multiple recipients of list ORACLE-L Subject:Tuning, RAID5, and fragmentation Presuming a DBA is forced to use RAID5, what elements of tuning become irrelevant? (in the sense that if you're stuck with RAID5, warts and all, then trying to tune X, Y, and Z would be a waste of time / ineffective). Load balancing files would be one thing.. no way to put indexes and tables on different disks (ditto redo log file members, etc) with one massive RAID5 volume. What about fragmentation and coalescing? Are these still a concern for tablespaces located on RAID5 volumes? Has anyone written an article about Oracle and "living with RAID5"? I'm finding that a customer has several Oracle databases on systems with nothing else but RAID5 storage for everything. Thanks very much. - Dana __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dana mn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Tuning, RAID5, and fragmentation
dana mn wrote: > Presuming a DBA is forced to use RAID5, what elements of tuning become > irrelevant? (in the sense that if you're stuck with RAID5, warts and > all, then trying to tune X, Y, and Z would be a waste of time / > ineffective). > > Load balancing files would be one thing.. no way to put indexes and > tables on different disks (ditto redo log file members, etc) with one > massive RAID5 volume. > > What about fragmentation and coalescing? Are these still a concern for > tablespaces located on RAID5 volumes? > > Has anyone written an article about Oracle and "living with RAID5"? I'm > finding that a customer has several Oracle databases on systems with > nothing else but RAID5 storage for everything. > > Thanks very much. > > - Dana > > __ > Do You Yahoo!? > Get email at your own domain with Yahoo! Mail. > http://personal.mail.yahoo.com/ > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: dana mn > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). Personally, I like the fail safe that RAID5 provides, however, it is nice if asked, to have a mixed RAID, i.e., 5 and 10. 10 for indexs. Fragmentation is generally more a function of segment extensions, that is, the more you have to extend the more likely you will fragment. If you can, plan your tablespaces so table with similar growth expectations are placed together. Ideally, the initial and next extensions should be identical. Avoid setting pctincrease to anything but zero to keep extents the same within a table space. Non contiguous extents will occur, however the hope is the extent space will be contiguous. Set the tablespace default settings appropriate for the tables they contain. Coalesce tablespaces manually rather than configuring SMON to do it, as that process requires pctincrease to be greater than zero. Tablespace fragmentation may not be perceptable, however, segment fragmentation can be depending on the activity of the table. I personally like to set minextents equal to the number of datafiles I have, and I have note less scattering of free space blocks. I can only assume then that contiguous space is being allocated. 8x offering partioned indexes and tables helps as well, and depending on the availability requirements of the instance partitioned tables may be exported and imported to really clean up an area if there was a miss on the settings for the inital sizing. I am assuming you have a number of RAID volumes and can apply archive and redo with tables that are not as active as others, which will help further distribute I/0 over the volumes. I like RAID5 since it has saved my neck on more than on occasion, however, RAID 10 (1 & 0) is very nice if you need the speed for indexes and can afford to loose and reconstruct an index if necessary. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dave Weber INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Tuning, RAID5, and fragmentation
Presuming a DBA is forced to use RAID5, what elements of tuning become irrelevant? (in the sense that if you're stuck with RAID5, warts and all, then trying to tune X, Y, and Z would be a waste of time / ineffective). Load balancing files would be one thing.. no way to put indexes and tables on different disks (ditto redo log file members, etc) with one massive RAID5 volume. What about fragmentation and coalescing? Are these still a concern for tablespaces located on RAID5 volumes? Has anyone written an article about Oracle and "living with RAID5"? I'm finding that a customer has several Oracle databases on systems with nothing else but RAID5 storage for everything. Thanks very much. - Dana __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: dana mn INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
