Recently one of my customers asked, what would happen if we had SQL Profile set up for a particular query to make sure it uses the correct execution plan, as well as an existing SQL Plan Baseline for the same query? What would happen if these two features would like the SQL statement to use different execution plans? Which one would win? I hesitated to answer immediately, although the answer seams obvious now. Look how I came to the answer!

AWR report is a great source of aggregated information on top activities happening in our databases. I use data collected in AWR quite often, and obviously the easiest way of getting the data out from the AWR is by running the AWR report. In most cases that’s not an issue, but there are certain scenarios when it hides the information one is looking for, just because of how it’s designed.

This blog post was originally released on Pythian blog

UKOUG Tech13 was great! Not only because of the amount of interesting presentations to choose from, but also because of the surrounding events. I felt this conference was quite different from previous UKOUG Oracle Technology & E-Business Suite events in Birmingham (I’ve attended six previous events so you can trust me when I say that.) I decided to take a few notes about my experience at the conference to reveal what made it such a great event to attend.

This blog post was originally released on Pythian blog

How to download patches from My Oracle Support (MOS) directly to the server? This has bothered me since the ftp access was closed a few years ago. Of course, I’ve been given some options by Oracle, like, I could access MOS from the server using a browser (probably from a VNC desktop - thank you very much), or I could look up the patches on my workstation to download the WGET script from MOS, which I uploaded to the server, adjusted with the username and the password of my MOS account and then started the downloads. Not too convenient, is it?

Then, back in 2009 my teammate John Piwowar published a blog post on [Retrieving Oracle patches with wget(http://only4left.jpiwowar.com/2009/02/retrieving-oracle-patches-with-wget). This eliminated the need to upload the wget script from MOS to the server and I only had to get the URLs of the patches and pass them to a shell function. While this was so much easier, I still needed to open the browser to find those URLs.

I think it’s time to get rid of browser dependency. So I’ve written a shell script [getMOSPatch.sh(http://bit.ly/getMOSPatch) that can be used to download patches directly to the server using only the patch number.

This blog post was originally released on Pythian blog

Sometimes it’s useful to check how performance of a SQL statement changes over time. The diagnostic pack features provide some really useful information to answer these questions. The data is there, but it not always easy to retrieve it, especially if you want to see how the performance changes over time. I’ve been using three really simple scripts to retrieve this information from the AWR. These scripts help me answer the following questions:

• How does the performance of a particular SQL change over time?
• How do wait times of a particular wait event change over time?
• How does a particular statistic change over time?