Pythian Group

The day has arrived, probably one of our favorite days at Pythian – Geek Pride Day! Since May 25 (the “official” day according to Wikipedia) falls on Saturday, we celebrate it a day early so everyone can participate. For the third year in a row, our 250+ global Pythianites have the opportunity to learn more about each others geekisms by offering an International Geeky costume/t-shirt contest, while our office locations hold a Pot Luck to provide the opportunity for everyone to relax for a bit and get their geek on at lunch. The old saying “birds of a feather flock together” is very true, every single one of us a little geeky in our own special way, even the Pythian HR Team!

Some of us on HR are Super Geeks! Tin and Christina have been planning this for months.

When we celebrated our first Geek Pride Day, our HR team was a modest size of three people.

The HR team in 2011, from left to right, Heidi Hauver, Julia Duffy, and me.

Since 2011, our team has grown substantially!

Pythian’s HR team today! From left to right, Peter Dean, Camila Suvaric, Tin Thinn, Christina Anderson, Rosie Leonard, Heidi Hauver and Heather Hillier.

People are sometimes surprised to learn that HR is filled with geeks, so I asked the whole team about what makes them a super geek.

Rosie Leonard – “The geekiest thing I can’t live without is my label maker.”

Heidi Hauver – “The geekiest thing about me is that I truly love science fiction. My favorite tv show is V and my favorite movie is the (original) Star Wars Trilogy.”

Heather Hillier – “The one geeky thing that I cannot live without is my iPad.”

Peter Dean – “My geeky habit is the way I speak.”

Camilla Suvaric – “Being able to quote Lord of the Rings (1, 2 and 3) is definitely what makes me a geek”

Tin Thinn- “Some of the things that make me a geek is knowing some pretty good details about comics, Star Wars, Star Trek, board games (i.e. Magic, D&D, Heroscape, etc.), my Spider Man golf head cover and my thoughts on why geeks are way cooler than jocks.”

Christina Anderson – “The geekiest thing I can’t live without is definitely my TARDIS and two Sonic Screwdrivers.”

Christina’s homemade TARDIS, one of her geekiest items.

My geeky confession? I am a huge RPG video game addict. The first RPG I ever played was Final Fantasy VII on the original PlayStation and I was immediately hooked from the age of 11. Since then, I have lost too many hours of sleep to get to the next save point, or to finish the next mission. I had to move my consoles out to my bedroom to make sure I got a good night sleep. I am also slightly embarrassed to say that I was the only person waiting for Future Shop to open when Final Fantasy XIII was released (and was quickly disappointed).

My Geeky t-shirt for Geek Pride Day.

Since you know about the geekiness of Pythian’s HR team, what makes you a geek?

During a recent customer environment cloning activity I got myself up to the point where CUSTOM.plx was required to be recompiled. Nothing difficult you may say, right? I thought the same. But that activity just killed lots of troubleshooting hours for me.

frmcmp_batch.sh call was just failing with “Terminal map initialization failed.”

[oracle@appslab01 ~]$ frmcmp_batch.sh module=CUSTOM.pll userid=apps/apps output_file=CUSTOM.plx module_type=LIBRARY compile_all=YES
Terminal map initialization failed.
API: could not initialize character-mode driver.
FRM-91500: Unable to start/complete the build.
[oracle@appslab01 ~]$

After some short troubleshooting I thought that just setting the DISPLAY variable and running a manual compilation should be ok. And it worked actually.

[oracle@appslab01 ~]$ export DISPLAY=:1
[oracle@appslab01 ~]$ frmcmp.sh module=CUSTOM.pll userid=apps/apps output_file=CUSTOM.plx module_type=LIBRARY compile_all=YES
Forms 10.1 (Form Compiler) Version 10.1.2.3.0 (Production)

Forms 10.1 (Form Compiler): Release – Production

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
PL/SQL Version 10.1.0.5.0 (Production)
Oracle Procedure Builder V10.1.2.3.0 – Production
Oracle Virtual Graphics System Version 10.1.2.0.0 (Production)
Oracle Multimedia Version 10.1.2.0.2 (Production)
Oracle Tools Integration Version 10.1.2.0.2 (Production)
Oracle Tools Common Area Version 10.1.2.0.2
Oracle CORE 10.1.0.5.0 Production
Compiling library CUSTOM…
…
…
…
…
…
Done.
[oracle@appslab01 ~]$

My victory didn’t last too long. During one of the later steps, I was recompiling Form objects for several products using the ADADMIN, and all of these jobs were failing too. When I started to look into worker logs, I found that frmcmp_batch.sh is being executed, of course, and the logs were full of “Terminal map initialization failed” messages.
Lots of hours passed troubleshooting this. I didn’t find any clue or known issue in MyOracleSupport and Google/Bing searches also didn’t find anything that could guide me to a solution. So I start “digging” myself.

Referring to Oracle Support Note [ID 1085526.1] for a generic FRM-91500 troubleshooting gave me good hints on possible issues with the fmrcvt220.res terminal mapping resource file and interaction with TERM/ORACLE_TERM environment variables. Getting no results here, I got an idea to try another terminal connection using the Mac default Terminal.app (I was using SecureCRT prior to that).
And it worked!!! I saw no issues with frmcmp_batch.sh, and initiated ADADMIN Forms object compilation, which also proceeded successfully.

Having a small Terminal.app window on the screen opened by default and 1920×1200 resolution on the screen visibility wasn’t too good, so I maximized the window by clicking on the plus icon.
As soon as my window was maximized, all running ADADMIN jobs started to fail. And what do you think I found in worker logs? Exactly! The same “Terminal map initialization failed” error.

So the reason for all these failures was just my “too large” terminal window size. I remembered “Terminal too wide” VIM text editor issues due to the same reason.

This can be easily reproduced. I resized my terminal to half-size and ran ADADMIN and initiated Forms compilation for all products. While workers processed the compilation jobs, I started to resize the window using the lower-right corner.
It was possible to clearly see how all workers started to fail, and again started to successfully compile when I was resizing the terminal window back to half-size.

I have just reproduced it on my lab instance while I was writing this blog post. And it’s not only happening on more exclusive platforms like HP-UX or AIX. It’s also a generic Linux issue, which is most commonly used for E-Business Suite.

– Maximized Terminal window

[oracle@appslab01 ~]$ frmcmp_batch.sh module=CUSTOM.pll userid=apps/apps output_file=CUSTOM.plx module_type=LIBRARY compile_all=YES
Terminal map initialization failed.
API: could not initialize character-mode driver.
FRM-91500: Unable to start/complete the build.
[oracle@appslab01 ~]$

– Resized it a bit and running same command.

[oracle@appslab01 ~]$ frmcmp_batch.sh module=CUSTOM.pll userid= apps/apps output_file=CUSTOM.plx module_type=LIBRARY compile_all=YES
Forms 10.1 (Form Compiler) Version 10.1.2.3.0 (Production)

