Search

Top 60 Oracle Blogs

Recent comments

May 2014

RAC 12c enhancements: adding an additional SCAN-part 3

Travel time can be writing time and while sitting in the departure lounge waiting for my flight I use the opportunity to add part 3 of the series. In the previous two parts you could read how to add a second SCAN and the necessary infrastructure to the cluster. Now it is time to create the standby database. It is assumed that a RAC One Node database has already been created on the primary cluster and is in archivelog mode.

Static Registration with the Listeners

The first step is to statically register the databases with their respective listeners. The example below is for the primary database first and standby next, it is equally applicable to the standby. The registration is needed during switchover operations when the broker restarts databases as needed. Without static registration you cannot connect to the database remotely while it is shut down.

RAC 12c enhancements: adding an additional SCAN-part 3

Travel time can be writing time and while sitting in the departure lounge waiting for my flight I use the opportunity to add part 3 of the series. In the previous two parts you could read how to add a second SCAN and the necessary infrastructure to the cluster. Now it is time to create the standby database. It is assumed that a RAC One Node database has already been created on the primary cluster and is in archivelog mode.

Static Registration with the Listeners

The first step is to statically register the databases with their respective listeners. The example below is for the primary database first and standby next, it is equally applicable to the standby. The registration is needed during switchover operations when the broker restarts databases as needed. Without static registration you cannot connect to the database remotely while it is shut down.

Query slower on exact clone

#555555;">A customer  was working with a clone copy of their production database for reports, but the cloned database reports were running slower than the same reports on production and they wanted to know why.

#555555;">Ostensibly the machines were similar and the memory was the same, though it’s always worth verifying.  The first thing I checked was the load profile. If you have OEM you can look at the performance page and/or top activity page to see the load profile.  The load profile of production was much more CPU bound and much less I/O bound than the clone and their was clearly a difference.

#555555;">The customer knew the time they the report was run on the clone  but was unsure exactly when it was run on production, though knew it had been run in the past day.

Online Move of Datafiles for Pluggable Databases

From my present Oracle Database 12c New Features course in Zürich: We have introduced the handy new functionality that you can move datafiles online in 12c. That is at first glance having an issue for pluggable databases:

 

Full table scan runs way slower today!

#555555;">Every so often a DSS query that usually takes 10 minutes ends up taking over an hour.  (or one that takes an hour never seems to finish)

#555555;">Why would this happen?

#555555;">When investigating the DSS query, perhaps with wait event tracing,  one finds that the query which is doing full table scans and should be doing large multi-block reads and waiting for “db file scattered read” is instead waiting for single block reads, ie “db file sequential read”.  What the heck is going on?

#555555;">Sequential reads during a  full table scan scattered read query is a classic sign of reading rollback and reading rollback can make that minute(s) full table scan take hours.

Quiz Night

Okay – so it’s not night time in my home time-zone, but I’m in Singapore at the moment so it’s night time.

A very simple little quiz – so I’ve disabled comments for the moment and will re-enable them tomorrow morning to allow more people to have a chance to see the question without the solution.

Explain the anomaly displayed in the following “cut-n-paste” from a session running SQL*Plus on 11.2.0.4:

SQL> create unique index t1_i1 on t1(v1 desc);
create unique index t1_i1 on t1(v1 desc)
                                *
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

SQL> create unique index t1_i1 on t1(v1);

Index created.

Answer

Well it didn’t take long for an answer and several bits of related infomration to show up – as Martin pointed out, all I have to do is insert NULL into the table twice.

Head to Head with Rick

Great guy; Great concert (Royal Albert Hall, April 30, 2014). With Rick in a “Grumpy Old Man Pose” – Nuff Said!

NFS Performance Issues at TCP level

What happens with I/O requests over NFS and more specifically with Oracle? How does NFS affect performance and what things can be done to improve performance?

What happens at the TCP layer when I request with dd an 8K chunk of data off an NFS mounted file system?

Here is one example:

I do a

dd if=/dev/zero of=foo bs=8k count=1

where my output file is on an NFS mount, I see the TCP send and receives from NFS server to client as:

(the code is in dtrace and runs on the server side, see #2970a6;" href="https://sites.google.com/site/oraclemonitor/tcp-d">tcp.d for the code)

#2970a6;" href="http://dboptimizer.com/wp-content/uploads/2011/04/nfs3.png">

Right Deep, Left Deep and Bushy Joins in SQL

#555555;">What is right deep verses left deep? Good question. In join trees (not VST) the object on the left is acted upon first then the object on the right.  Below are left deep and right deep examples of the same query, showing

    #555555;">
  • query text
  • join tree
  • join tree  modified to more clearly show actions
  • VST showing the same actions

#555555;">left_right_deep copy

Richard Foote’s Art Work (Art Decade)

Just a quick note to say I have a new tab on the blog called “Richard Foote’s Art” where I’ll periodically upload a new piece of my art work. As the page says, they highlight both what’s in my mind (so beware) and how I like to relax in the evenings. All pieces are for […]