5 years

I’ve joined Pythian exactly 5 years ago. Back then I was a performance guy, with no real DBA and operational support experience. I knew enough about technologies though. Still getting up to speed with common admin tasks took me almost a year. At some point later it became a bit boring: dealing with out of space issues and other noise during on-call can’t be appealing for long. After ~2 years I had an opportunity to join a big project which required experience in things I was familiar with. It lasted for almost a year, and it was a useful experience.

Then I got back to normal DBA work for a short period of time, after which I’ve joined another big project. This one was tricky at the beginning, and continues to be one of a kind for almost 2 years. Initially I was tasked to help with performance issues in an application I’ve never seen before, running on top of application server I’ve never touched. It took me few weeks to figure out unknown bits and pieces, and a few months to get stuff fixed (as it usually happens, negotiations how to make necessary changes take time).

After the app was fixed, I joined another part of a big project which is dealing with Oracle eBS. The work I did there was mostly operational support, helping with whatever things I can handle: almost anything but installation/patching/cloning which are things that seems to be high priority in eBS world. I did performance troubleshooting tasks as well, and it happened so that now I do performance work mostly for more than half a year. I really like it: I use my top skills to get to the bottom of issues, then fix it, get feedback pretty quick, and learn something along the way sometimes.

Such a long time on a dedicated project led me to a move to eBS team within Pythian.
Again, I need to catch up on stuff I know partially … same as 5 years ago 😉 I think it should be fun!
Several Pythian eBS folks are located in Riga, and I’m going to meet them at Riga Dev Days 2018 in about a week. Looking forward to it!

UNDO sizing

90% databases that I see for the first time have the same issue with UNDO tablespace: it’s over sized, yet still causing infamous ORA-1555 errors at times. Here is why.

Continue reading

Conferences 2017

Last year I’ve been to a few conferences. At some point I thought I need to record some of the sessions to let more people see them as well.
So I took a cheap action camera & recorded several presentations. Video quality is not good (mostly) due to lighting but still is enough to get an idea of how was it back there in a room.
Here’re links to the videos. Enjoy!

Hatem Mahmoud – Memory Access Tracing/Profiling

Jonathan Lewis – Just Don’t Do It

Panel Discussion at POUG2017

Marcin Przepiorowski – dNFS for DBAs

Neil Chandler – Why Has My Plan Changed

Timur Akhmadeev – Common Pitfalls in Complex Apps Performance Troubleshooting (same presentation in Russian at Russian OUG meetup in Moscow)

Neil Chandler – Histograms are Evil like Chocolate is Evil (part of the same presentation recorded in Poland as well)

Kamil Stawiarski – VOODOO: the black magic of cheep cross database replication

Roger MacNicol – Table Access Full

Mike Dietrich – Ensure Performance Stability When Upgrading Oracle Database

Adam Bolinski – High Performance Oracle Environment on Not Only NFS

MOS plugin

Few days back I was (again) angry at My Oracle Support “The page has expired” message appearing in an open tab I left for some time. I tried to find out how to avoid it, and it was relatively easy to do even though I don’t know JavaScript. Then I tried to make a plugin out of it so I it can be on by default & won’t need my attention.
Here it is: plugin for Chrome. It took me 2 seconds to come up with a name – MOS-cow and about 3 hours to prepare plugin.
Then I realized that having such plugin independently is probably not a good idea (it’s only for Chrome, and there are other browsers), and it may be enough to just add a custom JavaScript to Tampermonkey so it’s possible to use in almost any browser. So here it is. I don’t plan to add any new features at the moment, but maybe later. I know what else is needed but have no idea how much time it would take to implement, probably too much, which is not an option.
Anyway, I hope this little stupid piece of JavaScript helps you!

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

Hotsos 2016

I’m going to Hotsos 2016 soon for the first time. I’ll speak about Middleware and the issues I see commonly everywhere. It took me quite some time to prepare for this trip: budget, visa, and, most importantly, presentation – required at least 120 hours of my time (which is a lot). In fact presentation is still work in progress and I hope to finish it just before March 8 when I’m presenting. I feel a bit nervous about my topic since the conference is all about Database Performance, the quality of presentations is always high and they are super technical, while I’m planning to have a light talk focused not on the Database mainly – just my rants on the things done wrong I usually see in the wild everywhere. It’s not something on the cutting edge, and some of my notes are so bloody old. Anyway I have 1h available and will try to spend it with pleasure saying anything I want (like “Hello Mom”). In the worst case I’ll never be accepted again there, which is bad but not the end of the world, right?
Anyhow first I need to survive tight connection at Frankfurt this Saturday, plus jet lag and then enjoy almost a week of fun.
See you in Dallas!

Averages

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

Continue reading

Transcript of the interview with Jonathan Lewis

