analyze tables

[geobg]

for the stats to be proper, does the anlyse table compute statistics be run by system or by the schema owner ?

Please help.
How do i check for the stats ?


Geo

[sambavan]

If you are the owner of the table/indexes then you can run it, if you are not the owner and do not have the write permissions, then you use sys/system to analyze.

Sam

[geobg]

Thanks,

is there any way to see if the stats are proper ?

Geo

[sambavan]

Yes you can check it form the user_tables, user_indexes, dba_tables, dba_indexes. They all use the same table like all_tables and all_indexes in the back to query the information for you.

Sam

[tomate]

Is there an advantage to analyze a table estimate or compute.
I know the different. At compute the hole table will be analyze. So it is exacter and by estimate there will be used the first 1024 rows of a table to analyze.

And i know if the lengths of the rows are shaking then it is more better to use the compute statistics.

Is there more advantages or points to know.

[pipo]

well, first of all when you estimate stats, you specify the number of lines that is used. I often use estimate on 30000 lines, so small tables are computed (and not estimated), and you don't spend too much time calculating stats on big tables. Furthermore, my experience has shown that 30000 lines gives quite reliable stats

to know when stats were last computed, just check last_analyzed in [user/all/dba]_tab_columns

[tamilselvan]

Analyze command may not produce accurate result. It is better to use DBMS_STATS package for computing statistics.

[marist89]

Originally posted by tamilselvan
Analyze command may not produce accurate result. It is better to use DBMS_STATS package for computing statistics.

I've found that DBMS_STATS takes longer (at least twice as long in my case) to analyze your database than DBMS_UTILITY. Anybody else find this?

[chrisrlong]

Just thought I'd add my .02 here.

Compute vs. Estimate - I *always* use compute. Sorry, but I've never trusted estimating statistics, and after the 8.1.6.2 upgrade debacle, it's going to take an awful lot to convince me otherwise.

As for the packages - never use them. I don't see the advantage since they provide no additional functionality or performance.

As for speed, the problem is that almost *every* place I've gone to has done serial ANALYZE statement. *No wonder* they take forever. For example, the last project I was just on was doing an Estimate of 30% in 8.1.6.0, where it was *actually* only doing 1-3% (that was the bug), and it still took something like 3 hours to perform. Once 8.1.6.2 was installed and it actually started doing 30%, it climbed to over 6 hours.

When I propsed using COMPUTE on all the tables, they naturally balked and said "We don't have that kind of time". A simple procedure later and I was running 8 of them in parallel and cut the time back down to 3 hours. They are in Stress Testing again and are going to try running more in parallel (they have 24 procs), so I expect that will get even shorter.

So IMHO, COMPUTE is far better than ESTIMATE, and it is simple enough to fix the 'time' issue.

- Chris

[pando]

I think DBMS_STATS analyze in parallel