Bump up sequences

I got a requirement to bump up more than 200 sequences in a database. Initially I developed SQL scripts to bump up based on the difference between the target value and the dba_sequneces.last_number column for each of the sequences. However, I soon found out that the last_number does not necessarily equal to the seq.nextval. This is apparently due to the cache_size option. For example:

TESTDB> create sequence temp_seq;

Sequence created.

TESTDB> select temp_seq.nextval from dual;

   NEXTVAL
----------
         1

TESTDB> select temp_seq.nextval from dual;

   NEXTVAL
----------
         2

TESTDB> select SEQUENCE_NAME, cache_size, last_number from user_sequences;

SEQUENCE_NAME                  CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
TEMP_SEQ                               20          21

I finally decdied to adopt a PL/SQL solution. I first created a helper table (seq_bump), which stores the target values. Then, I created the following PL/SQL procedure:

declare
  cursor c is 
   select seq_owner, seq_name, bump_number 
   from seq_bump;

  stmt varchar2(300);
  l_seq_owner seq_bump.seq_owner%type;
  l_seq_name seq_bump.seq_name%type;
  l_bump_number seq_bump.bump_number%type;
  l_nextval number;
  l_inc   number;
  l_count number :=0;
begin
  open c;
  loop
   -- l_count := l_count + 1;
   --  dbms_output.put_line('**** conunter = ' || l_count );
    fetch c into l_seq_owner, l_seq_name, l_bump_number;
    stmt:= 'select ' || l_seq_owner || '.' || l_seq_name || '.nextval from dual';
    execute immediate stmt into l_nextval;
   --  dbms_output.put_line('next values is ' || l_nextval);
    l_inc := l_bump_number - l_nextval;
    stmt:= 'alter sequence ' || l_seq_owner || '.' || l_seq_name || ' increment by ' || l_inc ;
   --  dbms_output.put_line(stmt);
    execute immediate stmt;
    stmt:= 'select ' || l_seq_owner || '.' || l_seq_name || '.nextval from dual';
  --  dbms_output.put_line(stmt);
    execute immediate stmt into l_nextval;
    stmt:= 'alter sequence ' || l_seq_owner || '.' || l_seq_name || ' increment by 1 ';
    execute immediate stmt;
    exit when c%notfound;
  end loop;
  close c;
end;
/

I tested above procedure and this will be implemented in production this weekend, hopefully it will work as I expect.

Demonstrate a function-based index is unable to be used due to cursor_sharing setting

rem  Demonstrate a function-based index is unable to be used
rem  due to cursor_sharing setting
rem
rem  The function is substr(col, 1,6), when cursor_sharing = 
rem  FORCE or SIMILAR, the literals, i,e, 1 and 6 are replaced 
rem  by system-generated variables. This may casued mis-match 
rem  against the index definition
rem
rem  When we use autotrace to check the exection plan,
rem  we do see the index is used; however, at run time, this is 
rem  not the case as shown by 10046 trace. 
rem
rem  Test env: 9.2.0.8 

set echo on
spool fun_idx

drop table t;
create table t as
select rownum id,
       object_name name,
       rpad('x',8) val
from all_objects 
where rownum <=2000;

create index t_idx on t(substr(name, 1,6));
exec dbms_stats.gather_table_stats(user,'t', cascade=>true);

select * from v$version;

alter session set tracefile_identifier=fun;
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

alter session set cursor_sharing=force;
select /* force */ * 
from t 
where substr(name,1,6)='ABCDEF';

alter session set cursor_sharing=similar;
select /* similar */ * 
from t 
where substr(name,1,6)='ABCDEF';

alter session set cursor_sharing=exact;
select /* exact */ * 
from t 
where substr(name,1,6)='ABCDEF';

exit;


set doc off
doc

--- force  ----

