Search

Top 60 Oracle Blogs

Recent comments

Oracle

Fedora 14…

Fedora 14 is here and so are the obligatory articles:

My attitude to Fedora and Ubuntu as changed today, with most of that shift due to VirtualBox.

Before I switched to VirtualBox I was always reliant on my OS being able to run VMware Server. Over the years I had repeatedly encountered problems running VMware Server on Ubuntu and Fedora. Not all of them show stoppers, but enough to put me off them as my main desktop OS. Why did I stick with VMware Server? Just because it supported shared virtual disks, which allowed me to easily create virtual RAC installations. Version 3.2.8 of VirtualBox included support for shared disks for the first time, so I ditched VMware Server and launched full scale into using VirtualBox.

While I was playing around with Fedora 14 I was thinking how cool it would be to have a newer OS on my desktop that could run Google Chrome, then it dawned on me that now I can. I’ve been free of VMware Server for a while now and I hadn’t realized the knock-on effect of that.

My years of using RHEL mean I feel a little more comfortable with Fedora than Ubuntu, but to be honest all I do on a desktop is fire up VirtualBox, use a browser (preferably Chrome) and use a terminal for SSH. Virtually everything else is done in VMs.

Now, do I waste a few days assessing the various options for my desktop, or do I just stick with CentOS and deal with the fact I can’t use Chrome on it? :)

Cheers

Tim…

Advert: PL/SQL Masterclasses in Bulgaria and Serbia…

In November I’m scheduled to do 2 PL/SQL masterclasses in Europe:

  • Bulgaria (Nov 22-23)
  • Serbia (Nov 25-26)

As was shown by my recent class in Hong Kong, sometimes the number of people wanting to do the course is fine, but they don’t register in time so the class gets cancelled. Luckily in the case of the Hong Kong class I was able to reschedule it in time, but it could easily not have happened.

It is only 3 weeks until these courses start, so Oracle University will have to confirm/cancel them soon. If you are interested in either of the classes, contact your local Oracle Education office to register your interest. If you leave it until 1 week before the class is scheduled to start, you may find it has already been cancelled and I’ll be home watching day-time TV. :)

Cheers

Tim…

Oracle XMonth

I spent most of the last week in California at Oracle’s XMonth (it was really a week so I’m not sure why they called it XMonth). Which reminds me of a tuning engagement I did a few years ago. I showed up at the client’s facilities and we started discussing the problem. They told me that their daily job was running too slow and it was causing major problems. So I asked them what time the daily job ran each day. And they told me that it ran every 20 minutes. Ha! “And why do you call it the daily job?”, I asked. They weren’t really sure. The job was taking about an hour to complete as I recall, but I digress.

At XMonth, they had three tracks: Exadata (and two others that I can’t remember). I did learn a few things I thought were worth sharing.

  1. Exalogic is not shipping yet, but we did cover it in some detail. Exalogic’s biggest advantage appears to be the Infiniband fabric. It can be connected directly to any DB server using IB (Exadata for example) and can communicate with the extremely low latency RDS protocol.
  2. Oracle has relaxed their “no changes to the Exadata configuration” stance (but only very slightly). They said that it was OK to change out the Cisco switch and replace it with some other equivalent switch.
  3. A competitive analysis of Sun’s server line was provided. It included T series, X series, and M series servers along with Exadata. Exadata pretty much kicks all other options in the teeth (in my opinion). M series are still suitable for very large applications that are unable to scale out via RAC – such are Oracle’s own MRP package which uses the dbms_pipe package limiting its ability to scale in a RAC environment. But in general, the advice to the sales team was that if you are in a competitive situation, in most cases you should lead with Exadata.

So that’s about it. Oh they also let me talk about our experiences with Exadata. That was fun and I got to try my hand at a virtual presentation, as there were participants all over the world following along with Webex. The software has gotten pretty good for doing these kinds of presentations by the way. It was good practice for the Virtual Oracle Conference we have coming up next month. (note that there are only a couple of days left to sign up at the discounted rate)

Hong Kong Update

As always, you never know what you’re going to get when you do an Oracle University class. Originally the Hong Kong class was cancelled, then got rescheduled at the last minute. I was expecting a very small class, but as it turned out I had 19 people, so it was the biggest class I did on this trip.

The room was a little on the small side and a bit warm in the morning of the first day, but we managed to get through OK.

Doing the same course 4 times in such quick succession was a little odd. On a couple of occasions I caught myself thinking, “I’m sure I’ve already said this before”. :)

The first leg of the flight back home was “interesting”. There was an old guy on my row who cycled between snorting half his brain down into the back of his throat, then coughing up his lungs up into the back of his throat, followed by a good chew and swallow. It was less than a pleasant experience. Added to that, there was a small kid who was intermittently making a noise which kept waking me up. I kinda forgive kids on planes because it’s not a natural environment for them, but this kid had fallen out of the ugly tree and hit every branch on the way down, twice. When they don’t look cute I’m much less sympathetic.

