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 (more...)

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.

UNDO is over-sized because of a dumb monitoring which treats UNDO tablespace in the same way as normal datafiles, i.e. the monitoring suggests to have some free space in UNDO. So it notifies DBAs and tells them to increase UNDO datafiles. And most DBAs (more...)

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 – (more...)

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 (more...)

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.


Both cases were similar and involved queries which were accessing views; and those views’ code is generated by an (more...)

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 (more...)

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 (more...)

Averages

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

Here is a Workload Comparison section from an AWR diff report (generated with $ORACLE_HOME/rdbms/admin/awrddrpt.sql):

Workload Comparison
~~~~~~~~~~~~~~~~~~~                   1st Per Sec          2nd Per Sec      %Diff              1st Per Txn          2nd Per Txn      %Diff
                                  ---------------      ---------------     ------          ---------------      ---------------     ------
                    DB time:                  0.6                  0.6        1.6                      0.0                  0.0      -50.0
                   CPU time:                  0.3                  0. (more...)

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 (more...)

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 (more...)

OIC(A) again – 2

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


Setup:

drop table t1 cascade constraints purge;
create table t1 (id, x, pad, constraint t1_pk primary key(id, x))
as
select trunc(rownum/10)
     , mod(rownum, 10)
     , s1.text
  from all_source s1, all_source s2
 where  (more...)

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 (more...)

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 (more...)

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 (more...)

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.


Here’s a simple table T1 holding 1M rows with the indexed column X (more...)

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 (more...)

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)!

The start

A glass for questions. It was full in the end of the day

Tom started with a bit of history of how Oracle developed its database. Old stuff, C compilers, 37kg disk drives :-) followed by the modern, upcoming features in 12c. Pluggable Databases mostly (more...)

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.

Here is a case to demonstrate:

declare
    n number := 0;
begin
    dbms_session.session_trace_enable(true, true, 'all_executions');
   
    for x  (more...)

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.

2 or 3 weeks before the event I’ve got the list of topics to work on. It included several (more...)

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.

Basically OP has a query with disjuncted (OR-ed) predicate which started to fail after 11.2.0.3 upgrade with ORA-01790: expression must have same datatype as corresponding expression. Here is a test case (I’ve renamed column and table names cause I’ve used to such naming):

drop table t1 cascade constraints purge;
drop table t2 cascade constraints purge;

create table t1 (
  x int not null,
  y varchar2(13)
 (more...)