Author Archives: Timur Akhmadeev

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

Flashback cursors

Flashback query allows to get data as of required point in time. It’s a nice feature. It’s strange in the implementation though. If you try to use AS OF SCN query with bind variables, it won’t help you to keep number of child cursors low: each execution (independently of the incoming SCN value) will cause a new child cursor to appear with FLASHBACK_CURSOR as the cause. I don’t understand why Oracle is doing it this way.

Continue reading

ORA-600 Troubleshooting Tool

I’ve just noticed that the new HTML MOS introduced a nice addition to the old ORA-600/ORA-7445 Error Look-up Tool: now there is a new ORA-600 Troubleshooting Tool (if the link is not opened automatically for you try refreshing the page with F5 – works for me in Chrome).
Within a few minutes it allows you to upload a generated trace file and then automatically redirects you to the search results based on the error reported in the trace. The tool should be useful to those who don’t know what to do with the trace. It also allows you to save the results in a “report”. It’s definitely a very nice addition to the MOS functionality.

Statistics poll

Thanks to everyone for spreading the link and participating in the poll on statistics. As it was pointed out in the comments, the poll isn’t very specific, I know. My point was to get an idea how many people use the defaults (I considered Oracle version 11g) for stats collection, how many do a very simple and reasonable tweak to get more stable execution plans, and how many people prefer custom statistics management. Well, I think the results will not change much and here are as following (326 votes at the time of writing):

  • 42% use the defaults (if you truncate the value. I can’t put something other than 42 here, sorry guys :-))
  • 16% don’t like unexpected histograms :-D and prefer to take situation under control
  • OMG another 42% (since it’s not Russian elections it has to be 100% in total, you know) use custom statistics management

Nice! Now we know that the defaults are suited to less than a half of Oracle installations. And my opinion is there would be more than a half using the defaults, should Oracle decided to switch the default METHOD_OPT value to ‘FOR ALL COLUMNS SIZE REPEAT’. Consider Tim Hall’s reply “On low priority systems the defaults are fine in many cases”, and think about why defaults are fine for such systems? My humble opinion is: nobody cares about low priority systems. So it doesn’t really matter if it’s reliable or not, whether there are histograms present or not, whether a system is stable or not – it’s low priority anyway. It will survive somehow. For me it means it’s not too much of a problem to go back to the future and modify default METHOD_OPT so that no histograms are gathered initially by default – as all should do manually now /me thinks.
PS. I wouldn’t mind switching GATHER_TEMP back to the FALSE and returning it to the documentation too, as it was in 9i.

Information exchange

This is a short note on the topic of information exchange. I use OraNA.info to get up-to-date technology news. This is a blog aggregator I’ve tried first and have been using it since then, which is 3-4 years I guess. It’s great :) and I’d like to use this opportunity to say Thank You to Eddie Awad for a great blogs collection!
If you still don’t use blog aggregators and rely on your own blog collection in Google Reader or something else, then consider using one entry point instead of multiple, it’s much easier. Also I’d like to suggest using contact form on the OraNA to suggest new blogs if you know some which are not aggregated. I use it regularly when I find something interesting; I have contributed about a dozen of new blogs to OraNA so far. You can [should] do the same.

METHOD_OPT

Here is a poll prompted by today twitter talks on the default METHOD_OPT value (which is ‘for all columns size auto’ since 10g) and automatic statistics gathering job (auto-task since 11g). CBO development team suggests to use the default job to gather statistics with default options. Do you use the job exactly this way with real production databases? I’m very interested to see some numbers. Please share the poll link http://poll.fm/3n7pn so that more people vote. Thanks.

Fail

The post reminded me of my own fail happened several months ago.

Continue reading

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.

Follow

Get every new post delivered to your Inbox.

Join 287 other followers