The second leg of the trip seemed a lot quicker because I was chatting to an Aussie lady about life, the universe and everything. Seven hours flew by.

So now I’m home and trying to deal with the washing, backlog of mail and yet another time zone. I think it’s two weeks until the next trip, assuming there are no cancellations. :)

Cheers

Tim…

A few more announcements…

Today’s hacking session was a success – 144 people signed up and around 100 attended – thank you!
  • I will do the next hacking session in November! Don’t know exact date yet, I’ll just need to find a free enough day… I think I’ll go deeper into internals next time to justify the “hacking” part in the name :-)
Last days for getting the early-bird rate at the first Virtual Conference about #ff0000;">Systematic Oracle SQL Optimization with Cary Millsap, Jonathan Lewis, Kerry Osborne and me!
Additional Q & A sessions added into the package of my AOT2.0 seminars:
  • NB! As a fresh addition – the AOT2.0 seminar attendees can attend two #ff0000;">additional 2 hour Q & A sessions after the seminars!
    • 2 hours in mid-December 2010 (TBD)
    • 2 hours in mid-January 2011 (TBD)
    • The idea is that when you put the new tools and knowledge into use in real life, there will likely be additional questions that arise. The extra Q&A sessions, where Tanel answers questions related to the seminar material (and do even more demos) should help you to get the most out of these seminars!
    • Existing and new attendees of AOT20 seminars, no action is needed from you. I’ll just send you a mail with proposed dates after the seminars…
  • Last chance to sign up to the first part of the Advanced Oracle Troubleshooting v2.0 Online Deep Dives (1-5 Nov)!

Share

Recovering a lost Data Guard broker configuration file (ORA-16572)

While teaching the Oracle11g Release 2 Data Guard course a while back I got a question about how to recover from the loss of a Data Guard broker configuration file. I didn’t know the answer right away so I did a bit of research and this is what I found out. Basically there are 4 […]

Database Gateway for Sybase

Before starting to write this article I was wondering if it was of any use to anyone. Who in their right state of mind would use the Database Gateway for xxx, be it Sybase or Adabase or whatever othere database was to be used. If you have to, simply configure another data source in your code and read data from where you need it. Note that I said read! Ever since Piet de Visser’s presentation about simplicity I have been a great supporter of the simplicity approach. Or, like James Morle likes to quote, simplicity is the ultimate form of sophistication.

Transparent Gateways desupported

So, anyway, I have been asked to link Sybase ASE 15.0.3 to an Oracle 10.2.0.4 database, all on Solaris 10/SPARC 64bit. A quick proof of concept had to be performed. Initially I started out with Transparent Gateway for Sybase, the name of the product for 10g Release 2. I should have known something was wrong when the link to download the software was broken and I had to manually copy & paste it. Well to cut a long story short, 10.2 gateways are desupported since 2008! I wasted a whole 2 hours before that came up on MOS. The workaround is to use > 10.2 software for this, and I went for 11.2. MOS states that this is possible for > 9.2.0.7, > 10.1.0.4 and > 10.2.0.3. My database was 10.2.0.4 which means I’m fine.

Database Gateway for Sybase

As always the new software is on OTN, under the 11.2 database downloads. Click on “See all” to access and download solaris.sparc64_11gR2_gateways.zip. I downloaded and unzipped the file to /u01/app/oracle/stage. The idea was to install the gateway into its own Oracle home, as all the installed software was 10.2.0.4 and lower. Again, as always it’s required to unset all Oracle related environment variables such as ORACLE_SID, AGENT_HOME, OMS_HOME, LD_LIBRARY_PATH_64, LD_LIBRARY_PATH, ORACLE_HOME etc.

The installation is really straight forward as you’d expect. Ensure that you have X11 forwarding enabled and an X server available (I use XMing), and execute “runInstaller”.

  1. Choose an appropriate name for the home location and path. Mine were OraGtw11g_home1 and /u01/app/oracle/product/gateway/11.2.0.1 but any path (not pointing to an existing ORACLE_HOME) is fine
  2. On the “Available Product Components” page select “Oracle Database Gateway for Sybase”
  3. On the configuration screen enter the hostname where your Sybase database is running, the port number (from $SYBASE/interfaces) and the database name to be accessed. Don’t worry, all these parameters can be changed later
  4. Start the installation
  5. When prompted, perform a typical install using netca.
  6. Exit the Universal Installer

Great, that’s all done now! Some more configuration steps are to be performed still.

Configuring the Gateway Home

