7.2. Параметры, влияющие на работу оптимизатора запросов
В Oracle 11g Release 2 добавились новые параметры, влияющие на работу оптимизатора запросов.
Параметр | Описание |
OPTIMIZER_USE_INVISIBLE_INDEXES | Разрешает или запрещает использование индексов в состоянии INVISIBLE. По умолчанию имеет значение FALSE |
OPTIMIZER_USE_PENDING_STATISTICS | Разрешает или запрещает использование статистики в состоянии PENDING. По умолчанию имеет значение FALSE |
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES | Разрешает или запрещает захват планов запросов для размещения в SQL Management Base. По умолчанию имеет значение FALSE |
OPTIMIZER_USE_SQL_PLAN_BASELINES | Разрешает или запрещает использование SQL Management Base. По умолчанию имеет значение TRUE. |
Не рекомендуется изменять значение данных параметров на рабочей базе данных, без предварительного тестирования.
7.3. Задание по сбору статистики
Начиная с версии Oracle 10g Release 2, в базе данных запускаются задания по автоматическому сбору статистики. В Oracle 11g Release 2 данное задание запускается, как часть автоматизированных заданий по управлению базой данных (AutoTask). Для отключения автоматического сбора статистики можно использовать пакет DBMS_AUTO_TASK_ADMIN.
Пример :
$ sqlplus / as sysdba
SQL> exec DBMS_AUTO_TASK_ADMIN. DISABLE(client_name=>’auto optimizer stats collection’,operation=>NULL, window_name=>NULL);
Для проверки состояния задания можно использовать представление DBA_AUTOTASK_CLIENT.
Пример :
$ sqlplus / as sysdba
SQL> select client_name, status, TOTAL_CPU_LAST_7_DAYS from dba_autotask_client where client_name like 'auto optimizer stats collection';
CLIENT_NAME STATUS TOTAL_CPU_LAST_7_DAYS
------
auto optimizer stats collection DISABLED
7.4. Изменения параметров сбора статистики
Начиная с Oracle 9i, для сбора статистики рекомендуется использовать пакет DBMS_STATS. Для сокращения длительности процедуры сбора статистики, рекомендуется задавать параметр ESTIMATE_PERCENT, определяющий процент данных используемых для получения статистики.
В Oracle 11g Release 2 алгоритм расчета статистики был модифицирован. При указании значения ESTIMATE_PERCENT равным AUTO_SAMPLE_SIZE, будет использоваться новый алгоритм расчёта статистики, который работает значительно быстрее.
7.5. Оценочное изменение при построении запросов
Оптимизатор запросов в версии Oracle 11g Release 2 при построении планов выполнения запросов может использовать следующие оптимизационные решения :
- Null aware anti join Join predicate pushdown Group by placement
Данные возможности можно отключить использую параметр OPTIMIZER_FUTURES_ENABLED. В случае массовой деградации планов запросов рекомендуется установить параметр OPTIMIZER_FEATURES_ENABLED=10.2.0.4.
После этого рекомендуется выяснить причину деградации планов, провести работы по ее нормализации и вернуться к OPTIMIZER_FEATURES_ENABLED=11.2.0.2
При необходимости получения уникальных данных, если оптимизатор Oracle 11g Release 2 определяет, что данные уже являются уникальными, то оптимизатор исключает действия по сортировке уникальных значений.
7.6. Расширенная статистика
Зачастую в колонках таблиц содержаться данные, которые зависят друг от друга. По таким колонкам можно собрать расширенную статистику и при построении запросов, оптимизатор будет учитывать корреляцию таких данных.
Возможно так же собрать статистику по группе или набору колонок, которая позволит оптимизатору более точно учитывать распределение или выборку данных. Такая статистика включает в себя подсчёт количества уникальных значений, количество null значений, частотные гистограммы и плотность.
7.7. Адаптивное построение планов запросов
В Oracle 11g Release 2 оптимизатор сохраняет значения переменных, совместно со статистикой ресурсоемкости запросов. Если оптимизатор определяет, что ресурсоёмкость запроса зависит от значения переменных, то может быть произведено построение нового плана.
То есть в зависимости от значения переменных, могут использоваться разные планы выполнения запросов. Данный механизм является значительно более интеллектуальным и менее ресурсоёмким, в отличии от механизма bind peeking, появившегося в Oracle 9i.
7.8. Менеджер зависаний
В Oracle 11g Release 2 появилась подсистема мониторинга зависаний (hang manager). Данная подсистема автоматически определяет зависшие процессы, анализирует причины и фиксирует в логах зависание (hang) процессов базы данных. За работу данного процесса отвечает выделенный служебный процесс DIA0.
Для просмотра информации о таких случаях можно использовать утилиту adrci.
7.9. Автоматическая оптимизация запросов
В версии Oracle 10g Release 2 администратор базы данных для настройки SQL мог использовать SQL Tuning Advisor. В Oracle 11g Release 2 SQL Tuning Advisor запускается автоматически. В качестве запросов кандидатов для оптимизации используются наиболее ресурсоемкие запросы, сохраненные в Automated Workload Repository (AWR). Данное задание называется Automatic SQL Tuning и запускается каждую ночь.
Для отключения данного задания можно использовать пакет DBMS_AUTO_TASK_ADMIN.
Пример:
$ sqlplus / as sysdba
SQL> exec DBMS_AUTO_TASK_ADMIN. DISABLE(client_name=>’sql tuning advisor’,operation=>NULL, window_name=>NULL);
Для проверки состояния задания можно использовать представление DBA_AUTOTASK_CLIENT.
Пример :
$ sqlplus / as sysdba
SQL> select client_name, status, TOTAL_CPU_LAST_7_DAYS from dba_autotask_client where client_name like 'sql tuning advisor';
CLIENT_NAME STATUS TOTAL_CPU_LAST_7_DAYS
------
auto optimizer stats collection DISABLED
7.10. Кэширование результатов запросов
Начиная с Oracle 11g Release 1, появилась возможность кэширования и повторного использования результатов выполнения запросов, без повторной обработки данных. В базе данных можно определить размер памяти, выделяемой в SGA для хранения результатов запросов (RESULT_CACHE_MAX_SIZE) и режим использования (RESULT_CACHE_MODE).
Использование данной возможности можно явно указать через задание подсказки (HINT) в запросе.
Несмотря на преимущества, в общем случае, не рекомендуется использовать данную возможность для продуктов на основе «ЦФТ-Платформа Развития».
7.11. Оптимизация PL/SQL
Для продуктов на основе «ЦФТ-Платформа развития» значение параметра PLSQL_OPTIMIZE_LEVEL должно быть равным 0. Оптимизацию PLSQL с уровнем равным 1 и выше категорически не рекомендуется использовать.
7.12. Изменения в политиках безопасности
Начиная с Oracle 11g Release 2 измены значения параметров профиля, определяющих срок жизни паролей:
Параметр профиля | Значение по умолчанию в Oracle 11g Release 2 |
PASSWORD_GRACE_TIME | 7 |
PASSWORD_LIFE_TIME | 180 |
PASSWORD_LOCK_TIME | 1 |
Параметр SEC_CASE_SENSITIVE_LOGON разрешает или запрещает различать регистры букв в паролях. Значение данного параметра по умолчанию TRUE. Для продуктов на основе «ЦФТ-Платформа Развития», мигрирующих с Oracle 10g Release 2 рекомендуется устанавливать данный параметр в значение FALSE.
Нижеследующие параметры служат для защиты базы данных от попыток перебора паролей.
- SEC_PROTOCOL_ERROR_FURTHER_ACTION SEC_PROTOCOL_ERROR_TRACE_ACTION SEC_MAX_FAILED_LOGIN_ATTEMPTS
Начиная с Oracle 11g Release 2, параметр AUDIT_TRAIL по умолчанию имеет значение DB. В предыдущих версиях, значение данного параметра было NONE.
По умолчанию ведется аудит следующих действий :
ALTER ANY PROCEDURE
CREATE ANY LIBRARY
DROP ANY TABLE
ALTER ANY TABLE
CREATE ANY PROCEDURE
DROP PROFILE
ALTER DATABASE
CREATE ANY TABLE
DROP USER
ALTER PROFILE
CREATE EXTERNAL JOB
EXEMPT ACCESS POLICY
ALTER SYSTEM
CREATE PUBLIC DB LINK
GRANT ANY OBJECT PRIVILEGE
ALTER USER
CREATE SESSION
GRANT ANY PRIVILEGE
AUDIT SYSTEM
CREATE USER
GRANT ANY ROLE
CREATE ANY JOB
DROP ANY PROCEDURE
Помимо этого ведется аудит всех операций имеющих признак BY ACCESS.
Для управления информацией в журнале аудита (таблица AUD$), в Oracle 11g Release 2 создан новый пакет DBMS_AUDIT_MGMT.
Приложение 1. Список устаревших параметров базы данных Oracle 11g Release 2
Устаревшие (DEPRICATED) параметры не рекомендуется использовать. При использовании данных параметров, во время старта базы данных, выдается предупредительное сообщение и производиться предупредительная запись в alert логе базы данных. Не рекомендуется использовать данные параметры при обновлении и работе базы данных.
Для получения списка устаревших параметров Oracle 11g Release 2 можно использовать следующий запрос:
SQL > SELECT name FROM v$parameter WHERE isdeprecated = 'TRUE';
NAME
-------
lock_name_space
instance_groups
resource_manager_cpu_allocation
active_instance_count
buffer_pool_keep
buffer_pool_recycle
log_archive_start
standby_archive_dest
log_archive_local_first
parallel_server
parallel_server_instances
fast_start_io_target
serial_reuse
max_enabled_roles
remote_os_authent
global_context_pool_size
cursor_space_for_time
plsql_v2_compatibility
plsql_debug
background_dump_dest
user_dump_dest
commit_write
sql_trace
parallel_automatic_tuning
parallel_io_cap_enabled
25 rows selected.
И дополнительно следующие параметры :
ACTIVE_INSTANCE_COUNT
PARALLEL_IO_CAP_ENABLED
Приложение 2. Список вышедших из употребления параметров базы данных Oracle 11g Release 2
Вышедшие из употребления (OBSOLETE) параметры не рекомендуется использовать. При использовании данных параметров выдается предупредительное сообщение при старте базы данных и производиться предупредительная запись в alert логе базы данных. Категорически не рекомендуется использовать данные параметры при обновлении и дальнейшей работе базы данных.
DRS_START
GC_FILES_TO_LOCKS
MAX_COMMIT_PROPAGATION_DELAY
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
SQL_VERSION
Приложение 3. Обновление подсистемы STATSPACK
Подсистема STATPACK служит для сбора исторических статистических данных о работе базы данных. В отличии от подсистемы AWR и ASH использование подсистемы STATSPACK не требует дополнительного лицензирования.
Перед обновлением подсистемы STATSPACK рекомендуется сохранить данные подсистемы, так как процедура обновление не предусматривает возвращения к старым данным. Для этого рекомендуется провести полный экспорт схемы PERFSTAT.
Пример команды :
еxp userid=perfstat file=statspack. dmp log=statspack. log
Обновление проводиться последовательно:
$ sqlplus / as sysdba
SQL> spool spup102.lst
SQL> @?/rdbms/admin/spup102.sql
SQL> spool off
Проводиться обновление подсистемы STATSPACK до версии 11.2.0.1. Обновление проводиться скриптом spup1101.sql. Пример :
$ sqlplus / as sysdba
SQL> spool spup1101.lst
SQL> @?/rdbms/admin/spup1101.sql
SQL> spool off
Проводиться обновление подсистемы STATSPACK до версии 11.2.0.2. Обновление проводиться скриптом spup11201.sql. Пример :
$ sqlplus / as sysdba
SQL> spool spup11201.lst
SQL> @?/rdbms/admin/spup11201.sql
SQL> spool off
После успешного обновления в схеме пользователя PERFSTAT не должно быть объектов в состоянии INVALID.
В случае использования Active DataGuard и резервной базы данных в состоянии read only, пакет STATSPACK позволяет сохранять статистические данные о производительности запросов на указанной базе данных.
Приложение 4. Правила ACL для совместимости сетевых процедур с Oracle 10g Release 2
Для пакетов типа UTL_*, использующих сетевые соединения, в версии Oracle 11g Release 2 необходимо задавать правила, ограничивающие перечень сетевых атрибутов, таких как имя хоста или диапазон используемых портов. Данные правила задаются через пакет DBMS_NETWORK_ACL_ADMIN. Далее приведён пример команд, создающих правило ACL, максимально совместимое с Oracle 10g Release 2 :
BEGIN
DBMS_NETWORK_ACL_ADMIN. CREATE_ACL(
acl => 'network_services. xml',
description => '10gR2 compatible',
principal => 'IBS',
is_grant => true,
privilege => 'connect');
DBMS_NETWORK_ACL_ADMIN. ADD_PRIVILEGE(
acl => 'network_services. xml',
principal => 'IBS',
is_grant => true,
privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN. ASSIGN_ACL(
acl => 'network_services. xml',
host => '*');
COMMIT;
END;
Приложение 5. Примеры конфигурационных файлов для настройки Websphere MQ 6.0
Для Oracle 11g Release 2 сертифицированным к использованию является Websphere MQ client версии 7.0.1.3 или выше, однако в ограниченном режиме совместимо с продуктами на основе «ЦФТ-Платформа Развития» допускается использование части компонент Websphere MQ 6.0 client..
Пример настройки прослушивающего процесса Messaging Gateway для работы с Websphere MQ, файл listener. ora :
MGW112 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCMGW))
)
)
)
SID_LIST_MGW112 =
(SID_LIST=
(SID_DESC =
(SID_NAME= mgwextproc)
(ENVS = “EXTPROC_DLLS= ANY, LD_PRELOAD=/u/app/oracle/product/11.2.0/dbhome_2/jdk/jre/lib/IA64W/server/libjvm. so, SHLIB_PATH=/u/app/oracle/product/11.2.0/dbhome_2/jdk/jre/lib/IA64W:/u/app/oracle/product/11.2.0/dbhome_2/jdk/jre/lib/IA64W/server:/u/app/oracle/product/11.2.0/dbhome_2/lib")
(ORACLE_HOME=/u/app/oracle/product/11.2.0/dbhome_2)
(PROGRAM = extproc)
)
)
Пример конфигурационного файла mgw. ora, для совместной работы с Websphre MQ 6.0.
log_directory=/u/app/oracle/product/11.2.0/dbhome_2/mgw/log
log_level = 2
set CLASSPATH=
/u/app/oracle/product/11.2.0/dbhome_2/jdbc/lib/ojdbc5.jar:
/u/app/oracle/product/11.2.0/dbhome_2/jdk/jre/lib/rt. jar:
/u/app/oracle/product/11.2.0/dbhome_2/sqlj/lib/runtime12.jar:
/u/app/oracle/product/11.2.0/dbhome_2/sqlj/lib/translator. jar:
/u/app/oracle/product/11.2.0/dbhome_2/jlib/orai18n. jar:
/u/app/oracle/product/11.2.0/dbhome_2/jlib/jta. jar:
/u/app/oracle/product/11.2.0/dbhome_2/mgw/mgw. jar:
/u/app/oracle/product/11.2.0/dbhome_2/rdbms/jlib/jmscommon. jar:
/u/app/oracle/product/11.2.0/dbhome_2/rdbms/jlib/aqapi. jar:
/u/app/oracle/product/11.2.0/dbhome_2/mgw/doplib/mqm/java/lib/com. ibm. mq. jar:
/u/app/oracle/product/11.2.0/dbhome_2/mgw/doplib/mqm/java/lib/com. ibm. mqbind. jar:
/u/app/oracle/product/11.2.0/dbhome_2/mgw/doplib/mqm/java/lib/com. ibm. jms. jar:
/u/app/oracle/product/11.2.0/dbhome_2/mgw/doplib/mqm/java/lib/jms. jar:
/u/app/oracle/product/11.2.0/dbhome_2/mgw/doplib/mqm/java/lib/com. ibm. mqjms. jar:
/u/app/oracle/product/11.2.0/dbhome_2/mgw/doplib/mqm/java/lib/jndi. jar:
/u/app/oracle/product/11.2.0/dbhome_2/mgw/doplib/mqm/java/lib/jta. jar:
/u/app/oracle/product/11.2.0/dbhome_2/mgw/doplib/mqm/java/lib/connector. jar:
/u/app/oracle/product/11.2.0/dbhome_2/mgw/doplib/mqm/java/lib
set MGW_PRE_PATH=/u/app/oracle/product/11.2.0/dbhome_2/jdk/jre/bin/client
Примечания :
1. Считается, что программное обеспечение Oracle 11g Release 2 установлено в каталог /u/app/oracle/product/11.2.0
2. Библиотеки Websphere MQ 6.0 развёрнуты в каталог /u/app/oracle/product/11.2.0/mgw/doplib
3. В настройке CLASSPATH библиотеки должны быть перечислены без переносов строк.
Приложение 6. Пример результата работы скрипта utlu112i. sql
Oracle Database 11.2 Pre-Upgrade Information Tool 16:00:38
Script Version: 11.2.0.2.0 Build: 001
.
******
Database:
******
--> name: COPY33
--> version: 11.2.0.1.0
--> compatible: 10.2.0.4.0
--> blocksize: 8192
--> platform: HP-UX IA (64-bit)
--> timezone file: V14
.
******
Tablespaces: [make adjustments in the current environment]
******
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 8022 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
--> T_USR tablespace is adequate for the upgrade.
.... minimum required size: 173053 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 830 MB
.
******
Flashback: ON
******
FlashbackInfo:
--> name: /ora10/COPY33/flash
--> limit: 153600 MB
--> used: 108619 MB
--> size: 153600 MB
--> reclaim: 108115.890625 MB
--> files: 1325
WARNING: --> Flashback Recovery Area Set. Please ensure adequate disk
spacein recovery areas before performing an upgrade.
.
******
Update Parameters: [Update Oracle Database 11.2 init. ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
******
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
******
Renamed Parameters: [Update Oracle Database 11.2 init. ora or spfile]
******
-- No renamed parameters found. No changes are required.
.
******
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init. ora or spfile]
******
-- No obsolete parameters found. No changes are required
.
******
Components: [The following database components will be upgraded or installed]
******
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> Messaging Gateway [upgrade] VALID
.
******
Miscellaneous Warnings
******
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj. sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER PUBLIC has 1 INVALID objects.
.... USER MIKE has 1 INVALID objects.
.... USER IBS has 124 INVALID objects.
.... USER DIRECTUM has 1 INVALID objects.
.... USER ZSB has 1 INVALID objects.
.... USER PERFSTAT has 1 INVALID objects.
.
******
Recommendations
******
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats. gather_dictionary_stats;
******
Oracle recommends removing all hidden parameters prior to upgrading.
To view existing hidden parameters execute the following command
while connected AS SYSDBA:
SELECT name, description from SYS. V$PARAMETER WHERE name
LIKE '\_%' ESCAPE '\'
Changes will need to be made in the init. ora or spfile.
******
Oracle recommends reviewing any defined events prior to upgrading.
To view existing non-default events execute the following commands
while connected AS SYSDBA:
Events:
SELECT (translate(value, chr(13)||chr(10),' ')) FROM sys. v$parameter2
WHERE UPPER(name) ='EVENT' AND isdefault='FALSE'
Trace Events:
SELECT (translate(value, chr(13)||chr(10),' ')) from sys. v$parameter2
WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'
Changes will need to be made in the init. ora or spfile.
******
Приложение 7. Совместимость Oracle 11g Release 2 и Oracle Client других версий
На сайте поддержки компании Oracle опубликован документ (Doc ID : 207303.1) содержащий таблицу совместимости разных версий клиентского и серверного программного обеспечения.
Версия клиента | Версия сервера | ||||||
11.2.0 | 11.1.0 | 10.2.0 | 10.1.0 | 9.2.0 | 9.0.1 | 8.1.7 | |
11.2.0 | Yes | Yes | ES #7 | No | ES #5 | No | No |
11.1.0 | Yes | Yes | ES #7 | ES #6 | ES #5 | No | No |
10.2.0 | ES #7 | ES #7 | ES | ES | ES #5 | No | Was |
10.1.0 | ES #6 | ES #6 | ES | ES | ES | Was | Was |
9.2.0 | ES #5 | ES #5 | ES #5 | ES | ES | Was | Was |
9.0.1 | No | No | No | Was | Was | Was | Was |
8.1.7 | No | No | Was | Was | Was | Was | Was |
8.1.6 | No | No | No | No | No | Was | Was |
8.1.5 | No | No | No | No | No | No | Was |
8.0.6 | No | No | No | No | Was | Was | Was |
8.0.5 | No | No | No | No | No | No | Was |
7.3.4 | No | No | No | No | Was | Was | Was |
Пояснения :
· Yes поддерживается.
· No не поддерживается
· Was более не поддерживается
· ES#5 Соединение между версией 10.2 (или выше) и 9.2 версия клиента 9.2 должна быть не ниже чем 9.2.0.4. Соединение между 10.2 (или выше) и 9.2.0.1, 9.2.0.2 или 9.2.0.3 не поддерживается
· ES#6 Для соединения между базами данных версий 11.1 (или выше) и базой 10.1 / 10.2 через database link версия базы данных 10g должна быть не менее 10.1.0.5 / 10.2.0.2 (или выше) для корректной работы PLSQL вызовов в данных версиях..
· ES#7 Для database link соединений между базами данных версий 11.2 (или выше) и 10.2 версия базы данных 10g должны быть 10.2.0.2 (или выше) для корректной работы PLSQL вызовов в данных версиях.
В случае использования database link соединение должно поддерживаться в обоих направлениях. Например создание database link с версии 11.2 на 10.1 не поддерживается.
Приложение 8. Совместимость Oracle 11g Release 2 с JDBC/JDK
JDBC драйверы сертифицированы для работы только с текущей версией базы данных. Для примера, JDBC Thin driver в Oracle Database 11g Release 2 сертифицирован для работы с версиями 10.2.x, 10.1.x, 9.2.x и 9.0.1 баз данных. Для работы с более старыми версиями 8.1.x, 8.0.x и 7.x. - JDBC драйверы не сертифицированы.
В таблице указано, какая версия JDBC драйвера может подключаться и работать с какой версии базы данных:
JDBC drivers | Oracle database |
11.2.0 | 11.2.0 |
11.1.0 | 11.2.0 |
10.2.0 | 11.2.0 |
10.1 | 11.2.0 |
9.2.0 | 11.2.0 |
Для JDBC драйверов версий старее, чем версия база данных в целом гарантируется работоспособность базовых операций, однако новые возможности базы данных не будут доступны.
JDBC драйверы Oracle 11g поддерживают только JDK 1.5 и JDK 1.6
|
Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 4 |
