Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

March 2013

Index Selectivity

Submitted by Jonathan Lewis on Sun, 2013-03-31 19:42

Here’s a summary of a recent posting on OTN:

I have two indexes (REFNO, REFTYPESEQNO) and (REFNO,TMSTAMP,REFTYPESEQNO). When I run the following query the optimizer uses the second index rather than the first index – which is an exact match for the predicates, unless I hint it otherwise:

Observing Oracle Exadata SmartScan internals, part 1

Submitted by fritshoogland on Sun, 2013-03-31 16:56

In order to look how Exadata smartscans are different, first let’s have a peek the Oracle full segment/multiblock read evolution as short as possible:

a) Traditional multiblock reads, visible via the event ‘db file scattered read’
The essence is: Multiple adjacent blocks are read from disk, and put in the buffercache. Because every read is sequentially processed, IO latency is a performance penalty for every physical read. This works roughly this way: get a set of adjacent blocks from the segment header, fetch these blocks from disk, process these blocks, then get the next set of adjacent blocks, fetch these blocks from disk, process these blocks, etc.

dbms_dnfs and clone.pl

Submitted by pioro on Fri, 2013-03-29 19:03
If you are using to use dbms_dnfs to clone your database as described in MOS note 1210656.1 or on Tim's Hall blog - Direct NFS (DNFS) Clonedb in Oracle Database 11g Release 2 (Patchset 11.2.0.2) and your database version is 11.2.0.3 there is one additional step which is not covered by clone.pl script and has to be done manually.

In 11.2.0.2 it was enough to run crtdb.sql and dbren.sql scripts generated by clone.pl but this is what happen when you will run dbren.sql on 11.2.0.3 
SQL> !cat dbren.sql
declare
begin

dbms_dnfs and clone.pl

Submitted by pioro on Fri, 2013-03-29 19:03
If you are using to use dbms_dnfs to clone your database as described in MOS note 1210656.1 or on Tim's Hall blog - Direct NFS (DNFS) Clonedb in Oracle Database 11g Release 2 (Patchset 11.2.0.2) and your database version is 11.2.0.3 there is one additional step which is not covered by clone.pl script and has to be done manually.

In 11.2.0.2 it was enough to run crtdb.sql and dbren.sql scripts generated by clone.pl but this is what happen when you will run dbren.sql on 11.2.0.3 
SQL> !cat dbren.sql
declare
begin

Why is my VirtualBox VM not starting properly?

Submitted by oraclebase on Fri, 2013-03-29 15:46

I’ve just wasted quite a bit of time trying to figure out why one of my VirtualBox VMs wasn’t starting properly, so I thought I would share the experience here in case anyone else has a similar issue…

It all started because I decided to fire up an OL6 VM, that I hadn’t used for a little while. It started up fine and I figured I should probably patch the OS and the VirtualBox Guest Additions. The first thing I did was a “yum update” to bring it up to OL6.4 and the the latest UEK2. Once that was complete I did a reboot and that is where the issue started. The VM booted in the normal fashion, but then hung at this point.

ol6-startup

ORA_HASH and LOBs …. not nice partners

Submitted by connor_mc_d on Fri, 2013-03-29 11:25

Consider the following simple setup

SQL> create table t ( x int, b blob );

Table created.

SQL> declare
  2    bin blob;
  3  begin
  4    insert into t values (1, empty_blob())
  5    returning b into bin;
  6
  7    dbms_lob.writeappend(bin,10000,utl_raw.cast_to_raw(rpad('x',10000,'x')));
  8    commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select ora_hash(b) from t;

ORA_HASH(B)
-----------
 3856858313

SQL> select ora_hash(b) from t;

ORA_HASH(B)
-----------
 3856858313

SQL> select ora_hash(b) from t;

ORA_HASH(B)
-----------
 3856858313

That all seems fine…but thats because I ran the ORA_HASH commands quickly one after the other… Now that 30 seconds has passed (as I type this into the blog)…lets run the same ORA_HASH again

UltraEdit for Linux/Mac v4.0 Release Candidate…

Submitted by oraclebase on Fri, 2013-03-29 11:16

In a previous post I said that UltraEdit moves through beta really quickly. Today I’m rocking UltraEdit v4.0 Release Candidate on Fedora 18… :)

Cheers

Tim…

MERGE – concise syntax but not always fastest

Submitted by connor_mc_d on Fri, 2013-03-29 11:10

A long time ago … a long long time ago (http://www.oracledba.co.uk/tips/merge_speed.htm) I wrote that MERGE works best over update and insert.

This still holds true, but its also apparent that MERGE seems to be optimized for larger sets of rows.  When you take it down to single row operations, then don’t be SO hasty to recast your updates-and-inserts into merge commands.

Missing SQL

Submitted by Jonathan Lewis on Fri, 2013-03-29 10:35

From time to time I’ve looked at an AWR report and pointed out to the owner the difference in work load visible in the “SQL ordered by” sections of the report when they compare the summary figure with the sum of the work done by the individual statements. Often the summary will state that the captured SQL in the interval represents some percentage of the total workload  in the high 80s to mid 90s – sometimes you might see a statement that the capture represents a really low percentage, perhaps in the 30s or 40s.