As I’ve mentioned before, I’ve kinda ignored the fact that any operating system other than Linux (specifically Oracle Linux) exists. It’s quite easy to do when you are working with Oracle products and you get to choose your own environment.
As a vague nod to the fact that Windows does actually exist, I’ve finally got round to updating my Windows virtual RAC article.
Windows 2008 is an unusual operating system in some respects. The RAC installation is pretty simple really, but finding some of the config dialogs is a complete nightmare. Chains of menus, dialogs, buttons and hyperlinks to get you to the dialog you need. What’s worse, some of the menus are hidden unless you remember to “Alt” or “Alt+N”. Crazy! If I was using Windows on a regular basis I think I would just memorize all the dialog program names and start them directly from the Run menu. It’s got to be easier than traversing that nightmare. I remember when Windows was considered the easy option. It doesn’t feel like the case anymore.
At page 189 of TOP I wrote the following piece of text:
In summary, with the initialization parameter optimizer_secure_view_merging set to TRUE, the query optimizer checks whether view merging could lead to security issues. If this is the case, no view merging will be performed, and performance could be suboptimal as a result. For this reason, if you are not using views for security purposes, it is better to set this initialization parameter to FALSE.
What I didn’t consider when I wrote it, it is the implication of predicate move-around related to Virtual Private Database (VPD). In fact, as described in the documentation, that parameter controls view merging as well as predicate move-around.
Many Oracle DBA’s are probably familiar with what Optimizer trace files are and likely know how to create them. When I say “Optimizer trace” more than likely you think of event 10053, right? SQL code like this probably is familiar then:
alter session set tracefile_identifier='MY_10053'; alter session set events '10053 trace name context forever'; select /* hard parse comment */ * from emp where ename = 'SCOTT'; alter session set events '10053 trace name context off';
In 11g, a new diagnostic events infrastructure was implemented and there are various levels of debug output that you can control for sql compilation. ORADEBUG shows us the hierarchy.
As my loyal readers will know, I have been a big (maybe BIG) fan of the SQL Monitor Report since it’s introduction in 11g. It would not surprise me if I have looked at over 1000 SQL Monitor Reports in the past 4+ years — so I’m pretty familiar with these bad boys. Since I find them so valuable (and many customers are now upgrading to 11g), I’ve decided to do a deep dive into the SQL Monitor Report at both Oracle OpenWorld 2011 in October and the UKOUG in December. I think I have some pretty interesting and educational examples, but for anyone willing to share Active SQL Monitor Reports from their system, I thought I would extend the possibility to have it publicly discussed at either one of these sessions (or even a future blog post). Sound cool?
A reader of this blog, Paresh, asked me how I was able to find out the logic behind ITL waits without having access to Oracle code. My reply was: I wrote a test case that reproduce ITL waits and a piece of code that monitors them.
Since other readers might be interested, here is the shell script I wrote. Notice that it takes four parameters as input: user name, password, SID, and how long it has to wait in the monitoring phase.
In recent releases Oracle has silently changed the behavior of ITL waits. The aim of this post it to describe what has changed and why. But, first of all, let’s review some essential concepts about ITLs and ITL waits.
Interested Transaction List
The Oracle database engine locks the data modified by a transaction at the row level. To implement this feature every data block contains a list of all transactions that are modifying it. This list is commonly called interested transaction list (ITL). Its purpose is twofold. First, it is used to store information to identify a transaction as well as a reference to access the undo data associated to it. Second, it is referenced by every modified or locked row to indicate which transaction it is involved.
In this post I pointed out that I like to have a copy of the documentation in PDF format on my notebook. In the same post, and its comments, I also described how I generate the scripts I use to download the files. Recently I updated the scripts and, as a result, I thought to share them with you. So, below you find the CMD and SH scripts for the documentation related to 10.2, 11.1 and 11.2.
It looks like 18.104.22.168 has improved most of the original shortfalls of segment creation on demand that were present in 22.214.171.124.