Advanced Oracle Troubleshooting Guide – Part 12: control file parallel reads causing enq: SQ – contention waits?

Vishal Desai systematically troubleshooted an interesting case where the initial symptoms of the problem showed a spike of enq: SQ – contention waits, but he dug deeper – and found the root cause to be quite different. He followed the blockers of waiting sessions manually to reach the root cause – and also used my @ash/ash_wait_chains.sql and @ash/event_hist.sql scripts to extract the same information more conveniently (note that he had modified the scripts to take AWR snap_ids as time range parameters instead of the usual date/timestamp):

Definitely worth a read if you’re into troubleshooting non-trivial performance problems :)

Pillars of PowerShell: Profiling


This is the fourth blog post continuing the series on the Pillars of PowerShell. The previous post in the series are:

  1. Interacting
  2. Commanding
  3. Debugging


This is something I mentioned in the second post and can be a great way to keep up with those one-liners you use most often in your work. A profile with PowerShell is like using start up scripts in an Active Directory environment. You can “pre-run” things on a domain computer at start up or when a user logs into the machine. In a PowerShell profile you can “pre-load” information, modules, custom functions, or any command you want to execute in the PowerShell console. There is a separate profile for the console and then for PowerShell ISE. Your profile is basically a PowerShell script saved into a specific location under your Documents folder. The path to this profile is actually kept within a system variable, most notably called, $PROFILE.

Output of the $PROFILE variable

Output of the $PROFILE variable

I am using a Windows Azure VM that I just built, so I have not created any profiles on this machine. The path is kept within this variable but that does not mean it actually exists. We will need to create this file and the easiest method to do this is to actually use a cmdlet, New-Item. You can use this cmdlet to create files or folders. You can execute this one-liner to generate the PowerShell script in the path shown above:

New-Item $PROFILE -ItemType File -Force


Now, from here you can use another cmdlet to open the file within the default editor set to open any “.ps1″ file on your machine, Invoke-Item. This might be Notepad or you can set it to be the PowerShell ISE as well. Just execute this cmdlet followed by the $PROFILE variable (e.g. Invoke-Item $PROFILE).

One of the things I picked up on when I started using my profile more often was you can actually format your console. More specifically, I like to shorten the “PS C:Usersmelton_admin” portion. If you start working in directories that are 3 or 4 folders deep this can take up a good portion of your prompt. I came across a function that I truthfully cannot find the original poster, so sorry for not attributing it.

function prompt
if($host.UI.RawUI.CursorPosition.Y -eq 0) { "<$pwd> `n`r" + "PS["+$host.UI.RawUI.CursorPosition.Y+"]> "} else { "PS["+$host.UI.RawUI.CursorPosition.Y+"]> "}

Any function you save in your profile that performs an action you can call anytime in the PowerShell console, once it is loaded. However if I want that action to take effect when it loads the profile I simply need to call the function at the end of the profile script. I just add these two lines and ensure they are always the last two lines of my profile, anything added will go between the function above and these two lines:


I use the clear command (just like using cls in the DOS prompt) to just get rid of any output a function or command I have may cause; just starts me out on a fresh clean slate.

