[#SAK-2909] Performance problems with background SQL queries in Oracle

Sakai

Performance problems with background SQL queries in Oracle

Created: 21-Nov-2005 19:36 Updated: 24-Oct-2008 06:40

Component/s:

Global

Affects Version/s:

2.1.0

Fix Version/s:

2.1.0

Time Tracking:

Not Specified

Environment:

Sakai with Oracle

2.4.x Status:	None
2.5.x Status:	None
2.6.x Status:	None

Description

« Hide

Certain SQL commands and settings were found to be major performance problems with CTools' Oracle. These were:
- validating the db connection with a simple SQL statement
- setting the default transaction isolation level

(Note: doing a rollback after each connection use, and setting the auto-commit each time to the default value were also a problem and have been fixed already in the TRUNK)

Changes were put in CTools to address this, but the kernel db configuration was not structured to allow these changes to be applied to the TRUNK, so that the OOTB Sakai supports HSQLDB with these settings, and for Oracle, we can disable them as needed in the sakai.properties files.

Change the kernel db configuration to allow this, and recommend the Oracle settings (here, and in the various distributed properties files and examples).

All

Comments

Work Log

Change History

Subversion Commits

Sort Order:

Ascending order - Click to sort in descending order

[ Permlink | « Hide ]

Glenn R. Golden - 21-Nov-2005 19:57

The way to set the default transaction isolation level is different. It is an override property now, rather than a placeholder property. Set it in sakai.properties like this:

defaultTransactionIsolationString@javax.sql.BaseDataSource=TRANSACTION_READ_COMMITTED

* * *

For Oracle, we (CTools at University of Michigan) found it best for performance to leave the validation query and isolation level default unset. To get these values unset now, put this in you sakai.properties file:

# for best oracle performance
validationQuery@javax.sql.BaseDataSource=
testOnBorrow@javax.sql.BaseDataSource=false
defaultTransactionIsolationString@javax.sql.BaseDataSource=

[ Permlink | « Hide ]

Megan May - 24-Jan-2006 16:55

This issue was not verified in the formal QA process but is included in a production version of Sakai (2.1). If you find this is still a problem, please re-open.