Jul 24

Oracle 12.1.0.2 Released

Oracle just released the 12.1.0.2 patchset approximately thirteen months after 12.1.0.1 was made available. There are a few new features with this release that can be seen here.

Jul 22

New Parameters in 11.2.0.4

I am working towards upgrading databases from 11.2.0.3 to 11.2.0.4. This should go smoothly, right? Well after I upgraded a development database, I got a call about a slow performing query. In all of the 11.2.0.3 databases, the query runs in about 50 seconds. In the 11.2.0.4 databases, the query runs in 4 minutes. The execution plans are different even though the table/index stats are identical. So this is a version-related problem. One of the things I did was to check the parameters between the different versions. I was surprised to find that a simple patchset upgrade now has five new parameters.

From the 11.2.0.3 database:

 

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jul 22 14:23:39 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> select count(*) from v$parameter
 2 where name in ('cell_offloadgroup_name','dnfs_batch_size',
 3 'enable_goldengate_replication','instant_restore','java_restrict');
COUNT(*)
----------
 0
SQL> select count(*) from v$parameter;
COUNT(*)
----------
 347

 

From the 11.2.0.4 database:

 

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 22 14:23:26 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> select count(*) from v$parameter
 2 where name in ('cell_offloadgroup_name','dnfs_batch_size',
 3 'enable_goldengate_replication','instant_restore','java_restrict');
COUNT(*)
----------
 5
SQL> select count(*) from v$parameter;
COUNT(*)
----------
 352

 

Yep, five more parameters in 11.2.0.4. Those parameters are:

cell_offloadgroup_name
dnfs_batch_size
enable_goldengate_replication
instant_restore
java_restrict

The two in red are in the Oracle 11.2 documentation. The other three are not, nor is there any mention of them in Metalink.

 

I’ll have to see if I can get to the bottom of these and if they could be contributing to my performance problem.

 

 

Jul 15

Oracle Big Data SQL

Oracle announced a new product called Big Data SQL that will be available in the Fall. I think this product will be interesting. The one downside I see is that it appears to only run on an Oracle appliance which means you are forced to use their hardware as well.

For more information, see the following.

http://www.zdnet.com/oracle-big-data-sql-lines-up-database-with-hadoop-nosql-frameworks-7000031564/

http://www.informationweek.com/big-data/big-data-analytics/oracle-joins-sql-on-big-data-bandwagon/d/d-id/1297287

 

Jun 20

INS-41112 and INS-41113

Trying to install Grid Infrastructure on my new hosts on my laptop (see my previous post), I ran into two errors: INS-41112 and INS-41113.

For the INS-41112 error, I determined that I had a firewall running. Stopping iptables fixed this error.

The INS-41113 error was because I have eth0 and eth1 running on the same subnet. You can’t have this. So I changed eth0′s Network Adaptor in VMWare Fusion to use NAT. With eth1 Private to My Mac, the two would be on different subnets. Since eth0 was going to be NAT, I also changed the VIP and SCAN VIP in my hosts file to use the same subnet as eth0. This fixed the other error.

Jun 20

Oracle RAC on MacBook Pro with VMWare Fusion

My laptop is a MacBook Pro. Being that I work in the real world, it means that I often have to run software products that can only be installed on a Windows system.  So I have to run a Windows VM on my laptop to support those applications. For no good reason, I use VMWare Fusion for my virtual environments on my laptop.

I have seen a number of documents floating around that discussed how to set up RAC on a laptop. Many of them used Oracle’s Virtual Box, which is a great product. But I already have a hypervisor on my laptop and I did not want toinstall another one. I couldn’t find a lot of good information on how to setup Oracle RAC on a MacBook Pro with VMWare Fusion. Maybe its out there, but I couldn’t find it. So I figured out how to get Oracle RAC up and running in this configuration. I documented my process in this paper.

www.peasland.net/documents/Oracle_RAC_on_MacBookPro_with_VMWare_Fusion.docx

This paper does not show you how to install Grid Infrastructure or the Oracle RDBMS software for RAC. This paper just shows you how to get your VM’s up and running, get the network configured as well as the shared storage. The process of installing GI and the rest is well documented in other locations.

 

Jun 16

Safety Nets

Safety nets exist for a reason. To protect you from when a catastrophic event happens. To stop that event from becoming your worst nightmare.  A person who washes windows on a skyscraper has one catastrophic event to worry about…falling to the ground. So that person wears a harness attached to a rope. If the platform falls or if the wind knocks him over, the harness and rope will stop his worst nightmare, that of him hitting the ground below.  For a skyscraper window washer, the catastrophic event is falling, no matter the reason. The worst nightmare of that even is hitting the ground below.

