DBA Blogs

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.

I was working on some disk performance problems and came across a simple way to test the latency or delay built into the networks that connect my database servers to their disk storage.  All I have to do is read some disk blocks from a table several times to be sure they are cached in the disk storage server and then flush the database buffer cache and read from the table once more.  Since the blocks are not cached in the database buffer cache but are cached on the disk array the time it takes to read the blocks approaches the time it takes to copy the blocks over the network.  Of course there is some CPU on both the source and target to copy the blocks in memory but the physical disk read is eliminated and you see close to the minimum time it is possible for a transfer to take.

So, here is my simple test script.  It assumes the user, password, and tnsnames.ora name are passed as parameters and that the user is a DBA user like SYSTEM.

connect &1/&2@&3

-- create test that will show how fast reads
-- can be when cached on the disk subsystem

-- document version

select * from v$version;

-- create small table

drop table test;
create table test as select * from dba_tables where rownum < 1000;

-- query three times to get cached in the disk system

select sum(blocks) from test;
select sum(blocks) from test;
select sum(blocks) from test;

-- flush from database cache

alter system flush buffer_cache;

-- reconnect so session waits are cleared

disconnect
connect &1/&2@&3

select sum(blocks) from test;

-- show average scattered read
-- should be best time you can
-- get since blocks are cached
-- in the disk subsystem

VARIABLE monitored_sid number;

begin

SELECT sid into :monitored_sid from v$session 
where audsid=USERENV('SESSIONID');

end;
/

select  
total_waits,
time_waited_micro/total_waits
FROM V$SESSION_EVENT a
WHERE a.SID= :monitored_sid
and event='db file scattered read';

Here is what the output looks like on a slow system with network problems (over NFS in my case):

TOTAL_WAITS TIME_WAITED_MICRO/TOTAL_WAITS
----------- -----------------------------
          4                       1410.25

Here is what it looks like on a nice fiber network:

TOTAL_WAITS TIME_WAITED_MICRO/TOTAL_WAITS
----------- -----------------------------
          4                          40.5

40 microseconds for a disk read is sweet compared to 1410!

I’ve uploaded the script and three runs I made with it here.

- Bobby

P.S.  I realized this morning that the first example with 40 microseconds for a read is really just pulling from the operating system’s filesystem cache because the database I was testing on doesn’t use direct IO.  Here is a test on a system with the SAN that uses direct IO:

TOTAL_WAITS TIME_WAITED_MICRO/TOTAL_WAITS
----------- -----------------------------
          5                         478.4

I ran this several times and this was the best result.  The others were in the 600-700 microsecond range mostly.  So, I guess when you run the test script you are really testing everything behind the scenes that could benefit from caching except for the database’s buffer cache.  Without direct IO you may never reach your SAN network because you will have cached the blocks at the OS filesystem cache level.

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

[oracle@localhost dbs]$ pwd
/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs
[oracle@localhost dbs]$ date
Mon May 20 08:17:46 PDT 2013
[oracle@localhost dbs]$ ls -ltr|tail -3
-rw-r----- 1 oracle oracle 1536 Oct  2  2010 orapworcl
-rw-rw---- 1 oracle oracle 1544 May 20 08:16 hc_orcl.dat
-rw-r----- 1 oracle oracle 3584 May 20 08:16 spfileorcl.ora

