Search

Top 60 Oracle Blogs

Recent comments

June 2011

Knowing what you want to achieve before thinking of how to achieve it – a query optimization example

Today I received a question which was a good example of systematic problem solving approach. It was about getting a long-running query to run faster. It took a long time as the correlated subquery in the query was not unnested, was re-visited many times, causing the whole subquery subtree in the plan to be executed again and again). The main part of the question was this:
Is there a way to avoid “NOT IN” conversion to “NOT EXISTS” by optimizer … My sub query, I would like it to be fully instantiated as view and then execute it as a Hash Anti join.

Scalar subquery unnesting

Here is a nice example of what Oracle 11.2.0.2 is able to do with a subquery inside an expression. It can unnest it – that is a new 11.2.0.2 feature of the transformation part of the CBO.

Proof

There are those who understand the importance of evidence, and those who rely on other kinds of proof.

(You might want to turn your speakers down, or off, before following the link.)

Find Objects with the Greatest Wait Time – What is Wrong with this Quote?

June 28, 2011 While reading the alpha edition of the “Oracle Database 11g Performance Tuning Recipes” book, I noticed a couple of interesting SQL statements in Recipe 5-17 that just did not look right.  One of those SQL statements, slightly reformatted, appears below: SELECT   A.CURRENT_OBJ#,   O.OBJECT_NAME,   O.OBJECT_TYPE,   A.EVENT,   SUM(A.WAIT_TIME + [...]

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…

Cheers

Tim…

 




Editing/Adding HInts in Stored Outlines

Introduction 
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

Introduction 
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! :)

Cheers

Tim…




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…

DDL

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)
as
        in_use exception ;
        pragma exception_init(in_use, -54);
begin
        while true loop
                begin
                        execute immediate m_sql;
                        exit;
                exception
                        when in_use then null;
                        when others then raise;
                end;
                dbms_lock.sleep(0.01);
        end loop;
end;
/

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.