oracle 测量 plsql 过程中 sql 语句的时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8939849/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
measure time of an sql statement in a procedure in plsql
提问by sheepy
I must write a procedure which save the execute time of any sql-statement in a table.
我必须编写一个过程来保存表中任何 sql 语句的执行时间。
The procedure is calling by exec measuresqltime('sql statement as string');
该程序正在调用 exec measuresqltime('sql statement as string');
My idea is like this:
我的想法是这样的：
--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
But it didn't work for me for a SELECT *...clause. (I think sql need a INTO-order)
但它对我的SELECT *...条款不起作用。（我认为 sql 需要一个 INTO 顺序）
Is there a way to execute any sql-atatements in a procedure?
有没有办法在过程中执行任何 sql-atatements？
回答by Vincent Malgrat
If your SQL statement is a SELECT, you need to fetch from the cursor to have a meaningful measure of its execution time.
如果您的 SQL 语句是 SELECT，则需要从游标中获取数据以对其执行时间进行有意义的测量。
If you don't fetch from the cursor, you only measure the time spent in "parse" and "execution" phases, whereas much of the work is usually done in the "fetch" phase for SELECT statements.
如果您不从游标中获取，则只测量在“解析”和“执行”阶段花费的时间，而大部分工作通常在 SELECT 语句的“获取”阶段完成。
You won't be able to fetch with EXECUTE IMMEDIATEor OPEN cursor FOR 'string'if you don't know the number of columns the actual statement will have. You will have to use the dynamic SQL package DBMS_SQLif the number/type of columns of the SELECT is unknown.
您将无法与获取EXECUTE IMMEDIATE或者OPEN cursor FOR 'string'，如果你不知道的列数的实际语句都会有。DBMS_SQL如果 SELECT 的列数/类型未知，您将不得不使用动态 SQL 包。
Here's an example:
下面是一个例子：
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.
Note that this will measure the time needed to fetch to the last row of the SELECT.
请注意，这将测量获取到 SELECT 的最后一行所需的时间。
回答by Tiago Oliveira
completing devosJava answered... avoid using it at dawn ;P
完成 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;
回答by devosJava
To calculate the duration for an execution time
计算执行时间的持续时间
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;
回答by Ola
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', TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'),NULL);
Your query here;
您的查询在这里；
INSERT INTO PROC_RUNTIMES (PROC_NAME, START_TIME, END_TIME) VALUES ('PROC_NAME',NULL, TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
INSERT INTO PROC_RUNTIMES (PROC_NAME, START_TIME, END_TIME) VALUES ('PROC_NAME',NULL, TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
