Don't use PL/SQL to Do the Job of SQL

Several days ago, I was asked to review scripts from development team for an application consolidation effort. There is a particular script that uses PL/SQL, which run more than 3 hours. After I reviewed it, I believed they can be written by SQL statment.

For example, for the following PL/SQL block, it depends on the EXCEPTION condition to update a table. Though I am not an experienced PL/SQL programmer, I am suspicous this could be considered good practice in PL/SQL.

-- PL/SQL block

BEGIN
   FOR bas_rec IN bas_cur LOOP
      v_my_account_id:=bas_rec.my_account_id;

      BEGIN 
  SELECT DISTINCT ms.state_id INTO v_state_id
    FROM
      my_sch.tab_ms ms
    WHERE
      ms.account_id = v_my_account_id AND
      ms.state_id IS NOT NULL AND
      ms.svc_node_type_id  NOT IN (203,204,206,208,218,402) AND
      ms.is_pq ='N' AND
      ms.svc_status_id = 2;
     
  EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      UPDATE my_sch.conv_2 bas
        SET bas.to_be_converted_status=v_exclusion_flag, 
     bas.exclusion_reason = v_exclusion_reason_multi, 
     bas.is_processed = v_processed_flag
        WHERE bas.my_account_id = v_my_account_id;
        v_is_updated:='Y';
      COMMIT;
    WHEN NO_DATA_FOUND THEN NULL; 
    WHEN OTHERS THEN 
    dbms_output.put_line('Multi State block OTHERS - v_ban:'||sqlerrm ); 
  COMMIT;
      END; 
  END LOOP;
END;

Anyway, I know that " Don't use PL/SQL to Do the Job of SQL". So I translated the above PL/SQL to the following SQL.

-- SQL code 

UPDATE my_sch.conv_2 bas
SET bas.to_be_converted_status='N', 
    bas.exclusion_reason = 'SERVICES IN MULTIPLE STATES', 
    bas.is_processed ='Y' 
WHERE bas.is_processed is null
  AND 1 < ( select count( DISTINCT ms.state_id)
      from my_ord.tab_ms ms
       WHERE ms.account_id =  bas.my_account_id
  AND ms.state_id IS NOT NULL 
  AND ms.svc_node_type_id  NOT IN (203,204,206,208,218,402) 
  AND ms.is_pq ='N' 
  AND ms.svc_status_id = 2 )
;

I also re-wrote the other part of the script with SQL. In a small scale test, original PL/SQL took 5 min. My SQL code took about 1.5 min. Sadly, developers are more comfortable with their PL/SQL code and are unwilling to do a thoroug test and verification about SQL method. So I will still use their PL/SQL code in the production implementation - just running 5 threads of them to speed up instead of 1 thread previously.

DDL for CONSTRAINTS and DEFAULTS

When using a CTAS to create a table from an existing table, the new table won't have all the constraints as the exsiting table except that the NOT NULL constraints are preserved. Below is an example showing a CHECK constraint is not presented in a table created from CTAS:

TESTDB> create table t (id number not null, val number check (val > 5));
 
Table created.
 
 
TESTDB> insert into t values(1,7);
 
1 row created.
 
TESTDB> commit;
 
Commit complete.
 
TESTDB> insert into t values (2,4);
insert into t values (2,4)
*
ERROR at line 1:
ORA-02290: check constraint (OPS$MYUSRID.SYS_C001214102) violated
 
 
TESTDB> desc t;
 Name                  Null?    Type     
 --------------------- -------- --------
 ID                    NOT NULL NUMBER
 VAL                            NUMBER
 
TESTDB> create table t1 as select * from t;
 
Table created.
 
TESTDB> desc t1;
 Name                 Null?    Type
 -------------------- -------- ----------
 ID                   NOT NULL NUMBER
 VAL                           NUMBER
 
TESTDB> insert into t1 values (1, 4);
 
1 row created.
 


TESTDB> ;
  1* select owner, constraint_name, constraint_type, table_name, search_condition from user_constraints
TESTDB> /
 
OWNER        CONSTRAINT_NAME                C TABLE_NAME SEARCH_CONDITION
------------ ------------------------------ - ---------- ------------------------------
OPS$MYUSRID  SYS_C001214102                 C T          val > 5
OPS$MYUSRID  SYS_C001214101                 C T          "ID" IS NOT NULL
OPS$MYUSRID  SYS_C001214103                 C T1         "ID" IS NOT NULL

To create the constraints in the new table, I thus faced a task to extract all the DDL about constraints from the existing table. The approach I adopted after several tests was as follows.

1. First, I created a table my_constraints to have CLOB datatype for search_conditon column since we can not use LIKE operater on a LONG type column.

create table my_constraints ( 
    owner varchar2(30)
   , constraint_name varchar2(30)
   , constraint_type varchar2(1)
   , table_name varchar2(30)
   , search_condition clob
   ) ;

   insert into my_constraints
   select owner, constraint_name, constraint_type, table_name, to_lob(search_condition)
     from dba_constraints 
   where owner =&owner ;