I’d recommend that you enter the gateway ORACLE_HOME into /var/opt/oracle/oratab (or /etc/oratab, if you are not on Solaris). This way navigating is greatly simplified.

The connection information Oracle needs to connect to Sybase is stored in a configuration file located in the following location:

$ORACLE_HOME/dg4sybs/admin/initdg4sybs.ora

This is populated with the values provided during the installation, but for troubleshooting purposes you should set the logging to ON:

HS_FDS_TRACE_LEVEL=ON

Once connections to Sybase have been established, reset the value to OFF to avoid excessive log file generation. The log files are generated in $ORACLE_HOME/dg4sybs/log, one for each query making use of the connection to Sybase.

By default, all stored procedures do not return a result set to the use. To change this behaviour, set the following parameter to TRUE:

HS_FDS_RESULTSET_SUPPORT=TRUE

The next step is to configure the listener. You should have performed a typical configuration during the installation (i.e. netca will have run). If so, edit $ORACLE_HOME/network/admin/listener.ora and add a static SID into the SID_LIST as in the following example:

# listener.ora Network Configuration File:
# /u01/app/oracle/product/gateway/11.2.0.1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (PROGRAM = dg4sybs)
      (ENV = /u01/app/oracle/product/gateway/11.2.0.1/dg4sybs/driver/lib:/u01/app/oracle/product/gateway/11.2.0.1/lib)
      (SID_NAME = dg4sybs)
      (ORACLE_HOME = /u01/app/oracle/product/gateway/11.2.0.1)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  )
ADR_BASE_LISTENER =/u01/app/oracle/product/gateway/11.2.0.1

Unlike earlier versions of the gateway, the Sybase OCS libs don’t need to be included in the ENV variable. Obviously, if there is a listener on your host already, add the entries to the existing Now start the listern

Configuring the Database

Before Oracle can access the Sybase system, it must have an entry in its tnsnames.ora file to contact the listener we just created/modified:

dg4sybs =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))
    (CONNECT_DATA= (SID=dg4sybs))
    (HS=OK)
  )

The hostname needs to refer to where the gateway’s listener runs on, as will the port. The SID needs to match the gateway SID defined in the SID_LIST_LISTENER parameter of the gateway’s listener.ora.

Within Oracle, create a database link to the Sybase database:

SQL> create database link dg4sysb connect to user identified by password using 'dg4sysbs';

Enclose the username and password in double-quotes as they are case sensitive. That’s it, you can now query the Sybase database from within Oracle.

With this setup it is now possible to query Sybase tables, and stored procedures. I have written a very basic one to demonstrate that it’s possible. Logging on to Sybase, I created a simple stored procedure to report the version, similar to the v$version view in Oracle.

1> drop procedure myversion
2> go
1> set chained on
2> go
1> create procedure myversion ( @ver varchar(255) OUTPUT ) as select @ver = @@version
2> go

Now on Oracle, I can query this like this:

set serveroutput on
declare
  v varchar2(4000);
begin
  "dbo"."myversion"@DG4SYSB(v);
  dbms_output.put_line(v);
end;
/

And this worked!

Another (secret) hacking session with me – using Oracle Session Snapper for flexible troubleshooting (and fun)

And this time we have audio !!! (Wow!)

Following the huge success of my last hacking session, planned while drinking beer at Graham Woods OOW pre-party and delivered from Miracle’s massive Oracle Closed World event in Thirsty Bear (between drinking beers), I’m announcing another hacking session:

What: Using Session Snapper for flexible Oracle Performance Troubleshooting

When: Wednesday 27th Oct 9:00-10:00 AM PDT (US West coast / California time). Check what’s this in your time zone here

Where: Internet! -> Sign up here: http://tech.e2sn.com/secret

You’ll need to register fast and be “there” on time as my current GotoWebinar account only allow 100 attendees to log on… last time over 100 people signed up, but “luckily” less actually showed up, so nobody got left outside!

BTW, I have figured out what went wrong with audio last time and caused my voice in the end of presentation disappear). A program, which I accidentally launched via a keyboard shortcut, grabbed my Mic input to itself, so gotowebinar’s app couldn’t access it anymore.

See you soon!

Share

