25июн
Взаимодействие параметров STATISTICS_LEVEL, DB_CACHE_ADVICE, TIMED_STATISTICS, TIMED_OS_STATISTICS
Параметр STATISTICS_LEVEL был введен в Oracle 9.2 и призван упростить управление параметрами, отвечающими за сбор статистики.
Согласно документации он может принимать значения:
STATISTICS_LEVEL = ALL | TYPICAL | BASIC
Уровень BASIC не предполагает сбора статистики. Прочие статистики активируются на соответствующем уровне параметра statistics_level:
SQL> select statistics_name, activation_level, description from v$statistics_level
STATISTICS_NAME ACTIVATION_LEVEL DESCRIPTION
---------------------------------------------------------------- ---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Buffer Cache Advice TYPICAL Predicts the impact of different cache sizes on number of physical reads
MTTR Advice TYPICAL Predicts the impact of different MTTR settings on number of physical I/Os
Timed Statistics TYPICAL Enables gathering of timed statistics
Timed OS Statistics ALL Enables gathering of timed operating system statistics
Segment Level Statistics TYPICAL Enables gathering of segment access statistics
PGA Advice TYPICAL Predicts the impact of different values of pga_aggregate_target on the performance of memory intensive SQL operators
Plan Execution Statistics ALL Enables collection of plan execution statistics
Shared Pool Advice TYPICAL Predicts the impact of different values of shared_pool_size on elapsed parse time saved
Если мы установим значение параметров DB_CACHE_ADVICE, TIMED_STATISTICS, or TIMED_OS_STATISTICS вручную, то будут действовать установленные нами значение вне зависимости от значения параметра STATISTICS_LEVEL.
Если же мы захотим вернуться к управлению, скажем, значением параметра TIMED_STATISTICS с помощью STATISTICS_LEVEL нам необходимо выполнить команду:
ALTER SYSTEM RESET timed_statistics SCOPE=spfile;
Источники:
http://dsvolk.msk.ru/oracle/faq/dba2.php