Likewise, when I go to the zoo, I try to see the lions and tigers on display. No matter which zoo I visit, there is always a barrier of some sort between me and the animals. The catastrophic event would be if one of those animals could touch me and the worst nightmare is that I get attacked by those animals and suffer fatal wounds. The barrier is the safety net.

Many years ago, when I was much younger, I was sitting in an auditorium at our company’s facility listening to the HR manager talk about next year’s changes in our benefits. He was talking about changes to our health plan, etc. He said something during the discussion that has always stuck with me. He said “insurance is all about risk management”.  For the purposes of our meeting that day, he was talking about health insurance and life insurance. And its true, we buy the health insurance coverage that manages risk for us. I could buy the best option the company offers and I would be covered for lots of risk scenarios. But it would cost me the most. Or I could buy the cheapest option, which would not cover as much of my medical needs but then it wouldn’t cost me. His point is that I had to weigh the risks of things happening to me, medically, and by the appropriate coverage taking the costs into account as well.

So what does this have to do with database systems? Lately I’ve seen a few people trying to do some crazy things with their standby database configuration.  Remember that your standby database is your corporation’s safety net. Should a catastrophic event happen which shuts down the primary data center, the worst nightmare would be your customers not having access to the database. The standby database at an alternate data center comes to the rescue. It would be career-threatening to make a configuration change to your primary/standby systems that harm your safety net’s ability to work effectively. You won’t see the window washer loosening straps on his safety harness while he is 30 stories above ground. You won’t see me cutting the barrier between me and the tiger at the zoo. Similarly, you shouldn’t be taking chances with your corporation’s safety net for their production Oracle databases.

I recently came up with this quote:

There is a law written somewhere in the universe that says the day your standby’s config diverges from your primary is the day you will need to failover. Better hope such divergence doesn’t hurt the end user experience now that they are using the standby

Why take chances with your safety net. Just like the window washer 30 stores above ground, you never know when that gust of wind is going to test your safety net. Better hope the change you make doesn’t impact the ability of your safety net to eliminate the worst nightmare when the catastrophic event becomes a reality. Your company has already decided to commit the funds for risk management. They funded the implementation of an alternate data center and a standby database.

 

 

Jun 04

Splunk

A few weeks ago, I attended some training on a new product at my company called Splunk. After the training was over, I spent some time reflecting on this product’s strengths and how it can be leveraged in our business.

 

Splunk is great working with unstructured data. The most common source of this data is from log files. Log files are forwarded to the Splunk system. This system indexes the log file’s contents to facilitate searching that data. Over the course of my career, I’ve written a number of parsers to obtain information from a log file. I’ve used Perl and of course awk/sed/grep all in an effort to glean information from the log file. Each time, I have been required to determine column positions in a line, or delimiters in the file that let me separate the meaningful data from the chaff.  Splunk does all this work for you, nice and easy.

 

As data is ingested into Splunk, the Splunk engine indexes portions of the data. Splunk works with many of the common logs you may find in your IT organization. Apache web server logs. Windows Event View Logs. Unix/Linux syslogs. We all have these types of logs in our environment. Splunk indexes these logs around common tags that facilitate searching. The first thing that is indexed is the timestamp of the log entry and not surprisingly, time range searching is very common in Splunk. If you spot items that could serve as potential tags in the data stream, or if Splunk is not familiar with your specific log file, you can create your own tags and Splunk will index on those tags for you.

 

In Splunk, they call it “search” but for database professionals, we are used to the term “query”. These two are essentially the same. Splunk’s query language is not the same as SQL most DBA’s are familiar with. However, the DBA will have little trouble adapting to Splunk’s query language. In our Splunk training class, the first part of the training on how to search was spent learning what the DBA might term the WHERE clause. If you are familiar with SQL, you know that the WHERE clause of the query limits the rows returned in the result set. Splunk is very similar. You construct search criteria to limit rows. The next part the DBA will most likely focus is to limit the columns of the result set. Splunk allows you to do similar and even has an option to convert the data into a table format. Similar to the way a DBA would leverage views or stored procedures to provide a level of abstraction from the data, Splunk allows you to save your searches so that they can be used in reports or dashboards.

As I stated earlier, Splunk is very good at working with unstructured data. Where I work, we have a table in our production database to capture application errors. When the application has an error, it writes information into this table such as the date/time of the error, the module of the application being executed. But the most important piece of information in this table is a CLOB column called the “developer message”. The developer message is the full Oracle error stack, which we do not expose to the end user, but is very useful to IT staff in diagnosing errors. Being that this message is in a CLOB column, it is difficult to search with SQL statements. This is where Splunk excels. In the near future, I will be setting up a routine in Splunk to ingest this error log table so that we can perform quick searches of the unstructured data in the developer message column.