[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon May 20 08:17:53 2013

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

connected to target database: ORCL (DBID=1229390655)

RMAN> backup current controlfile;

Starting backup at 20-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=69 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 20-MAY-13
channel ORA_DISK_1: finished piece 1 at 20-MAY-13
piece handle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2013_05_20/o1_mf_ncnnf_TAG20130520T081800_8snhob80_.bkp tag=TAG20130520T081800 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-MAY-13

RMAN> quit


Recovery Manager complete.

[oracle@localhost dbs]$ ls -ltr|tail -3
-rw-rw---- 1 oracle oracle    1544 May 20 08:16 hc_orcl.dat
-rw-r----- 1 oracle oracle    3584 May 20 08:16 spfileorcl.ora
-rw-rw---- 1 oracle oracle 9748480 May 20 08:18 snapcf_orcl.f
[oracle@localhost dbs]$ 

“A beginning is the time for taking the most delicate care that the balances are correct.”

It is spring. Time for planting new seeds. I started on a new job last week, and it seems that few of my friends and former colleagues are on their way to new adventures as well. I’m especially excited because I’m starting not just a new job – I will be working on a new product, far younger than Oracle and even MySQL. I am also making first tiny steps in the open-source community, something I’ve been looking to do for a while.

I’m itching to share lessons I’ve learned in my previous job, three challenging and rewarding years as a consultant. The time will arrive for those, but now is the time to share what I know about starting new jobs. Lessons that I need to recall, and that my friends who are also in the process of starting a new job may want to hear.

Say hello
I’m usually a very friendly person and after years of attending conferences I’m very comfortable talking to people I’ve never met before. But still, Cloudera has around 200 people in the bay area offices, which means that I had to say “Hello, I’m Gwen Shapira the new Solutions Architect, who are you?” around 200 times. This is not the most comfortable feeling in the world. Its important to go through the majority of the introductions in the first week or two, later on it becomes a bit more awkward. So in the first week it will certainly seem like you are doing nothing except meeting people, chatting a bit and franctically memorizing names and faces. This is perfectly OK.

Get comfortable being unproductive
The first week in a new job feels remarkably unproductive. This is normal. I’m getting to know people, processes, culture, about 20 new products and 40 new APIs. I have incredibly high expectations of myself, and naturaly I’m not as fast installing Hadoop cluster as I am installing RAC cluster. It takes me far longer to write Python code than it does to write SQL. My expectations create a lot of pressure, I internally yell at myself for taking an hour or so to load data into Hive when it “should” have taken 5 minutes. But of course, I don’t know how long it “should” take, I did it very few times before. I’m learning and while learning has its own pace, it is an investment and therefore productive.

Have lunch, share drinks
The best way to learn about culture is from people, and the best way to learn about products is from the developers who wrote them and are passionate about how they are used. Conversations at lunch time are better than tackling people in the corridor or interrupting them at their desk. Inviting people for drinks are also a great way to learn about a product. Going to someones cube and asking for an in-depth explanation of Hive architecture can be seen as entitled and bothersome. Sending email to the internal Hive mailing list and saying “I’ll buy beer to anyone who can explain Hive architecture to me” will result in a fun evening.

If its not overwhelming, you may be in the wrong job
I’m overwhelmed right now. So many new things to learn. First there are the Hadoop ecosystem products, I know some but far from all of them, and I feel that I need to learn everything in days. Then there is programming. I can code, but I’m not and never have been a proficient programmer. My colleagues are sending out patches left and right. It also seems like everyone around me is a machine learning expert. When did they learn all this? I feel like I will never catch up.

And that is exactly how I like it.

Make as many mistakes as possible
You can learn faster by doing, and you can do faster if you are not afraid of failing and making mistakes. Mistakes are more understandable and forgivable when you are new. I suggest using this window of opportunity and accelerate your learning by trying to do as much as possible. When you make a mistake smile and say “Sorry about that. I’m still new. Now I know what I should and shouldn’t do”

Take notes
When you are new a lot of things will look stupid. Sometimes just because they are very different from the way you are used to things in a previous job. Don’t give in to the temptation to criticise everything, because you will look like a whiner. No one likes whiner. But take note of them, because you will get used to them soon and never see things with “beginner mind” again. In few month take a look at your list, if things still look stupid, it will be time to take on a project or two to fix them.

Contribute
I may be new at this specific job, but I still have a lot to contribute. I try hard to look for opportunities and I keep finding out that I’m more useful than I thought. I participate in discussions in internal mailing lists, I make suggestions, I help colleagues solve problems. I participate in interviews and file tickets when our products don’t work as expected. I don’t wait to be handed work or to be sent to a customer, I look for places where I can be of use.

I don’t change jobs often. So its quite possible that I don’t know everything there is to know about starting a new job. If you have tips and suggestions to share with me and my readers, please comment!

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

I’ve seen some very good information posted in this feed which combines blog postings from many different Oracle performance experts who are part of what is called the “Oak Table”

http://www.oaktable.net/feed/blog-rss.xml

I’ve been using Internet Explorer to keep track of new posts in its “Feeds” section of the Favorites.  Here is how to add the Oak Table blog feed to Internet Explorer:

Go to the URL listed above and click on “Subscribe to this feed”

Click on Subscribe button

Success!  Now click on Favorites and then Feeds

For any feed in your list if you see the feed name in a darker font it means there is a new post.  So, as I have time, I’ll go to my feeds and see which of the ones I’ve subscribed to have new posts.  If you are looking for performance tuning information I highly recommend the Oak Table feed.

- Bobby

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

In my forum discussion about free buffer waits I came across a term that I didn’t understand: “inode lock contention”.  I’m pretty sure I had seen this same term years ago on one of Steve Adams’ pages on IO.  But, I didn’t really understand what the term meant and so it was hard to understand whether this was something I was seeing on our production system that was experiencing “free buffer waits”.

First I had to figure out what an inode was.  I knew that it had something to do with the way Unix filesystems work but reading this article really helped clear up what inodes are at least on HP-UX.  Inodes are small chunks of bytes that are used to define a Unix filesystem.  On HP-UX’s VxFS filesystems a type 1 inode can point to up to 10 extents of one or more contiguous 8K blocks on a large filesystem.  The filesystem I’ve been testing on appears to have 32 meg extents if I’m reading this output from lvdisplay correctly:

LV Size (Mbytes)            1472000
Current LE                  46000

Total size of 1,472,000 meg divided by 46,000 logical extents = 32 meg per extent.

Since the inode can point to 1 to 10 extents it could point to between 32 and 320 meg.

My test case had 15 tables that were more than 1 gigabytes each.  It seems like each table should span multiple inodes so even if there is locking at the inode level it looks like it won’t lock the entire table at once.  Still, it seems unlikely to me that every time a table is updated that reads from all the other parts of the table pointed to by the same inode are really blocked by an inode lock.  Yet that is what this document suggests:

“During a read() system call, VxFS will acquire the inode lock in shared mode, allowing many processes to read a single file concurrently without lock contention. However, when a write() system call is made, VxFS will attempt to acquire the lock in exclusive mode. The exclusive lock allows only one write per file to be in progress at a time, and also blocks other processes reading the file. These locks on the VxFS inode can cause serious performance problems when there are one or more file writers and multiple file readers.”

It uses the term “file” but I assume if you have a large file that has multiple inodes it means it will lock just the pieces associated with the one inode that points to the blocks that are being written.  The article goes on to explain how you can use the “cio” option to enable concurrent IO and eliminate this inode contention preventing writers from blocking readers.  But, I’ve been testing with just the direct IO options and not the cio option and seeing great results.  So, would I see even better improvement with concurrent io?

I didn’t want to mess with our current filesystem mount options since testing had proven them to be so effective but I found that in glance, a performance monitoring tool like top, you have an option to display inode waits.  So, I took a test that was running with direct IO and had 15 merge statements loading data into the same empty table at once and ran glance to see if there were any inode waits.  There were not:

So, I don’t know if I can depend on this statistic in glance or not.  It appears that the direct IO mount options are all we need:

mincache=direct,convosync=direct

filesystemio_options=DIRECTIO

There may be some case within Oracle 11.2.03 on HP-UX 11.31 where you can be hampered by inode lock contention despite having direct IO enabled but my tests have not confirmed it and I’ve banged pretty hard on my test system with a couple of different types of tests.

- Bobby

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.

I just read this post about a new patch that will allow you to affect the way the optimizer calculates clustering factor and hence how likely it is to choose a particular index for a query plan.

http://richardfoote.wordpress.com/2013/05/08/important-clustering-factor-calculation-improvement-fix-you/

Pretty cool.  I haven’t tried it, but it looks promising.

- Bobby