Dontcheff

Archive for June, 2011|Monthly archive page

The Google DBA, the GUI DBA and the DBA who does all the work

In DB2 database, DBA, MySQL, Oracle database, SQL Server on June 26, 2011 at 12:36

I often neglect notes and comments on people who divide DBAs into groups and types but this DBA’s Journey into the Cloud is worth commenting on:

The exposé is so short that one does not get the impression if these 3 DBA types are mutually exclusive or not. If not, why would then someone profile DBAs into groups?

You obviously use Google and Metalink (called MOS nowadays). And you use GUIs: Grid Control, Oracle Developer, Toad? Doesn’t look impressive if you cannot make it into the last group, right?

And into which group will the guy hired for this Pre-Sales DBA job fit?

Let me change to another classification method and peek at the The DBA Corner of Craig Mullins. He says that the more common types of DBAs are:

– System DBA
– Database Architect
– Database Analyst
– Data Modeler
– Application DBA
– Task-Orientated DBA
– Performance Analyst
– Data Warehouse Administrator

Too many. I might agree that the system DBA and application DBA are different roles but all those analysts? “The performance analyst is usually the most skilled, senior member of the DBA staff, a role that he has grown into due to his experience and the respect he has gained in past tuning endeavors.” Interesting..

Next, I want to see what Don Burleson has to say. What Type of DBA Are You? Only 3 options here:

– The Scientist DBA
– The Can-do DBA
– The Empathic DBA

Looks like classification on emotional basis. Interesting reading though.

Let us look at what Wikipedia has to say. Déjà vu! 3 types again:

– System DBA
– Development DBA
– Application DBA

Development DBA? Used to be called a programmer years ago. I always thought that the Development DBA takes care of the development databases 🙂

What do people have to say about the DBA types in the internet forums. Let us visit DBA support. Funny stuff at the end! Now, the types are 4, with short descriptions listed below:

– The smart and industrious DBA
– The stupid and industrious DBA
– The smart and lazy DBA
– The stupid and lazy DBA

Up to now, it looks to me that I am always a mixture of some type. A hybrid? Brian Knight figures it out in his article DBA Job Description: What type of DBA are you? 3 types again:

– Production DBA
– Development DBA
– Hybrid DBA

I really don’t know. 10 years ago, he wrote about the Death of the Production DBA and companies still recruit SQL Server Production DBAs.

If you surf the Internet, you will find all type of DBA classifications: the Oracle DBA and the MySQL DBA; the certified DBA and non-certified DBA; the on-call DBA and the 9-to-5 DBA.

There is even a Marketing DBA: “DBA skill needs to sale out software to vendors. To convince customer in technical point of view marketing team requires DBA help. Marketing DBA tries to satisfy each technical queries of customer and help marketing team. There is no database administration and maintenance directly involve in this DBA role.”

Look once more in the text above: A database administrator role such that there is no database administration. No comment.

But eventually, it looks to me that DBAs can be divided into two groups: those who divide DBAs into groups and those who don’t..

Oracle database perches on the Amazon RDS cloud

In DBA, Oracle database on June 19, 2011 at 04:43

Have you already seen this one: Amazon RDS for Oracle Database ? Well, I even got an email:

And how about Maureen O’Gara’s comments in her article Oracle Goes to Amazon published in the Cloud Computing Journal?

Or do you agree with Curt Monash’s Quick thoughts on Oracle-on-Amazon where he claims that there is non-production appeal but that this isn’t for production usage?

Right, you can now get an Oracle database with Amazon Relational Database Service (Amazon’s RDS). But what is the Total Cost of Ownership (TCO)? Where is the calculator? Let us have a look at what Amazon RDS has to offer and see if it is worthy.

Obviously, if you want your database running on Amazon RDS, you want things to be as simple as possible. And I am not talking about the eighteen minute video called Oracle Database 11g on Amazon RDS in ten minutes. Which is worth watching anyway. Another good sources are:

The Amazon Web Services Blog by Jeff Barr
The Cloud Databases and Oracle Whitepaper by Frank Munz
The Oracle Database on Amazon RDS by Patrick Schwanke

You do not want to bother with licensing the database(s) separately. You do not want high DBA expenses for probably Amazon will do the system administration in return of you payment. In this case, you have the License Included option with payments either per hour (like I want to have a database for 17 hours) or per year (options are 1 or 3 years). With license included you are stuck with Standard Edition One.