2. For PK, UK and check constraints ( excluding NOT NULL), I used the following command to extract the DDL:

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', t.constraint_name , t.owner ) ddl_string
  from my_constraints t
 where table_name = upper('&table_name')
  and  owner=upper('&owner')
and constraint_type in ('P', 'U','C')
and ( search_condition is null
      or ( search is not null and search_condition not like '%NOT NULL')
    )
;

3. For FK constraints, I used the following command to extract the DDL

SELECT to_char(DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT', table_name, owner)) DDL
  FROM (SELECT DISTINCT b.table_name, b.owner
           from dba_constraints b
           where b.constraint_type = 'R'
           AND b.owner = upper('&owner')
           AND b.table_name = upper('&table_name')) ;

CTAS also does not preserve DEFAULT values of columns. I used the following to obtain the 'ALTER TABLE' statement for DAFAULTS:

BEGIN
        FOR r IN (
             select owner, table_name, column_name, data_type, data_default
               from dba_tab_columns
             where owner = upper('&owner')
               and table_name = upper('&table_name')
               and data_default is not null
        )
        LOOP
                DBMS_OUTPUT.PUT_LINE
                ( 'ALTER TABLE ' || r.owner || '.' || r.table_name  ||
                 ' modify  ' ||r.column_name ||
                 ' default ' || r.data_default || ';');
        END LOOP;
END;
/

The reason we need to use PL/SQL instead of SQL is that DATA_DEFAULT column is LONG type. We can not use '||' with LONG type column.

Move table to different tablespace through online redefinition

Some times ago, I tested the steps of moving two tables to different tablespace through online redefiniton. I developed the steps based on the Oracle online documentation.

The background is that we need to do an online copy of a production database with subset data to another data center. These two tables are critical and need to be copied over and would be used to build other tables. However, these two tables reside in a tablespace hosting many other tables. Thus to put them into a separate tablespace would make our copy job much easier.

Below describes the steps and commands involved for reference. TAB_MA and TAB_MS are under schema SCH_SO. The tablespace to which we want to move them is called SCH_SO_DATA_R. (Those are not real production table names)

Step:
~~~~~

1. Verify that the table is a candidate for online redefinition

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('sch_so','tab_ma',
dbms_redefinition.cons_use_pk);
END;
/


BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('sch_so','tab_ms',
dbms_redefinition.cons_use_pk);
END;
/


2. Create an interim tables

create table sch_so.tab_ma_int
tablespace sch_so_data_r
as
select * from sch_so.tab_ma
where 1=0;


create table sch_so.tab_ms_int
tablespace sch_so_data_r
as
select * from sch_so.tab_ms
where 1=0;


3. Start the redefinition process.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
'sch_so', 'tab_ma','tab_ma_int',
)
END;
/


BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
'sch_so', 'tab_ms','tab_ms_int',
)
END;
/


Note:
After redefinition finished, checked the segment size of interim tables:

1 select owner, segment_name, tablespace_name, bytes/1024/1024/1024 size_G from dba_segments
2* where tablespace_name='SCH_SO_DATA_R'
SQL> /

OWNER SEGMENT_NAME TABLESPACE_NAME SIZE_G
---------------- ----------------------- ----------------- ----------
SCH_SO TAB_MA_INT SCH_SO_DATA_R 4.9375
SCH_SO TAB_MS_INT SCH_SO_DATA_R 9.1875

time: 20 min

4. Create any triggers, indexes and constraints on the interim tables


5. Optionally, synchronize the interim table hr.int_admin_emp.

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('sch_so', 'tab_ms', 'tab_ms_int');
END;
/

6. Complete the redefinition.

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('sch_so', 'tab_ms', 'tab_ms_int');
END;
/


BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('sch_so', 'tab_ma', 'tab_ma_int');
END;
/

Note: The table is locked in the exclusive mode only for a small window toward the end of this step. After this call the table is redefined such that it has all the attributes of the interim table.

7. Drop the interim table.

session_cached_cursors and parse

The following  test case demonstrated the effects of session_cached_cursors on whether and how the PL/SQL caches cursors. I tested with either session_cached_cursors=0 or 20 and with a static SQL or an 'execute immediate' statement in a PL/SQL procedure, in a 9.2.0.8 database and a 10.2.0.1 database, respectively.

create or replace procedure  proc_p
is
obj_no number;
begin
for i in 1..100
loop
execute immediate 'select count(*)  from all_objects where object_id = :1 ' into obj_no using i;
dbms_output.put_line('i='  i  ' count='  obj_no);
end loop;
end;
/


create or replace procedure  proc_p2
is
obj_no number;
begin
for i in 1..100
loop
select count(*) into obj_no from all_objects where object_id = i;
dbms_output.put_line('i='  i  ' count='  obj_no);
end loop;
end;
/

alter session set sql_trace=true;

-- alter session set session_cached_cursors=0;
alter session set session_cached_cursors=20;

exec proc_p;
exec proc_p2;

