jump to navigation

Friday Philosophy – Do good DBAs need PL/SQL Skills? March 1, 2013

Posted by mwidlake in Friday Philosophy, PL/SQL.
Tags: ,
trackback

This Friday Philosophy was prompted by a discusion between some OakTable people about did we think “good” DBAs should know PL/SQL? Not all the tricks, bulk processing, using all the built-ins, but able to write PL/SQL with cursor loops and some exception handling that could eg cycle thorough tables and archive off data or implement some logon trigger functionality.

My response was “that depends on the age of the DBA”.

If you had asked me that question 15 years ago I would have said Yes, a good DAB would and should know PL/SQL.
If you had asked me 10 years ago I would have said I’d hope they would and most DBAs I respected has some PL/SQL skills.
If you had asked me 5 years ago I would have sighed and had a little rant about how they should but the younger ones don’t and that is wrong.

But now, I would say that no, a good DBA does not need PL/SQL skills as so often they have so many other things they have to do and the tools to manage the database are somewhat better than they were. But inside I would still be thinking any DBA beyond their first year or two in the job would benefit from knowing the basics of PL/SQL.

It seems to me that a DBA now is generally expected to look after a very large number of instances, application servers, agents etc and all their time is taken doing the bread-and-butter tasks of backups and recoveries, patching, duplicating data, raising SRs (and that seems to take more and more time each SR every year), unlocking accounts, sorting out permissions…

Not only that but there is more and more to Oracle that a good DBA needs to understand as the technolgy gets more complex. Oracle has tried to make Oracle look after itself more but the result seems to be that for larger systems there are more moving parts to go wrong – and when they do it is often the DBA who has to sort it out. As an example, you no longer need to set several instance parameters to allocate memory to the components of the PGA and SGA. Just set the Memory Target. But if the system starts to throw odd errors about components being out of memory, the DBA needs to sort that out. They need to know about the dynamic memory adjust ments and check them out. They need to understand that certain components are now calculated in a different way, like the log buffer size. And probably they will have to revert back to the old parameters so they still need to know all about that!

So unless a DBA is an old hand and “grew up” with all this, they have no time to develop PL/SQL skills. Thus my response was “that depends on the age of the DBA”.

Should a good DBA know SQL?

Yes. I still see that as a given. Buttons, widgets and assistants will only get you so far.

About these ads

Comments»

1. jgarry - March 1, 2013

I grew up with other languages. I took a PL/1 class around 1983 but never worked in it.

20 years ago it was common to have to get some other language like C and call sql from it. DBA’s didn’t need it at all. PL and the pro languages were profit centers. Forms (both Oracle and third parties) were the future. http://groups.google.com/group/comp.databases.oracle/browse_thread/thread/306a0c7047d54108/e21db232ed830b56

15 years ago I thought I ought to learn PL/SQL, but shell scripting seemed good enough. I would rant on about how C was the worst app language, but one couldn’t really fight the rising tide of CS grads.

10 years ago I thought I ought to learn PL/SQL, but really, the developers were better than I would ever be, I didn’t really like it, but I seemed to be able to hack through the simple dba needs and muddle through developer questions, often by directing them towards tkyte. I thought I ought to learn perl, but shell skills helped me do the minimum there. Rant about java.

5 years ago I would strongly advise prospective DBA’s to learn PL/SQL but do as I say, not as I do.

I still need to learn perl, analytics, PL/SQL, yet somehow most of my work is yet in other languages (I’m doing about 90% development).

It’s site dependent. I think over the long term Oracle has convergently evolved towards the Sybase/MS ideal of a DBA being a super-developer, memorizing all sorts of procedures and engine-specific styles of doing things. So now we have dev DBAs And at the same time, the breadth of knowledge necessary has increased as you say.

There’re enough things going on for much specialization, so generalists become rare and desirable, and those who can both see the big picture and dive in to the bits when things go wrong are the most desirable of all. As I’ve been maintaining for decades, you want a wide variety in skills in the staff, young and experienced, bit-twiddlers and analysts.

We live, as the curse says, in interesting times.

2. Noons - March 2, 2013

It’s not as if learning PL/SQL is right up there with rocket propellant chemistry… It’s a very simple language, for the level of knowledge required of it nowadays from a Database Administrator.
But most of the admin packages using it are quite complex in specification and trying to use them on a command line is akin to suicide by a thousand cuts.
On the other hand, OEM is dirt easy to setup and provides a good front-end for those. Same for SQLDeveloper. As such, the need for “deep-end” skills in PL/SQL is not that pressing.

I would say the same for MSSQL, where even the most simple task equivalents in Oracle are handled by a complex layer of T-SQL packages. But they have SQLStudio, which makes most of it child’s play.

Although of course:it’s good to have a basic knowledge that allows one to dig deep if/when needed. But more than likely the subtleties of PL/SQL bulk-processing, object layer handling, typing and sub-typing and all such would be a waste of time for a Database Administrator to learn in depth. Good to know they are there, a few examples, and that’s all one needs.

There is one aspect to all this that I rarely see discussed: the NEED to train the next generation of administrators in all these and other basics AND generate in them the curiosity to dig deeper when it becomes needed. That is what I and others try to do at the SydneyOracleMeetup.
Although of course we hear lots of deriding comments by “experts” along the line of “it’s too light, I expected more depth in your presentation”. To which my answer invariably is: “Really? Well, pack up and go to the Oaktable meetings then- what are you doing here? Mind you, they might be happier to let you attend if you listened and helped the newbies a little more than you do now…”

