Automatic stats(default 10g, manual 9i). Examine status begin dbms_stats.FLUSH_DATABASE_MONITORING_INFO(); end; select num_rows, last_analyzed, tot_updates, table_owner, table_name, partition_name, subpartition_name, inserts, updates, deletes, timestamp, truncated , to_char(perc_updates, 'FM999,999,999,990.00') perc_updates from ( select a.* , nvl(decode(num_rows, 0, '-1', 100 * tot_updates / num_rows), -1) perc_updates from ( select (select num_rows from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) num_rows , (select last_analyzed from dba_tables where dba_tables.table_name = DBA_TAB_MODIFICATIONS.table_name and DBA_TAB_MODIFICATIONS.table_owner = dba_tables.owner) last_analyzed , (inserts + updates + deletes) tot_updates , DBA_TAB_MODIFICATIONS.* from sys.DBA_TAB_MODIFICATIONS ) a ) b where perc_updates > 10 |
Please see:
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2)
--Verify enabled status:
SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
--disable
BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/
DBMS_STATS.DELETE_TABLE_STATS('OE','ORDERS');
DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS'); --and schema
|
Restore statistics to previous versions
select a.*
, (select 'begin sys.DBMS_STATS.RESTORE_DATABASE_STATS(to_timestamp(''' || end_time || ''', ''' ||
(select value from nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT') || ''')); commit; end;' from dual) f
from (
select operation, target,
to_char(start_time, timestamp_format) start_time,
to_char(end_time, timestamp_format) end_time from (
select v.*
, (select value from nls_session_parameters where parameter = 'NLS_TIMESTAMP_FORMAT') timestamp_format
from DBA_OPTSTAT_OPERATIONS v
)
) a
--where operation like 'gather%'
order by to_timestamp(start_time) desc
|
How to export/import statistics
|
How to take stats for tables (Sometime it doesn't work in TOAD, I don't know why) Index stats
begin
DBMS_STATS.GATHER_INDEX_STATS (ownname=>'ALDO',indname=>'IND_BANCHE_CODABI',partname=>NULL,estimate_percent=>49,
stattab=>NULL,statid=>NULL,statown=>NULL,degree=>4,granularity=>'DEFAULT',no_invalidate=>FALSE);
end;
begin
dbms_stats.gather_table_stats(ownname=>'ALDO',tabname=>'PERSONE',partname=>NULL,estimate_percent=>49,cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE AUTO',no_invalidate=>false, degree=>2);
end;
/
exit
Useful query to
generate script
select 'dbms_stats.gather_table_stats(ownname=> '''||owner||''', tabname=>''' || table_name ||''', partname=> NULL , estimate_percent=> 49, cascade=>true );' from dba_tables
where owner = 'VRQ'
and table_name like 'TMP_%';
How to take stats for tables (old way) Analyze Table ALDO.DVD_ATTORI Take stats for database execute DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent => 49, degree => 4, cascade => true, gather_sys => FALSE, no_invalidate => true, gather_temp => true);
Delete statistics for database begin DBMS_STATS.DELETE_DATABASE_STATS; end; Remember that sampling is based on this statement, example: ... FROM aldo.persons SAMPLE BLOCK (9) t ...
|
| How to enable auto statistics with monitoring Note:237901.1, Note 228186.1 Bug 1422285, Bug 2453682, Bug 3150750, Bug 2411876 Enable monitoring: exec dbms_stats.ALTER_SCHEMA_TAB_MONITORING('SCOTT',TRUE);
or
exec dbms_stats.alter_database_tab_monitoring(monitoring=>true,sysobjs=>false);
Wait 15 minutes for SMON to report updates in 9i, 3 hours in 8i
Stats are taken when 10% updates
Take stats using: set echo on;
set time on;
set timing on;
set serveroutput on;
set flush on;
DECLARE
anal_schema_01 VARCHAR2 (20) := 'DWH_ADM';
CURSOR c_enable_monitoring
IS
SELECT *
FROM dba_tables
WHERE MONITORING <> 'YES'
AND owner <> 'SYS'
AND TEMPORARY <> 'Y'
AND owner || table_name NOT IN (SELECT owner || table_name
FROM dba_external_tables)
AND owner = anal_schema_01;
cr_enable_monitoring c_enable_monitoring%ROWTYPE;
enable_statement VARCHAR2 (500);
m_already_running INTEGER;
m_action_name VARCHAR2 (48) := 'Auto stats';
PROCEDURE listgather (schema_name VARCHAR2)
IS
objlist DBMS_STATS.objecttab;
BEGIN
DBMS_OUTPUT.ENABLE (500000000);
DBMS_STATS.gather_schema_stats (ownname => schema_name,
options => 'LIST AUTO',
objlist => objlist
);
IF (objlist.COUNT = 0)
THEN
DBMS_OUTPUT.put_line ('No objects to analyze.');
ELSE
FOR i IN 1 .. objlist.COUNT
LOOP
DBMS_OUTPUT.put_line ( objlist (i).objtype
|| ' '
|| objlist (i).ownname
|| '.'
|| objlist (i).objname
|| ', partition:'
|| objlist (i).partname
|| ', sub part.:'
|| objlist (i).subpartname
);
END LOOP;
END IF;
END;
BEGIN
SELECT COUNT (*)
INTO m_already_running
FROM DUAL
WHERE EXISTS (SELECT *
FROM v$session s, v$process p
WHERE action = m_action_name);
IF m_already_running = 0
THEN
BEGIN
OPEN c_enable_monitoring;
LOOP
FETCH c_enable_monitoring
INTO cr_enable_monitoring;
EXIT WHEN c_enable_monitoring%NOTFOUND;
enable_statement :=
'alter table '
|| cr_enable_monitoring.owner
|| '.'
|| cr_enable_monitoring.table_name
|| ' monitoring';
DBMS_OUTPUT.put_line (enable_statement);
EXECUTE IMMEDIATE enable_statement;
END LOOP;
CLOSE c_enable_monitoring;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
DBMS_OUTPUT.put_line ('Taking stats...');
DBMS_APPLICATION_INFO.set_module (module_name => m_action_name,
action_name => m_action_name
);
DBMS_STATS.flush_database_monitoring_info ();
BEGIN
listgather (anal_schema_01);
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
--DBMS_STATS.gather_database_stats (options => 'GATHER AUTO');
DBMS_STATS.gather_schema_stats (ownname => anal_schema_01,
options => 'GATHER AUTO'
);
DBMS_APPLICATION_INFO.set_module (module_name => m_action_name,
action_name => 'Auto stats terminated'
);
DBMS_OUTPUT.put_line ('...done');
ELSE
DBMS_OUTPUT.put_line ('Stats already running');
END IF;
END;
/
exit;
|