Oracle 9i slow response on DBMS_STATS

[Jakes Louw]

We're getting bad performance results when gathering statistics using the DBMS_STATS package, compared to using the analyze syntax, in the order of 5 times slower!
Has anybody else experienced this?

[Radim Jarosek]

Hi,

Could give us an example ?


Radim

[Sanjay Kumar Suri]

Analyze syntax collects the staistics for a single table/index.

Where as DBMS_STATS package offer many features to gather statistics - including the collection of statistcis by taking a random sample of blocks not just rows.

Just check the entire syntax.

sks

[Jakes Louw]

I have included both the analyze and DBMS_STATS syntax including timings.
DBMS_STATS is more than 5 times slow.


1 analyze table tsausgo1.accumulated_usage_01_b estimate statistics sample 10
percent
2* for table for all indexes
SQL> /

Table analyzed.

Elapsed: 00:00:31.99
SQL> begin
dbms_stats.gather_table_stats(ownname=>'TSAUSGO1',
tabname=>'ACCUMULATED_USAGE_01_B',cascade=>TRUE,estimate_percent=>10);
end;
/ 2 3 4 5

PL/SQL procedure successfully completed.

Elapsed: 00:02:53.19

[Hein van den Heuvel]

Sorry, no straight answers, just more questions.

- What exact oracle version & patch level?
This may well be very version specific

- Does the long dbms_stats time correspond with a 'calculate statistics? / 100% time?

- The ANALYZE TABLE doc reads: "When you collect statistics for a table, Oracle also
automatically collects the statistics for each of the tableâ s indexes and domain
indexes, as long as no for_clauses are used."
I'm wondering whether the 'for table for all indexes' while legal, confuses Oracle? What if you drop that? Or to put it differently... did that command update all index stats as expected?

. The ANALYZE TABLE doc (in the SQL Reference
Release 2 (9.2) March 2002 Part No. A96540-01) reads:

"Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer
statistics. ... Further, the cost-based optimizer, which depends upon statistics, will eventually use only statistics that have
been collected by DBMS_STATS. See Oracle9i Supplied PL/SQL Packages and Types Reference for more information on this package."

So there is the suggestion that the tow compared commands will eventually do entirely differnt things. I don't know how different they actually are (if at all different!) for the version you are using. Apples - oranges ?

Groetjes,
Hein.

[Brian Crabtree]

One thing to note, is that DBMS_STATS is more invasive than analyze, and will do more for you.

Two, comparing the time it takes to run an analyze verses dbms_stats is not a performance result, and doesn't show performance problems. If the queries you are using show degredation from analyze over dbms_stats, that is a performance problem.

Three, Oracle recommends using DBMS_STATS over analyze in 8i and up, and will most likely discontinue analyze in the future (previous post). Further, Oracle recommends that ALL statistics on the database be done with DBMS_STATS. They have found that there are performance problems when queries attempt to use both statistics.

Four, DBMS_STATS has the option to gather statistics in PARALLEL (table only, not index), which could help reduce the amount of time the statistics gather takes.

Let me know if you have any questions.

Thanks,

Brian

[Sanjay Kumar Suri]

I created two tables each having 27392 rows and did the following:

SQL> set timing on;
SQL> analyze table hr.sks1 estimate statistics sample 10 percent;

Table analyzed.

Elapsed: 00:00:02.05
SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'hr', tabname=>'sks1',cascade=>TRUE,
estimate_percent=>10);
3 end;
4 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.00
SQL> analyze table hr.sks2 estimate statistics sample 10 percent;

Table analyzed.

Elapsed: 00:00:01.00
SQL> begin
2 dbms_stats.gather_table_stats(ownname=>'hr', tabname=>'sks2',cascade=>TRUE,
estimate_percent=>10);
3 end;
4 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

1. The time taken in 2nd analyze has been halfed.

2. There is a drastic reduction in the time taken for the 2nd DBMS_STATS. So my conclusion is as under:

1. System has taken more time to store the DBMS_STATS in Library Cache of Shared Pool. That is why more Elapsed time when first time the statistics were collected for table sks1.

2. When sks2 was done since the entire procedure was already loaded in the memory it turned out to be much faster.

I hope this explains.

sks

[Jakes Louw]

Hi guys:

I'm not ignoring you, we're just looking at your input so far.

To answer a previous question: Oracle 9.2.3 running on HP-UX 11.11 June 2002 patch bundle, mixed server bag of V-class and SD-64.

Yes, we knew that DBMS_STATS will be slower, but our tests did not indicate that 5 times slower would be common: more like 2 times on the outside.

But since we're converting to cost-based optimization, we have no choice but to get this thing tuned properly, and that's why I asked the question (why re-invent the wheel, indeed!).

And yes, I will allocate points later....

[Yogeeraj_1]

hi,

also note the following:
it is easier to automate with dbms_stats (it is procedural, analyze is just a command)

dbms_stats is the stated, preferred method of collecting statisttics.

dbms_stats can analyze external tables, analyze cannot.

DBMS_STATS gathers statistics only for cost-based optimization; it does not gather other statistics. For example, the table statistics gathered by DBMS_STATS include the number of rows, number of blocks currently containing data, and average row length but not the number of chained rows, average free space, or number of unused data blocks.

dbms_stats (in 9i) can gather system stats (new)

ANALYZE calculates global statistics for partitioned tables and indexes instead of gathering them directly. This can lead to inaccuracies for some statistics, such as the number of distinct values. DBMS_Stats won't do that.

Most importantly, in the future, ANALYZE will not collect statistics needed by the cost-based optimizer.

best regards
Yogeeraj

[Sanjay Kumar Suri]

Dear Yogeeraj

Your recent post has cleared all the fundas of DBMS_STATS. Thanks.

sks

[Jakes Louw]

Feedback on this: apparently Oracle is adressing this in one of the patches, perhaps 9.2.0.6.
If you have access to Metalink, there's apparently info on this issue.
Thanks to all for your responses, points have been awarded.

[Hein van den Heuvel · Accepted solution]

Sjaak,
If you found a specific metalink article to address this, then perhpas share it here and consider this topic closed?

If you consider this topic closed, as far as teh itrc is concerned, then why no mark some reply, any reply, with '8 points' just to show it is all done?

This metalink article might apply:

http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=BUG&p_id=3377706

[Jakes Louw]

So gemaak, Hein.

I was not aware that assigning an 8-point or higher was the correct method of closure....

[Hein van den Heuvel]

Well, maybe it is not the official way. I never read the 'itrc etiquete' documents.

In the left pane the 'bunny' is explained as:
"magical answer
Message with a response that solved the author's question"

I just thought it would be a good idea in general to assign that to some answer when the problem is essential solved or no longer followed up by the author. Even if it is not 'magical' or entirely solves the problem, it seems a useful flag.

You can also see the 0-7 and 8 - 10 point distrinction in your profile. There is a section 'my responses' (under 8), and 'my solutions (8 or more).

Met vriendelijke groetjes,
Hein.

(funny language that afrikaans :-).