Many years ago, when I was learning Oracle Database Administration, I used to study very hard. I had a very good instructor. He taught me all the fundamentals well. I even studied for Oracle Certification & passed the exam with flying colors.
In spite of all that, I always used to feel that I am not ready for the DBA job. I was not confident. I am pretty sure many of you have had this same feeling.
So What do you think is the problem here?
The problem is not that Oracle is one of most sophisticated, complex software in the world. It is simply because when you read so much, you would not know what’s important and what’s not important in the context of doing the Oracle DBA job actually.
In other words, you must focus on the few Oracle topics that are really important for the job.
So what are these important things?
I conducted a short survey asking Oracle DBA aspirants what’s the single most challenging thing you face in learning and becoming Oracle DBA. The survey is still open.
17% of the people said that they need real-world exposure.
If you have different opinion, let me know by taking this survey.
Want proof. Here you go.
Let’s get started and learn about one such important, real-world scenario.
Real-World Scenario: Why is the query running slow all of a sudden?
I have never been in an interview or taken any interview without talking about this one.
Let’s look at the problem in detail.
When you become an Oracle DBA, one of your main responsibilities will be monitoring production databases. At the very least, once every week, you will be wondering why some SQL query that was running fine the day before is running slowly on that day.
There could be many reasons behind a query’s behavior change.
Reasons why your query may be running slow
- Statistics was collected for the tables used in the query.
- The database environment or configuration in which the query is running has changed. May be somebody enabled auto degree of parallelism or a parameter that impacts the way optimizer work was modified. For example, OPTIMIZER_INDEX_CACHING.
- A lot of data was loaded into the tables used in the query.
- An underlying storage issue causing disk I/O operations to be slow.
Steps to troubleshoot this problem
- Almost always, the first thing, I check is whether the execution plan has changed for the query. If the plan has changed, I look at the differences between the old plan and the new plan. If I see too many full table scans in the new execution plan, I will quickly put a SQL Profile to force the old plan and see if the performance of the query improves. If not, I will drop the profile.
- The next step is to look at the details like the number of physical reads per query execution, the number of logical reads per execution, the number of rows returned per execution etc. I pull all this information from AWR snapshot data (dba_hist_sqlstats). If I see increasing trend in any of these metrics, it could just mean that the query is pulling more data than before. If these numbers are still the same, I will check the average service times of Disk IOPS and the total number of IOPS etc.
- If I still don’t find root cause by this time, I start looking into wait events specifically for that query, which can be retrieved from v$session and Active Session History (v$active_session_history).
- At this point, if I absolutely don’t find anything wrong with the query, it might help checking the database as a whole. I would look at the Top 5 wait events from the time when the query was run and go from there.
I purposefully didn’t get into a lot of details here. Because the purpose of this article to give you the big picture of the scenario.
I would love to help you on more topics like this. But for that I need to know what you’re struggling with.
What’s the single most challenging thing you face right now in learning Oracle Database Administration? Please write your answers as comments below.
Alternatively, you can take this survey – https://www.surveymonkey.com/r/FCVN3DZ