Recently in Development Category

Why don't applications which switch to Oracle Real Application Server (RAC) simply scale-up as expected? Generally you will find that a single instance database with a baseline performance of 100% under heavy load does not give you the expected 200% when you add a second server such as you would in a RAC environment. You will probably find that performance falls way short of expectations with an actual reduction (under the same heavy load) to 70%. This is often the reason why a twin-server RAC is configured to run in Hot-Standby thus only utilizing one server until a fail-over becomes necessary. But that means wasting half of your resources most of the time as with a HAC solution. So the question is, can you get a higher performance whilst retaining high availability thus not having to pay off one against the other? The answer is yes. But you don't get it by chance you have to be aware of some important issues.

The first and most important aspect is we must have some kind of logical separation within our application. That could by online and batch services or private and business services or anything else which gives you the ability to differentiate the work to be done. If we find something which breaks-down even lower say to 10 or 20 aspects that's ok too because we can simply group them together as needed. What we are aiming to achieve is to specifically map the work to the number of nodes we have in our Real Application Cluster and we don't want to leave that to chance. We want to direct certain work to specific nodes and not have the scan-listener do its standard load-balancing of distributing connections across the available nodes. That in itself can get a bit tricky to control but there a various ways of achieving it. The second and most important thing is to try and make sure the data buffers our work needs are held by that node. Whilst data not held on that node will be obtained from another node via interconnect or via direct-io we want to avoid that most of the time. In order to achieve that we should have our tables partitioned by our logical separation criteria. Although that sounds pretty simple it can be difficult to implement but the rewards are stunning.

We should find that we get very close to the expected 200% performance increase. If we later need to handle an even higher workload we should be able to scale-up to 3 or 4 database nodes and divide our logical workload accordingly. The key to success is getting our table partitions divided in line with our logical workload. The partitions (and their buffers) will be mastered on the node where the most work is being done. It may sound like we are forcing data blocks to be held on specific nodes but that is not really true. We are preferring data to be held where it is currently needed. If a node is added or removed the data is simply relocated to a different node. What we don't want at all costs is for hot-buffers to be constantly parsed between nodes. Whilst the exchanging of data across the interconnect is very fast, hot-buffers which are dirty (ie. have pending commits) will be synced to storage prior to the handing over of the block to the other instance. Instead of an interconnect block access on an old block (which is not dirty) that takes maybe 3ms it may take 103ms because the hot-buffer has to be synced to the SAN (costing 100ms) first.

Whilst there are many more standard Oracle features which you can leverage to help scale an application in a RAC environment this is probably the most significant.

The basic paradigm for High Performance RAC development is "Don't leave anything to chance and allways build to scale".

Oracle's Flashback functionality is a really great feature and it comes in all shapes and sizes except "schema". If you are writing a test suite and want exactly the same data conditions per test, it would be fantastic to build the schema and fill it with initial data then run 100 different tests, which always reset to the initial data to the original timestamp. That would be "Flashback Schema", which neither 10g nor 11g have on offer. But hey folks, let's do-it-ourselves!

The following code is a simple implementation of Flashback Schema with some restrictions regarding the kind of objects, which can be flashed:

CREATE OR REPLACE procedure Flashback_Schema( p_flashback2date in DATE ) AS

--

-- Author: Paul Lewin / 20th March 2009

-- Version 1.1 / 25th March 2009

-- Description:  Flashback an Oracle schema to a past point-in-time

-- Field of use: Not for production but for testing and simulation (QA)

--

-- Setup the instance:

--  Set the retention period for the instance before use (in seconds) 86400 = 24 Hrs

--  Make sure the UNDO-Tablespace is large enough to handle all changes for 24 Hrs

--  ALTER SYSTEM SET UNDO_RETENTION = 86400;

--  ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

--

-- Setup the user (as System):

--  Grant execute on DBMS_FLASHBACK to username;

--  Grant FLASHBACK ANY TABLE to username;

--  Grant execute any procedure to username;

