Oracle VM VirtualBox Upgrade - Version 4.0.4

I have been running on Oracle VM VirtualBox 3.x for quite sometime. I use to ignore the upgrade dialog box and kept saying to myself that I will upgrade next time. Finally, I made my mind to upgrade to the latest and the greatest version “4.0.4”. The upgrade was pretty straight forward.

When I tried to start one of the Virtual Machines, I was greeted with the following error:

I blamed myself for upgrading the stuff but the error message was quite straight forward. I was asked to install “Oracle VM VirtualBox Extension Pack”. You can download it from here.

When you try to install a Warning pops up as shown below. Click on the “Install” button and you are done.

The next message that pops up is the success of extension pack installation.

I can now start all my VM’s. ;)

Transpose Rows to Columns

In the old Oracle 8i days, transposing rows to columns was not straight forward. As Oracle Developer (working on Oracle Developer 2000), I remember writing a query using MAX and DECODE functions to transpose rows into columns for my previous employer. My efforts were applauded as this query replaced the old database stored procedure (function). Thanks to Tom Kyte for his wonderful asktom.oracle.com site.

Here’s a sample function which was then in use:

SQL> create or replace function test_func(p_deptno in emp.deptno%type)
  2    return varchar2 is
  3    cursor emp_cur is
  4       select deptno, ename
  5         from emp
  6        where deptno = nvl(p_deptno, deptno)
  7        order by ename;
  8    l_ename varchar2(500);
  9  begin
 10    for emp_rec in emp_cur loop
 11      l_ename := l_ename ||','|| emp_rec.ename;
 12    end loop;
 13    return(substr(l_ename, 2));
 14  end;
 15  /

Function created.

SQL>
SQL> column new_col format a50
SQL>
SQL>
SQL> select test_func(10) new_col from dual;

NEW_COL
--------------------------------------------------
CLARK,KING,MILLER

SQL> select test_func(20) new_col from dual;

NEW_COL
--------------------------------------------------
ADAMS,FORD,JONES,SCOTT,SMITH

SQL> select test_func(30) new_col from dual;

NEW_COL
--------------------------------------------------
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>
SQL> select deptno, test_func(deptno) new_col
  2    from (select distinct deptno from emp)
  3    order by deptno;

    DEPTNO NEW_COL
---------- -----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>
SQL>

I stumbled upon Analytic Functions that were introduced with Oracle 8i and came up with the following query:

SQL> select deptno, rtrim(new_col, ',') new_col
  2    from (select deptno,
  3                 max(decode ( rn , 1, ename || ',')) ||
  4                 max(decode ( rn , 2, ename || ',')) ||
  5                 max(decode ( rn , 3, ename || ',')) ||
  6                 max(decode ( rn , 4, ename || ',')) ||
  7                 max(decode ( rn , 5, ename || ',')) ||
  8                 max(decode ( rn , 6, ename )) new_col
  9            from (select ename, deptno,
 10                         row_number() over ( partition by deptno
 11                                             order by rownum) rn
 12                    from emp)
 13            group by deptno)
 14  order by deptno;

    DEPTNO NEW_COL
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

SQL>

The major drawback of the above query is the limitation of number of values it can display for the ENAME column. If a new employee joins department number “30” then this query has to be modified. This query was well suited for the environment where it was written as the number of values was known and were limited.

Then came Oracle 9i introducing SYS_CONNECT_BY_PATH function. The above limitations are overcome using this new function. It is capable of transposing “n” number of rows into columns. The same query can now be rewritten as:

SQL>
SQL> select deptno,
  2         max(substr(sys_connect_by_path (ename, ','),2)) new_col
  3   from (select deptno, ename,
  4                row_number() over (partition by deptno
  5                                    order by ename) rno
  6          from emp)
  7  start with rno = 1
  8  connect by rno = prior rno + 1
  9      and prior deptno = deptno
 10  group by deptno
 11  order by deptno;

    DEPTNO NEW_COL
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>

