# Clearing the cache on Oracle and SQL Server

To clear the cache on Oracle:
 ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH SHARED_POOL; 

To clear the cache on SQL Server:
 CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO 

# Indexing only null column values in Oracle

We have a table that can have tens or hundreds of millions of rows in it, and in this table there is a certain column. This column usually—typically—generally—has non-null hash values in it.

In unusual cases though, that column can be NULL. And the thing is, there are times when the interesting rows, the rows we want to gather together and do something with, are exactly the rows where that column is null.

Table scans to find the rows where the column is NULL are expensive, so naturally we want to index this column. But since non-null values in this column are a hash and we only ever care about the NULL values, we’d like to avoid the time and space overhead of indexing the non-null values. We’d like a partial index of only the rows where the column is NULL… Continue reading

# How to put a delay in an Oracle sproc, for testing

To simulate your Oracle stored procedure taking 15 minutes to execute, put this in your sproc:

dbms_lock.sleep(15 * 60);

But first you’ll need to give your user EXECUTE permissions on the dbms_lock module:

C:\>sqlplus sys@clayoracle3 as sysdba
...
SQL> GRANT EXECUTE ON dbms_lock TO my_user;

Grant succeeded.

If you’re trying this out outside a sproc, put an exec on the beginning, thus:

exec dbms_lock.sleep(15 * 60);

Note: dbms_lock.sleep() is known to be unreliable past about a 10 minute nap.

# How to put a delay in a SQL Server sproc, for testing

To simulate your SQL Server stored procedure taking 15 minutes to execute, put this in your sproc:

WAITFOR DELAY '00:15'

# How to spit out a result set returned by an Oracle sproc

When a sproc returns a result set, in Oracle it takes a little more work to view the contents of that result set. But it can be done.

## Simple example

As an example, say you want to call the spir_get_fooids_by_bazidkey sproc which returns a result set of foo ids, and you want to see what foo ids it returned. Here’s how that would look:

# Oracle stuff

 --To disable ampersand-variables: SET DEFINE OFF

 --To enable debug output: SET SERVEROUTPUT ON --To list data tablespaces: SELECT DISTINCT TABLESPACE_NAME FROM ALL_TABLES; 

--To list index tablespaces: SELECT DISTINCT TABLESPACE_NAME FROM ALL_INDEXES; 

# The SEVERE but ignorable error

There’s a certain error-and-stack-trace we get every time we bring up an instance of JBoss (or is it every time we deploy one of our war projects?), to wit:

09:15:37,945 ERROR [STDERR] Oct 16, 2008 9:15:37 AM oracle.jdbc.driver.OracleDriver registerMBeans
SEVERE: Error while registering Oracle JDBC Diagnosability MBean.
javax.management.MalformedObjectNameException: Invalid character '
' in value part of property
at javax.management.ObjectName.construct(ObjectName.java:529)
at javax.management.ObjectName.(ObjectName.java:1314)
at oracle.jdbc.driver.OracleDriver.registerMBeans(OracleDriver.java:303)
at oracle.jdbc.driver.OracleDriver$1.run(OracleDriver.java:213) at java.security.AccessController.doPrivileged(Native Method) at oracle.jdbc.driver.OracleDriver.(OracleDriver.java:209) at oracle.jdbc.pool.OracleDataSource.(OracleDataSource.java:94) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) at java.lang.reflect.Constructor.newInstance(Constructor.java:494) at java.lang.Class.newInstance0(Class.java:350) at java.lang.Class.newInstance(Class.java:303) at org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.getXADataSource(XAManagedConnectionFactory.java:219) at org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory.createManagedConnection(XAManagedConnectionFactory.java:136) at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.createConnectionEventListener(InternalManagedConnectionPool.java:577) at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:262) at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:500)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:341)
at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:315)
at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:396)
at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:842) at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:88) at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:82) at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:84) at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2073) at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1298) at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:859) at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:814) at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:732) at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:211) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1368) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1334) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:473) at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory$1.run(AbstractAutowireCapableBeanFactory.java:409)
at java.security.AccessController.doPrivileged(Native Method)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:380)
at org.springframework.beans.factory.support.AbstractBeanFactory\$1.getObject(AbstractBeanFactory.java:264)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:221)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:261)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:185)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:164)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:423)
at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:729)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:381)
at org.springframework.context.support.ClassPathXmlApplicationContext.(ClassPathXmlApplicationContext.java:139)
at org.springframework.context.support.ClassPathXmlApplicationContext.(ClassPathXmlApplicationContext.java:105)
[...]


We’re used to this, and it doesn’t seem to cause any harm, so we ignore it, but wish it weren’t there.

A post to the SQLJ/JDBC forum at Oracle Technology Network Discussion Forums brings up this same issue, and the reply says that a patch to ojdbc5.jar has been released, dealing with this issue.  My results show we’re not using the patched version:

C:\path\to\jboss-4.2.2.GA\server\default\lib>java -jar ojdbc5-11.1.0.6.0.jar
Oracle 11.1.0.6.0-Production JDBC 3.0 compiled with JDK5


(If we had the patch, the output would be

Oracle 11.1.0.6.0-Production+ JDBC 3.0 compiled with JDK5


).

The drivers are available from Oracle.  Perhaps soon we’ll upgrade to them…

# Oracle Treajure hunt

I wanted to find the Javadoc for Oracle’s JDBC datasource classes, to see what XA interfaces they implement (…to form a more focused question to ask on the Bitronix Transaction Manager forum).  I’d looked for the Javadoc before there and I thought I’d found some, though it was hard to find.  I decided to search again and this time, bookmark it.

(Usually one would just post the solution, but I’m cataloging what my process was too.  Maybe writing down what I did will allow us to analyze my process and improve on it next time.)

Buried

After some ask.com, Google, and dogpile.com searches didn’t seem to turn up anything (well, I did find something on someone else’s personal site, but I wanted my bookmark to point to Oracle) I decided to see if I could find what I was looking for by browsing Oracle’s site.  From the main page there were Products and Services, Industries, Support, Partners, Communities, and About across the top; Database, Middleware, Applications, and Industries in the middle, and Featured Downloads, Events and News, and Resources down the right hand side.

I picked Support, but didn’t see Java stuff there, so I went back and picked Communities.  The Oracle Technology Network looked promising (it turns out I could have gotten to this directly from the main page using a link under Resources there), and from there I went to Java under Technologies on the left hand sidebar, and then to JDBC under Focus Areas.