Oracle Database Standard Edition One (SE1) is for single processor servers with max. 2 sockets/CPUs. Let me quote the 11gR2 documentation: “Oracle Database Standard Edition One delivers unprecedented ease of use, power, and performance for workgroup, department-level, and Web applications. From single-server environments for small business to highly distributed branch environments, Oracle Database Standard Edition One includes all the facilities necessary to build business-critical applications.”

Although Oracle SE1 lacks dozens of features, still it is in my opinion enough appealing for a development platform, even for a basic business critical production system.

Example: I will buy the cheapest database possible. Thus, let us consider a small database which we will build and use for a period of 1 year. What we pay is one-time non-refundable $345 fee for the instance and then $0.06 per hour which makes about $524 (168 hours in a week and 52 weeks in a year).

One GB of storage will cost us $1.20 per year. I will then take 10GB which makes another $12 which is nothing. But we also pay for the I/O: $0.10 per 1 million requests. I cannot estimate how much this will cost us for the whole year. No idea. Let us hope that granted we will do mostly development in the databases we will not exceed $100. I am really guessing here.

Next is the backup fee. Amazon say: “For example, if you have an active DB Instance with 10GB-month of provisioned database storage, we will provide up to 10GB/month of backup storage at no additional charge. Based upon our experience as database administrators, the vast majority of databases require less raw storage for a backup than for the primary data set, meaning that most customers will never pay for backup storage.” Great, backups are for free 🙂

Finally, we have the data transfer fees. If we would like to load in some initial data it will cost us $0.10 per GB. Neglectful. For data out, the first 1GB per month is free, then it is $0.15 per GB for up to 10GB and if more it is also about $0.10 per GB/month.

So altogether, less than $1000 per year for one database. Not bad. Or?

And the estimated calculations are for a period of one year. If we prepay for 3 years, then the annual price will drop to about $800 which is European money makes about 50€ per month for a single database which is an extremely attractive price! Especially granted that the Oracle license fee is also part of this 50 euro/month.

Let us ask for second opinion and visit the Website Magazine’s article Amazon RDS for Oracle Database written by Peter Prestipino. I do agree 100% with Peter: “Deploying and managing databases is complicated, time-consuming, and expensive – it’s of the most complex activities in IT and demand is high for those skilled and knowledgeable enough to get it working and keep it working. The complexity and cost can be magnified when working with more “robust” databases. That may be coming to a fast end.”

If you would like to find out what database privileges you will have in the Amazon RDS, check Jeremiah Wilton’s article Amazon RDS for Oracle: First Impressions.

I do not know if it will be raining savings from the clouds but the “License Included” pricing model is something very attractive and worth taking seriously.

A friend in need is a friend to avoid: should I capture sql plan baselines?

In Database tuning, DBA, Oracle database on June 13, 2011 at 23:32

A friend in need is a friend indeed. I am talking about SPM, SQL Plan Management. Or should I rather say that a friend in need is a friend to avoid. Let us see.

Maxym Kharchenko wrote a very interesting note entitled How to screw up your neighbor. He says: Unbelievable! and considers his findings as a major bug.

So, the direct question is: Should we set optimizer_capture_sql_plan_baselines=true?

My answer is NO. Unless you run Oracle 12.1 and above where you could consider it. Or at least heavily patched 11.2.0.2 Oracle on Microsoft OS (Oracle on Windows is another absurd but that is another story). And I would like to explain why by giving you two reasons on why not to touch the default value.

Reason One: There is an Oracle Bug 9910484 called: SQL Plan Management Capture uses excessive space in SYSAUX. And excessive here means gigabytes! I have seen SYSAUX growing really a lot.

If you would like to recall what the parameter optimizer_capture_sql_plan_baselines is all about, please check the following article from Pythian called Wouldn’t “Automatic SQL Plan Management” be the 11g “killer app”. In short, let me quote the documentation: “SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time, and builds SQL plan baselines composed of a set of existing plans known to be efficient. The SQL plan baselines are then used to preserve performance of corresponding SQL statements, regardless of changes occurring in the system.”

