Oracle In-memory Database—Speed and Simplicity Meet Up. Have you tried it out yet?

Oracle In-memory Database—Speed and Simplicity Meet Up. Have you tried it out yet?

One of the major announcements Oracle made during the Open World was the launch of Oracle In Memory Database Option. The In-Memory option to Oracle Database 12c is 100 percent compatible with existing applications and leverages all existing Oracle Database functionality. At first look, some key benefits jump out:

  • Though this is a paid option, it promises a high ROI
  • It takes just one change in parameter to turn it on
  • From published information, it increases query performance on identical hardware by up to 100x (though it does require a lot of memory)
  • It also provisions for a 2x to 4x increase in insert and update performance (after removing unnecessary indexes)
  • It is backward-compatible to all existing applications and does not require any changes to applications

 

Beyond the speed and performance increases, the game-changing advantage I see is that the in-memory database stores data both in row orientation (as oracle always has) and in column orientation—similar to column-oriented databases like Vertica. Why is this important?

This can be a game changer because as we all know, some of the most significant tasks in architecting and maintaining databases involve indexes. Database architects and administrators spend quite a bit of effort in determining what indexes are necessary and what type they should be (considering there are numerous types). And once they plan this out, just the creation of the indexes is a huge task, after which there is also ongoing maintenance. And in many cases, we all are constantly created new indexes that were not foreseen in the design requirements earlier.

With the in-memory option, the only indexes that are necessary are those that enforce referential integrity—those involved in primary and foreign key relationships. Additionally, systems that have a large number of indexes now may see a significant reduction of disk requirements as the need for those indexes is eliminated.

Other vendor’s offerings, such as SAP HANA, are taking a similar approach, implementing in-memory storage and processing, and columnar oriented data management. However, Oracle’s offering is special because nothing, including hardware and applications, has to change in order to take advantage of this new technology and the significant performance benefits that it yields. This could help change the landscape completely—especially for organizations that deal with massive, distributed data warehouses on a global scale.

Have you tried out the in-memory option on Oracle 12c? What has been your experience? We’d be delighted to share your experience with our readers.

Using the Data Dictionary to find hidden data in SQL Server

Using the Data Dictionary to find hidden data in SQL Server

A client asked me recently how he could find a string in his SQL Server database without knowing what table or column it was in. The string was a translation of a code that appeared on one of the UI screens. This was a packaged CRM database and he had no documentation on the schema. With hundreds of tables whose names were not obvious, he had no idea where the translation might be kept, but wanted to do some reporting using the translations. It was like finding a needle in a haystack!

In order to help, I wrote a utility to find the data for him. Using the data dictionary tables and a little dynamic sql,  I created a script that would search for a string in every column of every table in the database.

We can do this by using the information_schema.columns view to create statements that insert search results into a temporary table. We want to record the table, the column, and the full contents of any column that contained the string we wanted to search for in order to provide context for the search results.

There are some complications that we have to address as we do this.  First, since we want to do a like comparison against any of the fields we must restrict the search to char and varchar fields. This is necessary because the like comparison cannot be used against xml and some other datatypes. That restriction works in this case because I was searching for a string and it was very unlikely that this string would be embedded in an xml field. Second, to prevent errors resulting from spaces, hyphens, or other special characters in table or field names, we must surround the object names with brackets — this is always a good practice when using dynamic sql with sql server.

Since this utility uses the like comparison with a wilcdard before and after the search string, indexes will not be used so performance will be an issue. This utility is best run during non-production hours and may take a long time to complete. This can be mitigated by modifying the application to remove the leading wildcard and then indexes on any of the columns would be used, but this will only find strings that start at the beginning of the column value that is being checked.

We now have a nice utility that give a report of all places where the string lives. The utility can easily be extended to handle numbers, dates, and other data types as necessary. This script works with SQL Server but similar scripts can be created for any major database that has a user accessible data dictionary.

Note that this procedure does not automatically delete the prior contents of the search_findings table. You may wish to add that to the script if you don’t want the results to be cumulative.

The instructions follow.

1. Start by downloading find-it

2. Create the table that will hold the search results using create_search_results_table.sql

