Search

Top 60 Oracle Blogs

Recent comments

August 2011

Real-Time SQL Monitoring - Statement Not Appearing

Like Greg Rahn, I've looked at many SQL Monitoring reports over the past year or two. Possibly not as many as Greg, but it's become my default method of communicating* SQL performance issues to colleagues to the point that some might be finding it irritating by now, whilst others are hooked from the start. (Personally, I can't understand those who aren't hooked from the start!)

One of those who have been hooked came to me with a problem last week. He simply couldn't see his report in the OEM SQL Monitoring screen and after asking him if it was really running right now (it was) and attempting a re-run with a /*+ MONITOR */ hint, I was almost stumped. Then I suggested we fall back on using DBMS_XPLAN.DISPLAY_CURSOR to get the plan and when I saw the results, I suddenly understood what the problem was. This was a massive plan! It wasn't a particularly complex query but it was referencing a Data Dictionary view (I can't remember which one now) which expanded out into what looked like hundreds of lines. Which was the problem.

There's a hidden parameter - _sqlmon_max_planlines with a default value of 300 - which limits the number of lines beyond which a statement will not be monitored. This statement exceeded that limit.

A small post, but hopefully useful should you ever wonder why a statement isn't appearing. Some day soon there'll be another post about why you can't find statements that you know executed fairly recently, which is a much more common problem in my day-to-day work. (As a preview, that parameter is _sqlmon_max_plan.) Then another one about the mysterious disappearing tabs!

* Communication is where SQL Mon excels, with the ability to send ACTIVE reports to allow others to dig around in the detail.

Advertising appearing?

I’m curious – is anyone visiting my blog seeing some form of advertising popping up?

I ask as there is a section on “links clicked” in the stats page and rather than the usual traffic of people clicking on the oaktable logo or people in my blogroll, the most common link is for ecopressed-dot-com. I’ve never heard of them. When I go in to my blog I don’t seen anything but then it knows it is “me” so maybe it would not.

I’m not too bothered about it, after all WordPress are hosting my blog for pretty much nothing {I pay them some outrageous sum of a few US dollars a year so I can alter my CSS file and thus make the layout wider}. I’m just curious.

I wonder if this is a result of increased traffic to my site? I’m still fairly small-fry compared to lots of other sites but as I’ve been putting out more stuff of late I think I’m going up the web rankings. Oddly enough, those pictures of bullets I put on last Friday’s philosophy have been very popular. I can’t help but feel that most people looking for information on bullets are going to find a blog about IT somewhat disappointing :-)

Indexing A Column With Just One Distinct Value (All The Madmen)

When one thinks about a column that might benefit from being indexed, one generally considers columns with lots of different values so that the selectivity of the column is such that relatively few rows get selected, making the index appealing to the cost based optimizer.   There are of course many exceptions to this generalisation [...]

Logical I/O Evolution - Part 3: 11g

Preface (with apologies to Kevin Closson)

This blog post is too long

Introduction

In the previous part of this series I've already demonstrated that the logical I/O optimization of the Table Prefetching feature depends on the order of the row sources - and 11g takes this approach a big step further.

It is very interesting that 11g does not require any particular feature like Table Prefetching or Nested Loop Join Batching (another new feature introduced in 11g) to take advantage of the Logical I/O optimization - it seems to be available even with the most basic form of a Nested Loop join.

Crowdsourcing Active SQL Monitor Reports

As my loyal readers will know, I have been a big (maybe BIG) fan of the SQL Monitor Report since it’s introduction in 11g. It would not surprise me if I have looked at over 1000 SQL Monitor Reports in the past 4+ years — so I’m pretty familiar with these bad boys. Since I find them so valuable (and many customers are now upgrading to 11g), I’ve decided to do a deep dive into the SQL Monitor Report at both Oracle OpenWorld 2011 in October and the UKOUG in December. I think I have some pretty interesting and educational examples, but for anyone willing to share Active SQL Monitor Reports from their system, I thought I would extend the possibility to have it publicly discussed at either one of these sessions (or even a future blog post). Sound cool?

blevel=1

Here’s one of those quick answers I give sometimes on forums or newsgroups. I forget when I wrote this, and when, and what the specific question was – but it was something to do with rebuilding an index on a small table where data was constantly being deleted and inserted.

Another problem with high insert/delete rates appears with very small indexes.

If you have a table that is small but constantly recycles its space you may also find you have an index where the number of leaf blocks puts you close to the borderline between having blevel = 1 and blevel = 2. If the size crosses that border occasionally and the statistics are updated to reflect the change – which is quite likely for a table subject to lots of updates and deletes if you have automatic stats collection enabled – then execution plans could change, resulting in dramatic changes in performance.