If you want to test your profile script you can force it to load into your current session by doing this: .$profile. That is enter “period $profile” and just hit enter. You will need to take note that since I use the clear command in my profile if any cmdlet or one-liner I add outputs an error you will not see it. So when I have issues like this I simply comment the line out of my profile. You can put comments into your script using the pound sign (#), putting that before a command will allow it to be ignored or not run.


PowerShell is a security product by default, so in certain operating system environments when you try to run your profile script above you may have gotten an error like this:





This means pretty much what it says, execution of scripts is disabled. To enable this you need to use the Set-ExecutionPolicy cmdlet with a few parameters. You can find the documentation for this if you want by looking at the “about_Execution_Policies” in PowerShell or follow the link in the error. The documentation will explain the various options and policies you can set. The command below will allow you to execute scripts in your console and let it load your profile scripts:

Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned


In this post I pointed out the following cmdlets and concepts:

  • New-Item
  • Invoke-Item
  • Commenting in your script
  • Set-ExecutionPolicy

These are fairly basic areas of PowerShell and putting each one into your favorite search engine should lead you to a plentiful list of reading material. This post by no means encompassed all the potential you can do with Profiles, but was simply meant to get you started, and hopefully excited about what can be done.

Oracle Midlands : Event #9

Oracle Midlands Event #9 is now confirmed with another great lineup!


Thanks to Red Stack Technology for sponsoring the event, allowing it to remain free!

Put the date in your diary. See you there!



Oracle Midlands : Event #9 was first posted on April 24, 2015 at 10:32 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

WordPress 4.2 “Powell” (and 4.1.3)

wordpressOvernight I got an auto-upgrade to WordPress 4.1.3 maintenance release and a notice telling me WordPress 4.2 “Powell” was released. The downloads are in the usual places, and of course available from the update screen in your blog.

The announcement page has a video explaining the new features, which seem to make it easier to repost other people’s content, where you would normally expect to just tweet a link. Not sure what I feel about this yet!

Happy upgrading.



WordPress 4.2 “Powell” (and 4.1.3) was first posted on April 24, 2015 at 7:35 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Singapore Maths Question Solution and Very Interesting Observation (The Trickster)

OK, time to reveal the solution to the somewhat tricky Singapore maths exam question I blogged previously. Remember, there were 10 dates: May 13   May 15   May 19 June 13   June 14 July 16   July 18 August 14   August 15   August 16 Bowie only knew the month of my birthday, Ziggy only knew the day. Bowie […]

Golden Oldies

I’ve just been motivated to resurrect a couple of articles I wrote for DBAZine about 12 years ago on the topic of bitmap indexes. All three links point to Word 97 documents which I posted on my old website in September 2003. Despite their age they’re still surprisingly good.

Kickstarting After a Failed Addition to the AWR Warehouse

A common issue I’ve noted are dump files generated from the AWR Warehouse, but upon failure to transfer, the dumpfiles simply exist, never upload and the data is stuck in a “limbo” state between the source database, (target) and the AWR Warehouse.  This can be a very difficult issue to troubleshoot, as no errors are seen in the actual AWR Warehouse “View Errors” and no data from the source is present in the AWR Warehouse.


Empowered by EM Jobs

If you go to Enterprise –>  Jobs –> Activity and inspect the Log Report after a search for %CAW% jobs that perform the extraction, transfer and load that experienced a problem, you will then be able to view the error and can inspect the details of the issue.



If you double click on the job in question and note in the example above, you’ll notice that the jobs have the naming convention of CAW_RUN_ETL_NOW.  This is due to a force run via the AWR Warehouse console, (Actions –>  Upload Snapshots Now.)  If the job was a standard ETL run, the naming convention will be CAW_TRANSFER.

The job is a multi-step process, so you will see where the extraction step failed.  This is a bit misleading, as the previous failure that set into motion was a preferred credential issue that stopped the transfer step to the AWR Warehouse.  If you look far enough back in the jobs, you’ll find the original error, but now we are stuck in a loop-  the source can’t go forward once the credentials are fixed and yet it’s difficult for someone unfamiliar with the process to know where it all went wrong.  The first recommendation is often to remove the database and re-add it, but in this scenario, we are going to kickstart the process now that the credentials have been fixed.

Digging into a Job Error




As we stated earlier, you’ll see in the steps, that the extract failed, but the transfer would have been successful if a file had been created.  Double click on “Failed” to see the error that occurred:



In the SQL output, the error states, ORA-20137:  NO NEW SNAPSHOTS TO EXTRACT

Now we know that no files have been uploaded to the AWR Warehouse, yet, the logic written to the AWR Warehouse package that is in the DBNSMP schema in the source database thinks it’s already pulled all of these snapshots to an extract and created a dumpfile.  This error is very clear in telling the user what is going on.  There is a data telling the ETL process the data ALREADY has been extracted.

Disclaimer:  This solution we are about to undertake is for a BRAND NEW ADDITION TO THE AWR WAREHOUSE ONLY.  You wouldn’t want to perform this on a source database that had been loading properly and then stopped after successful uploads to the warehouse, (if you have one of those, I would want to proceed differently, so please keep this in mind before you attempt this in your own environment….) This fix is also dependent upon all preferred credential issues to be resolved BEFOREHAND.

The Kickstart

To “kickstart” the process after a failure, first, verify that there are no errors that aren’t displaying in the console:

select * from dbsnmp.caw_extract_metadata;

Next, gather the location for the dumpfiles:

select * from dbsnmp.caw_extract_properties;

There will be one line in this table-  It will include the oldest snapID, the newestID and the location of the dumpfile, (often the agent home unless otherwise configured.) This is the table the logic in the package is using to verify what has been already extracted.  We now need to remove this tracking information and the pre-existing dumpfiles created in the previous failed processes:

  1. Make a copy of this table, (create table dbsnmp.caw_extract_prop2 as select * from dbsnmp.caw_extract_properties;)
  2. Truncate dbsnmp.caw_extract_properties table.
  3. Delete the extract, (dumpfiles) from the directory shown in the caw_extract_properties table.  Don’t remove anything else from that directory!
  4. Log into the AWR Warehouse console.
  5. Click on the source database you just scoured of the ETL extract files.
  6. Highlight the database, click on Actions, Click on “Upload Snapshots Now.”
  7. View the job via the link displayed at the top of the console and monitor to completion.
  8. Once the job has succeeded completely, remove the dbsnmp.caw_extract_prop2 table and the backup files you moved that were created earlier from failed extracts.

You should now see successful upload jobs from this point on in the job, along with data in your AWR Wareshouse:


Tags:  ,

Copyright © DBA Kevlar [Kickstarting After a Failed Addition to the AWR Warehouse], All Right Reserved. 2015.

When bad form design attacks!

Take a look at these two items from a form I was using today.


If, like me, you thought that checking those boxes meant you agreed with the statements, you would be wrong. It turns out what they mean is you’ve remembered to copy that text into the main body of your request. WTF?

I suggested to the company in question, they either make the check boxes work as they should, or at least change the text to explain what they are there for! It amazes how people can turf out pages like this and not see how blatantly bad they are!

As it turned out, the person who this DMCA takedown notice was directed at has already taken down the offending pages, so I don’t need to “correct” the notice and resend it.



When bad form design attacks! was first posted on April 21, 2015 at 5:20 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

WordPress 4.1.2

WordPress 4.1.2 has been released. It’s a security release and I’m guessing most of you will pick it up passively using the auto-update features, so it’s nothing to worry about too much.

You can read about the fixes in this release here.

I’ve just installed it on 5 separate installations and there were no dramas.



WordPress 4.1.2 was first posted on April 21, 2015 at 5:02 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.