3. Create the find-it procedure using create_find-it_procedure.sql

4. Issue the search with the following command:

exec find_it  ‘search string’

Oracle In-memory Database—Speed and Simplicity Meet Up. Have you tried it out yet?

MySQL Parser Deficiencies

MySQL is a very popular database for many applications. But the parser has some important deficiencies that developers need to be aware of. The parser is inconsistent and not robust in the way that it enforces statement syntax. This may lead to ambiguity and application defects that are very difficult to find. I include one example below and link to another blog article that discusses another.

Inadequate Group By Parsing

The MySQL group by function does not require that all non-aggregated elements in the select list be present in the group by clause. This causes an ambiguity because if a given element in the select list is neither present in the group by clause, nor aggregated in the select list, then an arbitrary value is chosen (it actually seems to be the first value but I don’t know if that is always the case).  Consider this example table that contains the number of games a set of players has played in each of four seasons.

We can see that each player has played 100 games in each of the four seasons.

Now consider this simple query to get a sum of all the games that the player has played across all seasons:

As expected, we can see that each player has played 400 games.

But watch what happens if a novice developer places the season number field in the select list without placing it in the group by clause as well:

If the developer does not realize his error, he will think that each player played 400 games in season 1!

The parser should have prevented the query from being executed with the season_number in the select list when it was neither aggregated in some form or present in the group by clause.

This type of error is very difficult to find because the resulting data looks plausible unless you really know the data.

Function Syntax Inconsistency

The next example is related to the way that the parser enforces one statement syntax vs another. This involves the tolerance of whitespace (line breaks) when specifying the sum function vs the format function. This inconsistency does not concern me as much as the group by problem because it does result in an error that is visible to the developer, but it does illustrate a lack of consistency and robustness in the parser.

As MacLoghLainns Weblog describes, the SUM function requires that the opening parentheses be on the same line as the SUM keyword, while the FORMAT function does not have that restriction. View the blog article for more details.

Please reply back with comments or other issues that you have found that might help others avoid unexpected problems in their MySQL applications.

Visit www.budaconsulting.com for more information about Buda Consulting.

Oracle Editioning — Upgrading database applications while in use

Oracle Editioning — Upgrading database applications while in use

Among the features introduced in Oracle 11g is Edition Based Redefinition. This feature enables us to have multiple active versions of database objects in order to facilitiate online application upgrades.

Prior to this release, if an application used stored procedures or triggers, upgrades required database downtime in order to redefine database objects. This new feature facilitates zero down-time application upgrades but it is a complicated feature and using it can be difficult.

Our colleague, Dennis Heltzel, is writing a series of blog posts about the nuances of using this important new feature. The following four posts cover the general concepts of Oracle Editioning and discuss some important limitations and problems that he found while implementing it.

I hope you find these helpful:

https://dheltzel.wordpress.com/2013/05/25/editioning-in-an-oracle-database-concepts/

https://dheltzel.wordpress.com/2013/05/29/editioning-with-synonyms/

https://dheltzel.wordpress.com/2013/06/05/editioning-views/

https://dheltzel.wordpress.com/2013/06/08/editioning-new-data-dictionary-views/

Critical Oracle Security and Stability Flaw Discovered

Infoworld today announced that they have been researching a flaw in oracle that can result in the inability to restore your database. Large oracle shops with interconnected databases are most at risk.

The problem revolves around oracle’s SCN (system change number). The number has a limit that if exceeded, can render a database unusable, even after restoring the database. Under normal circumstances, this limit would never be reached. However, there are two ways that the limit can be reached accidentally.

  • A bug in Oracle’s hot backup mechanism results in a sharp increase in the SCN number under certain conditions.
  • The SCN can be increased manually, resulting in all connected databases to increase their SCN as well.

The January Oracle Critical Patch Update has a patch that resolves the hot backup problem. We recommend that this patch be applied immediately, especially if you are a large shop or use hot backups. Another fix increases the limit and and makes it less likely to reach it, but the accidental (or deliberate) modification to an SCN remains a vulnerability. Extra care should be taken with all databases that connect to any critical databases in your environment.

Read the full article for more details.

If you have any questions or need assistance, please contact us.