Re: EOF and optimizing Oracle sequence generation
Re: EOF and optimizing Oracle sequence generation
- Subject: Re: EOF and optimizing Oracle sequence generation
- From: "Ruggentaler, JR" <email@hidden>
- Date: Tue, 11 Aug 2015 16:49:58 +0000
- Thread-topic: EOF and optimizing Oracle sequence generation
Below is our Oracle extension of EROraclePlugIn and ERXJDBCAdaptor. I overrode method JDBCAdaptor.newPrimaryKeys() to cache primary keys and reduce database roundtrips to get the sequence’s NEXTVAL. The code also contains some other Oracle specific features like storing the pid and app name in Oracle’s v$session…
I would like to add code to determine the sequence INCREMENT_BY value rather than depending on the developer and or DBA to update the Oracle sequence value and Entity.userInfo dictionary (ERXPrimaryKeyBatchSize #).
Does any one have an example of executing a select from ERXJDBCAdaptor (SELECT INCREMENT_BY FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = '<TABLE_NAME>_SEQ';)?
public class MYOraclePlugIn extends EROraclePlugIn {
public MYOraclePlugIn(JDBCAdaptor adaptor) {
super(adaptor);
}
protected Class<?> defaultExpressionClass;
/* (non-Javadoc)
* @see com.webobjects.jdbcadaptor.JDBCPlugIn#defaultExpressionClass()
*/
public Class<?> defaultExpressionClass() {
if (defaultExpressionClass == null) {
if (MYOracleExpression.isFetchLimitSupportEnabled()) {
defaultExpressionClass = MYOracleExpression.class;
} else {
defaultExpressionClass = OracleExpression.class;
}
}
return defaultExpressionClass;
}
/**
* Overriden to augment the connection properties to enable the DBA to see the application name
* and PID for WO5 apps.
*/
@Override
public Properties connectionPropertiesForConnectionDictionary(@SuppressWarnings("rawtypes") NSDictionary nsdictionary) {
Properties properties = super.connectionPropertiesForConnectionDictionary(nsdictionary);
// Oracle specific properties
if (properties != null) {
String pid = "" + System_Additions.processID();
String processName = System_Additions.processName();
Number port = System_Additions.port();
String host = System_Additions.hostName();
String program = processName + ":" + port + "@" + host;
if (program.length() > 41) {
program = StringUtils.truncate(program, 41, "");
}
if (null != processName) {
properties.put("v$session.program", program);
}
if (null != pid) {
properties.put("v$session.process", pid);
}
Boolean isBackgroundTask = (Boolean) ERXThreadStorage.valueForKey(MYTask.IS_BACKGROUND_TASK_KEY);
if (isBackgroundTask != null) {
properties.put("v$session.program", program + "-job");
}
}
return properties;
}
/**
* The whole purpose of this MYJDBCAdaptorPatch class is to expose
* the JDBCAdaptor's _cachedContext protected i-var. This allows the
* plugin's jdbcInfo() method to access this cached context object
* and close it after using it to fetch the JDBC info.
*/
public static class MYJDBCAdaptorPatch extends ERXJDBCAdaptor {
public MYJDBCAdaptorPatch(String name) {
super(name);
}
public JDBCContext cachedAdaptorContext() {
return (JDBCContext) this.createAdaptorContext();
}
}
/**
* Overrides method jdbcInfo() in order to close the database connection
* used to fetch the JDBC info.
*/
@Override
public NSDictionary<String, Object> jdbcInfo() {
NSDictionary<String, Object> jdbcInfo;
// Just in case two different adaptors share the same plug-in and this
// method happens to get called by different threads it seems like a
// good idea to guard this code that swaps out the _adaptor i-var
// so that the _adaptor is not restored by the other thread while in
// the middle of doing this.
synchronized(this) {
// Remember the current adaptor
JDBCAdaptor adaptor = _adaptor;
// Instantiate and setup our adaptor patch that gives us access to
// the object used to fetch the JDBC info.
MYJDBCAdaptorPatch newAdaptor = new MYJDBCAdaptorPatch("JDBC");
newAdaptor.setConnectionDictionary(adaptor.connectionDictionary());
// Now swap out this plugin's adaptor with ours temporarily so that
// when we call jdbcInfo() in the JDBCPlugIn superclass it ends up
// getting the secondary JDBC context in it to fetch the JDBC info.
// And because our adaptor exposes this secondary JDBC context object
// we can disconnect it from the database here. Then we restore the
// adaptor in use before the swap.
_adaptor = newAdaptor; // swap
jdbcInfo = super.jdbcInfo();
newAdaptor.cachedAdaptorContext().disconnect();
_adaptor = adaptor; // restore
}
return jdbcInfo;
}
@SuppressWarnings("rawtypes")
private static NSMutableDictionary<String, NSMutableArray> pkCache = new NSMutableDictionary<String, NSMutableArray>();
private int defaultBatchSize = ERXProperties.intForKeyWithDefault("er.extensions.ERXPrimaryKeyBatchSize", -1);
/**
* Return an array of dictionaries with primary keys for <code>entity</code>.
* Set er.extensions.ERXPrimaryKeyBatchSize to 1 and add Entity userInfo dictionary ERXPrimaryKeyBatchSize <Oracle INCREMENT_BY for sequence>
* SELECT INCREMENT_BY FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = '<TABLE_NAME>_SEQ';
*/
@Override
public NSArray<NSDictionary<String, Object>> newPrimaryKeys(int count, EOEntity entity, JDBCChannel channel) {
Object batchSize = (entity.userInfo() != null ? entity.userInfo().objectForKey("ERXPrimaryKeyBatchSize") : null);
if (defaultBatchSize > 0 && null != batchSize) {
synchronized (pkCache) {
String key = entity.primaryKeyRootName();
@SuppressWarnings("unchecked")
NSMutableArray<NSMutableDictionary<String, Object>> pks = pkCache.objectForKey(key);
if (pks == null) {
pks = new NSMutableArray<NSMutableDictionary<String, Object>>();
pkCache.setObjectForKey(pks, key);
}
if (pks.count() < count) {
int size = ERXValueUtilities.intValue(batchSize);
pks.addObjectsFromArray(_newPrimaryKeys(size + count, entity, channel, size));
}
NSMutableArray<NSDictionary<String, Object>> batch = new NSMutableArray<NSDictionary<String, Object>>();
for (Iterator<NSMutableDictionary<String, Object>> iterator = pks.iterator(); iterator.hasNext() && --count >= 0;) {
NSMutableDictionary<String, Object> pk = iterator.next();
batch.addObject(pk);
iterator.remove();
}
return batch;
}
}
return super.newPrimaryKeys(count, entity, channel);
}
private NSArray<NSMutableDictionary<String, Object>> _newPrimaryKeys(int count, EOEntity entity, JDBCChannel channel, int batchSize) {
NSMutableArray<NSMutableDictionary<String, Object>> pks = new NSMutableArray<NSMutableDictionary<String, Object>>(count);
int iMax = (int) Math.ceil((double)count / batchSize);
NSArray<NSDictionary<String, Object>> npks = super.newPrimaryKeys(iMax, entity, channel);
for (NSDictionary<String, Object> pk : npks) {
pks.addObjectsFromArray(incrementPrimaryKey(pk, batchSize));
}
return pks;
}
private NSArray<? extends NSMutableDictionary<String, Object>> incrementPrimaryKey(NSDictionary<String, Object> pk, int batchSize) {
NSMutableArray<NSMutableDictionary<String, Object>> pks = new NSMutableArray<NSMutableDictionary<String, Object>>(batchSize);
pks.addObject((NSMutableDictionary<String, Object>) pk);
for (int i = 1, iMax = batchSize; i < iMax; i++) {
@SuppressWarnings("unchecked")
NSMutableDictionary<String, Object> pkClone = (NSMutableDictionary<String, Object>) pk.clone();
for (String key : pkClone.keySet()) {
Object value = pkClone.get(key);
if (value instanceof Number) {
long newVal = ((Number) value).longValue() + i;
pkClone.takeValueForKey(new Long(newVal), key);
pks.addObject(pkClone);
}
}
}
return pks;
}
}
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden