Лабораторная работа №7. Указания
1. Создание заданий
Автоматически исполняемые задания – удобный инструмент для периодического выполнения административных действий – сбора статистики данных для эффективной работы стоимостного оптимизатора, обновления данных в материализованных представлениях, мониторинга производительности экземпляра.
1.1 Общие настройки экземпляра
За выполнение заданий отвечают следующие фоновые процессы:
Координатор очереди заданий - CJQ0.
Подчиненные процессы – J000-J999, которые и выполняют задания.
Максимальное число подчиненных процессов определяется параметром инициализации JOB_QUEUE_PROCESSES, эти процессы порождаются не в момент запуска экземпляра, а динамически координатором, в зависимости от числа заданий требующих исполнения в данный момент времени. Если значение параметра JOB_QUEUE_PROCESSES равно 0, то процесс координатор не запускается, и задания выполняться не будут.
Пользователь создающий задание должен обладать привилегией CREATE JOB .
1.2 Управление заданиями, используя пакет dbms_job
До версии Oracle 9i включительно единственным средством управления заданиями был пакет dbms_job, позволявший выполнять следующие операции:
Создание задания:
SQL> variable v_job number;
SQL> begin
bmit(job => :v_job,
what => 'insert into test_l6(dt) values (sysdate);',
next_date => sysdate,
interval => 'sysdate+1/(24*60)');
commit;
end;
/
Используя процедуру submit создается задание:
what - выполняемый sql или pl/sql оператор, в примере – вставка в поле dt таблицы test_l6 текущей даты.
next_date – время первого запуска, в данном примере – время создания задания
interval – выражение для вычисления времени следующего запуска. В примере устанавливается время следующего запуска – 1 минута после текущего запуска (1 день разделить на 24 часа в сутках и 60 минут в часе).
Номер созданного задания записывается в переменную, передаваемую в параметр job.
Обратите внимание, что после выполнения bmit обязательно должен выполниться commit.
Изменения задания – используется процедуру change, параметры аналогичны параметрам предыдущей процедуры. В параметр job передается номер изменяемого задания.
Удаление задания – процедура remove. В параметр job передается номер удаляемого задания.
После выполнения процедур изменения/удаления, также должен выполняться commit.
Получить информацию о существующих заданиях можно из представления словаря данных dba_jobs например, запрос получающий номер задания, действие, время последнего выполнения и время следующего выполнения:
sql>select job, what, to_char(last_date,'hh24:mi:ss') ld, to_char(next_date,'hh24:mi:ss') nd from dba_jobs;
JOB WHAT LD ND
---------- -------------------------------------------------- -------- --------
21 insert into test_l6(dt) values (sysdate); 23:47:26 23:48:26
Подробнее о пакете dbms_job: http://docs. /cd/E11882_01/appdev.112/e40758/d_job. htm#ARPLS019
1.3 Управление заданиями, используя пакет dbms_scheduler
Начиная с версии 10g, существует новая версия планировщика, обладающая следующими возможностями:
- Запуск в качестве задания внешних, по отношению к экземпляру, приложений операционной системы. Отдельные сущности – «программа» и «расписание», комбинируя которые можно создавать задания.
Рассмотрим создание задания аналогичного приведенному в п. 1.2, используя сущности «программа» и «расписание».
Создание программы:
begin
dbms_scheduler. create_program(
program_name => 'test_prog',
program_type => 'PLSQL_BLOCK',
program_action => 'insert into test_l6(dt) values (sysdate);',
enabled => true,
comments => 'тестовая программа');
end;
Создается программа с именем test_prog тип которой plsql или sql оператор. Программа доступна для выполнения (enabled => true), также установлен комментарий. В дальнейшем информацию о созданных программах можно получить из представления dba_scheduler_programs.
Если бы было необходимо создать программу, соответствующую программе операционной системе сервера (например, выполнение экспорта), операция создания выглядела бы следующим образом:
begin
dbms_scheduler. create_program(program_name => 'test_ext_prog',
program_type => 'EXECUTABLE',
program_action => 'c:\app\test_exp. cmd',
enabled => true,
comments => 'тестовая внешняя программа');
end;
Тип программы указывается EXECUTABLE, в качестве действия указывается файл сценария c:\app\test_exp. cmd внутри которого уже осуществляется вызов утилиты exp.
Пользователь создающий такие программы должен обладать системной привилегией CREATE EXTERNAL JOB.
Создание расписания:
begin
dbms_scheduler. create_schedule(schedule_name => 'test_sched',
start_date => null,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2',
end_date => null,
comments => 'тестовое расписание');
end;
start_date – дата начиная с которой будет выполняться связанная с расписанием программа, если null, то программа начнет выполняться с момента доступности задания.
end_date - дата окончания выполнения связанной с расписанием программы.
repeat_interval – интервал повторения, в данном примере - через каждые 2 минуты.
Примеры значений repeat_interval:
FREQ=DAILY; INTERVAL=10; - через каждые 10 дней
FREQ=DAILY; BYHOUR=16,17,18; - каждый день в 16,17 и 18 часов
FREQ=MONTHLY; BYMONTHDAY=29; - 29 – число каждого месяца
Информация о созданных расписаниях - dba_scheduler_schedules.
Создание задания:
begin
dbms_scheduler. create_job(job_name => 'test_job',
program_name => 'test_prog',
schedule_name => 'test_sched',
enabled => true,
auto_drop => false,
comments => 'тестовое задание');
end;
Отмечаем, что задание доступно (enabled), и его не нужно удалять (auto_drop), после того как оно потеряет актуальность. На самом деле это задание никогда не потеряет актуальность, т. к. в расписании значение end_date было задано null.
Созданная ранее программа 'test_prog' может участвовать в другом задании с другим расписанием. Аналогично и для расписания 'test_sched'.
Если нет необходимости в отдельном объявлении расписания и программы, задание может быть создано в один прием:
begin
dbms_scheduler. create_job(job_name => …,
job_type => …,
job_action =>…,
start_date => …,
repeat_interval => …,
end_date => …,
enabled => …,
auto_drop => …,
comments => …);
end;
Параметры job_type и job_action соответствуют program_type и program_action при создании программы, а start_date, repeat_interval и end_date расписанию.
Информацию о заданиях созданных с использованием dbms_scheduler можно получить из представления dba_scheduler_jobs.
Остановка задания:
begin
dbms_scheduler. disable('имя задания');
end;
Дальнейшее включение:
begin
dbms_scheduler. enable('имя задания');
end;
Удаление задания:
begin
dbms_scheduler. drop_job(''имя задания ');
end;
Подробнее о планировщике заданий:
http://docs. /cd/E11882_01/appdev.112/e10577/d_sched. htm#ARPLS72235
2. Статистика данных и ее сбор.
Статистика данных является ключевым фактором при построении плана выполнения запроса.
2.1 Просмотр существующей статистики данных
Значения статистики данных можно получить из представлений словаря данных dba/all/user_tables для таблиц и dba/all/user_indexes для индексов.
Поля со значениями статистики данных в представлении USER_TABLES
NUM_ROWS | Число строк |
BLOCKS | Число занятых блоков |
CHAIN_CNT | Число строк, находящихся более чем в одном блоке |
AVG_ROW_LEN | Средний размер строки данных в байтах |
Поля со значениями статистики данных в представлении USER_TABLES
BLEVEL | Количество уровней в B*-дереве индекса |
LEAF_BLOCKS | Число листовых блоков индекса |
DISTINCT_KEYS | Количество различных значений в проиндексированном столбце (столбцах). Для уникальных индексов равно числу строк в проиндексированной таблице |
AVG_LEAF_BLOCKS_PER_KEY | Среднее число листовых блоков, в которых встречается одно и тоже значение ключа индекса. Для уникальных индексов эта величина равна 1. |
AVG_DATA_BLOCKS_PER_KEY | Среднее количество блоков данных таблицы, соответствующих одному значению ключа индекса. |
CLUSTERING_FACTOR | Фактор кластеризации, характеризует соответствие упорядоченности строк в таблице упорядоченности значений индекса. Если значение фактора кластеризации приближается к количеству блоков таблицы, то это означает, очень хорошее соответствие упорядоченности, и ссылки из листового блока индекса будут, скорее всего, вести в один блок данных таблицы. Если значение фактора кластеризации приближается к количеству строк, то строки расположены в блоках таблицы случайно по отношению к значениям индекса. И ссылки из одного листового блока индекса будут указывать на различные блоки данных проиндексированной таблицы. |
2.2 Сбор статистики данных
Сбор статистики для отдельной таблицы:
begin
dbms_stats. gather_table_stats (
ownname => ‘схема’,
tabname => ‘таблица’,
cascade => TRUE/FALSE
);
end;
/
Параметр cascade определяет, нужно ли собирать статистику для всех индексов таблицы. Для сбора статистики отдельного индекса используется процедура dbms_stats. gather_index_stats.
Для сбора статистики по всем таблицам схемы:
dbms_stats. gather_schema_stats .
Подробнее о сборе статистики и остальных процедурах пакета dbms_stats:
http://docs. /cd/E11882_01/appdev.112/e40758/d_stats. htm#ARPLS059
3. Получение плана выполнения запроса и статистики выполнения.
Для получения статистики выполнения (количества логических/физических чтений, использования времени процессора) конкретного запроса в SQLPlus перед выполнением этого запроса должен быть установлен параметр autotrace :
SET AUTOTRACE {OFF | ON | TRACEONLY} [EXPLAIN] [STATISTICS]
ON | OFF включает/выключает autotrace
TRACEONLY не показывать результат выполнения запроса.
EXPLAIN показывать только план выполнения запроса
STATISTICS показывать только статистику выполнения запроса
Т. е. после выполнения команды
SQL>set autotrace on
и выполнения запроса select * from t
Будет выведен собственно сам результат запроса, план выполнения и значения статистики выполнение.
А если перед выполнением запроса было установлено
set autotrace traceonly
то будут показаны план выполнения и статистика выполнения, а самого результата не будет. Такая установка полезна, если анализируется выполнение запроса, возвращающего большое количество данных.
Для того чтобы любой пользователь ORACLE мог использовать autotrace предварительно системный администратор должен выполнить:
Перед запуском SQLPlus установить переменную окружения ORACLE_HOME если она не установлена:
Для виртуальной машины:
set ORACLE_HOME=C:\app\admin\product\11.2.0\dbhome_1
Значение oracle_home для установки, можно посмотреть в реестре Windows:
После установки oracle_home, в SQLPlus, подключившись как sysdba необходимо выполнить:
SQL> @%ORACLE_HOME%/rdbms/admin/utlxplan
SQL> create public synonym plan_table for plan_table;
SQL> grant select, insert, update, delete on plan_table to public;
SQL> @%ORACLE_HOME%/sqlplus/admin/plustrce
SQL> grant plustrace to public;
4. Создание хранимых процедур
Приводится только необходимые для выполнения данной лабораторной работы сведения.
Синтаксис хранимой процедуры
CREATE PROCEDURE имя (параметр in/out/in out тип)
AS
DECLARE
переменная тип;
BEGIN
SQL-оператор;
……..;
END;
Процедура вставки n строк в таблицу, со случайным значением поля value от 1 до 20:
CREATE PROCEDURE add_lines(n in number)
as
begin
for i in 1..n loop
insert into test1(id, name)
values(i, floor(dbms_random. value(1,20)));
end loop;
end;
/
Вызов процедуры:
exec add_lines(50);
Тестовая процедура, внутри которой выполняется выборка данных:
CREATE PROCEDURE count_lines
as
declare
v number;
begin
select count(*)
into v
from test1;
end;
/
5. Получение AWR отчета.
Параметры создания AWR снимков:
Интервал создания – как часто снимки создаются в автоматическом режиме.
Поле snap_interval представления DBA_HIST_WR_CONTROL. По умолчанию, интервал создания равен одному часу. Для того чтобы установить значение интервала создания в 20 минут, нужно выполнить:
BEGIN
DBMS_WORKLOAD_REPOSITORY. MODIFY_SNAPSHOT_SETTINGS( interval => 20);
END;
/
Принудительное создание снимка:
BEGIN
DBMS_WORKLOAD_REPOSITORY. CREATE_SNAPSHOT ();
END;
/
Получение AWR отчета:
SQL>@%ORACLE_HOME%/rdbms/admin/awrrpt. sql
Тип отчета необходимо указать - html
Количество дней – 1
После этого будет выведен список имеющихся снимков, из которых нужно выбрать начальный и конечный для интересующего периода времени.
После этого указывается имя результирующего файла отчета. Возможно, сразу указать путь, где файл будет расположен. Например:
C:\app\admin\report1.html
Для получения отчета, сравнивающего 2 периода, используется сценарий
awrddrpt. sql
Подробнее о AWR:
http://docs. /cd/E11882_01/server.112/e16638/autostat. htm#PFGRF027
