v$sqlstats

Sometimes you find bugs on MOS (Metalink, OCIS, whatever) that make you feel positively ill. I’ve just been on a customer site where (in passing) they mentioned that one of their historic queries against v$sqlstats now tool just over one second (CPU) in 11.2.0.3 when it had previously taken about 200ms on 10.2.0.4***. After a little checking it seemed likely that the change was possibly related to the fact that they had increased the size of the SGA significantly, allowing for a much larger shared pool and library cache; however there have been numerous code changes in the shared pool area on the route from 10g to 11g, so I decided to check MOS to see if anyone else had seen a similar problem. I found this:

11.2 New views

It looks like Laimutis Nedzinskas has spent some time producing  a list of new views, and changes to existing views, for trouble-shooting specialists. The lists cover the dynamic performance views and the AWR history views, and the differences between 10.2 and 11.2. The lists include, where relevant, convenient hyperlinks to the view definitions in the online copy of  the 11g Server Reference manual.

I haven’t checked the lists in detail, but I can see myself  taking advantage of his efforts from time to time:

• New/Changed dynamic performance views (v$)
• New/Changed AWR data dictionary views (dba_hist).

AWR Reports

A little thought for those who use OEM to generate AWR reports – if you generate the standard HTML form and then try posting it into (say) the OTN database forum when you want some help interpreting it, it won’t look very readable (even if you do remember to put the right tags before and after to reproduce it in fixed font).

Analysing Statspack 12

[Further Reading on Statspack]

Part 12 is about a 2-node RAC system.  Someone recently posted links to a matched pair of AWR reports in this thread on the OTN Database General forum and, after I had made a couple of quick comments on them, gave me permission to use them on my blog.

AWR Dilemma

Here’s an odd little observation that came out of the UKOUG conference last week:

If you’re running 10g, and don’t have the relevant performance pack and diagnostic licences there are various views you are not allowed to query, packages you are not allowed to run, and reports you are not allowed to read.

To support the performance diagnostics, though, your database is probably churning away constantly dumping all sorts of useful data into the memory structures and tables that you are not allowed to access.

But that’s no longer a problem – it looks as if you can disable the AWR collection very easily in 10.2 with the following procedure call:

execute dbms_workload_repository.modify_snapshot_settings(interval => 0)

There’s just one problem with this – you can’t run this package unless you’re licensed to run the AWR. So if you want to disable the AWR, it looks like you have to pay to run it first.