Is this a good time to be a DBA? My biased opinion is that any time is a good time to be a DBA. The US Bureau of Labor Statistics released an outlook indicating that DBA positions are expected to increase 15% between 2012 and 2022.

Now comes this article that says about 50% of DBAs are expected to leave the market in the next 10 years.
Veterans Day is such purchase viagra http://pharma-bi.com/2009/11/how-to-create-a-misleading-quadrant-analysis-%E2%80%93-by-accident/ a day. Likewise, if your thoughts are positive, you attract positive people and events that match discounts on levitra that belief and expectation. It improves buy levitra professional your sexual stamina to last longer in bed. Foot tattoo gives cialis online pharmacy your foot a unique look.
Demand is rising!

I am working on upgrading all of our production databases to the Oracle 11.2.0.4 version. My company’s most important database serves a custom, in-house developed application so we have the luxury (and sometime the curse) of having complete control over the application source code. If I discover a version-specific issue with a their party application, I file a trouble ticket with the vendor to get the issue fixed. But for our own application, I often have to diagnose the problem and determine how to get the issue fixed.

Since I have been at this company, I have upgraded from 11.1.0.7 to 11.2.0.2 and then to 11.2.0.3 and now to 11.2.0.4. The two previous upgrades went just fine. No problems. So I have been very surprised that the upgrade from 11.2.0.3 to 11.2.0.4 has been problematic for our application.

EVEN WHEN YOU THINK THE UPGRADE IS “minor“, PERFORM ADEQUATE TESTING!!!!

I never expected to find issues with this simple patchset upgrade. I’m not skipping versions and 11.2.0.4 shouldn’t introduce too many problems.  My first issue I blogged about here:

http://www.peasland.net/2014/08/19/sticky-upgrade-problem/

The next problem is a query similar to the following in our application code:

SELECT DISTINCT columnA

FROM our_table

Lawax Capsules and Vital M-40 Capsules is a wonderful solution to treat erectile dysfunction. cialis generika You are not complete without a partner, this is the reason these capsules have been proved as the best herbal remedies for solving your impotence, premature ejaculation, nightfall, wet dreams, spermatorrhea and cialis samples nocturnal emissions. All in all, avoid cheap pills and always look for reasonabhttp://foea.org/projects/ cialis tadalafil generic price and be aware of the manufacturer’s reputation as well as taking the appropriate dosage for your body. Remember, this benefit of Kamagra for ED issue can hurt mentally to viagra tablets 100mg some of the person as penile erection has been a major cause of couple’s broken relationship.

ORDER BY columnB;

The above query will now return an ORA-01791 error in Oracle 11.2.0.4 but it ran just fine in previous versions. When a DISTINCT is used, and the ORDER BY clause has a column not seen in the SELECT clause, the ORA-1791 error will be raised. Oracle says that the fact that this used to work is a bug. The bug is fixed in 11.2.0.4 so the above now raises an exception.

When I first was made aware of this issue, my initial thought was why are we ordering by a column not in the SELECT caluse? The end user won’t know the data is ordered because they can’t see the order. Then I found out that this routine is only used for internal processing. Well machine’s work just fine without ordering the data. So the simple fix on our end was to remove the GROUP by clause. As soon as the code changes gets into production, I can proceed with my database upgrade.

It is so important that I’ll say it again:

EVEN WHEN YOU THINK THE UPGRADE IS “minor“, PERFORM ADEQUATE TESTING!!!!

At this company, we follow a strict process for changes. The change is made in development first. And then after a period of time, the change is made in Test environment. And then after a period of time, if there are no issues, the change can proceed to production. We also have a custom test application that exercises key components of our application so even if our testers are not hitting that portion of the app, our automated test suite will.

Without adequate changes, the two issues we encountered would most likely not have been noticed until the change was in production. Then the DBA would have been blamed, even though both of these issues were application code problems. Test, test, and test again.

I found this nice blog entry today:

https://blogs.oracle.com/UPGRADE/entry/grid_infrastructure_management_repository_gimr

However, there are several ED medicines in the market that can increase the level of generico levitra on line this sex hormone. Cheap kamagra tablets have been packed with sildenafil citrate, the award-wining chemical approved by the commander cialis Food and Drug Administration for the treatment of premature ejaculation. Else, he can lowest prices cialis also go with World Wide Web. The situation can levitra 10 mg be easily evaded by maintaining a healthy lifestyle and to avoid or stop smoking all together.  

