identity column in oracle

I was involved in a DB2 to Oracle 11gr2 migration.
There were couple of table in db2 that had identity columns.  These columns get incremented during inserts.
In oracle, the equivalent to this is to use sequence objects in the database.

As always, oracle gives you more control than any other database. I can think of two methods that could be used to implement identity columns.

•  Sequence only
• Sequence + trigger.

 Sequence only : In oracle you could simply create a sequence object and call  sequenceName.nextvalue in the insert statement. this is most efficient. This is the  method mostly implemented by Oracle DBAs/applications.
e.g. insert into test_tab values ( sequence.nextvalue,' testvalue'); 

Sequence + Trigger : This method is slightly more code than the first option. One advantage of this method is that your sql code could run on both oracle and DBs that have identity columns. You still have to ensure that you list all the columns you are inserting  and skip the identity column.
eg. insert into test-tab(ename) values ('testvalue');
 

In the below test, i could insert 100000 rows with no trigger in 10 seconds and 26 seconds with triggers.
The difference may be very insignificant for most of the cases.
I also found no difference in performance with the IF condition in the trigger. So it is a good to have the IF condition in place so that they do not get fired with we try to load data into the table including the column having the trigger.


SQL> @trig
SQL>
SQL> --create table trig_tab nologging parallel as select *     from testvijay;
SQL> --create sequence trig_tab_seq START WITH 1 INCREMENT BY 1 NOCYCLE cache 1000;
SQL> create     or replace trigger trig_tab_tr
  2  BEFORE INSERT ON trig_tab
  3  FOR EACH ROW
  4  BEGIN
  5      IF :new.object_id IS null THEN
  6          SELECT trig_tab_seq.nextval INTO :new.object_id FROM DUAL;
  7      END IF;
  8  END disable ;
  9  /

Trigger created.

Elapsed: 00:00:00.12
SQL> alter trigger trig_tab_tr enable;

Trigger altered.

Elapsed: 00:00:00.02
SQL> set timing on
SQL> set echo on
SQL> insert into trig_tab (OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)
  2   select OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,sysdate,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
  3  from testvijay where rownum <= 100000;

100000 rows created.

Elapsed: 00:00:26.26
SQL> commit;

Commit complete.

Elapsed: 00:00:00.95
SQL> create     or replace trigger trig_tab_tr
  2  BEFORE INSERT ON trig_tab
  3  FOR EACH ROW
  4  BEGIN
  5   --   IF :new.object_id IS null THEN
  6          SELECT trig_tab_seq.nextval INTO :new.object_id FROM DUAL;
  7    --  END IF;
  8  END disable ;
  9  /

Trigger created.

Elapsed: 00:00:00.13
SQL> alter trigger trig_tab_tr enable;

Trigger altered.

Elapsed: 00:00:00.03
SQL> insert into trig_tab (OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)
  2   select OWNER,OBJECT_NAME,SUBOBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,sysdate,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
  3  from testvijay where rownum <= 100000;

100000 rows created.

Elapsed: 00:00:26.23
SQL> commit;

Commit complete.

Elapsed: 00:00:04.80
SQL> alter trigger trig_tab_tr disable;

Trigger altered.

Elapsed: 00:00:00.09
SQL> insert into trig_tab (OWNER,OBJECT_NAME,SUBOBJECT_NAME,object_id,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME)
  2   select OWNER,OBJECT_NAME,SUBOBJECT_NAME,trig_tab_seq.nextval,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,sysdate,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
  3  from testvijay where rownum <= 100000;

100000 rows created.

Elapsed: 00:00:10.08
SQL> commit;

Commit complete.

Elapsed time or exeution time for Oracle sqls in 11g

Oracle is perhaps the best database available out there when it comes to diagnostics and performance monitoring tools. The captured data can tell us the number of times events were called and the time waited for each event.

To find the elapsed time of a sql in 10g timeframe, we usually look at v$sqlstats or dba_hist_sqlstats. The average elapsed time of a sql is equal to sum( elspsed_time) /sum(executions) from v$sqlstats or
sum(elapsed_time_delta)/sum(executions_delta) from dba_hist_sqlstat.
When a sql rans in parallel, there was a problem with the accuracy of elapsed_time.

The elapsed time adds the time spent by the individual parallel slaves for a sql. So for simple sql quries, where all the parallel slaves were busy until the end of the sql, we could still get the elapsed time by dividing the total_elapsed_time by number of parallel slaves. i.e sum (elapsed_time_delta)/sum(px_servers_executions). But what if not all slaves were busy , then the above formulate would not work.

The solution to this problem is in v$active_session_history and dba_hist_active_sess_history and the introduction of a new column SQL_EXEC_START in 11g.

Oracle background process looks at v$session information and updates v$active_session_history every second. A new column introduced in 11g called sql_exe_start now logs the time the sql started its execution.

So now you can calculate the elapsed time by looking for the maximum sampled time the sql was running with the same start time ( sql_exec_start). Sample sql script below.

Since the precision of v$active_session_history is in seconds, The precisions of the result is one second. And for sql queries taking less than 1 second, the traditional way of looking at v$sqlstat should be good enough as in most cases, those queries may not run in parallel.


I have observer that dba_hist_active_sess_history records sample data from v$active_session_history every 10 seconds. Now that would mean that the precision of elapsed_time for sqls  would be 10 seconds from dba_hist_active_sess_history table.

select sql_id,round(avg(exe_sec1),3) ela_per_exe from (

select sql_exec_start, sql_id,

  to_char(max(sample_time),'SSSSS')

- to_char(sql_exec_start,'SSSSS') exe_sec1
from v$active_session_history
where
sql_id is not null and
sql_exec_start is not null
group by sql_exec_start, sql_id
having

  to_char(max(sample_time),'SSSSS')

  -to_char(min(sample_time),'SSSSS') > 1 )
group by sql_id;