Earlier I did a presentation at VirtaThon which is the same topic that I presented at Hotsos 2011.. Mining the AWR and Capacity Planning are very dear to my heart and up until now I’m using every research I did on that presentation to work on an “Exadata Provisioning Tool” which I’m planning to present at the next Hotsos 2012… well, the only thing that’s different this time is.. my attendees are virtual geeks all over the world
I was at the Virtual Room #100, and the staff earlier were really helpful and most of all I had fun (more...)
If you are running 10.2.0.2 in your production environment check document [ID 4604970.8] on Oracle support site.
This week colleague noticed that query, he wanted to use for some reports, returns different results than he expected. He made some additional checks using Microsof Excel and confirmed that something is wrong. He was getting incorrect results.
My first suspicion was that probably something is wrong with query or maybe logical corruption happened.
Example of the queries (I’ve changed names of the table/columns):
-- partitioned_table (date range partitioned table)
create table temp_tab_2011
select col, date_to, sum (col1) (more...)
We had serious problems with Oracle TNS Listener last few days. Problems were happening on very critical system - most active OLTP database.
Every few hours/minutes, in irregular intervals, TNS listener suddenly crashes which forces our Red Hat Cluster Manager to restart instance and even switch database to another node. Business systems that have relied on this database were experiencing great difficulties.
It is worth to mention that everything worked without any problems for about 2 years without any intervention on OS or database configuration.
OS: Red Hat Enterprise Linux ES release 4 (64bit)
DB: Oracle EE 10.2. (more...)
I've received following error while trying to perform full database export using datapump to NFS:
Export: Release 10.2.0.4.0 - Production on Tuesday, 31 May, 2011 11:27:50
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.4.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/backup-nfs/testdb/export/exportfull.dmp"
ORA-27086: unable to lock file - already in use
Linux Error: 37: No locks available
Additional information: 10
In this case problem was lying in NFS locking deamons - on most Unix-like (more...)
Dave Abercrombie mailed me earlier that the May 2011 NoCOUG Journal is already out and can be viewed online at http://www.nocoug.org/Journal/NoCOUG_Journal_201105.pdf.
I met Dave at Hotsos 2011 when he attended my presentation.. apparently we have the same interest about Statistics, Linear Regression, AWR, AAS and he’s got awesome blogs about it at http://aberdave.blogspot.com. Also being the Journal Editor at Northern California Oracle Users’s Group, he invited me to write a short article about the question:
Why is my database slow?
The NoCOUG Journal having a very wide readership with the last issue being downloaded around 6000 times from (more...)
For all that loves Oracle performance.. Hotsos is truly the best conference, all the speakers are performance geeks, all the attendees talks about performance. Everything is about performance! There are lot more stuff that I like about my first Hotsos experience, the following are some of them..
I like that presenters and attendees are curious about what each performance geek has to say..
I like that whenever their brains are already fried. They consume as much coffee/soda as they can.. and just lay their butts on this couch.. and still.. talk about performance.
I like that after all the sessions, (more...)
I just got back in the office from a 2 week conference + vacation (SFO,WAS,NY). Then I was finally back in shape to work and do the usual geek stuff again but suddenly my Neo laptop suddenly stopped working! (the one I mentioned here, but it’s now on Fedora)
It can’t even boot to BIOS, certainly a case worse than BSOD.
So after fiddling with the laptop and systematically ruling out other component failures (power cable,monitor,memory,HD), Yes it’s much like troubleshooting an Oracle database! … we decided to bring it to the service center.
But wait! it may (more...)
There are so many things to blog about these past few days. That is mainly about the cool stuffs around OCW and OOW, sessions that I have attended (OCW, unconference, OOW), plus the interesting people that I’ve met on various areas of expertise.. So I’ll be posting some highlights (and a lot of photos) on the next posts.
Last Monday (Sept. 20) I was able to present at the Oracle Closed World @ Thirsty Bear. The full agenda is here http://www.amiando.com/ocw.html?page=434169
I did my presentation after Tanel’s live hacking session. There were 30 (+-5) attendees and (more...)
From time to time I create enhancement requests or bugs, commonly on Oracle eBusiness Suite. Additionally I come across enhancement requests that I think deserve more visibility. This page is dedicated to that cause. If you have an Oracle Enhancement Request (ER) or Bug you would like to raise the visibility on, please feel free to comment or contact me.
If you like the looks of one of the Enhancement Requests noted below, log a Service Request (SR) on My Oracle Support (MOS) and ask for your MOS Customer record to be attached to the ER / Bug.
On this previous blog post I was able to take advantage of the AWR repository particularly the DBA_HIST tables to have a far better workload information and nice correlation of the Database Server’s Capacity, Requirements, and Utilization on a single output… and yes… easily going through all the SNAP_IDs!
Having this info made it easier for me to notice trends (text or visualization) and play around with the data (some statistics out of it)… which I can say a big help on finding the root cause of the problem (here and here)
Before going any (more...)
On my post about observing the Exadata V1 I had an interesting comment posted by Mark Seger (author of collectl and collectl utilities) about the correlation of activities across a system, the sample and snap time, and seeing the state of the subsystem before and after…
The comment made me curious about the effect of snap intervals on the performance numbers of the datafiles and block devices.. especially on the latency numbers.. so I made a few test cases and created some scripts that would give me 5 seconds, 10 minutes, and 60 minutes output of (more...)
It’s been a while since I visited the Sun Video RSS feed and I found some interesting videos related to performance that are worth sharing and something you could watch over a big cup of coffee..
First is titled Performance: The “Not a Problem” Problem which I could also relate when doing performance analysis for example…
- When a client instantly jump into conclusion that the performance degradation is caused by the database link well in reality when everything is measured/profiled it turned out that it’s because of the slow IO subsystem..
- Or when the Data Guard environment was being blamed (more...)
Optimizer Hints like Index, Cardinality, Ordered ... are local settings to a SQL statement. Local means the context given is the context of the SQL statement, the scope is local. If you query a View you cannot use for example an Index hint because the table names and aliases are hidden behind the view. So normal hinting would not work without changing the view to add a hint.
Are you running Oracle E-Business Suite (EBS) / Applications and want to get an operating system level environment variable value from a database table, for example for use in PL/SQL? Or perhaps to default a concurrent program parameter? Didn't think environment variables were stored in the database?
Try out out this query that shows you $FND_TOP:
where variable_name = 'FND_TOP'
and concurrent_process_id =
( select max(concurrent_process_id) from fnd_env_context );
Or did you want to find out the Product "TOP" directories e.g the full directory path values from fnd_appl_tops under APPL_TOP?
Been busy these past few days..
Well, this post is about taking advantage of the built in data store that started in 10gR1.. yeah it’s AWR, and it’s very much like a “Statspack on steroids” (got that from Kyle Hailey’s modules). What’s exciting about 10g and above is we could utilize the “Time Model” and the OSSTAT view and use them together with the SYSTAT view to have a far better workload information when going through all the SNAP_IDs.
I have this “little” issue before of getting lazy when generating multiple AWR reports… yeah it’s just so daunting, you’ll get (more...)
Revisited: Following the upgrade from Metalink to My Oracle Support (MOS) I've updated the Note and Bug search engines (files oranote.xml and orabug.xml) per my prior post.
Revisited again 30-NOV-2010: Following the ARU change I've updated the Patch search engine (file orapatch.xml) per my prior post.
Navigate directly to a specific Oracle Patch, MOS/Metalink Note or Bug, speeding things up & sidestep that Flash! You gotta know the Patch/Note/Bug number you wanna get to:
Disclaimer: This page may become out of date very quickly!
Only a couple of days of Metalink access left, with the change over to full My Oracle Support due on Friday - 6 November 09.
For me this is a somewhat sad occasion. Metalink has been around for such a long time, and has been a great companion, it will be a shame to see it go.
We now herald in the era of MOS (My Oracle Support). And of course, with any shiny new thing, there have been discussions and more discussions. With that debate there has been (more...)
Ver este articulo en Español
Datamarts/Datawarehouse performance issues can be scaring events, taking into account the massive amount of data they handle; the tuning task is even more daunting if they're used for operational day-to-day decision making.
During the last week and days, I've been involved on a 15 Tb datamart with performance issues on dataloading, not querying. Tablespaces for indexes and data share filesystems, nowadays this is not a concern anymore, however I prefer to encourage customers to allocate independent storage for each type, that way they are able to discriminate the source of IO.
They have concurrent schedules (more...)
So you're working with Discoverer 10g integrated with the Oracle eBusiness Suite on Release 12. You've installed and set everything up per Metalink/MOS Note 373634.1 "Using Discoverer 10.1.2 with Oracle E-Business Suite Release 12" plus created a custom application and responsibility to have it's own menu items corresponding to your Discoverer Workbooks/Worksheets.
You login to your new responsibility and click on your new menu entry that you created per Metalink/MOS Note "How to Create a Link to a Discoverer Workbook in Apps R12" and what do you get when you query subledger data such as Payables Invoices, (more...)
Recently, my client deployed a new application and had this intermittent “Deadlock Storm” …
A trace file was sent and I was able to pinpoint the cause of the deadlock and the session that caused it.
The deadlock was a TX enqueue with mode of 4 (S – share) which could be verified by looking at the following lines of the Process State dump:
last wait for 'enq: TX - row lock contention' blocking sess=0x 7000000cb239d60 seq=7849 wait_time=2929705 seconds since wait started=3
name|mode=54580004, usn<<16 | slot=a0028, sequence=283f2
the “enqueue and lock mode” is explained as:
mode=54580004 (see above)
5458 (hex) (more...)