{"id":73261,"date":"2013-01-28T12:35:41","date_gmt":"2013-01-28T12:35:41","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/jonathan-lewis-helps-to-optimize-red-gates-oracle-tools\/"},"modified":"2021-07-14T13:07:45","modified_gmt":"2021-07-14T13:07:45","slug":"jonathan-lewis-helps-to-optimize-red-gates-oracle-tools","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/jonathan-lewis-helps-to-optimize-red-gates-oracle-tools\/","title":{"rendered":"Jonathan Lewis helps to optimize Red Gate’s Oracle tools"},"content":{"rendered":"

Speed, performance,\u00a0optimization: time is of the essence when you\u2019re waiting for an operation to complete. Our Oracle tools<\/a> \u2014 Schema Compare<\/a>, Data Compare<\/a> and now Source Control for Oracle<\/a> \u2014all 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 \u2014 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.<\/p>\n

Last week, the Red Gate Oracle team had the pleasure of welcoming independent Oracle performance expert Jonathan Lewis<\/a> 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\u2019s 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\u2019s what we learnt.<\/p>\n

Day one: the perfect\u00a0<\/strong>optimization<\/h2>\n

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?<\/p>\n

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,\u00a0wasn’t\u00a0something we could consider. We focused on the SQL for populating dependencies between objects, where customers report performance problems most often.<\/p>\n

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\u2019s what it looked like:<\/p>\n

SELECT dba_tab_cols.owner,\r\n dba_tab_cols.table_name,\r\n dba_tab_cols.data_type_owner,\r\n dba_tab_cols.data_type\r\n FROM dba_tab_cols\r\n JOIN dba_types\r\n ON dba_types.owner\u00a0\u00a0\u00a0\u00a0\u00a0 = dba_tab_cols.data_type_owner\r\n AND dba_types.type_name = dba_tab_cols.data_type\r\n WHERE (dba_types.owner IN ('SCHEMA1', 'SCHEMA2'......))<\/pre>\n
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.<\/p>\n
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\u00a0aren’t\u00a0meant for mere mortals, so the post was met with silence. It\u2019s still on OraFAQ<\/a>, long abandoned.<\/p>\n
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\u00a0optimizer\u00a0not to use a merge join. We visited the underlying \u201c$\u201d tables to see if there any other ways in. We discussed rewriting the whole operation in PL\/SQL. Nothing worked.<\/p>\n
In the end, Jonathan came up with an answer: accessing dba_tab_cols can\u2019t be done efficiently, and we\u00a0shouldn’t\u00a0do it at all. For most customers the query would not return anything, so given the cost of the query versus the relative benefit, it\u2019s just not worth the hassle. So\u00a0we’ve\u00a0replaced it with code that takes an educated guess at those dependencies. The operation now takes effectively 0% of the total time. Perfectly\u00a0optimized, you might say.<\/p>\n
Day two: detecting schema changes<\/strong><\/h2>\n
Source Control for Oracle<\/a> is our latest tool. It links Oracle databases to a source control system, so you can track and save schema changes quickly and easily.<\/p>\n
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\u2019s visit, we discussed our options for implementing Oracle database polling.<\/p>\n
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.<\/p>\n
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.<\/p>\n
Jonathan filled in a lot of gaps for us. To deliver notifications to clients, we\u2019d 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\u2019d 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\u00a0hadn’t\u00a0even considered!<\/p>\n
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.<\/p>\n
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.<\/p>\n
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 \u201cdefault\u201d option, and the one we should implement for version one of Source Control for Oracle<\/a>. Jonathan quickly got to sorting out potential pitfalls. We should query obj$\u2019s 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<\/a>\u00a0engine, let us avoid having to do a schema comparison at all! I\u2019ll keep the details of that one up my sleeve for now, though…<\/p>\n
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\u00a0optimized\u00a0our decision making, so we can deliver efficient, easy-to-use tools to our customers as quickly as possible.<\/p>\n
Source Control for Oracle<\/h2>\n
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.<\/p>\n