Who's online

There are currently 0 users and 43 guests online.

Recent comments

Oakies Blog Aggregator

Free In-Memory Column Store Workshop with Maria Colgan

I am very proud to be able to announce a free workshop on Friday the…

Public Appearances 2015

Here’s where I’ll hang out in the following months:

11-12 Feb 2015: IOUG Exadata SIG Virtual Conference (free online event)

  • Presentation: Exadata Performance: Latest Improvements and Less Known Features
  • It’s a free online event, so sign up here

18-19 Feb 2015: RMOUG Training Days (in Denver)

  • I won’t speak there this year, but plan to hang out on Wednesday evening and drink beer
  • More info here

1-5 March 2015: Hotsos Symposium 2015

31 May – 2 June 2015: Enkitec E4

  • Even more awesome Exadata (and now also Hadoop) content there!
  • I plan to speak there again, about Exadata performance and/or integrating Oracle databases with Hadoop
  • More info here

Advanced Oracle Troubleshooting v3.0 training

  • One of the reasons why I’ve been so quiet in recent months is that I’ve been rebuilding my entire Advanced Oracle Troubleshooting training material from ground up.
  • This new seminar focuses on systematic Oracle troubleshooting and internals of database versions all the way to Oracle 12c.
  • I will launch the AOT seminar v3.0 in early March – you can already register your interest here!


Quick diff for two pieces of text in vim

Sometimes you need to see the difference between two pieces of console output. When I research, this can be two stacktraces, but also /proc//maps and smaps output; really anything. Of course, there’s diff, but the diff output is not very visual. Also, diff doesn’t do diffing between more than two files.

This can be done reasonably simple in vim. Here’s how to do that:
1. start vi; vi
2. do a vertical split using a new buffer; :vnew
3. open the first (left side) file; :r path/file or goto insert mode (esc i) and paste text.
4. goto the second window: ctrl+w ctrl+w
5. open the second (right side) file; :r path/file or goto insert mode (esc i) and paste text.
6. diff the two windows; :windo diffthis
7. turn diff mode off; :windo diffoff

You can also expand your diffing to three windows:
1. goto the rightside; ctrl+w l
2. change new window placement to the right side; :set splitright
3. do another virtual split; :vnew
4. open another file or paste text
5. diff again; : windo diffthis

I wrote this down for myself, but hopefully this helps other people too.

Tagged: commandline, diff, linux

How to Use an ASH Report and Why

Active Session History, (ASH) reports are one of my favorite when investigating a performance issue.  The biggest challenge I think that faces many administrators is to know when to turn to an ASH report and how to use the report to diagnose an issue.

Today’s example is brought to us thanks to a Workload replay that should be a simple, but also, a clear step by step of when and why to use an ASH report.

Top Activity

In our EM12c environment, we note some high activity in a database and use Top Activity to view what’s going on:


As a DBA, I’m not a fan of pink, red, brown or worse, black, in my Top Activity.  I’m displaying the information from the Top SQL and Top Sessions from just before 3pm and we can see that “Other” was the wait event that is shown to be most impacting in our Top SQL.

SQL Details

We can then double click on the highlighted Top SQL session to dig down a bit more into that unique SQL_ID.


Noting that this is an insert statement, I can then scan lower to see more detailed information about the SQL statement, waits and sessions involved:


The top wait is Buffer Exterminate and this wait event might not be familiar to everyone, but we’re going to work through this post to investigate the information provided from the ASH report to experience the type of impact this wait event has on the database.

Report Options

You’ll notice that the SQL Details page offers you two reports that you can run-  AWR SQL Report and the ASH Report.  These reports can both be found in the $ORACLE_HOME/rdbms/admin directory.

AWR SQL_ID specific report is named awrsqrpt.sql and the ASH report is named ashrpt.sql if you’re interested in running them from the command line via SQLPlus instead of using EM12c.

We are going to choose to use the ASH report instead of the SQL_ID specific AWR report OR a full AWR report for a couple reasons:

  1. We are only interested in this specific SQL_ID, so the full AWR report would be overkill.
  2. The specific AWR SQL_ID report wouldn’t include the session, blocked session and other wait info we are interested in.
  3. We aren’t interested in execution plans, which the SQL_ID specific AWR Report includes.
  4. The ASH report allows us to use samples and drill down to by time vs. snapshots, which comes in handy when we are inspecting particular set of transactions.  We aren’t concerned with number of executions, which is offered in AWR, but not in samples with ASH.

Running an ASH Report

