March 30, 2011 If you take a look at the Oracle Database Performance Tuning Guide, you will find the following SQL statement to help identify the session waiting in an enqueue and the session that currently prevents that session from continuing: SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess, id1, id2, lmode, request, type [...]
You have probably all experienced situations where you get an error message from Oracle and it turns out to be a total Red Herring. Occasionally though the error messages can be really helpful. I got an email from a co-worker today (thanks Stephan) that read like this:
I was working on an external table today and fat fingered something. This is the error report-
SQL Error: ORA-30657: operation not supported on external organized table
30657.0000 – “operation not supported on external organized table”
*Cause: User attempted on operation on an external table which is
*Action: Don’t do that!
Gotta love it. They don’t actually tell us what we did wrong, but they do tell us how to resolve the issue!
Apparently some of the Oracle developers have a sense of humor.
By the way, I’m sure you already know this but there is a nifty little Oracle provided tool called oerr that spits out this information:
SYS@SANDBOX1> !oerr ora 30657 30657,0000, "operation not supported on external organized table" // *Cause: User attempted on operation on an external table which is // not supported. // *Action: Don't do that!
People talk about the Oracle SQL Developer 3 being out, which is cool, but I have something even cooler for you today ;-)
I finally figured out how to convert my screen-recordings to uploadable videos, so that the text wouldn’t get unreadable and blurry.
So, here’s the first video, about a tool called MOATS, which we have built together with fellow OakTable Network member and a PL/SQL wizard Adrian Billington (of oracle-developer.net).
Here’s the video, it’s under 3 minutes long. Play the video in full screen for best results (and if it’s too slow loading, change it to lower resolution from HD mode):
Check it out and if you like MOATS, you can download it from Adrian’s website site (current version 1.05) and make sure you read the README.txt file in the zip!
Also thanks to Randolf Geist for finding and fixing some bugs in our alpha code… Note that MOATS is still kind of beta right now…
P.S. I will post my ORA-4031 and shared pool hacking video real soon now, too! :-)
P.P.S. Have you already figured out how it works?! ;-)
Update: Now you can suggest new features and improvement requests here:
One of the problems of building models of Oracle activity is that it’s easy to build the wrong model. One of the commonest issues appears with repetitive actions – how do you write code that repeats a simple action many times in a row. It’s often enough to write a simple pl/sql loop but there are cases where a pl/sql loop behaves very differently from a long list of individual SQL statements – which is why I’ve occasionally used a very simple-minded approach to avoid that particular trap.
If you checked the directory of my database work area, you’d find an intereseting pattern of names made by five very short scripts:
q:> dir start*.sql Volume in drive Q has no label. Volume Serial Number is A8BA-79AA Directory of q:\ 28/03/2011 08:56 350 start_1.sql 13/01/2005 13:38 110 start_10.sql 13/01/2005 13:39 120 start_100.sql 13/01/2005 13:39 130 start_1000.sql 13/01/2005 13:40 140 start_10000.sql 5 File(s) 850 bytes 0 Dir(s) 82,803,621,888 bytes free
The script start_10000.sql contains just 10 lines:
@ start_1000 @ start_1000 @ start_1000 @ start_1000 @ start_1000 @ start_1000 @ start_1000 @ start_1000 @ start_1000 @ start_1000
and you won’t be surprised to find that the script start_1000.sql also contains just 10 lines:
@ start_100 @ start_100 @ start_100 @ start_100 @ start_100 @ start_100 @ start_100 @ start_100 @ start_100 @ start_100
and so on down the chain, until the script start_1.sql contains the piece of code that I actully want to run many times.
Obviously there are cases where you need a more subtle framework – but it’s very convenient to be able to write a small script holding the piece of code that you want to keep repeating and then run it 10,000 times with just a single call to: @start_10000.sql.
One MySQL Plug-in customer reported an issue with reports — all charts were displaying an Oracle error and didn’t display any data as if it couldn’t connect to the EM repository. I concluded that this has nothing to do with the plug-in itself since the extensibility framework provides no way of controlling usernames and passwords that are used to access the repository. After opening an SR with Oracle, it turned out that MGMT_VIEW user password has been changed and this causes “ORA-01017: invalid username/password; logon denied” and possibly subsequent “ORA-28000: the account is locked” when policy is set to auto lock an account after certain failed login attempts (default in 11g database).
If you face this issue, follow My Oracle Support Note 374382.1 “Grid Control Repository: How to Change the Password of the MGMT_VIEW User”.
Thanks for reporting it Teffany!
This is just a quick update (basically agreeing with Robin Moffat in the comments here and Dom Brooks here ). One of the blogs I follow is that of the optimizer team. . They’ve recently just released quick update because their excellent white papers have moved – and er there’s no redirection in place. Oracle, [...]
I am doing a couple of one day seminars with Oracle University, currently planned for Austria and Switzerland. They go by the title “Grid Intrastructure and Database High Availability Deep Dive”, and can be accessed via these links.
To save you from having to get the abstract, I copied it from the Oracle University website:
Providing a highly available database architecture fit for today’s fast changing requirements can be a complex task. Many technologies are available to provide resilience, each with its own advantages and possible disadvantages. This seminar begins with an overview of available HA technologies (hard and soft partitioning of servers, cold failover clusters, RAC and RAC One Node) and complementary tools and techniques to provide recovery from site failure (Data Guard or storage replication).
In the second part of the seminar, we look at Grid Infrastructure in great detail. Oracle Grid Infrastructure is the latest incarnation of the Clusterware HA framework which successfully powers every single 10g and 11g RAC installation. Despite its widespread implementation, many of its features are still not well understood by its users. We focus on Grid Infrastructure, what it is, what it does and how it can be put to best use, including the creation of an active/passive cold failover cluster for web and database resources. Special focus will be placed on the various storage options (Cluster File System, ASM, etc), the cluster interconnect and other implementation choices and on troubleshooting Grid Infrastructure. In the final part of the seminar, we explore Real Application Clusters and its various uses, from HA to scalability to consolidation. We discuss patching and workload management, coding for RAC and other techniques that will allow users to maximise the full potential of the package.
See you there if you are interested!
Last week the email account associated with my blog amassed no less than 83 emails from readers asking what I’m up to in response to the cliff-hanger I left in my post entitled Will Oracle Exadata Database Machine Eventually Support Offload Processing for Everything?
I appreciate all the email and I regret I was unable to answer any of them as I was taking some time away with my family.
I’ve resigned from my position of the last 4 years as performance architect in Oracle’s Exadata development organization and have joined the EMC Data Computing Division to focus on Greenplum in a performance engineering role. While this is a big and exciting news piece for me personally, I need to make this a small and quick blog entry at this time.
Filed under: oracle
What are good ways to learn about the inner workings of Oracle to troubleshoot performance and availability issues?
Fortunately there is only one good answer - just one good source - Tanel Poder's virtual seminars at http://tech.e2sn.com/oracle-training-seminars
Why? Let's examine the typical objectives and the various means to accomplish them. I'm sure you have heard this complaint before - "the database is slow". If I had a dime every time I heard it, well ... you know the rest! Most DBAs by now know the next best thing they should do - check the wait interface - V$SESSION. That's a very good first step.
From the view they figured out the session is waiting on cache buffer chain latch, and has been waiting 1234.56 seconds, measured to the precise microseconds and counting. They also got all the other data such as session Id, the statement and so and so forth. But then what? It's like a radio host reporting the stick market - x number of stocks are up, y are down, Dow is up by n, Nasdaq is down by m, and so on. All are factually correct; but none helps you in answering your question - why your specific portfolio of stocks is down and what other stocks you should consider.
Of course, you have to pay a professional to get that information. In your database issue at the moment, that's what you have to do as well - you have to be a professional to decipher the further information. And that's the vital second step. After you identified what is the ailment, you have to understand how to eliminate that. Most seasoned professionals stop at the first step when the second step is the most important. And that's where you need Tanel's highly acclaimed Advanced Oracle Performance Troubleshooting seminar. It's not based on slides; but showing the demos right in front of you, using code you can understand and reuse.
In this specific example, in the first part of his series, under the section "Latches" he shows you how to get the specifics of the latch, for instance the latch children. There are some traditional fixes, of course; but the big question is what if (and, that is a big IF) these well understood fixes do not work? What do you do next. Pray?
No; turn to Tanel's class. He explains how to get information from various sources inside the database. In this case the trick is to find out who has the latch and who is waiting for it, and what specific latch is so popular. This picture helps (from Tanel's class). Reproduced with permission:
As they say, a picture is worth thousand words. Now that you know how to find that popular latch, you are well on your way to troubleshooting. Hopefully this is all you have to do.
But what if it's not. The problem still eludes a solution? Have no fear. In his hang analysis section, Tanel talks about taking system dumps and explains how to analyze them - again with a few slides; and showing the actual trace file and interpreting the file right in front of you.
Talk about tough luck; suppose the problem is still not solved. Tanel takes a process stack dump. Yes, it is not for the faint of the heart; but with the right training anyone can do it. He shows you how to take the stack trace and analyzes one right in front of you. Here is an example of how to interpret the stack trace (reproduced with permission)
And he explains each section with how to interpret the different data to come to the solution.
At this point no problem is big enough to stand this type of scrutiny. If the problem persists, well, it's most likely a bug then. Oh, yes, Tanel will state the bugs that could affect performance issues and point to the MetaLink notes as well.
Interested? I guess more like excited. I certainly am. Tanels' first class is running April 11-15 http://tech.e2sn.com/oracle-training-seminars. It's a virtual class; so you don't have to step away from wherever you are, in whatever state you are in. As long you have the internet connection and an agile mind to absorb the superb information presented, you are in for a massive dose of superior learning.
What about the scripts Tanel uses in the class? Are they proprietary? Do you have to buy them to use them? Do you need to write them down so fast that you capture all the details? Not at all. As a part of the course, Tanel will expose his entire script library to you.
Intersted? Oops; I lied! Actually the scripts and tools are all free, even if you don't attend his class. They are at http://tech.e2sn.com/oracle-scripts-and-tools. Download to your heart's content.
So, why is Tanel giving away his hard work for free? Well, buying the best tool from Home Depot will not make me a good carpenter; I must learn how to use them to build a deck. A script is only 20% of the solution; the rest 80% is knowledge. And the 80% is what Tanel's virtual seminars are all about. Hope you make the right choice. I know I have.
I was putting together abstracts for Oracle Open World #oow11 this year and remembered something someone had asked me earlier - to present at a conference like this, how does one overcome the fear of delivering a session? In fact, it is a question asked of me several times.
This is not a trivial issue; it's a real problem. There are many folks who are otherwise excellent sources of knowledge, in fact fountains of practical ideas; but when asked to speak in front of a live audience, they would rather kiss a frog than step in from of the podium. The mortal fear of public speaking is one of the many challenges to get good speakers for conferences.
The second challenge is cost. Conferences are conducted at some physical place. Unless you live in that city, or within commutable distance, you have to travel there. Add to the plane fare, cost of hotel, food, rental car, and all that extra expenses the emotional turmoil of being away from the family, especially those folks with small children who would miss one of their parents a is not something you can just brush aside. Even if you are not a parent, you may be a caregiver to a loved one and your absence will be hard on the cared one.
Finally, the change is not something most people like. You may be comfortable with your present surroundings, among familiar people you work with every day. Traveling to a new city and spending time with strangers may be exciting or daunting based on how you look at it. Perhaps you work from home everyday in your PJs. Getting the wrinkles off the pants to go to the conferences may be a lot. At least to some people.
The answer to all this may be a trend I see developing now - web based conferences. You can attend them in your PJs and speak at them in your PJs as well; no iron needed. One such conference is #VirtaThon (http://bit.ly/hc2Vjh) where several speakers, most of them widely known in the Oracle user community are speaking. Being a virtual conference, you don't have to travel anywhere to attend the sessions; you attend from the privacy and comfort of your own home or office. Remember, they are *not* webcasts; they are virtual conference sessions. So you actually participate in the sessions as you would do in a normal conference - ask questions, interact via chat and have follow ups after talks. If you are a speaker, there is nothing better - you don't have to travel to the venue. You need a computer with an internet connection. If you are uncomfortable speaking to a very live audience, it should be much easier speaking to virtual audience.No traveling, no TSA checks and no red eye flights.
Just to pique your interest, here is just a sampling of the speakers (in no particular order)
And many more. Oh, I am speaking as well.
Interested? Submit an abstract at http://bit.ly/hc2Vjh as soon as possible. The deadline is approaching fast.
Oh, yeas, a little detail. You will not be speaking for free; you will earn money for your efforts. So, what's stopping you?