sql - Oracle:在包和过程中设置 session 参数

Question

我是一名 SQL Server DBA，目前正在快速了解 Oracle。我正在尝试为 SQL Server 但为 Oracle 创建与 sp_WhoIsActive 非常相似的东西，而无需重新发明轮子。本质上我所做的就是从 v$session 中选择一些值并将它们插入到表中(穷人的 ASH/AWR)。

在 Oracle 12.1 中，查询字典 View 时似乎存在一个错误，由于解析逻辑错误，查询可能会花费很长时间(错误 22225899:复杂查询的缓慢解析)。解决方法是设置 session 参数:

更改 session 设置“_optimizer_squ_bottomup”=false；

在 T-SQL 中，我可以非常轻松地在 session 中执行存储过程并在运行时设置此变量。但在 Oracle 中，情况似乎并非如此。

示例代码:

CREATE OR REPLACE PROCEDURE SP_DB_ACTIVITY
(
    v_temp NUMBER :=1
) IS
BEGIN
alter session set "_optimizer_squ_bottomup"=false;
INSERT INTO SY_DB_ACTIVITY 
    SELECT  
       <fields>
    FROM
      v$session;
commit;

当我运行这个时，我收到错误:

“PLS-00103:在预期以下其中一项时遇到符号“ALTER”...”

现在，我知道如何做到这一点的唯一方法是通过像 SQL Plus 这样的实用程序来启动交互式用户 session 。谁能告诉我 Oracle 如何处理这种情况？我想将其捆绑到 SP 或包中，然后从 Oracle Scheduler 调用它。

Answer 1

这是一个如何在过程内部执行更改 session 的简单示例:

CREATE PROCEDURE SP_DB_ACTIVITY IS
BEGIN

EXECUTE IMMEDIATE 'alter session set "_optimizer_squ_bottomup"=false';

END;
/

以下是将其与 select 和 insert 语句结合起来的方法:

CREATE OR REPLACE PROCEDURE SP_DB_ACTIVITY
(v_temp IN number) AS 

    v_Id NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'alter session set "_optimizer_squ_bottomup"=false';

    SELECT 1 
    INTO v_Id
    FROM dual;

    INSERT INTO SY_DB_ACTIVITY (id) VALUES(v_Id); 
 END SP_DB_ACTIVITY; 
 /

Here is a small DEMO您可以在其中看到调用它时过程会做什么以及如何调用它。另外，在此示例中，您将使用 IN 参数调用过程。因此，您可以使用该参数进行某些操作，在上面的示例中是没有任何参数的过程...

当然，您也可以直接插入表中:

CREATE OR REPLACE PROCEDURE SP_DB_ACTIVITY
(v_temp IN number) AS 

    v_Id NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'alter session set "_optimizer_squ_bottomup"=false';

    INSERT INTO SY_DB_ACTIVITY(id) 
    select 1
    from dual; 

 END SP_DB_ACTIVITY; 
 /