Agile DBA

We have been doing some data moving lately using Ruby and Ruby-OCI. We started with Ruby OCI 1.0 and did use prepared statements with bind variables (since we are using oracle database and pulling data from an oracle database and pushing data to an oracle database). Later we found this really cool feature in Ruby-OCI8 2.0 where you can bind a whole array and just make one database trip for many database operations.

Lets say you want to insert 10 rows, using the insert one row at a time would be 10 trips to the database.

def save_accounts(accounts)
    stmt = $connection.parse "INSERT INTO account (accountid,name) values (:account_id,:name)"
      accounts.each do |account|
        stmt.bind_param(:account_id, account[0], Float)
        stmt.bind_param(:name, account[1], String)
        stmt.exec
      end
      $connection.commit
    end

Using the array bind feature, its actually just one trip to the database (off course depends on the array size you are going to bind, but you get the picture, it reduces database trips)

def save_accounts(account_ids, account_names)
      stmt = $connection.parse "INSERT INTO account (accountid,name) values (:account_id,:name)"
      stmt.max_array_size= account_ids.size
      stmt.bind_param_array(:account_id, account_ids)
      stmt.bind_param_array(:name, account_names)
      stmt.exec_array
      $connection.commit
    end

We saw a 100% improvement in performance by changing the way we bind the variables in just one place. Looks like a feature to look out for.

Posted by Pramod Sadalage at 8:16 PM | Permalink

Most of the time I have seen database foreign key constraints on tables without indexes on those columns. Lets say the application is trying to delete a row from the CUSTOMER table

DELETE FROM CUSTOMER WHERE CUSTOMERID = 1000;

When the database goes about deleting the customerId of 1000, if there are foreign key constraints defined on customerId, then the database is going to try to find if the customerId of 1000 is used in any of those tables. Lets say ORDER table has the customerId column, the database is going to issue

SELECT ... FROM ORDER WHERE CUSTOMERID = 1000;

now if there is no index on ORDER.CUSTOMERID, the database will have to do a full Table scan which is very expensive in terms of IO and resources, imagine customerId being used in lots of tables, the problem just multiplies significantly. In an multiuser scenario, this will lead to deadlocks, since the same tables are being read and locks being applied to find dependend children. Introducing an index on all the columns that are foreign key referenced helps a lot in this case.

Posted by Pramod Sadalage at 11:15 PM | Permalink

Recently one of my colleague Jeff Norris had a weird error. He was trying to build a materialized view over some tables in his local database and some tables in his remote database using database links the sql to create the view ran fine and provided the results as expected, but when put inside a materialized view statement complained with ORA-00942 errors.

Lets say the two databases in question are local and remote, so the sql to create the materialized view to load immediately and refresh everyday is

CREATE MATERIALIZED VIEW MV_CUSTOMERBALANCE 
BUILD IMMEDIATE
REFRESH FORCE START WITH ROUND(SYSDATE) + 23/24
NEXT SYSDATE + 1
AS
SELECT customer.name , account.balance, accounttype.name 
FROM customer , account@remotedb account, accounttype@remotedb accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/

Oracle started to complain when creating the above materialized view issuing an error ORA-00942: table or view does not exist, but the SQL without the create materialized view command ran fine giving the expected results.

SELECT customer.name , account.balance, accounttype.name 
FROM customer , account@remotedb account, accounttype@remotedb accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/

After some searching around and experimenting I found, in the create materialized view statement the database link name can be used only once, which meant we can only use the "remotedb" name once, we got around this restriction by creating two database links to the remote database as REMOTEACCOUNT and REMOTEACCOUNTTYPE and using them in the creation of the materialized view as shown below.

CREATE MATERIALIZED VIEW MV_CUSTOMERBALANCE 
BUILD IMMEDIATE
REFRESH FORCE START WITH ROUND(SYSDATE) + 23/24
NEXT SYSDATE + 1
AS
SELECT customer.name , account.balance, accounttype.name 
FROM customer , account@remoteaccount account, accounttype@remoteaccounttype accounttype
WHERE
customer.id = account.customerid
AND account.accounttyppeid = accounttype.id
/