--  Grant SELECT ANY TRANSACTION to username;

--

-- Calling:

--  Use the following call in each schema to reset the data to a specific point-in-time

--  EXEC Flashback_Schema_To_Timestamp( TO_DATE('18.03.2009 10:50','DD.MM.YYYY HH24:MI') );

--

-- Restrictions:

--  This will not work on schemas, which include materialized views.

--  Truncating tables will make this functionality in-operable.

--  Using alter table or tablespace commands will also make this functionality in-operable.

-- Cursor builds a list of execute immediate commands to enable row movement

CURSOR c_cmd_move_enable is

  select

    'alter table '||table_name||' enable row movement'

  from

    user_tables

  where

    temporary='N'

  and

    row_movement='DISABLED';

-- Cursor builds a list of execute immediate commands to revoke previously enabled row movement

CURSOR c_cmd_move_disable is

  select

    'alter table '||table_name||' disable row movement'

  from

    user_tables

  where

    temporary='N'

  and

    row_movement='DISABLED';

-- Get a list of tables in the current schema

CURSOR c_cmd_flashback_list is

  select

    table_name

  from

    user_tables

  where

    temporary='N'

  and

    table_name not in (select mview_name from user_mviews)

  Order By

    table_name;

v_cmd varchar2(250 char);

v_cmd_flashback varchar2(32000 char) := 'flashback table ';

v_flashback_date date;

v_table_cnt number := 0;

v_reset_enable := 0;

Begin

               /* Default flashback by 1 hour */

  if p_flashback2date is NULL

  then

    v_flashback_date := sysdate - (1/24); /* flashback 1 hour */

  else

    v_flashback_date := p_flashback2date;

  end if;

  Open c_cmd_move_enable;

  Open c_cmd_move_disable;

  Open c_cmd_flashback_list;

  /* All tables must have row movement allowed! */

  loop

    FETCH c_cmd_move_enable into v_cmd;

    EXIT WHEN c_cmd_move_enable%NOTFOUND;

--  dbms_output.put_line( 'EXECUTE IMMEDIATE ' || v_cmd );

    EXECUTE IMMEDIATE v_cmd;

    v_reset_enable := 1;

  end loop;

  /* Get a list of tables in this schema */

  /* in a comma separated list with 6 names per line */

  loop

    FETCH c_cmd_flashback_list into v_cmd;

    EXIT WHEN c_cmd_flashback_list%NOTFOUND;

    if v_table_cnt > 0

    then

                              v_cmd_flashback := v_cmd_flashback || ', ' || v_cmd;

      if mod(v_table_cnt,6) = 0

      then

                              v_cmd_flashback := v_cmd_flashback || chr(13);

      end if;

    else

                              v_cmd_flashback := v_cmd_flashback || v_cmd; -- first table doesn't need a comma

    end if;

    v_table_cnt := v_table_cnt + 1;

  end loop;

  v_cmd_flashback := v_cmd_flashback || ' TO TIMESTAMP :1';

  /* Flashback all tables in one transaction */

--dbms_output.put_line( 'EXECUTE IMMEDIATE ' || v_cmd_flashback || ' using parameter ' || to_char(v_flashback_date, 'DD.MM.YYYY HH24:MI') );

  EXECUTE IMMEDIATE v_cmd_flashback using v_flashback_date;

  /* restore row movement to all tables which previously had row movement allowed */

  loop

    FETCH c_cmd_move_disable into v_cmd;

    EXIT WHEN c_cmd_move_disable%NOTFOUND;

    v_reset_enable := 0;

--  dbms_output.put_line( 'EXECUTE IMMEDIATE ' || v_cmd );

    EXECUTE IMMEDIATE v_cmd;

  end loop;

  Close c_cmd_flashback_list;

  Close c_cmd_move_disable;

  Close c_cmd_move_enable;