In order to understand in detail how SPM works, the best link is this white paper from Oracle called SQL Plan Management in Oracle Database 11g.

The following “nice” table is from MOS: Reference Note [ID 567104.1]:

As of now, June 2011, by just looking at the bugs above (if running below 12g), I would say that the words locks, slowness and excessive space would be a very, very good indicators to back off and not to play the role of a cowboy DBA.

If the information above is still not enough convincing why you should stay with the default of optimizer_capture_sql_plan_baselines=false, then go on reading.

Reason Two: When doing manual SQL tuning, not all people might be aware that optimizer_capture_sql_plan_baselines has been set to true. In fact, some may be but not totally comprehend the behavior of Oracle in this special case. Their frustration can escalate rather quickly when they notice that recalculating statistics, adding an index, etc. does not change the execution plan and has no effect whatsoever. Not until when Oracle eventually evolves the execution plans and accepts them. Oracle calls this a “conservative plan selection strategy” as the CBO preferentially uses a tried and tested execution plan even if the new plan looks like it might perform much better.

This might have quite of an impact on the people doing manual tuning across the organization. If you have not yet read Maxym Kharchenko’s article How to screw up your neighbor, please have a look.

Note here that if a SQL profile recommendation made by the automatic SQL tuning task is implemented, the execution plan found by the SQL Tuning Task will be added as an accepted SQL plan baseline.

Interesting enough, only when the newer plan is proved to perform well will it be accepted for use. However, is cost the only criteria for an accepted plan? Have a look, this is output from

select * from DBA_SQL_PLAN_BASELINES where signature = '12258705699202968790':

Why wasn’t the one with lowest cost 31 evolved to an accepted one? Note that this is after running the Automatic SQL Tuning task. Obviously, the plan did not pass the performance criterion. Oracle needs a compound improvement ratio bigger than 1.5 as a new plan must be at least 1.5 times better than the current baseline to be evolved with verification.

Now, how is this compound improvement ratio calculated? I do not know the exact formula but what is taken into account are the ratios of Elapsed Time, CPU Time, Buffer Gets, Disk Reads, Direct Writes and Fetches. In his paper SQL Plan Management and Your Oracle 11g Upgrade, Mike Tefft supports SQL Plan Management. But looking at his slide 17, I wonder why Oracle fails the performance criterion:

A few more nails in the SQL plan basline’s coffin:

Sql Plan Baseline Not always created: 788853.1
ORA-600 when optimizer_capture_sql_plan_baselines=true Bug: 12319228
ORA-1403 when optimizer_capture_sql_plan_baselines=true Bug: 9157046
Automatic capture of sql plan baselines generates excessive redo: Bug 10386337
Zero plan loaded into spm from sql tuning set: Bug 10091254
Sql not being registered in the sql plan baseline: Bug 11077029

For me this is not a stable and most of all not a simple mechanism. Fixed plan, evolved plan, accepted plan, enabled plan, disabled plan… If it is up to me I will make it more simple and clear. And what is the story behind the way the compound improvement ratio is calculated? I have looked into 100s of “Evolve SQL Plan Baseline Report”s and all looks so ambiguous to me.

If you are still with me and need more fun click on Jed Walker’s article Making plans with Oracle 11g and not leaving them behind. Very good writing Jed! Going on a trip? Take the baselines with you 🙂

So, if select count(*) from dba_sql_plan_baselines; returns nothing you are doing OK on this front. If not, here is how do wipe away all the baselines:

Option 1: Bulk delete SQL plan baselines
Option 2: How to drop plans from SPM repository

But if you are still not convinced about the usage of optimizer_capture_sql_plan_baselines (true or false), then use the SQL Performance Analyzer, just like this:

And view the report:

The Overall Impact is 60% plus. Uups.. It is very much up to what you have in the SQL Tuning Set! This is a very important thing to remember.

One last thing. Oracle suggests to migrate stored outlines to SQL plan baselines! Oracle enables you to safely migrate from stored outlines to SQL plan baselines. After the migration, you can maintain the same plan stability you had using stored outlines while being able to utilize the more advanced features provided by the SQL Plan Management framework. For more information, see “Migrating Stored Outlines to SQL Plan Baselines”.

On the most common mistakes made by DBAs when writing CVs/Resumes

In DBA on June 8, 2011 at 00:05

