luchuanc
asked on
Oracle 11gR2 table analyze
Hi,
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?
Thanks,
Luchuan
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?
Thanks,
Luchuan
Does this link answer's your question?
http://askdba.org/weblog/2009/08/gather_stats_job-is-it-enabled/
http://askdba.org/weblog/2009/08/gather_stats_job-is-it-enabled/
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)
Ajexpert,
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Agreed. As long as they are in an active maintenance window.
Please review the askers previous question. I covered all this there.
Please review the askers previous question. I covered all this there.
Here is the reference from the other question:
https://www.experts-exchange.com/questions/26946515/Why-Oracle-SQL-select-union-runs-100-times-slower-than-its-individual-sqls.html
-------------------------- ---------
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
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#PFGRF94713
https://www.experts-exchange.com/questions/26946515/Why-Oracle-SQL-select-union-runs-100-times-slower-than-its-individual-sqls.html
--------------------------
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
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#PFGRF94713
ASKER
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,
Luchuan
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> /
TRUNC(LAS COUNT(*)
--------- ----------
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.
SQL>
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,
Luchuan
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> /
TRUNC(LAS COUNT(*)
--------- ----------
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.
SQL>
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
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#i41788
If you still wish to manually generate them, check out the section:
Gathering Statistics Manually
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#insertedID3
>>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
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#i41788
If you still wish to manually generate them, check out the section:
Gathering Statistics Manually
http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#insertedID3
ASKER
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,
Luchuan
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,
Luchuan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi slightwv/ajexpert,
This is good for me now.
Thanks a lot for your help.
Luchuan
This is good for me now.
Thanks a lot for your help.
Luchuan
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.