Database Shutdown With crsctl

I noticed something for the first time tonight when I was playing around in the Enkitec lab – something that I have been doing wrong for a while.  When working in the lab, I often rely on the crsctl command to shut down the entire cluster stack for me.  It’s really easy to use “crsctl stop cluster -all” followed by “dcli -l root -g ~/dbs_group /u01/app/ stop crs” to get everything down (more...)

NVL() change

One of the problems of functions is that the optimizer generally doesn’t have any idea on how a predicate based on function(col) might affect the cardinality. However,  the optimizer group are constantly refining the algorithms to cover an increasing number of special cases more accurately. This is a good thing, of course – but it does mean that you might be unlucky on an upgrade where a better cardinality estimate leads to a less efficient execution plan. Consider for example (more...)

New Version Of XPLAN_ASH Utility

A minor update 4.01 to the XPLAN_ASH utility is available for download.

As usual the latest version can be downloaded here.

These are the notes from the change log:

- More info for RAC Cross Instance Parallel Execution: Many sections now show a GLOBAL aggregate info in addition to instance-specific data

- The Parallel Execution Server Set detection and ASSUMED_DEGREE info now makes use of the undocumented PX_STEP_ID and PX_STEPS_ARG info (bit mask part (more...)

SCAN VIP Troubleshooting

We had a client that was running into a strange issue on their Exadata where new connections coming in through the SCAN were failing.  After doing some troubleshooting, it was discovered that it was related to one of the SCAN listeners not properly accepting requests from new sessions.  The VIP and listener were running, and everything looked normal.

We had the following SCAN setup:

2 (more...)


One of the problems of “knowing” so much about Oracle is that the more you know the more you have to check on each new release of the software. An incoming ping on my posting “Lock Horror” reminded me that I was writing about, and the terminal release is, and the whole thing may have changed in – so I ought to re-run some tests (more...)


Here’s a little test you might want to try. Examine the following script, and decide what sort of symptoms you would see in the AWR report.

create global temporary table gtt1(n1 number);

execute dbms_workload_repository.create_snapshot;

insert into gtt1 values(1);
truncate table gtt1;

-- repeat insert/truncate for a total of 100 cycles

execute dbms_workload_repository.create_snapshot;

-- generate an AWR report across the interval.

I don’t need anyone to tell me their results – but if your (more...)

When v$sesstat statistics are updated

Craig Shallahamer wrote excellent article “When is v$sesstat really updated?”.
And my today post just a little addition and correction about the difference of updating ‘Db time’ and ‘CPU used by this session’ statistics.

Test #1

In this test I want to show that the statistics will be updated after every fetch call.
I have set arraysize=2, so sql*plus will fetch by 2 rows:
(full script)

-- Result will be fetched by  (more...)

Ben Admin Snapshot Error

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...)

Wrong Result Bug using group-by placement optimization

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

In test I (more...)

November/December Highlights

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...)

New Version Of XPLAN_ASH Utility

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...)

When error say nothing about real issue

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 - Production on Wed Sep 18 20:47:38 2013

Copyright (more...)

Oracle BI Publisher Desktop Microsoft Visual Basic System Error &H8000FFFF -2147418113 Catastrophic failure

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.


Speaking at E4 2013! … and some Exadata Patents good stuff

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...)

GoldenGate error OGG-01755 SQL error: OCI Error 26,723 = ORA-26723

I was trying to register extract  OGG v 11.2 on one of my dev servers GGSCI ( 1> dblogin  userid srvegate password xxxxx Successfully logged into database.  GGSCI ( 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

Wrong result after using join predicate push into a view with a GROUP BY

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 on OEL 5.7

I will post shortened version of the query with fake table names and columns.

SELECT p_name,
   r.status r_status
   FROM table_1 r,
       SELECT  ri.r_id,            
       MAX ( DECODE (ri.c_name,  'SPACE', ri. (more...)

ORA-01422 while compiling objects

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...)

Fast Analytics of AWR Top Events

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...)

SQLTXPLAIN quick tips and tricks and DB Optimizer VST

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

BTW, Carlos Sierra, the author of the tool will (more...)

Troubleshooting memory usage

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 ( 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...)