ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle Automatic DBSTAT

Oracle Automatic DBSTAT

原创 Linux操作系统 作者:licheng79 时间:2012-02-23 11:37:52 0 删除 编辑

Oracle Automatic DBSTAT

The recommended approach to gathering statistics is to allow Oracle to automatically gather the statistics. Oracle gathers statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job. Automated statistics collection eliminates many of the manual tasks associated with managing the query optimizer, and significantly reduces the chances of getting poor execution plans because of missing or stale statistics.

Oracle Support - February 15, 2012 12:01:17 AM GMT+08:00 [ODM Answer]

There is nothing to suggest that the job scheduler is having any issues. As the queries show the jobs are running as scheduled with no errors. The jobs are stopping due to the end of the maintenance windows closing.

This would mean you are running into one of the following issues:
The tables are not considered stale and so stats are not being gathered
The window is too small to gather the stats for the tables
The tables are locked for statistics.

Please run the following query:

SET SERVEROUTPUT ON
spool stats.log
set linesize 200
set pagesize 2000
set echo on
DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_schema_stats(ownname => 'PB',objlist=>ObjList, ptions=>'LIST AUTO');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/

DECLARE
ObjList dbms_stats.ObjectTab;
BEGIN
dbms_stats.gather_schema_stats(ownname => 'PB',objlist=>ObjList, ptions=>'LIST STALE');
FOR i in ObjList.FIRST..ObjList.LAST
LOOP
dbms_output.put_line(ObjList(i).ownname || '.' || ObjList(i).ObjName || ' ' || ObjList(i).ObjType || ' ' || ObjList(i).partname);
END LOOP;
END;
/


select owner, table_name, num_rows, to_char(last_analyzed, 'DD-MON-YYYY HH24:MI') "Last Analyzed", stattype_locked
from dba_Tab_statistics
where num_rows is not null
order by 3 desc;

spool off

Regards,
Nicholas Meola
Oracle Customer Support

Note: Please do not reply to this email. This mailbox does not allow incoming messages.

 

Oracle Support - February 14, 2012 9:47:08 PM GMT+08:00 [Notes]

Generic Note
------------------------
Thank you for your recent update to your SR, I will be reviewing the uploaded files and will update the SR with any additional information as it becomes available.

Regards,
Nicholas Meola
Oracle Customer Support

Note: Please do not reply to this email. This mailbox does not allow incoming messages.

 

CHRIS.CC.WONG@CTM.COM.MO - February 14, 2012 3:21:39 PM GMT+08:00 [Update from Customer]

Uploaded.

 

CHRIS.CC.WONG@CTM.COM.MO - February 14, 2012 3:18:31 PM GMT+08:00 [Update from Customer]

Upload to gtcr successful for the file stats_job_info1.log.

 

Oracle Support - February 13, 2012 10:22:34 PM GMT+08:00 [ODM Action Plan]

I apologize, I made a mistake in the second query and listed the view incorrectly, it has been resolved,
It appears from the log that the job has been running, so we need to get a bit more detail on the job history.

This could be an issue where the windows are not large enough to get all the work done due to size and number of processes that are being used to gather stats.


SPO stats_job_info1.log
SET LINESIZE 200
SET PAGESZIE 2000
SET ECHO ON

select dbms_stats.get_param('cascade') from dual;
select dbms_stats.get_param('degree') from dual;
select dbms_stats.get_param('estimate_percent') from dual;
select dbms_stats.get_param('method_opt') from dual;
select dbms_stats.get_param('no_invalidate') from dual;
select dbms_stats.get_param('granularity') from dual;
 

SELECT status ,
TO_CHAR(log_date,'dd-mon-yyyy hh24:mi') log_date,
error#,
run_duration,
additonal_info
FROM dba_scheduler_job_run_details
WHERE job_name = 'GATHER_STATS_JOB';

SELECT owner ,
job_name ,
job_subname ,
session_id ,
slave_process_id ,
slave_os_process_id ,
running_instance ,
resource_consumer_group,
elapsed_time ,
cpu_used
FROM dba_scheduler_running_jobs
WHERE job_name = 'GATHER_STATS_JOB';

SPO off

Regards,
Nicholas Meola
Oracle Customer Support

Note: Please do not reply to this email. This mailbox does not allow incoming messages.

 

CHRIS.CC.WONG@CTM.COM.MO - February 13, 2012 10:49:45 AM GMT+08:00 [Update from Customer]

Upload to gtcr successful for the file stats_job_info.log.

 

CHRIS.CC.WONG@CTM.COM.MO - February 13, 2012 10:49:26 AM GMT+08:00 [Update from Customer]

Updated.

 

Oracle Support - February 10, 2012 10:08:12 PM GMT+08:00 [Notes]

Generic Note
------------------------
SPO stats_job_info.log
SET LINESIZE 200
SET PAGESZIE 2000
SET ECHO ON

SELECT log_id ,
job_name,
status ,
TO_CHAR(log_date,'dd-mon-yyyy hh24:mi') log_date
FROM dba_scheduler_job_run_details
WHERE job_name = 'GATHER_STATS_JOB';

