How to check the execution record time of stored procedure in Oracle query

In Oracle, you can query the stored procedure execution record time through the following method: use the DBMS_PROFILER package to obtain execution statistics. Use the V$PROFILER view to get statistics on executing SQL statements and PL/SQL units. Use Oracle Monitor to view related events in the session event history. Use the AUDIT plug-in to audit stored procedure execution and obtain information in the audit file.

How to query the stored procedure execution record time in Oracle

In Oracle, you can query the storage through the following method Process execution record time:

1. Use the DBMS_PROFILER package

The DBMS_PROFILER package provides the function of viewing stored procedure execution statistics, including execution time. To use this package:

<code class="sql">BEGIN
  -- 开始剖析
  DBMS_PROFILER.START_PROFILING;

  -- 执行您的存储过程
  -- ...

  -- 结束剖析并获取结果
  DBMS_PROFILER.END_PROFILING;
END;</code>

The results will be stored in the DBMS_PROFILER.RESULTS table, which you can query to get the execution time of the stored procedure:

<code class="sql">SELECT * FROM DBMS_PROFILER.RESULTS
WHERE OBJECT_TYPE = 'PROCEDURE'
AND OBJECT_NAME = '您的存储过程名称';</code>

2. Using V$ PROFILER view

The V$PROFILER view provides statistical information about SQL statements and PL/SQL units being executed in the current or most recently run session. To use this view:

<code class="sql">SELECT * FROM V$PROFILER
WHERE OBJECT_TYPE = 'PROCEDURE'
AND OBJECT_NAME = '您的存储过程名称';</code>

3. Using Oracle Monitor

Oracle Monitor is a graphical user interface (GUI) that allows you to view Various statistics, including stored procedure execution time. To use Oracle Monitor:

• Start Oracle Monitor and connect to your database.
• Expand the "Session" panel and select the session that executes the stored procedure.
• In the Event History tab, find events related to your stored procedure execution.

4. Using the AUDIT plug-in

The AUDIT plug-in allows you to audit activity in your database, including the execution of stored procedures. To use the AUDIT plug-in:

<code class="sql">-- 启用 AUDIT 插件
ALTER SYSTEM SET AUDIT_TRAIL='DB' SCOPE=SPFILE;

-- 启用存储过程审核
AUDIT EXECUTE ON PROCEDURE BY PUBLIC;

-- 执行您的存储过程
-- ...

-- 禁用存储过程审核
AUDIT EXECUTE ON PROCEDURE BY PUBLIC REVOKE;

-- 禁用 AUDIT 插件
ALTER SYSTEM SET AUDIT_TRAIL='NONE' SCOPE=SPFILE;</code>

You can find information about the execution time of a stored procedure in the audit file.

The above is the detailed content of How to check the execution record time of stored procedure in Oracle query. For more information, please follow other related articles on the PHP Chinese website!