Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

August 2008

Flexible sampling of any V$ or X$ view with sample.sql

Submitted by tanelpoder on Tue, 2008-08-26 11:35

In recent past I’ve blogged few scripts which use specially crafted ordered nested loop for sampling contents of V$ and X$ views fast, with plain SQL.

If you haven’t read them yet, here are the links:

I wrote the above scripts having special purposes in mind (e.g. profile session waits or latching activity).

Partition-wise dependencies in 10g release 2

Submitted by Adrian Billington on Wed, 2008-08-20 07:49

Modify partitions without invalidating dependant objects. October 2007 (updated August 2008)

AWR Usage Poll

Submitted by jkstill on Mon, 2008-08-18 20:29

A number of recent threads in the Oracle-L list have made it pretty clear that Automated Workload Repository (AWR) is a tool that you are expected to use when troubleshooting a database problem.

Never mind the fact that AWR is still a product that is licensed separately from the database, and that a large segment of the Oracle DBA population doesn't seem to realize that. Or that Active Session History (ASH) is part of AWR, and falls under the same license restrictions.

So I conducted a poll regarding the use of AWR. AWR Usage Poll. If you haven't in the AWR Poll, please do so.

While the web site does provide a chart of results, those results don't include the extra comments made by poll takers. You may are may not be able to download all the results, I'm not sure if that is restricted to the poll owner.

Case study on some rowcache internals, cached non-existent objects and a describe bug

Submitted by tanelpoder on Mon, 2008-08-18 13:37

I got a question regarding Metalink note 296235.1 about a describe bug which causes objects to “disappear” when they are described when database is not open.
It was an interesting case involving a bug, so I wrote a quite long analysis with test cases today. However when posting the entry to wordpress, it managed to completely mess up the formatting. After wasting half an hour trying to get the formatting correct I gave up and saved the article into a PDF instead.

Why does even a small difference in SQL text cause a hard parse?

Submitted by tanelpoder on Thu, 2008-08-14 19:45

I just replied to an Oracle Forum Thread about why does even a small difference in SQL statement text cause it to be hard parsed and loaded as a different cursor. The reason is actually very simple – and I’m posting it into my blog too:
—
 The reason why a statement with even a minor difference in text is parsed as a separate cursor is due how Oracle looks up statements from library cache.

Script: Display valid values for multioption parameters (including hidden parameters)

Submitted by tanelpoder on Tue, 2008-08-12 19:16

I wrote a little script pvalid.sql for listing valid values for multioption parameters (the ones which are not string, number or boolean type, but accept a parameter from predetermined list, like optimizer_mode which can have values of ALL_ROWS, FIRST_ROWS, CHOOSE, FIRST_ROWS_1, etc).

The script accepts a (part of) Oracle parameter name as first argument, for example the following output is from Oracle 10.2.0.3 database:

Effective Testing

Submitted by Niall Litchfield on Mon, 2008-08-11 10:51

I’ve been following a number of threads on forums.oracle.com recently – the quality of discussion seems to have improved markedly there since I gave up on it in 2001. Anyway there was a thread about interpreting the AWR report that Oracle provides (at extra cost) and which is very smilar to the Statspack report. The [...]

The simplest query for checking what’s happening in a database

Submitted by tanelpoder on Wed, 2008-08-06 17:30

Update: If you are able to download & run scripts, it’s probably better to use my Snapper and @ashtop.sql tools for performance analysis. Neither of these scripts require any installation, Ashtop requires Diagnostics Pack (as it uses ASH), Snapper doesn’t even need that - it can also run on Standard Edition! Search for them in my blog, or check an intro video in my YouTube channel.
When someone asks you to take a quick look into database performance and for whatever reason you can’t run your usual scripts or performance tools on there, ), then what query would you run first?

How I got picked for special attention in Denver International Airport

Submitted by mno on Wed, 2008-08-06 15:14

So my wife Anette and I are on our way home from Tim's wedding.

We flew British Airways both ways. In Copenhagen I told a lady at the BA check-in counter that I might have discovered a way for terrorists to put bombs on planes without being on the plane themselves.

You see, the last couple of times where I have had to change terminals in Heathrow and there's been approximately two hours or less between the flights my bags haven't made it.

So if the bags are onboard a plane but the passenger doesn't show up, they'll pull the bags. But if the bags are delayed they'll let the passenger fly without his luggage.

Application Express and E-Business Suite 11i.

Submitted by Niall Litchfield on Mon, 2008-08-04 11:02

I’m just starting an installation of Oracle APEX 3.1.1 into one of our development ebs instances for 2 reasons.

To provide a small applet to a project team
To provide the infrastructure for the dbas to write their own little applets.

I chose this method for the following reasons (unordered)

it’s fairly cool
we know pl/sql much better than java
we [...]