When performing database upgrades, adequate testing is important to understand the impacts, both positive and negative, the database upgrade has on the application. I have been preparing to upgrade databases from the 11.2.0.3 version to 11.2.0.4. One weekend, myself and another DBA spent some time upgrading about half of our production databases to the new target version. First thing on Monday morning I got a call from a Developer who had a query that was now running slowly. Why did we upgrade just half of the dev databases? For this specific reason. I immediately suspected the query performance was version related. I was able to formulate a reproducible test case. I ran the test case against all of the dev databases. The 11.2.0.3 databases executed the query in 30 seconds, consistently across the board. The 11.2.0.4 databases ran the same query in 3.5 minutes, repeatable in the same version. Because we only upgraded half of the databases, I was able to verify if the issue was version-related…and it was…at least on the surface.

After any database upgrade that has SQL statements performing worse, a common “fix” is to upgrade the table and index stats so that the new optimizer version has good information to work with. Updating stats did not fix the problem. I could see that in the 11.2.0.3 database, the CBO was choosing to use an index and since everything it needed was in the index, it did not access the table. Furthermore, the join was performed with a Nested Loops algorithm. In the 11.2.0.4 database, the same index was used but the table was also accessed and a Hash Join algorithm was used. Why was the CBO making two different decisions?

Anytime we need to peak into the CBO decision making process, it means we need to use the 10053 trace. I captured the trace files from each version. The first part of the trace file shows the optimizer-related initialization parameters. All the parameters were the same except for OPTIMIZER_FEATURES_ENABLE and DB_FILE_MULTIBLOCK_READ_COUNT. Neither of these are explicitly set so they are default values. Obviously, O_F_E has a different default value for each database version. I was surprised that DB_F_M_R_C changed its default value from 11.2.0.3 to 11.2.0.4. I tried to explicitly set the parameter values in the 11.2.0.4 database to match the 11.2.0.3 database but it did not improve the runtime. These parameters, while different, were not having any bearing on the query performance.

The next part of the 10053 trace shows statistics on the tables involved in the query. These were identical in both versions so stats weren’t the issue.

The next part of the 10053 trace shows the table access paths and which one is deemed to have the lowest cost. Here is where the mystery got interesting. In the 11.2.0.3 version, the CBO determined that the cost to access the table using the index was 1258 and the cost of using just the index alone was 351. In the 11.2.0.4 version, the CBO determined the cost to access the table using the index was 127 and the cost of using just the index alone was 351. In fact, all of the table access paths examined by the CBO were identical in both version. It was in that very first cost calculation that the CBO determined a low cost in 11.2.0.4 and a higher cost in 11.2.0.3, thus leading to one access path for one version and another access path for the other version. In the part of the 10053 where it considers which join method to use, the answers were different because the chosen access paths were different.

I still have no answers as to why 11.2.0.4 made that one calculation differently than 11.2.0.3 did, especially when all the other access path calculations were identical in both versions. That one puzzles me and I might need the help of higher powers to get to the answer.

The always in stock buy cheap cialis blood flow in that region for the effect to manifest if you have eaten fatty foods. How Should It Be Stored? Store at room temperature between 59 to 86 degree F away from moisture, heat and not in levitra uk bathroom. Further it had serious side effects like change in buy female viagra appearance or hormonal changes. viagra ordination http://appalachianmagazine.com/category/featured/page/5/?filter_by=random_posts Tell your doctor for foods helpful to enhance the quality of erections. That being said, I was able to determine the root cause of the problem and it wasn’t really version related after all. The problem was that we had in our WHERE clause the following:

WHERE column = :b1

It seems innocent enough. The problem is that the column is defined as VARCHAR2(4) and the bind variable is declared as NUMBER. Oracle performs an implicit conversion. Because the CBO doesn’t have an accurate picture of the bind variable’s contents, it obtains a suboptimal execution plan. Changing the datatype of the bind varialbe fixed the issue. The query now ran in 10 seconds! Wait…it went from 3.5 minutes down to 10 seconds, which is great, but in 11.2.0.3 it was running in 30 seconds. Because the bind variable had the wrong datatype there as well. The proper data type in the 11.2.0.3 had the query running in…you guessed it…10 seconds.  This is why I say the problem turned out to not be a version-related issue. We had the same problem in 11.2.0.3 a query that could be improved with the proper datatypes. The new version just magnified an existing problem we didn’t know we had.

All of this highlights the importance of proper testing even for simple patchset upgrades.