измерить время оператора sql в процедуре в plsql

Я должен написать процедуру, которая сохранит время выполнения любого sql-оператора в таблице.

Процедура вызывает exec measuresqltime('sql statement as string');

Моя идея такая:

  --declarations 
  timestart NUMBER;
  BEGIN 
    dbms_output.enable; 
    timestart:=dbms_utility.get_time(); 
    EXECUTE IMMEDIATE sql
    COMMIT; 
    dbms_output.put_line(dbms_utility.get_time()-timestart); 
    -- save time

Но для пункта SELECT *... это не сработало. (Я думаю, что sql нужен INTO-порядок)

Есть ли способ выполнить какие-либо sql-атрибуты в процедуре?


oracle plsql procedure
person sheepy    schedule 20.01.2012    source источник

Ответы (4)


arrow_upward
16
arrow_downward

Если ваш оператор SQL является оператором SELECT, вам необходимо выполнить выборку из курсора, чтобы иметь значимое измерение времени его выполнения.

Если вы не выполняете выборку из курсора, вы измеряете только время, затраченное на фазы «синтаксического анализа» и «выполнения», тогда как большая часть работы обычно выполняется на фазе «выборки» для операторов SELECT.

Вы не сможете выполнить выборку с помощью EXECUTE IMMEDIATE или OPEN cursor FOR 'string', если не знаете, сколько столбцов будет иметь фактический оператор. Вам нужно будет использовать пакет динамического SQL DBMS_SQL, если число / тип столбцов SELECT неизвестен.

Вот пример:

SQL> CREATE OR REPLACE PROCEDURE demo(p_sql IN VARCHAR2) AS
  2     l_cursor  INTEGER;
  3     l_dummy   NUMBER;
  4     timestart NUMBER;
  5  BEGIN
  6     dbms_output.enable;
  7     timestart := dbms_utility.get_time();
  8     l_cursor  := dbms_sql.open_cursor;
  9     dbms_sql.parse(l_cursor, p_sql, dbms_sql.native);
 10     l_dummy := dbms_sql.execute(l_cursor);
 11     LOOP
 12        EXIT WHEN dbms_sql.fetch_rows(l_cursor) <= 0;
 13     END LOOP;
 14     dbms_sql.close_cursor(l_cursor);
 15     dbms_output.put_line(dbms_utility.get_time() - timestart);
 16  END;
 17  /

Procedure created.

SQL> exec demo('SELECT * FROM dual CONNECT BY LEVEL <= 1e6');
744

PL/SQL procedure successfully completed.

Обратите внимание, что это будет измерять время, необходимое для выборки до последней строки SELECT.

person Vincent Malgrat    schedule 20.01.2012
comment
эй, выглядит здорово :) но это работает только с операторами выбора, не так ли? Есть ли возможность запустить операторы удаления или вставки? - person sheepy; 24.01.2012
comment
@sheepy: вы не можете получить курсор DML (INSERT / UPDATE ...), поэтому строка 12 в этом случае вызовет ошибку. Перехватите исключение, и процедура будет работать как для запросов DML, так и для запросов SELECT. - person Vincent Malgrat; 24.01.2012
comment
dbms_utility.get_time нельзя доверять, и вместо этого вам следует использовать systimestamp, см. asktom.oracle.com/pls/asktom/ - person Superdooperhero; 08.05.2018
comment
@Superdooperhero Это совсем не то, что предлагает Том Кайт! Он предлагает заменить SYSDATE на SYSTIMESTAMP для большей точности. Он по-прежнему использует dbms_utility.get_time в предложенном им коде :) - person Vincent Malgrat; 08.05.2018

arrow_upward
4
arrow_downward

завершение devosJava ответил ... не используйте его на рассвете; P

PROCEDURE MY_PROCEDURE IS
  timeStart  TIMESTAMP;
  timeEnd    TIMESTAMP;
  timeSecond NUMBER
BEGIN
  timeStart  := SYSTIMESTAMP;

  -- YOUR CODE HERE

  timeEnd    := SYSTIMESTAMP;
  timeSecond :=((extract(hour from timeEnd)*3600)+(extract(minute from timeEnd)*60)+extract(second from timeEnd))-((extract(hour from timeStart)*3600)+(extract(minute from timeStart)*60)+extract(second from timeStart));
  dbms_output.put_line('finished: '||timeSecond||' seconds');
END MY_PROC;
person Tiago Oliveira    schedule 16.04.2015

arrow_upward
1
arrow_downward

Чтобы рассчитать продолжительность для времени выполнения

PROCEDURE MY_PROCEDURE IS
  timeStart  TIMESTAMP;
  timeEnd    TIMESTAMP;
BEGIN
  timeStart  := SYSTIMESTAMP;

  -- YOUR CODE HERE

  timeEnd    := SYSTIMESTAMP;

  INSERT INTO PROC_RUNTIMES (PROC_NAME, START_TIME, END_TIME)
    VALUES ('MY_PROCEDURE ', timeStart  , timeEnd    );
END MY_PROC;
person devosJava    schedule 30.05.2013

arrow_upward
0
arrow_downward

INSERT INTO PROC_RUNTIMES (PROC_NAME, START_TIME, END_TIME) VALUES ('PROC_NAME', TO_CHAR (SYSDATE, 'DD / MM / YYYY HH24: MI: SS'), NULL);

Ваш запрос здесь;

INSERT INTO PROC_RUNTIMES (PROC_NAME, START_TIME, END_TIME) VALUES ('PROC_NAME', NULL, TO_CHAR (SYSDATE, 'DD / MM / YYYY HH24: MI: SS'));

person Ola    schedule 07.12.2015