alter session set sql_trace=false;
exit;

I used TKPROF to analyse the trace files. Below is the summary:

----------------------------------------------------------------------------
version  session_cached_cursors    SQL          # of parse    # of execution
----------------------------------------------------------------------------
9.2.0.8      0                     exe immed    100            100
9.2.0.8      0                     static       100            100
9.2.0.8      20                    exe immed    100            100
9.2.0.8      20                    static       1              100
10.2.0.1     0                     exe immed    100            100
10.2.0.1     0                     static       100            100
10.2.0.1     20                    exe immed    1              100
10.2.0.1     20                    static       1              100
----------------------------------------------------------------------------

It can be seen that with session_cached_cursors=0, no matter in which version and with what kind of SQL, Oracle needs to at least soft parse each SQL for each execution. So session_cached_cursors=0 effectively disable the ability of PL/SQL to cache cursors. With session_cached_cursors=20 (nozero essentially),in 9.2.0.8, for the static SQL it can be achieved "parase once, execute many times"; in 10.2.0.1, for both type of SQL, same effect of "parse once, execute many times" is observed. Whenever possible, we should use static SQL in PL/SQL code; if not, we should try to use 'execute immediate' with bind variables. It seems from 10g, PL/SQL engine can cache cursors from 'execute immediate' statement also.

I continued to experiment about the session_cached_cursors and the number of SQL that is repeatedly executed. The test code looks like:

create or replace procedure  proc_p2
is
obj_no number;
obj_name all_objects.object_name%type;
begin
for i in 1..100
loop
-- sql a
select count(*) into obj_no from all_objects where object_id = i; 
-- sql b
select max(object_name) into obj_name from all_objects where rownum <= i;
-- sql c 
select min(object_name) into obj_name from all_objects where rownum <= i;
end loop;
end;
/

alter session set sql_trace=true;

-- alter session set session_cached_cursors=1;
alter session set session_cached_cursors=3;

exec proc_p2;

alter session set sql_trace=false;
exit;

I tested with session_cached_cursors from 1 to 3 and number of SQL from 1-3 as well. Here is the summary:

-------------------------------------------------------------------------
version  session_cached_    # static SQL      # of parse    # of execution
cursors
-------------------------------------------------------------------------
10.2.0.1     1                      1                1             100
10.2.0.1     1                      2   (a)          100           100
                                        (b)          100           100
10.2.0.1     2                      2   (a)          100           100
10.2.0.1                                (b)          100           100
10.2.0.1     3                      2   (a)          1             100
                                        (b)          1             100
10.2.0.1     3                      3   (a)          100           100
                                        (b)          100           100
                                        (c)          100           100
-------------------------------------------------------------------------

In the case of session_cached_cursors=3 and three static SQLs, in the above test, I repeatedly executed all three SQLs together 100 times. How about repeatedly executing each SQL 100 times, for three SQLs total?. How about picking up the next SQL to be executed randomly? Can increase session_cached_cursors really help?. Below are the test codes and some results.

Test code of excuting each static SQL 100 times, for three SQLs total:

create or replace procedure  proc_p2
is
obj_no number;
obj_name all_objects.object_name%type;
begin
for i in 1..100
loop
-- sql a
select count(*) into obj_no from all_objects where object_id = i; 
end loop;     

for i in 1..100
loop  
-- sql b
select max(object_name) into obj_name from all_objects where rownum <= i;
end loop;

for i in 1..100
loop 
-- sql c 
select min(object_name) into obj_name from all_objects where rownum <= i;
end loop;
end;
/

alter session set sql_trace=true;

-- alter session set session_cached_cursors=1;
alter session set session_cached_cursors=3;

exec proc_p2;

alter session set sql_trace=false;
exit;

Test code of picking next static SQL to be executed randomly:

create or replace procedure  proc_p2
is
obj_no number;
obj_name all_objects.object_name%type;
r number;
begin
for i in 1..300
loop
r := dbms_random.value; 
if ( r <= 0.3333 ) then
select count(*) into obj_no from all_objects where object_id = i; 
elsif ( r >= 0.6667 ) then
select max(object_name) into obj_name from all_objects where rownum <= i;
else 
select min(object_name) into obj_name from all_objects where rownum <= i;
end if;
end loop;     
end;
/
show errors;

alter session set sql_trace=true;

-- alter session set session_cached_cursors=1;
alter session set session_cached_cursors=20;

exec proc_p2;

alter session set sql_trace=false;
-- exit;

Here is the result from TKPROF for above two cases:

------------------------------------------------------------------------
Order      session_cached_    # static SQL    # of parse  # of execution
cursors
------------------------------------------------------------------------
Each SQL
100 times      3                   3   (a)         1          100
                                       (b)         1          100
                                       (c)         1          100
Random         3                   3   (a)        33           90
                                       (b)        33          104
                                       (c)        33          106
Random         20                  3   (a)         1           96
                                       (b)         1          104
                                       (c)         1          100
------------------------------------------------------------------------