The workaround is fairly obvious – don’t let Oracle collect stats automatically on that table, instead create a stats-collection strategy for eliminating the change in blevel. For example, keep the stats locked except when you run your own code to deal with the stats, making sure that you overwrite the index blevel with 1 even if it has just crossed the boundary to 2.

Footnote: the reason why a change from 1 to 2 is dramatic is because Oracle ignores the blevel in the optimizer arithmetic when it is set to 1; so the change from 1 to 2 actually has the impact of a change from zero to 2. Then the cost of a nested loop access is “cost of single access multiplied by number of times you do it” – so the sudden appearance of a 2 in the formula gives an increment in cost of  “2 * number of times you visit the table” of your small table is the second table in a nested loop – and suddenly a nested loop becomes much more expensive without a real change in the data size.

Footnote 2: it should be obvious that you don’t need to rebuild the index once you know what the problem is; but since we’re talking about a small index with a blevel that is usually 1 it probably won’t take more than a fraction of a second to rebuild the index and there’s a fair chance you can find a safe moment to do it. In terms of complexity the solution is just as simple as the stats solution – so you might as well consider it. The only thing you need to be careful about is that you don’t happen to rebuild the index at a time when the blevel is likely to be 2.

Footnote 3: For an example of the type of code that will adjust the blevel of an index see this URL. (Note, the example talks about copying stats from one place to another – but the principle is the same.)

Reaction to riots around the UK…

I spent quite a while watching the coverage of the riots on TV last night and this morning. I also made a few angry posts on twitter. Not surprisingly I am still pretty angry about everything this morning. What follows will be a rather extreme rant about the situation. Look away if easily offended…

What were the recent incidents *not* about?

  • This is not a mass uprising. London has 12 million people and only a few hundred scum are doing this.
  • This is not a race-specific issue. I’ve watched the footage and the crowds are made up of people from all races. Roughly speaking, the mix reflects the racial mix of the area that is being attacked. Don’t use this incident to further your racist (positive or negative) agenda!
  • This is not political. We are not under the control of some unjust dictator and fighting for our political freedom. Likening this to the recent events in the middle east is offensive. Since when has looting a PC store or a phone store been political?
  • This is not about “the kids”. The age range of the people is mixed.

What should be done in my opinion?

  • They should announce a curfew. Being on the streets after that time means you accept the consequences of anything that happens to you. Good people will be off the streets, so you know if you see someone they are up to no good.
  • Police and army reserves should be mobilized. They should be issued with rubber-bullets given a “shoot first, ask questions later” policy.
  • If someone gets injured or killed as a result of the police/army action, tough! No claims. No law suits. No apologies. Tough! If you don’t want to get hurt, don’t be there.
  • We have loads of surveillance cameras on our streets. Faces from the footage should be placed on a website where the public can anonymously shop them to the police. I would encourage anyone who recognizes any people from the footage, or has information from any social network sites, to pass the information to the police and get these people off our streets.
  • Everyone identified should be prosecuted and get a custodial sentence. If it means cramming them into cells and “infringing on their human rights”, so be it. They gave up their rights when they decided to do this.
  • Stop putting “community leaders” on TV who try to justify this crap. I’m sorry, but as a disenfranchised man I do not feel the need to rob a new pair of trainers or a mobile phone. Some of the footage showed a group of kids talking and one said, “Let’s get some watches!” Nuff said!

This country is going to hell in a hand basket. We need to stop pussy footing about and get some authority back.

Cheers

Tim…

PS. If this stops me seeing Rise of the Planet of the Apes, I’m going to kick off… :)




Secret hacking session – full scans, direct path reads, object level checkpoints, ORA-8103s! (again)

I’m mentioning this again just in case you missed the announcement (because I posted it on the weekend):

 

There will be anotner free Secret hacking session – about full scans, direct path reads, object level checkpoints, ORA-8103s!

It will happen tomorrow, Tuesday 9th August, online!

 

Register here:

See you soon!

 

Are you getting the most out of your Exadata performance? Part 1

In almost all of the Exadata migration projects I’ve been part of, the client sees immediate speedup & performance increase when testing their workload on Exadata (of course, we’ve made sure that we do plan & execute the tasks right). However, my performance geek’s nature usually doesn’t allow to stop there and leave the client with just 2x or 3x performance increase. For data warehousing and reporting workloads, Exadata can do much better than just 2-3x performance increase!

Are you getting the most out of your Exadata performance? Part 1

In almost all of the Exadata migration projects I’ve been part of, the client sees immediate speedup & performance increase when testing their workload on Exadata (of course, we’ve made sure that we do plan & execute the tasks right). However, my performance geek’s nature usually doesn’t allow to stop there and leave the client with just 2x or 3x performance increase. For data warehousing and reporting workloads, Exadata can do much better than just 2-3x performance increase!