Top 60 Oracle Blogs

Recent comments

June 2011

New vs Existing Customers: Introductory Offers…

My car insurance renewal quote came through the post the other day. First thing I did was get insurance quotes from some comparison websites to see if my renewal price was reasonable. As it turned out it was in the same ballpark as the better quotes, so I figured I would accept the renewal. Before I did, I decided to get an online quote from the same company for a new policy. Low an behold, it was a little over £100 cheaper to take out a new policy rather than to renew the existing one.

I phoned customer services and asked what the discrepancy was and I was told it was an offer for new policies only. I promptly cancelled my renewal and took out a new policy with the same company and saved myself over £100.

I understand that companies do this because most customers are apathetic and will accept being ripped off for an easy life, but it stinks. Wouldn’t it be nice if companies valued your custom and gave existing customers their best deals, rather than saving them to entice new people, only to rip them off the new year. Don’t even get me started on the high-street banks…




Editing/Adding HInts in Stored Outlines

This note describes changing a hint in a SQL Outline by updating the OUTLN.OL$ and OUTLN.OL$HINTS tables directly. It shows that in the limited test case that it does affect the resulting execution plan.
CAVEAT: This is a hack, so this is more of an oddity that something that I would recommend for use in production. I am only interested in it for the limited purpose of adding a NO_EXPAND hint to an outline, that would not otherwise be included if the outline was generated in the conventional manner.

Why Outlines? 

Editing/Adding HInts in Stored Outlines

This note describes changing a hint in a SQL Outline by updating the OUTLN.OL$ and OUTLN.OL$HINTS tables directly. It shows that in the limited test case that it does affect the resulting execution plan.
CAVEAT: This is a hack, so this is more of an oddity that something that I would recommend for use in production. I am only interested in it for the limited purpose of adding a NO_EXPAND hint to an outline, that would not otherwise be included if the outline was generated in the conventional manner.

Why Outlines? 

VirtualBox 4.0.10 Released…

VirtualBox 4.0.10 released. It’s a maintenance released with a number of bug fixes. Happy upgrading! :)



KSCOPE 2011: What do you mean “Agile”?

Currently in Cary Millsap’s session about his agile approach on things called “My Case for Agile Methods“. Agile is (not yet) my thing, but knowing Cary, and he is in to it, when he is enthusiastic about something its probably one of those things which you should look into. If not even due to, as far as I know, the Agile context Cary is using is not the Agile context referred to I see being used out there. The “agile” thing out there is the one, is the one, I will joke about. But that said, a lot of methods are not bad at all, but people implement them wrongly so trying to keep an open mind, this session of Cary was more or less mandatory to get my vision about this back on track once more.

Cary also mentioned this emotion that probably mainly goes around in the DBA world. But as Cary mentioned during his presentation, “Agile is not undisciplined”, so if it gets the wrong emotional context, then is mainly due to people not doing it correctly. Could be thats it has to do with not being correctly trained in Agile or maybe incorrectly “managed”. So what is Cary’s feeling about this, that is, “Agile” as is…

Incremental Design… “Plans fail, bit there are ways to prevent a failed plan from failing your project…” so you can prevent this by continuously design, build and construct your project. The main key here is “continuously”. So for example, you don’t design your house and then leave the project, but should continuously design and iterate on your design as needed by a customer. This counts, is needed, for every stage, so the design, build and construct part.

Rapid Integration… “The worst software in the world? …90% complete, but nobody can run it yet…”. So if you want incremental design to be implemented quickly is really a step to support this continuous integration regarding bringing in all those improved, new, altered designs, build and construct tests

Test-First Programming
… “Ever been afraid to improve your code?”. So how does test-first programming work?

  1. Add a case
  2. Add a test
  3. Run all test (and check off all tests and see what fails or not…)
  4. Write code
  5. Run all tests (and make sure it now all succeeds)
  6. Refactor

Pair Programming… Are you stuck? Not in the mood? Are you skipping steps? The fun part Cary here describing is that he is aware of how his office furniture is placed. It turned out that it is in such a way that its supports the buddy part where your buddy (wingman) can look at your code or comment on your code during your programming. Also your buddy can back you up when your stuck or tired. Of course its also more creative in the end due to the fact that you push each other in more creative and productive ways while doing your tasks, like programming.

Ten-Minute Build… This will mainly keep your energy up to create the best as you can do. You can’t continuously keep up the high level of concentration and if you can’t keep your pace your code level will deteriorate…

