Speed, performance, optimization: time is of the essence when you’re waiting for an operation to complete. Our Oracle tools — Schema Compare, Data Compare and now Source Control for Oracle —all need a database model filled with schema objects and their properties. They also need to figure out object dependencies so they can do their jobs with accuracy — all the time warning the user of potential data loss, long-running operations and other pitfalls. Because Oracle is complex, some of our SQL gets pretty interesting.

Last week, the Red Gate Oracle team had the pleasure of welcoming independent Oracle performance expert Jonathan Lewis to give our SQL the once-over, pick apart feature implementations, and tell us what more we could do for our customers. Not only is Jonathan aware of the various features available within the database to help us accomplish our task, he’s also familiar with many different working environments, so he could tell us the scale our customers may deal with and how a particular implementation might affect them. Here’s what we learnt.

Day one: the perfect optimization

Jonathan asked for specifics about each tool. How fast does it need to be? Which areas perform badly? What features might we add in the future?

The views we query are owned by the system, so creating indexes was not an option. Our tools are client-side only, so creating objects, even temporary ones, wasn’t something we could consider. We focused on the SQL for populating dependencies between objects, where customers report performance problems most often.

Jonathan and I jumped straight into the dependencies code, specifically the SQL, to determine which columns are of PL/SQL type and what those types are. Here’s what it looked like:

The problem? dba_tab_cols would rather be accessed the way it was intended to be: by owner and table, rather than owner and data_type. Not only was this slow to execute, but it could be executed multiple times during our traversal of cross-schema dependencies; more often than not, after all that time, it would return nothing.

Why did we get Jonathan to help rather than ask on a discussion forum, where a friendly member of the community could optimise it for us? Well, we tried that. But the execution plans that data dictionary views produce aren’t meant for mere mortals, so the post was met with silence. It’s still on OraFAQ, long abandoned.

Jonathan quickly established the problem: we needed a faster way to access dba_tab_cols, preferably instructing Oracle to limit the rows in the driving result set. We tried inline sub-queries, instructing the optimizer not to use a merge join. We visited the underlying “$” tables to see if there any other ways in. We discussed rewriting the whole operation in PL/SQL. Nothing worked.

In the end, Jonathan came up with an answer: accessing dba_tab_cols can’t be done efficiently, and we shouldn’t do it at all. For most customers the query would not return anything, so given the cost of the query versus the relative benefit, it’s just not worth the hassle. So we’ve replaced it with code that takes an educated guess at those dependencies. The operation now takes effectively 0% of the total time. Perfectly optimized, you might say.

Day two: detecting schema changes

Source Control for Oracle is our latest tool. It links Oracle databases to a source control system, so you can track and save schema changes quickly and easily.

To do this, we need to poll the database to detect schema changes so the tool can notify the user of changed objects. On day two of Jonathan’s visit, we discussed our options for implementing Oracle database polling.

Option one involved performing a full comparison of all objects in the nominated schemas at regular intervals. Jonathan quickly dismissed this as too heavyweight an operation to perform at such frequency, especially when you increase the number of developers and schemas across an organisation. Multiple clients kicking off regular comparison operations would quickly bring the development environment to its knees, the negatives vastly outweighing the benefits of source controlling your schemas. Not a good idea.

Option two was the most complicated: a DDL trigger to notify clients of a change and triggering a comparison. Creating a trigger was clearly the most elegant solution, and opened up many future features, like team notifications, cached comparisons, and complete support of the shared development model.

Jonathan filled in a lot of gaps for us. To deliver notifications to clients, we’d need a queue as well as a trigger. Queues are one of the less used Oracle features, so we could be in the dark when it came to bug-fixing. On top of that, we’d probably need customers to create a Source Control for Oracle schema, a role that allows execution of whatever packages we install there and for each user to be granted our new role. These were details we hadn’t even considered!

Jonathan explained that having this part of the application installed on the database could even affect our sales process. Many development teams require that third-party code is reviewed and confirmed safe before purchase.

Given all that, while option two seemed like the solution to aim for, there were lots of details to iron out before we could provide a reliable component and easy installation/review process, so it had to be put on hold for the time being.

Option three involved an initial check on LAST_DDL_TIME of all_objects to look for changes, followed by a full comparison to determine what those changes are. It became clear that this was the best “default” option, and the one we should implement for version one of Source Control for Oracle. Jonathan quickly got to sorting out potential pitfalls. We should query obj$’s LAST_MODIFIED_TIME whenever we can (as opposed to dba_objects LAST_DDL_TIME), and be aware that DBLinks never have a LAST_MODIFIED_TIME, because you can only drop or create them. We could either count the number of objects compared to our last poll in order to detect drops, or compare the list of objects in the database to those we already have in source control. Ultimately, we came up with a solution that, with a little work to improve the Schema Compare engine, let us avoid having to do a schema comparison at all! I’ll keep the details of that one up my sleeve for now, though…

Thanks to Jonathan, we removed one of the slowest parts of our code and found answers about everything from database polling to the wording in our user interfaces. In that sense, Jonathan not only improved our code but optimized our decision making, so we can deliver efficient, easy-to-use tools to our customers as quickly as possible.

Source Control for Oracle

Source Control for Oracle is a new tool that makes the process of checking database changes into SVN or TFS simple. Just type a log message and click Check in. See who modified the database and why, and access a history of every change.

Download a 28-day trial