Recently I was working on migrating Oracle 220.127.116.11 database running on Windows, to new hardware infrastructure on Linux. This Oracle version is with us for quite a long time, plus migration from Windows to Linux is quite common (fortunately in that direction…;)), so when I got an error while trying to use Active Duplicate feature to create a standby database, I was quite sure I’ll be able to quickly find the solution either in MOS or elsewhere. Indeed, I found several blog posts with exactly the same error, but solution mentioned there was not working for me.
Today I want to share with you another interesting example of solving performance problem which happened in the database used for Business Intelligence purposes. Users were complaining that the performance in kind of staging database (STGDB) was much better than the production one (PRDDB), even though the dataset was exactly the same.
How often did you have the situation when performance problem disappeared just after refreshing object statistics? Of course it is nice to solve the problem quickly and simply, but is always better to understand what has exactly happened.
The last blog post of Jeff Smith (18.1 Features: SQL Injection Detection) about little, but nice feature of SQL Developer detecting if your PL/SQL code might be vulnerable for SQL Injection, reminded me about the presentation I’ve delivered during Oracle Tutorials at CERN in 2013.
If you have performance problems after migrating your BI databases from 18.104.22.168 to 22.214.171.124 or any other performance issue, you should not underestimate SQL Plan Notes, which can point you to the right direction or just let you understand what exactly is going on with your queries. Please check below how SQL Plan Notes helped me to investigate problem I recently encountered.
Having a need to run the same set of commands on many machines is a very common task for IT engineers of all kind. If you want to do it efficiently and in a smart way (e.g. to run it only on machines with specific set of conditions), I have an example, that could be added to your toolbox.
Proper monitoring of backup jobs is one of the crucial elements in ensuring that your databases are well-enough protected against data loss. You can for example grep your backup logs, put some kind of alerting inside your backup scripts, but the most obvious method is just to use Oracle dictionary views.