Pctfree oracle что это
deferred_segment_creation – SEGMENT CREATION DEFERRED/IMMEDIATE. DEFERRED-сегмент создается при первом INSERT.
Почти все физические атрибуты для партиционированных таблиц могут быть заданы индивидуально для разделов (а могут и на уровне таблицы, тогда это влияет на все разделы).
table_properties
AS subquery – запрос, результат которого будет вставлен в таблицу сразу при создании. При таком подходе можно не указывать явно перечень полей, он сформируется из запроса (а также типы данных и их размерности).
CACHE – блоки помещаются в начало LRU-листа как наиболее часто используемые (полезно для справочников). По дефолту NOCACHE.
enable_disable_clause – имеет отношение к изменению constraints (можно создать таблицу с неактивным constraint).
alter_table_properties - изменение атрибутов таблицы, аналогично созданию ( pctfee, pctused, initrans, logging, cache, result_cache, parallel, row_movement ), а также:
High Water Mark (HWM) – точка в сегменте, после которой блоки данных не отформатированы и никогда не использовались.
Oracle может выбрать для вставки любой блок ниже HWM, в котором достаточно места (ниже low HWM, или между ними).
Full Table Scan: т.к. блоки ниже HWM форматируются только при использовании, некоторые могут не быть не отформатированы. Поэтому Oracle читает bitmap block и определяет low HWM, читает все блоки до low HWM (они гарантированно отформатированы – можно применить «мультиблочное» чтение – за 1 операцию чтения с диска подтягивается DB_FILE_MULTIBLOCK_READ_COUNT блоков, это задано в в v$parameter), и затем читает по одному отформатированные блоки между low HWM и HWM.
Когда место между low HWM и HWM заполнено, HWM увеличивается, а low HWM становится на её старое место.
Для UNUSABLE индекса не создается сегмент, соотв. он не может быть использован при запросе, пока не перестроен ( rebuild ), или не удален и пересоздан ( drop, create )
index_expr – поле таблицы, или выражение ( function-based indexes - FBI ). Bitmap индекс может иметь до 30 полей, остальные до 32.
index_properties – физические атрибуты ( pctfree, pctused, initrans, storage ) , logging, tablespace, parallel, (IN)VISIBLE – видимость для CBO (Cost-Based Optimizer – оптимизатор запросов), REVERSE и др.
physical_attributes_clause
pctfree, pctused, initrans
PCTFREE – сколько % свободного места для последующих UPDATE нужно оставить в блоке при выполнении INSERT. Значение от 0 до 99 (default 10)
PCTUSED – минимальный % используемого места в блоке. Значение от 0 до 99 (default 40). Нельзя указывать для index-organized tables
Сумма PCTFREE и PCTUSED должна быть
Если задать маленькое значение PCTFREE и часто выполнять UPDATE – с большой долей вероятности строки будут перемещаться из текущего в другие блоки, а это относительно медленный процесс – быстрее просто обновить строку в блоке, чем фактически сделать DELETE (убрать её из блока) + INSERT (вставить в другой блок).
Но маленькое значение (например, PCTFREE = 2) для экономии дискового пространства имеет смысл делать для архивных таблиц, или таблиц логов – в них редко делаются UPDATE.
INITRANS – сколько слотов транзакций изначально выделяется в блоке. Значение от 1 до 255. По умолчанию 1, за исключением:
Слот транзакции – это место под некий маркер, который транзакция ставит в блок для пометки того факта, что она меняет этот блок. Занимает около 23 байт.
storage_clause
При создании сегмента для вычисления его первоначального размера используются параметры INITIAL, MINEXTENTS, NEXT и PCTINCREASE. При дальнейшем выделении новых экстентов они игнорируются.
другие – может быть выделено 64K, 1M, 8M, или 64M. Из них берем ближайшее меньшее, выделяем нужное количество таких экстентов (4M = 1M+…)
PCTINCREASE – на сколько % следующий экстент при выделении больше последнего (используется только для dictionary-managed tablespaces).
MINEXTENTS – минимальное количество экстентов объекта. Общее место при создании будет вычислено как INITIAL * MINEXTENTS. С помощью ALTER можно уменьшить этот параметр таблицы, но не увеличить. Это может быть полезно, например, перед использованием TRUNCATE . DROP STORAGE (чтобы сегмент занял минимальное количество экстентов после этого).
Buffer pool – в каком пуле будут лежать закэшированные блоки таблицы (DEFAULT – обычный, где горячие держатся дольше (LRU); KEEP – блоки вообще не вытесняются (пока хватает места), может быть полезно для справочников; RECYCLE – блоки вытесняются сразу после использования).
Reverse indexes
Индексы с реверсированным ключом – байты данных ключевого столбца в блоке индекса меняют порядок на противоположный (порядок столбцов остается неизменным). Oracle не сохраняет ключи индекса друг за другом в лексикографическом порядке.
Преимущество: если ключи монотонно возрастают, то велика вероятность, что вставляемые в разных сессиях строки попадут в один и тот же блок в индексе. Reverse индекс позволяет уменьшить конкуренцию за заголовок блока индекса при вставках из множества параллельных сессий, поскольку вероятность попадания «перевернутых» ключей в один и тот же блок меньше, чем неизмененных.
Недостаток: работает только на равенство, диапазонные поиски не работают.
Function-based indexes
Создается, когда в качестве index_expr задано выражение по полю таблицы, константа, SQL или user-defined функция (должны быть deterministic – для всех входных значений всегда возвращаются одни и те же результаты).
После создания FBI рекомендуется обновить статистику по индексу и таблице, чтобы CBO мог принимать правильные решения о его использовании.
Oracle иногда не может преобразовать типы, даже если это явно задано (to_number(‘123 abc’)). Поэтому, если индекс построен на TO_NUMBER/TO_DATE и вставляется/изменяется невалидное значение, возникает ошибка в операторе INSERT/UPDATE
Одно из применений - индекс по части таблицы: create index scott.emp_job_manager_idx on scott.emp (case when job = 'MANAGER' then 1 else null end) – если при запросах в scott.emp не участвуют строки с должностью, отличной от «MANAGER» - нет смысла держать их в индексе, экономим место. Кроме того, полное сканирование индекса быстрее работает по маленькому.
The physical_attributes_clause lets you specify the value of the PCTFREE , PCTUSED , and INITRANS parameters and the storage characteristics of a table, cluster, index, or materialized view.
You can specify the physical_attributes_clause in the following statements:
CREATE CLUSTER and ALTER CLUSTER : to set or change the physical attributes of the cluster and all tables in the cluster (see CREATE CLUSTER and ALTER CLUSTER).
CREATE TABLE : to set the physical attributes of the table, a table partition, the OIDINDEX of an object table, or the overflow segment of an index-organized table (see CREATE TABLE).
ALTER TABLE : to change the physical attributes of the table, the default physical attributes of future table partitions, or the physical attributes of existing table partitions (see ALTER TABLE). The following restrictions apply:
You cannot specify physical attributes for a temporary table.
You cannot specify physical attributes for a clustered table. Tables in a cluster inherit the physical attributes of the cluster.
CREATE INDEX : to set the physical attributes of an index or index partition (see CREATE INDEX).
ALTER INDEX : to change the physical attributes of the index, the default physical attributes of future index partitions, or the physical attributes of existing index partitions (see ALTER INDEX).
CREATE MATERIALIZED VIEW : to set the physical attributes of the materialized view, one of its partitions, or the index Oracle Database generates to maintain the materialized view (see CREATE MATERIALIZED VIEW).
ALTER MATERIALIZED VIEW : to change the physical attributes of the materialized view, the default physical attributes of future partitions, the physical attributes of an existing partition, or the index Oracle creates to maintain the materialized view (see ALTER MATERIALIZED VIEW).
CREATE MATERIALIZED VIEW LOG and ALTER MATERIALIZED VIEW LOG : to set or change the physical attributes of the materialized view log (see CREATE MATERIALIZED VIEW LOG and ALTER MATERIALIZED VIEW LOG).
This section describes the parameters of the physical_attributes_clause . For additional information, refer to the SQL statement in which you set or reset these parameters for a particular database object.
Specify a whole number representing the percentage of space in each data block of the database object reserved for future updates to rows of the object. The value of PCTFREE must be a value from 0 to 99. A value of 0 means that the entire block can be filled by inserts of new rows. The default value is 10. This value reserves 10% of each block for updates to existing rows and allows inserts of new rows to fill a maximum of 90% of each block.
PCTFREE has the same function in the statements that create and alter tables, partitions, clusters, indexes, materialized views, materialized view logs, and zone maps. The combination of PCTFREE and PCTUSED determines whether new rows will be inserted into existing data blocks or into new blocks. See "How PCTFREE and PCTUSED Work Together" .
Restriction on the PCTFREE Clause
When altering an index, you can specify this parameter only in the modify_index_default_attrs clause and the split_index_partition clause.
Specify a whole number representing the minimum percentage of used space that Oracle maintains for each data block of the database object. PCTUSED is specified as a positive integer from 0 to 99 and defaults to 40.
PCTUSED has the same function in the statements that create and alter tables, partitions, clusters, materialized views, materialized view logs, and zone maps.
PCTUSED is not a valid table storage characteristic for an index-organized table.
The sum of PCTFREE and PCTUSED must be equal to or less than 100. You can use PCTFREE and PCTUSED together to utilize space within a database object more efficiently. See "How PCTFREE and PCTUSED Work Together" .
Restrictions on the PCTUSED Clause
The PCTUSED parameter is subject to the following restrictions:
You cannot specify this parameter for an index or for the index segment of an index-organized table.
This parameter is not useful and is ignored for objects with automatic segment-space management.
Oracle Database Performance Tuning Guide for information on the performance effects of different values of PCTUSED and PCTFREE and CREATE TABLESPACE segment_management_clause for information on automatic segment-space management
How PCTFREE and PCTUSED Work Together
In a newly allocated data block, the space available for inserts is the block size minus the sum of the block overhead and free space ( PCTFREE ). Updates to existing data can use any available space in the block. Therefore, updates can reduce the available space of a block to less than PCTFREE .
After a data block is filled to the limit determined by PCTFREE , Oracle Database considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED . Until this value is achieved, Oracle Database uses the free space of the data block only for updates to rows already contained in the data block. A block becomes a candidate for row insertion when its used space falls below PCTUSED .
FREELISTS for information on how PCTUSED and PCTFREE work with freelist segment space management
Specify the initial number of concurrent transaction entries allocated within each data block allocated to the database object. This value can range from 1 to 255 and defaults to 1, with the following exceptions:
The default INITRANS value for a cluster is 2 or the default INITRANS value of the tablespace in which the cluster resides, whichever is greater.
The default value for an index is 2.
In general, you should not change the INITRANS value from its default.
Each transaction that updates a block requires a transaction entry in the block. This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.
The INITRANS parameter serves the same purpose in the statements that create and alter tables, partitions, clusters, indexes, materialized views, and materialized view logs.
In earlier releases, the MAXTRANS parameter determined the maximum number of concurrent update transactions allowed for each data block in the segment. This parameter has been deprecated. Oracle now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.
Existing objects for which a value of MAXTRANS has already been set retain that setting. However, if you attempt to change the value for MAXTRANS , Oracle ignores the new specification and substitutes the value 255 without returning an error.
The storage_clause lets you specify storage characteristics for the table, object table OIDINDEX , partition, LOB data segment, or index-organized table overflow data segment. This clause has performance ramifications for large tables. Storage should be allocated to minimize dynamic allocation of additional space. Refer to the storage_clause for more information.
Итак, давайте попробуем разобраться, с самой фундаментальной частью БД Oracle, которая называется "блок БД". Блоки являются самой элементарной единицей выборки данных. И стоят на самом нижнем уровне организации собственно самой БД. Блоки хранят всю информацию, из которой собственно и состоит ваша БД. Сразу оговариваюсь, не путайте блоки БД с блоками файловой системы, какая бы она не была на вашем сервере, FAT, FAT32, NTFS и т.д. Блоки БД, естественно базируются на блоках файловой системы, но с последними ничего общего не имеют. :) Хотя блоки БД, должны быть, кратны блокам файловой системы. Например, для UNIX это 8192, 16384 и т.д. Выбирать, размер блока БД можно самостоятельно при создании экземпляра БД, либо система задает этот параметр сама. В том случае как мы с вами устанавливали БД, размер блока у вас, скорее всего будет 2048 байт. В этом легко убедиться, проверив значение параметра db_block_size, в уже известном вам файле init.ora. Естественно при чтении данных из БД, считывается столько байт, сколько входит в один блок или кратное этому числу, в зависимости от количества информации, например, в запросе. Так же запомните сразу, единожды установленный размер блока БД, в процессе уже не может быть изменен. Если вы, по какой либо причине захотите изменить, размер блока БД, то необходимо сохранить ваши данные, затем удалить ваш экземпляр БД, и создать его вновь с новым значением блока БД! Что же представляет из себя блок БД? На рисунке представлено схематичное изображение блока.
Цифрой "1" обозначен заголовок блока, в нем хранится информация о том, к какому сегменту БД, принадлежит блок, количество одновременных транзакций и т.д. Цифрой "2" обозначено пространство, зарезервированное параметром PCTFREE для дальнейших обновлений данных в блоке. Что это за параметр мы сейчас разберем. Цифра "3" и "4" это пространство готовое к использованию, а вот цифра "5" показывает границу параметра PCTUSED. Что, это за параметры? PCTFREE - указывает какое количество (в процентах) пространства будет зарезервировано для дальнейших обновлений данных в блоке, а PCTUSED задает часть объема блока, которое должно освободиться прежде чем БД включит его в список доступных для ввода новых строк. Давайте разберем это все, так чтобы стало понятно! Например, вы создали таблицу MYTABLE, в которую поместили скажем 100 записей, ваша таблица заняла, фигурально один блок. Теперь, при создании БД параметр PCTFREE, в нашем случае, имел значение 10%, а параметр PCTUSED имел значение 40%. Что, это значит? Теперь ваш блок, имеет 10% пространства для обновления находящейся в нем таблицы, а 90% пространства отводятся под саму таблицу. То есть, если ваша таблица заполнила 90% процентов блока, то он вычеркивается самой БД из списка доступных для ввода новых строк БД. Но, те 10% процентов так и остались в блоке, для обновления вашей MYTABLE. Теперь скажем, вы обновили данные в вашей MYTABLE таким образом, что она стала занимать не 90% процентов пространства блока, а скажем 49% блока. Итак, 90%-49% = 41%, а параметр PCTUSED = 40%, замечательно, блок снова включается в список для записи новых строк! Теперь сюда можно поместить, скажем, таблицу NEWTABLE! Вот таким образом эти два параметра работают и управляют блоками БД. При этом ни один из параметров никогда не должен иметь значение 100%! С помощью PCTFREE и PCTUSED можно регулировать производительность самой БД. Забегая немного вперед скажу, что экземпляры БД имеют два типа при построении. Так называемые OLTP и DSS системы, первая это БД рассчитанная на тысячи активных транзакций, а вторая это хранилище данных используемое в основном для чтения, так вот правильная настройка блоков и их размер обязательно необходимое условие для производительного функционирования обоих типов БД! Посмотреть ваши параметры настройки блоков, как я уже говорил, можно в файле init.ora в секции db_block_size, например:
А значение параметров PCTFREE и PCTUSED можно посмотреть, войдя пользователем SYS или SYSTEM в SQL*Plus и написав такой запрос:
Как видно, в нашем случае PCTFREE = 10% и PCTUSED = 40%, в чем мы с вами и убедились! В дальнейшем мы еще, вернемся к этой теме, так как она еще далеко не полностью раскрыта. Но пока на этом с системными объектами БД мы закончили. Советую, еще раз все осмыслить и хорошенечко и запомнить на будущее.
В данном разделе представлены выдержки и некоторые программы из моего диплома,
посвященного настройке и оптимизации работы сервер ORACLE.
Параметры PCTFREE и PCTUSED.
Оба эти параметра служат для управления пространством в блоках данных. Точнее PCTFREE и PCTUSED позволяют управлять использованием свободного пространства для вставки и обновления строк в блоках данных.
Параметр PCTFREE устанавливает процент памяти блока, резервируемой для возможных обновлений строк, уже содержащихся в блоке. Как только блок данных будет заполнен до процента PCTFREE, в этот блок не возможно будет вставить новые строки до тех пор, пока процент памяти используемой в этом блоке не упадет ниже значения параметра PCTUSED. Параметр PCTUSED задает нижнюю границу, достижение которой вызывает возврат блока данных в список свободных областей. Оба параметра настраиваются в паре. Устанавливая разные варианты значений для этих параметров, можно оптимизировать использование дискового пространства.
Параметры PCTFREE и PCTUSED задаются при создании таблиц и индексов и указываются в конструкциях CREATE TABLE и CREATE INDEX соответственно, а так же могут быть изменены для уже созданных таблиц и индексов при помощи конструкций ALTER TABLE и ALTER INDEX.
При настройке PCTFREE и PCTUSED необходимо помнить о двух ограничения. Во-первых, их сумма не может превышать 100. Во-вторых, PCTFREE нельзя устанавливать равным 0, так как это вызовет проблемы распределения памяти для внутренних операций.
Дальше я приведу несколько стандартных вариантов установки данных параметров:
1. Большая часть запросов содержит операторы UPDATE, которые увеличиваю размеры записей.
PCTFREE = 20
PCTUSED = 40
PCTFREE установлен в 20, чтобы оставить достаточно места для записей, увеличивающихся в размере при обновлении. PCTUSED оставлен по умолчанию.
2. В основном запросы состоят из операторов INSERT и DELETE, а операторы DELETE в среднем не увеличивают размер записи.
PCTFREE = 5
PCTUSED = 60
PCTFREE установлен в 5, так как в основном длины записей не изменяются. PCTUSED установлен в 60, чтобы избежать дополнительного выделения большого числа блоков данных, так как память, освобождаемая оператором DELETE, почти сразу же используется оператором INSERT.
3. Данные из таблицы выбираются в основном на чтение.
PCTFREE = 5
PCTUSED = 90
PCTFREE установлен в 5, так как операторы UPDATE используются редко. PCTUSED установлен в 90, так что для хранения данных используется большая часть блока. В результате уменьшается общее число используемых блоков.
Высокая производительность Web-приложений зависит от эффективных баз данных, которые могут быстро обрабатывать запросы и извлекать их результаты. В статье описан анализ событий ожидания в Oracle для повышения производительности. В ней также демонстрируется использование системных утилит для эффективного сбора и анализа статистики производительности базы данных Oracle с целью определения проблем.
Мониторинг производительности базы данных и анализ событий ожидания
Чтобы повысить производительность СУБД Oracle, необходимо проанализировать события ожидания Oracle, чтобы определить узкие места и настроить параметры базы данных. Повышение эффективности обработки в базе данных Oracle повышает производительность всех систем и приложений, использующих эту базу данных. В статье демонстрируется использование системных утилит для эффективного сбора и анализа статистики производительности базы данных Oracle с целью определения проблем.
На производительность базы данных Oracle влияет множество факторов. В частности, существенное влияние на производительность оказывают факторы, перечисленные ниже.
Неэффективные SQL-операторы негативно влияют на производительность
При создании SQL-операторов имейте в виду, что они могут существенно повлиять на производительность запросов. Чем конкретнее оператор, тем быстрее будет получен результат. Предположим, нужно просмотреть информацию о конкретном сотруднике employee1 с идентификатором employee_id , равным 123 , из таблицы employee. Таблица employee содержит столбцы employee_name , employee_sal и employee_id , где employee_id является первичным ключом. Рассмотрим следующие варианты в порядке повышения эффективности:
- С помощью запроса select * from employee , извлекаем все записи из таблицы. Находим в результатах конкретного работника. Если результат запроса содержит много записей, этот процесс будет медленным.
- Используем запрос select * from employee where employee_name='employee1' . Этот оператор сужает поиск и ускоряет процесс.
- Используем запрос select * from employee where employee_id=123 . Поскольку employee_id является первичным ключом, этот запрос будет самым быстрым
Чтобы создать наиболее эффективный SQL-оператор, используйте следующие рекомендации:
- Избегайте запросов, которые не содержат условий where и первичных ключей.
- Большое количество запросов delete со временем приводит к росту несвязных блоков данных. Оператор delete только удаляет данные из блока, но не освобождает блок данных для дальнейшего использования. Поскольку операторы delete не снижают верхнюю границу заполнения, общий объем данных, в которых будут выполнять поиск будущие запросы, не уменьшается. Используйте оператор truncate, где это возможно, поскольку он сбрасывает верхнюю границу заполнения.
- Используйте операторы COMMIT только в случае необходимости. Избегайте их частого использования, поскольку они инициируют очистку буфера, что может привести к чрезмерному количеству операций ввода/вывода.
Неэффективная конфигурация базы данных негативно влияет на производительность
Неумелое конфигурирование негативно влияет на производительность базы данных. Конкретные параметры инициализации и конфигурации базы данных влияют не только на конфигурацию запуска, но и на обработку запросов. На производительность могут повлиять в сторону ухудшения приведенные ниже параметры (если они не настроены оптимальным образом).
Параметр PCTFREE
Параметр PCTFREE устанавливает минимальный процент резервирования свободного пространства блока для возможных обновлений строк, уже существующих в этом блоке. Слишком низкое значение PCTFREE может вызвать проблемы при обновлении. Слишком высокое значение может привести к неэкономному использованию пространства и вызвать избыточное перемещение блоков данных.
Параметр PCTUSED
Параметр PCTUSED устанавливает минимальный процент использования блока для данных в строках и накладных расходов на добавление в блок новых строк. После заполнения блока данными до предела, определенного параметром PCTFREE , Oracle считает блок недоступным для вставки новых строк, пока процент не опустится ниже значения параметра PCTUSED . До достижения этого порога Oracle использует свободное пространство блока данных только для обновления строк, уже содержащихся в блоке.
Неадекватное выделение памяти System Global Area (SGA) негативно влияет на производительность
Системная глобальная область (System Global Area - SGA) представляет собой группу общих структур памяти, содержащих данные и управляющую информацию для одного экземпляра базы данных Oracle. Пользователи, одновременно подключившиеся к одному и тому же экземпляру, совместно используют данные в SGA экземпляра. Поэтому SGA иногда называют общей глобальной областью. Для обеспечения эффективной производительности базы данных необходимо определить оптимальный размер памяти SGA и настроить ее соответствующим образом. Слишком маленькая память может существенно снизить производительность базы данных.
События ожидания негативно влияют на производительность
Oracle Database 11g имеет более 1000 событий ожидания, которые могут привести к задержкам обработки запроса. Эти события ожидания принято делить на следующие группы:
- Cluster (кластер)
- Network (сеть)
- Administration (администрирование)
- Configuration (конфигурация)
- Commit (фиксация)
- Application (приложение)
- Concurrency (параллелизм)
- System I/O (системный ввод/вывод)
- User I/O (пользовательский ввод/вывод)
- CPU (процессор)
Использование Oracle Enterprise Manager для анализа событий ожидания
Для мониторинга событий ожидания при обработке данных используется Oracle Enterprise Manager (OEM). OEM графически отображает состояние базы данных во время обработки. Он также предоставляет подробный аналитический отчет, в котором можно перейти к каждому событию и найти соответствующие SQL-операторы (см. рисунок 1). В легенде справа показаны типы событий ожидания.
Рисунок 1. Пример снимка экрана Oracle Enterprise Manager
На рисунке 2 показано детальное представление ожидания типа User I/O для данных, использованных в рисунке 1. Нажмите User I/O в OEM, чтобы перейти на страницу сведений о событиях ожидания типа User I/O.
Рисунок 2. Ожидание активных сеансов
Анализ событий ожидания с помощью shell-сценария
Можно собрать статистику событий ожидания базы данных Oracle без использования инструментальных средств, воспользовавшись shell-сценарием и встроенными системными утилитами. Чтобы собрать и проанализировать статистику для выявления источника узких мест и исправления проблем при поддержке администратора базы данных, необходимо выполнить приведенные ниже шаги.
Подготовка данных
Необходимо подготовить достаточное количество данных, поскольку база данных будет работать в течение нескольких часов или всю ночь. Зачастую много времени занимает обнаружение проблем кэша, которые могут не проявиться в течение нескольких первых часов работы базы данных. Нагрузка на базу данных должна имитировать производственные условия. Необходимо обеспечить такую смесь данных, чтобы запросы содержали выполняемые в режиме реального времени операции вставки, обновления, удаления и усечения. Для каждого элемента синтаксиса языка манипулирования данными (DML) используются разные источники, поэтому нужно сделать набор данных аналогичным или близким к данным производственной среды.
Шаг 1. Создание сценария
Чтобы получить подробную информацию и SQL-операторы для событий ожидания в представлениях V$ACTIVE_SESSION_HISTORY , V$EVENT_NAME и V$ SQLAREA , создайте простое соединение трех таблиц.
V$ACTIVE_SESSION_HISTORY Отображает активность сеансов в базе данных. В нем содержатся ежесекундные снимки активного сеанса базы данных.
V$EVENT_NAME Отображает информацию о событиях ожидания.
V$SQLAREA Отображает статистику для общей SQL-области, содержит одну строку для каждого SQL-выражения. Предоставляет статистику по SQL-операторам, которые находятся в памяти, проанализированы и готовы к выполнению.
Поскольку данные в этих представлениях периодически очищаются, используйте сценарий, приведенный в листинге 1, для сбора данных через одинаковые промежутки времени.
Листинг 1. Сценарий gather_event_stats.sh для периодического сбора данных
echo "SET MARKUP HTML OFF;">>sql 2>/dev/null
Чтобы данные, собранные в текстовый файл, можно было представить с помощью электронных таблиц (например, Microsoft Excel) и в дальнейшем проанализировать, в качестве разделителя столбцов используется символ тильды (~). Этот сценарий создает SQL-файл через одинаковые промежутки времени, выполняет SQL-выражения для сбора данных и помещает статистику в текстовый файл
Шаг 2. Запуск сценария
Разместите сценарий на любом сервере приложений и выполните сценарий для сбора данных. В приведенном ниже коде перед вызовом сценария замените переменные ORADBNAME, ORACLEID, PASSWORD соответствующими значениями.
Примечание . Эти значения можно сделать параметрами и передать в сценарий во время выполнения.
Для выполнения сценария используйте следующий синтаксис:
gather_event_stats.sh
Запустите сценарий до загрузки данных и не останавливайте его, пока загрузка данных не завершится. При выполнении сценария тщательно подберите значения No.Of.Iterations ($1) и Interval ($2) для выражения No.Of.Iterations x Intervals = Test Duration (Число итераций х интервалы = продолжительность теста).
Чтобы убедиться, что сценарий запущен, выполните следующую команду:
Шаг 3. Обработка данных
Введите данные и убедитесь, что приложение обрабатывает данные. Проверьте еще раз, что данные загружаются в базу данных. Подождите, пока не будут обработаны все данные. Выведите файл $outfile_name (имя файла, в который помещаются выходные данные запроса; в нашем случае - статистика событий ожидания) через одинаковые промежутки времени и убедитесь, что размер файла растет.
Шаг 4. Анализа данных и настройка базы данных
- После завершения обработки данных и выполнения сценария gather_event_stats.sh загрузите выходной файл $outfile_name из машины Unix в машину Windows.
- Откройте файл в текстовом редакторе. Если в этом файле есть символы табуляции или дополнительные пробелы, замените их одним пробелом, прежде чем загрузить файл в электронную таблицу Excel. На рисунке 3 показано, как выглядят загруженные данные в редакторе Notepad.
Рисунок 3. Снимок экрана с исходными данными в текстовом файле
- Импортируйте данные в электронную таблицу Excel, выбрав Data > Convert Text to Columns . Укажите символ тильды (~) как разделитель данных в столбцах (см. рисунок 4).
Рисунок 4. Снимок экрана с данными событий ожидания после разделения в Excel с помощью разделителя ~
- На основании этих данных создайте сводную таблицу, чтобы получить сумму общего времени ожидания для каждого события. Единицей времени является одна сотая секунды, поэтому делите значение на 100, чтобы преобразовать его в секунды. После создания отсортируйте сводную таблицу, чтобы определить событие с максимальным временем ожидания (см. рисунок 5).
Рисунок 5. Снимок экрана сводной таблицы
- Проанализируйте событие ожидания с помощью администратора базы данных Oracle, чтобы понять причину этого события. В нашем примере событием с наибольшим временем ожидания является TX - index contention . Это говорит о наличии проблемы индексирования при обработке транзакций.
- Получите SQLID для операторов wait с наибольшим временем ожидания и проверьте соответствующие SQL-операторы. В поле C.SQL_TEXT указывается SQL-оператор для каждого SQLID . Обратитесь к разработчикам и администратору базы данных, чтобы определить возможность настройки SQL-операторов для сокращения времени ожидания.
- Повторите пункт 6 для каждого из 10 событий с наибольшим временем ожидания.
- Реализуйте решения, предложенные разработчиками и администратором базы данных, для устранения этих событий ожидания.
- Снова выполните этот тест с той же нагрузкой и с той же продолжительностью, чтобы убедиться в уменьшении времени ожидания или полном устранении события ожидания.
Заключение
В этой статье описывается один из способов сбора статистики о событиях ожидания для базы данных Oracle. Процесс очень прост и не требует никаких специальных инструментов. Этот метод применим для анализа и решения проблем производительности базы данных для продуктов IBM, интегрированных с серверными базами данных Oracle.
physical_attributes_clause
pctfree, pctused, initrans
PCTFREE – сколько % свободного места для последующих UPDATE нужно оставить в блоке при выполнении INSERT. Значение от 0 до 99 (default 10)
PCTUSED – минимальный % используемого места в блоке. Значение от 0 до 99 (default 40). Нельзя указывать для index-organized tables
Сумма PCTFREE и PCTUSED должна быть
Если задать маленькое значение PCTFREE и часто выполнять UPDATE – с большой долей вероятности строки будут перемещаться из текущего в другие блоки, а это относительно медленный процесс – быстрее просто обновить строку в блоке, чем фактически сделать DELETE (убрать её из блока) + INSERT (вставить в другой блок).
Но маленькое значение (например, PCTFREE = 2) для экономии дискового пространства имеет смысл делать для архивных таблиц, или таблиц логов – в них редко делаются UPDATE.
INITRANS – сколько слотов транзакций изначально выделяется в блоке. Значение от 1 до 255. По умолчанию 1, за исключением:
Слот транзакции – это место под некий маркер, который транзакция ставит в блок для пометки того факта, что она меняет этот блок. Занимает около 23 байт.
storage_clause
При создании сегмента для вычисления его первоначального размера используются параметры INITIAL, MINEXTENTS, NEXT и PCTINCREASE. При дальнейшем выделении новых экстентов они игнорируются.
другие – может быть выделено 64K, 1M, 8M, или 64M. Из них берем ближайшее меньшее, выделяем нужное количество таких экстентов (4M = 1M+…)
PCTINCREASE – на сколько % следующий экстент при выделении больше последнего (используется только для dictionary-managed tablespaces).
MINEXTENTS – минимальное количество экстентов объекта. Общее место при создании будет вычислено как INITIAL * MINEXTENTS. С помощью ALTER можно уменьшить этот параметр таблицы, но не увеличить. Это может быть полезно, например, перед использованием TRUNCATE . DROP STORAGE (чтобы сегмент занял минимальное количество экстентов после этого).
Buffer pool – в каком пуле будут лежать закэшированные блоки таблицы (DEFAULT – обычный, где горячие держатся дольше (LRU); KEEP – блоки вообще не вытесняются (пока хватает места), может быть полезно для справочников; RECYCLE – блоки вытесняются сразу после использования).
Читайте также: