Most recent blog entries

How to Keep Dead People Out of Your Database

john@excellentmanagementsystems.com — Thu, 15 May 2008 07:00:00 GMT

If dead people are accessing your database, data breach is right around the corner. In this week’s blog, we discuss database access management concerns, and how to architect a compliance system to handle them.

Toad Saves Mucho Dinero

Bert Scalzo — Wed, 14 May 2008 12:15:00 GMT

Sometimes people will compare Toad against low cost competitors or freeware (remember, Toad offers a freeware version as well). The idea being that one can save money by going with a lesser product. That seems fiscally responsible – until you do the math that is. So, let’s work a simple example to demonstrate just how valuable Toad can be to your organization.

The table below shows various resource types, the percentage of time they do database related work, the relative productivity increase by using a GUI tool, the percentage of Toad’s superiority to the other low cost competitors or freeware, and the annual net benefit of using Toad versus those lesser tools.

So with Toad starting at a mere $870 – why would anyone choose to work without it? And I’ve very purposefully chosen very, very conservative numbers. Since so many Toad users fanatically tell us they love Toad and could not do their jobs without it – the actual numbers could more realistically be as high as the following table.

So even in a small company with just five developers, one DBA, two database analysts, and just one of each manager type – the annual savings from using Toad amounts to nearly $84,000 dollars per year. And remember, that’s the additional savings of using Toad in lieu of lesser products! Therefore Toad’s meager premium over lesser products is quite easily justified.

Batch Optimizer Part 4 – Options Settings for the Batch Optimizer

Richard To — Fri, 09 May 2008 13:53:00 GMT

Written by Rene Woody

This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle. It covers the execution options settings for determining the criteria for selecting the best alternative, for terminating longer running SQL, and eliminating the effect of caching on the comparison run times.

Selecting the Best Alternative

The best alternative is selected based on the setting selected in the Best alternative selected based on lowest option. This is either the fastest Total elapsed time or the fastest First row elapsed time. The Total elapsed time is how long it takes to retrieve or process all the records. The First row elapsed time is how long it takes to retrieve or process the first record.

Termination Criteria

In testing of the SQL alternatives, the goal is to find a SQL statement that runs faster than the original. So as soon as it is determined that a SQL alternative is not the best alternative, it should be terminated to minimize the time it takes for testing.

The Terminate execution of SQL alternative if it runs longer than option provides three settings for determining when to terminate the execution of a SQL statement.

Run time of fastest SQL is the default setting. With this option, the original SQL statement is executed first and the time from that statement is used as the termination time for the next SQL statement. When a SQL statement runs faster than this termination time, the faster time is used as the new termination time. So you are always using the fastest run time as the termination time for the next SQL statement.

Run time of original SQL terminates a SQL alternative if it runs longer than the original SQL statement.

User defined time lets you set your own termination time. It first retrieves the run time of the original SQL statement. When the SQL alternatives are executed, they are terminated if they reach the specified time.

Accounting for Caching

Caching the data, the indexes, and the SQL statement can affect the comparison times if one SQL statement has to do some caching and others do not. Therefore, options are available so that you can select the one that provides you with the most accurate comparison of the run times in your environment.

The Retrieving run time executing optionhas these settings:

Original SQL twice using second run time and all others once

The first time you access data from a table, the data is cached into memory. This process takes a few moments. The next time you access that data, it is already in memory. Therefore, the first SQL statement executed will have the additional time it takes to cache the data included in the run time. Whereas, the SQL statements that follow do not have the additional time included in their run time. So to have a comparable test, the first SQL is run twice and the time from the second run is compared to the time from the other statements which are run once. In this case, all SQL statements are executed with the data cached.

All SQL twice using the second run time

For fast running SQL statements, executing all SQL statements twice and using only the second run time enables you to eliminate two additional caching factors that can affect the accuracy of the comparison results: caching the SQL statement and caching the indexes. If some SQL statements have been recently executed, then the SQL information for those statements is likely to be resident in the cache and the statements may execute faster because the SQL statement does not need to be cached. Also, if some of the SQL statements use different indexes, one index may be resident in the cache and the other may not be in the cache. So running all the SQL statements twice ensures that each index is cached.

All SQL once

For long running SQL statements, there is no need to run any statement twice since the effect from caching is diminished over time.

If you would like to learn more about Quest SQL Optimizer for Oracle, please visit the Inside SQL Optimizer for Oracle community.

To Write, or Not to Write? Good Question!

john@excellentmanagementsystems.com — Thu, 08 May 2008 07:00:00 GMT

Here we take a look at end users sending data back to the database. Is it a good idea? What are some of the reasons why your end users would want to do this? And, four key tips to consider when designing in this functionality.

Yet another Little Toad Gem

