Yesterday Scott Maziarz got this error in the log of an Oracle Application Server instance:
[Tue May 16 13:27:52 2006] [warn] long lost child came home! (pid 8134)
I can’t decide if this is a good thing or a bad thing.
Yesterday I attended a Banner/Oracle user conference at Wellesley College. This annual, one-day event of largely peer presentations was a great opportunity for networking and knowledge exchange. Here are some of the take-homes I got out of this conference in no particular order. These are just my observations and opinions and pertain to the scope of our institution and environment.
Marc Kelberman, Oracle pre-sales engineer for higher-ed gave a presentation on RAC/Grid control and SQL Developer.
Very few have moved from 9i to 10g. Those who have, or will soon, are going straight to 10gR2.
It is important to go to the latest patchset for 10gR2.
Oracle’s RAC/Grid technology is very cool, but it is unlikely it would offer much to our small (6,000 student) university. Larger universities may benefit from it.
Grid/RAC requires a shared storage architecture.
It would take a great effort between systems, networking and database administrators to implement RAC/Grid.
Around 75% of the institutions I spoke to were running Oracle on Sun Solaris.
None of the institutions I spoke to were running Oracle on Linux.
None of the institutions I spoke to were running clustering/RAC/Grid Control.
Several institutions are running some type of network attached storage (NAS) to store their data files.
One institution (I believe this was the host institution, Wellesley) is running their Oracle home directories on network attached storage. This allows them to maintain only one oracle home per database version saving hours of work per upgrade.
Most institutions are still relying on cold backups as their primary backup method.
Only a couple institutions I talked to have adopted Oracle RMAN for backups.
Oracle SQL Developer
Marc did a good demonstration of SQL Developer, but this product is hard to appreciate until you’ve used it. Thankfully it’s free, so there’s no good reason not to test drive it.
Dan Sterling, Chief Technology Architect for SunGard Higher Education presented on SunGard HE’s plans for identity management. It looks like they will focus on integrating with third party tools via open standards.
Banner Student Information System
One university mentioned that when a user asks for a modification to their student information system they require that the user submit a request for product enhancement with the vendor before a local modification is made. This seems like a good policy.
The relatively small number of attendees made this conference great for networking. It’s very interesting to interact with universities of different sizes. Some of the universities in attendance had one administrator for their Oracle databases, application servers and application support, while others have a large staff and highly individualized positions.
Though this conference happens near the end of the academic year it was a nice diversion from summer planning. Beyond the networking, the take-homes are more than worth the day out of work.
Thanks to the diligence of my coworker Scott Maziarz we have been able to run some practical performance tests on the new Sun Fire T2000 we got on a try and buy program from Sun.
About our environment
While we’re not truly equipped for traditional load testing here at Plymouth State University I decided early on that our daily datamart scripts would be a good test of this system’s capabilities. These scripts are a combination of vendor supplied and homegrown code which create datamarts for reporting. The scripts run separately take between a minute and several hours to complete. They rely heavily on joins and functions so, while there is a fair amount of disk I/O, the speed of the processors and memory should play a large role.
Oracle 9iR2 was used for the testing on both systems. The database configuration was not altered, so despite the point that the T2000 has twice as much memory as our V440s (16GB versus 8GB) Oracle will be using the same amount of memory on both systems. We also unfortunately did not have the opportunity to do any performance tuning on the T2000. As such I consider this a comparison with a straight out of the box T2000. With some careful system tuning the T2000 would probably perform even better.
The two systems
Sun Fire T2000
Sun Fire V440
While these two systems are different in many ways they are comparable in price (the V440s are actually quite a bit more expensive, but this one is around two years old now.) All testing was done on internal disks making the results dependant on the entire system performance.
Oracle was not reconfigured to take advantage of the extra memory in the T2000.
For the testing we chose a set of 19 datamart creation scripts which we run daily in our production environment. To assure that there is no additional load on the V440 we ran the tests on our reporting instance which is on a relatively idle system.
The first test is to run the 19 datamart scripts staggered. This is how we run them in production. Four scripts are started every half hour to spread out the load on the server. There’s a lot of overlap, but the staggering is enough to keep the system responding normally.
The graph above represents the runtime of each job individually. A shorter line represents a quicker runtime and we can see that the T2000 consistently outperformed the V440 often running n a quarter the time!
The total runtime for all 19 jobs was 2915 minutes on the V440 and 847 on the T2000. On average it took only 29% as long for the jobs to run on the T2000!
For the second test we ran all 19 jobs at once.
We still see a significant improvement in performance, but not as high as when these were run staggered. Here the T2000 completed the tasks in 59% of the time of the V440. I attribute this to contention for disk on the T2000.
Thanks to Scott Maziarz for running the bulk of these tests and compiling the results for me.
The Sun Fire T2000 has certainly proven its worth. Some may be put off by the relatively low processor speed (the model tested was a mere 1GHz) but it is clearly not an impediment. The 8 core CPU seems to be up to the challenge and I’m sure with additional tuning I’m sure they’d scream.
With increased performance and higher efficiency than the comparably priced V440, the T2000 will definitely be in our future. The lower energy consumption and lower heat output would be a welcome change in our already taxed data center and the compact 2U size should be an easy fit in any rack.
Oracle’s rapid development web application tool HTML DB (recently renamed to Application Express) seems to be a perennial topic for my IT team.
Oracle offers this description of the capabilities of HTML DB:
Using only a web browser and limited programming experience, you can develop and deploy professional-looking applications that are both fast and secure.
What Oracle doesn’t mention in their description is that you could easily expose more than you wanted to. As with most apps that expose your data, security and best practices should be the main focus.
Burleson Consulting has provided a great outline of many of the pitfalls of HTML DB. These vulnerabilities are common on web servers, but what this document highlights is that Oracle has not tied up all the loose ends for you.
It’s clear that HTML DB/Application Express is not something to be entered into lightly. If setup carefully it can be used to increase security by reducing the number of access points for your data, but to get to that point without making the type of mistake that lands your name in the paper you need to be ready to address database security, network security, web security and user education. Of course a passing familiarity with how Oracle typically does things wouldn’t hurt either.
Check out Burleson Consulting’s article on HTML DB vulnerabilities for more info.
Oracle SQL Developer (formerly Project Raptor) was released last month with little fanfare; however its release represents a quantum leap in functionality and ease of use over previous Oracle provided SQL development tools.
SQL Developer is Oracleâ€™s new, free graphical tool that enhances productivity and simplifies database development tasks. With SQL Developer,you can:
- Browse database objects
- Run SQL statements and SQL scripts
- Edit and debug PL/SQL statements
- Run provided reports
- Create and save custom reports
Now all these features exist in Quest Software’s product Toad for Oracle so why switch? Well, here’s the hook… SQL Developer is free. Yup, free. It also has another strategic advantage over Toad… SQL Developer is cross platform! I am currently running it side-by-side on Mac OS X and Windows XP, and there is also a Linux version available. The Mac and Windows versions are both extraordinarily easy to install, configure, and use. I can only assume the Linux version follows this trend.
So what’s the down side? Well, I’m personally disappointed that they changed the name from “Raptor” to “SQL Developer”, but that aside, you can’t use it on databases earlier than 126.96.36.199.
A small problem for Toad users will be adjusting to the date format in SQL Developer. Toad defaults to showing the time when displaying date data types while SQL Developer will only show the date. The solution to that is to add a TO_CHAR around the date column in the query (for more information on the TO_CHAR function, check out the Displaying Dates section of my article Oracle, SQL, Dates and Timestamps). Toad users may also miss being able to click on the header of a column to change the sort order of the output, but this can also be overcome by adding an ORDER BY clause to the query.
Setup was quick, taking less than a minute to download, unzip and connect (yes, one minute, you’ve probably spent more time reading this than it takes to install). As you’re setting up SQL Developer you’ll notice that it does not require a TNS Names or other descriptor file to find databases. When you set up a connection you are prompted for the hostname and SID of the database. This assures maximum flexibility but does run the risk of confusing end users, since they can nickname the connections anything they want to.
Below are some screenshots of SQL Developer, but if you’re interested, just go out and get it. It’s free after all, what do you have to loose.
Click on the thumbnail for a larger image.