COUNT STOPKEY operation (the where ROWNUM

I was running tests on some very large tables on an Exadata cluster and found an interesting bug.

Instead of having to query 4Billion row tables, I have reproduced this case with a cartesian join…

Check this. I’m generating 8 Billion rows using a 3-way cartesian join of set of 2000 rows. So, this results in 2000 * 2000 * 2000 rows, which is 8 billion rows.

SQL>  with sq as (select null from dual connect by level <= 2000)
      select count(*)
      from sq a, sq b, sq c;

COUNT(*)
----------
8000000000

Everything worked well as you see. All 8 billion rows were nicely counted. Let’s modify this query a bit, by adding a WHERE rownum <= 8 000 000 000 predicate, which shouldn’t modify the outcome of my query as 8 billion rows is exactly what I have:

SQL> with sq as (select null from dual connect by level <= 2000)
     select count(*)
     from sq a, sq b, sq c
     where rownum <= 8000000000;

COUNT(*)
----------
4294967295

Ding! (as I’m unable to come up with a better word to present this problem :)

Seems like we have a problem! The COUNT operation thinks that we have only a bit over 4 billion rows returned from the query plan branches below it.

We clearly have a(n easily reproducible ) bug here!

What happens here is that whenever you use ROWNUM < N operator in a query predicate like I have done, you will introduce an additional step to the query plan (COUNT STOPKEY).

Check this simple query:

SELECT * FROM t WHERE ROWNUM <= 1000

Plan hash value: 508354683

--------------------------------------------
| Id  | Operation          | Name | E-Rows |
--------------------------------------------
|   0 | SELECT STATEMENT   |      |        |
|*  1 |  COUNT STOPKEY     |      |        |
|   2 |   TABLE ACCESS FULL| T    |     41 |
--------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=1000)

As the above plan explains, the “WHERE rownum <” predicate is applied in the execution plan line (row source) 1 – COUNT STOPKEY. You won’t see this line when you are just counting rows without any “ROWNUM <” predicate.

How this works is that the COUNT STOPKEY knows how many rows you want and will just keep calling its child function under it in the execution plan tree to get more and more rows, until the required amount of rows have been returned. And then the COUNT STOPKEY function just stops calling its child row sources and declares the end-of-data condition.

And here’s the catch – due to a bug, the variable used to keep track of number of processed rows by COUNT STOPKEY is only 4 bytes, even on 64bit platforms. So, it can hold values up to 4294967295 in it (the count returned above), which is 2 ^ 32 – 1. That wouldn’t be so much of a problem in practical real world applications, but what I don’t like is that the operation will silently return wrong data – it will just stop fetching more rows, even though we haven’t reached the ROWNUM <= 8 000 000 000 limit yet and there are rows to fetch from the child row-source, but COUNT STOPKEY declares end-of-data condition and returns ~4B as a result.

This is (obviously a bug) and now there’s a bug number for that as well (thanks to Greg Rahn for letting me know) – Bug# 10214991 (unfortunately it’s not public in MOS).

Now, there’s a reason why this bug has gone unnoticed for so long despite that 4+ billion-row tables have existed for long time (I worked first with such Oracle database in year 2000 – on Windows NT :).

  1. A real-life business query with a construct of WHERE ROWNUM <= X makes sense only when the data is ordered by some meaningful business attribute (a column). Otherwise you’ll get the query results in quite random order. Note that I’m talking about real, business queries here.
  2. The only right way to order data in SQL is via an ORDER BY clause. There is no other right way to do it, even though some hacks sometimes work (and will stop working after the next upgrade)
  3. Nobody (or not many people) have written queries like: give me the sum of 5 billion biggest purchases ordered by the selling price or give me the sum of last 5 billion purchases up to this date. If you replace the billion by just ten, hundred, or just a thousand, then hell yeah, such types of queries are being executed every day (or should I say minute, second).
    Whether using ROWNUM in your business queries (instead of actual date range or purchase price in $$$) is a good or bad design is a completely different question – the fact is that ROWNUM is used in such SQLs.

Now we’ll just need to wait until the data volumes (and reports) get so big that someone actually hits this problem in real life!

So, better patch your database before the end of this decade! :-)

Share

Melbourne Update

The Melbourne class was a little bigger than the previous classes, with 12 people including several people from Oracle Support. Being the 3rd class in quick succession, everything felt really smooth, with no real surprises.

I actually managed to get a reasonable amount of sleep before both classes, which was a bonus. Saying that, I fell asleep at 20:00 last night, so I ended up waking up at 03:00, which gave me some time to play catch-up with emails and forum questions. It does mean that today will end up being a pretty long day, since my flight to Hong Kong isn’t until 00:15 (including a slight delay).

Apparently there have been typhoon warnings in Hong Kong and the schools have been closed, but surprisingly it hasn’t really affected flights. Let’s see how that works out.

This was my third visit to Australia, but only my second visit to Sydney and Melbourne. The real take-home message from this trip is I felt really at ease all the time I’ve been in Australia. I’m not surprised a lot of British people decide to move to Australia. It’s probably one of the easiest places for a Brit to relocate to.

Cheers

Tim…

Update: I’m in the hotel in Hong Kong now. No signs of a typhoon, but I’ve received a mail saying the class may be cancelled if the weather is too bad. Weird. I’m just off to lunch in the tallest building in Hong Kong, or so my mate tells me.