Learn how to a build a cloud-first strategyRegister Now


Oracle 11gR2 table analyze

Posted on 2011-04-21
Medium Priority
Last Modified: 2012-05-11
We upgraded Oracle from 9i to 11gR last December. A procedure is running slower and slower: from 5 minutes to several hours. I manually analyzed the related tables. It then run fast again. We used to analyze all our tables weekly, we stopped doing that because we were told that we don't need to analyze table anymore in 11gR2 because Oracle will do it automatically. But the result indicated that we still need to manually analyze our tables. I am confused. Do we need to manually analyze our tables or not in 11gR2?

Question by:luchuanc
  • 7
  • 3
  • 2
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35444649
Didn't we just address this in your previous question?

Yes: tables need to be analyzed on a regular basis.  How often depends on how often major changes happen that could affect execution plans.

If the tables are not analyzed as part of a maintenance window, then you need to do it somehow.
LVL 14

Expert Comment

ID: 35444671
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35444674
Should also add that you can check important tables and indexes LAST_ANALYZED column in user_tables and user_indexes (or the ALL_/DBA_ level views)
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35444703

That link is the 10g gather_stats_job not 11g.  Keep Googling...

In 11g, there is no GATHER_STATS_JOB. It has been now incorporated into DBMS_AUTO_TASK_ADMIN job along with Automatic segment advisor and Automatic tuning advisor. All these 3 run in all maintenance windows. New maintenance windows introduced with 11g are
LVL 14

Assisted Solution

ajexpert earned 600 total points
ID: 35444741
Infact the link says it too...

These 3 maintainance tasks are clubbed in to DBMS_AUTO_TASK_ADMIN  job, that should be enabled and can be done by:

    client_name => 'auto optimizer stats collection',
    operation => NULL,
    window_name => NULL);
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35444748
Agreed.  As long as they are in an active maintenance window.

Please review the askers previous question.  I covered all this there.
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35447031
Here is the reference from the other question:
11g has a pre-configured maintenance window that 'should' automatically do this.  Maybe something in your environment turned it off.

Check out:  automatic optimizer statistics collection


Author Comment

ID: 35447877
Hi slightwv/ajexpert:,
The auto analyze seems partially working. The following sql result shows that only a small portiion of tables are auto analyzed every day.  How to control/see which tables to be auto analyzed? How to make all tables to be auto analyzed?

Thanks a lot,
  1  SELECT trunc(last_analyzed), count(*)
  2    FROM all_tab_statistics WHERE owner = 'AXIUM'
  3*   group by trunc(last_analyzed) order by 1 desc
SQL> /

--------- ----------
21-APR-11         25
20-APR-11         10
19-APR-11          3
16-APR-11          3
15-APR-11          4
14-APR-11          5
13-APR-11          6
12-APR-11          5
09-APR-11          6
08-APR-11          5
07-APR-11          2
06-APR-11          4
05-APR-11          3
03-APR-11          2
02-APR-11          2
01-APR-11          4
31-MAR-11          3
30-MAR-11          3
26-MAR-11          3
24-MAR-11          2
23-MAR-11          4
22-MAR-11          1
18-MAR-11          3
17-MAR-11          2
16-MAR-11          2
15-MAR-11          1
12-MAR-11          1
10-MAR-11          1
09-MAR-11          3
08-MAR-11          1
04-MAR-11          2
03-MAR-11          4
02-MAR-11          1
01-MAR-11          2
26-FEB-11          3
25-FEB-11          1
17-FEB-11          1
16-FEB-11          1
12-FEB-11          2
11-FEB-11          1
10-FEB-11          2
09-FEB-11          1
02-FEB-11          1
29-JAN-11          1
28-JAN-11          1
26-JAN-11          1
25-JAN-11          1
05-JAN-11          2
24-DEC-10          1
23-DEC-10          1
17-DEC-10          2
11-DEC-10        360

53 rows selected.

LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35447972
Everything you need to know is in the doc link I provided.

>>How to make all tables to be auto analyzed?

Are you sure you want to do this?  

If you review the link I provided in the docs, Oracle looks to see what tables 'need' their stats updated.

Excerpt from link:
"prioritizes database objects that require statistics, so that objects that most need updated statistics are processed first, before the maintenance window closes."

Also check out the section:  When to Use Manual Statistics

If you still wish to manually generate them, check out the section:
Gathering Statistics Manually



Author Comment

ID: 35449666
Hi slightwv:
That document seems no details on how oracle determine the objects that most need updated statistics. We need a few tables to be analyzed weekly. How to tell oracle to analyze certain tables weekly?

Thanks a lot,
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 1400 total points
ID: 35450824
What makes you think you MUST perform this on a few tables?

Same doc:

Automatic optimizer statistics collection should be sufficient for most database objects being modified at a moderate speed. However, in some cases the collection may not be adequate. Because the collection runs during maintenance windows, the statistics on tables that are significantly modified throughout the day may become stale. There are typically two types of such objects:

    *      Volatile tables that are deleted or truncated and rebuilt during the course of the day.
    *      Objects that are the target of large bulk loads which add 10% or more to the object's total size.

If you still want to physically do a few tables, I would create my own job and physically call:  DBMS_STATS.GATHER_TABLE_STATS


This will allow you personally tweak the settings for the individual tables.  For example, you might want to change sample sizes across the tables or even do a full compute.

Author Closing Comment

ID: 35491228
Hi slightwv/ajexpert,
This is good for me now.
Thanks a lot for your help.


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question