Forms 10.1 (Form Compiler): Release – Production

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
PL/SQL Version 10.1.0.5.0 (Production)
Oracle Procedure Builder V10.1.2.3.0 – Production
Oracle Virtual Graphics System Version 10.1.2.0.0 (Production)
Oracle Multimedia Version 10.1.2.0.2 (Production)
Oracle Tools Integration Version 10.1.2.0.2 (Production)
Oracle Tools Common Area Version 10.1.2.0.2
Oracle CORE 10.1.0.5.0 Production
Compiling library CUSTOM…
…
…
…
Done.
[oracle@appslab01 ~]$

This blog post, definitely, is not about a common issue many of my colleagues all over the world might face, but it’s a good starting point and, I hope, it will save lots of troubleshooting time or Severity 1 SR’s for someone as soon as the search engines process this post.
High-resolution displays are slowly getting a more wide usage and good old 1024×768 isn’t always appropriate anymore. Who knows what else besides “Terminal too wide” and this one might await us.

Can’t find what you want or need? Do you have a tip or trick to share? Do you want to lament over a technical woe? If yes then blog and send it to us for the Log Buffer :), just like this Log Buffer contains tips, tricks and woes.
Oracle:

Fahd Mirza and Tanel Poder throw spotlight on the v$cell_thread_history view with respect to the Exadata.

Mark W. Farnham‘s rightsizing goes on with a roar, as he declares that he is pretty much a green sneaker, tree hugging conservationist.

I would like to use the Microsoft Query tool in Microsoft Excel to extract records from the ERP database. Charles Hooper answers.

Kevin continues with yet another post about Exadata. The time he discusses: Oracle Exadata Database Machine: Proving 160 Xeon E7 Cores Are As “Slow” As 128 Xeon E5 Cores?

Alberto does a post which is a brief discussion about the advantages of activating parallelism by altering the session environment instead of using the alternative ways (hints, DDL).

SQL Server:

Security Questions: Differences between “Grant” and “With Grant?”

Using Scoped Assignments To Show Calendar and Financial Year-To-Date Values In The Same Calculated Measure.

A cool post about Business Objects on Linux and SQL Server.

Is Your Code an English Garden or Ikebana?

The Journey is the Reward: Speaking for SQLPass, where do you start?

Resolving Error Connecting Report Builder to a PowerPivot Data Source.

MySQL:

Stewart Smith is sharing an old note on the Storage Engine API.

A good look at the Non-Deterministic Query in Replication Stream.

The MariaDB project is pleased to announce the immediate availability of the MariaDB 5.5.31.

Fernando had recently had to do some customer work involving the McAfee MySQL Audit Plugin.

There’s a big difference in how plugins are treated in MySQL and how they are treated in Drizzle.

Recently I have noticed that re-imaging process on the second Oracle Database Appliance node took significantly shorter time comparing with the first node. The difference was so significant that I started to suspect that there were something wrong with either particular set of hardware or some of the re-imaging process steps have failed on the second node. On the first node the process has completed  in 120 minutes, but  on the second it took just 20 around minutes.

I spent quite a bit of time to understand that exactly has been happening. But before I tell you, can I ask you what theoretical options would you come with given the behavior I just described? Please share those with me in the comment section below, please :)

Question is if we are ready to pay for it. Sometimes it takes quite a bit of effort to get to the truth and very often we don’t have time or interest or budget to find it. In this particular case I was so curious that I have spent good part of my my weekend looking for a clue. On the way I had  to learn a bit of “Anaconda (installer)“, SquashFS file system, how to rebuild ISO image and the way ODA re-imaging process works. The purpose of this paragraph is to encourage you to be curious and don’t leave mysteries unresolved. Invest  some time and you will learn a lot on the way :)

NOTE: I will try to share the way I have troubleshot this problem in my future blog posts.

It appears that the problem is in the way the ISO:/Extras/setupodaovm.sh post install script checks if software RAID have completed re-synchronization of 4 internal HDD partitions (md devices) in between 2 physical disks. There are the following check at the very end of the script:
mdadm --wait /dev/md1
mdadm --wait /dev/md2
mdadm --wait /dev/md3
Each of the lines designed to check if the software RAID completed synchronizing an md device (partition). The following is part of man page for mdadm utility

       -W, --wait
              For  each  md  device  given, wait for any resync, recovery, or reshape activity to finish before returning.  mdadm will return with success if it actually waited for
              every device listed, otherwise it will return failure.

During the re-imaging process all 4 volumes got to be rebuild and need to be synchronized by the software RAID. It worth mentioning that software RAID on ODA configured to re-synchronise  one device at the time. Other devices just seating and waiting they turn in the status DELAYED.  The problem is that if a device is in the state resync=DELAYED the “mdadm –wait” check will not stop and wait for it. Therefore just one of the mdadm checks will wait until re-synchronization process finishes others successfully pass even if a device isn’t synchronized yet (resync=DELAYED). Now let’s have a look on the devices’ sizes and associated synchronization times:

Just to make life a bit more interesting the software RAID picks up a device to be re-synchronized next randomly. That means it is just matter of luck what device will get processed next. If it is md1 device (17GB) then the whole re-imaging process takes 20 minutes. However if the software RAID synchronises md2 device (217GB) during the execution of the mdadm check then the re-imaging process takes ~120 minutes.

I am not a great expert in the Linux System Administration area (I am an Oracle DBA after all) and would rather let Oracle folks make the final call, but it seems to me that in order to make sure that all 4 devices got re-synchronized before the re-imaging process finishes the check should look like the following.

mdadm --wait /dev/md0 /dev/md1 /dev/md2 /dev/md3

To conclude until the issue is fixed know that

1. you may face different ODA nodes’ re-imaging times
2. to be on the safe side check if md devices’ re-synchronization process  is finished by running “cat /proc/mdstat” command before running any business critical processes on your ODA.

Yury

PS “Stay Hungry Stay Foolish” - Steve Jobs

The red carpet has been laid down at this Log Buffer Edition, and you can witness and cheer the cat-walking blog posts from Oracle, SQL Server and MySQL. Every one of them is chic, elegant, sensual in its own right. Enjoy.
Oracle:

Create colored heat maps in SQL*Plus with Kyle Hailey.

Here’s a quick and dirty script to create a procedure (in the SYS schema – so be careful) to check the Hakan Factor for an object.

Connor has a good post about default null for collection parameter.

This is yet another blogpost on Oracle’s direct path read feature which was introduced for non-parallel query processes in Oracle version 11.

Owen Allen has seen some questions about provisioning Oracle Solaris 11. They boil down to this.

SQL Server:

Shashank Srivastava tells us as how to Change the SQL Server Instance Name after Renaming the Windows Host.

Daniel Calbimonte shares as how to synchronize two SSAS Servers.

Data Architecture underpins just about everything we do in IT.  Without a clear understanding of how data is structured, there is no reliable way to derive meaning from it.

Orlando Colamatteo is login-less in Seattle.