Posted by Pramod Sadalage at 10:54 AM | Permalink

Ever since I moved to the Mac, I had to run some other OS inside a VM so that I could run Oracle and use it, since Oracle was not available for the the Mac. Now that is no longer the case. Oracle 10gR2 (10.2.0.4) is now available for Mac here

This is especially nice since the Oracle for Mac was the most voted requirement on mix.oracle.com

Posted by Pramod Sadalage at 3:25 PM | Permalink

In Oracle 10g and before we all know that passwords are not case sensitive, so PASSWORD, Password, password would let you in and everything would be okay.

If you upgrade to Oracle 11g (I know lot of you are waiting for 11gR2), you will find that passwords are case sensitive. Here is an example of case sensitive passwords.

c:\Software>sqlplus bddd/bddd@dosa
SQL*Plus: Release 11.1.0.6.0 - Production on Wed May 6 15:17:43 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning and OLAP options
BDDD@dosa >

Lets try to connect with a upper case password

c:\Software>sqlplus bddd/BDDD@dosa
SQL*Plus: Release 11.1.0.6.0 - Production on Wed May 6 15:19:25 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:

So what does this mean to apps running with 10g, that get ported to run with 11g. Make sure that the password set in the properties files is of the correct case.

You can also revert to 10g behavior by changing sec_case_sensitive_logon parameter to FALSE, since its TRUE by default.

alter system set sec_case_sensitive_logon=FALSE;

Posted by Pramod Sadalage at 3:22 PM | Permalink

Oracle has metadata about all its objects in various tables/views. One such view is the USER_OBJECTS or ALL_OBJECTS, this view has a column named as STATUS which shows you if the given object is VALID or INVALID. The status applies to DB Code (Stored Procedures, Functions, Triggers etc). To find all the INVALID objects in the schema, issue SELECT * FROM USER_OBJECTS WHERE STATUS='INVALID'. One problem with the way oracle maintains this metadata is, changing the underlying table on which the DB Code depends, oracle marks the objects are INVALID even though the underlying table may have changed in such a way, that it does not affect the DB Code at all (like adding a new column, or making a colum nullable). Here is some code which shows you what I mean. Run it through SQLPlus.

COLUMN OBJECT_NAME FORMAT A30
COLUMN STATUS FORMAT A15
spool objects.log

CREATE TABLE FOO (ID NUMBER(10), NAME VARCHAR2(30));

CREATE OR REPLACE TRIGGER TRIG_FOO
BEFORE INSERT OR UPDATE
ON FOO
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
	IF :NEW.name IS NULL THEN
		:NEW.name := 'NOT AVAILABLE';
	END IF;
END;
/

CREATE OR REPLACE FUNCTION FUNCTION_GET_NAME_FOR_FOOID(inFooId number)
RETURN VARCHAR2
IS
fooName VARCHAR2(30);
BEGIN
	BEGIN
     SELECT name INTO fooName FROM foo WHERE id = inFooId ;
     EXCEPTION
         WHEN NO_DATA_FOUND THEN 
     		RETURN 'NOT FOUND';
 	END;
     RETURN fooName;
 END;
/

SELECT OBJECT_NAME,STATUS FROM USER_OBJECTS WHERE STATUS='INVALID';

ALTER TABLE FOO ADD ( DESCRIPTION VARCHAR2(100));

SELECT OBJECT_NAME,STATUS FROM USER_OBJECTS WHERE STATUS='INVALID';

spool off

To get the objects back to VALID status, all that needs to be done is

ALTER TRIGGER TRIG_FOO COMPILE;
ALTER FUNCTION FUNCTION_GET_NAME_FOR_FOOID COMPILE;

SELECT OBJECT_NAME,STATUS FROM USER_OBJECTS WHERE STATUS='INVALID';

Posted by Pramod Sadalage at 5:49 AM | Permalink