Well, so far so good. But Oracle 11g introduced LISTAGG function. This function makes life very easy. The query looks very simple, no hierarchies, no connect by ….

Here’s the new query:

SQL> 
SQL> select deptno,
  2         listagg(ename, ',') within group 
  3            (order by ename) new_col
  4    from emp
  5  group by deptno
  6  order by deptno;

    DEPTNO NEW_COL
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>

Isn’t it simple? Over the years Oracle has introduced tons of new features (like the one discussed here) that make life simple and at the same time making the code more performant.

The question is "are we really using these features efficiently?"

Tsunami in Japan

A massive tsunami triggered by one of the largest earthquakes smashed into Japan, killing hundreds of people. It swept away boats, cars and homes.

My heartfelt condolences to the people who have suffered or lost their near and dear ones in the disaster.

Extend your help to the Tsunami victims here.

Massive Deletes Vs Truncating Partitions

This is not a revolutionary post, everything I discuss here has been discussed numerous times. Massive deletes are nothing new in a DBA’s life. They were there, they are there and they will continue to be there. From time to time DBA’s fall prey to the bad application design. My last post was about heavy redo log switches and this post is a continuation.

The application was performing massive DELETE operations resulting in excessive redo being generated. This in turn was responsible for too many redo log switches and severely affecting database performance. It was possible that the same DELETE statement on a non-partitioned table could be translated into a “ALTER TABLE … TRUNCATE PARTITION” statement by partitioning the same table.

In this post, I will demonstrate how one can achieve extreme performance by employing right techniques for the right job.

For this demo, I will create a test table “T” (without any indexes) with a single column. Populate the table with 2 Million records and then DELETE 1 Million records. Before and after the delete we will make a note of the redo generated.

Here we go:

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t (id number) tablespace test_ts;

Table created.

SQL>
SQL> insert into t select level from dual connect by level <=2000000;

2000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Before we delete, let’s make a note of redo generated by our session:

SQL> column name format a20
SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                30.93

SQL>

Now perform the massive DELETE operation:

SQL>
SQL> set timing on
SQL> delete from t where id <= 1000000;

1000000 rows deleted.

Elapsed: 00:02:12.87
SQL> set timing off
SQL>

Make a note of redo generated by the DELETE statement:

SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                257.41

SQL>

So, the DELETE statement generated 226.48 MB (257.41 – 30.93) of redo and it took over 2 minutes to clock time.

Now, let us improvise the purge strategy but partitioning the table. I range partition the table into six partitions as shown:

SQL> drop table t purge;

Table dropped.

SQL> create table t (id number)  tablespace test_ts
  2  partition by range (id) (
  3  partition p1 values less than (500000),
  4  partition p2 values less than (1000000),
  5  partition p3 values less than (1500000),
  6  partition p4 values less than (2000000),
  7  partition p5 values less than (2500000),
  8  partition p6 values less than (MAXVALUE)
  9  );

Table created.

SQL>

Now run the same INSERT statement against the partitioned table:

SQL> insert into t select level from dual connect by level <=2000000;

2000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>

Make a note of redo before and after executing the purge command:

SQL>
SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                288.53

SQL>
SQL> set timing on
SQL> alter table t truncate partition p1;

Table truncated.

Elapsed: 00:00:00.62
SQL> alter table t truncate partition p2;

Table truncated.

Elapsed: 00:00:00.29
SQL> set timing off
SQL>
SQL> select sn.name, round(ms.value/1024/1024, 2) redo_in_mb
  2    from v$statname sn,
  3         v$mystat ms
  4   where sn.statistic# = ms.statistic#
  5     and sn.name = 'redo size';

NAME                 REDO_IN_MB
-------------------- ----------
redo size                288.64

SQL>

Purging of two partitions consumed less than a second and generated only 0.11 MB of redo.

In some situations it may be possible that the redo logs were sized inadequately but in this case it was the application’s bad design responsible for massive redo log generation.

What happens when you have additional columns and indexes associated with the table? You could take that as a further exercise.