Running an ASH report after clicking on the button is quite easy.  Not only is the time pre-filled-


we also have the SQL_ID filter to give us more accurate information about this one SQL_ID.  You can change the window for the beginning and end time if you’d like to extend it, but know that ASH is much more effective for smaller snapshots of time.

Once satisfied with the criteria for the report, click on Run Report and the HTML version of an ASH Report will display in short order.

The Actual Report via Cloud Control

The first thing to remember is that the report is generated by database node if you are in a RAC environment.  The left hand Target Navigation will show you what target node the report has been sourced from:



The report starts out with basic information about the environment the report was run in:



The first section we jump to, which we almost always jump to, is the top wait events:


I find it difficult to spend much time on anything that comes up under 10%, (at least its a pretty good rule of thumb to follow…) but as you can see, we are showing over 86% of the event waits on buffer exterminate.

If we go down to the second section we go to most often, the top SQL, we’ll see more verification of this issue:



For SQL_ID  67sjxrgv720p4, we can see that over 86% is spent on the wait event buffer exterminate.



We can also view the sessions involved with this SQL_ID and if there were any Blocked Sessions, that is listed in its own section.  No blocked sessions here, so we can go onto more important research.

Objects are most impacted by the waits for this statement are also displayed and if this had been part of a stored procedure of other internally stored code, this would have been shown, again, in it’s own section.



At the bottom of the report, we see what top waits happened in succession during the time we chose to examine the ASH samples:



As you can see, the samples are all about one minute each of an approximate five minute examination of the ASH sample time.  We can see at the 3pm time, (just as was displayed in the Top Activity) the impact of the buffer exterminate wait, then the wait for CPU and cluster GC waits post the “hit”.

Buffer Exterminate Waits

So what is Buffer Exterminate?  A lot of folks like to refer to this as “SGA Thrashing”-  sounds lovely, doesn’t it?  The wait event happens when a database uses Automatic Memory Management, (AMM) and the MMON background process decides to shrink the buffer cache and reallocate the memory to other areas of the SGA after advice from the memory advisors. A lot of DBAs haven’t been sold on AMM yet and I have to admit, I’m one of them.  I only use it in small, non-important databases and often avoid setting it for any OLTP environment.  One thing to keep in mind though, I’m more often in the EM12c world these days vs. working with memory allocation issues in DB12c, so I don’t have the experience in DB12c and multi-tenant to give an educated review of AMM with the new version.. :)

The above ASH Report should be enough to let you know that this database is impacted by current AMM settings during resize, but if you need to provide more information, you can do this in two ways:

  1. Review the Memory Advisors in EM12c.
  2. Query the V$SGA_RESIZE_OPS and V$MEMORY_RESIZE_OPS directly to see how often the resize is happening.

Here’s a simple query to start:

SELECT parameter, initial_size, target_size, start_time
FROM v$memory_resize_ops 
WHERE initial_size > = 0 
and final_size > = 0 
ORDER BY parameter, start_time;

Another choice is to set DB_CACHE_ADVICE to off and then set the db_cache_size directly.

And that concludes an example of how and why you would use an ASH report to investigate an issue in Oracle.  Have a great day and as always, do everything you can to be bulletproof!












Copyright © DBA Kevlar [How to Use an ASH Report and Why], All Right Reserved. 2015.

Video Tutorial: XPLAN_ASH Active Session History - Part 3

The next part of the video tutorial explaining the XPLAN_ASH Active Session History functionality continuing the actual walk-through of the script output.

More parts to follow.

iTunes 12.1 skips after recent update

iTunes started skipping on my PC after a recent update (end of Jan 2015). It is version 12.1 of iTunes.

iTunes skips when using Chrome, mostly.

That’s enough search-friendly lines to help the web crawlers get here. Apologies to my normal visitors, this is obviously nothing to do with Oracle databases, development or my usual ramblings. Go read something else unless you landed here looking for help with skipping iTunes playback.

iTunes on my laptop wanted to update a few days ago so I let it. Next time I fired iTunes up, I was annoyed to discover that a track I had recently uploaded, from a compilation CD I have of 60’s classics, was poor quality and skipping. Except that the next few tracks also skipped a little, old favorites I knew were fine. I did a quick web search but most of the references were 4 or 5 years old. The skipping was getting worse so I shut down iTunes and used a…. CD player.