Bert Scalzo — Wed, 07 May 2008 13:23:00 GMT

There are so many useful little make or break features inside Toad, it’s very often difficult to pick one to highlight. A few weeks ago I started to divulge some things coming in Toad 9.7 (and already in the beta). One item I mentioned was the enhancements to the Database Browser, where most DBA screens and/or utilities can now be launched via a right hand mouse menu option. This can almost eliminate the need to launch those items from the Main Menu->Database list of menu options. But the Database Browser has been around for a while now – and it has one very cool feature that makes it indispensible!

Have you ever needed to know what resources were being consumed by all the databases on a shared server? Well, the database browser makes short work of that task, and for all of the more useful things one might need to measure. Look at the screen snapshot below. If you either select the server node or multi-select databases under that node, the Database Browser shows all the associated information in the data grid. But look at the area near the bottom circled red – it also shows the cumulative statistics for all those databases. So here I can see that my two databases use 8.6 GB of disk space, of which 7.8 GB are used. And this feature works like this for all the tabs that make sense.

Keep It Under Control With SOD

john@excellentmanagementsystems.com — Thu, 01 May 2008 07:00:00 GMT

For the last couple of weeks we’ve been talking about controls. Here we round out our series by talking about Segregation of Duties ( SOD ) as a way to keep things in control. Here are some key tips for designing systems that demonstrate control with SOD.

Working With Date Ranges in Toad for Data Analysis

Daniel Norwood — Wed, 30 Apr 2008 17:13:00 GMT

Often when writing a query you need to get data from a range of dates; when you start thinking about automating the SQL query it’s even more important. In this post I’ll show you a quick trick to let Toad for Data Analysis figure it out for you!

For this example I’ll be working with Oracle though it’s no different for other platforms. In my Oracle database, if I wanted to get a list of the orders that were placed last year I’d have to write something like the following:

SELECT ORDER_NUM, BOOKINGS_DATE
 FROM LICENSE_BOOKINGS
 WHERE (BOOKINGS_DATE BETWEEN to_date
                                 ('2007-01-01 00:00:00',
                                  'yyyy/mm/dd hh24:mi:ss')
                          AND to_date
                                 ('2007-12-31 23:59:59',
                                  'yyyy/mm/dd hh24:mi:ss'));

Sure, some people are familiar with the syntax. But what about the syntax for the last quarter, the current month or just last week? It’s not rocket science, but it can get confusing and is quite error prone.

Now, in the SQL above, I just hard coded the dates – that’s a dangerous practice in most cases. So what do I do? I start thinking about maybe writing it a little bit differently… maybe you use a bind variable or I start working with SYSDATE to get the system’s date and first and work backwards from there. If I want to automate the query, this is the path that I have to go down.

In Toad for Data Analysis we make life a little bit easier – we write the date ranges for you. So now instead of using complicated SQL to get the job done, you can drop in something like /*Last week*/ or /*Last 30 days*/ and Toad for Data Analysis will dynamically insert the correct SQL. Keep in mind that this is a Toad for Data Analysis feature – if you execute the SQL statement in something like SQL*Plus the Oracle compiler will see /*Last week*/ as a comment and your statement will not run.

Here’s how the revised query will look:

SELECT ORDER_NUM, BOOKINGS_DATE
 FROM LICENSE_BOOKINGS
 WHERE (BOOKINGS_DATE = '' /*Last month*/ );

(Note that the criteria is empty – I use two single quotes with no space. This is important. Toad for Data Analysis will insert the correct SQL between these two single quotes when I execute the query.)

If you use the Query Builder in Toad for Data Analysis it’s super-simple. Once you select a column with a date datatype, click on the “Where Clause” field to open a new window where you pick from a list of available ranges. Viola! You’re done.

For those of you writing SQL scripts by hand, here’s a list of the available commands. Keep in mind that they’re CaSe SeNsItIvE… if you don’t write it exactly as you see here then it won’t work.

/Today/	/Current month/
/Yesterday/	/Last month/
/Current week/	/Current quarter/
/Last week/	/Last quarter/
/Last 7 days/	/Current year/
/Last 15 days/	/Last year/
/Last 30 days/

If you want to automate the script you’ve created, save it to disk and then click “Automate” on the Toad for Data Analysis toolbar. After you build the automation routine you can click “Schedule” to add it to the Windows Scheduler and set it to run anytime.

If you would like to see other articles on Toad for Data Analysis, please email me at daniel.norwood@quest.com.

How Data Modeling Can Save Money

Bert Scalzo — Wed, 30 Apr 2008 12:46:00 GMT