Lets get started testing database with tSQLt with Robert Sheldon.

MySQL:

After a lot of fuzz, Anders Karlsson is now releasing MyQuery version 3.5.1.

Nothing like reestablishing a tradition and Dave Stokes is doing just that for MySQL.

Mare Alff is spreading the word about the performance schema.

Slava Akhmechet talks about secondary indexes, batched inserts performance improvements, soft durability mode.

It is a central part of the MySQL philosophy to try and help you as much as you can. There are many occasions when it could tell you that what you are asking for is utterly stupid or give you a bad execution plan because “you asked for it”.

Sometime back I was involved in a project which was to collect information for the servers we are supporting – creating inventory of servers. Logging into each server and collecting information is bit tedious when you have hundreds of server in your environment.  I have spent sometime and created a script that does this work for me, however, I still need to connect to the server – but using SSMS/Query Window and execute the script. And then the script will provide me the details I needed.

This inventory should have details like below for cluster and stand alone instances:

Server Name

OS Name

OS Edition

OS Patch Level

SQL Server IP

Is Clustered

Node1_Name

Node1_IP

Node2_Name

Node2_IP

SQL Server Edition

SQL Server Patch Level

Server Time Zone

SQL Server Version

SQL Server Platform

Processor Core

Physical Memory

Service Account Name

Domain

Looks good ?  Below is the version 1 of this script.

/*
IP address portion using : http://www.sqlservercentral.com/Forums/Topic150196-8-1.aspx
REMOVE sp_configure parameters if you are executing this script on SQL Server 2000

Created By : Hemantgiri S. Goswami | http://www.sql-server-citation.com
Date : 24th March 2013
Version : 1.0

Tested ON:
Windows Server: 2003, 2008, 2008 R2
SQL Server:2000, 2005, 2008, 2008 R2, 2012

*/
sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'xp_cmdshell',1
RECONFIGURE WITH OVERRIDE
GO
DECLARE @TimeZone NVARCHAR(100)
 ,@ProductVersion SYSNAME
 ,@PlatForm SYSNAME
 ,@Windows_Version SYSNAME
 ,@Processors SYSNAME
 ,@PhysicalMemory SYSNAME
 ,@ServiceAccount SYSNAME
 ,@IPAddress SYSNAME
 ,@DOMAIN SYSNAME
 ,@MachineType SYSNAME
 ,@SQLServerIP VARCHAR(255)
 ,@CMD VARCHAR(100)
 ,@Node1 VARCHAR(100)
 ,@Node2 VARCHAR(100)
 ,@Node1IP VARCHAR(100)
 ,@Node2IP VARCHAR(100)
 ,@OSEdition VARCHAR(100)
 ,@OSVersion VARCHAR(100)
 ,@OSName VARCHAR(100)
 ,@OSPatchLevel VARCHAR(100)

CREATE TABLE #TempTable
 (
 [Index] VARCHAR(2000),
 [Name] VARCHAR(2000),
 [Internal_Value] VARCHAR(2000),
 [Character_Value] VARCHAR(2000)
 ) ;

INSERT INTO #TempTable
EXEC xp_msver;

-- Replace @Value_Name to N'TimeZoneKeyName' when running on Windows 2008
EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
 @value_name = N'StandardName',
 @value = @TimeZone output

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
 @value_name = N'ObjectName',
 @value = @ServiceAccount output

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SYSTEM\CurrentControlSet\Control\ProductOptions',
 @value_name = N'ProductType',
 @value = @MachineType output

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters',
 @value_name = N'Domain',
 @value = @DOMAIN output

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'CLUSTER\NODES\1',
 @value_name = N'NodeName',
 @value = @Node1 output

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'CLUSTER\NODES\2',
 @value_name = N'NodeName',
 @value = @Node2 output

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
 @value_name = N'ProductName',
 @value = @OSName output

create table #OSEdition (VALUe varchar(255),OSEdition varchar(255), data varchar(100))
insert into #OSEdition
EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SYSTEM\CurrentControlSet\Control\ProductOptions',
 @value_name = N'ProductSuite'
