CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloperoracle12c
Chase Marler
Chase Marler

Reputation: 43

Oracle: Setting Session Parameters in Packages and Procedures

I'm a SQL Server DBA currently getting up to speed on Oracle. I'm trying to create something very similar to sp_WhoIsActive for SQL Server but for Oracle without reinventing the wheel. Essentially all I'm doing is selecting some values from v$session and inserting them into a table (poor man's ASH/AWR).

It would seem that in Oracle 12.1, there's a bug when querying dictionary views where it can take forever due to bad parsing logic (Bug 22225899 : SLOW PARSE FOR COMPLEX QUERY). The work-around is to set a session parameter:

alter session set "_optimizer_squ_bottomup"=false;

In T-SQL, I could very easily execute a stored procedure in-session and set this variable at runtime. However in Oracle, it wouldn't seem thats the case.

Sample Code:

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;

When I run this, I get the error:

"PLS-00103: Encountered symbol 'ALTER' when expecting one of the following..."

Right now, the only way I know how to do this is via a utility like SQL Plus that initiates an interactive user session. Can anyone give me some direction as to how Oracle handles this situation? I'd like to bundle this up into a SP or a Package and then call it from Oracle Scheduler.

Upvotes: 3

Views: 4891

Answers (1)

VBoka
VBoka

Reputation: 9083

Hre is a simple example how to execute alter session inside of the procedure:

CREATE PROCEDURE SP_DB_ACTIVITY IS
BEGIN

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

END;
/

Here is the way you can combine that with your select and insert statement:

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 where you can see what will procedure do when you call it and how you can call it. Also, in this example you are calling procedure with and IN parameter. So you can use that parameter for something and in the example above is the procedure without any parameters...

You can also, of course, insert into table directly:

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; 
 /

Upvotes: 7

Related Questions