select /* force */ *
from t
where substr(name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         13          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0         13          0           0
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 183
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL T

--- similar ----
 
select /* similar */ *
from t
where substr(name,:"SYS_B_0",:"SYS_B_1")=:"SYS_B_2"
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         13          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.00          0         13          0           0
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 183
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS FULL T

--- exact ---

select /* exact */ *
from t
where substr(name,1,6)='ABCDEF'
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          2          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          2          0           0
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 183
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TABLE ACCESS BY INDEX ROWID T
      0   INDEX RANGE SCAN T_IDX (object id 224130)
# 

May 11, 2010 updated: Original post ended at the previous line, all below is added today:

I was pinged by one of my colleagues the other day saying "Jonathan Lewis referred to your blog". I was surprized to hear that. Then I found out that Coskan first included this post in his blog , then Jonathan mentioned it in one of his blog posts (footnote section), which describes the same issue but of course includes deeper technical insights. I regarded Jonathan as my mentor secretly :-). First of all, I learned a lot from his blog and website, and his book "Cost-based Oracle Fundenmental" as well. In my LinkedIn profile "Reading List by Amazon" section, I commented "I read it through when I was on bench in Fall 2007. Even 20% of understanding of it helped me gain some respect from app team and peer DBAs by showing I was able to tune sqls . I will read it again and again utill fully digest it hopefully one day. " Secondly, I once listened to a podcast of his interview with DBAZine, in which he emphasized how the approach of building test cases can help one become better DBA or troubleshooter. I kept this in mind and have been trying practise this tip always. The test case I showed  in this post is a proof :-). The style of this test case is actually somewhat mimicing the code examples of the above mentioned book. Especially the "set doc off" syntax, which I did not know until I read his book.

This test case was resulted from my trouble-shooting experience for a real production issue at the posting date. A production database  server CPU utilization reached 100% from time to time from the morning and some jobs were slower than normal. Top SQL during the problem period was an update statement, supposedly using a function-based index. I was called by a fellow DBA to check why full table scan was actually used instead. From the shared pool, we knew it has been executed by FTS .

My favorite tool to check the execution plan at the first round is Autotrace :

** Autotrace showing index scan but consistent gets = 148626

myusrid@MYPRDDB1> set autotrace traceonly
myusrid@MYPRDDB1> select * from
2 XYZ_UVW.ABC_ABCDEFG_ABCDEF_ABCDEF
3 WHERE substr(DEPENDANT_ORDER,1,13) = '123456789012'
4 ;

o rows selected

ecution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=18 Bytes=1494)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ABC_ABCDEFG_ABCDEF_ABCDEF' (Cost=4 Card=18 Bytes=1494)
2 1 INDEX (RANGE SCAN) OF 'ABC_ABCDEFG_ABCDEF_ABCDE_IDX5N' (NON-UNIQUE) (Cost=Card=1)


statistics
---------------------------------------------------------
0 recursive calls
0 db block gets
148626 consistent gets
135926 physical reads
0 redo size
490 bytes sent via SQL*Net to client
229 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


I knew parse-time execution plan could be different from run-time. So from Autotrace, I confirmed index scan was considered favarable, however from the "consistent gets", I knew at run time Oracle did FTS as it matched stackpack "Gets per Execution":


** From statspack during problem period, Gets Per Exec = 149314

Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
98,995,283 663 149,314.2 49.0 ######## 10769.86 3803649956
Module: JDBC Thin Client
UPDATE ABC_ABCDEFG_ABCDEF_ABCDEF SET DEPENDANT_ORDER =:1 WHERE S
UBSTR(DEPENDANT_ORDER,:"SYS_B_0",:"SYS_B_1") =:2

It took me quite a while to realize what happened was due to CURSOR_SHARING setting:

** Autotrace showing index scan and consistent gets = 3 after seting cursor_sharing=exact

myusrid@MYPRDDB1> alter session set cursor_sharing=exact;

Session altered.

myusrid@MYPRDDB1> select * from
2 XYZ_UVW.ABC_ABCDEFG_ABCDEF_ABCDEF
3 WHERE substr(DEPENDANT_ORDER,1,13) = '123456789012'
4 ;

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=18 Bytes=1494)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'ABC_ABCDEFG_ABCDEF_ABCDEF' (Cost=4 Card=18 Bytes=1494)
2 1 INDEX (RANGE SCAN) OF 'ABC_ABCDEFG_ABCDEF_ABCDE_IDX5N' (NON-UNIQUE) (Cost=3 Card=1)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
2 physical reads
0 redo size
490 bytes sent via SQL*Net to client
229 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


I took a brave step to solve the prodution issue as during the middle of the day code change was not possible and also it was subjected to some change management processes in my working envrionment. I told the development team, I was going to change CURSOR_SHARING from FORCE to EXACT in the production database as an emergent performance stablizing measure ( as this change should be subjected to change management process too), and if we saw problems after the change, I would revert it back and they still needed to plan code change to fix that update statment (Dev think they should have used bind variables appropriately, but no one could really gurantee this change would not cause other more serious problems). So I changed it to EXACT, so far so good.

How to find out which session caused ORA-01555?

