RE: dbms_stats
Yes. At least I was before reverting back to ANALYZE. For our little 30GB DB, there's little penalty for non-parallel stats gathering on Saturdays. Then again, one could ANALYZE multiple tables at once to regain some parallelism in stats gathering... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Wednesday, December 03, 2003 10:19 PM To: Multiple recipients of list ORACLE-L Rich, I went thru the archives. Are you using the option 'cascade=true' for gather_table_stats? -Original Message- Jesse, Rich Sent: Wednesday, December 03, 2003 4:21 PM To: Multiple recipients of list ORACLE-L Yes -- same platform and version. If you look on the list archives (fatcity.com, for example) for a subject of Burned by DBMS_STATS **AGAIN** back in April 2003, you may get some insight. There are also known bugs in GATHER_SCHEMA_STATS in 8i. The recommendation is to iteratively call GATHER_TABLE_STATS. GL! Rich -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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
Yes -- same platform and version. If you look on the list archives
(fatcity.com, for example) for a subject of Burned by DBMS_STATS **AGAIN**
back in April 2003, you may get some insight.
There are also known bugs in GATHER_SCHEMA_STATS in 8i. The recommendation
is to iteratively call GATHER_TABLE_STATS.
GL!
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
-Original Message-
Sent: Wednesday, December 03, 2003 3:04 PM
To: Multiple recipients of list ORACLE-L
Hello,
Oracle 8.1.7.4 on HP-UX 11i
A week ago, we replaced 'analyze table ... estimate statistics sample 10
percent' with dbms_stats.gather_schema_stats('x', estimate_percent=10,
cascade=true, degree=4, method_opt=''for all indexed columns size 2')
Performace is good against partitioned tables but not for non-partitioned
tables.
Saw a note in Metalink that its better to do the above with 'cascade=false'
and then do a gather_index_stats separately.
In my tests, I see that 'analyze' makes the CBO use an index while
dbms_stats is making the CBO to use a FTS instead.
Have you faced any similar issues?
TIA
Prakash
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
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
I think there are various little (hah!) details that
change with versions, but unless your analyze
command was:
analyze table T estimate statistics sample 10 percent
for table
for indexes
for all indexed columns size 2
;
(I may have the sample clause in the wrong place).
then the two commands will be generating different
things. The default for analyze columns is 'size' 75,
which gives Oracle much better precision on range
scans with literal values than 'size 2' - which may be
a significant part of your problem.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___November
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, December 03, 2003 9:04 PM
Hello,
Oracle 8.1.7.4 on HP-UX 11i
A week ago, we replaced 'analyze table ... estimate statistics sample 10
percent' with dbms_stats.gather_schema_stats('x', estimate_percent=10,
cascade=true, degree=4, method_opt=''for all indexed columns size 2')
Performace is good against partitioned tables but not for non-partitioned
tables.
Saw a note in Metalink that its better to do the above with 'cascade=false'
and then do a gather_index_stats separately.
In my tests, I see that 'analyze' makes the CBO use an index while
dbms_stats is making the CBO to use a FTS instead.
Have you faced any similar issues?
TIA
Prakash
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
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
Rich, I went thru the archives. Are you using the option 'cascade=true' for
gather_table_stats?
-Original Message-
Jesse, Rich
Sent: Wednesday, December 03, 2003 4:21 PM
To: Multiple recipients of list ORACLE-L
Yes -- same platform and version. If you look on the list archives
(fatcity.com, for example) for a subject of Burned by DBMS_STATS **AGAIN**
back in April 2003, you may get some insight.
There are also known bugs in GATHER_SCHEMA_STATS in 8i. The recommendation
is to iteratively call GATHER_TABLE_STATS.
GL!
Rich
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA
-Original Message-
Sent: Wednesday, December 03, 2003 3:04 PM
To: Multiple recipients of list ORACLE-L
Hello,
Oracle 8.1.7.4 on HP-UX 11i
A week ago, we replaced 'analyze table ... estimate statistics sample 10
percent' with dbms_stats.gather_schema_stats('x', estimate_percent=10,
cascade=true, degree=4, method_opt=''for all indexed columns size 2')
Performace is good against partitioned tables but not for non-partitioned
tables.
Saw a note in Metalink that its better to do the above with 'cascade=false'
and then do a gather_index_stats separately.
In my tests, I see that 'analyze' makes the CBO use an index while
dbms_stats is making the CBO to use a FTS instead.
Have you faced any similar issues?
TIA
Prakash
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jesse, Rich
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: Bala, Prakash
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
Maybe I didn't make my point clear enough. If you use dbms_stats.gather_table_stats with method_opt=''for all indexed columns size 2' , i.e. any other than size 1, dbms_stats in Oracle 8i will issue an analyze ... command to gather the statistics. Run a sql_trace if you don't believe me. Therefore the results of gathering statistics with dbms_stats are no different than gathering them with the equivalent analyze command. It is different if you use the default method_opt (for all columns size 1). Then gather_table_stats uses its own sql to collect table and column ( num_distinct, min, max, null, avg_col_length) statistics. For index statistics always resorts to the analyze command. That all changes in Oracle 9i. There the gather procedures do their own work and do not use analyze anymore. How did you determine the bucket size of 2? And no, I have not had any issues with analyze for partitioned tables; however, I do not gather histograms blindly on all indexed columns. Only on a few columns with highly skewed data content and some of them are on partitioned tabled. At 09:29 PM 12/3/2003, you wrote: The reason why we switched from 'analyze table .. 10 percent' is because when we partitioned some of the huge tables, the query performance against these tables was really bad. 'Gather_table_stats' with size 2 on indexed columns did a much better job. Have you had issues with 'analyze' against partitioned tables? -Original Message- Wolfgang Breitling Sent: Wednesday, December 03, 2003 5:29 PM To: Multiple recipients of list ORACLE-L In Oracle 8i you may as well stick with analyze since the dbms_stats call you use translates simply into a analyze table ... ESTIMATE statistics sample 10 percent FOR TABLE FOR ALL INDEXES for all indexed columns size 2 Why did you go from a simple analyze to gathering histograms on all indexed columns? I question the rationale of gathering histograms of size 2. Aside from that, I question the rationale of a blanket histogram gathering (regardless of # of buckets) on all indexed or all columns. Histograms are like medicine. In the right (i.e. sparing dose) they are a therapeutic tool. In the wrong, especially too high dose, they become poison. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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
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? 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
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 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
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 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
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 to
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
Govind.Arumugam@
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 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
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 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
Govind.Arumugam@
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 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
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
Govind.Arumugam@
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 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
Govind.Arumugam@
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
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
Govind.Arumugam@
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 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
RE: DBMS_STATS error
why are you so focused on this view: DBA_TAB_MODIFICATIONS It contains info on tables *modified* : columns INSERTS, UPDATES, DELETES give you the number rows inserted or updated or deleted since the last your tables was analyzed Concerning your error, I ran it as SYSTEM and it worked...8.1.7.0 SQL exec dbms_stats.gather_schema_stats(ownname='REGIS',estimate_percent=20,degree= 16,cascade=true,options='GATHER EMPTY' PL/SQL procedure successfully completed. which version of Oracle you are running ? Can you do a: desc dbms_stats ? Regis -Original Message- Sent: Thursday, June 12, 2003 1:49 PM To: Multiple recipients of list ORACLE-L I ran the following and got an error. Any idea? I noticed after I ran it that my DBA_TAB_MODIFICATION view has no records even though I ran gather_schema_stats yesterdays SQL exec dbms_stats.gather_schema_stats(ownname='MASTER_TEST2',estimate_percent=20, degree=16,cascade=true,options='GATH ER EMPTY'); BEGIN dbms_stats.gather_schema_stats(ownname='MASTER_TEST2',estimate_percent=20, degree=16,cascade=true,options='GATHER E MPTY'); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at SYS.DBMS_STATS, line 4609 ORA-06512: at SYS.DBMS_STATS, line 4732 ORA-06512: at SYS.DBMS_STATS, line 4704 ORA-06512: at line 1 -- 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). * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala 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: DBMS_STATS error
8.1.7 I ran it again and it worked. I really have no idea why it failed. From: Regis Biassala [EMAIL PROTECTED] Date: 2003/06/12 Thu AM 10:09:52 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: DBMS_STATS error why are you so focused on this view: DBA_TAB_MODIFICATIONS It contains info on tables *modified* : columns INSERTS, UPDATES, DELETES give you the number rows inserted or updated or deleted since the last your tables was analyzed Concerning your error, I ran it as SYSTEM and it worked...8.1.7.0 SQL exec dbms_stats.gather_schema_stats(ownname='REGIS',estimate_percent=20,degree= 16,cascade=true,options='GATHER EMPTY' PL/SQL procedure successfully completed. which version of Oracle you are running ? Can you do a: desc dbms_stats ? Regis -Original Message- Sent: Thursday, June 12, 2003 1:49 PM To: Multiple recipients of list ORACLE-L I ran the following and got an error. Any idea? I noticed after I ran it that my DBA_TAB_MODIFICATION view has no records even though I ran gather_schema_stats yesterdays SQL exec dbms_stats.gather_schema_stats(ownname='MASTER_TEST2',estimate_percent=20, degree=16,cascade=true,options='GATH ER EMPTY'); BEGIN dbms_stats.gather_schema_stats(ownname='MASTER_TEST2',estimate_percent=20, degree=16,cascade=true,options='GATHER E MPTY'); END; * ERROR at line 1: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at SYS.DBMS_STATS, line 4609 ORA-06512: at SYS.DBMS_STATS, line 4732 ORA-06512: at SYS.DBMS_STATS, line 4704 ORA-06512: at line 1 -- 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). * This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible. This footnote also confirms that this message has been swept for computer viruses. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regis Biassala 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 via dbms_job - syntax question
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of quotes
(including none at all) around the cacscade keyword as per Raj' suggestion with still
no success.
Thomas's suggestion of using a procedure was my failback position and is probably the
way I will have to go. I am still frustrated that I cannot get the syntax to work
correctly.
Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it
fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
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: Igor Neyman
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: Hallas, John, Tech Dev
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 via dbms_job - syntax question SOLVED
Thanks for all the help. The problem was not with my coding but the syntax. In my
worked example I had CASCADE=TRUE whereas it should have been
CASCADE=TRUE. A subtle difference but one that caused me (and fellow listers) no end
of hassle.
So all the answers that I said were incorrect were not, they had all replicated my
original typo.
But one thing I proved was that you can mix and match positional and naming parameters
The final working version for anyone who is interested is
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCADE=TRUE);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Thanks to all
John
-Original Message-
Sent: 11 June 2003 09:24
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of quotes
(including none at all) around the cacscade keyword as per Raj' suggestion with still
no success.
Thomas's suggestion of using a procedure was my failback position and is probably the
way I will have to go. I am still frustrated that I cannot get the syntax to work
correctly.
Note I can get my original PL/SQL code to work and put an entry into dbms_jobs but it
fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
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: Igor Neyman
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: Hallas, John, Tech Dev
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
RE: dbms_stats via dbms_job - syntax question
John,
My code doesn't work probably because CASCADE = TRUE should actually
be: CASCADE = TRUE.
So, this should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Wednesday, June 11, 2003 3:24 AM
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of
quotes (including none at all) around the cacscade keyword as per Raj'
suggestion with still no success.
Thomas's suggestion of using a procedure was my failback position and is
probably the way I will have to go. I am still frustrated that I cannot
get the syntax to work correctly.
Note I can get my original PL/SQL code to work and put an entry into
dbms_jobs but it fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
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: Igor Neyman
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: Hallas, John, Tech Dev
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
RE: dbms_stats via dbms_job - syntax question SOLVED
But one thing I proved was that you can mix and match positional and
naming parameters
Hmm, I'd still rather use one OR another: you never know if mixing them
would work in the next version...
Why trying your luck?-:)
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Wednesday, June 11, 2003 4:35 AM
To: Multiple recipients of list ORACLE-L
Thanks for all the help. The problem was not with my coding but the
syntax. In my worked example I had CASCADE=TRUE whereas it should have
been
CASCADE=TRUE. A subtle difference but one that caused me (and fellow
listers) no end of hassle.
So all the answers that I said were incorrect were not, they had all
replicated my original typo.
But one thing I proved was that you can mix and match positional and
naming parameters
The final working version for anyone who is interested is
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCAD
E=TRUE);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Thanks to all
John
-Original Message-
Sent: 11 June 2003 09:24
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of
quotes (including none at all) around the cacscade keyword as per Raj'
suggestion with still no success.
Thomas's suggestion of using a procedure was my failback position and is
probably the way I will have to go. I am still frustrated that I cannot
get the syntax to work correctly.
Note I can get my original PL/SQL code to work and put an entry into
dbms_jobs but it fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
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: Igor Neyman
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
RE: dbms_stats via dbms_job - syntax question SOLVED
John
Then be sure to set the init.ora parameter
_do_what_I_mean=true
It solved most of my problems.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, June 11, 2003 4:35 AM
To: Multiple recipients of list ORACLE-L
Thanks for all the help. The problem was not with my coding but the syntax.
In my worked example I had CASCADE=TRUE whereas it should have been
CASCADE=TRUE. A subtle difference but one that caused me (and fellow
listers) no end of hassle.
So all the answers that I said were incorrect were not, they had all
replicated my original typo.
But one thing I proved was that you can mix and match positional and naming
parameters
The final working version for anyone who is interested is
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,CASCADE=T
RUE);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
Thanks to all
John
-Original Message-
Sent: 11 June 2003 09:24
To: Multiple recipients of list ORACLE-L
Thanks for the try Igor but that doesn't work either
ERROR at line 1:
ORA-06550: line 2, column 24:
PLS-00201: identifier 'CASCADE' must be declared
ORA-06550: line 1, column 99:
Wolfgang's suggestion doesn't work and I have tried every permutation of
quotes (including none at all) around the cacscade keyword as per Raj'
suggestion with still no success.
Thomas's suggestion of using a procedure was my failback position and is
probably the way I will have to go. I am still frustrated that I cannot get
the syntax to work correctly.
Note I can get my original PL/SQL code to work and put an entry into
dbms_jobs but it fails when I try a dbms_job.run
Thanks to everyone anyway.
John
-Original Message-
Sent: 10 June 2003 16:55
To: Multiple recipients of list ORACLE-L
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
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: Igor Neyman
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 via dbms_job - syntax question
Title: RE: dbms_stats via dbms_job - syntax question
you don't need quotes around cascade=true ...
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: Hallas, John, Tech Dev [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 10, 2003 10:35 AM
To: Multiple recipients of list ORACLE-L
Subject: dbms_stats via dbms_job - syntax question
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via dbms_jobs
The following syntax works fine to run the procedure interactively
execute dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit (l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the line but 2 of them around the schema name as that is a varchar2. Ideally I think I want 2 single quotes around the cascade but I cannot get it to work properly. The above example submits the job as
Job What
==
2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE');
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on his web site but it doesn't appaer to exist any more
John
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.*2
RE: dbms_stats via dbms_job - syntax question
John,
I think the easiest way to do this is to create a stored procedure that
calls dbms_stats for you. you could then simply run your stored procedure
from dbms_jobs.
create or replace procedure run_stats is
begin
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10
,cascade=true);
end;
and then:
declare
l_job number;
begin
dbms_job.submit (l_job,'run_stats',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
make sense?
PS. I personally don't like DBMS_JOBS. I use either Cron, or Tivoli on NT
systems to schedule all jobs.
hope this helps.
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Tuesday, June 10, 2003 10:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10
,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The syntax I
am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE
=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally I
think I want 2 single quotes around the cascade but I cannot get it to work
properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE');
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on his
web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
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 via dbms_job - syntax question
This should work:
declare
l_job number;
begin
dbms_job.submit (l_job,'begin dbms_stats.gather_schema_stats(ownname ='
|| CHR(39) || 'RPT_2G_MASTER' || CHR(39) || ', estimate_percent = 10,
block_sample = FALSE, CASCADE = TRUE); end;',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
When passing parameters to stored procedure, you can not mix and match
positional method with naming: either you use formal parameters, or
not.
It seems like you need naming.
I used CHR(39) to get quotes around schema name.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-Original Message-
Hallas, John, Tech Dev
Sent: Tuesday, June 10, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent
=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The
syntax I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASC
ADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally
I think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2
dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE')
;
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on
his web site but it doesn't appaer to exist any more
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hallas, John, Tech Dev
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: Igor Neyman
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 via dbms_job - syntax question
Why not submit it exactly the same as you do it interactively:
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(ownname=''RPT_3G_MASTER'',estimate_percent=10,cascade=true);',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
All you need to do is replace the single quotes around the schema name with
double quotes.
At 06:34 AM 6/10/2003 -0800, you wrote:
Listers,
Looking for a bit of help with the syntax to submit a dbms_stats run via
dbms_jobs
The following syntax works fine to run the procedure interactively
execute
dbms_stats.gather_schema_stats(ownname='RPT_3G_MASTER',estimate_percent=10,cascade=true);
but trying to get that into an dbms_job is destroying my brain.The syntax
I am trying is based around this script
declare
l_job number;
begin
dbms_job.submit
(l_job,'dbms_stats.gather_schema_stats(''RPT_2G_MASTER'',10,FALSE,''CASCADE=TRUE'');',
trunc(sysdate+1)+01/24,'sysdate+7');
end;
/
The problem area is the cascade keyword. I am using single quotes in the
line but 2 of them around the schema name as that is a varchar2. Ideally I
think I want 2 single quotes around the cascade but I cannot get it to
work properly. The above example submits the job as
Job What
==
2 dbms_stats.gather_schema_stats('RPT_2G_MASTER',10,FALSE,'CASCADE=TRUE');
but that fails to run
Has anybody got any ideas.
I seem to recall Connor McDonald having some information about this on his
web site but it doesn't appaer to exist any more
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Wolfgang Breitling
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 broken
Interesting, I use dbms_stats with gather stale in 8.1.7.4 it behaves beautifully. Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 11:15 PMTo: Multiple recipients of list ORACLE-LSubject: RE: dbms_stats broken Hmmm ... I haven't tried with stale option, but gather-schema works. I know .. I know... this isnot the answer you were looking for ... Raj -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 10:25 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_stats broken Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey.
Re: dbms_stats broken
Koivu, Lisa wrote: Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? Im trying to use dbms_stats gather schema stats with the stale option and it just isnt working in 8.1.7.4. This is documented on Metalink. Id love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. I run this sql out of a python program against a 9.2.0.2.0 database on a Compaq Tru64 server. Works like a charm. The 'gather stale' option works as well. sql = BEGIN DBMS_STATS.GATHER_SCHEMA_STATS( ownname = '%s', options = 'GATHER AUTO', estimate_percent = dbms_stats.auto_sample_size, method_opt = 'for all columns size auto', cascade = true); END; % schema --Glenn Stauffer -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Glenn Stauffer INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: dbms_stats broken
Hey Lisa (Monkey?), Yes, I had a problem with the stale option, too, at least under 8.1.6. It would bomb out with no more rows or something like that. Under 8.1.7.4 on Solaris, I no longer had that problem but I did have some poorer explain plans than with good ol' ANALYZE. See the Burned by DBMS_STATS **AGAIN** thread starting on 04/07/2003. Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Wednesday, May 28, 2003 9:25 PM To: Multiple recipients of list ORACLE-L Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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 broken
Darrell,
I put dbms_stats into production, on 8.1.7.4, over two months ago have not
had a problem. Here is what I did to compute the stats automatically. (BTW: this
runs under system)
create procedure compute_daily_stats is
begin
for a in (select distinct table_owner from dba_tab_modifications) loop
DBMS_STATS.GATHER_SCHEMA_STATS(a.table_owner,
NULL,FALSE,
'FOR ALL indexed COLUMNS SIZE 1',
NULL,'DEFAULT',TRUE,NULL,NULL,'GATHER
STALE','DEFAULT');
end loop;
end;
/
declare
jb integer;
begin
dbms_job.submit(jb,
'compute_daily_stats;',trunc(sysdate+1)+(6/24),'trunc(sysdate+1)+(6/24)',FALSE);
dbms_output.put_line('Job is '||jb);
end;
/
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-Original Message-
Sent: Thursday, May 29, 2003 12:30 AM
To: Multiple recipients of list ORACLE-L
Lisa,
Wow, you might be saving me from peril right now. I have tested this
with a small set of tables with no problems (in and 8.1.7.4 instance).
I'm preparing to go 'schema' wide in the next week or so for further
testing prior to implementing in production.
I'd be very interested in more details of your problems in 8.1.7.4 and
of course I'll post reports of testing to the list as well.
For starters, here is the code I use to obtain a list of 'stale'
qualified tables:
( For proper credit, I think I got this from asktom.oracle.com)
set serverout on size 9
declare
l_objList dbms_stats.objecttab;
begin
dbms_stats.gather_schema_stats
( ownname = '1',
options = 'LIST STALE',
objlist = l_objlist );
for i in 1 .. l_objlist.count
loop
--dbms_output.put_line( l_objlist(i).objtype );
dbms_output.put_line( l_objlist(i).objname );
end loop;
end;
/
And the code to gather stats:
set serverout on size 99000
begin
dbms_stats.gather_schema_stats(
ownname='1',
options='GATHER STALE',
cascade=TRUE,
degree=8,
granularity='ALL',
method_opt='FOR ALL INDEXED COLUMNS SIZE 1'
);
end;
/
Thanks,
Darrell
[EMAIL PROTECTED] 05/28/03 09:24PM
Hello everyone,
Is anyone using dbms_stats and gather stale or gather auto in 9.2?
I'm
trying to use dbms_stats gather schema stats with the stale option and
it just isn't working in 8.1.7.4. This is documented on Metalink.
I'd
love to hear from someone else if this is fixed in 9.2 and if it can
be
reliably used.
Thank you
Lisa Monkey.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Darrell Landrum
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: Goulet, Dick
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 broken
I haven't used it, but here is what we are doing: we created a simple package which loops through dba_tab_modifications and finds itself (the same way as oracle would) which tables are stale: where the sum of inserts, updates and deletes is more than 10% of the number of rows, and than run dbms_stats for those only. That way you have a bit more flexibility (you can for example exclude some tables), makeestimate or calculate decisions on the table basis, etc. Djordje -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 10:25 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_stats broken Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? Im trying to use dbms_stats gather schema stats with the stale option and it just isnt working in 8.1.7.4. This is documented on Metalink. Id love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey.
RE: dbms_stats broken
Lisa, I'm still using Analyze so I have no direct experience with this package. That being said, would the SET_TABLE_STATS work for the partitions that haven't been loaded yet? Mike Hand Polaroid Corp. -Original Message- Sent: Thursday, May 29, 2003 10:46 AM To: Multiple recipients of list ORACLE-L Hi Darrell Kirti - It was late last night when I was looking at this. It seems I should be using GATHER EMPTY instead of GATHER STALE. However this will put statistics on partitions with no rows. When I load new partitions tomorrow (they are empty prior to the daily load), the number of rows inserted wouldn't reach 10% change. It would take over a week to reach 10% change in order for GATHER STALE to pick up on this and re-analyze these partitions. I don't want statistics saying there are 0 records in a partition that is indeed loaded. I guess GATHER STALE would be much more useful if the 10% threshold could be modified, and/or the threshold could be partition specific. And Kirti, the bugs I have seen are 1192012, 1890016, 2157655. I thought I was running into 1890016. Looks like I'll have to code around this after all. Darn it all. I really am a monkey. Sheesh Lisa -Original Message- Sent: Thursday, May 29, 2003 12:30 AM To: Multiple recipients of list ORACLE-L Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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: Koivu, Lisa 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: Hand, Michael T 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 broken
Hi Darrell Kirti - It was late last night when I was looking at this. It seems I should be using GATHER EMPTY instead of GATHER STALE. However this will put statistics on partitions with no rows. When I load new partitions tomorrow (they are empty prior to the daily load), the number of rows inserted wouldn't reach 10% change. It would take over a week to reach 10% change in order for GATHER STALE to pick up on this and re-analyze these partitions. I don't want statistics saying there are 0 records in a partition that is indeed loaded. I guess GATHER STALE would be much more useful if the 10% threshold could be modified, and/or the threshold could be partition specific. And Kirti, the bugs I have seen are 1192012, 1890016, 2157655. I thought I was running into 1890016. Looks like I'll have to code around this after all. Darn it all. I really am a monkey. Sheesh Lisa -Original Message- Sent: Thursday, May 29, 2003 12:30 AM To: Multiple recipients of list ORACLE-L Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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: Koivu, Lisa 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 broken
Lisa et al, Am I missing something or did someone ever mention that for STALE to work, one needs to set MONITORING on required objects? Straight from the Fine Manual: Enabling Automated Statistics Gathering The GATHER STALE option only gathers statistics for tables that have stale statistics and for which you have enabled the MONITORING attribute. To enable monitoring for tables, use the MONITORING keyword of the CREATE TABLE and ALTER TABLE statements, as described in Designating Tables for Monitoring and Automated Statistics Gathering on page 8-9. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Great, uplifting music (and best of all commercial-free!) - http://www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Darrell Landrum [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 9:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: dbms_stats broken Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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: John Kanagaraj 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
Hi John, Yes, monitoring was set. I wouldn't see anything in *tab_modifications if monitoring wasn't set. Here's a new twist. What percentage are you comfortable with for valid estimates? I attended a seminar given by Jonathan Lewis a few weeks ago, and he stated that adequate statistics can be gathered using 1% sample. That was great news to me, who has time for huge estimates? OK, so I ran dbms_stats.gather_schema_stats using GATHER EMPTY and estimate_percent set to 1. The rowcounts are way wrong. (FACP-LISA)SELECT COUNT(*) 2 FROM VEGAS_MART 3 PARTITION (MAY_28_2003); COUNT(*) -- 603826 (FACP-LISA)select num_rows 2 from dba_tab_partitions 3 where table_name = 'VEGAS_MART' 4 and partition_name = 'MAY_28_2003'; NUM_ROWS -- 595500 (FACP-LISA) And Jonathan if you happen to read this email, if I am mis-stating what you stated in class please correct me. I am on 8.1.7.4 and that may be the difference. -Original Message- Sent: Thursday, May 29, 2003 1:36 PM To: Multiple recipients of list ORACLE-L Lisa et al, Am I missing something or did someone ever mention that for STALE to work, one needs to set MONITORING on required objects? Straight from the Fine Manual: Enabling Automated Statistics Gathering The GATHER STALE option only gathers statistics for tables that have stale statistics and for which you have enabled the MONITORING attribute. To enable monitoring for tables, use the MONITORING keyword of the CREATE TABLE and ALTER TABLE statements, as described in Designating Tables for Monitoring and Automated Statistics Gathering on page 8-9. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Great, uplifting music (and best of all commercial-free!) - http://www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Darrell Landrum [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 9:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: dbms_stats broken Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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: John Kanagaraj 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
RE: dbms_stats broken
Hi Mike, Yes, I could use it... but the GATHER EMPTY option set stats on the empty partitions correctly. I'm more concerned about having old stats on a partition that is now loaded. Thanks -Original Message- Sent: Thursday, May 29, 2003 12:30 PM To: Multiple recipients of list ORACLE-L Lisa, I'm still using Analyze so I have no direct experience with this package. That being said, would the SET_TABLE_STATS work for the partitions that haven't been loaded yet? Mike Hand Polaroid Corp. -Original Message- Sent: Thursday, May 29, 2003 10:46 AM To: Multiple recipients of list ORACLE-L Hi Darrell Kirti - It was late last night when I was looking at this. It seems I should be using GATHER EMPTY instead of GATHER STALE. However this will put statistics on partitions with no rows. When I load new partitions tomorrow (they are empty prior to the daily load), the number of rows inserted wouldn't reach 10% change. It would take over a week to reach 10% change in order for GATHER STALE to pick up on this and re-analyze these partitions. I don't want statistics saying there are 0 records in a partition that is indeed loaded. I guess GATHER STALE would be much more useful if the 10% threshold could be modified, and/or the threshold could be partition specific. And Kirti, the bugs I have seen are 1192012, 1890016, 2157655. I thought I was running into 1890016. Looks like I'll have to code around this after all. Darn it all. I really am a monkey. Sheesh Lisa -Original Message- Sent: Thursday, May 29, 2003 12:30 AM To: Multiple recipients of list ORACLE-L Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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: Koivu, Lisa 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: Hand, Michael T 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:
RE: dbms_stats
I wouldn't call 1.4% [ (603826-595500)/603826 ] way wrong. Actually, for a 1% sample I find that pretty good. The problem I found with low sampling percentages is if you have skewed column values. If some values occur very often and others rather seldom, a 1% sample may only encounter the frequently occurring values and none of the infrequently occurring ones and come up with a really way off estimate for num_distinct. At 10:05 AM 5/29/2003 -0800, you wrote: Hi John, Yes, monitoring was set. I wouldn't see anything in *tab_modifications if monitoring wasn't set. Here's a new twist. What percentage are you comfortable with for valid estimates? I attended a seminar given by Jonathan Lewis a few weeks ago, and he stated that adequate statistics can be gathered using 1% sample. That was great news to me, who has time for huge estimates? OK, so I ran dbms_stats.gather_schema_stats using GATHER EMPTY and estimate_percent set to 1. The rowcounts are way wrong. (FACP-LISA)SELECT COUNT(*) 2 FROM VEGAS_MART 3 PARTITION (MAY_28_2003); COUNT(*) -- 603826 (FACP-LISA)select num_rows 2 from dba_tab_partitions 3 where table_name = 'VEGAS_MART' 4 and partition_name = 'MAY_28_2003'; NUM_ROWS -- 595500 (FACP-LISA) And Jonathan if you happen to read this email, if I am mis-stating what you stated in class please correct me. I am on 8.1.7.4 and that may be the difference. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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
Lisa, Have a look at ML Note:44961.1 (and maybe Note:114671.1 could also help). The 1% sample may work for larger tables - I am not sure John -Original Message- From: Koivu, Lisa [mailto:[EMAIL PROTECTED] Sent: Thursday, May 29, 2003 11:05 AM To: Multiple recipients of list ORACLE-L Subject: RE: dbms_stats Hi John, Yes, monitoring was set. I wouldn't see anything in *tab_modifications if monitoring wasn't set. Here's a new twist. What percentage are you comfortable with for valid estimates? I attended a seminar given by Jonathan Lewis a few weeks ago, and he stated that adequate statistics can be gathered using 1% sample. That was great news to me, who has time for huge estimates? OK, so I ran dbms_stats.gather_schema_stats using GATHER EMPTY and estimate_percent set to 1. The rowcounts are way wrong. (FACP-LISA)SELECT COUNT(*) 2 FROM VEGAS_MART 3 PARTITION (MAY_28_2003); COUNT(*) -- 603826 (FACP-LISA)select num_rows 2 from dba_tab_partitions 3 where table_name = 'VEGAS_MART' 4 and partition_name = 'MAY_28_2003'; NUM_ROWS -- 595500 (FACP-LISA) And Jonathan if you happen to read this email, if I am mis-stating what you stated in class please correct me. I am on 8.1.7.4 and that may be the difference. -Original Message- Sent: Thursday, May 29, 2003 1:36 PM To: Multiple recipients of list ORACLE-L Lisa et al, Am I missing something or did someone ever mention that for STALE to work, one needs to set MONITORING on required objects? Straight from the Fine Manual: Enabling Automated Statistics Gathering The GATHER STALE option only gathers statistics for tables that have stale statistics and for which you have enabled the MONITORING attribute. To enable monitoring for tables, use the MONITORING keyword of the CREATE TABLE and ALTER TABLE statements, as described in Designating Tables for Monitoring and Automated Statistics Gathering on page 8-9. John Kanagaraj Oracle Applications DBA DBSoft Inc (W): 408-970-7002 Great, uplifting music (and best of all commercial-free!) - http://www.klove.com ** The opinions and statements above are entirely my own and not those of my employer or clients ** -Original Message- From: Darrell Landrum [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 28, 2003 9:30 PM To: Multiple recipients of list ORACLE-L Subject: Re: dbms_stats broken Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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
RE: dbms_stats
Hi Wolfgang, In the grand scheme of things, that probably isn't awful. However, if the analyze can't get the row count right (how easy is that?) then how can I trust it to get the rest of the statistics correct? Just my two cents. Thanks for your reply. Lisa -Original Message- Sent: Thursday, May 29, 2003 3:25 PM To: Multiple recipients of list ORACLE-L I wouldn't call 1.4% [ (603826-595500)/603826 ] way wrong. Actually, for a 1% sample I find that pretty good. The problem I found with low sampling percentages is if you have skewed column values. If some values occur very often and others rather seldom, a 1% sample may only encounter the frequently occurring values and none of the infrequently occurring ones and come up with a really way off estimate for num_distinct. At 10:05 AM 5/29/2003 -0800, you wrote: Hi John, Yes, monitoring was set. I wouldn't see anything in *tab_modifications if monitoring wasn't set. Here's a new twist. What percentage are you comfortable with for valid estimates? I attended a seminar given by Jonathan Lewis a few weeks ago, and he stated that adequate statistics can be gathered using 1% sample. That was great news to me, who has time for huge estimates? OK, so I ran dbms_stats.gather_schema_stats using GATHER EMPTY and estimate_percent set to 1. The rowcounts are way wrong. (FACP-LISA)SELECT COUNT(*) 2 FROM VEGAS_MART 3 PARTITION (MAY_28_2003); COUNT(*) -- 603826 (FACP-LISA)select num_rows 2 from dba_tab_partitions 3 where table_name = 'VEGAS_MART' 4 and partition_name = 'MAY_28_2003'; NUM_ROWS -- 595500 (FACP-LISA) And Jonathan if you happen to read this email, if I am mis-stating what you stated in class please correct me. I am on 8.1.7.4 and that may be the difference. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Koivu, Lisa 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 broken
I looked at bug 1890016 on MetalClink and I'm confused. Did you run ANALYZE COMPUTE before running DBMS_STATS like the bug's test case? I thought this was a no-no??? If so, I'm wondering if it would it help to ANALYZE DELETE then rerun DBMS_STATS w/EMPTY, insert/update/delete test rows, force an update of DBA_TAB_MONITORING (via SHUTDOWN NORMAL or the new 9i method that escapes me), then try DBMS_STATS w/STALE option. Just a thought... Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Koivu, Lisa [mailto:[EMAIL PROTECTED] Sent: Thursday, May 29, 2003 9:46 AM To: Multiple recipients of list ORACLE-L Subject: RE: dbms_stats broken Hi Darrell Kirti - It was late last night when I was looking at this. It seems I should be using GATHER EMPTY instead of GATHER STALE. However this will put statistics on partitions with no rows. When I load new partitions tomorrow (they are empty prior to the daily load), the number of rows inserted wouldn't reach 10% change. It would take over a week to reach 10% change in order for GATHER STALE to pick up on this and re-analyze these partitions. I don't want statistics saying there are 0 records in a partition that is indeed loaded. I guess GATHER STALE would be much more useful if the 10% threshold could be modified, and/or the threshold could be partition specific. And Kirti, the bugs I have seen are 1192012, 1890016, 2157655. I thought I was running into 1890016. Looks like I'll have to code around this after all. Darn it all. I really am a monkey. Sheesh Lisa -Original Message- Sent: Thursday, May 29, 2003 12:30 AM To: Multiple recipients of list ORACLE-L Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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 broken
I haven't read the note yet, but what Rich mentions here may explain why I haven't encountered any problems. I started out testing with copies of prod tables which had no stats at all. So their first stats were gathered with gather_shema_stats. [EMAIL PROTECTED] 05/29/03 03:14PM I looked at bug 1890016 on MetalClink and I'm confused. Did you run ANALYZE COMPUTE before running DBMS_STATS like the bug's test case? I thought this was a no-no??? If so, I'm wondering if it would it help to ANALYZE DELETE then rerun DBMS_STATS w/EMPTY, insert/update/delete test rows, force an update of DBA_TAB_MONITORING (via SHUTDOWN NORMAL or the new 9i method that escapes me), then try DBMS_STATS w/STALE option. Just a thought... Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Koivu, Lisa [mailto:[EMAIL PROTECTED] Sent: Thursday, May 29, 2003 9:46 AM To: Multiple recipients of list ORACLE-L Subject: RE: dbms_stats broken Hi Darrell Kirti - It was late last night when I was looking at this. It seems I should be using GATHER EMPTY instead of GATHER STALE. However this will put statistics on partitions with no rows. When I load new partitions tomorrow (they are empty prior to the daily load), the number of rows inserted wouldn't reach 10% change. It would take over a week to reach 10% change in order for GATHER STALE to pick up on this and re-analyze these partitions. I don't want statistics saying there are 0 records in a partition that is indeed loaded. I guess GATHER STALE would be much more useful if the 10% threshold could be modified, and/or the threshold could be partition specific. And Kirti, the bugs I have seen are 1192012, 1890016, 2157655. I thought I was running into 1890016. Looks like I'll have to code around this after all. Darn it all. I really am a monkey. Sheesh Lisa -Original Message- Sent: Thursday, May 29, 2003 12:30 AM To: Multiple recipients of list ORACLE-L Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Darrell Landrum 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
RE: dbms_stats
The answer is as usual it depends. If the table has a reasonably uniform row size and the blocks are approximately evenly utilized, then the analyze can extrapolate the total number of rows fairly accurately even from a small sample. However, if the row size fluctuates wildly, or if the block utilization fluctuates heavily, then you need a larger sample for accurate estimates of the totals. At 12:14 PM 5/29/2003 -0800, you wrote: Hi Wolfgang, In the grand scheme of things, that probably isn't awful. However, if the analyze can't get the row count right (how easy is that?) then how can I trust it to get the rest of the statistics correct? Just my two cents. Thanks for your reply. Lisa Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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 broken
Hmmm ... I haven't tried with stale option, but gather-schema works. I know .. I know... this isnot the answer you were looking for ... Raj -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28, 2003 10:25 PMTo: Multiple recipients of list ORACLE-LSubject: dbms_stats broken Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. 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.*2
Re: dbms_stats broken
Lisa, Wow, you might be saving me from peril right now. I have tested this with a small set of tables with no problems (in and 8.1.7.4 instance). I'm preparing to go 'schema' wide in the next week or so for further testing prior to implementing in production. I'd be very interested in more details of your problems in 8.1.7.4 and of course I'll post reports of testing to the list as well. For starters, here is the code I use to obtain a list of 'stale' qualified tables: ( For proper credit, I think I got this from asktom.oracle.com) set serverout on size 9 declare l_objList dbms_stats.objecttab; begin dbms_stats.gather_schema_stats ( ownname = '1', options = 'LIST STALE', objlist = l_objlist ); for i in 1 .. l_objlist.count loop --dbms_output.put_line( l_objlist(i).objtype ); dbms_output.put_line( l_objlist(i).objname ); end loop; end; / And the code to gather stats: set serverout on size 99000 begin dbms_stats.gather_schema_stats( ownname='1', options='GATHER STALE', cascade=TRUE, degree=8, granularity='ALL', method_opt='FOR ALL INDEXED COLUMNS SIZE 1' ); end; / Thanks, Darrell [EMAIL PROTECTED] 05/28/03 09:24PM Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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 broken
Hello Lisa Monkey, What platform are you jumping up on? Do you have tables with lots of branches.. er.. partitions? I have been using dbms_stats to collect stale stats on HP-UX 11.0 with a 8.1.7.4 database. These are not partitioned tables. All are being 'monitored', the script to collect stale stats runs every morning as some tables are truncated and reloaded, while others are modified. I get a report of all tables that were found to have 'stale' stats and got analyzed. Here is my today's report : (HRWT is the database, HRW is the schema) df2hptest [HRWT]: more gather_stale_stats_HRWT_HRW.log Connected. Owner HRW : TABLE - ATH_EMPLOYEE Owner HRW : TABLE - CTLR_KEYERS Owner HRW : TABLE - CUR_EMPLOYEES Owner HRW : TABLE - EMERGENCY_CONTACTS Owner HRW : TABLE - EXT_BA_EMPLOYEES Owner HRW : TABLE - EXT_GTE_EMPLOYEES Owner HRW : TABLE - LKP_EMPL_NAME Owner HRW : TABLE - MSS_APPLICATION_DIR Can you please post the Metalink reference about the problem with DBMS_STATS in 8.1.7.4? I know that the global stats (histograms) for partitioned tables is an issue, but would like to know of other problems as well. Thanks. - Kirti --- Koivu, Lisa [EMAIL PROTECTED] wrote: Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. __ 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: Kirtikumar Deshpande 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 broken
I don't use it but the other DBA in our group does (gather stale)... and it's working in 9.2.0.1 and 9.2.0.2 --- Koivu, Lisa [EMAIL PROTECTED] wrote: Hello everyone, Is anyone using dbms_stats and gather stale or gather auto in 9.2? I'm trying to use dbms_stats gather schema stats with the stale option and it just isn't working in 8.1.7.4. This is documented on Metalink. I'd love to hear from someone else if this is fixed in 9.2 and if it can be reliably used. Thank you Lisa Monkey. __ 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: Re: DBMS_STATS
Hi, friends: How do you use dbms_stats.gather_schema_stats in OLTP production system? I ever used estimate statistics =20% percent, and some time have serious performance impact while two big table join in my production changed. Later I changed to compute and till now , It is ok. And another question about CBO stability: Do you always analyze your database weekly(or with less data changed, monthly)? Some times because of something wrong, the db is performance poor. While development team will always say: it must be your CBO that is misfunction, repair it! Faint, I think CBO is ok in most case with compute statistics, but that kind of question is really headache, right? How do you friends answer that kind of questions? Sometimes I even want to stop analyze the db weekly.As far as I know, some site like Ebay do not analye database.Can it because they use rbo? And to jeff, for DW application, for indexed columns size 1 is good because it gather histrogram data, but for OLTP system, do you think it is necessary? Friends please share your opinions? Do you use dbms_stats.gather_table_stats, or dbms_stats.gather_schema_stats? My scripts: echo begin to analyze user bidder at `date +%x%T` /export/home/oracle/log/analyze.log sqlplus /nolog EOF connect / as sysdba alter session set sort_area_size=1; alter session set sort_multiblock_read_count =128; execute dbms_stats.gather_schema_stats(ownname='bidder',estimate_percent=99,degree=8,cascade=true,method_opt='for all columns'); quit EOF echo finished analyze user bidder at `date +%x%T` /export/home/oracle/log/analyze.log Thanks. Regards zhu chao msn:[EMAIL PROTECTED] www.cnoug.org(China Oracle User Group) === 2003-02-27 08:09:00 ,you wrote£º=== Terrian, Tom (Contractor) (DAASC) wrote: I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster.Runs in 45 seconds:analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours:execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same?Thanks,Tom Hello Tom We too experienced terrible performance upon first using dbms_stats. There are two things we did that brought the performance in line with the analyze. 1. We set method_opt = 'FOR ALL INDEXED COLUMNS SIZE 1' 2. We set estimate_percent = 15 Hope this helps. BTW: Sun/Solaris 2.6 2.8 Running 8i, 9i, 9.2 5 terabyte db's Jeff = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: chao_ping 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: DBMS_STATS
We run DBMS_STATS.GATHER_TABLE_STATS nightly as cron job using this script
to spool the result to a temp file, then use sqlplus to run that file.
select 'execute DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','''||
segment_name||''',cascade = TRUE);'
from ( select owner,segment_name, sum(bytes) from dba_segments s
where segment_type like 'TABLE%' and
owner in ('XYZ') and
s.partition_name is null
group by owner,segment_name
order by sum(s.bytes) desc);
We use multi-thread (3 threads) to run this script and it cuts the whole
time by half. Now it takes about 1.5 hours (our data set is small). For
another larger schema, we do this once a month.
Guang Mei
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of chao_ping
Sent: Friday, February 28, 2003 3:49 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Re: DBMS_STATS
Hi, friends:
How do you use dbms_stats.gather_schema_stats in OLTP
production system?
I ever used estimate statistics =20% percent, and some
time have serious performance impact while two big table join
in my production changed. Later I changed to compute and till
now , It is ok.
And another question about CBO stability:
Do you always analyze your database weekly(or with less
data changed, monthly)? Some times because of something
wrong, the db is performance poor. While development team
will always say: it must be your CBO that is misfunction,
repair it! Faint, I think CBO is ok in most case with compute
statistics, but that kind of question is really headache,
right? How do you friends answer that kind of questions?
Sometimes I even want to stop analyze the db weekly.As far as
I know, some site like Ebay do not analye database.Can it
because they use rbo?
And to jeff, for DW application, for indexed columns
size 1 is good because it gather histrogram data, but for
OLTP system, do you think it is necessary? Friends please
share your opinions? Do you use
dbms_stats.gather_table_stats, or dbms_stats.gather_schema_stats?
My scripts:
echo begin to analyze user bidder at `date +%x%T`
/export/home/oracle/log/analyze.log
sqlplus /nolog EOF
connect / as sysdba
alter session set sort_area_size=1;
alter session set sort_multiblock_read_count =128;
execute
dbms_stats.gather_schema_stats(ownname='bidder',estimate_perc
ent=99,degree=8,cascade=true,method_opt='for all columns');
quit
EOF
echo finished analyze user bidder at `date +%x%T`
/export/home/oracle/log/analyze.log
Thanks.
Regards
zhu chao
msn:[EMAIL PROTECTED]
www.cnoug.org(China Oracle User Group)
=== 2003-02-27 08:09:00 ,you wrote£º===
Terrian, Tom (Contractor) (DAASC) wrote:
I have never had good luck with DBMS_STATS. It seems that the old
analyze runs much faster.Runs in 45 seconds:analyze table
log_trans partition (log_trans_20030104) estimate
statistics sample 5
percent; Takes over 2 hours:execute
dbms_stats.gather_table_stats(ownname = 'LDGADMIN', -
tabname = 'LOG_TRANS', -
partname =
'LOG_TRANS_20030102', -
estimate_percent = 5);
Am I missing something? Aren't both commands the same?Thanks,Tom
Hello Tom
We too experienced terrible performance upon first using dbms_stats.
There
are two things we did that brought the performance in line with the
analyze.
1. We set method_opt = 'FOR ALL INDEXED COLUMNS SIZE 1'
2. We set estimate_percent = 15
Hope this helps.
BTW:
Sun/Solaris 2.6 2.8
Running 8i, 9i, 9.2
5 terabyte db's
Jeff
= = = = = = = = = = = = = = = = = = = =
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: chao_ping
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: gmei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also
RE: DBMS_STATS
Thanks to Tim Johnston and Jonathan Lewis. They were exactly right! Tom -Original Message- Sent: Tuesday, February 25, 2003 7:49 PM To: Multiple recipients of list ORACLE-L To expand on this, the action level is controlled by the granularity parameter... Granularity of statistics to collect (only pertinent if the table is partitioned). DEFAULT: Gather global- and partition-level statistics. SUBPARTITION: Gather subpartition-level statistics. PARTITION: Gather partition-level statistics. GLOBAL: Gather global statistics. So, in you case, run the following... execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', estimate_percent = 5, granularity = 'PARTITION'); See the supplied package reference for more details... Tim -Original Message- Sent: Tuesday, February 25, 2003 4:50 PM To: Multiple recipients of list ORACLE-L The default action of dbms_stats against a single partition of a partitioned table is much more aggressive than a simple analyze of the partition. At the least, it does a similar analyze of the whole table in order to maintain the global table statistics - you need to set the granularity of the analyze to stop this happening. On a quick test, with sql_trace turned on and using 9.2.0.2, your choice of parameters gave me: Two 5% analyzes of the specified partition, with a small difference relating to one longish varchar() column. One 5% analyze of the whole table. One 50% analyze of the whole table - restricted to a much smaller process of a subset of the columns that omitted the one longish varchar() column. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 25 February 2003 18:12 I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Johnston, Tim 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: Terrian, Tom (Contractor) (DAASC) 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
Terrian, Tom (Contractor) (DAASC) wrote: I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster.Runs in 45 seconds:analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent;Takes over 2 hours:execute dbms_stats.gather_table_stats(ownname => 'LDGADMIN', - tabname => 'LOG_TRANS', - partname => 'LOG_TRANS_20030102', - estimate_percent => 5); Am I missing something? Aren't both commands the same?Thanks,Tom Hello Tom We too experienced terrible performance upon first using dbms_stats. There are two things we did that brought the performance in line with the analyze. 1. We set method_opt = 'FOR ALL INDEXED COLUMNS SIZE 1' 2. We set estimate_percent = 15 Hope this helps. BTW: Sun/Solaris 2.6 2.8 Running 8i, 9i, 9.2 5 terabyte db's Jeff
RE: DBMS_STATS
When is the best time to gather Global Stats? Using the old ANALYZE command, I would analyze each partition as it accumulated data. Now that we have global stats, should I be gather global stats each time I analyze a partition? That would naturally increase the length of time to gather stats. Oracle recommends deleting global stats on partitioned tables in 11i (running on an 8i database). See bde_last_analyzed.sql on MetaLink. In fact, if you read the comments in this script, it implies that you should never gather global stats on a partitioned table. Any thoughts on this? Has anyone had problems with global stats on 9i? Also, does anybody recommend SKEWONLY or AUTO_SAMPLE_SIZE with DBMS_STATS? My testing shows that this causes the analyze to take longer (which is to be expected). I haven't yet determined if its worth the extra time. Thanks, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA [EMAIL PROTECTED] 02/25/03 07:49PM To expand on this, the action level is controlled by the granularity parameter... Granularity of statistics to collect (only pertinent if the table is partitioned). DEFAULT: Gather global- and partition-level statistics. SUBPARTITION: Gather subpartition-level statistics. PARTITION: Gather partition-level statistics. GLOBAL: Gather global statistics. So, in you case, run the following... execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', estimate_percent = 5, granularity = 'PARTITION'); See the supplied package reference for more details... Tim -Original Message- Sent: Tuesday, February 25, 2003 4:50 PM To: Multiple recipients of list ORACLE-L The default action of dbms_stats against a single partition of a partitioned table is much more aggressive than a simple analyze of the partition. At the least, it does a similar analyze of the whole table in order to maintain the global table statistics - you need to set the granularity of the analyze to stop this happening. On a quick test, with sql_trace turned on and using 9.2.0.2, your choice of parameters gave me: Two 5% analyzes of the specified partition, with a small difference relating to one longish varchar() column. One 5% analyze of the whole table. One 50% analyze of the whole table - restricted to a much smaller process of a subset of the columns that omitted the one longish varchar() column. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 25 February 2003 18:12 I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Johnston, Tim INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to
Re: DBMS_STATS
There is no generic answer to that question. Global Stats on partitioned tables do take a long time to collect - and you do need them to be reasonable because any query that the optimizer cannot pin down to exactly one partition at parse time is optimised according to the global stats. The best bet is to cheat. If you know your data you can analyze the partitions, but use dbms_stats.set_table_stats et. al. to set the global stats. Run dbms_stats with sql_trace switched on to see what happens with the 'exotic' options. I think Connor has some comments on his website - www.oracledba.co.uk - but the bottom line is don't be surprised if Oracle wastes computer resources when trying to automatic the jobs that require you to know the data. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 26 February 2003 13:23 When is the best time to gather Global Stats? Using the old ANALYZE command, I would analyze each partition as it accumulated data. Now that we have global stats, should I be gather global stats each time I analyze a partition? That would naturally increase the length of time to gather stats. Oracle recommends deleting global stats on partitioned tables in 11i (running on an 8i database). See bde_last_analyzed.sql on MetaLink. In fact, if you read the comments in this script, it implies that you should never gather global stats on a partitioned table. Any thoughts on this? Has anyone had problems with global stats on 9i? Also, does anybody recommend SKEWONLY or AUTO_SAMPLE_SIZE with DBMS_STATS? My testing shows that this causes the analyze to take longer (which is to be expected). I haven't yet determined if its worth the extra time. Thanks, Jay Hostetter Oracle DBA D. E. Communications Ephrata, PA USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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
Title: Message May have something to do with bug 2649728, which I just heard about for the first time no more than 10 seconds ago. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 56 Denver - Hotsos Clinic101, Mar 2527 London -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Terrian, Tom (Contractor) (DAASC) Sent: Tuesday, February 25, 2003 12:13 PM To: Multiple recipients of list ORACLE-L Subject: DBMS_STATS I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statisticssample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom
RE: DBMS_STATS
Title: Message ok, I will take a look. thanks -Original Message-From: Cary Millsap [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 25, 2003 1:35 PMTo: Multiple recipients of list ORACLE-LSubject: RE: DBMS_STATS May have something to do with bug 2649728, which I just heard about for the first time no more than 10 seconds ago. Cary MillsapHotsos Enterprises, Ltd.http://www.hotsos.comUpcoming events:- RMOUG Training Days 2003, Mar 5-6 Denver- Hotsos Clinic101, Mar 25-27 London -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Terrian, Tom (Contractor) (DAASC)Sent: Tuesday, February 25, 2003 12:13 PMTo: Multiple recipients of list ORACLE-LSubject: DBMS_STATS I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statisticssample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', -estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom
Re: DBMS_STATS
Title: Message Could it have to do with the fact that ANALYZE is running against a different partition than DBMS_STATS? - Original Message - From: Terrian, Tom (Contractor) (DAASC) To: Multiple recipients of list ORACLE-L Sent: Tuesday, February 25, 2003 11:12 AM Subject: DBMS_STATS I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statisticssample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', -estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom
RE: DBMS_STATS
Reading the bug, I'm frustrated for the user. Anyway, what does Event 10190 do? Or is this a case of File a TAR? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Tuesday, February 25, 2003 12:35 PM To: Multiple recipients of list ORACLE-L May have something to do with bug 2649728, which I just heard about for the first time no more than 10 seconds ago. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- (Contractor) (DAASC) Sent: Tuesday, February 25, 2003 12:13 PM To: Multiple recipients of list ORACLE-L I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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
I think since DBMS_STATS also gathers histograms its taking more time Babu Tim Gorman [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] m cc: Sent by: Subject: Re: DBMS_STATS [EMAIL PROTECTED] 02/25/03 02:59 PM Please respond to ORACLE-L Could it have to do with the fact that ANALYZE is running against a different partition than DBMS_STATS? - Original Message - From: Terrian, Tom (Contractor) (DAASC) To: Multiple recipients of list ORACLE-L Sent: Tuesday, February 25, 2003 11:12 AM Subject: DBMS_STATS I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom _ This e-mail transmission and any attachments to it are intended solely for the use of the individual or entity to whom it is addressed and may contain confidential and privileged information. If you are not the intended recipient, your use, forwarding, printing, storing, disseminating, distribution, or copying of this communication is prohibited. If you received this communication in error, please notify the sender immediately by replying to this message and delete it from your computer. -- 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
Check out $ORACLE_HOME/rdbms/mesg/oraus.msg to find out the meaning of any kernel event. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- Rich Sent: Tuesday, February 25, 2003 2:19 PM To: Multiple recipients of list ORACLE-L Reading the bug, I'm frustrated for the user. Anyway, what does Event 10190 do? Or is this a case of File a TAR? Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Tuesday, February 25, 2003 12:35 PM To: Multiple recipients of list ORACLE-L May have something to do with bug 2649728, which I just heard about for the first time no more than 10 seconds ago. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 25-27 London -Original Message- (Contractor) (DAASC) Sent: Tuesday, February 25, 2003 12:13 PM To: Multiple recipients of list ORACLE-L I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: Cary Millsap 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
The default action of dbms_stats against a single partition of a partitioned table is much more aggressive than a simple analyze of the partition. At the least, it does a similar analyze of the whole table in order to maintain the global table statistics - you need to set the granularity of the analyze to stop this happening. On a quick test, with sql_trace turned on and using 9.2.0.2, your choice of parameters gave me: Two 5% analyzes of the specified partition, with a small difference relating to one longish varchar() column. One 5% analyze of the whole table. One 50% analyze of the whole table - restricted to a much smaller process of a subset of the columns that omitted the one longish varchar() column. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 25 February 2003 18:12 I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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
To expand on this, the action level is controlled by the granularity parameter... Granularity of statistics to collect (only pertinent if the table is partitioned). DEFAULT: Gather global- and partition-level statistics. SUBPARTITION: Gather subpartition-level statistics. PARTITION: Gather partition-level statistics. GLOBAL: Gather global statistics. So, in you case, run the following... execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', estimate_percent = 5, granularity = 'PARTITION'); See the supplied package reference for more details... Tim -Original Message- Sent: Tuesday, February 25, 2003 4:50 PM To: Multiple recipients of list ORACLE-L The default action of dbms_stats against a single partition of a partitioned table is much more aggressive than a simple analyze of the partition. At the least, it does a similar analyze of the whole table in order to maintain the global table statistics - you need to set the granularity of the analyze to stop this happening. On a quick test, with sql_trace turned on and using 9.2.0.2, your choice of parameters gave me: Two 5% analyzes of the specified partition, with a small difference relating to one longish varchar() column. One 5% analyze of the whole table. One 50% analyze of the whole table - restricted to a much smaller process of a subset of the columns that omitted the one longish varchar() column. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon one-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___March 19th USA_(FL)_May 2nd Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 25 February 2003 18:12 I have never had good luck with DBMS_STATS. It seems that the old analyze runs much faster. Runs in 45 seconds: analyze table log_trans partition (log_trans_20030104) estimate statistics sample 5 percent; Takes over 2 hours: execute dbms_stats.gather_table_stats(ownname = 'LDGADMIN', - tabname = 'LOG_TRANS', - partname = 'LOG_TRANS_20030102', - estimate_percent = 5); Am I missing something? Aren't both commands the same? Thanks, Tom -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Johnston, Tim 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 question
Well, the results seem to indicate that the saved statistics are taken before
gathering new statistics. When I read the documentation, I see stattab User stat
table identifier describing where to save the current statistics. I guess this can
be read as save the current statistics in stattab before computing the new
statistics. Nothing like clarity of expression. This is why tech writers earn the
big bucks 8-)
Kevin Kennedy
First Point Energy Corporation
If you take RAC out of Oracle you get OLE! What can this mean?
-Original Message-
Sent: Thursday, July 25, 2002 1:14 PM
To: Multiple recipients of list ORACLE-L
Hi,
We are testing dbma_stats package to gather statistics for our schema???
we have created 1 table 'test' with 1 column
abc number
we are doing following steps:
1) insert into test values(1);
commit;
2) SQL EXEC DBMS_STATS.GATHER_Schema_STATS('HS',STATTAB = 'stats_temp1',statid
=
'test1');
3) SQL select table_name,num_rows,blocks,empty_blocks from user_tables where
table
_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
-- -- --
test 1 10
4) insert into test values(2);
commit;
5) SQL EXEC DBMS_STATS.GATHER_Schema_STATS('HS',STATTAB = 'stats_temp1',statid
=
'test2');
6) SQL select table_name,num_rows,blocks,empty_blocks from user_tables where
table
_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
-- -- --
test 2 10
7) now if i do,
SQL EXEC DBMS_STATS.DELETE_Schema_STATS('HS');
8) If i try to import
SQL EXEC DBMS_STATS.import_Schema_STATS('HS',STATTAB = 'stats_temp1',STATID
=
'TEST1');
THIS DOES NOT POPULATE THE STATSISTICS IN USER_TABLES
9) And if i do like this:
SQL EXEC DBMS_STATS.import_Schema_STATS('HS',STATTAB = 'stats_temp1',STATID
=
'TEST2');
it shows output num_rows=1 whereas it is expected to have num_rows=2
3) SQL select table_name,num_rows,blocks,empty_blocks from user_tables where
table
_name='TEST';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
-- -- --
test 1 10
WHY THE FIRST IMPORT not poplulaing the stats column AND SECOND IMPORT have 1
rows and not 2 ROWS ?
Thanks
--Harvinder
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Harvinder 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: kkennedy
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: dbms_stats issues on 8i
Jack, 7) GATHER_STALE We just discussed this, is the MONITOR option set for all your tables? So that DBA_TAB_MODIFICATIONS is populated and then DBMS_STATS can determine if stats are stale. I know there is an article in Metalink that speaks to this. -Original Message- Sent: Wednesday, May 22, 2002 1:58 PM To: Multiple recipients of list ORACLE-L Hello fellow orabrains, Although Oracle has made it clear that DBMS_STATS is the future and that ANALYZE will be desupported, and it seems to work well in 9i, here are some things that I uncovered in my research of it during our initial stats process implementation (we were on 8.1.6 during this time): 1) GATHER_DATABASE_STATS gathers stats on SYS schema. Also creates possible deadlock scenario that terminates the process with DD stats half gathered, and the workaround is to delete the SYS stats. 2) ANALYZE_SCHEMA does not work I found this to be true in our warehouse. Fixed in 9i. 3) ANALYZE_SCHEMA does not gather stats on all tables Workaround is to analyze those tables manually (er, um,.never mind) 5) DBMS_STATS does not gather all stats Gathers only stats relevant to CBO, such as num_rows, not those such as empty_blocks. Not really relevant, necessarily. 6) PARALLEL clause does not work unless you specify FOR ALL COLUMNS SIZE x 7) GATHER_STALE clause does not gather stale stats Also found this to be true, which is why I wrote a looping analyze procedure. 8) Would not analyze our partitioned fact table at the top level, no matter what I tried. Waited 9 hours, restarted, and waited another 6. Used parallel degree 24 and still no good. Gave it up and used analyze which did each partition in ~3-5 minutes. 9) Cannot use ANALYZE after dbms_stats. DBMS_STATS sets GLOBAL_FLAG to Y, which prevents ANALYZE from storing stats for that table. Workaround is to delete stats with DBMS_STATS.DELETE_TABLE_STATS and then use ANALYZE. There are others, like the DESC index problem that I did not research, just noted in passing. /jack silvey __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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: Grabowy, Chris 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: dbms_stats issues on 8i
So Jack, if I'm reading between the lines correctly, you find no reason to use DBMS_STATS prior to 9i? My 8i databases are purrfectly happy with ANALYZE, and I'm loathe to change something (that works) just to use the latest and greatest. I don't like hemorrhaging out on the bleeding edge. Jared Jack Silvey [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/22/2002 10:57 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:dbms_stats issues on 8i Hello fellow orabrains, Although Oracle has made it clear that DBMS_STATS is the future and that ANALYZE will be desupported, and it seems to work well in 9i, here are some things that I uncovered in my research of it during our initial stats process implementation (we were on 8.1.6 during this time): 1) GATHER_DATABASE_STATS gathers stats on SYS schema. Also creates possible deadlock scenario that terminates the process with DD stats half gathered, and the workaround is to delete the SYS stats. 2) ANALYZE_SCHEMA does not work I found this to be true in our warehouse. Fixed in 9i. 3) ANALYZE_SCHEMA does not gather stats on all tables Workaround is to analyze those tables manually (er, um,.never mind) 5) DBMS_STATS does not gather all stats Gathers only stats relevant to CBO, such as num_rows, not those such as empty_blocks. Not really relevant, necessarily. 6) PARALLEL clause does not work unless you specify FOR ALL COLUMNS SIZE x 7) GATHER_STALE clause does not gather stale stats Also found this to be true, which is why I wrote a looping analyze procedure. 8) Would not analyze our partitioned fact table at the top level, no matter what I tried. Waited 9 hours, restarted, and waited another 6. Used parallel degree 24 and still no good. Gave it up and used analyze which did each partition in ~3-5 minutes. 9) Cannot use ANALYZE after dbms_stats. DBMS_STATS sets GLOBAL_FLAG to Y, which prevents ANALYZE from storing stats for that table. Workaround is to delete stats with DBMS_STATS.DELETE_TABLE_STATS and then use ANALYZE. There are others, like the DESC index problem that I did not research, just noted in passing. /jack silvey __ Do You Yahoo!? LAUNCH - Your Yahoo! Music Experience http://launch.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack Silvey 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: DBMS_STATS
ya u have to run the DBMSstat.sql which is in oracle_home/rdbms/admin/dbmsstat.sql after that u have to gather the statistics. Rgds Ayyapps This communication contains information, which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s), please note that any distribution, printing, copying or use of this communication or the information in it is strictly prohibited. If you have received this communication in error, please notify the sender immediately and then destroy any copies of it. Visit us @ www.ssiworldwide.com -- 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: DBMS_STATS in 8.1.6
We had trouble with analyze during heavy updates. Something to do with SNAPSHOT TOO OLD. The instance froze. It is corrected somewhere in 8.1.7. Oracle will not port the fix to 8.1.6. We schedule the analyze to work at night and no problems. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: Jesse, Rich [SMTP:[EMAIL PROTECTED]] Sent: Tue, February 26, 2002 12:39 AM To: Multiple recipients of list ORACLE-L Subject: DBMS_STATS in 8.1.6 Hey all, I'm looking to start CBO on an 8.1.6.0.0 DB on Solaris 2.8. But looking thru Metaclink, I see some potential problems with some of the DBMS_STATS package in this version, like with GATHER_SCHEMA_STATS. Anyone have any suggestions as to Yay or Nay for this on 8.1.6.0.0? Unfortunately, this is another 3rd party app which refuses to support any other version (not sure about patchsets, though) of Oracle, so I'm stuck here for the time being. I'm leaning heavily towards the cautious route of using ANALYZE and reading DBA_TAB_MODIFICATIONS once a week to see if I should re-ANALYZE. TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= 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: DBMS_STATS in 8.1.6
Well, it has some bugs with GATHER EMPTY and GATHER STALE on your release. However, I set it in some clients and it is working fine. As an advice, don't use those options, reduce the size of the bucket just to generate statistics quickly, lastly if you decide against DBMS_STATS, don't run statistics with analyze because it doesn't generate any. I set it with monitoring option and as I said it is working fine. I was cautious just to exclude tables that recently been truncated. However, it is working fine. Regards. --- Jesse, Rich [EMAIL PROTECTED] wrote: Hey all, I'm looking to start CBO on an 8.1.6.0.0 DB on Solaris 2.8. But looking thru Metaclink, I see some potential problems with some of the DBMS_STATS package in this version, like with GATHER_SCHEMA_STATS. Anyone have any suggestions as to Yay or Nay for this on 8.1.6.0.0? Unfortunately, this is another 3rd party app which refuses to support any other version (not sure about patchsets, though) of Oracle, so I'm stuck here for the time being. I'm leaning heavily towards the cautious route of using ANALYZE and reading DBA_TAB_MODIFICATIONS once a week to see if I should re-ANALYZE. TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- 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). = Ing. Christian Trassens Senior DBA Unix Administrator Phone: 0034-699240979 0034-649824704 __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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: DBMS_STATS in 8.1.6
DBMS_STATS only gathers stats that the optimizer needs. For example, the CHAIN_CNT field is not updated because the optimizer doesn't need it. I hate this because now I don't know if the table has chained or migrated rows. The ANALYZE command still gives you this information. -Original Message- Sent: Tuesday, February 26, 2002 4:33 AM To: Multiple recipients of list ORACLE-L Well, it has some bugs with GATHER EMPTY and GATHER STALE on your release. However, I set it in some clients and it is working fine. As an advice, don't use those options, reduce the size of the bucket just to generate statistics quickly, lastly if you decide against DBMS_STATS, don't run statistics with analyze because it doesn't generate any. I set it with monitoring option and as I said it is working fine. I was cautious just to exclude tables that recently been truncated. However, it is working fine. Regards. --- Jesse, Rich [EMAIL PROTECTED] wrote: Hey all, I'm looking to start CBO on an 8.1.6.0.0 DB on Solaris 2.8. But looking thru Metaclink, I see some potential problems with some of the DBMS_STATS package in this version, like with GATHER_SCHEMA_STATS. Anyone have any suggestions as to Yay or Nay for this on 8.1.6.0.0? Unfortunately, this is another 3rd party app which refuses to support any other version (not sure about patchsets, though) of Oracle, so I'm stuck here for the time being. I'm leaning heavily towards the cautious route of using ANALYZE and reading DBA_TAB_MODIFICATIONS once a week to see if I should re-ANALYZE. TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- 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). = Ing. Christian Trassens Senior DBA Unix Administrator Phone: 0034-699240979 0034-649824704 __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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: Terrian, Tom 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: DBMS_STATS in 8.1.6
Thanks for the advice, Christian, but I'm a little confused. What do you mean by don't run statistics with analyze because it doesn't generate any? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Tuesday, February 26, 2002 3:33 AM To: Multiple recipients of list ORACLE-L Well, it has some bugs with GATHER EMPTY and GATHER STALE on your release. However, I set it in some clients and it is working fine. As an advice, don't use those options, reduce the size of the bucket just to generate statistics quickly, lastly if you decide against DBMS_STATS, don't run statistics with analyze because it doesn't generate any. I set it with monitoring option and as I said it is working fine. I was cautious just to exclude tables that recently been truncated. However, it is working fine. Regards. --- Jesse, Rich [EMAIL PROTECTED] wrote: Hey all, I'm looking to start CBO on an 8.1.6.0.0 DB on Solaris 2.8. But looking thru Metaclink, I see some potential problems with some of the DBMS_STATS package in this version, like with GATHER_SCHEMA_STATS. Anyone have any suggestions as to Yay or Nay for this on 8.1.6.0.0? Unfortunately, this is another 3rd party app which refuses to support any other version (not sure about patchsets, though) of Oracle, so I'm stuck here for the time being. I'm leaning heavily towards the cautious route of using ANALYZE and reading DBA_TAB_MODIFICATIONS once a week to see if I should re-ANALYZE. TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- 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: DBMS_STATS in 8.1.6
Sorry, I haven't explained myself correctly. When you have first run a dbms_stats to generate on partitioned tables, analyze doesn't generate any. However, this applies for compute statistics. I think this article could explain you better than I: Doc ID: Note:97207.1 Type: PROBLEM Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 24-JAN-2000 Last Revision Date: 28-MAR-2001 Problem Description --- You have run ANALYZE TABLE COMPUTE STATISTICS on a partitioned table and the statistics in dba_tables have not been replaced. Solution Description You ran the dbms_stats package to get statistics on the partitioned table with granularity = ALL. By running the dbms_stats package on the table the global_stats column in dba_tables is set to YES. You need to do the following to delete the stats generated by the dbms_stats package: execute dbms_stats.delete_table_stats(ownname = 'SCOTT', tabname = 'ORDERS', cascade_parts = TRUE); Once you have done that you can run the ANALYZE TABLE and the statistics will populate dba_tables. Explanation --- When analyzing a partitioned table with dbms_stats package, the global_stats flag is set. When it is set in this case you need to use the dbms_stats package to delete the statistics before the ANALYZE command can be used on the table. References -- [BUG:908138] Additional Search Words --- Regards. --- Jesse, Rich [EMAIL PROTECTED] wrote: Thanks for the advice, Christian, but I'm a little confused. What do you mean by don't run statistics with analyze because it doesn't generate any? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Tuesday, February 26, 2002 3:33 AM To: Multiple recipients of list ORACLE-L Well, it has some bugs with GATHER EMPTY and GATHER STALE on your release. However, I set it in some clients and it is working fine. As an advice, don't use those options, reduce the size of the bucket just to generate statistics quickly, lastly if you decide against DBMS_STATS, don't run statistics with analyze because it doesn't generate any. I set it with monitoring option and as I said it is working fine. I was cautious just to exclude tables that recently been truncated. However, it is working fine. Regards. --- Jesse, Rich [EMAIL PROTECTED] wrote: Hey all, I'm looking to start CBO on an 8.1.6.0.0 DB on Solaris 2.8. But looking thru Metaclink, I see some potential problems with some of the DBMS_STATS package in this version, like with GATHER_SCHEMA_STATS. Anyone have any suggestions as to Yay or Nay for this on 8.1.6.0.0? Unfortunately, this is another 3rd party app which refuses to support any other version (not sure about patchsets, though) of Oracle, so I'm stuck here for the time being. I'm leaning heavily towards the cautious route of using ANALYZE and reading DBA_TAB_MODIFICATIONS once a week to see if I should re-ANALYZE. TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- 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). = Ing. Christian Trassens Senior DBA Unix Administrator Phone: 0034-699240979 0034-649824704 __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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: DBMS_STATS in 8.1.6
Does this apply in 8.1.7. I couldn't tell from the doco. Kathy -Original Message- Sent: Tuesday, February 26, 2002 9:28 AM To: Multiple recipients of list ORACLE-L Sorry, I haven't explained myself correctly. When you have first run a dbms_stats to generate on partitioned tables, analyze doesn't generate any. However, this applies for compute statistics. I think this article could explain you better than I: Doc ID: Note:97207.1 Type: PROBLEM Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 24-JAN-2000 Last Revision Date: 28-MAR-2001 Problem Description --- You have run ANALYZE TABLE COMPUTE STATISTICS on a partitioned table and the statistics in dba_tables have not been replaced. Solution Description You ran the dbms_stats package to get statistics on the partitioned table with granularity = ALL. By running the dbms_stats package on the table the global_stats column in dba_tables is set to YES. You need to do the following to delete the stats generated by the dbms_stats package: execute dbms_stats.delete_table_stats(ownname = 'SCOTT', tabname = 'ORDERS', cascade_parts = TRUE); Once you have done that you can run the ANALYZE TABLE and the statistics will populate dba_tables. Explanation --- When analyzing a partitioned table with dbms_stats package, the global_stats flag is set. When it is set in this case you need to use the dbms_stats package to delete the statistics before the ANALYZE command can be used on the table. References -- [BUG:908138] Additional Search Words --- Regards. --- Jesse, Rich [EMAIL PROTECTED] wrote: Thanks for the advice, Christian, but I'm a little confused. What do you mean by don't run statistics with analyze because it doesn't generate any? Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- Sent: Tuesday, February 26, 2002 3:33 AM To: Multiple recipients of list ORACLE-L Well, it has some bugs with GATHER EMPTY and GATHER STALE on your release. However, I set it in some clients and it is working fine. As an advice, don't use those options, reduce the size of the bucket just to generate statistics quickly, lastly if you decide against DBMS_STATS, don't run statistics with analyze because it doesn't generate any. I set it with monitoring option and as I said it is working fine. I was cautious just to exclude tables that recently been truncated. However, it is working fine. Regards. --- Jesse, Rich [EMAIL PROTECTED] wrote: Hey all, I'm looking to start CBO on an 8.1.6.0.0 DB on Solaris 2.8. But looking thru Metaclink, I see some potential problems with some of the DBMS_STATS package in this version, like with GATHER_SCHEMA_STATS. Anyone have any suggestions as to Yay or Nay for this on 8.1.6.0.0? Unfortunately, this is another 3rd party app which refuses to support any other version (not sure about patchsets, though) of Oracle, so I'm stuck here for the time being. I'm leaning heavily towards the cautious route of using ANALYZE and reading DBA_TAB_MODIFICATIONS once a week to see if I should re-ANALYZE. TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- 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). = Ing. Christian Trassens Senior DBA Unix Administrator Phone: 0034-699240979 0034-649824704 __ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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
Re: DBMS_STATS
Exactly: bug 1192012. And this consists on skiping the table or segment when those options are used. Therefore it won't recalculate the statistics. Regards. --- Anand Prakash [EMAIL PROTECTED] wrote: What kind of problem? [EMAIL PROTECTED] 07/11/01 12:30AM I don't know from Steve. However I can tell from my experience that the options GATHER STALE and GATHER EMPTY have problems. Regards. --- Anand Prakash [EMAIL PROTECTED] wrote: Steve You mentioned about dbms_stats having some teething problem on version 8.1.6. Can you provide some more details? I was planning to implement on version 8.1.6.2 on Compaq Tru64 unix 5.1. Thanks. Anand Prakash -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anand Prakash 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). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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: Anand Prakash 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). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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: DBMS_STATS
What kind of problem? [EMAIL PROTECTED] 07/11/01 12:30AM I don't know from Steve. However I can tell from my experience that the options GATHER STALE and GATHER EMPTY have problems. Regards. --- Anand Prakash [EMAIL PROTECTED] wrote: Steve You mentioned about dbms_stats having some teething problem on version 8.1.6. Can you provide some more details? I was planning to implement on version 8.1.6.2 on Compaq Tru64 unix 5.1. Thanks. Anand Prakash -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anand Prakash 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). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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: Anand Prakash 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: DBMS_STATS
I don't know from Steve. However I can tell from my experience that the options GATHER STALE and GATHER EMPTY have problems. Regards. --- Anand Prakash [EMAIL PROTECTED] wrote: Steve You mentioned about dbms_stats having some teething problem on version 8.1.6. Can you provide some more details? I was planning to implement on version 8.1.6.2 on Compaq Tru64 unix 5.1. Thanks. Anand Prakash -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Anand Prakash 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). = Eng. Christian Trassens Senior DBA Systems Engineer [EMAIL PROTECTED] [EMAIL PROTECTED] Phone : 541149816062 __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christian Trassens 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: dbms_stats
Well, that is what I said. Try not to use dbms_stats with the parameter GATHER STALE o GATHER EMPTY. With GATHER I haven't had any problem yet. And the monitoring feature is runing well. Therefore, if you are not sure of dbms_stats, use monitoring but with analyze or dbms_utility or dbms_ddl. Regards. -Mensaje original- De: paquette stephane [SMTP:[EMAIL PROTECTED]] Enviado el: mircoles 28 de febrero de 2001 11:01 Para: Multiple recipients of list ORACLE-L Asunto: Re: dbms_stats I've test it with Oracle 815 and there were several bugs with it. I do not remember if the bugs were fix in 816 or in 817. You'd better check on Metalink. --- Lord David [EMAIL PROTECTED] a crit : Hi list Does anyone have any tales to tell of using MONITORING and the 'GATHER STALE' option of the dbms_stats.gather* procedures? Someone here has suggested that we use it for a new product we're developing, but I am a little concerned over whether the monitoring has any significant performance impact. TIA David Lord Senior DBA, Hays Commercial Services, IT Solutions [EMAIL PROTECTED] ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord David 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). = Stephane Paquette DBA Oracle [EMAIL PROTECTED] __ 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: =?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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Trassens, Christian 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).
