7.3. Измерение времени в программе
Важно уметь измерять время, затраченное на выполнение процесса, чтобы определить качество программного кода, поскольку именно скорость является ключевой характеристикой приложений, работающих с базами данных.
Один из способов измерения времени выполнения процедуры состоит в том, чтобы фиксировать время в самой процедуре, как показано в приведенном ниже SQL-сценарии. Для определения времени начала и завершения цикла, вставляющего 5000 записей в таблицу, здесь используется функция SYSDATE. Затем вычисляется время вставки одной записи путем деления общего времени выполнения на 5000.
Полученные результаты будут варьироваться в зависимости от различных факторов: нагрузки на сервер базы данных, фактической скорости работы серверного компьютера и т.д. Для оценки производительности обычно запускают один и тот же код несколько раз, а затем берут среднее.
DROP TABLE timetab CASCADE CONSTRAINTS;
CREATE TABLE timetab (
c1 NUMBER NOT NULL,
c2 VARCHAR2(30) NULL,
c3 DATE NULL
) ;
CREATE OR REPLACE PROCEDURE test_time IS
maxloops NUMBER := 5000;
loopcount NUMBER (6,0) := 0;
starttime CHAR(5);
endtime CHAR(5) ;
/* Поскольку начальное и конечное время определяются как число
секунд после полуночи, эта процедура не будет работать, если
во время выполнения произойдет переход через полночь .
*/
runtime NUMBER ;
processrate NUMBER (20, 10) ;
BEGIN
starttime := TO_CHAR( SYSDATE, 'SSSSS');
LOOP
loopcount := loopcount +1;
INSERT INTO timetab (C1, C2, C3)
VALUES (loopcount, 'TEST ENTRY', SYSDATE);
COMMIT;
IF loopcount >= maxloops THEN
EXIT;
END IF;
END LOOP;
COMMIT;
endtime := TO_CHAR(SYSDATE,'SSSSS');
runtime := TO_NUMBER(endtime) - TO_NUMBER(starttime);
dbms_output.put_line(runtime || ' seconds');
processrate := maxloops / runtime;
INSERT INTO timetab (C1, C2, C3) VALUES
(loopcount+1,
TO_CHAR(processrate, '9999999999') || 'records per second',
SYSDATE
) ;
END test_time;
/
EXECUTE test_time;
SELECT * FROM timetab
WHERE c1 > 5000;
Ниже приведен тестовый цикл, показывающий результаты измерения времени при десятикратном запуске того же самого кода.
TRUNCATE TABLE timetab;
COMMIT;
SET SERVEROUTPUT ON
BEGIN
FOR trial_count IN 1..10
LOOP
test_time;
COMMIT;
END LOOP;
END;
/
SELECT * FROM timetab
WHERE c1 > 5000
ORDER BY c3;
Когда нужно произвести более точные измерения, можно воспользоваться командой TIMING. Чтобы показать, как это делается, нужно немного модифицировать последний пример.
TIMING START;
EXECUTE test_time;
COMMIT;
TIMING STOP;
Истекшее время будет отображаться автоматически командой TIMING STOP в миллисекундах; например, число 8670 означает, что между командами TIMING START и TIMING STOP прошло 8.67 секунды.
7.4. Пакеты (модули) pl/sql
При разработке приложений PL/SQL обеспечивает эффективный подход, предоставляя пакеты (packages), или модули, для совместного хранения полезных функций, процедур, типов записей и курсоров. Аналогично процедуре или функции, пакет имеет спецификацию и тело. Однако спецификацию и тело пакета можно создавать по отдельности. Можно даже заменить одно тело пакета другим, сохранив совместимость с существующей спецификацией, и пакет по-прежнему будет работать.
Держать тело отдельно от спецификации целесообразно потому, что пользователи пакета не обязаны знать все детали его реализации, и эти детали скрываются внутри тела пакета. Тело хранится, компилируется и обрабатывается внутри базы данных и невидимо пользователям пакета. Для программирования своих задач им просто следует пользоваться спецификацией. Это очень важно, когда когда нужно защитить свой код, сделав его недоступным для хакеров или конкурентов.
Спецификация пакета имеет следующий вид:
CREATE PACKAGE имя_пакета IS
[объявления переменных_и_типов]
[спецификации курсоров]
[спецификации функций_и_процедур]
END [имя_пакета] ;
Для создания тела пакета используется такой синтаксис:
CREATE OR REPLACE PACKAGE BODY имя_пакета IS
[локальные объявления]
[полные спецификации курсоров пакета] ,
[полные спецификации функций_и_процедур пакета]
BEGIN
[выполняемые операторы]
[EXCEPTION]
[обработчики исключений]
END [имя_пакета];
Все переменные и типы, объявленные в спецификации пакета, доступны его пользователям. Спецификация позволяет узнать, что содержит пакет, а также какие переменные ожидаются этим содержимым на входе и выходе. Секция объявлений содержит объявления функций, процедур, исключений, курсоров, переменных и констант, доступных для использования в теле пакета. Выполняемая секция тела пакета содержит полные определения (без команды CREATE OR REPLACE) всех курсоров, функций и процедур, объявленных в его спецификации.
Переменные, присутствующие в спецификации пакета, называются переменными пакета (package variables). Они инициализируются только один раз — при первом обращении к нему. Когда производится вызов какой-либо составляющей пакета, Oracle загружает пакет в память, где он остается все то время, пока пользователь соединен с базой данных. При наличии нескольких сеансов переменные пакета и их значения становятся разделяемыми; следует также заметить, что обращение к объектам, помещенным в пакет, в последующих сеансах может происходить быстрее.
Выполняемая секция пакета обычно используется для инициализации локальных переменных пакета или переменных пакета. Выполнение этой секции производится один раз при загрузке пакета, а следовательно, ее не стоит использовать для каких-либо повторяющихся действий, поскольку обратиться к ней более одного раза все равно не удастся.
Для обращения к процедурам, переменным и функциям пакета используется та же самая нотация контейнер.содержимое. В данном случае она имеет вид имя_пакета.объект_пакета. В примерах уже использовалась одна функция из пакета — это была функция dbms_output.put_line из пакета dbms_output, поставляемого в составе Oracle. При ссылках на функции внутри их собственного пакета нет необходимости помещать квалификатор имя_пакета перед именем функции.
В качестве иллюстрации далее показан SQL-сценарий для создания пакета, включающего в себя некоторые из ранее рассмотренных примеров:
CREATE OR REPLACE PACKAGE pack IS
DATE_LOADED DATE;
/* Эффективность (performance) продавца — это текущая средняя сумма заказа в процентах от исторической средней суммы заказа для того же продавца. Status возвращает сообщения об ошибках или их отсутствии.
*/
TYPE pkg_perform_type IS RECORD
(person_code person.person_code%TYPE,
person_name char ( 12 ) ,
current_sales NUMBER(8,2),
perform_percent NUMBER(8,1),
status char (30)
);
CURSOR PKG_PER_CUR RETURN person%ROWTYPE;
/* Функция pkg_comp_discounts для расчета скидки на заказ. Входной параметр — сумма заказа.
Возвращается сумма скидки (нуль при неверных входных данных) .
*/
FUNCTION pkg_comp_discounts (order_amt NUMBER) RETURN NUMBER;
/* Процедура pkg_compute_perform вычисляет эффективность и текущий суммарный объем продаж для одного продавца . Информация о продавце передается через запись a_person. Если в течение дня продавец ничего не продал, current_sales устанавливается равной нулю.
Если история продавца отсутствует (например, он начал работу лишь сегодня), perform percent устанавливается равной нулю.
*/
PROCEDURE pkg_compute_perform (a_person person%ROWTYPE,
a_perform OUT pkg_perform_type) ;
END pack;
/
CREATE OR REPLACE PACKAGE BODY pack IS
small_order_amt NUMBER (8, 2) := 400;
large_order_amt NUMBER (8, 2) := 1000;
small_disct NUMBER (4, 2) := 1;
large_disct NUMBER (4, 2) := 5;
CURSOR PKG_PER_CUR RETURN person%ROWTYPE
IS
SELECT * FROM person;
FUNCTION pkg_comp_discounts (order_amt NUMBER) RETURN NUMBER
IS
BEGIN
IF (order_amt < large_order_amt AND order_amt > small_order_amt)
THEN
RETURN (order_amt * small_disct / 100) ;
ELSIF (order_amt >= large_order_amt)
THEN
RETURN (order_amt * large_disct / 100) ;
ELSE
RETURN (0);
END IF;
END pkg_comp_discounts;
PROCEDURE pkg_compute_perform (a_person person%ROWTYPE,
a_perform OUT pkg_perform_type)
IS
hist_ord_avg NUMBER(8,2) := 0;
current_avg_sales NUMBER(8,2) := 0;
BEGIN
a_perform.person_code := a_person.person_code;
a_perform.person_name := a_person.last_name;
a_perform.status := NULL;
BEGIN
SELECT SUM(tbl2.product_price * tbl1.quantity),
AVG(tbl2.product_price * tbl1.quantity)
INTO a_perform.current_sales,
current_avg_sales
FROM purchase tbl1,
product tbl2
WHERE tbl1.product_name = tbl2.product_name
GROUP BY tbl1.salesperson
HAVING tbl1.salesperson = a_person.person_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
a_perform.status := 'Current purchases exception';
a_perform.current_sales:= 0;
END;
BEGIN
SELECT AVG(tab2.product_price * tab1.quantity) avg_order
INTO hist_ord_avg
FROM purchase_archive tab1, product tab2
WHERE tab1.product_name = tab2.product_name
GROUP BY tab1.salesperson
HAVING tab1.salesperson = a_person.person_code;
a_perform.perform_percent :=
100 * (current_avg_sales-hist_ord_avg) / hist_ord_avg;
a_perform.status := 'All fine';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
a_perform.perform percent := 0;
IF (a_perform.status IS NULL)
THEN
a_perform.status := 'Erroneous or no history' ;
END IF;
END;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
a_perform.status := 'Exceptions found' ;
END pkg_compute_perform;
BEGIN
/* Дата первой загрузки пакета */
DATE_LOADED := SYSDATE;
END pack;
/
DECLARE
one_perform pack.pkg_perform_type;
cursale char(8);
disct char(8);
perf char(8); ,
BEGIN
dbms_output.put_line('Code' || ' ' || 'Last Name' || ' ' || 'Total Sales' || ' ' || 'Discounts' ||
' ' || 'Performance%' || ' ' || 'Errors?');
FOR person_rec IN pack.PKG_PER_CUR
LOOP
pack.pkg_compute_perform(person_rec, one_perform);
cursale := TO_CHAR(one_perform.current_sales);
disct := TO_CHAR(pack.pkg_comp_discounts(one_perform.current_sales));
perf := TO_CHAR(one_perform.perform_percent);
dbms_output.put_line(one_perform.person_code ||
' ' || one_perform.person_name || ' ' || cursale ||
' ' || disct || ' ' || perf || ' ' || one perform.status);
END LOOP;
dbms_output.put_line('Pkg load date seconds ' ||
TO_CHAR(pack.DATE_LOADED, ' SSSSS'));
dbms_output.put_line('System date seconds ' ||
TO CHAR(SYSDATE, ' SSSSS'));
END;
/
Для курсора PKG_PER_CUR требуется указывать тип возвращаемого значения. Коэффициенты скидок скрыты внутри пакета, чтобы его пользователи не могли их модифицировать. DATE_LOADED — это переменная пакета.