Eleanor Roosevelt said: ““People are like tea bags. You find out how strong they are when you put them in hot water.” Same holds for DBAs.

Last week, I read an article in the Wall Street Journal called “Updating a Résumé for 2011”.

It mentions about the top 10 overused terms when writing CVs/Résumés: innovative, dynamic, motivated, extensive experience, results-oriented, proven track record, team player, fast-paced, problem solver, and entrepreneurial.

How true! Having read (or rather scanned as noted in the WSJ) hundreds of DBA resumes over past years, I must say that this is very close to the reality also in the database management world.

But first, what is the difference between a CV (Curriculum Vitae) and a Résumé? About.com and Answers.com say: “The primary differences between a resume and a curriculum vitae (CV) are the length, what is included and what each is used for. A resume is a one or two page summary of your skills, experience and education. While a resume is brief and concise – no more than a page or two, a Curriculum Vitae is a longer (at least two page) and more detailed synopsis.” I see this a bit differently: it is basically the same thing. Americans call it Resume, Europeans call it CV.

Now, what are the most common mistakes made by DBAs when writing CVs/Résumés?

1. Use of cliches: Check your CV if it contains some of the words/phrases listed above. Remove them! We all know that everyone is a team player with proven track record but only one finger hits Enter after typing shutdown immediate.

2. Overbrand yourself: too much information in the CV: No need to explain in several pages what you did for every single project. Nobody has time to read all that. Really! I understand everyone wants to mention about all his skills and knowledge but forget about including technologies you have used for only few weeks or even worst: used for years but mostly on PowerPoint.

3. Including technologies you do not master well: A major mistake is to add to the CV all possible IT software and hardware you can find in Google. I doubt very much the existence of a DBA who can administer all database brands, knows all operating systems, storage vendors and is also a programmer/developer. And here I would point a finger to some employers who demand too much in their job ads.

4. Writing about old stuff: One of the WSJ article’s comments says “The problem with all resumes is that they focus on the past, not on the future”. Absolutely! I always smile when I see in the CV words like Oracle 7, Oracle Parallel Server and Forms/Reports. As if somebody really cares of how well someone masters the Rule Based Optimizer, the proper setting for gc_file_to_locks or when to use the Snap to Grid feature instead of Anchoring.

5. Unbalanced DBA skills: Few years ago, I received a CV where it read “IT skills: Internet Explorer and Oracle”. This is an extreme example but from the CV one should get the idea (for example) if the person is Senior DBA or not. For all I have seen, a typical CV is so unbalanced (see 3. above) that after reading it I am rather perplexed and bewildered of how good the DBA is and what he is most strong at. Two examples: a person is a SQL Tuning expert who has never heard of SQL Profiles or has several years of EM experience but does not the difference between Database Control and Grid Control.

6. Mimic the job ad: Déjà vu, it is so ironic when you see in the CV all you want. The worst case is when people are so lazy that they even copy/paste from my job ad. I reject these candidates at once.

7. Using DBA jargon: The one who reads your CV is not necessarily with DBA background. Too many abbreviations without explanation in brackets might lead to the frustration of the one reading it and ignoring you without cause. Even the word DBA should be avoided and use Database Administrator instead. For many people outside IT the word DBA resembles a profession from a Harry Potter book. Literally!

8. Underestimating the achievements: In the very beginning section of your CV, you want your key skills to shine out at recruiters. Include on the top of the first page what you are best at, your most significant achievements, your top skills. One paragraph! Maximum 5 bullets. That might be your keys success for getting to the interview at least.

9. Photograph in the CV: Do not put a photograph unless the employer/job ad asks for one. You may be judged based on what you wear, your hear style, how you smile. Who knows.

10. Poor Grammar and Spelling: A very recent case: strongest skill was MSSSQL 🙂

All of the above is based on my experience and I know that DBA recruitment is often culture based. For example, in the US you cannot ask candidates about age and family status. But in Europe, these things can be at times quite important.

It is worth checking for second opinion. DBApool.com lists the 9 common mistakes in DBA resumes. It is a very good one indeed.

Another article called DBA resumes that make the cut says that “DBAs need to do more than just tell the truth” and the number one tip is “Name the same skills that the job advertisement does”. I am sorry but that is not how you get the DBA job. May be the interview but that is badly wrong advice.