A few days later I was working on the machine and wanted some music to ease the task in hand, fired up iTunes and it was soon skipping again. I then fired up a few extra Chrome windows (for unrelated reasons) and the skipping got a lot worse. I shut down Chrome and the skipping stopped. A bit of testing revealed that iTunes did not skip for me on an, admittedly, lightly loaded machine – but did when Chrome was up and running. Anything constantly updating in Chrome (Twitter feed or live sports update) made it worse.

My machine is quite powerful, it’s an MSi gaming machine with an Intel i7-4700 processor, 8GB of memory and a dual SSD/HDD with the OS and applications sitting on the SSD. It’s not lack of grunt that was causing the issue but something crap in what iTunes is doing.

I found a solution – This discussion – which itself links to this download for a 64-bit version of iTunes 12.1 supporting “older video cards”. My video card is not that old in the scheme of things, being a GeForce GTX 765M which was a pretty reasonable card 12 months ago.

After I downloaded the alternative version of iTunes and let it install, the skipping seems to have stopped. I’ve tested having a dozen Chrome tabs open with a couple of them being interactive and my chosen 80’s music selection was skip-free.

I hope that helps people. If you came here for the iTunes hint, don’t look at the rest of the blog, you will be sadly disappointed :-)

To N or Not to N, is SYS_OP_C2C the Question; Oracle NVARCHAR Slow?

February 8, 2015 I was recently contacted about an odd performance issue with a software product named TDM, where certain SQL statements submitted to an Oracle 9.2.x database required roughly 30 seconds to execute when a nearly instantaneous response was expected.  The person who reported the issue to me provided a sample SQL statement that […]

Functions & Subqueries

I think the “mini-series” is a really nice blogging concept – it can pull together a number of short articles to offer a much better learning experience for the reader than they could get from the random collection of sound-bites that so often typifies an internet search; so here’s my recommendation for this week’s mini-series: a set of articles by Sayan Malakshinov a couple of years ago comparing the behaviour of Deterministic Functions and Scalar Subquery Caching.

Although I’ve labelled it as “this week’s” series, I wouldn’t want you to assume that I’ll be trying to find a new mini-series every week.

Footnote 2:
I had obviously expected to publish this note a long time ago – but must have forgotten about it. I was prompted to search my blog for “deterministic” very recently thanks to a recent note on the OTN database forum and discovered both this note and an incomplete note about improving the speed of creating function-based indexes by tweaking hidden parameters – which I might yet publish, although if you read all of Sayan’s articles you’ll find the solution anyway.


Little Things Doth Crabby Make – Part XVIII. Automatic Storage Management Won’t Let Me Use My Disk For My Files! Yes, It Will!

It’s been a long time since my last installment in the Little Things Doth Crabby Make series and to be completely honest this particular topic isn’t really all that fit for a LTDCM installment because it covers something that is possible but less than expedient.  That said, there are new readers of this blog and maybe it’s time they google “Little Things Doth Crabby Make” to see where this series has been. This post might rustle up that curiosity!

So what is this blog post about? It’s about stuffing any file system file into Automatic Storage Management space. OK, so maybe this is just morbid curiosity or trivial pursuit. Maybe it’s just a parlor trick. I would agree with any of those descriptions. Nonetheless maybe there are 42 or so people out there who didn’t know this. If so, this post is for them.

ASMCMD cp Command

The cp sub-command of ASM lets you stuff certain database files into ASM. We all know this. However, just to make it all fresh in people’s minds I’ll show a screen shot of me trying to push a compressed tar archive of $ORACLE_HOME/bin/oracle up into ASM:


Well, that’s not surprising. But what happens if I take heed of the error message and attempt to placate? The block size is 8KB so the following screen shot shows me rounding up the size of the compressed tar archive to an 8192B blocking factor:


ASMCMD still won’t gobble up the file. That’s still not all that surprising because after ASMCMD checked the geometry of the file it then read the file looking for a header or any file magic it could understand.  As you can see ASMCMD doesn’t see a file type it understands. The following screen shot shows me pre-pending the tar archive with file magic I know ASMCMD must surely understand. I have a database with a tablespace called foo that I created in a non-Oracle Disk Manager naming convention (foo.dbf). The screen shot shows me:

  1. Extracting the foo.dbf file
  2. “Borrowing” 1MB from the head of the file
  3. Creating a compressed tar archive of the Oracle Database executable
  4. Rounding up the size of the compressed tar archive to an 8192B blocking factor