So keep in mind, if “Agile” looks stupid then most of the time its not the method that is “stupid”, but that it is implemented “stupid” by people. I indeed really believe that to make Agile work, that you need smart and disciplined people to make this work and a “customer” that continuously interacts with the team. Getting the hang of “it”, I indeed believe that most of the laughable stuff out there, is due to people, but then again, isn’t most IT/software/method out there based on what people do?. Its people, good people, that make it work, with a proper understanding of what the goals are you want to achieve…

I run into lately regarding a big project, if you are implementing very restrictive security rules in your development environment, then what is the “security goal” of doing this? If there is no balance into this kind of thinking, in the process, then its destructive to the overall goal. In such environment, probably, Agile methodology shouldn’t be applied in the first place. Think outside the box and give “Agile” a go, it might surprise you, but don’t underestimate the energy, flexibility, that is needed to implement it from each and every team member and the environment you work in.

There was more in Cary’s presentation, but have a look at Cary’s website, where most of this is way better explained anyway and a place to get into on topic discussions…


A couple of days ago one of the replies to a question on the Oracle-L listserver suggested using “Jonathan Lewis’ do_ddl function”. This seemed a little odd to me, as I had no idea when my do_ddl function was. After a little search through the archives, though, I discovered that this was a quick and dirty piece of pl/sql I had invented 7 years ago as a possible method of avoiding error  ”ORA-00054″.

The suggestion was in response to a posting from Jeremiah Wilton:

Sometimes when trying to perform DDL on really hot objects (heavy read/DML), I get:

ORA-00054: resource busy and acquire with NOWAIT specified.

I guess this is because you need to obtain a library cache lock in order to change the object definition. Since there is no enqueue mechanism for this resource, you can’t just ‘lock table … in exclusive mode;’ All that gives you is a DML lock.

One way to avoid this is to write a PL/SQL routine that uses DBMS_SQL and spins trying to run the DDL, stopping only when it succeeds. This seems to work most of the time.

Does anyone have a script for doing the above that they would like to share?

This is what I came up with:

create or replace procedure do_ddl(m_sql varchar2)
        in_use exception ;
        pragma exception_init(in_use, -54);
        while true loop
                        execute immediate m_sql;
                        when in_use then null;
                        when others then raise;
        end loop;

Seven years later I think it’s probably worth pointing out that you might want to count the number of times you fail and then give up rather than retrying indefinitely – but apart from that, you might find this a useful little hack for rare occasions (but probably shouldn’t use it for regular production activity).

If you follow the original question, by the way, you will see that Mark Bobak has suggested that do_ddl is redundant in 11g because you can set the  ddl_lock_timeout parameter to address the problem.

Presenting in IOUG Webinar: RAC Performance tuning – Private inter connect

I will be presenting in IOUG Webinar on Thursday June 30, 2011 11AM-12Noon CDT. Hope you can join me.
Register at Private interconnect .
I know, I have not been blogging more actively, but I have been working on books and few presentations lately. Hopefully, you will see more blog entries soon.
Thanks for reading my blog.

Give Me a Hint – How were These Autotrace Execution Statistics Achieved?