Some times ago, my colleague asked me this question since we frequently recieved ORA-01555 alert email from our reporting database. The alerts came from the alert log monitoring job, which contains sql text but nothing more than that. I searched the internet and was excited to find a thread on the AskTom site, which appeared to be exactly addressing our needs.

Based on that thread, I tested and installed the following database event trigger in our reporting database:

create or replace trigger servererror_trig
after servererror on database
declare
   var_id        number; 
   var_user      varchar2(30);
   var_osuser    varchar2(30);
   var_machine   varchar2(64);
   var_process   varchar2(8);
   var_program   varchar2(48);
   var_txt       varchar2(4000);
   l_sql_text    ora_name_list_t;
   l_n           number;
begin
  if ( is_servererror(1555) )
  then
     select
      username,
      osuser,
      machine,
      process,
      program
    into
      var_user,
      var_osuser,
      var_machine,
      var_process,
      var_program
    from
      v$session
    where
     audsid=userenv('sessionid');
 
     l_n := ora_sql_txt( l_sql_text );
     var_txt :=  'ora_sysevent = ' || ora_sysevent || ' ora_login_user = ' || ora_login_user || 
                   ' ora_server_error = ' || ora_server_error(1) || chr(10); 
 
     for i in 1 .. l_n
     loop
        var_txt := var_txt || l_sql_text(i); 
     end loop;
 
     insert into
      db_admin.servererror_log
     values(
      dbms_standard.server_error(1),
      sysdate,
      var_user,
      var_osuser,
      var_machine,
      var_process,
      var_program,
      var_txt);
  end if;
end;
/

At begining, it looked good, however, soon I found that not all ORA-01555s that were appeared in the alert log were captured by this trigger. Being aware that some tables in the reporting database are referred by remote SQLs through dblink, I set up a test to see if this trigger workes for remote queries.

First of all, I did everything in the local database:

denis@DBWRKEV1> -- create undo tablespace small datafile  '/tmp/small.dbf' size 2m autoextend OFF;
denis@DBWRKEV1> drop table ora_1555;
 
Table dropped.
 
denis@DBWRKEV1> create table ora_1555 as select * from all_objects;
 
Table created.
 
denis@DBWRKEV1> alter system set undo_tablespace = small;
 
System altered.
 
denis@DBWRKEV1> 
denis@DBWRKEV1> --  Now open a cursor -- it is read consistent as of NOW
denis@DBWRKEV1> variable x refcursor
denis@DBWRKEV1> exec open :x for select * from ora_1555;
 
PL/SQL procedure successfully completed.
 
denis@DBWRKEV1> 
denis@DBWRKEV1> -- generate lots of undo and commit and do it over again and again,
denis@DBWRKEV1> 
denis@DBWRKEV1> begin
  2    for i in 1 .. 10
  3    loop
  4         loop
  5            delete from ora_1555 where rownum <= 1000;
  6            exit when sql%rowcount = 0;
  7            commit;
  8         end loop;
  9             commit;
 10      insert into ora_1555 select * from all_objects;
 11      commit;
 12    end loop;
 13  end;
 14  /
 
PL/SQL procedure successfully completed.
 
denis@DBWRKEV1> 
denis@DBWRKEV1> print x
ERROR:
ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19$" too small
 
 
 
no rows selected
 
denis@DBWRKEV1> 
denis@DBWRKEV1> 
denis@DBWRKEV1> select * from db_admin.servererror_log;
 
ERROR                                                             TIMESTAMP           USERNAME
----------------------------------------------------------------- ------------------- ------------------------------
OSUSER                         MACHINE                                                          PROCESS
------------------------------ ---------------------------------------------------------------- --------
PROGRAM
------------------------------------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
1555                                                              2010-03-19 12:12:01 DENIS
oracle                         sjscqdd1                                                         19823
sqlplus@sjscqdd1 (TNS V1-V3)
ora_sysevent = SERVERERROR ora_login_user = DENIS ora_server_error = 1555
SELECT * FROM ORA_1555
 

Check the alert log, I can find:

Fri Mar 19 12:12:01 2010
ORA-01555 caused by SQL statement below (SQL ID: 0kj3gdzq34dk9, Query Duration=100 sec, SCN: 0x09e8.6a0a74a2):
Fri Mar 19 12:12:01 2010
SELECT * FROM ORA_1555

Now I do a test from remote db:

-- At remote, open a curor for the test table through db link

denis@REMOTEDB>variable x refcursor
denis@REMOTEDB>exec open :x for select * from ora_1555@dbwrkev1;

PL/SQL procedure successfully completed.

-- At local

