te: > To all 9i DBA's.
>
> I am trying to find out how efficient (or not ) is
> the option of running
> dbms_stats with dbms_stats.auto_sample_size.
>
> Reading metalink I see a lot of issues with the time
> it takes to run,
> and also that sample_size column is
Title: Message
To all 9i DBA's.
I am
trying to find out how efficient (or not ) is the option of running dbms_stats
with dbms_stats.auto_sample_size.
Reading metalink I see a lot of issues with the time it takes to
run, and also that sample_size column is always equal to
num
Oooppp...
Sorry... I sent the testing proc... this is the
original...
procedure get_statistics as
cursor get_users_list is
select username
from dba_users
where username != 'SYS' and username != 'SYSTEM';
begin
for i in get_users_list
loop
dbms_stats.gather_schema_stats(ownname=>
c
PROTECTED]>
> Subject: Re: Strange behavior with dbms_stats...
>
> The proc generates the 'exec dbms_stats ' statements for all the users. Are you
> saving the output and running it manually or not. IT would have the same statements
> that you run one by one.
>
The proc generates the 'exec dbms_stats ' statements for all the users. Are you saving the output and running it manually or not. IT would have the same statements that you run one by one. Jose Luis Delgado <[EMAIL PROTECTED]> wrote:
List...SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too).I
Um...the PL/SQL script at the bottom of your e-mail doesn't
DO anything other than output a line. Do you use this
in a subsequent command file?
Cheers,
Mike
-Original Message-
Sent: Thursday, December 11, 2003 8:09 AM
To: Multiple recipients of list ORACLE-L
List...
SunOS 5.8, Oracle
after running the block, do you actually spool the output and run that??
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having
List...
SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too).
I use the proc at the bottom to generate statistics.
It seems to work, but if I check statistics with:
select owner, table_name, num_rows, blocks,
av_row_len,
to_char(last_analyzed, 'MM/DD/ HH24:MI:SS')
from dba_tables;
the tables have the O
List...
SunOS 5.8, Oracle 8.1.6 (and 8.1.7 too).
I use this proc to generate statistics:
create or replace procedure get_statistics as
cursor get_users_list is
select username
from dba_users
where username != 'SYS'
and username != 'SYSTEM';
begin
for i in get_user
>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 **AGAI
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 statis
th '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
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
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 histogr
#x27;'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 t
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
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 iterat
We are having issues running dbms_stats.gather_table_stats. We get and ORA-3113 error message . The following can be found in the ALERT file:
Errors in file /oracle/admin/VLDB/udump/vldbn1_ora_21486.trc:ORA-07445: exception encountered: core dump [000100E81374] [SIGSEGV] [Address not mapped to
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-
>>> Fro
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: Mult
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: DBM
> 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: Multip
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
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-
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
x27;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:
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
> OR
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
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 we
(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 i
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
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
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
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 CO
Sent by: cc:
[EMAIL PROTECTED]Subject: RE: DBMS_STATS and CBO
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
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
>
> wh
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 ?
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=>'GAT
"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
]
-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
exec
IL 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 interactive
; 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:
llas, 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(
#x27;);
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 inte
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
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',estimat
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
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
There are a Lot of options to DBMS_STATS .. And be Judicious in using
the same. Read the docs before attempting it.
BTW .. What is the session waiting for .. Just Check v$session_wait.
HTH
Best Regards,
Ganesh R
DID : +65-6215-8413
HP : +65-9067-8474
Thanks a lot Goulet,Thomas,Raj,Stephen and Michael.
Hope i have not missed any one of u :)
Thanks once again.
Jp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Prem Khanna J
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcit
Hi all,
Everyone is recommending DBMS_STATS to be used for computing the
statistics.
But even after specifying the parallel option , DBMS_STATS is taking lots of time !
in comparison to Analyze...
In case of tables with a million records the query just hangs when i use the
With some versions, dbms_stats has issues with partitions. Since we use
partitioning, we wrote a script to simulate the gather stale
functionality. Monitoring is enabled and we do a compute of any table
or partition thereof that has more than x% modified. This is
essentially what the gather
Title: RE: Schedule Analyze using DBMS_STATS ???
(B
(B
(B
(B
(BThe sample output looks like ...
(B
(B
(B[EMAIL PROTECTED]> sys
(BSQL*Plus: Release 9.2.0.2.0 - Production on Tue Jun 3 11:57:51 2003
(BCopyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
(BConnected.
(
Title: RE: Schedule Analyze using DBMS_STATS ???
(B
(B
(B
(B
(BWe don't "analyze" schema, we use dbms_stats ...
(B
(B
(BI wrote a package that analyzes everything using dbms_stats and (for my instance) it runs in 10 parallel streams. It splits all the tables in 10 streams
DBMS_STATS is quicker because it can run in parallel mode. Analyze cannot.
(B
(B-Original Message-
(BSent: Tuesday, June 03, 2003 11:25 AM
(BTo: Multiple recipients of list ORACLE-L
(B
(B
(BSorry JP, I should have clarified that DBMS_STATS is the way to go, when on
(Ba 9i or above
);
UTL_FILE.put_line( v_fh, 'ERROR : ' || v_error_message );
UTL_FILE.fflush( v_fh );
UTL_FILE.fclose( v_fh );
DBMS_OUTPUT.put_line( 'Exception Exit' );
DBMS_OUTPUT.put_line( 'ERROR! (' || v_error_code || ')' );
DBMS_OUT
egin
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: Tuesday, June 03, 2003 11:25
03jun0304:00','DDMONYYHH24:MI'),
(B'trunc(sysdate)+(1+(4/24))');
(B END;
(B
(B===
(B
(BThe above will run a COMPUTE analyze on all schemas, except SYS and SYSTEM,
(Bat 4:00am every day. Modify it to your own needs, but it
Sorry JP, I should have clarified that DBMS_STATS is the way to go, when on
a 9i or above release. One of the simple factors being that ANALYZE is being
deprecated.. There seemed to be a number of bugs/quirks, whatever you want
to call them, in certain 8.1.x versions, which are now fixed within 9i
Thanx a lot Mark.
Sure , your code has given me a starting point.
let me change accordingly to use DBMS_STATSe and give a try :-)
So,can i conclude that DBMS_STATS is better than ANALYZE ?!
Someone over the list mentioned that DBMS_STATS is slower.
is it so ? What is your opinion regarding this
;
END;
===
The above will run a COMPUTE analyze on all schemas, except SYS and SYSTEM,
at 4:00am every day. Modify it to your own needs, but it should give you a
starting point..
I would also recommend using DBMS_STATS to generate your statistics.
Have f
Guys,
I would like to scedule the process of analyzing tables/indexes
using DBMS_STATS ?
Hope someone of u would have a script for the same.
can u share with me please ?!
BTW,Which is advisable : ANALYZE or DBMS_STATS ?
there was a discussion about the same on the list also.
but not found any
When one runs dbms_stats how often is the data committed? Before Oracle exports a
table it needs to gather information about it such as the columns comprising it. I
wonder if this information also includes data changed by dbms_stats and/or RMAN.
We used to have similar problems when exporting
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
3: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,
inse
Hi Kirti,
Sounds like you have the same suspicions as do we. So far as we know there isn't any
heavy duty DML before the export and there isn't any other activity on the schema
other than RMAN and DBMS_STATS. We don't understand how RMAN or DBMS_STATS ***could***
be the culprit
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
t
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:
ents 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 es
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
fr
; and DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on the same schema being
> backed up
> via the user level export. There was no other end user access to the schema data.
> Since exp got
> the error I assume it was reading from the rollback segments but why? I'm
backup and
DBMS_STATS.GATHER_TABLE_STATS(...) which was being run on the same
schema being backed up via the user level export. There was no other end
user access to the schema data. Since exp got the error I assume it was
reading from the rollback segments but why? I'm suspecting dbms_stat
tats(
ownname=>'&1',
options=>'GATHER STALE',
cascade=>TRUE,
degree=>8,
granularity=>'ALL',
method_opt=>'FOR ALL INDEXED COLUMNS SIZE 1'
);
end;
/
Thanks,
Darrell
>>> [EMAIL PRO
ifting 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
>
HELP...
Has anyone encountered rollback problems while running dbms_stats?
-Original Message-
Sent: Thursday, May 29, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L
Oh yeah, for the export consistent=N
-Original Message-
Sent: Thursday, May 29, 2003 9:14 AM
To: '[
[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
wnname=>'&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:2
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/
user level export. There was no other end user
access to the schema data. Since exp got the error I assume it was reading from the
rollback segments but why? I'm suspecting dbms_stats. We have ample RBS. Is there any
significant undo generated by dbms_stats or RMAN which could create this pr
t why? I'm suspecting dbms_stats. We have ample RBS. Is there any
significant undo generated by dbms_stats or RMAN which could create this problem?
(Of course we need to improve our job scheduling but that's another issue, the timing
of the user level export is application driven an
n run dbms_stats for
those only. That way you have a bit more flexibility (you can for example
exclude some tables), make estimate or calculate decisions on the table
basis, etc.
Djordje
-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]Sent: Wednesday, May 28,
2003
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
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
Koivu, Lisa 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
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
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'
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
#x27;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 gat
ts 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 Metalin
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
> 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, a
:[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
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
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 defau
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.oracl
? 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 Host
inal 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 o
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
os 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
1 - 100 of 175 matches
Mail list logo
