Category Archives: Performance

Long Parsing and PGA limits

Recently I’ve seen not so smart optimizer behavior: one query took long time to parse, and ended with an error hitting PGA_AGGREGATE_LIMIT in few minutes; another query was just parsed for ages while using reasonable (under 2G :)) amount of PGA and still could hit PGA_AGGREGATE_LIMIT but after way more time – up to an hour.

Continue reading

Hotsos 2016 Recap

I wanted to go to Hotsos Symposium for quite some time, having heard so many great topics from there for years. And every time I was a bit lazy to think about what I can talk about. Apparently I thought that everything I know is well covered elsewhere, so why would I be accepted. Plus all these complexities of budget, getting a visa, travel arrangements, jet lag, and personal matters in between. Last year, when call for papers was still open, I realized that there’s a good chance I can make it to Hotsos in 2016: I had a budget, visa is a doable thing, and most importantly I knew I had a topic to talk about which most likely will not be in competition with other speakers.

Continue reading


Recently while observing AWR reports, I’ve seen a very good example of how average value hides important pattern.

Continue reading

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

Continue reading


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.

Continue reading

Statspack quiz

I’ve a level 5 Statspack report from a real production 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.

Continue reading

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.

Continue reading

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

Continue reading


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.

Continue reading

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.

Continue reading

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.

Continue reading

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.

Continue reading

Missing wait event

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

Continue reading

WLS + Oracle benchmark – 2

Another press release from Oracle on the same topic: benchmark of WLS 10.3.3 (not released yet) + Oracle @ 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=

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.

Continue reading

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.

Continue reading

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.

Continue reading