denis@DBWRKEV1> begin
  2    for i in 1 .. 10
  3    loop
  4         loop
  5            delete from ora_1555 where rownum <= 1000;
  6            exit when sql%rowcount = 0;
  7            commit;
  8         end loop;
  9             commit;
 10      insert into ora_1555 select * from all_objects;
 11      commit;
 12    end loop;
 13  end;
 14  /

PL/SQL procedure successfully completed.

-- At remote

denis@REMOTEDB>print x
ERROR:
ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19$" too small
ORA-02063: preceding line from DBWRKEV1



no rows selected

-- Local alert log (PST)

Fri Mar 19 12:20:11 2010
ORA-01555 caused by SQL statement below (SQL ID: 7xnnz01z88xhp, Query Duration=249 sec, SCN: 0x09e8.6a135bd3):
Fri Mar 19 12:20:11 2010
SELECT "A1"."OWNER","A1"."OBJECT_NAME","A1"."SUBOBJECT_NAME","A1"."OBJECT_ID","A1"."DATA_OBJECT_ID","A1"."OBJECT_TYPE","
A1"."CREATED","A1"."LAST_DDL_TIME","A1"."TIMESTAMP","A1"."STATUS","A1"."TEMPORARY","A1"."GENERATED","A1"."SECONDARY" FRO
M "ORA_1555" "A1"

Note: it is intereting to see Oracle rewrites the original SQL in such a way that the '*' is expanded to full column list and charaters are capitalized.

-- Remote alert log
Not see any 1555, I am a little surprized.

-- at local note the ORA-01555 in alert log at time 12:20 not captured by the trigger.

denis@DBWRKEV1> select * from db_admin.servererror_log;
 
ERROR                                                             TIMESTAMP           USERNAME
----------------------------------------------------------------- ------------------- ------------------------------
OSUSER                         MACHINE                                                          PROCESS
------------------------------ ---------------------------------------------------------------- --------
PROGRAM
------------------------------------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
1555                                                              2010-03-19 12:12:01 DENIS
oracle                         sjscqdd1                                                         19823
sqlplus@sjscqdd1 (TNS V1-V3)
ora_sysevent = SERVERERROR ora_login_user = DENIS ora_server_error = 1555
SELECT * FROM ORA_1555

In conclusion, the database event trigger solution to identify sessions cause ORA-01555 has its limitation when the session is initiated by a remote query through dblink.

Copy schema between databases through transportable tablespace

Today, I worked out a plan to copy a schema from one database to another using transportable tablespace feature.The schema objects only reside in three tablespaces. Below are the steps: (as usual, real schema, server name etc are modified):

1. Check if the tablespace is self-contained

conn / as sysdba
execute dbms_tts.transport_set_check('TST_PCD_DATA_1,TST_PCD_DATA_2,TST_PCD_IDX_1', true);

SELECT * FROM TRANSPORT_SET_VIOLATIONS;

Note: we should see no rows returned.


2. Make the tablespace READ ONLY at source:

alter tablespace TST_PCD_DATA_1 read only;
alter tablespace TST_PCD_DATA_2 read only;
alter tablespace TST_PCD_IDX_1 read only;

select tablespace_name, status from dba_tablespaces where status='READ ONLY';


4. Export the tablespace

exp tablespaces=TST_PCD_DATA_1,TST_PCD_DATA_2,TST_PCD_IDX_1 transport_tablespace=y file=sch1_tts.dmp

Username: / as sysdba


5. Drop the tablespaces in the target database

drop tablespace TST_PCD_DATA_1 including contents and datafiles;
drop tablespace TST_PCD_DATA_2 including contents and datafiles;
drop tablespace TST_PCD_IDX_1 including contents and datafiles;


6. Copy the data files and dump file to target server

select file_name from dba_data_files
where tablespace_name in ('TST_PCD_DATA_1','TST_PCD_DATA_2','TST_PCD_IDX_1');

FILE_NAME
--------------------------------------------------------------------------------
/db1/u01/oradata/sch1stg1/TST_PCD_IDX_1_01.dbf
/db1/u01/oradata/sch1stg1/TST_PCD_DATA_2_01.dbf
/db1/u01/oradata/sch1stg1/TST_PCD_DATA_1_01.dbf