You can google for generic mistakes in CVs. Opinions vary although the tendency is framed.

Question I am not able to answer: What is an overqualified DBA?

And finally, how do you rate this Oracle DBA resume from 1-5?

On gathering dictionary statistics: do I analyze the SYS schema?

In Database tuning, DBA, Grid Control, Oracle database on June 4, 2011 at 21:29

Attorneys specialize nowadays in neighbor’s-trees-blocking-your-view litigation, I try to specialize in fine-grain-database-tuning.

First you specialize in databases, then you narrow to Oracle, then you specialize in performance tuning. And then I narrow to certain types of tuning: I think automatic tuning is the future. But who knows, I have often been wrong before. People ask me: “Julian, are you getting lazy nowadays that you do not want to do the manual work?”

Anyway, you express opinion on a certain subject, some people take it for granted, some want to see a bulletproof one. So, how about this question: should we analyze the SYS schema? “Beats me” is the most common answer.

Rusted DBAs who still go memory lane 7.3.4 after a glass of whiskey will probably tell you no way! But most DBAs from the new school have already read 245051.1, where it clearly says: “In Oracle 10g collecting of data dictionary statistics is mandatory for CBO to work properly”.

1. Dictionary Statistics. In another MOS note, 457926.1, we read the following: “Gather_schema_stats gathers statistics for objects owned by the SYS Schema. We recommend gathering statistics for the SYS schema, specifically if you are using Oracle APPS.” Here is more:

2- SQL Performance Analyzer. Question is, is this enough convincing? Granted that all we have seen documented and praised, implemented in reality is not always a one-to-one match. Right?

Solution is to use the SQL Performance Analyzer with a SQL Tuning set containing only system queries. Delete the data dictionary statistics first, flush the shared pool and analyze the SYS schema without publishing the statistics. I did it and here is the report (click on the picture to enlarge the image):

22% improvement is nothing great but at least it clearly shows that you better analyze SYS than not. Of course, with a different SQL set you might have a different result. Try for yourself! Look at the top 10 statements affecting the performance, I witnessed the following:

Have a look also at the following good articles: Data Dictionary Statistics Gathering and
Gathering System Statistics in Oracle.

3. How and when to gather SYS stats. Granted we agree that we have to analyze SYS, it would be quite natural to ask: when and how often? Usually, I would do this after big changes to the database. Like creation of a new schema with lots of objects, before/after big datapump operations, etc. Check for example: Poor Performance With DataPump Export on Large Databases [ID 473423.1]

Or even better have it as a database job. If you check “Oracle Sun Database Machine Application Best Practices for Data Warehousing” [ID 1094934.1], you will note the following:

“When to gather dictionary statistics: Statistics on the dictionary tables will be maintained via the automatic statistics gathering job run during the nightly maintenance window. If you choose to switch off the automatic statistics gathering job for your main application schema consider leaving it on for the dictionary tables. You can do this by changing the value of AUTOSTATS_TARGET to ORACLE instead of AUTO using the procedure DBMS_STATS.SET_GLOBAL_PREF.

EXEC DBMS_STATS.SET_GLOBAL_PREFS(AUTOSTATS_TARGET,’ORACLE’);

If you choose to switch off the auto job complete you will have to maintain the dictionary statistics manually using the GATHER_DICTIONARY_STATS procedure.”

By default, automatic optimizer statistics collection calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. By default GATHER_SYS is true (?).

4. Fixed tables’ statistics. Neil Johnson wrote a good one on When to Gather Fixed Object Optimiser Statistics. The example is enough convincing to me at least.

Fixed Objects Statistics Considerations [ID 798257.1] suggests why those statistics are needed:

1. Missing or bad statistics on the X$ / fixed tables can lead to performance degradation or hangs. Various X$ views are protected by latches and as a result can be very expensive to query in large / busy systems.
2. Most commonly this issue is seen on the underlying X$ tables for DBA_EXTENTS, V$ACCESS, V$RMAN_BACKUP_JOB_DETAILS, and V$RMAN_STATUS, but any fixed table protected through latching can experience this.
3. Another commonly seen symptom is extreme TEMP space usage driven by poor plans against the fixed tables.
4. RMAN, Data Guard, Streams, and Grid Control make heavy usage of the fixed tables through the DBA/V$ views and so can often bear the brunt of performance issues.

