Oracle 11g system automatically collects statistics

Oracle 11g system automatically collects statistics

Before the 11g period, when the table's data volume was modified to more than 10% of the total data volume, statistics will be automatically collected at night. How to Determine the 10% data volume has been studied in previous posts: principles and experiments of Oracle automatic statistics collection. This STALE_PERCENT = 10% cannot be modified. If the table is very large and 10% is a lot of data, it is very likely that the statistical information is inaccurate.

After 11 GB, STALE_PERCENT = 10% can be modified, which can be divided into global (DBMS_STATS.SET_GLOBAL_PREFS) and Table level (DBMS_STATS.SET_TABLE_PREFS ). Table-level settings:

Change to 5% (range from 1-100): exec dbms_stats.set_table_prefs (null, 'table _ name', 'stale _ PERCENT ', 5 );
10%: exec dbms_stats.set_table_prefs (null, 'table _ name', 'stale _ PERCENT ', null );
Query percentage: select dbms_stats.get_prefs ('stale _ PERCENT ', null, 'table _ name') from dual;

--- 11 GB is from Monday to Friday-Saturday-

SELECT w. window_name, w. repeat_interval, w. duration, w. enabled
FROM dba_autotask_window_clients c, dba_scheduler_windows w
WHERE c. window_name = w. window_name
AND c. optimizer_stats = 'enabled ';
WINDOW_NAME REPEAT_INTERVAL DURATION
-----------------------------------------------------------------------------------------------
SUNDAY_WINDOW freq = daily; byday = SUN; byhour = 6; byminute = 0; bysecond = 0 + 000 20:00:00
FRIDAY_WINDOW freq = daily; byday = FRI; byhour = 22; byminute = 0; bysecond = 0 + 000 04:00:00
WEDNESDAY_WINDOW freq = daily; byday = WED; byhour = 22; byminute = 0; bysecond = 0 + 000 04:00:00
SATURDAY_WINDOW freq = daily; byday = SAT; byhour = 6; byminute = 0; bysecond = 0 + 000 20:00:00
THURSDAY_WINDOW freq = daily; byday = THU; byhour = 22; byminute = 0; bysecond = 0 + 000 04:00:00
TUESDAY_WINDOW freq = daily; byday = TUE; byhour = 22; byminute = 0; bysecond = 0 + 000 04:00:00
MONDAY_WINDOW freq = daily; byday = MON; byhour = 22; byminute = 0; bysecond = 0 + 000 04:00:00

-- Disable automatic collection
Exec DBMS_AUTO_TASK_ADMIN.DISABLE (client_name => 'Auto optimizer stats collection ', operation => NULL, window_name => NULL );
-- Enable Automatic Collection
Exec DBMS_AUTO_TASK_ADMIN.ENABLE (client_name => 'Auto optimizer stats collection ', operation => NULL, window_name => NULL );
-- View job

Select client_name, status from dba_autotask_client;

CLIENT_NAME STATUS
------------------------------------------------------------------------
Auto optimizer stats collection ENABLED
Auto space advisor ENABLED
SQL tuning advisor ENABLED

-- Collect statistics of all users in the current database
Exec dbms_stats.gather_database_stats (user );
-- Collect statistics on all objects in the current database user
Exec dbms_stats.gather_schema_stats (user );
-- Collect statistics of data dictionaries
Exec dbms_stats.gather_dictionary_stats ();
-- When the system has a large partition table, if it is always collected in full, it will be slow. After 11 GB, you can set INCREMENTAL to only collect partitions with data changes.

Exec dbms_stats.set_table_prefs (user, 'table _ name', 'credential', 'true'); -- collect only partitions with data changes

Exec dbms_stats.set_table_prefs (user, 'table _ name', 'credential', 'false'); -- collect all

Select dbms_stats.get_prefs ('credential', null, 'table _ name') from dual; -- view the INCREMENTAL value of the Partition table

-- Obtain the global statistics collection setting Option
Select dbms_stats.get_prefs ('method _ opt') from dual;
Select dbms_stats.get_prefs ('concurrent') from dual;
Select dbms_stats.get_prefs ('granularity ') from dual;

Select dbms_stats.get_prefs ('credential') from dual;
-- Set the global statistics collection Option

Exec DBMS_STATS.SET_PARAM ('degree', 4 );

Exec DBMS_STATS.SET_PARAM ('credential', 'true ');

Migration from 32-bit to 64-bit for a single Oracle instance

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Oracle Import and Export expdp IMPDP details

Solution to Oracle 10g expdp export error ORA-4031