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
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
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
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
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.
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
(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
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
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
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:
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
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
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
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
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
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
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:
recipients of list
ORACLE-L [EMAIL PROTECTED]
Sent by: cc:
[EMAIL PROTECTED]Subject: 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
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
:
[EMAIL PROTECTED]Subject: RE: DBMS_STATS and CBO
.com
@
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
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
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
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
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
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,
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
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
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
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
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
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
-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
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
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
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
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
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
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
: 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
://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
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
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
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
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-
-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
-
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
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
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
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
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
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
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.
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
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
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
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
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
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
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
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
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,
:
Sent by: Subject: Re: DBMS_STATS
[EMAIL PROTECTED
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,
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
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
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
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:
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.
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
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
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
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
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
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:
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,
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.
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
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
.
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
81 matches
Mail list logo
