There was an interesting issue at one of the customer sites. Few tables in the database were altered and the dependent objects became invalid. But the attempts to compile the objects using utlrp.sql or manually were failing. In all the cases it was giving the same error:
SQL> alter function SCOTT.SOME_FUNCTION compile;
alter function SCOTT.SOME_FUNCTION compile
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 27
At first look it sounded like some issue with the dictionary as (more...)
I’ve been working on a lot of good schtuff lately on the area of capacity planning. And I’ve greatly improved my time to generate workload characterization visualization and analysis using my AWR scripts which I enhanced to fit on the analytics tool that I’ve been using.. and that is Tableau.
So I’ve got a couple of performance and capacity planning use case scenarios which I will blog in parts in the next few days or weeks. But before that I need to familiarize you on how I mine this valuable AWR performance data.
Let’s get started with the AWR top (more...)
Lately I’ve been busy on projects and research so I’m putting more and more stuff on my wiki/braindump… and really I need to catch up on blogging.
I have a longer draft post about SQLTXPLAIN.. but I haven’t finished it yet so I’ll just go with the quick post for now.
I’ve been using SQLTXPLAIN for quite a while, and I can say I’m a really big fan. It’s a cool tool to use for systematic SQL Tuning so I got them all neatly documented here http://karlarao.tiddlyspot.com/#SQLTXPLAIN.
BTW, Carlos Sierra, the author of the tool will (more...)
Getting tired of typing and setting your ORACLE_HOME, ORACLE_SID and troubleshooting your . oraenv ?
going/connecting to different client sites with different platforms and server environments sometimes it is frustrating just how long it takes to get a proper SQL*Plus environment
So I always make use of this script
and for every server that I access I just have to do three bits of typing:
. ~oracle/.karlenv <<– set the environment
2 <<– choose an instance
s <<– rlwrap'd sqlplus alias
see example usage below:
[root@desktopserver ~]# su - oracle
[oracle@desktopserver ~]$ vi .karlenv <-- copy (more...)
One of the easiest ways to understand something is to see a visualization. Looking at Active Session History (ASH) data is no exception and I’ll dive into how to do so with R and how I used R plots to visually present a problem and confirm a hypothesis. But first some background…
Frequently DBAs use the Automatic Workload Repository (AWR) as an entry point for troubleshooting performance problems and in this case the adventure started the same way. In the AWR report Top 5 Timed Foreground Events, the log file sync event was showing up as the #3 (more...)
I have extended the Undo usage scripts to include two additional indicators:
1) undo change vector size statistics
2) Used undo records/blocks
and support for RAC infrastructure, so you can spot the hungriest UNDO eaters for any given instance.
Then the script for Oracle 11g is as follows:
set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname (more...)
Came across this small oddity that documentation of 10.2 and 11.2 states that expdp by default takes consistent image of the database. But actually it is not so. You need to use flashback_scn/flashback_time for that. Metalink doc 377218.1 explains the scenario.
I often get asked to take a look at an Oracle eBusiness Suite concurrent request to see what it is doing, this can come from a few different angles:
- What requests are currently running?
- I have an operating system process that is taking too much CPU - what is it doing?
- Can you tell me where the request is at? I've clicked on the log but it doesn't show anything!
- My request is taking too long - can you check for blocking locks?
There are a number of strategies to track and trace where things are at for a running (more...)
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...)
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...)