It’s not uncommon to discover databases that have simply evolved over time rather than having been built according to some overall design or “master plan”. And there seem to be no shortage of answers as to why this occurs. Sometimes DBA’s are just far too busy with a myriad of other tasks. Sometimes developers are under the gun for deliverables so quickly that there’s just not time. Or my favorite, this project started as just a couple new tables and then ballooned into a monster. Whatever the reason, the situation exists far too often in the real world of information systems.

Let’s put this into perspective. Would you purchase a brand new house where the builder simply showed you a high level “floor plan”, and then said “trust us” – we’ll just hammer together the lumber and stuff until we’re done, don’t worry. Not too sure very many of us would so nonchalantly encumber ourselves with a 30 year loan on such vague promises. We automatically expect the home builder to use that rough diagram as the precursory for an architectural blueprint that meets local codes, standards and ordinances. We expect the city zoning commission or code enforcement division to sign off on key items during the construction. We expect to spend the week before taking ownership to inspect the home and to mark things to be fixed before the closing. In short, when it’s our money and/or financial future – we expect things to simply work right.

The same is true in many other disciplines that we utilize every day. We get on elevators knowing that they were well designed and recently inspected. We readily fly on airplanes knowing that the FAA and other government agencies are making sure it’s safe and well regulated. We undergo surgery at hospitals knowing that doctors and administrators have jumped through major hoops to guarantee a reasonable level of safety. Yet we still build computerized systems with all the skill of a MASH surgeon – just get it stable enough to pass onto the next guy. No wonder then that both database administrators and application developers are quickly catching up to lawyers and politicians in terms of likeability. :-(

So let’s start this week by just looking at the two most critical database success factors: effectiveness (doing the right thing) and efficiency (doing it expediently).

How can anything be effective if it’s just cobbled together haphazardly over time? I’m not saying that everyone must do a logical data model and transform that into a physical data model. That may not be most peoples’ “cup of tea”. But slowing down a wee bit to think of the overall physical design should yield a more consistent business solution. That should translate into higher effectiveness.

The same can be said for efficiency. So by not simply and seemingly randomly adding tables and columns on an as need basis can easily result in a more fluid design. It’s much easier to code against such a design, too, which can also mean lower costs in terms of the development (i.e. less complicated to work with). Furthermore, this generally translates into lower ongoing maintenance costs as well – and often far less unplanned crisis.

Find all this “pie in the sky” thinking to be too esoteric or too academic? Well I’ve been an expert witness in several trials where companies have sued consulting firms that built substandard applications and databases. In every single case, the plaintiff won substantial judgments based solely on poor database design. So far my testimony has directly resulted in seven figure financial awards. That’s millions of dollars per case that could easily have been saved by slowing down and doing more planning or design. So as the old song goes “Look before you leap, still water runs deep” …

Why Can’t Johnny Use BMF?

Mike Ault — Tue, 29 Apr 2008 15:05:00 GMT

As many of you are aware Quest provides a tool known as Benchmark Factory, BMF for short. BMF is a load generation tool that also allows you to install small databases for testing. The tools out of the box can provide TPC-B, TPC-D, TPC-H, AS3AP and many other standard benchmarks as well as allowing goal and scalability tests from your own code, either manually entered or scanned from text or, in the case of Oracle, from active database memory. BMF does all this for several databases such as Oracle, SQL Server, DB2 and MySQL with no additional licenses required.

In the last year and a half I have been involved with 2-3 clients who wanted to do large scale testing with the BMF suite, like between 2000-20000 users and hundreds of gigabyte databases. Usually the conference call begins with “BMF doesn’t work” and goes down hill from there. However, BMF does work, but there are certain things that need to be addressed before BMF is used.

Johnny doesn’t understand the tool

The first thing to remember about BMF is that it is a load generation tool providing the capability to run benchmarks and other tests against a standard benchmark database or in the case of application specific code, against your own database. As a courtesy we provide the capability to generate the basic database structures and build small test environments for the standard benchmarks. BMF is not a benchmark artificially intelligent facilitator (BAiRF for short.) BMF cannot analyze your entire system including the disk setup and tell you how to reconfigure your disk array, partition the specific size of database tables and indexes and load a terabyte database in under 3 hours. Maybe someday we will come up with BARiF but for now we have BMF.

Johnny doesn’t understand the database

The next thing to remember is that BMF is not an analytical tool that will look at your database setup and make recommendations as to optimizing the parameters or configuration for a benchmark. Perhaps when we build BAiRF we can add in that functionality; however, now you must use Performance Analysis or Grid Control (with the appropriately licensed tuning and diagnostic packs) to give you tuning recommendations. Many database and perhaps system parameters will need to be adjusted to achieve a successful benchmark. A good source for what needs tweaking is the www.tpc.org website in the full disclosure documents. In a full disclosure document the TPC-C author needs to completely describe their system setup, including initialization parameters for Oracle, how they loaded the data, and, how their disk subsystem was organized.

Johnny doesn’t understand the IO subsystem

Another item to remember is that BMF is not an IO subsystem analyzer that can tell you if you have configured your RAID setup with enough disks, have enough host bus adapters or have utilized the proper form of RAID, all of this falls on your shoulders (at least until BAiRF). In order to get a good benchmark result you have to provide high IO bandwidth with maximum concurrent access. This is only achieved through large numbers of disks in a RAID10 configuration, in most cases. For example, in order to get decent TPC-H values (the data warehouse/DSS benchmark) the normal ratio of disk capacity to database size is 20-30 to 1, so if you want to run a test against a 1 terabyte database, you have better provided 20-30 terabytes of storage. This 20-30 to 1 ratio is based on the study of many TPC-H full disclosure documents available from www.tpc.org. This amount of disk space is needed not because of some need to pay large power bills, but because of the needed disk IO requirements of the TPC-H. There is a direct correlation between the number of disks and the success of the TPC-H. Of course having said this, you may notice some recent benchmarks where the disk capacity to database size ratio is much smaller. These are using advanced caching and in-memory database systems as well as solid state disks. A single 2 terabyte solid state disk array can provide 100,000 IO/sec and fits in a 4U rack space.

Johnny doesn’t understand the benchmarks

Finally, all that BMF provides for database benchmark physical build and load is the basic structures and capability to load a small database data set. In order to achieve good results with any of the large scale benchmarks you must add indexes, use partitioning and use advanced loading techniques such as external parallel tables with create-table-as-select (CTAS). I suggest if you need to build large datasets for a large scale test you use the dbgen tool which is available as a free download from, you guessed it, www.tpc.org. I also suggest you spend some time burning the midnight oil studying a few of the full disclosure reports, particularly the appendices where they describe in great and gory detail how they set up the database, system and disk arrays for their test. Why invent the wheel?

Summary

So, what can BMF do for you out of the box? It can install and run small standard benchmarks (however, you may have to add a stray index or two) and can be used to perform scalability tests on code as well as goal tests. The BMF tools can harvest code from text, trace files or active memory and provide the needed scripts to provide for random input of any of the needed variables. I myself have used BMF to test the applicability of partitioning, update on commit materialized views for testing, scalability tests using code from a users application to test new hardware, determine what additional hardware would be needed to support a larger user base and database data set and run TPC-C and TPC-D against solid state arrays in both single and RAC systems. I have also used BMF for harvesting code from single trace files, multiple trace files, active memory and text files as well as done manual entry.

As long as you understand the limitations of BMF, what you want to test and the benchmark or test you are doing, BMF can provide great benefits to your organization. On the other hand if you refuse to do the homework needed to properly layout your disk array and setup your database for the test you wish to run, then you will be faced with great frustrations. So unless you want to wait for BAiRF, just use BMF but do your homework and you should find it satisfactory.

Batch Optimizer Part 3 – Options Settings for the Batch Optimizer

Richard To — Fri, 25 Apr 2008 14:44:00 GMT

Written by Rene Woody

This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle. It covers the execution options settings that determine which SQL statements will be automatically executed by the Batch Optimizer process and the method used for their execution.

Executing Options

When the optimization process is finished, the execution of the original SQL statement and the selected alternatives is automatically started if the Automatically execute SQL rewrites after optimization setting is selected.

The specific type of SQL statements which are executed automatically is determined by the SQL types selected under the Execute only these SQL statement types: SELECT, INSERT, UPDATE, or DELETE. By default, only the SELECT SQL statements are executed automatically.

Note: INSERT, UPDATE, and DELETE SQL statements are run in a transaction that is rolled back after each execution so that the data in the database is not changed by the execution process.

The Execute only these SQL alternatives setting also determines which SQL alternatives are executed. All of the options under this setting are based on the Oracle cost estimation for each SQL alternative in comparison to the Oracle cost of the original SQL statement. By default, only the ten SQL statements with the lowest Oracle cost are executed.

When executing the SQL statements in the Batch Optimizer, the default execution method is Run on server. This means that all SQL statements are executed on the server and do not return the data from the SELECT statements to the client. Executing the SQL statements with this method provides you with the time it takes the SQL statement to execute on the Server. It does not cause extra network traffic by sending the data to the client. When using this method, your logon account must have the privilege to use the SYS.DBMS_SQL package to execute SQL statements on the server.

If you would like to learn more about Quest SQL Optimizer for Oracle, please visit the Inside SQL Optimizer for Oracle community.

/Today/	/Current month/
/Yesterday/	/Last month/
/Current week/	/Current quarter/
/Last week/	/Last quarter/
/Last 7 days/	/Current year/
/Last 15 days/	/Last year/
/Last 30 days/