The UKOUG conference is over for another year – but it has left me with plenty to do and lots of things to investigate. Here’s just one little point that I picked up during one of the 10 minute “Oak Talks” that members of the Oak Table Network were doing in the lunch breaks.
There is a fairly well-known strategy for generating a list of numbers by using a “select from dual … connect by …” query, but I hadn’t realised that there were two ways of using it. The code I’ve usually used is this:
select rownum id from dual connect by rownum <= 4000 ;
But it looks as if most people use it like this:
select rownum id from dual connect by level <= 4000 ;
My good friend (and personal hero) Cary Millsap is doing a series of one day classes around the world — Mastering Oracle Trace Data. One of them is conveniently scheduled in Birmingham Thursday next week right after the UKOUG Conference. It’s not far from the Birmingham ICC where the UKOUG Technology and Business Suite Conference [...]
November 23, 2011 I previously wrote a couple of articles that mention reasons why an index might not be use for a particular query, including an article that was formatted as a True or False quiz with several reference articles. A few days ago I saw an OTN thread that caught my curiosity, where the [...]
I think I’ve posted before about how deep a good DBA should dig into solving issues, as opposed to fixing them as soon as possible and moving on to the next urgent task.
Well, a friend of mine, Neil Chandler, has just posted on this topic, giving his reasons why you don’t run a 10046 trace on production. Neil raises some good points about how difficult it can be to get permission to do something as intrusive as a 10046 trace on a production system as well as the fact that most problems can be solved way before you get down to the level of tracing. Especially if it is not your job to go around solving the problems that have stumped the in-house team, which is the lot of many people who are recognised as being very good with Oracle.
I planned to write on this for quite some time, but failed to do so. Sorry about that. Today I finally got time and desire to describe a situation from the title. It was observed on an Oracle 126.96.36.199 running Solaris SPARC; manifested itself as a severe CPU burning at 100% utilization with ‘latch free’ on the first place of the Top 5 wait events in Statspack report.
Here is part of a 1 hour report:
If you work with SQL Trace files (and profile them) then you should check out the awesome novel use of the “external table preprocessor” feature explained by Adrian Billington here:
Ironically just a day after writing my “Evil things” article, I noticed a note in MOS about how to enable an event 10384 at level 16384 to get a parallel plan to be executed in serial:
I was lucky enough to get my hands on the Method-R Profiler and had a test drive with it. So first of all what is it? As you might expect a profiler, a profiler for Oracle performance problems. The tool makes use of Oracle’s trace facilities. If you have a performance problem with the database …
In my last post, IOT part 6, inserts and updates slowed down, I made the point that IOT insert performance on a relatively small Oracle system was very slow, much slower than on a larger system I had used for professional testing. A major contributing factor was that the insert was working on the whole of the IOT as data was created. The block buffer cache was not large enough to hold the whole working set (in this case the whole IOT) once it grew beyond a certain size. Once it no longer fitted in memory, Oracle had to push blocks out of the cache and then read them back in next time they were needed, resulting in escalating physical IO.