Not that he needs it, but I can confirm Jonathan's claim that the
method_opt clause you are using does not collect column statistics:
SQL> @delete_table_stats tp1
PL/SQL procedure successfully completed.
SQL> @tblstats tp1
without effect on my testcase
Thnx,
Jeroen
-Oorspronkelijk bericht-
Van: Jonathan Lewis [mailto:[EMAIL PROTECTED]
Verzonden: Thursday, December 11, 2003 10:49
Aan: Multiple recipients of list ORACLE-L
Onderwerp: Re: analyze problems
Take a look at user_tab_columns -
your choice of
Take a look at user_tab_columns -
your choice of:
method_opt => 'for columns'
On a quick test on 9.2.0.3 this seems to
leave the columns with null statistics.
(check
select column_name, num_distinct
from user_tab_columns
)
In passing, computing histograms with 75
buckets for all in
Has you db_file_multiblock_read_count changed due
upgrade?
What are your optimizer_index_cost_adj and
optimizer_index_caching parameter values?
If they're default you should perhaps change them
according to Tim Gorman's CBO article (or in 9i, gather system stats
instead)
Tanel.
-
Title: RE: analyze after truncate
I once took over an old database which hadn't been analyzed in nearly a year, so I started analyzing it every two weeks. For the most part things stayed the same or got faster; however, one procedure would sometimes, only sometimes, take a lot longer.
You are truncating a table so as to add another 0.7% of its
size in inserts, and to update 1.4% of the rows?
My first thought would be to stop doing that.
Modify the extent sizes (if needed) so that your 50k rows
fairly fill up the extents allocated on the insert ( so as
to avoid wasting space
Presumably, after you truncate the huge table, it's no longer a huge table.
DBMS_STATS will run much faster after you truncate the table. You've just
discovered a great optimization.
That's exactly what I've been saying for a long time: my database would
work extremely fast, if there weren't any us
> -Original Message-
> quriyat
>
> Can anyone enlighten me on the diff between
> ANALYZE TABLE COMPUTE STATISTICS
> --AND--
> analyze table my_emp compute statistics for table for all indexes
> for all indexed columns;
Time to dust off the old "check analyze options" SQL!
These
There is plenty of documentation on these commands which I suggest reading,
but to summarise obscenely...
These options can be used to analyze just certain elements whilst leaving
other statistics unchanged. If you rebuilt the indexes on a table you
might want to simply "analyze table blah for a
Prem - "It depends". The first issue is why are you analyzing them? Some
(Bpossible reasons:
(B1. Because the table has changed enough that the CBO could use fresh
(Bstatistics? Then it depends on how volatile your data is. One danger is that
(Bat some point the fresh statistics may change the
Andrey,
What does V$SESSION_WAIT values for the session that performs the ANALYZE
say? Another though: Do you have Parallel DEGREE set on that table?
John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002
What would you see if you were allowed to look back at your life at the end
As well as the structure of the table... What (or how many) indexes are on
the table? Have you tried just analyzing the indexes, or perhaps just
table data?
Also, since I don't have a convenient environment to test this out... Can
someone please confirm what happens to a global index when only
Title: RE: analyze table takes forever
You
mentioned that count(*) took 10 minutes but did not say how many rows are
present in that partition; if number of rows is less than 1 million, I would do
the following.
alter
table . move partition
tablespace nologging;
and
then re
Title: RE: analyze table takes forever
what's the structure of the table?
And try taking an export of this partition and import it into it's own table in a development box... see if you can analyze it there.
Nick
-Original Message-
From: Andrey Bronfin [mai
Thanks all for your advice. Will check an option to do estimate.
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Wednesday, July 24, 2002 7:23 PM
> At the last Open World I attended a couple of sessions
> where the general adv
At the last Open World I attended a couple of sessions
where the general advice for 9i DB is to use ANALYZE
ESTIMATE without specifying ANY value. A few brief
"comparision" tests did show that it got better results
than the alternatives tested.
As always, YMMV & HTH
HAND!
--
Please see the offi
t;
>
> >
> > Please correct me if my assumption is wrong, we had strange behaviour
here
> > when SYS objects were analyzed on a development db.
> >
> > Regards,
> > Patrice Boivin
> > Systems Analyst (Oracle Certified DBA)
> >
> > System
technologiques
> > Informatics Branch | Direction de l'informatique
> > Maritimes Region, DFO | Région des Maritimes, MPO
> >
> > E-Mail: [EMAIL PROTECTED]
> >
> >
> > -Original Message-
> > Sent: Wednesday, July 24, 2002 9:09 A
ge-
> Sent: Wednesday, July 24, 2002 10:55 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: ANALYZE question
>
> This message uses a character set that is not supported by the Internet
> Service. To view the original message content, open the attached messa
Maritimes, MPO
> >
> > E-Mail: [EMAIL PROTECTED]
> >
> >
> > -Original Message-
> > Sent: Wednesday, July 24, 2002 9:09 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: ANALYZE question
> >
> > DBMS_STATS can be used to
ormatique
Maritimes Region, DFO | Région des Maritimes, MPO
E-Mail: [EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, July 24, 2002 10:55 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: ANALYZE question
This message uses a character set that is not support
> Sent: Wednesday, July 24, 2002 9:09 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: ANALYZE question
>
> DBMS_STATS can be used to analyze tables.
>
> Dave
>
> -Original Message-
> Sent: Wednesday, July 24, 2002 4:03 AM
> To: Multiple
O
E-Mail: [EMAIL PROTECTED]
-Original Message-
Sent: Wednesday, July 24, 2002 9:09 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: ANALYZE question
DBMS_STATS can be used to analyze tables.
Dave
-Original Message-
Sent: Wednesday, July 24, 2002 4:03 AM
To:
DBMS_STATS can be used to analyze tables.
Dave
-Original Message-
Sent: Wednesday, July 24, 2002 4:03 AM
To: Multiple recipients of list ORACLE-L
Apart from explicity running an ANALYZE command against a table, what, if
any, other events/actions can cause an analyze to be run on the ta
No Impact. Analyze would just collect the latest statistics for the
concerned table and the next time any query gets fired on this table the
optimizer (CBO) would generate the execution plan based on these statistics.
I also believe that this would be healthier sign as the CBO is generating
plan
dbms_stats
--- "O'Neill, Sean" <[EMAIL PROTECTED]> wrote:
> Apart from explicity running an ANALYZE command against a table,
> what, if
> any, other events/actions can cause an analyze to be run on the
> table?
>
> -
> Seán O' Neill
> Organon (Ireland) Ltd.
> [subscribed
An export has default parameter of STATISTICS=ESTIMATE.
If such an exported file is imported , the default import
parameter ANALYZE=Y will result in the import utility
executing the analyze stmts in dump file.
-Original Message-
Sean
Sent: Wednesday, July 24, 2002 2:33 PM
To: Multiple r
As John says, you can check on what Oracle is waiting
on .
It may not solve your problem, but can make the
analyze faster. Since your fact tables are partitioned
and since you're migrating from 8.0.4 that means range
partitioning. If the tables are partioned by date and
you do you allow update/in
ilvey
ahoo.com>cc:
Sent by: Subject: RE: Analyze running twice as
long after upgrade to 8.1.7.2
[EMAIL P
ect: RE: Analyze running twice as
long after upgrade to 8.1.7.2
[EMAIL PROTECTED]
cc:
[EMAIL PROTECTED] Subject: RE: Analyze running twice
as long after up
Just a thought, since analyze does sorting, might be a
change in sort_area_size. Did you change this param
when you upgraded? Try it with a large sort_area and
see what happens.
jack silvey
--- [EMAIL PROTECTED] wrote:
> I suggest running in parallel. Also, is the table
> partitioned?
>
> -
Cherie,
> Any other ideas? I'd like to be able to give our
> application owners some
> explanation.
If you have access to the ANALYZE scripts, could you modify it to obtain
_what_ this session is waiting for and how much (from V$SESSION_EVENT) as
well as look at V$MYSTAT to determine the volu
Title: RE: Analyze running twice as long after upgrade to 8.1.7.2
I suggest running in parallel. Also, is the table partitioned?
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, June 10, 2002 4:39 PM
To: Multiple recipients of list ORACLE-L
Thanks for the reply.
>>> [EMAIL PROTECTED] 01/10/02 11:11AM >>>
Jay,
I have switched to DBMS_STATS for quite some now. Most databases are
8.1.7.x.
I have not had any problems running DBMS_STATS. Or from the generated
stats.
- Kirti
-Original Message-
Sent: Thursday, January 10, 20
Jay,
I have switched to DBMS_STATS for quite some now. Most databases are
8.1.7.x.
I have not had any problems running DBMS_STATS. Or from the generated
stats.
- Kirti
-Original Message-
Sent: Thursday, January 10, 2002 9:31 AM
To: Multiple recipients of list ORACLE-L
Now that I f
I would highly recommend that you move to DBMS_STATS if you are running at
least 8.1.6.3 -- especially if you are estimating statistics.
The reason is that DBMS_STATS generates more and better statistics than
analyze. I've seen queries go from hours to minutes just by changing from
ANALYZE to DB
Ruth,
Pl check out dbms_stats package and gather_table_stats procedure in
particular..
Here is an example from my scripts..
execute dbms_stats.gather_table_stats ('DBM','CAMPAIGNS',NULL, NULL,
FALSE,'FOR ALL INDEXED COLUMNS SIZE 150',NULL,'ALL',TRUE,NULL,NULL, NULL);
HTH,
- Kirti
-Origi
Title: RE: Analyze only latest partition
Hi
Why not you can try with partition related dictionary views:
Here is my query:
[EMAIL PROTECTED]> select table_name, partition_name, high_value,
partition_position from dba_tab_partitions a where partition_position =
(select
Hi Connor
Thanq for responding.
In dba_tab_partitions there is no such column like partition_id, and I
would like to generate a list at schema level for each table.
That means, this script should capture the latest (last) partitions for
each table (if it is partitioned) for a given schema.
Is
declare
pname varchar2(100);
begin
select partition_name
into pname
from user_tab_partitions
where table_name = ...
and partition_id =
( select max(partition_id)
from user_tab_partitions
where table_name = ... );
execute immediate 'analyze table ... partition
('||p
It may not be OPS, butis it installed?
is your dbms clustered, or was it?
I have seen it in " stand alone " databases which
used to be OPS-aware and the other instance is
down.
but..older versions of oracle.certainly not cRAC,
or whatever.
hth
- ross
-Original Message-
Sent
cc:
Sent by: Subject: RE: analyze partitioned
indexes
[EMAIL PROTEC
Title: RE: analyze partitioned indexes
>-Original Message-
>From: Daiminger, Helmut [mailto:[EMAIL PROTECTED]]
>
>I want to write a procedure that analyzes all my indexes. But I'm not sure whether my source code will also analyze partitioned indexes.
>...
Helmut, have you tried your code, that would be the best way to see if
it works(hint: partitioned indexes work pretty much like normal indexes,
in that you can analyze the table and the indexes will be analyzed also,
including partitioned tables/indexes).
joe
> "Daiminger, Helmut" wrote:
>
> Hi
I think your id is default to use SYSTEM tablespace as temporary tablespace.
Bing
-Original Message-
Sent: Monday, September 24, 2001 10:29 AM
To: Multiple recipients of list ORACLE-L
what is the temp tablespace for the account you are using to do the
analyze? For that matter, what is
what is the temp tablespace for the account you are using to do the
analyze? For that matter, what is the temp tablespace on all user ids
in your database.
This error sounds like you have the temp tablespace for someone set to
be SYSTEM and that's why it failed.
Rachel
--- Deen Dayal <[EMAIL P
Thanks for all the messages. I was executing it connecting as internal and SYS has
system as temporary TS. Rest of the users have
TEmporary_data as temp TS. I know its stupid to have SYS pointing to SYSTEM for
temporary ts. I changed it, hop it will work this
time.
Thanks
Deen
-Original Me
It needs temp space to write the changes. When it is done, the space
becomes permanent. Try making you system tablespace bigger.
Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, September 24, 2001 11:45 AM
> I am not analyzing SYS
I am not analyzing SYSTEM Schema. I thought analyze updates stats in Data Dictionery.
Only 12MB of 160MB was occupied in System TS.
I had System TS in Auto Extend mode upto 200MB, but it did not extend. So I am
confused whether really it needs more space in System
TS
Thanks
Deen
-Original
Are you trying to analyze the system schema? If you are, don't. It can
cause problems with all kinds of things. If you are not trying to analyze
the system schema then just make your system tablespace bigger. You can
either increase the size of existing datafile(s) or add a datafile.
HTH,
Ruth
It means the user that was executing the
dbms_utility.analyze_schema has as temporary
tablespace the SYSTEM one. Probably you did it with
system or sys and since you had created the database
you had never changed the temporary tablespaces of
those users. Issue an alter user temporary
tablespace
Iain,
This is covered in greater detail in the SQL Reference
manual under the Analyze command, but here's a summary
of the basics.
Computing/estimating statistics on the table (without
a FOR clause) calculates/estimates stats for the table
and its indexes.
The for clause has the following optio
r largest table has 27 million
rows and many others are in the 2 to 10 million row range. Also, our DB is
very dynamic, with LOTS of inserts and updates each day, so we need to
re-analyze daily.
'COMPUTE' looks at all the rows in all the tables and all the rows in all
their indexes - th
Really? On our approx. 200GB production DB (8.1.6 on Win2k)
DBMS_Utility.Analyze_Schema takes about an hour each morning (estimate,
sample 5%). I tried DBMS_Stats.Gather_Schema_Stats with 5% sampling a few
weeks ago and finally killed the thing after almost 3 hours and, if I
remember correctly,
Here is the syntax I am using:
execute dbms_utility.analyze_schema('PROD','COMPUTE');
Is there anything that might have made the analyze run so long? By the way,
we are running 7.3.4.
Ron
-Original Message-
Sent: Monday, July 16, 2001 5:32 PM
To: Multiple recipients of list ORACLE-L
Why not use DBMS_STATS? It is faster.
"Walking on water and developing software from a specification are easy if
both are frozen."
Christopher R. Spence
Oracle DBA
Fuelspot
-Original Message-
Sent: Monday, July 16, 2001 9:46 AM
To: Multiple recipients of list ORACLE-L
I have an An
It seems you use 'compute' option of analyze command.
Try to use 'estimate' with about 45% sample of data and
then check whether the plans are still unchanged.
This solution may decrease the time you spend on analyzing
but usually keeps the precision of statistics.
Regards,
Ed
>
>
> I have
On 8i, use DBMS_STATS that you can do it in parallel
except for the indexes. You need to put as argument a
degree>1. Also beware of the method_opt. Because the
default is 'FOR ALL COLUMNS SIZE 1'.
Regards.
--- "Smith, Ron L." <[EMAIL PROTECTED]> wrote:
> I have an Analyze Schema running once a
Yes, the library cache object is locked so it is not dropped during an
analyze.
"Walking on water and developing software from a specification are easy if
both are frozen."
Christopher R. Spence
Oracle DBA
Fuelspot
-Original Message-
Sent: Thursday, May 31, 2001 5:25 AM
To: Multiple
It is a forum post I believe, I think I did a search on "analyze lock". I
have seen numerous articles were Oracle claims locking during analyze, that
is the only one I found with quick parusal. Like many other things, Oracle
is to blame on this old wives tale.
"Walking on water and developing s
Thought of trying this out...
I analyzed a huge table in our system and simultaneously checked for
locks...
Oracle did not lock the concerned table being analysed, but aquired locks on
sys tables for sometime, both in estimate and compute options.
On oracle 8.1.7.
rgds
amar
-Original Messa
Christopher,
Is this document perhaps a TAR as I cannot find it on Metalink (but maybe
the search engine doesn't like me today).
Do you have the exact URL for this note 213220.999?
Thanks,
Bruce
-Original Message-
Sent: Thursday, 31 May 2001 6:59
To: Multiple recipients of list ORACLE
On May 30, 2001 03:21 pm, Jeremiah Wilton wrote:
> I have used compute statistics since version 7.3.2 up through
> 8.1.6, and never had it hold a table lock for the duration of the
> analyze. I don't know where people are getting the idea that
> compute statistics holds a lock and estimate doesn'
It could be a bug with my version... I don't think it actually
keeps a lock but it wants to be able to get the lock. For the most
part we don't get our analyzes in due to this. Fortunately for me,
we no longer want to and have deleted the stats.
-Original Message-
Sent: Wednesday, May
Sorry but it is true. I have a 7.3.3 database and if it can't acquire a
table lock it cannot do the compute.
-Original Message-
Sent: Wednesday, May 30, 2001 1:03 PM
To: Multiple recipients of list ORACLE-L
At 8:10 -0800 30/5/01, [EMAIL PROTECTED] wrote:
>In earlier versions (7.3 ?)
recipients of list ORACLE-L
Subject:RE: Analyze table and locking
Oracle actually claims this statement. There are numerous docs stating
this,
for example DOC ID: 213220.999. But I agree 100% with Jeremiah's claims,
in
fact it can be verified very easily looking at v$lock while anal
Oracle actually claims this statement. There are numerous docs stating this,
for example DOC ID: 213220.999. But I agree 100% with Jeremiah's claims, in
fact it can be verified very easily looking at v$lock while analyzing a
large table. (I have actually done this in the past and present) and th
nice to see you posting again :)
"Walking on water and developing software from a specification are easy if
both are frozen."
Christopher R. Spence
Oracle DBA
Fuelspot
-Original Message-
Sent: Wednesday, May 30, 2001 4:03 PM
To: Multiple recipients of list ORACLE-L
At 8:10 -0800 30
Oracle actually claims this statement. There are numerous docs stating this,
for example DOC ID: 213220.999. But I agree 100% with Jeremiah's claims, in
fact it can be verified very easily looking at v$lock while analyzing a
large table. (I have actually done this in the past and present) and th
At 8:10 -0800 30/5/01, [EMAIL PROTECTED] wrote:
>In earlier versions (7.3 ?) 'analyze ..compute' used to take table
>level locks. From 8 onwards, compute or estimate does not take any
>table level or row level locks at all...
Sorry, not true. From at least 7.2.3 (the oldest I have here) there
I have used compute statistics since version 7.3.2 up through 8.1.6, and never
had it hold a table lock for the duration of the analyze. I don't know where
people are getting the idea that compute statistics holds a lock and estimate
doesn't. That just doesn't make sense. Why would compute need
Many
thanks
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Sent: 30 May 2001
17:10To: Multiple recipients of list ORACLE-LSubject:
RE: Analyze table and lockingAnalyze index validate structure takes shared lock on the table
disallowing any changes
Not necessarily. 8i+ DOESN'T require _ANY_ lock to COMPUTE while collecting
statistics (99.99% of total operation time). It needs short lock to start
and complete analyze.
Regards
Vadim
-Original Message-
Sent: Wednesday, May 30, 2001 9:07 AM
To: Multiple recipients of list ORACLE-L
Hi
Yeah If you use compute statistics your table get locked and will take long
time, But If you use estimate statistics it will be fast But not as
accurate as before. Better to analyze at nights by setting time.
Raghu.
>From: Robertson Lee - lerobe <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTE
: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: Analyze table and locking
Ok, now I'm confused
One reply
-
It depends. For instance, 'analyze index validate structure' takes out
'shar
It depends.
For instance, 'analyze index validate structure' takes out
'shared' lock on the table (preventing inserts/deletes/updates), while 'analyze
index compute/estimate statistics' does not.
For more info see 'Practical Oracle 8i' by Jonathan Lewis -
very good book.
Igor Neyman, OCP DB
I think thats the reason why we have two options - Estimate and Compute Statistics.
Rajaram.
-Original Message-
From: Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, May 30, 2001 7:22 AM
To: Multiple recipients of list ORACLE-L
Subject:Analyze table and lo
Ok, now I'm confused
One reply
-
It depends. For instance, 'analyze index validate structure' takes out
'shared' lock on the table (preventing inserts/deletes/updates), while
'analyze index compute/estimate statistics' does not.
Another
---
Compute option locks a tabl
Hi Lee,
Compute option locks a table, but 'estimate' doesn't
Regards,
Ed
-Original Message-
Sent: 30 ??? 2001 ?. 15:22
To: Multiple recipients of list ORACLE-L
All,
Just a quicky !!
Anyone know if there are any locking issues while analyzing statistics for
objects ??
TIA
Lee
Yes,
table is exclusively locked when ANALYZE ... VALIDATE. Until 9i, I
believe.
Vadim
Gorbounov
Oracle
DBA
-Original Message-From: Robertson Lee - lerobe
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, May 30, 2001 7:22
AMTo: Multiple recipients of list ORACLE-LSubject:
Analy
The key to the whole deal, as it turns out, being that the ANALYZE
ANY privilege must be granted explicitly in order to work in a stored
procedure. I tested this with a procedure for analyzing a table and
not even sys had sufficient privileges. But once I granted 'analyze
any' to a user they w
[EMAIL PROTECTED] wrote:
> I have two schemas: schema1 and schema2. I need to analyze
> schema1.mytable from a stored procedure owned by schema2. Schema1 has
> granted ALL on mytable to Schema2.
>From the Administrator's Guide: "To analyze a table, cluster, or index, you
must own the table,
Did you grant the privileges directly or through a role?
You need to grant them directly in order to have access to them in PL/SQL.
Jay Miller
x48355
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 10, 2001 9:56 AM
To: Multiple recipients of list ORACLE-L
This problem
Jared,
Thanks for replying.
You are right about binds and histograms. I didn't mean to imply "real"
histograms, where the number of buckets (size) are specified. Instead, in
this case, a simple "analyze table foo compute statistics" was used. This
would result in "column statistics" for *all* co
Larry,
You might want to give this a try using PL/SQL and bind variables.
I don't know if this has changed in 8i, but in 8.x, queries using
bind variables could not make use of histograms ( column statistics )
Jared
On Sat, 3 Feb 2001, larry elkins wrote:
> Sam,
>
> You had the right topic.
They most definitely non indexed will have influence on the CBO - there is
the ANALYZE FOR ALL COLUMNS option anyhow, that is there for that reason. I
think also likely used in conjunction with histograms on fields - DSS / Data
Warehouse :
Regards
Sam
-Original Message-
Sent: Saturday,
Sam,
You had the right topic. The original questions was "In what way do
statistics (or lack thereof) on non-indexed columns influence the CBO?". I
was having trouble thinking of a scenario where this would make a
difference, hence my posing the question to the list.
I finally thought of a scena
I missed out on original e-mail regarding this subject so I hope I'm on the
right track, but I assume that the CBO will use non-indexed columns in its
algorithms. I know DB2 would use non-indexed columns, maybe because DB2 is
a more advanced optimizer(only because IBM have been doing it a lot lon
Steve,
Thanks for taking the time to reply.
My initial thought was that the stats would be of no use, but, the thing
that still makes me wonder is why would a simple "analyze table compute
statistics" gather statistics on *all* columns. For this to be the default
behavior, there could be a reaso
Hi Larry,
I think that the answer is that the statistics are of no use to the CBO until an
index is created.
@ Regards,
@ Steve Adams
@ http://www.ixora.com.au/
@ http://www.christianity.net.au/
-Original Message-
Sent: Friday, 2 February 2001 11:16
To: Multiple recipients of l
92 matches
Mail list logo
