Prepare for take-off the Tuning-Platform

To understand how differend kind of processes affect underlying database queries, is a main duty for a dba. Even though the most understandings marked by “common-sense” database-tuning-books, we all know the truth. Despite most lovely wrote “walkthroughs” to optimize or detect performance-problems by those books, we have no concrete mention about a definition, leaded by. What means, if we want to use an index? How much is “a few rows”, to use a performance-index? Well we´ll see it in the most cases by turn in to a practical example.

At the other hand, some definitions or guidelines are very unfortunate written. For instance, let me take a small excerpt from the book SQL-Performance Tuning[1]:

The optimum page size is related to the disk system´s attributes. Smaller page sizes like 2KB were once the norm, but disks capacity tends to increase over time, so now 8KB is reasonable, while 16KB is what we´ll upgrade to soon.

Its a matter of course, that we keep such assertions really serious? When i just telling you, that i´ve done this step, with an monster of disastrous decrease of the worthwhile property called “query-time”. Let me say, an increasing of 100 times(query-time) by blocksize-migrating(2K- to 16K) i would say, that this “tip” is just still ending in a farce.

a. Comparing two access-methods on physical datablocks

Therefore we have built a appropriate solution to help students, dba´s or other business-people to understanding - leaded by scenarios - things, escalations, which normally labeled as “prooved for tuning” or similar. Deppict in (a) you can see a first imagination presenting a query-result of a scenario. Intended for a public release date is marked in october, middle. You will read more about this platform at this site, in the right time (and when its revealed).

[1] Trudy Pelzer Peter Gulutzan. SQL Performance Tuning. Addison-Wesley Longman, 2002.

August 29, 2008 | Filed Under Oracle, Database Competence Center