Oracle related rants (and lots of off-topic stuff)…
In today’s video we demonstrate using expressions in initialization parameters, introduced in Oracle database 21c.
The video is based on this article.
The star of today’s video is Deiby Gómez, who is a fellow Oracle ACE Director, and was kind enough to take me sightseeing when I visited Guatemala for a conference.
Cheers
Tim…
“what’s the expectation a DBA should do when “something is slow” – but HW and SW is ok and DBA is not responsible (and must not manipulate) schema or statement?”
The thread includes some suggestions, but I want to come at it from a different angle…
I have a lot of sympathy for this situation. In my current role I look after a lot of databases that sit behind 3rd party applications. In many cases, that means we can’t change the code. We can’t play around with the contents of the schema, for fear of losing support. Our hands are, to a greater or lesser extent, tied. This presents an interesting problem because you’re damned if you do, and damned if you don’t.
My attitude to databases behind 3rd party applications might surprise you. I try to stick closely to what the application vendor recommends, even if I know it to be stupid. Why?
At this point I expect someone to say, “But Tim, you’re supposed to be good at this stuff. I would have expected more from you!” What’s my response? Walk a mile in my shoes. If you have hours to obsess over one system to make it perfect, great for you, just don’t come and work here! 🙂
When you buy a product it should be fit for purchase. Part of that is the vendor should be able to give adequate guidance on getting the most out of their system. Also, when you purchase a system, you should be doing your due diligence. Having written this, I understand it is a complete fiction. Why?
Even when it’s not a 3rd party app, you can’t always fix it easily. As an application grows it gets significantly harder to refactor pieces of it. Sure, you can change that little bit, but what are the knock-on effects? If you have good regression tests, great. If not, it can be a risky endeavour. I mentioned in another post it took about six months to find some of the performance issues brought about by an upgrade from 11.2.0.4 to 12.1.0.2. It was stuff that had been missed during the testing and only ran once a year. The bigger the application, the easier it is for something to hide.
Even if you can refactor, do you have the time and resources to do it? Applications aren’t static. Sure, there is pressure to fix performance problems, but there is also pressure to add new functionality. What’s best for one group of people may not be good for others.
Martin’s tweet exemplifies the misunderstanding most managers (not him) have about databases. There’s rarely a magic button you can press that fixes performance problems. The vast majority of the time it comes down to bad database design and/or bad SQL. When you can’t change either, you don’t have much choice other than to wait for the next application patch/upgrade from the vendor that might fix it, or throw hardware at it on the DB layer or the App layer, depending where the problem is. {Insert “Run it on Exadata” comment here!} I really don’t think a lot of people outside the database world understand this!
Oracle has a lot of goodies that can be used to mitigate terrible applications without having to touch them directly. Update: I’m not trying to make out all these features don’t come with their own set of issues too. 🙂
But the real solution is to do proper database design, write good SQL, and write good applications on top of that. Performance is a development issue, and we are all developers now. Yes, even you DBAs. 🙂
Anyway, just some thoughts on the situation Martin found himself in, and I find myself in all the time. 🙂
Cheers
Tim… (Chief Presser of Magic Buttons)
I’ve also talked about how the world has changed for PL/SQL and SQL developers.
The reactions I’ve received following all these posts, as well as the comments about Kellyn’s post, can be broken down into the following categories.
I think from my previous posts you will know I’m in the Pragmatic category. The type of work I did 20 years ago, whilst calling myself a DBA, is drastically different to what I do today. In 10 years time my role will be totally different, but I will probably still call myself a DBA (Do Bloody Anything).
At this point someone will chip in with, “We will never move our databases to the cloud so this doesn’t affect me!” This is naive for a couple of reasons. First, you will move *some* things to the cloud. It will happen! Second, the changes to the DBA role will happen regardless of the cloud. Automation is the thing that is altering the lives of DBAs and SysAdmins. Cloud is just another form of automation. If they haven’t already, your company will have to get on board with automation or die. In addition, the products you use will evolve over time, as they have been for years.
You can look at all this from a couple of angles.
Take your pick… 🙂
Cheers
Tim…
PS. It will be interesting to see what Oracle actually come up with at the end of all this… 🙂
Update: Loïc Lefèvre just sent me a link to this article, which is pretty cool!
Update 2: You might want to read this from Thomas LaRock from the SQL Server camp. 🙂
I’ve been doing Oracle database development and DBA work for nearly 22 years. In that time the job of an Oracle DBA has changed a lot. Despite this, having people who understand what is going on below the surface has remained in demand. If you keep trying to be an old-school DBA you are going to find yourself in a very dark place very quickly. If you keep your ear to the ground and try to move with the times there will always be a role for you. Good people always land on their feet.
The way you move depends on your interests and the demands of your company. Some will move closer to an architecture role related to the infrastructure, which is pretty important when dealing with the cloud services, docker, DevOps, continuous deployment etc. Some will align themselves more closely to development, which is of greater importance in the new world. Others will completely move away from RDBMs into other technologies related to data or elsewhere.
The next question is typically, “When?” I’m not saying we should all run around screaming and pulling out our hair, but we should also not turn a blind eye to the way the world is changing. I can pretty much guarantee there will be comments by people telling me I’m wrong and the DBA role will exist forever, to which I will reply, “Denial is not just a river in Africa!” 🙂
Some companies, especially those that are more development led, will transform rapidly. DevOps, continuous deployment and technologies like Docker have the power to transform a company rapidly, whether on-prem or on the cloud. In all cases, someone needs to help build and maintain the layers that contain databases and app servers, and that could be you, but I don’t see the same volume of work we currently have, because if done properly it should be a build once, deploy many approach. For some companies that are into automation, this is already a reality. Very soon it will be true for much more of us.
Some companies will be slow in moving forward and their staff will wonder what all the fuss is about, until they apply for their next job and realise there isn’t one for them!
Having said all that, I did an “unconference” session at OOW in 2007 called “The Oracle DBA… A dying breed?” and we’re still here now. The important point is you need to take responsibility and shape your own destiny. Don’t sit idly by an watch the world take your job. It’s easier than ever to learn new things and prepare for the future, so do it! 🙂
Cheers
Tim…
The title is of course inspired by “They took our jobs!” from South Park.
I’ve been doing some cloud-related talks recently and a pretty regular question is, “How is this going to affect my job as a [DBA | Sysadmin]?”
My answers usually include some of the following points.
There is no doubt the cloud will affect what we as DBAs and system administrators do, but our jobs have been constantly evolving over the last couple of decades I’ve been involved in IT. As Francisco said recently, “These days, DBA stands for Database Architect”, which I think is kind-of true. A decade ago I just did Oracle databases. Now I do Oracle, SQL Server and MySQL databases. I look after WebLogic, Tomcat, IIS and Apache App/Web servers. I’m helping to set up load balancers. I get involved in infrastructure projects for applications and middleware. It’s not that I’m awesome at any of this stuff, but as a DBA and/or system administrator you get exposed to so much, which makes you an ideal resource to help with this architectural stuff.
If you think a DBA just installs Oracle, creates databases and checks backups, your job will be gone soon. If you are a system administrator that just installs operating systems and does patches, your job will be gone soon. These are trivial tasks that anyone can learn in a few weeks, so you should hardly be surprised they can be automated out of existence. If instead you concentrate on the skills where you add true value to your company, you will be in demand for a long time!
I know it’s a bit of a random post, but I hope you can see where I’m coming from! 🙂
Cheers
Tim…
I was a little surprised by the reaction I got to my previous post on this subject. A number of people commented about the problems with automation and many pointed to this very appropriate comic on the subject.
There are one of two conclusions I can draw from this.
I’m really hoping the answer is option 1, because I think it would be really sad if being a DBA has degenerated to the point where people spend their whole life doing tasks that could be easily scripted.
So what do I mean when I speak about automation? Most of the time I’m talking about basic scripting. Let’s take and example I went through recently, which involved cloning a database to refresh a test system from production. What did this process entail?
None of those tasks are difficult. It requires only a basic knowledge of shell scripting to allow me to start a single shell script and come back later to see my newly refreshed test environment.
What’s the alternative? I perform all the same tasks individually, but have to sit there waiting for each step to finish before I can move on to the next. No doubt, during this time I will be distracted by phone calls or colleagues asking me questions, which drastically increases the risk of human error.
When I talk about automation, I’m not talking about some Earth shattering AI system. I’m talking about scripting basic tasks to make myself more efficient.
At times you have to draw a line. There is no point making your automation too clever because it just becomes a rod for your own back. I’m a DBA, not a software house. This is what people are really warning about, which I did not really make clear in my first post. If something is liable to change each time you do it, you are better having a written procedure to work from, reminding you of the necessary steps and how to determine what needs to be done. You can’t become a slave to automation.
Cheers
Tim…
The DBA role can sometimes feel like a mix of detective, crime writer and DBA all thrown together. What do I mean by that? When you hit some problems you have to play detective, trying to find clues to what is going on. Once you’ve started to gather clues, you have to play crime writer and put them together to form a coherent story that explains what is going on. Once you have the outline of your crime story you can start looking at the facts again and see if they fit with your story. If they do, your story may just be correct. If they don’t, you probably need to check the accuracy of the facts and do some rewriting of the story until the two things fit together. Once things seem to fit, you can then get busy trying to arrest the villain, or fix the problem. 🙂 This process is often necessary as part of root cause analysis.
I’ll use a recent case to highlight what I mean. I’m going to simplify things a little or the post will be too long. 🙂
We recently had some intermittent, but very severe performance problems on a system, resulting in the application effectively hanging for some time. As usual, it’s the DBA job to prove it’s not the fault of the database. 🙂 A bit of detective work came up with the following facts:
So let’s switch to crime writer and think of some stories that could explain this.
High load on the application causes lots of database connections, eventually swamping the servers.
Does it fit the facts? The reduction in free memory, eventually leading to swap could be caused by an increase in numbers of processes on the servers. Lots of processes all doing I/O on the server will probably increase the amount of I/O waits as they fight with each other for the disks. That sounds promising.
How do we confirm it? Use the AWR reports to compare the load profiles (amongst other things) of the system compared to other times in the day, days of the week, same time and day last week etc.
Conclusion: The load on the system was not substantially different from other times when the system was running fine. The story doesn’t fit together.
Slow I/O is reducing response time. As new requests come in, the backlog of work is getting bigger…
Does it fit the facts? In checking out the AWR reports looking for confirmation of the previous story we noticed the average times of certain physical I/O operations were about 30ms. Normally the average is <1ms. Checking the event histogram section of the AWR report, we could see a very wide spread of timings for physical I/O operations, including some in excess of 1 second. After a few minutes we started to see Warning “aiowait timed out x times” in alert.log [ID 222989.1] messages in the alert log, suggesting some of those I/O events were taking longer than 10 minutes. 🙁
Now we have a new fact to add to the previous list:
We know the application layer of this application is not too clever. If a request comes and there is an idle application process available, it will be reused. If no idle application process is available, a new one is spawned to process the request. The only limit on this is the max number of processes for that user at the OS level.
If the disk I/O is slow, application requests will take a long time to complete. If application requests are taking a long time, the likelihood of new application requests finding an idle application process to reuse is reduced, so the incidence of new processes being spawned increases. As new processes are spawned, we see a gradual increase in memory usage, leading eventually to swap. At the same time, we see an increase in database connections, which require processes, which use memory… You can see where this is going…
Conclusion: This story does seem to fit the facts. Essentially, memory, swap and the number of processes are the symptom, not the cause of the problem. The root cause could be the I/O performance issue.
Now I said this was an intermittent problem. The next time it occurred I was prepared and watched events unfold using Luca Canali’s nifty Latency Heat Map Visualization, which prompted this quick post. The result of this was we had pretty solid evidence to suggest the problems we were experiencing were nothing to do with the application or database, but were as a result of poor I/O performance. The baton was passed to the system administrators, network and storage team to try and bottom out the problem. That process is still ongoing, but smoking gun seems to be the storage network layer.
Over time you build up a level of experience that allows you to spot patterns and quickly get to the root of problems. To observers it can seem almost mystical, which is why us grunt DBAs look at those database performance gurus with awe, but it all comes down to root cause analysis. The more you do this stuff, the better you get at it. Stop doing it for a while and you get rusty.
Detective? Crime Writer? DBA? Which are you? You should be all three. 🙂
Cheers
Tim…
Hopefully the title got your attention. Of course it could have read, “Real Linux Sysadmins use Cobbler and Puppet…”, or any number of comparable statements and products. The point being, there is a gradual evolution in the way we approach tasks and if we don’t move with them we marginalize ourselves to the point where we are so unproductive we cease to be of use.
A few years ago I was doing a lot of Linux installations and I got sick of running around with CDs, so started doing network installations to save time. I’ve been doing loads of installs on VMs at home recently, so I started doing PXE Network Installations, which saved me even more time. As a result of the article I wrote about that, Frits Hoogland pointed me in the direction of Cobbler, which makes PXE installations real easy (once you get to grips with it). I’m not a sysadmin, so why do I care? Even when I’m installing and running a handful of VMs at home I can see productivity gains by using some of these tools. Imagine the impact in a data-center!
So back to Grid Control. Does anyone remember the days when you kept a “tail -f” on your alert log? At one site I used to have a CDE workspace on an X station just running tails. Then the number of instances got too big, so I used to scan through the alert logs each day to look for issues. The next step was to use shell scripts to check for errors and mail me. This was a pain at one site where I was using Solaris, HP-UX and Windows, which meant I needed three solutions. Then the Oracle 9i Enterprise Manager with the Management Server came into my life. All of a sudden it could manage my alert logs and I could assume everything was fine ( 🙂 ) unless I got a notification email. This feature alone sold me on the 9i management server.
Back then, being a DBA and admitting using Enterprise Manager was a little like announcing to the world you were into cross dressing. 🙂 Time has moved on, the product name has changed and so has its functionality, but essentially it’s still doing the same thing, which is reducing the effort needed to manage databases (and other things). The difference is that rather than managing 40 instances, teams are now managing thousands of instances.
Of course, none of this is new. I guess it’s just been brought into focus by a few things that have happened to me recently, like the PXE/Cobbler thing, the recent demise of my Grid Control VM at home and the constant talk of cloud computing and SaaS etc.
Specialists and performance consultants have the time to obsess over minute detail. The day-to-day DBAs and sysadmins have to churn through work at a pace, with reliable and reproducible results. Failing to embrace tools, whatever they are, to aid this is career suicide.
Cheers
Tim…
I’ve updated the site notes for my OpenWorld unconference session. If you fancy having a look, it’s on the Oracle Wiki.
It’s quite hard to summarize the conversation, but I think I got the gist of it down. Remember, the notes are trying to convey the opinions of the people present. I’m not saying this was a representative sample of people, but the opinions and attititudes was certainly quite interesting to me.
Cheers
Tim…
Update: The Oracle Wiki page was removed.
Rant Alert. The following is an unreasoned attack on the IT community in order to vent my frustration. I’m not claiming it makes any sense or it’s factually correct. It’s just how I feel today. Maybe I’ll feel different tomorrow…
I can’t help feeling that companies like Oracle are doing the IT world a major disservice by trying to make out that their products are easy to use. I have a quick newsflash… They are not!
This post is really a response to two things:
From a work perspective, the mass exodus of people from my current company has left me having to deal with bits of technology that aren’t really my bag. It gets doubly annoying when I’m having to use bad support services to help me do really basic tasks. If software and hardware vendors were honest and made customers aware that they would need trained professionals to deal with this crap, perhaps people like me wouldn’t be left fumbling in the dark, trying to pick up the pieces.
From the Oracle forum side of things, I’ve really noticed a shift over the last few years and I’ve written about it before. The same type of questions are being asked as they always were. The difference is that in the past these questions were being asked by people trying to learn the technology. Now they seem to come from people who are employed as DBAs and developers by companies. I don’t believe the intellectual capacities of people have dropped over the years. I just think companies are employing under-skilled people to save money, or expecting people to cover roles they are not qualified to do. You wouldn’t let an electrician fix your plumbing, so why would you let this happen?
I don’t claim to know the answers, but I can see that the constant barrage of “point-and-click”, “intuitive” and “self-tuning” marketing messages are leading people to believe they don’t need qualified staff, and the result is a whole bunch of people asking how to recover their production databases from incomplete backups.
IT is getting more complicated and the range of skills needed in a company is getting bigger by the year. Companies need to be made to understand this or they will constantly be finding themselves in the shit!
Cheers
Tim…
