DBMS_STATS and CBO
We have a table (monthly fact table) which has 24 partitions and partitioned by month. There is data in only 3 partitions. All the indexes are locally partitioned. In dev db, I analyzed the table and indexes with analyze table..compute statistics. When I query the no. of rows group by month, it returns the query in couple of seconds and does the index scan(bitmap) on month column. I created this table in production db and this time I used the dbms_stats to create the statistics (compute both on table and indexes). When I run this query, it does full table scan. The only way I could make it to use index scan by specifying hints. All the parameters(init.ora) are exactly the same on both databases and it is 8.1.7.4. In prod db, I tried various combinations of optimizer_index_caching, optimizer_index_cost_adj to favor the index scan. There was no use. Then, I did analyze table .. compute statistics on prod table but it was still doing the FTS. I did not delete the stats created by dbms_stats before using analyze table stmnt. Finally, I deleted the stats generated by dbms_stats/analyze table before generating stats again with Analyze table compute statistics. It is doing the index scan now. I have no clue 1. why it does not use index when I generated the stats w/dbms_stats. 2. why I had to explicitly delete the stats before generating the stats again to make use of the index. Thanks for your help in advance. Thanks. Best Regards, Prasad 860 843 8377 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: DBMS_STATS and CBO
(Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- 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: DBMS_STATS and CBO
Sorry about the last post. Forgot to change the subject. Duh! -- 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: DBMS_STATS and CBO
Stephen, I had something similar happen to me. In 8i, Temp files are not fully allocated when they get created. So if you create a temp file of 600M, only a small portion gets immediately allocated. The Temp file grows into the full 600M as needed. They did this to speed up the creation of the Temp files. The problem is that if you fill the disk up with other stuff, then the Temp file cannot grow when it wants to, and you get the error you got. Kinda a subtle little gotcha here. I personally don't like it - just because you can get stung with this much later in the life of the database - like you and I did. So, yeah, I think you understand what's happening. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 18, 2003 11:50 AM To: Multiple recipients of list ORACLE-L (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- 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: Mercadante, Thomas F 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: DBMS_STATS and CBO
Btw, how much free space do you have in OS where your tempfiles are? Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 6:49 PM > (Resending) > > Any comments on the following?? > > When creating index, got > ORA-00603: ORACLE server session terminated by fatal error > > apparently caused by > > ksedmp: internal or fatal error > ORA-01114: IO error writing block to file 121 (block # 149) > ORA-27063: skgfospo: number of bytes read/written is incorrect > Additional information: 16384 > Additional information: 49152 > > which I determined was caused by attempted write to temp tablespace using a > tempfile. The tablespace was dropped and recreated, and all was well again. > > What I think MIGHT have happened is the tablespace created weeks ago, but > not used. So it didn't grab any actual storage. In the mean time, some of > the storage might have been used by something else, but storage was > released. Now tempfile goes to grab some space, but filesystem is all > screwed up about what storage the tempfile should be grabbing. > > Does this sound plausible? > Is there something else going on here? > Is this another one of those spiffy cool things in Oracle that are just > something else to go wrong? There seems to be no way of creating a LMT > tempfile so that it pre-grabs the disk space. > > Note that the index create blew up immediately, so the original tempfile > never grabbed any space. So, I think I can say that is definitely didn't > run out of space; but maybe somebody walked across the space the tempfile > thought it was going to get in the future. > -- > 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: 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: DBMS_STATS and CBO
At this moment it's 13 Gig. The tempfile was created as 5 Gig, but since it remained unused, it didn't grab any significant disk space. It's possible, that at some time, the free space in the file system fell below 5 Gig. When I tried to create the index, the error occurred immediately. So, the file never tried to grow at all ... ever. The big question is why not? The OS recorded no I/O errors at the time, and we have had no I/O errors on any of the other stuff using the file system. What I am wondering is if there is some IMPLIED disk address assignment that occurs when the tempfile (a "sparse" file, I assume) is created, and can this assignment be overwritten or screwed up in some way if the tempfile remains unused so that when the tempfile finally wants its space, the filesystem has changed from the time the file was created, and now tempfile can't grab anything. Or do we have some other kind of weirdness going on here? Is this making sense? > -Original Message- > From: Tanel Poder [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 18, 2003 11:35 AM > To: Multiple recipients of list ORACLE-L > Subject: Re: DBMS_STATS and CBO > > > Btw, how much free space do you have in OS where your tempfiles are? > > Tanel. > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, September 18, 2003 6:49 PM > > > > (Resending) > > > > Any comments on the following?? > > > > When creating index, got > > ORA-00603: ORACLE server session terminated by fatal error > > > > apparently caused by > > > > ksedmp: internal or fatal error > > ORA-01114: IO error writing block to file 121 (block # 149) > > ORA-27063: skgfospo: number of bytes read/written is incorrect > > Additional information: 16384 > > Additional information: 49152 > > > > which I determined was caused by attempted write to temp > tablespace using > a > > tempfile. The tablespace was dropped and recreated, and > all was well > again. > > > > What I think MIGHT have happened is the tablespace created > weeks ago, but > > not used. So it didn't grab any actual storage. In the > mean time, some > of > > the storage might have been used by something else, but storage was > > released. Now tempfile goes to grab some space, but > filesystem is all > > screwed up about what storage the tempfile should be grabbing. > > > > Does this sound plausible? > > Is there something else going on here? > > Is this another one of those spiffy cool things in Oracle > that are just > > something else to go wrong? There seems to be no way of > creating a LMT > > tempfile so that it pre-grabs the disk space. > > > > Note that the index create blew up immediately, so the > original tempfile > > never grabbed any space. So, I think I can say that is > definitely didn't > > run out of space; but maybe somebody walked across the > space the tempfile > > thought it was going to get in the future. > > -- > > 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: Tanel Poder > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee INET: [EMAIL P
Re: DBMS_STATS and CBO
Hi, I have hit similiar situation in my datawarehouse server. As temp file are sparse, it did not allocate the actual space when it was created. But as you really begin to sort and the filesystem is full , you can get this error. I offlined that tempfile and add another tempfile in another filesystem and everything is ok. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 11:49 PM > (Resending) > > Any comments on the following?? > > When creating index, got > ORA-00603: ORACLE server session terminated by fatal error > > apparently caused by > > ksedmp: internal or fatal error > ORA-01114: IO error writing block to file 121 (block # 149) > ORA-27063: skgfospo: number of bytes read/written is incorrect > Additional information: 16384 > Additional information: 49152 > > which I determined was caused by attempted write to temp tablespace using a > tempfile. The tablespace was dropped and recreated, and all was well again. > > What I think MIGHT have happened is the tablespace created weeks ago, but > not used. So it didn't grab any actual storage. In the mean time, some of > the storage might have been used by something else, but storage was > released. Now tempfile goes to grab some space, but filesystem is all > screwed up about what storage the tempfile should be grabbing. > > Does this sound plausible? > Is there something else going on here? > Is this another one of those spiffy cool things in Oracle that are just > something else to go wrong? There seems to be no way of creating a LMT > tempfile so that it pre-grabs the disk space. > > Note that the index create blew up immediately, so the original tempfile > never grabbed any space. So, I think I can say that is definitely didn't > run out of space; but maybe somebody walked across the space the tempfile > thought it was going to get in the future. > -- > 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: zhu chao 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: DBMS_STATS and CBO
Tom, Your observation on which platform? On HPUX 11.0 I think it allocates full given physical size of tempfile at the time of creation and it was 8.1.6.2 when I created it 2 years back. I remember that I created 6 files of 501 MB each and it occupied disk space of 3GB+. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 18 Sep 2003 08:19:40 -0800 Stephen, I had something similar happen to me. In 8i, Temp files are not fully allocated when they get created. So if you create a temp file of 600M, only a small portion gets immediately allocated. The Temp file grows into the full 600M as needed. They did this to speed up the creation of the Temp files. The problem is that if you fill the disk up with other stuff, then the Temp file cannot grow when it wants to, and you get the error you got. Kinda a subtle little gotcha here. I personally don't like it - just because you can get stung with this much later in the life of the database - like you and I did. So, yeah, I think you understand what's happening. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 18, 2003 11:50 AM To: Multiple recipients of list ORACLE-L (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- 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: Mercadante, Thomas F 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). _ Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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: DBMS_STATS and CBO
Rafiq, SunOS 5.8 Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 18, 2003 1:40 PM To: Multiple recipients of list ORACLE-L Tom, Your observation on which platform? On HPUX 11.0 I think it allocates full given physical size of tempfile at the time of creation and it was 8.1.6.2 when I created it 2 years back. I remember that I created 6 files of 501 MB each and it occupied disk space of 3GB+. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 18 Sep 2003 08:19:40 -0800 Stephen, I had something similar happen to me. In 8i, Temp files are not fully allocated when they get created. So if you create a temp file of 600M, only a small portion gets immediately allocated. The Temp file grows into the full 600M as needed. They did this to speed up the creation of the Temp files. The problem is that if you fill the disk up with other stuff, then the Temp file cannot grow when it wants to, and you get the error you got. Kinda a subtle little gotcha here. I personally don't like it - just because you can get stung with this much later in the life of the database - like you and I did. So, yeah, I think you understand what's happening. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 18, 2003 11:50 AM To: Multiple recipients of list ORACLE-L (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- 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: Mercadante, Thomas F 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). _ Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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
Re: DBMS_STATS and CBO
Hm, it's strange. When you create a temp datafile there is some data written to it despite you actually store anything there or not (headers or whatever control structs). So, the tempfile is being used from beginning. The mechanism of creating sparse files is quite simple: just forward seek command is issued on the file, over the current end of file and then one byte (maybe block in Oracle) is written at the end position. That way, if OS & filesystem support sparse files, they don't actually allocate space for the empty part. Space is allocated only when something is written to these parts (when reading empty space in sparse file, nulls are retrieved). To avoid any sparse file issues in future, I recommend you to copy the tempfiles to another name using cp, then rename back, that way the file isn't sparse anymore (well, until the tempfile autoextends, then file gets sparse again). And compare file sizes periodically with ls -l and ls -ls. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, September 18, 2003 8:04 PM > > At this moment it's 13 Gig. The tempfile was created as 5 Gig, but since it > remained unused, it didn't grab any significant disk space. It's possible, > that at some time, the free space in the file system fell below 5 Gig. When > I tried to create the index, the error occurred immediately. So, the file > never tried to grow at all ... ever. The big question is why not? The OS > recorded no I/O errors at the time, and we have had no I/O errors on any of > the other stuff using the file system. > > What I am wondering is if there is some IMPLIED disk address assignment that > occurs when the tempfile (a "sparse" file, I assume) is created, and can > this assignment be overwritten or screwed up in some way if the tempfile > remains unused so that when the tempfile finally wants its space, the > filesystem has changed from the time the file was created, and now tempfile > can't grab anything. Or do we have some other kind of weirdness going on > here? > > Is this making sense? > > > -Original Message- > > From: Tanel Poder [mailto:[EMAIL PROTECTED] > > Sent: Thursday, September 18, 2003 11:35 AM > > To: Multiple recipients of list ORACLE-L > > Subject: Re: DBMS_STATS and CBO > > > > > > Btw, how much free space do you have in OS where your tempfiles are? > > > > Tanel. > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Thursday, September 18, 2003 6:49 PM > > > > > > > (Resending) > > > > > > Any comments on the following?? > > > > > > When creating index, got > > > ORA-00603: ORACLE server session terminated by fatal error > > > > > > apparently caused by > > > > > > ksedmp: internal or fatal error > > > ORA-01114: IO error writing block to file 121 (block # 149) > > > ORA-27063: skgfospo: number of bytes read/written is incorrect > > > Additional information: 16384 > > > Additional information: 49152 > > > > > > which I determined was caused by attempted write to temp > > tablespace using > > a > > > tempfile. The tablespace was dropped and recreated, and > > all was well > > again. > > > > > > What I think MIGHT have happened is the tablespace created > > weeks ago, but > > > not used. So it didn't grab any actual storage. In the > > mean time, some > > of > > > the storage might have been used by something else, but storage was > > > released. Now tempfile goes to grab some space, but > > filesystem is all > > > screwed up about what storage the tempfile should be grabbing. > > > > > > Does this sound plausible? > > > Is there something else going on here? > > > Is this another one of those spiffy cool things in Oracle > > that are just > > > something else to go wrong? There seems to be no way of > > creating a LMT > > > tempfile so that it pre-grabs the disk space. > > > > > > Note that the index create blew up immediately, so the > > original tempfile > > > never grabbed any space. So, I think I can say that is > > definitely didn't > > > run out of space; but maybe somebody walked across the > > space the tempfile > > > thought it was going to get in the future. > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Stephen Lee > &
RE: DBMS_STATS and CBO
Now, THERE is an idea! Thanky. By the way, there is now a Tanel folder in my mailbox. > -Original Message- > From: Tanel Poder [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 18, 2003 1:35 PM > To: Multiple recipients of list ORACLE-L > Subject: Re: DBMS_STATS and CBO > > > > To avoid any sparse file issues in future, I recommend you to copy the > tempfiles to another name using cp, then rename back, -- 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: DBMS_STATS and CBO
Tom Thanks. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 18 Sep 2003 09:54:47 -0800 Rafiq, SunOS 5.8 Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 18, 2003 1:40 PM To: Multiple recipients of list ORACLE-L Tom, Your observation on which platform? On HPUX 11.0 I think it allocates full given physical size of tempfile at the time of creation and it was 8.1.6.2 when I created it 2 years back. I remember that I created 6 files of 501 MB each and it occupied disk space of 3GB+. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 18 Sep 2003 08:19:40 -0800 Stephen, I had something similar happen to me. In 8i, Temp files are not fully allocated when they get created. So if you create a temp file of 600M, only a small portion gets immediately allocated. The Temp file grows into the full 600M as needed. They did this to speed up the creation of the Temp files. The problem is that if you fill the disk up with other stuff, then the Temp file cannot grow when it wants to, and you get the error you got. Kinda a subtle little gotcha here. I personally don't like it - just because you can get stung with this much later in the life of the database - like you and I did. So, yeah, I think you understand what's happening. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, September 18, 2003 11:50 AM To: Multiple recipients of list ORACLE-L (Resending) Any comments on the following?? When creating index, got ORA-00603: ORACLE server session terminated by fatal error apparently caused by ksedmp: internal or fatal error ORA-01114: IO error writing block to file 121 (block # 149) ORA-27063: skgfospo: number of bytes read/written is incorrect Additional information: 16384 Additional information: 49152 which I determined was caused by attempted write to temp tablespace using a tempfile. The tablespace was dropped and recreated, and all was well again. What I think MIGHT have happened is the tablespace created weeks ago, but not used. So it didn't grab any actual storage. In the mean time, some of the storage might have been used by something else, but storage was released. Now tempfile goes to grab some space, but filesystem is all screwed up about what storage the tempfile should be grabbing. Does this sound plausible? Is there something else going on here? Is this another one of those spiffy cool things in Oracle that are just something else to go wrong? There seems to be no way of creating a LMT tempfile so that it pre-grabs the disk space. Note that the index create blew up immediately, so the original tempfile never grabbed any space. So, I think I can say that is definitely didn't run out of space; but maybe somebody walked across the space the tempfile thought it was going to get in the future. -- 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: Mercadante, Thomas F 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). _ Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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 t
Re: DBMS_STATS and CBO
If you want to get the same effect entirely from the "SQL>" prompt (i.e. without having to issue operating-system copy commands), you can first create a tablespace (named DUMMY_TEMP?) with datafiles of the name and size you want for your TEMP tablespace. Then drop the DUMMY_TEMP tablespace and create the TEMP tablespace over the same files, now as tempfiles. Don¹t forget the REUSE clause... on 9/18/03 11:34 AM, Tanel Poder at [EMAIL PROTECTED] wrote: > Hm, it's strange. > When you create a temp datafile there is some data written to it despite you > actually store anything there or not (headers or whatever control structs). > So, the tempfile is being used from beginning. The mechanism of creating > sparse files is quite simple: just forward seek command is issued on the > file, over the current end of file and then one byte (maybe block in Oracle) > is written at the end position. That way, if OS & filesystem support sparse > files, they don't actually allocate space for the empty part. Space is > allocated only when something is written to these parts (when reading empty > space in sparse file, nulls are retrieved). > > To avoid any sparse file issues in future, I recommend you to copy the > tempfiles to another name using cp, then rename back, that way the file > isn't sparse anymore (well, until the tempfile autoextends, then file gets > sparse again). And compare file sizes periodically with ls -l and ls -ls. > > Tanel. > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, September 18, 2003 8:04 PM > > >> >> At this moment it's 13 Gig. The tempfile was created as 5 Gig, but since > it >> remained unused, it didn't grab any significant disk space. It's > possible, >> that at some time, the free space in the file system fell below 5 Gig. > When >> I tried to create the index, the error occurred immediately. So, the file >> never tried to grow at all ... ever. The big question is why not? The OS >> recorded no I/O errors at the time, and we have had no I/O errors on any > of >> the other stuff using the file system. >> >> What I am wondering is if there is some IMPLIED disk address assignment > that >> occurs when the tempfile (a "sparse" file, I assume) is created, and can >> this assignment be overwritten or screwed up in some way if the tempfile >> remains unused so that when the tempfile finally wants its space, the >> filesystem has changed from the time the file was created, and now > tempfile >> can't grab anything. Or do we have some other kind of weirdness going on >> here? >> >> Is this making sense? >> >>> -Original Message- >>> From: Tanel Poder [mailto:[EMAIL PROTECTED] >>> Sent: Thursday, September 18, 2003 11:35 AM >>> To: Multiple recipients of list ORACLE-L >>> Subject: Re: DBMS_STATS and CBO >>> >>> >>> Btw, how much free space do you have in OS where your tempfiles are? >>> >>> Tanel. >>> >>> - Original Message - >>> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> >>> Sent: Thursday, September 18, 2003 6:49 PM >>> >>> >>>> (Resending) >>>> >>>> Any comments on the following?? >>>> >>>> When creating index, got >>>> ORA-00603: ORACLE server session terminated by fatal error >>>> >>>> apparently caused by >>>> >>>> ksedmp: internal or fatal error >>>> ORA-01114: IO error writing block to file 121 (block # 149) >>>> ORA-27063: skgfospo: number of bytes read/written is incorrect >>>> Additional information: 16384 >>>> Additional information: 49152 >>>> >>>> which I determined was caused by attempted write to temp >>> tablespace using >>> a >>>> tempfile. The tablespace was dropped and recreated, and >>> all was well >>> again. >>>> >>>> What I think MIGHT have happened is the tablespace created >>> weeks ago, but >>>> not used. So it didn't grab any actual storage. In the >>> mean time, some >>> of >>>> the storage might have been used by something else, but storage was >>>> released. Now tempfile goes to grab some space, but >>> filesystem is all >>>> screwed up about what storage the tempfile should be grabbing. >>>> >>>> Does this sound plausible? &g
RE: DBMS_STATS and CBO
Thanks Govind for your reply and suggestion.
Actually, I did 'FOR ALL COLUMNS SIZE 1' only.
Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS
('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE);
Best Regards,
Prasad
860 843 8377
To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED]Subject: RE: DBMS_STATS and CBO
.com
08/09/2003 12:14
AM
Please respond to
ORACLE-L
Prasad,
We ran into the same problem when we did FOR ALL INDEXED COLUMNS using
DBMS_STATS. Then we changed it to run against FOR ALL COLUMNS SIZE 1. Then
CBO started to use the indexes.
execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE,
'FOR ALL COLUMNS SIZE 1',20,'DEFAULT',TRUE);
Here 30 is the sample percent (ie 30%) and 20 is the the parallel degree.
Hope this helps.
Govind
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Friday, August 08, 2003 9:24 PM
To: Multiple recipients of list ORACLE-L
We have a table (monthly fact table) which has 24 partitions and
partitioned by month. There is data in only 3 partitions. All the indexes
are locally partitioned.
In dev db, I analyzed the table and indexes with analyze table..compute
statistics. When I query the no. of rows group by month, it returns the
query in couple of seconds and does the index scan(bitmap) on month column.
I created this table in production db and this time I used the dbms_stats
to create the statistics (compute both on table and indexes). When I run
this query, it does full table scan. The only way I could make it to use
index scan by specifying hints.
All the parameters(init.ora) are exactly the same on both databases and it
is 8.1.7.4. In prod db, I tried various combinations of
optimizer_index_caching, optimizer_index_cost_adj to favor the index scan.
There was no use.
Then, I did analyze table .. compute statistics on prod table but it was
still doing the FTS. I did not delete the stats created by dbms_stats
before using analyze table stmnt.
Finally, I deleted the stats generated by dbms_stats/analyze table before
generating stats again with Analyze table compute statistics. It is
doing the index scan now.
I have no clue 1. why it does not use index when I generated the stats
w/dbms_stats. 2. why I had to explicitly delete the stats before generating
the stats again to make use of the index.
Thanks for your help in advance.
Thanks.
Best Regards,
Prasad
860 843 8377
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-53
RE: DBMS_STATS and CBO
Prasad,
We ran into the same problem when we did FOR ALL INDEXED COLUMNS using DBMS_STATS.
Then we changed it to run against FOR ALL COLUMNS SIZE 1. Then CBO started to use the
indexes.
execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE,
'FOR ALL COLUMNS SIZE 1',20,'DEFAULT',TRUE);
Here 30 is the sample percent (ie 30%) and 20 is the the parallel degree.
Hope this helps.
Govind
-Original Message-
[mailto:[EMAIL PROTECTED]
Sent: Friday, August 08, 2003 9:24 PM
To: Multiple recipients of list ORACLE-L
We have a table (monthly fact table) which has 24 partitions and
partitioned by month. There is data in only 3 partitions. All the indexes
are locally partitioned.
In dev db, I analyzed the table and indexes with analyze table..compute
statistics. When I query the no. of rows group by month, it returns the
query in couple of seconds and does the index scan(bitmap) on month column.
I created this table in production db and this time I used the dbms_stats
to create the statistics (compute both on table and indexes). When I run
this query, it does full table scan. The only way I could make it to use
index scan by specifying hints.
All the parameters(init.ora) are exactly the same on both databases and it
is 8.1.7.4. In prod db, I tried various combinations of
optimizer_index_caching, optimizer_index_cost_adj to favor the index scan.
There was no use.
Then, I did analyze table .. compute statistics on prod table but it was
still doing the FTS. I did not delete the stats created by dbms_stats
before using analyze table stmnt.
Finally, I deleted the stats generated by dbms_stats/analyze table before
generating stats again with Analyze table compute statistics. It is
doing the index scan now.
I have no clue 1. why it does not use index when I generated the stats
w/dbms_stats. 2. why I had to explicitly delete the stats before generating
the stats again to make use of the index.
Thanks for your help in advance.
Thanks.
Best Regards,
Prasad
860 843 8377
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: DBMS_STATS and CBO
Prasad,
Make sure that low_value and high_value columns do not have null values ie. generate
statistics on all columns. Analyze table generates the correct values for these
columns whereas FOR ALL INDEXED columns in DBMS_STATS do not. Once these values are
available through FOR ALL COLUMNS SIZE 1 ( ie do not generate histograms ), we seen
the queries to be using the indexes without any hints. We ran into this problem when
we went to 9i for the first time.
select
column_name,
NUM_DISTINCT ,
decode(LOW_VALUE ,null,null,'Full') Low ,
decode(HIGH_VALUE ,null,null,'Full') High ,
DENSITY ,
NUM_NULLS ,
NUM_BUCKETS ,
LAST_ANALYZED ,
SAMPLE_SIZE
from dba_tab_columns
where owner = 'your_owner'
and table_name = 'your_table';
Govind
-Original Message-
Sent: Saturday, August 09, 2003 10:49 AM
To: Multiple recipients of list ORACLE-L
Hi!
Maybe you used analyze command without column analyzing clause, but used
dbms_stats package with column analyze clause (for all columns parameter).
Or it just could be because dbms_stats calculates some stats somewhat
differently (supposedly better), than old analyze command (average column
length and some spare columns of hist_head$ have varied in my tests).
If you set SIZE to 1, then only column low and high value are stored in
histogram. (In hist_head$ instead of histgrm$).
As an alternative to Govind's suggestion, you could increase SIZE parameter
(maximum is 254) to give CBO better understanding of data distribution.
Note that you should be careful with the METHOD_OPT parameter in
gather_schema_statistics procedure in version 9i, because if you supply
invalid parameter there, then the procedure just does nothing and returns
without error! You should verify from last_analyzed column to see whether a
segment was actually analyzed or not. (This was tested on 9.2.0.1 on W2k, it
might be fixed on newer patch levels).
Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, August 09, 2003 7:29 AM
>
> Thanks Govind for your reply and suggestion.
>
> Actually, I did 'FOR ALL COLUMNS SIZE 1' only.
>
> Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS
> ('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE);
>
> Best Regards,
> Prasad
> 860 843 8377
>
>
>
> alltel.com> To: Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Sent by: cc:
> [EMAIL PROTECTED]Subject: RE: DBMS_STATS
and CBO
> .com
>
>
> 08/09/2003 12:14
> AM
> Please respond to
> ORACLE-L
>
>
>
>
>
>
> Prasad,
>
> We ran into the same problem when we did FOR ALL INDEXED COLUMNS using
> DBMS_STATS. Then we changed it to run against FOR ALL COLUMNS SIZE 1.
Then
> CBO started to use the indexes.
>
> execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE,
> 'FOR ALL COLUMNS SIZE 1',20,'DEFAULT',TRUE);
>
> Here 30 is the sample percent (ie 30%) and 20 is the the parallel degree.
>
> Hope this helps.
>
> Govind
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]
> Sent: Friday, August 08, 2003 9:24 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> We have a table (monthly fact table) which has 24 partitions and
> partitioned by month. There is data in only 3 partitions. All the indexes
> are locally partitioned.
>
> In dev db, I analyzed the table and indexes with analyze table..compute
> statistics. When I query the no. of rows group by month, it returns the
> query in couple of seconds and does the index scan(bitmap) on month
column.
>
> I created this table in production db and this time I used the dbms_stats
> to create the statistics (compute both on table and indexes). When I run
> this query, it does full table scan. The only way I could make it to use
> index scan by specifying hints.
>
> All the parameters(init.ora) are exactly the same on both databases and it
> is 8.1.7.4. In prod db, I tried various combinations of
> optimizer_index_caching, optimizer_index_cost_adj to favor the index scan.
> There was no use.
>
> Then, I did analyze table .. compute statistics on prod table but it was
> still doing the FTS. I did not delete the stats created by dbms_stats
> before using analyze table stmnt.
>
> Finally, I deleted the stats generated by dbms_stats/analyze table before
> generating stats again with Analyze table compute statistics. It
is
> doi
RE: DBMS_STATS and CBO
Thanks Govind and Tanel for your replies.
Since I was testing both ways (analyze table and dbms_stats) before, I
cleared out the stats using analyze table..delete statistics and
dbms_stats.delete_table_stats.
Then, I generated stats using dbms_stats.gather_table_stats and it is
working fine now.
Thanks for your help.
Best Regards,
Prasad
860 843 8377
To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED]Subject: RE: DBMS_STATS and CBO
.com
08/09/2003 01:19
PM
Please respond to
ORACLE-L
Prasad,
Make sure that low_value and high_value columns do not have null values ie.
generate statistics on all columns. Analyze table generates the correct
values for these columns whereas FOR ALL INDEXED columns in DBMS_STATS do
not. Once these values are available through FOR ALL COLUMNS SIZE 1 ( ie
do not generate histograms ), we seen the queries to be using the indexes
without any hints. We ran into this problem when we went to 9i for the
first time.
select
column_name,
NUM_DISTINCT ,
decode(LOW_VALUE ,null,null,'Full') Low ,
decode(HIGH_VALUE ,null,null,'Full') High ,
DENSITY ,
NUM_NULLS ,
NUM_BUCKETS ,
LAST_ANALYZED ,
SAMPLE_SIZE
from dba_tab_columns
where owner = 'your_owner'
and table_name = 'your_table';
Govind
-Original Message-
Sent: Saturday, August 09, 2003 10:49 AM
To: Multiple recipients of list ORACLE-L
Hi!
Maybe you used analyze command without column analyzing clause, but used
dbms_stats package with column analyze clause (for all columns parameter).
Or it just could be because dbms_stats calculates some stats somewhat
differently (supposedly better), than old analyze command (average column
length and some spare columns of hist_head$ have varied in my tests).
If you set SIZE to 1, then only column low and high value are stored in
histogram. (In hist_head$ instead of histgrm$).
As an alternative to Govind's suggestion, you could increase SIZE parameter
(maximum is 254) to give CBO better understanding of data distribution.
Note that you should be careful with the METHOD_OPT parameter in
gather_schema_statistics procedure in version 9i, because if you supply
invalid parameter there, then the procedure just does nothing and returns
without error! You should verify from last_analyzed column to see whether a
segment was actually analyzed or not. (This was tested on 9.2.0.1 on W2k,
it
might be fixed on newer patch levels).
Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, August 09, 2003 7:29 AM
>
> Thanks Govind for your reply and suggestion.
>
> Actually, I did 'FOR ALL COLUMNS SIZE 1' only.
>
> Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS
> ('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE);
>
> Best Regards,
> Prasad
> 860 843 8377
>
>
>
> alltel.com> To: Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Sent by: cc:
> [EMAIL PROTECTED]Subject: RE: DBMS_STATS
and CBO
> .com
>
>
> 08/09/2003 12:14
>
Re: DBMS_STATS and CBO
Hi!
Maybe you used analyze command without column analyzing clause, but used
dbms_stats package with column analyze clause (for all columns parameter).
Or it just could be because dbms_stats calculates some stats somewhat
differently (supposedly better), than old analyze command (average column
length and some spare columns of hist_head$ have varied in my tests).
If you set SIZE to 1, then only column low and high value are stored in
histogram. (In hist_head$ instead of histgrm$).
As an alternative to Govind's suggestion, you could increase SIZE parameter
(maximum is 254) to give CBO better understanding of data distribution.
Note that you should be careful with the METHOD_OPT parameter in
gather_schema_statistics procedure in version 9i, because if you supply
invalid parameter there, then the procedure just does nothing and returns
without error! You should verify from last_analyzed column to see whether a
segment was actually analyzed or not. (This was tested on 9.2.0.1 on W2k, it
might be fixed on newer patch levels).
Tanel.
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Saturday, August 09, 2003 7:29 AM
>
> Thanks Govind for your reply and suggestion.
>
> Actually, I did 'FOR ALL COLUMNS SIZE 1' only.
>
> Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS
> ('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE);
>
> Best Regards,
> Prasad
> 860 843 8377
>
>
>
> alltel.com> To: Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Sent by: cc:
> [EMAIL PROTECTED]Subject: RE: DBMS_STATS
and CBO
> .com
>
>
> 08/09/2003 12:14
> AM
> Please respond to
> ORACLE-L
>
>
>
>
>
>
> Prasad,
>
> We ran into the same problem when we did FOR ALL INDEXED COLUMNS using
> DBMS_STATS. Then we changed it to run against FOR ALL COLUMNS SIZE 1.
Then
> CBO started to use the indexes.
>
> execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE,
> 'FOR ALL COLUMNS SIZE 1',20,'DEFAULT',TRUE);
>
> Here 30 is the sample percent (ie 30%) and 20 is the the parallel degree.
>
> Hope this helps.
>
> Govind
>
> -Original Message-
> [mailto:[EMAIL PROTECTED]
> Sent: Friday, August 08, 2003 9:24 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> We have a table (monthly fact table) which has 24 partitions and
> partitioned by month. There is data in only 3 partitions. All the indexes
> are locally partitioned.
>
> In dev db, I analyzed the table and indexes with analyze table..compute
> statistics. When I query the no. of rows group by month, it returns the
> query in couple of seconds and does the index scan(bitmap) on month
column.
>
> I created this table in production db and this time I used the dbms_stats
> to create the statistics (compute both on table and indexes). When I run
> this query, it does full table scan. The only way I could make it to use
> index scan by specifying hints.
>
> All the parameters(init.ora) are exactly the same on both databases and it
> is 8.1.7.4. In prod db, I tried various combinations of
> optimizer_index_caching, optimizer_index_cost_adj to favor the index scan.
> There was no use.
>
> Then, I did analyze table .. compute statistics on prod table but it was
> still doing the FTS. I did not delete the stats created by dbms_stats
> before using analyze table stmnt.
>
> Finally, I deleted the stats generated by dbms_stats/analyze table before
> generating stats again with Analyze table compute statistics. It
is
> doing the index scan now.
>
> I have no clue 1. why it does not use index when I generated the stats
> w/dbms_stats. 2. why I had to explicitly delete the stats before
generating
> the stats again to make use of the index.
>
> Thanks for your help in advance.
>
> Thanks.
>
> Best Regards,
> Prasad
> 860 843 8377
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: U