5. Locked statistics in the Data Dictionary. Starting 11.2.0.2, statistics for some objects in the Dtata Dictionary are locked by default with a likely goal of preserving access paths for those queries. And in fact, not only in the SYS schema! You can modify the following SQL to find out what is locked in your database by default:

select TABLE_NAME, LAST_ANALYZED, STATTYPE_LOCKED 
from DBA_TAB_STATISTICS
where STATTYPE_LOCKED is not null and owner='SYS'
/

Let me finish with a screenshot of the statements which Oracle considered unsupported during my SQL Performance Analyzer test:

Do you know a DBA specialized in Oracle bugs?

GoldenGate enters the Oracle database

In DBA, Golden Gate, MySQL, Oracle database, Replication, Sybase on June 1, 2011 at 00:02

A friend of mine claims that GoldenGate is a product that is supposed to be used and exist outside the Oracle database. And if you run Oracle on a version below 11.2.0.2, the following query for GOLDENGATE objects will probably return no rows selected. But here is what happens with Oracle 11.2.0.2:

Interesting, right ?

Note that recently Oracle GoldenGate 11.1.1.1 was released. For Oracle GoldenGate on Oracle versions 10gR2 and higher, Extract now manages log retention by default. With this new feature, the documentation says you should be aware of the following:

1. The upgrade will automatically register each of your Extract processes with the database, which will start retaining the logs based on the current recovery requirements for each process.

2. An Oracle Streams Capture process will be created for each Extract process and will have the same name as that process. These Capture processes are non-functional and can operate concurrently with other local Oracle Streams installations.

3. In order for the upgrade to succeed (on 11.2.0.2), you will need to assign the Extract user some additional privileges.

Note first that you need to commit after executing the procedure in the dbms_goldengate_auth package:

SQL> exec dbms_goldengate_auth.grant_admin_privilege('GGATE');
PL/SQL procedure successfully completed.
SQL> commit work;
Commit complete.

Note additionally that the privilege_type can be CAPTURE, APPLY or BOTH. Both (= *) is the default. If you prefer that the GGATE user is granted also the select_catalog_role, you should run the command like this:

SQL> exec dbms_goldengate_auth.grant_admin_privilege('GGATE','*',TRUE);
PL/SQL procedure successfully completed.
SQL> commit work;
Commit complete.

Oracle GoldenGate version 11.1.1.1 contains improved sequence support. To enable this support, you must install some database procedures that support the new FLUSH SEQUENCE command. I recommend that for details you read Samuel Oleleh’s Oracle Goldengate 11.1.1.1 Sequence Support article.

The (Oracle database related) new features of GoldenGate 11.1.1.1 are very well described in the following blob entries:

1. Oracle GoldenGate version 11.1.1.1 new features by Samuel Oleleh.
2. Oracle GoldenGate version 11.1.1.1 released by Miladin Modrakovic.

For all the MySQL users (are there any?), I will note that there is a new ALTLOGDEST option that was added to TRANLOGOPTIONS to specify the location of the MySQL log index file. This value overrides the database default location. Extract reads the log index file to find the binary log file that it needs to read.

For the Sybase users I will mention that the range of values for DBOPTIONS TDSPACKETSIZE has been changed. Additionally, two formerly internal parameters are now available for use in a TRANLOGOPTIONS statement:
– TRANLOGOPTIONS MAXREADSIZE specifies how many records Extract reads from the transaction log at one time. It can be used to improve performance.
– TRANLOGOPTIONS READQUEUESIZE specifies the internal queue size, in bytes, for transaction data. It can be increased to improve performance.

For Oracle GoldenGate installations please check:

1. Pythian’s Oracle GoldenGate Installation, Part 1
2. Pythian’s Oracle GoldenGate Installation, Part 2

As I hinted above MySQL and GoldenGate do not in my opinion go hand in hand. But I would briefly like to mention that Oracle GoldenGate can be used together with Active Data Guard to achieve almost zero planned and/or non-planned down time:

I will soon write more on live upgrades, zero downtime and 100% SLAs 🙂

P.S. As of today, Google returns only two pages with results for dba_goldengate_privileges, try for yourself to see if things have already changed: