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...)
Last week, one of our clients had a sudden slow down on all of their applications which is running on two node RAC environment
Below is the summary of the setup:
- Server and Storage: SunFire X4200 with LUNs on EMC CX300
- OS: RHEL 4.3 ES
- Oracle 10.2.0.3 (database and clusterware)
- Database Files, Flash Recovery Area, OCR, and Voting disk are located on OCFS2 filesystems
- Application: Forms and Reports (6i and also lower)
As per the DBA, the workload on the database was normal and there were no changes on the RAC (more...)
Recently I encountered a performance problem scenario where a simple sqlplus “/ as sysdba” took about 2minutes to finish, this is critical to the client’s business because they have a local C program that loads Call Detail Reports on the database making use of local authentication for most of its operations and Sql*Loader to load the data, so this “2minutes of waiting” when accumulated greatly consumes significant time on their operations and greatly impacts the business.
When I arrived on the client I first checked the alert logs of both ASM (they have a separate home for ASM) and RDBMS, (more...)
- Networking and evaluations
Today was the first day of the second Miracle Masterclass this year: Tanel Põder has come to The Netherlands, saw a room filled with a lot of people (he called it the best seminar even before he started, just because of the amount of attendants) and made me feel I have an awful lot to explore yet. However, that is in fact a good thing and it made me get through the “trains nightmare” today with a smile: I had trouble in the morning since I left home far too late, had to cycle as fast as I (more...)