Saturday we upgraded one of our Oracle databases in production from version 9 to 11g Release 2. Yes – you heard right! From Oracle 9i … Well it’s a long story, and I’ll leave that for now. What I do want to blog about is some of the changes in Oracle 11g. I’ve used Oracle 11g on my laptop for about 2 years bye now. This week, the first week with Oracle 11g in production, I realized that I haven’t really been digging into the new features of 11g. I ‘ve read some, but definitely not all.
Going from 9i to 11g was one thing. Another big change was going from RULE to COST based optimizer. Of course we’d done a lot of testing of our application running against 11g and the cost based optimizer. The testing revealed some peformance issues, and we did both some code changes (design) and changes in the SQLs (and datatypes) before upgrading in production. I really did expect some performance issues, but not all of them was that obvious.
The main problem after the upgrade was contention and waits caused by blocking locks. Many times blocking locks could be a symptom of some other problem. Having some SQLs with inappropriate execution plans keeping the transaction and the locks open for a longer time, this could really explain the locking issues. In the beginning we were following this theory, and just worked on tuning the bad SQLs. One of our application (soon to be retired) is not using bind variables, and unfortunately it’s not possible to change the code. To solve this we created a logon trigger identifying the client application, setting the optimizer mode to rule based for these sessions. This is just a workaround until the new application is rolled out.
To tune the other bad SQL we used sql_handles with some hists, we created some new indexes, but also made some indexes invisible. One after one the bad SQLs started to perform as wanted, but the blocking locks still showed up and jammed the systemes once in a while.
Suddenly I came across this article. I also read this article “Oracle11g: New Locking Modes When Policing FK Constraints (A Wolf at the Door)” by Richard Foote. These articles states that there has been a change since 11.1.0.6 on how locks are held in relation to foreign key contraints. Using the script referenced in this first article, we found some child tables without an index on the foreign key. We recognized one of the tables as one we had been seeing locks waits on. After creating an index on the foreign key the “Enq: TM contention” waits disappeared immediately.
I also noticed some SQLs in the library cache that was flushed very quick. When looking in v$sql and v$sql_shared_cursor there are some new columns. There were obviously going on some new stuff in the libray cache. After some more reading I learned that this new feature is called “Adaptive Cursor Sharing. Maybe this is the missing link between binds and histograms, and a fix to the bind_peeking feature. Or should I say “bug”?
Post a Comment