SET @OSEdition = (SELECT TOP 1 OSedition FROM #OsEdition)

EXEC master.dbo.xp_regread
 @rootkey = N'HKEY_LOCAL_MACHINE',
 @key = N'SOFTWARE\Microsoft\Windows NT\CurrentVersion',
 @value_name = N'CSDVersion',
 @value = @OSPatchLevel output

set @cmd = 'ping ' + @Node1
create table #Node1IP (grabfield varchar(255))
insert into #Node1IP exec master.dbo.xp_cmdshell @cmd
set @cmd = 'ping ' + @Node2
create table #Node2IP (grabfield varchar(255))
insert into #Node2IP exec master.dbo.xp_cmdshell @cmd

set @cmd = 'ping ' + @@servername
create table #SQLServerIP (grabfield varchar(255))
insert into #SQLServerIP exec master.dbo.xp_cmdshell @cmd

SET @SQLServerIP=(
 SELECT substring(grabfield, charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1)
 from #SQLServerIP where left(grabfield,7) = 'Pinging'
 )
SET @Node1IP =(
 SELECT substring(grabfield, charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1)
 from #Node1IP where left(grabfield,7) = 'Pinging'
 )

SET @Node2IP =(
 SELECT substring(grabfield, charindex('[',grabfield)+1, charindex(']',grabfield)-charindex('[',grabfield)-1)
 from #Node2IP where left(grabfield,7) = 'Pinging'
 )

SET @ProductVersion = (SELECT Character_Value from #TempTable where [INDEX]=2)
SET @Platform = (SELECT Character_Value from #TempTable where [INDEX]=4)
SET @Windows_Version= (SELECT Character_Value from #TempTable where [INDEX]=15)
SET @Processors = (SELECT Character_Value from #TempTable where [INDEX]=16)
SET @PhysicalMemory = (SELECT Character_Value from #TempTable where [INDEX]=19)
SELECT
 ServerName = @@SERVERNAME
 ,OSName = @OSName
 ,OSEdition = @OSEdition
 ,OSPatchLevel = @OSPatchLevel
 ,SQLServerIP = @SQLServerIP
 ,IsClustered = SERVERPROPERTY('IsClustered')
 ,Node1_Name = @Node1
 ,Node1_IP = @Node1IP
 ,Node2_Name = @Node2
 ,Node2_IP = @Node2IP
 ,SQLServerEdition = SERVERPROPERTY('Edition')
 ,SQLServerLevel = SERVERPROPERTY('ProductLevel')
 ,ServerTimeZone = @TimeZone
 ,SQLServerVersion = @ProductVersion
 ,SQLServerPlatform = @PlatForm
 ,ProcessorCore = @Processors
 ,PhysicalMemory = @PhysicalMemory
 ,ServiceAccountName = @ServiceAccount
 ,WKS_Server = @MachineType
 ,Domain = @DOMAIN

GO
DROP TABLE #Node1IP
DROP TABLE #NODE2IP
DROP TABLE #SQLServerIP
DROP TABLE #TempTable
DROP TABLE #OSEdition
GO
sp_configure 'xp_cmdshell',0
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE
GO

Please do post back your feed back for this script, I will try my best to update and post back new version for this script.

- Hemantgiri S. Goswami (Cross posting from http://www.sql-server-citation.com/)

photo credit: Skimaniac via photopin cc

When I started my work in IT, I used to be in a very small shop, and even though we had people in several places in the same state, everything used to be very centralized and from 9 to 5, and because we were basically only 2 people , our action plan used to be a talk over the lunch table and that would be it, we would go ahead and execute it after 5 PM, and I won’t lie sometimes before 5 :) .

Over the years I have understood that even if you are a 2 guy shop or a team of 15 separated by oceans and being miles apart, communication is the most important thing to have on your team, and one of the means of communication is having an action plan in place for any major/medium change you do in your organization. First this will generate discussions amongst your teammates and it will reduce the possibility of errors when you are faced with time and pressure constraints when implementing it.

This might sometimes feel like a mundane and boring task, as it will take an effort to come up with it and it will take time to verify it, but when game day comes along you will see the great benefit of having an action plan.

Another great benefit of having an action plan is that you also have a road map if you need to rollback your change, and that is also critical, because normally any major change or rollback is not done only by one person, take for example a change that takes about 7 or 8 hours to be done, and at the end when the UAT (User Application Testing) is done, 1 or 2 more hours ,the application team decides that a rollback is needed, you are probably not in a good state of mind to do the rollback after 8 hours of continuous work, if you have an action plan, one of your teammates can step in and you can have a rest, even if it is to go to the kitchen and have a sandwich and a coke and forget 10 minutes about that pressure.

As with life and with us being human, having an action plan doesn’t mean that everything will go smoothly or you won’t have an error in there, but believe me, it will reduce in a big way the possibility of an error if you execute it by memory or by doing one yourself without revision.

I do hope that you already have an action plan as part of your major/medium changes, but if you don’t, it is time to get FIT-ACER, here is an example of one (Kudos to Cesar Sanchez as it is his Action Plan Template), use it and modify it to your needs, it is a good start.

Action Plan Template

This week again, the Log Buffer Edition covers the exciting and ever-evolving galaxies of Oracle, MySQL and SQL Server.

Oracle:

When does an Oracle process know it’s on Exadata?

MobaXterm 6.3 has been released. You can find the download and changelog in the usual place.

A new major release (version 3.0) of Randolf’s XPLAN_ASH tool is available for download.

Important !! Clustering Factor Calculation Improvement

Grouping Data Sets by Week Number of the Month

SQL Server:

Jason Crider asks to put some Windows update in his basket.

Data cleansing can be done via SSIS as well as Data Quality Services (DQS) and Master Data Services (MDS).  The lines are a bit blurred when talking about data cleansing using SSIS, DQS and MDS.

How To: Migrate Encrypted Procedures To Windows Azure SQL Database (WASD).

It’s a best practice to keep your index keys as narrow as possible, and SQL Server enforces a maximum key length of 900 bytes on most “normal” clustered and non-clustered indexes.

Webcast Presentation Materials: Introduction to SQL Server Security

MySQL:

This is the start of the heavy travel season for the MySQL Community Team. So if you are attending PHPTek, Drupalcon, Texas LinuxFest, SELF, Redhat Summit, or Lonestar PHP please make sure to say ‘hi’ while you are in Chicago, Portland, Austin, Charlotte, Boston, or Addison.

Your databases in a glance with Glimpsee

Continuent Tungsten 2.0 is a major step forward that includes key feature improvements to help you manage very large datasets on MySQL.

How to create a new (or repair a broken) GTID based slave with Percona XtraBackup

Disk I/O is frequently the performance bottleneck with relational databases. With AWS recently releasing 4,000 PIOPs EBS volumes, Laine Campbell wanted to do some benchmarking with pgbench and PostgreSQL 9.2.

Mothers day is coming up fast. May 12th 2013 is just around the corner and If you have not already done so, make sure you go out and get mom a special gift as soon as possible. If you need a hand picking something great, take a look at this mothers day gift guide by the HuffingtonPost

Many this year like they have in previous years shopped online and bought mom that special gift from an online retailer, a simple, easy and fun process. But is it always? Unfortunately for many, the experience did not live up to expectation due to site performance and both customers and merchants end up losing.

Is this a big deal? We put together this infograph on mothers day shopping. Take a look.

News and views are an integral part of our modern daily life. When it comes to the information-hungry roles such as database professionals, such need becomes more pressing. Log Buffer is one way to keep abreast of news and views from the world of Oracle, SQL Server, and MySQL.

Oracle:

There is a significant update SLOB 2, Kevin Closson informs.

Charles Hooper is grouping Data Sets by Week Number of the Month.

Chris Antognini has shared a script that is used to demo ITL deadlocks.

As Exadata Storage Indexes (SI) are purely memory only structures located on the Exadata storage servers, care needs to be taken in how much memory they can potentially consume.

Connon McDonald says that its not about the outage, but….

SQL Server:

Richard Douglas is giving information about SQL Server events in UK.

Thomas LaRock has been involved in a virtualization projects for almost ten years now.

Kendra Little has five things about Fillfactor.

Kevin Kline suggests to use TPC database benchmarks to save money.

Here’s a quick mystery. You have a SQL Server with several jobs that appear to be running properly and on schedule, but many of them show no history.

MySQL:

How does InnoDB behave without a Primary Key? Jeremy Cole asks.

The MariaDB project is pleased to announce the immediate availability of the MariaDB Java Client 1.1.2.

Normal 0 false false false EN-US X-NONE X-NONE Everybody loves free. It is the best marketing term one could use.

MySQL Cluster Manager 1.2.3 is now available to download from My Oracle Support.

When is a Subquery Executed?  Øystein Grøvlen writes.

“What have I gotten myself into” ran through my head during the first week of training at Pythian. The environment was great, the people were great, the work was great, but many doubts ran through my head as I ran from training meeting to training meeting – how am I going to retain all this information (my memory skills are bad as it is), am I going to meet their expectations, am I too junior for this, I can’t seem to catch up on anything, is this the right career choice.
6 months later and I’m still here! I feel more comfortable with the work, I know I am meeting expectations, I have confidence in my skills, my organizational skills have improved, I know I’ve made the right career choice, even my memory has gotten much better! What changed over the past 6 months you ask? Well, here is how it started.

I was happy and content at my previous employer. I knew what I was doing, I had the skills and knowledge, and I was regarded as one of their senior employees. Although it was mainly recruiting work I was performing, it was under a Sales umbrella. I had always wanted to follow the path of an HR professional, may it be recruiting, organizational design, compensation, employee relations, training – all of which fell under the umbrella of HR. I arrived at Pythian because I was contacted by a previous colleague with an opportunity that I could not resist. Finally, I will get a chance to get my hands dirty and work on my true passion – Human Resourcing (HR). Contrary to some beliefs, HR professionals are not “paper pushers”, administrators or strictly recruiters but in fact we are pieces of the strategic puzzle in any organization. The harsh reality though is that much of the world still believes that we are administrators. So, for me I really wanted to join an organization that leverages HR as a strategic partner for the organization.

What Pythian offered me was the “3 Pillars of Growth” – Sales, Service Delivery and HR. These 3 ‘pillars’ are crucial in helping grow a business. This was further solidified for me by the people I met, the new/ existing plans and programs within HR, and the focus on culture Pythian provided for the growth of the business. This was a great opportunity for me to grow as a person and my career, and be part of a strategic team.

So I arrive at Pythian and was excited to learn the things I have always wanted to learn while providing the expertise I already possessed. In fact, I was delegated as the lead on a major, high profile project within the first week. However, shortly after doubt crept in as I found myself feeling like I could barely tread water (ask my wife, I’m not a very strong swimmer). Here I was absorbing so much information about the company, policies, procedures and now I have to figure out how to role out a project while I was still learning about the internal processes (and performing my main responsibility of recruiting). I rapidly felt like I was taking on too much water and started drowning. I started to loose confidence in my abilities and doubted the decision to take on this roll. I had several discussions with my work colleagues and my peers about my concerns. However, the one discussion that really changed my mind set was a conversation with my wife, she told me – “You worked too hard for this, don’t let yourself be your worst enemy.” There it was, clear as day! It was all my own insecurities, my own self-doubts, myself talking me out of this equation. So, I worked hard to observe and utilize the tools I had been provided throughout my career and at Pythian.

Once I was more open, I quickly noticed that Pythian really believes in their employees. They really empower their employees to do well and have a company culture to nurture their growth. They are always to open to new ideas and are always willing to take on risks with you. In the event that your new idea does not work, they still support you and move on to other ideas without pointing fingers or seeing you as a failure. Pythian’s unwritten culture is to make sure their employees don’t fail. Being in HR here, I was able to observe these facts are not just in our department but it is well exemplified in all business units across Pythian, starting from the CEO and Founder, to the Senior Executive team, to the Leadership team, and so on. I also had the opportunity to address my concerns to my own colleagues and manager and they were nothing but supportive. They provided immediate feedback and were able to provide relatable experiences with me.

Today, I feel like I am a much better swimmer. With the support I was provided by my colleagues, my managers, and the company my confidence in my abilities have grown and I know I have and will continue to add value to the organization. I feel I have met the expectations of the people who hired me, and I know I have made the right decision in joining this team.

This was my own experience of the first 6 months at Pythian. I hope that others have had similar or will have similar experiences as I have had (although I hope they are better swimmers). It has been tough but I’ve enjoyed every second of it. Life is a journey after all!

Reminders are an inevitable part of our personal and professional lives. Keep reminding of oneself about goals, priorities, and milestones is key to stay on top of things. This Log Buffer Edition is a reminder about all those not-to-be-missed blogs out there.

Oracle:

One of the developers was performing a delete using a nested subquery, which was throwing out ORA-00600 errors. The ORA-00600 Lookup Tool on MOS suggested it was caused by a bug, Tim Hall reports.

The healthcare sector has a clear opportunity to modernize care delivery by adopting new data interchange standards and EHR systems. Kyle opines.

This might be something very obvious for the reader but Martin had an interesting revelation recently when implementing parallel_degree_limit_p1 in a resource consumer group.

Alberto has a good post on fast refresh of outer-join-only materialized views.

Charles Hooper is discussing Unexpected Timer Resolution, Unexpected Parked CPUs, Unexpected Power Consumption.

SQL Server:

There are five SQL Server white papers which every DBA should read.

James gets into an interesting discussion about consultants.

Kendra Little wonders as why most people don’t follow best practices.

Kendal Van Dyke writes a quick note to let you know that SQL Power Doc version 1.0.1.0 is now live.

Let’s talk about database application benchmarking with Kevin Kline.

MySQL:

Stewart Smith is sharing a few notes on InnoDB in MySQL 5.7.1.

Alexey Kopytov has blogged more on transaction descriptors optimization.

MySQL Cluster gets a Community Award, Tomas tells us.

Oracle’s VP of MySQL Engineering Tomas Ulin delivered on Tuesday a keynote entitled “Driving MySQL Innovation for Next Generation Applications” at the Percona Live Conference.

MariaDB Introduces Atomic Writes, Arel Schwenke reveals.

This week my DBA team satisfied a request to restore 1 month old lost data.  A review of the RMAN backups showed that we indeed had a valid level 0 from the requested day which was 33 days ago.  However, upon recovery, the necessary archive logs to bring the database to a consistent point, were not available.  With a 28 day retention policy, running level 0 backups every 15 days, and level 1s each night, we did have the necessary level 0 backup, but the archive logs had been deleted.  How to retrieve a copy of a user schema from a database that you cannot open?  With the hidden “_allow_resetlogs_corruption” parameter.

To make matters more difficult, we were also doing just a partial database restore – we only required 1 schema, located in its own tablespace but this tablespace was encrypted.

The partial database restore was simple enough.  The team began the restore and recovery on another server.  The server where recovery is to be performed should have the same path/location of files as the old server, if not then update the path of the datafiles using set newname command in rman recovery, update the logfiles using alter database rename commands and update the tempfile by re-creating it.

RMAN> run
{
ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
ALLOCATE CHANNEL C2 DEVICE TYPE DISK;
ALLOCATE CHANNEL C3 DEVICE TYPE DISK;
set until time "to_date('16-03-2013 07:00:00','dd-mm-yyyy hh24:mi:ss')";
restore database skip forever tablespace 'USERS1','USERS2',' USERS3',' USERS4';
recover database skip forever tablespace 'USERS1','USERS2',' USERS3',' USERS4';
RELEASE  CHANNEL C1;
RELEASE  CHANNEL C3;
RELEASE  CHANNEL C3;
}

RMAN completed the restore of the SYSTEM, SYSAUX, UNDO and our USERS tablespace without issue, but sadly during the recovery we received the Oracle Error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
RMAN-03002: failure of recover command at 04/19/2013 17:24:34
RMAN-06053: unable to perform media recovery because of missing log

You know at this point that your data is right there – so close to grab and provide for your customer and become the DBA hero you long to be – but Oracle won’t open the door and let you in.  What to do?  All we need is to get the data and get out.  We are not going to allow users into this half-way house we call an Oracle database.  So, we allow for the corruption with the hidden parameter “_allow_resetlogs_corruption”.  This should not be used lightly – your database will be a mess – but it will allow you to open it, and hopefully get the data you need.

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  530288640 bytes
Fixed Size                  2131120 bytes
Variable Size             310381392 bytes
Database Buffers          209715200 bytes
Redo Buffers                8060928 bytes
Database mounted.

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  530288640 bytes
Fixed Size                  2131120 bytes
Variable Size             310381392 bytes
Database Buffers          209715200 bytes
Redo Buffers                8060928 bytes
Database mounted.

SQL> alter database open resetlogs;

Database altered.

Halleluiah! As a DBA, these are words you long to see on your screen “Database opened”.  At this point, I am certain my team has super human DBA talent.  The data is there for the taking!  But wait, still not able to grab it.  The database continues to crash with ORA-600 errors.  The team realizes that the errors are related to Oracle jobs that are failing, we set job_queue_processes=0; and restart the instance.  The database seems to be stable now, but the expdp needs a job process in order to run.  If we modify the job_queue_processes parameter, then the instance will crash.  We go back to our roots and try the old export utility “exp”.  This works great, except if you remember our tablespaces are encrypted.  Exp cannot export the data from them.  Painstakingly, the team moves each object into a new non-encrypted tablespace.  Many ORA-00600 errors are hit along the way.  The road is not smooth, but, with perseverance all objects get moved.  We then create a new UNDO tablespace:

SQL> CREATE UNDO TABLESPACE undo1 datafile '<ora_data_path>\undo1_1.dbf' size 200m autoextend on maxsize unlimited;
Tablespace created.
SQL> ALTER SYSTEM SET undo_tablespace = undo1 SCOPE=spfile;
System altered.
SQL> alter system set undo_management=auto scope=spfile;
System altered.

SQL> shutdown immediate
SQL> startup

Finally the team is able to generate a successful export dump using exp:

exp file=<path_to_exportlocation>\tran.dmp indexes=n direct=y feedback=100000 statistics=none owner=('<schema_owner>')

The team members are now experts at both partial recoveries and getting the front door to an Oracle database open when you fail to have the right keys.  The lesson here?  Keep all your keys to the front door, but remember back doors do exist.

It is not uncommon for Oracle to have something undocumented , and this is just another case of this ailment.

We have a database in which we have two copies of the same backup, one we keep in the FRA, the other one in an external disk, which from time to time gets deleted from this second disk. But what happens after you do a crosscheck and one of those 2 backups is expired? The one in the FRA is available and the one in the external disk is expired for RMAN, but how will you see it in your listing?

Looking at the 11.2 documentation for the LIST command , it only tells you that the Status of a backup can be AVAILABLE, UNAVAILABLE, or EXPIRED, but as you can see below that is not the case with our database :)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1986    B  F  A DISK        17-APR-13       1       2       NO         *
1987    B  F  M DISK        17-APR-13       1       2       NO         *
1988    B  F  A DISK        17-APR-13       1       2       NO         *
1989    B  F  A DISK        19-APR-13       1       1       NO         TAG000661
1990    B  A  A DISK        19-APR-13       1       1       YES        BUP_ARCH_FRA

When I first faced this, it got me curious as to what the meaning of the M status meant (and I still don’t know the exact meaning, but I have my supposition) . If I did a trace of my list backup summary command , I could see exactly what I knew it was happening:

pythian@oracleenespanol.local /home/pythian/working/antunez
pythian $ rman target / debug trace rman.trc
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Apr 19 16:02:56 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN-06005: connected to target database: TESTDB (DBID=1834739652)

RMAN> list backup summary;

This is a small result of the trace done above :

DBGMISC: 339 BS (datafile) key=1987 recid=1987 stamp=812962808 setstamp=812962807 setcount=2021
DBGMISC: level=1 level_i=-1 piececount=1 keepopts=0, site_key=0 [16:03:27.544]
DBGMISC: site_key=0 [16:03:27.544]
DBGMISC: chid=NIL parm=NIL [16:03:27.544]
DBGMISC: flags= [16:03:27.544]
DBGMISC: valid backup set list is [16:03:27.544]
DBGMISC: 1 VBS copy#=1 tag=BACKUP_CONTROLFILE_FRA deviceType=DISK status=A
DBGMISC: 1 BPIECEX key=3502 recid=3502 stamp=812962808
DBGMISC: bskey=1987 set_stamp=812962807 set_count=2021 site_key=0
DBGMISC: pieceno=1 handle=+FRA/test/backupset/2013_04_17/ncnnf0_backup_controlfile_fra_0.297.812962809
DBGMISC: device=DISK krmkch { count=0 found=FALSE }
DBGMISC: 2 VBS copy#=2 tag=BACKUP_OF_BACKUPSET deviceType=DISK status=X
DBGMISC: 1 BPIECEX key=3538 recid=3538 stamp=813024283
DBGMISC: bskey=1987 set_stamp=812962807 set_count=2021 site_key=0
DBGMISC: pieceno=1 handle=/backupdisk/TEST_bck/db_bckset_backup_TEST2_04-18-2013_v5o79kvn_1_2.rman
DBGMISC: device=DISK krmkch { count=0 found=FALSE }
DBGMISC: restore target list is [16:03:27.545]
DBGMISC: 1 ACT type=full fromSCN=0 toSCN=307822519 fno=0
DBGMISC: CURCF

As you can see above, one copy is EXPIRED and the other one is AVAILABLE, also because each copy has a different tag name, when you list them, you have an asterisk “*”  for a tag of the backupset with Key 1987.

Both of these behaviours are perfectly normal, but you won’t find them anywhere in Oracle’s Documentation, so the next time you see this in your list command, you will know the exact meaning of this status. Have you seen this before in your RMAN listings ?

P.S. For me it means Multiple or Mixed.

Just as information has become an integral part of technology, blogging has become an inseparable part of technology too. Database technologies are no exception and blogging in that arena is booming. This Log Buffer Edition is booming with some bombastic blog posts.

Oracle:

Arup Nanda asks. Application Design is the only Reason for Deadlocks? Think Again.

It’s time that businesses took a good, hard look at the way they manage their cloned database environments, Kyle Opines.

Randolf touches the interesting topic of ASM AU Size And LMT AUTOALLOCATE.

Jonathan Lewis is having some entertainment.

James Morle produces a gem of an article on Optimal Oracle Configuration for Efficient Table Scanning.

SQL Server:

Peter Skoglund is running DBCC CHECKDB on a regular basis.

Usually named instance can uses dynamic port, if firewall setup on the server, you can use SQL Server Configuration Manager.

Jason is backing up Azure SQL database to the cloud.

Microsoft let new SQL Server version name slip? Richard Douglas asks.

David Dye was recently introduced to a number of SSIS packages containing execute SQL and data flow tasks, all of which were using dynamic SQL.

MySQL:

Stewart Smith says that the MERGE storage engine is not dead, just resting or forgotten.

Installing Apache2 With PHP5 And MySQL Support On OpenSUSE 12.3 (LAMP)

Rotating MySQL slow logs safely is important.

MariaDB Foundation Takes Next Steps To Community Governance

InnoDB: A journey to the core: At the MySQL Conference

Pythian has been working with clients on big data consulting projects and when it comes to MongoDB and Hadoop we are often asked 2 questions.

• What is the differences between Hadoop and MongoDB?

• Can Hadoop and MongoDB be used together?

Alex Gorbachev, Pythian’s CTO recorded this short video to share his insight and answer these two questions.

Sometimes, it’s humongous revolutions. Most of the time, it’s an itsy bitsy evolutionary steps. Today’s hack definitively sits in the second category, but I have the feeling it’s a little dab of abstraction that is going to provide a lot of itch relief.

You see, MetaCPAN does not only have a pretty face, but also has a smashing backend that can be used straight-up for fun and profit.

Accessing REST endpoints is not hard, but it’s a little bit of a low-level chore. In Perl space, there is already MetaCPAN::API to abstract

my $ua = LWP::UserAgent;
my $me = decode_json(
    $ua->get( 'https://api.metacpan.org/author/YANICK'
)->content;
[/perl>

<p>into</p>

1
my $mcpan = MetaCPAN::API;
my $me = $mcpan->author('YANICK');

In JavaScript-land? Well, there was jQuery, of course:

$.get('https://api.metacpan.org/author/YANICK').success( function(data) {
    alert( 'hi there ' + data.name );
});

But now there is also metacpan.js:

$.metacpan().author('YANICK').success( function(data) {
    alert( 'hi there ' + data.name );
});

The plugin is still very simple and only implements author(), module(), release() and file(). And each of those methods is nothing but a glorified wrapper around the underlying $.ajax() calls. But, then again, isn’t the road to heaven paved with glorified wrappers? (which could be more of an indication of the terrible littering habits of angels than anything else, mind you)

Enjoy (and/or fork, depending on how much the current code is already scratching your own itch)!

When I was in high school, my practical arts teacher asked us to create a miniature of a structure. This meant looking at the blueprints of an existing structure and using it to create a small replica. I thought that this would be a great opportunity to learn more about reading blueprints and understanding what they are and how they affect structures. So, I asked my mom if I could look at our single-story house’s blueprints as the basis for my project. I told her about the project and that I wanted to learn more about reading blueprints. The beauty of what I did was that I had the luxury of looking around the structure with blueprint in hand without asking for permission. After all, it was our house anyway. As I was going around the house, I noticed a small crack in the wall near the living room. If you’re a house owner, your first instinct would be to get it fixed. In my case, I had the blueprint to see why there was a crack in the wall. I found out that there wasn’t a foundation built near the wall when there should have been one. The tension caused by the characteristic behavior of concrete caused the crack (after taking a materials science course in university, I finally understood what this meant.) Unfortunately, this wasn’t an easy fix. In order to fix the crack, we would have to take the entire wall down, put in a foundation and rebuild the wall. This would prevent the occurrence of future cracks as well as allow us to put up a second level should we decide to do so in the future.

Databases are no different from structures in that they require careful thought on the planning and design – both logical and physical. Like structures, we need to design databases for capacity and growth. Imagine a young couple with no plans to have kids deciding to buy a single-room unit in a high-rise condominium. Should they change their decision to have kids after some time, their condo unit will no longer be fit for them. They will have to either stay in the single-room unit even after having kids or move out of the condo unit and get a larger property. In both cases, they still have to go through some inconveniences. With the massive amount of data that businesses need to collect nowadays, database growth and capacity increase should be expected. The growth will definitely affect performance as more and more data need to be accessed by applications. A simple concept in point is the selection of appropriate data types. This is considered in the design process even before a table is created. For most database developers, defining the appropriate data type for a column simply addresses data integrity. The data type defines the valid values that can be stored in a specific column. However it’s more than that. Careful selection of the appropriate data types affect overall performance as the database grows over time.

Disk Performance

Databases are stored on disk. Records are stored in data pages (in SQL Server) or data blocks (in Oracle). Since data types have corresponding storage requirements, proper selection can save not just disk space but also improve disk performance. Take for example the use of an integer data type. I used this data type because it has the same storage requirement for both Oracle and SQL Server. Imagine creating an application that will store the number of devices assigned to an individual. Since this is a number between 0 (meaning no device) and some positive value, it wouldn’t make sense to use bigint or int even though they are both appropriate data types for this purpose. First, we wouldn’t need a negative value for this example, only positive values. Negative values won’t make sense. Second, imagine the maximum number of devices any individual can have at any given point in time. I personally have 7 devices – 2 laptops, a tablet and 4 phones. I don’t use all of my phones so I consider 3 of them as inactive. I doubt that any given individual can have more than 120 devices. You can argue with me on some edge cases but that’s more of a deviation than the norm. Given this information, a tinyint data type would suffice because it can store up to a maximum value of 255. Imagine the space savings that you get with using tinyint (1 byte) versus int (4 bytes) or even bigint (8 bytes) data types. That’s 75% reduction in space if you initially used int or about 87.5% if you used bigint. Now you might be thinking, “we’re talking about bytes here, not gigabytes.” Well, you’re right. But we do expect to store more and more data over time. Imagine saving 300GB of disk space when you get to around 400 billion rows with using tinyint versus int. This translates to faster disk processing because its dealing with less storage size to process such as reading and writing data pages/blocks, running backups, and running consistency checks.

I/O Performance

Records don’t just stay on disk. They get modified depending on how the business process requires it. In SQL Server, before a record gets modified, the data page containing the record is retrieved from disk and loaded in memory. After the modification is done in memory, the changes are written in the transaction log for durability. A regular checkpoint commits those changes from the log to the data file. Imagine what this does to your data modification process. If you need to modify the same integer column, the amount of records you can fit in memory will depend on the size of the row. The size of those rows will depend on the size of each individual column that comprise the row. The smaller the data type used, the more rows we can fit in memory. The more records we can fit in memory, the faster the data modification process will be since we are not wasting space in the SQL Server buffer cache. The goal of every performance tuning exercise is to minimize I/O overhead. Reducing the storage requirement by selecting the smallest but appropriate data type, we’ve not only improve the I/O performance when doing data modification (and retrieval as well,) we’ve also improve I/O performance when writing to the transaction log file and back to the data file.

Index Performance

Indexes are a way to speed up access to database records. An index contains keys built using a column or combination of columns defined in the underlying table. Creating indexes mean consuming disk storage almost equivalent to the size of the columns defined in the index. The same reasoning applies to this: if you are using the smallest yet appropriate data type for your columns, the storage requirement for your indexes will also be smaller. This also affects whether or not the column will be used as a clustered index or a non-clustered index. A column defined as a clustered index will also be defined in the non-clustered index. Imagine the overhead of a wide clustered index on all of your clustered index as it appears on all of your non-clustered indexes. While indexes are a great way to tune queries and return records faster, they are still affected by the type of data that you select for your columns.

In summary

I can go on and on and talk about other side effects of a very simple concept such as data type on performance. Proper selection of the smallest yet appropriate data type does not happen after the database has been deployed and in operation for some time. It has to happen even before you create your first table. Because if you expect your business to grow, your database will grow as a side effect. And you want your database to be able to handle that growth. The last thing you want is to start seeing cracks on your wall that you have to tear the whole thing down just to build a better foundation.

By now the Denver Convention Center is probably cleaned up from IOUG Collaborate. The signs directing thousands of attendees to top-notch technical presentations have been removed, the twenty rental laptops which composed the classroom for Pythian’s RAC Attack class have been returned and the vendor exhibition floor has been completely cleared out. Flight delays notwithstanding (thanks to some midwest weather), attendees are generally home by now – even those coming from places as far away as Germany and Australia.

Now that the buzz is dying down, I’ve finally found a few minutes to post my personal highlights.

First off, my favorite part of Collaborate is the opportunity to meet so many old friends and make new acquaintances who are all using Oracle technology. It’s both fun and informative to hear about the ways others are using Oracle software.

One person deserves very special mention: this was my first time hanging out in person with my Australia-based colleague Yury Velikanov. You could hear Yury’s deep-chested, enthuiastic “Pythian” chants even if you were on the opposite side of the exhibition hall from our booth. His sense of fun and relentless demands for silly faces during photos were so unresistable that anybody within about thirty feet of him was affected. In the photo below, notice how the guy standing behind me has also made a silly face for the picture.

Yury’s infectious enthusiasm

I wasn’t able to attend many technical sessions simply because I was so busy with the RAC Attack classes but the two I did attend didn’t disappoint.

• Craig Shallahamer‘s session Practical Performance Forecasting for the Oracle DBA was excellent. He compared benchmarking, simulating and modelling for performance evaluation and then took a deep dive into best practices for creating and using models. He discussed a variety of potential input sources in depth and walked through practice exercises on a theoretical consolidation case. Finally we wrapped up with a brief discussion of the queueing theory. Excellent material!

  If I had to choose one take-away which I think was the single most important point, it would be this: R = S + Q. Every DBA should know what this is referring to on a basic level. If you don’t know what I’m referring to, then I’d recommend Craig’s class – or any of Cary Millsap‘s talks for that matter (they both cover this very well).

• Frits Hoogland‘s session How Oracle Secretly Changed Multiblock Reads (at most conferences this same session is simply titled About Multiblock Reads) is a must-see presentation for DBA’s with any interest in database internals. With the aid of operating system traces and debuggers, Frits has dissected the Oracle database kernel’s I/O process and compiled an incredible summary report. Showing surprising differences between recent releases of Oracle RDBMS, Frits dispels any myth that they aren’t making major changes to the database engine.

  However, despite unmatched technical depth and quality, my favorite part of this session wasn’t technical. At one point during the presentation I remember Frits saying that he ran a test and was completely baffled by the output. While trying to problem-solve himself, he also fired off a few emails looking for feedback from others. An email from a colleague pointed him to the “total number of slots” statistic, which led to an “ah-ha” moment and explained the data he was seeing. This was my favorite part because it made me feel much better about asking for help myself, since someone as smart as Frits asks for help sometimes too!

The bulk of my energy this week was dedicated to the RAC Attack class.

• Improvements

  First off, I am excited to announce a major development for RAC Attack: we have re-introduced printed textbooks. Two years ago we stopped printing textbooks – when the curriculum was moved to wikibooks in order to better support broad collaboration in creating the best beginner RAC curriculum available. Last week we completed and released the process for creating a printed textbook from the wikibooks content. The process is completely automated. You start up an EC2 instance and run a little code which lives at github; shortly after this you get a PDF which is ready to go to the printers. The 250-page textbook has over 200 screenshots. In the USA, I’ve had them printed in the past for US$30-40 each. This should go a long way toward supporting instructors who want to teach RAC Attack classes. Information about the textbooks is at http://racattack.org/book.

  In addition to this, there have been a number of improvements to the organizer’s guide over the past few months. Anyone interested in organizing RAC Attack events can find a good deal of helpful material on the Events wiki page. We’ve learned a lot through the many events we’ve run in the past!

• Monday Morning RAC Attack

  We had 15-20 people gather in the foyer area for the initial kick-off. Everyone listened to the description of the RAC Attack project with interest and some stayed to try starting out with their personal laptops, while many decided to attend one of the two later classroom sessions. We occupied most of the available tables and we were a very noticeable crowd as everyone passed by from the keynote to find technical sessions to attend!

• Monday Afternoon and Tuesday Morning RAC Attack

  We filled the classroom sessions. There were no major technical problems with lab machines as people worked through the labs. There were many good conversations; I remember in-depth discussions about listener architecture in 11.2 RAC (with SCAN) and about init parameters related to client connections and about services and about applications which require all connections to be made in a single instance.

• Feedback

  There was significant positive feedback about RAC Attack. One guy stopped me as I was entering the exhibition hall Monday night just to tell me that that he had learned more in the RAC Attack class than any other session thus far at Collaborate – he was visibly pleased with the experience!

Collaborate was a memorable conference this year. I’m very thankful for the hard work by the IOUG leadership to put this event together! If you attended Collaborate then make sure to pass along your thanks and any other feedback to IOUG! And whether or not you attended – support your local user group; they are a crucial resource and tremendous asset to all of us.

For those of you who attended Collaborate, what were your favorite technical sessions or keynotes? What other parts of the conference did you find valuable? Leave a comment here to tell us what you thought about Collaborate!

Like in love there is no precondition, there are no limits held when it comes to the labor of love called as blogging. Lovingly this Log Buffer edition brings you some more roses from database arena.

Oracle:

Marco Vigelini shares as how to duplicate a production database on a different server mantaining the same SID and directory structures with ‘duplicate’ RMAN command and how to resynchronize a duplicated remote database using RMAN backups.

Recent thread in the OakTable mailing list prompted Timur Akhmadeev to create a poll and ask about the ways DBAs use system statistics in real systems.

Marc Fielding was recently playing with an Oracle XE database, and when Marc plays, it stayed played.

Here are some insane set indifferences.

Cloud-delivered applications are one of those things you watch to see how the  phenomenon is going to fully express itself.

SQL Server:

Discover, diagnose, and document all your SQL Servers On your Coffee Break.

Jen Stirrup has a good post post about PASSBAC keynote.

There are lots of horror stories of people going to Europe on vacation or on business and coming home to massive cell phone bills.

Jason Strate discusses about staggering SQLIO sessions across multiple volumes.

You can export data from SQL Server using BCP command for SQLCMD utility. However, these utilities does not support dynamic file names when exporting data.

MySQL:

Cedric Peintre dishes out an incomplete guide to linux system configuration for MySQL.

Do you need run multi-master MySQL database clusters across sites?

Daniel van Eeden has a good review about Instant InnoDB book.

SQLAchemy has support for MySQL Connector/Python for a while now. Here is a little HOWTO showing how install both, and setup a database engine.

Ivan Zoratti is welcoming to the MySQL month!