Adaptive STAT lines in SQL trace

Lately I’ve been using SQL runtime execution statistics combined with SQL monitor for performance diagnostics and, honestly, almost forgot about SQL trace. So this note is not very useful to me but it might be to someone: along with ALL_EXECUTIONS option appeared in (I believe) 11gR2, there’s a new option starting with 11.2.0.2 which can significantly decrease amount of data in the trace files for STAT lines compared to ALL_EXECUTIONS, still providing some of them from time to time.

Consultancy

I have a permanent job at the NetCracker‘s System Performance group. Recently I was offered to do one day job outside, on-site in another company, which coincidentally has an office close to NetCracker’s Moscow office. It was an opportunity to apply my skills in a completely different situation which I couldn’t miss; plus I’ve never done public presentations before and this was a good occasion to practice that. Here I’d like to write down some notes how the event went.

Statspack quiz

I’ve a level 5 Statspack report from a real production 11.2.0.2 database with the following Top 5 Timed events section:

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
log buffer space                                    28       1,396  49870   41.0
buffer busy waits                                   51       1,049  20567   30.8
CPU time                                                       320           9.4
log file parallel write                          4,011         159     40    4.7
log file sync                                    3,275         153     47    4.5

The complete report (without SQL and several other sections) is here. Can you explain what & why is wrong in this Top?
I must admit I don’t have the definitive answer (probably yet), but I have partial explanation of the issue as well as several ideas.

Load profile

I like Load profile section of Statspack or AWR reports (who doesn’t). It’s short and gives a brief understanding of what kind of work a database does. But what if you don’t have an access to Statspack or AWR but still want to see something similar? It’s possible to use V$SYSMETRIC to get this numbers for last 60 or 15 seconds. I wanted to write a script to do this for a long time. Here it is.

Recycle bin

Recent Charles Hooper’s post on the topic of Recycle bin (which is, BTW, documented behavior) reminded me of an issue with that functionality I’ve seen recently. The problem was a single-row INSERT INTO table VALUES () statement was hanging for more than an hour burning CPU.

16K CR gets for UQ index scan? Easy. Kind of.

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 9.2.0.8 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.

JIT

Starting with Oracle 11gR1 Oracle JVM includes Just-in-Time compiler. Its goal is to convert JVM bytecode into platform-specific native code based on the performance data gathered in run-time. It is stated in the documentation that JIT “enables much faster execution” – and this is, in general, true. There are ways to control the behavior of JIT, one way is described in the MOS Doc ID 1309858.1, and another one here.

Enabling constraint in parallel

Recently I did some tuning of data generation scripts, which purpose is to build large amount of representative data for application testing. Direct-path inserts are in use and as a prerequisite all constraints and indexes on target tables are disabled before the load and are enabled after it. Since I wanted to utilize available resources on the machine for that task, almost each step uses parallel execution. Well, kind of almost, because enabling constraints didn’t run in parallel, although I’ve politely asked Oracle to do so. I’ll explain here why it didn’t work.

PQ_DISTRIBUTE enhancement – Part 1

Oracle 11g has introduced a hell lot of new features related to Parallel Execution – like statement queuing, in-memory PX and auto-DOP, to name a few. There are also small bits of enhancements here and there – like default value of the PARALLEL_EXECUTION_MESSAGE_SIZE and an introduction of an easy way to stick PX slaves to a current RAC node with PARALLEL_FORCE_LOCAL. As I’ve recently discovered, PQ_DISTRIBUTE hint used only for joins prior to 11gR2, can now be used for load operations.

How to kill performance with 16MB table

If you think 16MB tables are so small that can’t be root cause of significant performance problems with SELECT queries, then read about my today’s headache – a story of 16MB table worth something like 270 seconds of response time.

Missing wait event

Oracle 11.2.0.1 for Windows is out for some time. I’ve installed it on my 32-bit Windows XP machine because I like Windows – just to check that it’s actually working fine. Playing with TPC-H test using Hammerora I’ve noticed an anomaly in the way Oracle reports IO waits for some queries.

WLS + Oracle benchmark – 2

Another press release from Oracle on the same topic: benchmark of WLS 10.3.3 (not released yet) + Oracle 11.2.0.1 @ Dell PowerEdge R910 Server with 4×8-core beasts X7560 (announced today by Intel). Benchmark was done by Oracle itself (previous were conducted by Cisco & HP). Results and FDA are here. DB & WLS configuration are pretty much the same as in previous run, but there are some variations in numbers, like ~400GB redo logs instead of ~140GB; with one big change: compatible=11.2.0.1.

WLS + Oracle benchmark

Today Oracle announced this press release. Cisco Systems tested WebLogic SE 10.3.3 (not available yet) + single-node Oracle 11gR2 @ Cisco UCS C250 M2 Rackmount Server with 2×6-core Intel Xeon X5680 (not available yet) – here are the results. I want to highlight some specifics about this test bed – after all, it’s interesting to know what’s behind the scene.

ASH Viewer

If you like Top Activity page of the Oracle EM console, but don’t have it up and running or want to simulate the same in 8i/9i, then here is an open-source standalone program to do that: ASH Viewer.

RAC is not a requirement for in-memory PX

As pointed out by Greg Rahn in the comments to Mark Rittman’s blog post, new Oracle 11gR2 feature called In-Memory Parallel Execution work well in a single-instance environment.