Search

Top 60 Oracle Blogs

Recent comments

December 2013

Bitmap Question

This question came up on the OTN database forum a couple of months ago: “Why doesn’t Oracle allow you to create globally partitioned bitmap indexes?” The obvius answer is “It just doesn’t, okay.” But it can be quite interesting to think of reasons why a particular mechanism might not have been implemented – sometimes the answers can give you an insight into how a feature has been implemented, it might suggest cases where a feature might not work very well, it might give you some ideas on how to work around a particular limitation, and sometimes it just an entertaining puzzle to while away a short flight.

WordPress 3.8 Released

WordPress 3.8 has arrived. The download and changelog are in the usual places.

The admin interface has had quite a big redesign. I think it looks neater, but I’m sure it will take a bit of getting used to. The nice thing is it’s mobile aware now. If I run it on my Nexus 7 in landscape I get something similar to the browser view. If I switch to portrait it rearranges the screen to make it fit better. Neat.

The auto-updater (manually initiated) worked fine on 5 blogs, so not worries there.

Cheers

Tim…

 

 

Playing with Application Continuity in RAC 12c

One of the more interesting features in Oracle 12c RAC is application continuity. Why do I believe it is? Because it relieves developers from having to think about retrying connections and catch SQLExceptions in the code. I already thought that Fast Application Notification and Fast Connect Failover (FCF) are great, _but_ they required the developer to understand RAC and Oracle which you can’t take for granted. In fact looking back over the last few years since I co-wrote Pro Oracle Database 11g RAC on Linux the number 1 complaint I got from developers was that RAC was too complex (see for example in this thread on oracle-l)

Now Application Continuity takes away 2 problems I have seen.

12c Adaptive Optimization – Part 3

This is the third and final post on follow up questions from the Redgate webinar I did on 12c Adaptive Optimization (the link goes to a recording of the webcast by the way).

Also, here are links to the 2 earlier posts:

    12c Adaptive Optimization – Part 1.
    12c Adaptive Optimization – Part 2 (Hints).

So here are the last set of questions along with my responses:

Q: Is this feature on by default or you have to set a parameter to make sure of it?
A: It’s on by default but can be turned off by the methods listed in the presentation.

SQL Developer 4.0

Oracle have given birth to SQL Developer 4.0 today, as announced by Jeff Smith.

Referencing LDAP for JDBC thin client connections….

Just a quick note, to mention something that I just recently discovered.  (Yes, this has been available since at least Oracle 10.1, but I wasn’t aware of it!)

When composing a JDBC thin client connect URL, we have been using something of the form:
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOSTNAME=myhost.my.domain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=mydb)))

This is fine, but if you have a mix of Oracle OCI clients and JDBC thin clients, and if the OCI clients are all configured to use an LDAP server for Net Service Name resolution, then, whenever you make any kind of reconfiguration, you need to update the LDAP server, and then still need to update the hardcoded connection information in the JDBC Thin URL.

However, what i recently discovered, is that it’s possible to define your JDBC Thin URL, thus:

Null Quiz

Here’s an example I saw a few months ago of the confusion caused by NULL. As the owner of the problem put it: the first query, run from SQL*Plus for testing purposes, takes no time to complete; but when “put into a pl/sql cursor” (as shown in the second query) it takes ages to complete.

What’s going on ?


    select id
         , description
         , inventory
    from the_table
    where category =  nvl(null, category);

    --

    open c_results for
        select id
             , description
             , inventory
        from the_table
        where category =  nvl(p_user_category, category);
    fetch c_results into v_id, v_description, v_inventory;
    close c_results;

After pondering the problem for a while the OP managed to get the required level performance by changing the predicate to this:

Oracle License Audit

I found out yesterday that we have an Oracle license audit in January, so I spent yesterday having a look at everything we have to check what features we are using…

Our licensing situation is a little different to anywhere I’ve worked before, in that they are based on the number of Full Time Employees (FTEs), not on named users or processors. As a result, we don’t need to worry about the number of installations we have. We just need to make sure we are not using features we are not licensed for.

Database Licenses

The database side is quite easy because we have FTE licenses for Enterprise Edition, Diagnostics and Tuning Pack and Partitioning. I checked the DBA_FEATURE_USAGE_STATISTICS view on each server and everything looks OK.

WebLogic Licenses

Subquery

How not to write subqueries:

AND     sal.ticket_airline || sal.ticket_number NOT IN (
                SELECT sub.dsd_airline || sub.dsd_ticket_number
                FROM   ...
        )

If there had been any opportunity for the optimizer to do something clever with the NOT IN, you’ve just made sure it can’t happen. On top of that you may find that you don’t get the right results – consider the following cut-n-paste:

SQL> select user from dual where 1 || 23 = 12 || 3;

USER
------------------------------
TEST_USER

1 row selected.

Sometimes people simply forget that you can have multiple columns in subqueries (or in “IN Lists”) – so it’s perfectly valid to write the subquery as:

The Delphix Alchemist?

Gold

Alchemists are best known for their (completely fictional and entirely ridiculous, but that’s besides the point) amazing ability to turn lead into gold. Let’s face it, there’s a lot of lead in the Oracle world. Bugs, angry developers, metrics that can seem to elude human understanding…but I digress.