测量plsql过程中sql语句的时间

发布于 2024-12-27 22:11:29 字数 488 浏览 0 评论 0原文

我必须编写一个过程来保存表中任何 sql 语句的执行时间。

该过程由 execmeasuresqltime('sql statements 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 数据？

原文

I must write a procedure which save the execute time of any sql-statement in a table.

The procedure is calling by 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)

Is there a way to execute any sql-atatements in a procedure?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

如梦 2025-01-03 22:11:29

如果您的 SQL 语句是 SELECT，则需要从游标中获取数据以对其执行时间进行有意义的测量。

如果不从游标中获取，则仅测量“解析”和“执行”阶段所花费的时间，而大部分工作通常在 SELECT 语句的“获取”阶段完成。

如果您不知道实际语句将具有的列数，您将无法使用 EXECUTE IMMEDIATE 或 OPENcursor FOR 'string' 进行获取。您必须使用动态 SQL 包 DBMS_SQL< /a> 如果 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 的最后一行所需的时间。

If your SQL statement is a SELECT, you need to fetch from the cursor to have a meaningful measure of its execution time.

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.

You won't be able to fetch with EXECUTE IMMEDIATE or 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_SQL if the number/type of columns of the SELECT is unknown.

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.

回复收藏 0 原文

风尘浪孓 2025-01-03 22:11:29

完成 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;

completing devosJava answered... avoid using it at dawn ;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;

回复收藏 0 原文

二智少女猫性小仙女 2025-01-03 22:11:29

计算执行时间的持续时间

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;

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;

回复收藏 0 原文