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 takesto
run, and also that sample_size column is always equal to
num_rows.
Would
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_rows.
Would like to hear from anyone who
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
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
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
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,
The proc generates the 'exec dbms_stats ' statements for all the users. Are yousaving theoutput 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 use the proc
: 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.
Jose Luis Delgado [EMAIL PROTECTED] wrote:List...
SunOS 5.8
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=
' 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
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
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
')
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
(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
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
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
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:
, 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
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 1',20,'DEFAULT
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
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
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
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
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
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
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
, 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
.
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
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
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
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
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
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
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
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
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 fun! ;0)
Mark
===
Mark Leith | T: +44 (0)1905 330
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
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
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
(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 AM
To: Multiple recipients of list ORACLE-L
Sorry JP, I should have clarified that DBMS_STATS is the way
errors
-Original Message-
Sent: Tuesday, June 03, 2003 4:55 AM
To: Multiple recipients of list ORACLE-L
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
DBMS_STATS is quicker because it can run in parallel mode. Analyze cannot.
-Original Message-
Sent: Tuesday, June 03, 2003 11:25 AM
To: Multiple recipients of list ORACLE-L
Sorry JP, I should have clarified that DBMS_STATS is the way to go, when on
a 9i or above
Title: RE: Schedule Analyze using DBMS_STATS ???
We don't analyze schema, we use dbms_stats ...
I 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 so that all
Title: RE: Schedule Analyze using DBMS_STATS ???
The sample output looks like ...
[EMAIL PROTECTED] sys
SQL*Plus: Release 9.2.0.2.0 - Production on Tue Jun 3 11:57:51 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
11:57
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
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
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
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
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
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
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
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
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 and out of our control
='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
',
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
: 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
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
://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
,
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
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_stats.
We
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_stats.
We have ample RBS. Is there any significant undo generated by dbms_stats or RMAN
which
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-
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
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 but we've seen
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
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
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
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
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
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
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
===
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
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
,
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
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
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
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
Title: Message
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
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
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
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
I think since DBMS_STATS also gathers histograms its taking more time
Babu
Tim Gorman
://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
1 - 100 of 165 matches
Mail list logo