Splunk can query databases directly. During the training, I started thinking of other things we could extract from our production database and use to search in Splunk. I had to stop myself because what I was often thinking of is no different than issuing SQL in the database. If the data is structured in rows and columns in a database table, then use the database engine to query for the data. The database engine is very good at doing so. But in the paragraph above, I am describing one column of a table that contains unstructured data. So if we can move that into Splunk, we can then use that tool to do what it is very good at doing.

 

That being said, there are still very good reasons to have Splunk query a database system directly for structured data. One of our uses is to mine logs for security related issues. We have already set up a security dashboard that our auditors can use to gain information. We also have a need to add information already contained in our databases to this dashboard. Now in the past, we’ve written many reports and used many different reporting tools to present data in a meaningful way. We’ve never needed Splunk in the past for that. However, what is different in this case is that we need a lot of information from the unstructured data source, hence leveraging Splunk. And we desire to add data contained in a structured layout to the same report. So we will obtain this information from the database so that all data, structured and unstructured, is contained in one report, in Splunk.

 

Towards the end of my training, I kept thinking more and more that Splunk is really just a database engine, a highly specialized one. It won’t replace Oracle or SQL Server because Splunk has no concept of transaction control. Nonetheless, at its very essence, Splunk is a database engine. Just like Oracle and SQL Server, data is added to the system. Indexes are created to facilitate fast searching of that data. Splunk has “report acceleration” that is akin to table partitioning. Reports are run faster if your time period in the report is within the date range of a partition (Splunk uses the term “bucket”). Splunk’s reporting engine is not that different than SQL Server’s Reporting Services, part of a traditional SQL Server installation. I’m always finding parallels between Splunk and other traditional database engines. Whereas Oracle and SQL Server are general purpose databases capable of handling many different data serving needs, Splunk is specialized having the ability to easily handle unstructured data.

 

I do like this product very much. I am excited to learn more about it. I think that Splunk will have a good future in our organization.

 

 

 

 

 

 

May 19

Google Doodle – Rubik’s Cube

This is just one of those posts that have nothing to do with databases.

 

Today’s Google Doodle is a working Rubik’s Cube to celebrate the 40th anniversary of this device. Like millions of others, I had one of these when I was growing up. I learned early on how to break them apart so that you could put the blocks back together and have a completed puzzle. But eventually I figured out how to solve it. We used to have races between class mates back in the day.

Many doodle have animations if you click on them. This was no different I assumed. If you click on it, the animation will start. But I was delighted to discover that after the animation stopped, the image became a working cube that you could solve!  Very cool!

 

There is also a link to Google’s Cube Lab. Here you can build your own cubes or see what others have done.

May 14

Tuning SQL

Not that long ago, I gave a tutorial on Explain Plan to our application development staff. One question that came up was how do I go about deciding which SQL statements need tuning? I use a few different approaches and find SQL tuning candidates from different angles.

 

  1. I routinely perform code reviews on SQL statements that hit our database. I leverage my experience to quickly look at a SQL statement that is new or has been significantly modified to decide if I need to investigate the SQL further. For instance, if a query on a table is searching on a PK or Unique column, then I can be pretty sure it will run fast. If the SQL statement looks suspect to me, I will run it through Explain Plan and/or time the statement to ensure it runs sufficiently.
  2. I have Oracle’s Enterprise Manager alert me for resource contention in our Test database system. Any SQL statements I missed during code review can sometimes be found here. If I get an alert of resource contention, I will jump on and see if a SQL statement or two is causing a problem. This is my last chance to catch SQL statements before they hit production.
  3. I leverage Ignite for Oracle. This product is from Confio, but Confio is now part of Solarwinds. Normally, I don’t plug vendor’s products, but I’ll make an exception in this case. The thing I like about Ignite is that I have it email me a report every Monday. The report contains the Top N offenders based on wait time. It takes me seconds to look at this report. I look for the big bars in the report. In the example screen shot below, you can see a blue bar that immediately grabs your attention. The number to the right is an id value and if you click on it, you can get the SQL text. This is a great way of identifying the SQL statements that need tuning. It is super quick and super easy.

ignite

 

As you can see, I try to find problematic SQL as they are being developed, when they are in our test system, and even after they hit production.

May 14

Updated WP

Today I updated WordPress that I use for this blog. The upgrade started when I initially upgraded the theme used for presentation. But the theme upgrade broke my blog. So I used a different theme for a short period of time. I then upgraded the WP software and was finally able to use the upgraded theme.

It’s been awhile since I posted anything relevant. I promise something soon.

Older posts «