If you missed the interview with Jonathan Lewis or don’t want to spend 93 minutes watching it, here is its transcript. Thanks to Stanislav Osekin from Innova, who did most of the hard work. I have only corrected special Oracle terms. Also thanks to Jonathan, who helped me in several complex cases where I couldn’t understand the wording.

Continue reading

Interview with Jonathan Lewis

If you follow my Twitter you probably have heard that Jonathan Lewis visited Moscow in May this year. Thanks to Innova, Jonathan gave a 1-day seminar, and I was one of the lucky people from Russian Oracle community who have been invited to the event.
Even more, I was offered to take an interview with Jonathan which was recorded! It was my first time interview and I was very excited (long before the recording) and scared to death (right in front of the camera) so please forgive me for speaking like a dull. I know that, and it almost doesn’t matter. What matters is Jonathan was brilliant and so excited at times, that 90+ minutes of sitting in a cold room right under air conditioner for me flew like a 1 minute.
Well, enough the words, just enjoy it (this is uncut version recorded in 1 go by the way)!

OIC(A) again – 2

Continuing from the previous post, here is one more case when adjusting optimizer_index_cost_adj may hurt you.

Continue reading

start_of_group

Those who visit SQL.ru often know what the title means. It’s a very simple yet powerful technique to group data which doesn’t seem appropriate for grouping at first sight.
I learned this very nice tip long time ago but often have to re-think before applying it to SQL or even search for the correct way of doing it when my brains give me denial of service. Recently I needed to do this type of query again and thought I should document the process, so I won’t forget it next time.

Continue reading

System statistics poll

Recent thread in the OakTable mailing list prompted me to create a poll and ask about the ways DBAs use system statistics in real systems. If you struggle to understand what system statistics is and what are the available options, here is the suggested reading:
Documentation – System Statistics
Best Practices for Gathering Optimizer Statistics, Oracle whitepaper
System Statistics – Troubleshooting Oracle Performance
Understanding the different modes of System Statistics, blog series

Ignoring hints

A hint is an instruction to the optimizer

This is what’s written in Oracle documentation. Instruction is defined as

a code that tells a computer to perform a particular operation

Which means Oracle CBO must obey the hints and must perform particular operation. The latter is hard to define correctly and explain precisely because it involves the logic of the block-box (what Cost Based Optimizer is). Some of the operations are mentioned in the standard Oracle documentation, some of them scattered across different places, and there are exceptions as usual. I think I’ll list here these cases which could lead to “ignoring hints” with the links to documentation/blogs.

Continue reading

OIC(A) again

Issues with OICA/OIC (OPTIMIZER_INDEX_COST_ADJ/OPTIMIZER_INDEX_CACHING) parameters have already been mentioned many times. Recently I’ve noticed one more and I think I didn’t see this case somewhere else on the Internet so I’ll share it.

Continue reading

Fixed stats

Some time ago I had two questions about fixed objects statistics for which I couldn’t quickly find the answers. Questions are:

At the time of asking these question I’ve read CBO development team blog post on the topic, but still I was unable to answer them distinctly. Well, it appears the questions are simple and could probably be deduced after careful reading (first two questions for sure). The core phrase is:

The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks

That’s the key, the statistics are the same, except for the number of blocks. Which effectively means, you can gather the statistics on the specific X$-table in the same way as you do with a normal table, by using DBMS_STATS.GATHER_TABLE_STATS procedure. The only difference would be you will not populate the number of blocks (but you can do this manually). Now, since these statistics are essentially the same as statistics for normal tables, it makes no sense to run the GATHER_FIXED_OBJECTS_STATS procedure on all RAC nodes. After all, the database is still the same and it doesn’t care about the table statistics in relation to RAC node (at least for now in 11g).

I wish I was that smart back then 🙂 I’ve found the answers recently via MOS notes updates:
In RAC, Should We Execute GATHER_FIXED_OBJECTS_STATS And Gather_dictionary_stats In Both Nodes? [ID 1479804.1] which gives the definitive answer “No” and
Why do some Fixed Tables not have Optimizer Statistics [ID 1355608.1] which explains that some fixed tables cannot have optimizer statistics by design decisions made by Oracle.
What I don’t understand after getting better idea of fixed stats is why Oracle made the choice to introduce a specific new procedure, without any parameters/configuration options if they could have better done it inside the standard procedure for statistics gathering? To me it looks very strange and I would prefer not to introduce one more complication to the already over-complicated statistics package.

Tom Kyte in Moscow – 2

On December 11th Tom Kyte performed “AskTom in Moscow” for the second time (first time was almost three years ago in February 2010). I was there and enjoyed presentations, tweeted a lot about the content and in the end I’ve won a signed copy of the Expert Oracle Database Architecture (2nd edition, in Russian)!

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

Continue reading

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.

Continue reading

Unnesting disjunctive subqueries (with OR predicate)

Jonathan Lewis has recently posted a good example of CBO not good enough in transforming specific query types. A recent thread on the SQL.ru Oracle forum reminded me of this issue.

Continue reading