ssh2 srcserv 'cat /db1/u01/oradata/sch1stg1/TST_PCD_IDX_1_01.dbf | gzip -c' | gunzip -c > /db1st2/u07/oradata/trgtdbsid/TST_PCD_IDX_1_01.dbf
ssh2 srcserv 'cat /db1/u01/oradata/sch1stg1/TST_PCD_DATA_2_01.dbf | gzip -c' | gunzip -c > /db1st2/u07/oradata/trgtdbsid/TST_PCD_DATA_2_01.dbf
ssh2 srcserv 'cat /db1/u01/oradata/sch1stg1/TST_PCD_DATA_1_01.dbf | gzip -c' | gunzip -c > /db1st2/u07/oradata/trgtdbsid/TST_PCD_DATA_1_01.dbf



7. Make the tablespace read write at source database

alter tablespace TST_PCD_DATA_1 read write;
alter tablespace TST_PCD_DATA_2 read write;
alter tablespace TST_PCD_IDX_1 read write;

8. At the target host, plug the tablespace into the target database.
From the OS command prompt, issue:

imp parfile=imp_tts.par

-- imp_tts.par --------------------------------------------
tablespaces=TST_PCD_DATA_1,TST_PCD_DATA_2,TST_PCD_IDX_1
transport_tablespace=y
file=sch1_tts.dmp
datafiles=(
'/db1st2/u07/oradata/trgtdbsid/TST_PCD_IDX_1_01.dbf'
,'/db1st2/u07/oradata/trgtdbsid/TST_PCD_DATA_2_01.dbf'
,'/db1st2/u07/oradata/trgtdbsid/TST_PCD_DATA_1_01.dbf'
)
tts_owners=ssp2_sch1
------------------------------------------------------------

Note: enter / as sysdba for username

Best practices and recommendations for preparing SQL Scripts

We have code release for our Oracle databases frequently. Typically it is implemented by executing various SQL or PL/SQL scripits on servers from SQL*Plus.  Many times I have observed there are simple syntax errors in the scripts we received from development team and also the format of the script is not consistent. I thus tried to compile a list of best practices and recommendations for preparing the release scripts for the team. Here are some points:

• Add spool to every script in the following format as minimum:

             set echo on timing on
             spool log/script_name.log
              ...
             spool off

• Separate DDL and DML SQL statements into different scripts.

              Recommend using suffix to distinguish DDL and DML script: e.g. script_ddl.sql, script_dml.sql.

• Separate different type of object creation into different scripts:

           i.e. table, index, package etc. Recommend using the following suffix to distinguish different script in   the format: script_xxx.sql. The ‘xxx’can be:

                      Package specification              pks
                      Package body                         pkb
                      Package sepc and body          pkg
                      Procdueure                             pro
                      Trigger                                    trg
                      Function                                  fun
                      Create table script(s)               tab or ddl
                      Synonym creation statements   syn
                      Index definition                        idx
                      Constraint definitions               con

• Use lower case ".sql" as a suffix for all scripts

        [Mar 16,2010 Update - I tried to do: grep -i create *.sql yesterday, I failed to find a new table creation  due to the script ended with ".SQL". ]

• Recommend consolidating the script in the execution sequence of DDL first followed by DML.

• Recommend using some GUI tools such as TOAD, SQL Developer etc to minimize the syntax errors.

                   At minimum, everyone should do the following:
                   - Check if missing "schema" prefix for table, index names etc.
                   - Check if missing ";" in SQL
                   - Check if missing "/" as the end the PL/SQL begin/end block
                   - Check if comma used properly in the table column list for example
                   - Check if upper or lower cases are used consistently

• Recommend including the back out method (CTAS) for DML script

• Recommend formatting and aligning SQL statements properly whenever possible for improving readability

• Add "show error;" at the end of PL/SQL procedure, function, packages units.

• Avoid using ampersand character ‘&’ within any comment lines in the SQL scripts.

• Avoid using space in the script file name.

• Avoid adding storage clause in the table or index creation statement

           Note: defer to primary DBA to make such a decision if defaults are not desirable

• Avoid using more than 30 characters for table name or column name.

• Avoid using system-generated constraint names.

           For example the following method to add a primary key constraint is not acceptable:
              alter table myschema.t modify (id primary key);

• Recommend using the following methods to create primary key

         (1) Within CREATE TABLE statement

              CREATE TABLE t
              ( id number,
               val varchar2(40),
                constraint t_pk primary key (id) using index tablespace tablespace_name
              ) tablespace  tablespace_name;

        (2) Through ALTER TABLE statement

              CREATE TABLE t
              ( id number,
               val varchar2(40)
               ) tablespace tablespace_name ;

              ALTER TABLE t
              add constraint t_pk primary key(id) using index tablespace tablespace_name;