SELECT owner ,
job_name ,
job_subname ,
session_id ,
slave_process_id ,
slave_os_process_id ,
running_instance ,
resource_consumer_group,
elapsed_time ,
cpu_used
FROM dba_scheduler_jobs_running
WHERE job_name = 'GATHER_STATS_JOB';

SPO off

Regards,
Nicholas Meola
Oracle Customer Support

Note: Please do not reply to this email. This mailbox does not allow incoming messages.

 

Oracle Support - February 10, 2012 9:24:55 PM GMT+08:00 [Notes]

Generic Note
------------------------
Thank you for your recent update to your SR, I will be reviewing the update and will provide any additional information as it becomes available.


Regards,
Nicholas Meola
Oracle Customer Support

Note: Please do not reply to this email. This mailbox does not allow incoming messages.

 

Oracle Support - February 10, 2012 9:24:25 PM GMT+08:00 [ODM Data Collection]

Name
--------
=== ODM Data Collection ===

Gather Stats job appears to be in a running state. Will need to get more diagnostics to determine for how long and what it is doing.

FileName
----------------
check_job.log

FileComment
----------------------
SELECT OWNER ,
2 JOB_NAME,
3 STATE ,
4 SCHEDULE_NAME
5 FROM dba_scheduler_jobs
6 WHERE job_name= 'GATHER_STATS_JOB';

OWNER JOB_NAME STATE SCHEDULE_NAME
------------------------------ ------------------------------ --------------- --------------------------------------------------------------------------------

SYS GATHER_STATS_JOB RUNNING MAINTENANCE_WINDOW_GROUP

 

CHENG.C.LI@CTM.COM.MO - February 10, 2012 10:17:34 AM GMT+08:00 [Update from Customer]

As your request, file uploaded. Thanks for timely replay!

 

CHENG.C.LI@CTM.COM.MO - February 10, 2012 10:16:21 AM GMT+08:00 [Update from Customer]

Upload to gtcr successful for the file check_job.sql.

 

Oracle Support - February 10, 2012 4:13:05 AM GMT+08:00 [ODM Action Plan]

Hello, my name is Nick Meola of the Oracle Database Performance Support Team, and I have been assigned to work on your Service Request.
++++++++++
Please run the following script. and upload spool file.

spo check_job.sql
SELECT OWNER ,
JOB_NAME,
STATE ,
SCHEDULE_NAME
FROM dba_scheduler_jobs
WHERE job_name= 'GATHER_STATS_JOB';

SELECT *
FROM DBA_SCHEDULER_WINGROUP_MEMBERS;

clear col
SET lines 200 pages 99 trimspool ON echo ON col window_name FOR a16 col resource_plan FOR a13 col repeat_interval FOR a43 col duration FOR a15 col enabled FOR a10 col active FOR a10
SELECT WINDOW_NAME ,
RESOURCE_PLAN ,
START_DATE ,
REPEAT_INTERVAL,
END_DATE ,
DURATION ,
ENABLED ,
ACTIVE
FROM dba_scheduler_windows;

spo OFF;

Regards,
Nicholas Meola
Oracle Customer Support

Note: Please do not reply to this email. This mailbox does not allow incoming messages.

 

Oracle Support - February 10, 2012 3:05:50 AM GMT+08:00 [ODM Question]

Statistics are not gathering automatically.

 

Oracle Support - February 10, 2012 3:05:13 AM GMT+08:00 [Notes]

Generic Note
------------------------
User reported that database performance is very bad. Execute a one query spend much time to completed. As I knew oracle 10g have default auto gather stats job running in maintenance window. I used to be depending on it to help us to gather the stats in our database. But today I found all indexes and tables didn't analyze since Jul-28-2011. What should I do now? How do I fix the problem? Please help us to find the root case and provide a solution for us. Thanks!

 

Oracle Support - February 10, 2012 2:08:35 AM GMT+08:00 [Information]

Oracle Database 10.2 has now transitioned from Premier Support to Sustaining Support. Our records indicate the CSI used for this SR does not have Extended Support so you are only eligible to Sustaining Support. Sustaining Support includes assistance with service requests, on a commercially reasonable basis, 24 hours per day, 7 days a week. It does not include new program updates, fixes, security alerts and critical update. The support policy details can be accessed at “http://www.oracle.com/us/support/library/057419.pdf “. If you need Extended Support please contact your account team.

 

CHRIS.CC.WONG@CTM.COM.MO - February 10, 2012 2:03:53 AM GMT+08:00 [Customer Problem Description]


1) Additional Information

 

CHRIS.CC.WONG@CTM.COM.MO - February 10, 2012 2:03:52 AM GMT+08:00 [Customer Problem Description]

Problem Description: User reported that database performance is very bad. Execute a one query spend much time to completed. As I knew oracle 10g have default auto gather stats job running in maintenance window. I used to be depending on it to help us to gather the stats in our database. But today I found all indexes and tables didn't analyze since Jul-28-2011. What should I do now? How do I fix the problem? Please help us to find the root case and provide a solution for us. Thanks!

 

  • Reference:

http://docs.oracle.com/cd/B13789_01/server.101/b10752/stats.htm

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26136400/viewspace-716997/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-09-07

  • 博文量
    54
  • 访问量
    66912