EXCEPTION

    WHEN OTHERS THEN

        IF c_cmd_flashback_list%ISOPEN

        THEN

            CLOSE c_cmd_flashback_list;

        END IF;

        IF c_cmd_move_disable%ISOPEN

        THEN

            CLOSE c_cmd_move_disable;

        END IF;

        IF c_cmd_move_enable%ISOPEN

        THEN

            IF v_reset_enable = 1

            THEN

               /* restore row movement to all tables which previously had row movement allowed */

              loop

                FETCH c_cmd_move_disable into v_cmd;

                EXIT WHEN c_cmd_move_disable%NOTFOUND;

--              dbms_output.put_line( 'EXECUTE IMMEDIATE ' || v_cmd );

                EXECUTE IMMEDIATE v_cmd;

              end loop;

            END IF;

            CLOSE c_cmd_move_enable;

        END IF;

        RAISE;

End;

/

Back in September Oracle released the 10.2.0.4 version of Oracle Warehouse Builder for Windows and other platforms. If you go looking for the client download, don't look for a Windows XP client just head directly for the patch on metalink (https://metalink.oracle.com/). The patch-id is 7005587 and you will need the 32 bit client for windows.

Further details are here on the Oracle OWB blog.

If you are looking for a comfortable way of efficiently logging information within your Oracle database or you want a seamless logging from within your Java application which already uses log4j and uses an Oracle database - look no further. It is part of the Apache project and can be freely used within production solutions. So take a look here for details on log4plsql.

Okay, so we all love to build tokenizer's but take a look at this one. It is really versatile. I used it to easily convert some very dynamic path information into individual catagories - works great.

I thought this blog entry from Optimizermagic was very well put together and explains the differences between Oracle inner and outer join syntax and the ansi syntax. It is both clearly laid out and easy to follow. So if your are unsure of the pitfalls take a closer look.

Have you had sporadic problems with sql statements firing at the wrong schema and can’t work out why? Well you may be a victim of an ongoing Oracle bug. This bug has been around since 9i and is documented under Note: 392673.1. Under certain circumstances an insert or select will use an old cursor which fires at the wrong schema and object. This will, however, only happen under certain conditions and only if the object is not full qualified. It is fixed in version 11 and patched for 10.2.0.4. Check out this thread for more details.

Okay, so this problem only comes up once a century - but it's just so easy to handle. If you have some date fields you want to insert into an Oracle table - say from Excel - then your two-digit years will get expanded from 96 to 2096. What you probable want is to see 1996 rather than 2096.

The solution is really very simple, insert the year using the date operator 'RR' as apposed to 'YY'. For example let’s say Excel gives you a date string like "13. Jan 78". Using the date format to_date('13. Jan 78', 'DD. Mon RR') any year values between 0-49 will arrive in the table as 2000-2049 and values between 50-99 will arrive as 1950-1999 in your database.
If you are looking for more information check-out this link.

If you're thinking of calling commands on the operating system from Oracle PL/Sql you might want to take a look at this code or this explanation. There are also some security aspects which you should not overlook when using this kind of functionality.

By default, a stored procedure executes with the privileges of its definer, not its invoker. Such procedures are bound to the schema in which they reside. For example, assume that the following standalone procedure, which can drop any kind of database object, resides in schema scott:
CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;

Also assume that user jones has been granted the EXECUTE privilege on this procedure. When user jones calls drop_it, as follows, the dynamic DROP statement executes with the privileges of user scott:
SQL> CALL drop_it('TABLE', 'dept');

Also, the unqualified reference to table dept is resolved in schema scott. So, the procedure drops the table from schema scott, not from schema jones.
However, the AUTHID clause enables a stored procedure to execute with the privileges of its invoker (current user). Such procedures are not bound to a particular schema. For example, the following version of drop_it executes with the privileges of its invoker:
CREATE PROCEDURE drop_it (kind IN VARCHAR2, name IN VARCHAR2)
AUTHID CURRENT_USER AS
BEGIN
EXECUTE IMMEDIATE 'DROP ' || kind || ' ' || name;
END;