Sitting in an airport, waiting for a plane, I decided to read a note (pdf) about Flashback data archive written by Beat Ramseier from Trivadis. I’d got about three quarters of the way through it when I paused for thought and figured out that on the typical database implementation something nasty is going to happen after approximately 3 years and 9 months. Can you guess why ?
It’s all about smon_scn_time – which normally records one (more...)
I ran into this error with the Ben Admin Snapshot process. It was the second time I ran into it, so I thought that I had better make sure that I got this in my notes so I would remember it.
Here’s the error…
Costs Not Found For Pgm/Plan/Optn: <benefit program>/<plan type>/<option code>.
Application Program Failed
In Pgm Section : MOVE-TO-PDEFN
Application Program Failed
In Pgm Section : TABLE-ACCESS(PSPBATBL)
Application Program Failed
In Pgm Section (more...)
Last week I’ve mentioned on Twitter
that we ran into wrong result bug
. We found workaround quickly but I’ve decided to spend some time to reproduce error and write blog post to warn you about this optimizer behavior.
Special thanks to my colleague who spotted odd results which led us to this finding.
My test (virtual) environment is:
OS: Oracle Enterprise Linux 5.8
DB: Oracle EE 126.96.36.199.12
In test I (more...)
In the Oracle technical universe, it seems that the end of the calendar year is always eventful. First there’s OpenWorld: obviously significant for official announcements and insight into Oracle’s strategy. It’s also the week when many top engineers around the world meet up in San Francisco to catch up over (more...)
A new version of the XPLAN_ASH tool (detailed analysis of a single SQL statement execution) is available for download. The previous post
includes links to video tutorials explaining what the tool is about.As usual the latest version can be downloaded here.
The new version comes with numerous improvements and (more...)
We want restore backup from source. So we took backup from source and copied it to /dba/share/MYDB on target. My collouge sent me email saying restore failed with following error
rman target /
Recovery Manager: Release 188.8.131.52.0 - Production on Wed Sep 18 20:47:38 2013
Previously i wrote about 2 undocumented cases when oracle invalidates result cache:
1. “select for update” from table with commit;
2. deletion of unrelated rows from parent table if there is unindexed foreign key with “on delete cascade”.
Later I found another case, but forgot to post:
If there are (more...)
Not too sure what I changed on my laptop, possibly just installation of another product or perhaps Microsoft or similar updates, but when I went to edit a BI Publisher RTF template I got the following error message on clicking ANY button in the BI Publisher plugin for Microsoft Word.
I’ll be co-speaking with Tyler Muth at E4 and we will be talking about Performance Visualization… some of you may already know that I’m really a big fan of Tableau and just to give you an example on what the tool can do I’ve consolidated all the different viz that (more...)
I was trying to register extract OGG v 11.2 on one of my dev servers GGSCI (tpadldb10.gratiscard.com) 1> dblogin userid srvegate password xxxxx Successfully logged into database. GGSCI (tpadldb10.gratiscard.com) 2> register extract testext database and run into error above : OGG-01755 SQL error: OCI Error 26,723. See Extract user privileges in the Oracle GoldenGate for … Read more
Yesterday colleague reported that he’s receiving wrong/odd result from query so we decided to examine closely what's happening. Query had more then 150 lines with inline view, group by clause, several outer joins, function, decodes and cases included. So the first step was to exclude all the stuff that wasn’t important for clearer diagnosis.
Environment: Oracle EE 184.108.40.206 on OEL 5.7
I will post shortened version of the query with fake table names and columns.
FROM table_1 r,
MAX ( DECODE (ri.c_name, 'SPACE', ri. (more...)
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...)
Last few days we had significant load problems with one of our database servers. Server was choking under minor database load which caused applications to be non-responsive.
This is 8-core 32-bit OEL5 (2.6.18-92.el5PAE) server with 4GB RAM running two Oracle 10g (10.2.0.4) SE databases. SGA's of those databases where 1,2 GB and 700MB with pga_aggregate_target 64M and 128M.
As databases were small (under 50G) and not very active I didn't expected any performance problems.
Using vmstat, mpstat, top and sar tools I've noticed that we were having slight issues with swapping, despite that there (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...)