June 27, 2011 I recently received an email asking why different performance is achieved when a FIRST_ROWS hint, FIRST_ROWS(100) hint, and an unhinted version of the query are executed.  This seems to be a simple problem, yet it might also be an interesting problem.  I thought that it might be helpful to transform my response into a blog article (allowing the [...]

KSCOPE 2011: Sunday’s Symposium

Sunday’s Conference day started very early at around 07:30 by going straight to registration, then on to a quick breakfast and the first session at 08:30 AM. Joel Kallman of the APEX Development team started my round of that morning by explaining all the tools and processes which are used / were used by the APEX Development teams that is located more or less around a huge amount of different places in the US, Europe and India. Not even to mention al the supporting people on topics like security, creating manuals, testing, supporting etc, etc. Fun to see that the team uses tools like Hudson, Subversion and Oracle’s conference tooling to be able to work wherever you as a team member are located.

Kris Rice talked about new and sometimes not so known features in SQL Developer 3.0. It was cool to see, while demo-ed by Kris, which features went in, in this new version. Stuff like drag & drop tables into a worksheet that give you a wizard for insert, update, delete etc and join options that automatically generates the SQL you need as a basis or the final statement needed. There are a lot of new options in there, I don’t know really which ones, because I started really using SQL Developer now in a project from version 3.0 “onwards” but, although I am not that GUI guy, I really start to like it. Not sure if it already will replace my UltraEdit tool, but it is getting there for some of those features like explain plan, autotrace, doing a “DIFF” between a baseline environment and a development user schema. Something I also use very much is all those fast wizards to create “load” and/or “unload” features in CSV, insert statements, SQL*Loader format or, for example (there are lots more), if needed, secured pdf file format. Something I didn’t notice yet is that you now also can open Oracle trace files and SQL Developer nicely formats the output. You also have the option to sort or filter this trace output in SQL Developer. Nice one. Also still a lot of nice stuff to come like Scheduler wizards that help you graphically layout the Scheduler process.

To give you another small teaser of what you were missing out on, have a look at the peak of the “SQL Developer’s Build in Logging and Monitoring presentation of Kris later on in the afternoon…and although not everything went that smoothly, Kris demo’s some of the power which has been build-in nowadays in SQL Developer V3.

Sue Harper demoed and talked about the SQL Developer Data Modeler, as always, very passionately and justified… SQL Developer Data Modeler is a great tool to design or getting fast info about an existing database environment via reverse engineering. At least for me thats a great plus… SQL Developer Data Modeler is nowadays even a free product, that is “a no cost option”, so in other words “free” as long as you have licensed your database. You can use it as s stand alone product or as a embedded option via SQL Developer. I would really like in SQL Developer as in SQL Developer Data Modeler an option like WebDAV, so it would/could be using XDB Repository options (XMLDB) to store XML related info like metadata stored, in XML, data. Said that most options are integrating with Subversion like looking up the differences between different saved designs. I can’t loose the feeling though that I could might make sense to store, handle XML related data in the database, instead on a file system (or even Subversion), for a tool set that pinpoints on database design and database development and its in there so why not use it. It could make even more sense regarding saving design in the same database as the development /code and options you would have like, backup & restore, oracle (text) search options or XML search via an XMLIndex and / or using XML diff’s.

Tom Kyte’s session started with pointing out all those tools you already have for free, what’s out there for free at least say, those “no cost options”, that are already there in the database and how important it is to gather some info to be able to further improve you environment if it is the code in the database or if there are problems outside the database. So the session mentioned and demo’d items like DBMS_APPLICATION_INFO, DBMS_MONITOR and DBMS_TRACE. To give you a small peak of the presentation have a look at the following short 10 min. Tom Kyte video I taped during the session.

Hope you enjoyed this small overview of the things that went on during Kaleidoscope’s 2011 Sunday’s Conference day.


KSCOPE 2011: Combining Work with Fun

Finally arrived on Friday in Long Beach after a long flight. On Saturday we (in the end: Alex Nuijten, Patrick Barel and Roel Hartman) were very lucky to be asked by Aris Prassinos, who is living and working in the Los Angeles area, if we were interested in a small tourist by car tour for stuff like: Hollywood Blvd, Walk of Fame, Malibu, Sunset Blvd, Universal Pictures etc. So just to show you the area of this years Kaleidoscope 2011 Conference hereby some pictures from, amongst others the places mentioned above plus area’s like Mulholland Drive

One of the old movie theaters in LA, USA
The place to be when the
No honey here (Walk of Fame, LA, USA)
Mickey Mouse, Walk of Fame, LA, USA
Walk of Fame, LA, USA
Errh ! (aka
Outside Universal Studio's, LA, USA
Universal Studios, LA, USA
Los Angeles as seen from Mulholland Drive
The Hollywood sign as seen from Mulholland Drive, LA
The conference center in Long Beach, CA, USA
View on the harbor, Long Beach, CA, USA
View on the harbor, Long Beach, CA, USA
Renaissance Hotel, Long Beach, California, USA
Walk of Fame, Los Angelas, CA, USA
Walk of Fame, Los Angelas, CA, USA
Kermit the Frog, Walk of Fame, Los Angelas, CA, USA
El Capitan, LA, The Movie Theater Disney show its new pictures first
View from one of the elavators from the Renaissance Hotel, Long Beach, CA
View outside the Renaissance Hotel, Long Beach, CA
Patrick Barel and Roel Hartman waiting
Alex Nuijten and Roel Hartman waiting
Full star trek cast
Nerdy geek stuff