So now I have a file that has the “shape” of a datafile and the necessary header information from a datafile. The next screen shot shows:

  1. ASMCMD cp command pushing my file into ASM
  2. Removal of all of my current working directory files
  3. ASMCMD cp command pulling the file form ASM and into my current working directory
  4. Extracting the contents of the “embedded” tar archive
  5. md5sum(1) proof the file contents survived the journey


OK, so that’s either a) something nobody would ever do or b) something that can be done with some elegant execution of some internal database package in a much less convoluted way or c) a combination of both “a” and “b” or d) a complete waste of my time to post, or, finally, e) a complete waste of your time reading the post. I’m sorry for “a”,”b”,”c” and certainly “e” if the case should be so.

Now you must wonder why I put this in the Little Things Doth Crabby Make series. That’s simple. I don’t like any “file system” imposing restrictions on file types :)


Filed under: oracle

Friday Philosophy – How Much does Social Media Impact your Career for Real?

Does what you tweet impact your chances of getting that next interview?
Do people check out your Facebook pictures before making you a job offer?
Does my Blog actually have any impact on my career?

We’ve all heard horror stories about people losing their job as a result of a putting something “very unfortunate” on their facebook page, like how they were on holiday/at a sports event when their employer was under the illusion they were off sick, or the more obvious {and pretty stupid} act of denigrating their boss or employer. But how much does general, day-to-day social media impact your career? {“Your” as in you people who come by this blog, mostly professionals in IT. I know it will be different for people trying to get a job in media or….social media :-) }.

Two things recently have made me wonder about this:

  • The first is that I’ve been in or watched a few discussions recently (via social media!) where people are suggesting that their social media output is part of who they are seen as professionally and they make efforts to ensure they give the right impression, or have even sought professional help to improve their social media standing in respect of employment.
  • The second is that I recently was involved in some hiring and I never even thought to look at their social media. Maybe that is just because I’m over {picks an age} 30 and social media is not a massive thing to me. Most of my hiring experience was before the likes of Facebook and though I would check out a blog if it was mentioned on a CV, I would not have thought to check them out.

When I initially thought about that second point I assumed that most people hiring in the world of IT are similarly a bit ancient like myself and maybe not that attuned to social media. But perhaps I am wrong as it’s people similar to me out there on Twitter who have been worrying about such things. Maybe social media is considered by potential employees than I think? I’d like to know what anyone else thinks.

I should add that I don’t see all Social Media as the same when it comes to it’s impact on your career. I think their is Friends Social and Business Social. Something like LinkedIn is aimed fair and square at business and professional activity and is Business Social. You would really expect it to be looked at and, in fact, most people who use it would hope it is! {Mine isn’t, I get about 3 or 4 views a week and only once, 5 or 6 years ago, was I approached via it for a work opportunity}. If you blog about a work topic or tweet as an expert in your field (so your tweets are mostly about your day job, not just the odd reference) and especially if you are doing either under a company banner then, yes, I’d expect that to be taken into account when prospective employment comes up.

Social Media is most people’s twittering, personal Facebook, private blogs, Pinterest and all those dozens of things I know nothing about as I am too old and too antisocial. Do these really have much impact on your career?

I would suggest not, again for two reasons:

  • I don’t think most employers are going to look at your Friends Social Media until they have at least interviewed you, as when you are hiring you barely have enough time to check over the CV’s, let alone research each candidate’s personal history. Once you have interviewed them, then they have become a real person rather than a name and if you do check out their Friends Social Media then you will look at it in light of them being a human being, which is point 2:
  • Unless you are saying things that would make anyone think you are a bit odd or unpleasant, I can’t see that discussions of football, insulting your friends, making double entendra comments or (one of my favorites) pointless drivel about your cat is going to make anyone who you would want to work for worry about you. Some people might put up things that could be offensive to others – but then, if you really do think immigrants are ruining the UK, we are not going to get on so working together is a mistake for both of us. So maybe even stating your strongly held opinions is long-term beneficial as well. Some people take my strong dislike of children as a real reason to not like me very much. Best we don’t spend 8 hours a day, five days a week together. You’ll only bang on about your bloody kids.

What I think is a shame is that I suspect some people {many people?} self-censor themselves on all Social Media due to a concern to always be seen as professional. As good worker material. We all know that almost everyone we work with have unprofessional moments and, in fact, those few who are professional all the time tend to be… staggeringly dull.

So maybe being mindful of your professional standing is totally correct on Business Social Media but a bit of a shame if you let it impact your Friends Social Media.

But remember, on all social media there are limits. There are some things about you, Dave, that you should simply not share. Or at least, only at the pub when we are all too drunk to care.