Bottom line: seems to me that although all these languages may be needed and useful sometimes, the level of GUI tools nowadays makes it easy to manage things given a basic knowledge of the languages. And leaves us plenty of time for tinkering in C and to do capacity planning, strategic thinking and design – which is what I like to do and mostly get paid to!

mwidlake - March 2, 2013

“There is one aspect to all this that I rarely see discussed: the NEED to train the next generation of administrators in all these and other basics AND generate in them the curiosity to dig deeper when it becomes needed”

That is something I strongly agree with Noons. It seems that more and more the latest generation do not know enough about how computers and computer languages work, let alone are able to survive on the command line. I know the generation before me said the same, but it is partly because I agreed with them that I ended up moving more towards the DBA-type side of things – I wanted to understand how the database worked so that I could get the best out of it.

As a further consequence of my feeling that there is a lack of intro skills being offered to those new to the world of Oracle, I now offer far more “intro” talks that deep-dive talks to conferences. It’s great to talk about the details of something {and show how “smart” you are} but there is more of a need for “how to get going with X” talks where it is still practical and of use.

jgarry - March 6, 2013

Don’t you need a secret handshake to go to Oaktable meetings?

Noons - March 6, 2013

:D
Not really. Just a LOT more knowledge than what a newb is expected to have. And without that, there can be no new Oakies. And that is BAD!

3. Gary - March 2, 2013

Following on from Nuno’s comment, the point of admin packages is that they are APIs built to be called from an external client (SQL Developer, OEM….). Exporting database objects ? You used to have to run massively complex SQLs to work out the DDL for a table, its indexes, constraints etc. Now a quick DBMS_META.GET_DDL does it, and with SQL Developer you just point and click what you need.

mwidlake - March 2, 2013

As I said, the tools are getting better. And the initial question was should DBAs know PL/SQL and not be expert at it (as I expanded on in my post). I still think it is a distinct advantage – but no longer such a core skill.
Though you do need to now those PL/SQL packages/functions like DBMS_METADATA exist :-)

4. Narendra - March 2, 2013

As Joel (jgarry) mentioned above briefly, I will have to say it depends upon whether you are talking about “Development DBAs” or “Production DBAs”. A production DBA might actually be too busy, as you mentioned, in maintaining the estate of Oracle Databases (RAC, Dataguard etc.) to be able to benefit from knowing PL/SQL. Throw in all the Fusion and Weblogic Servers to maintain, she/he can be easily excused for not knowing PL/SQL. Just look at installation and maintenance of RAC. It, literally, needs a SysAdmin-and-DBA to look after. So I would have to say a production DBA can live without knowing PL/SQL.
As for “Development DBA”, I think there is no question here. She/he has to know PL/SQL because she/he would be (I assume) quite instrumental in guiding/influencing/designing the, what I call, “solutioning” side of the database. So a development DBA has to know PL/SQL.

5. Keiran Raine - March 4, 2013

Food for thought, there are other reasons to learn PL/SQL. I’m not a DBA and I taught myself PL/SQL while working with Oracle Clinical (you need a basic level of understanding).

A good use is if you want to write code against your Oracle DB but have the interface be language agnostic. I’ve yet to hear of a programming language that can’t talk to the database. WIth a bit of thought you can handle race conditions for concurrent requests and relatively complex processes can be handled through a simple:

select myfunc(params) from dual;

Admittedly you could write something in C(++) and write bindings for several languages but then the database connection is separate and, trust me, it’s far easier to write PL/SQL.

6. Friday Philosophy – Level of Presentations | Martin Widlake's Yet Another Oracle Blog - March 8, 2013

[…] FF is a bit of a follow-up to the one I posted last week on PL/SQL skills and a comment made by Noons on how much knowledge you need to be an OakTable […]

7. Wolfgang - March 8, 2013

I have a bit a problem with Narendra’s distinction between “development dba” and “production dba” and the conclusion that the production dba has too much else to do to be “bothered” with knowing PL/SQL. In my view both dbas ( if there is such a distinction in your environment ) should know some of the other’s world. Both should have at least a basic knowledge of the languages ( Java, PL/SQL, to name just two ) used by the applications ( developed in-house or bought ). The development dba maybe should have a deeper knowledge so that (s)he can help developers avoid problems stemming from db-agnosticism. But at the same time (s)he should know about the prod world ( backups, IO infrastructure, etc ) so (s)he can help with designing applications which fit easily with the production infrastructure. And for the prod dba, the more (s)he know about the languages used by the applications the better for troubleshooting, including performance problems.

8. Gaius - April 2, 2013

A good DBA will know C, PL/SQL, one of bash/csh and one of Perl/Python/Tcl. If all you’ve got is a hammer, everything looks like a nail…

Noons - April 3, 2013

That reminded me of java development… :)

9. sshdba - January 13, 2014

Part of my job as a Senior dba sometimes requires me to develop reporting tools for our end users. Knowledge of Shell scripting, pl/sql and some good ole VB can definetly help you with that.

I wouldn’t say it is mandatory for DBA’s to know PL/SQL, but to know the basics can certainly help you have an edge over the pesky developers who consider themselves as DB experts sometime :)) The one time i got a lecture from a developer on init trans parameter for performance improvement is one of the funniest moments of my career.

10. bernard polarski - September 25, 2014

bump on this

My daughter is studing her OCP, and the dad insist very loudly on the importance of SQL :”A database is made of data and actions, actions are expressed in SQL. “You want to become DBA? learn the langage of the beast if you want to master it.”

You know when you get old once you realize your are repeating the same stuff over and over while there is no alternative.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 171 other followers

%d bloggers like this: