The £10 UKOUG Weak Joke Challenge

Oracle-WTF will pay the sum of £10 to the first person who makes the following weak Brummie joke to a conference audience at UKOUG:

Are there any Brummies here today?

Is it true that Ozzy Osbourne thought the Spice Girls were astronauts?

(Note for visitors to England: it's about the accent. And The Spice Girls used to be a pop group. And Ozzy Osbourne, oh never mind.)

Now where are those user accounts?

The IM conversation below is part of a much longer one (notice the date stamps) between a friend who we'll just call 'TR' and a developer.

Developer (11 Oct 2009 14:39:51): I created some users and now they are gone?
TR (11 Oct 2009 14:40:01): We have implemented a daily flashback to the data baseline so that repeatable tests can run every day in that database.
TR (11 Oct 2009 14:40:03): You need to notify us (as per the mail I sent out) when you make data changes that you want to keep from day to day.
TR (11 Oct 2009 14:40:06): Ok, so could you please create those users again and let me know? I'll create a new baseline for the refresh....
Developer (11 Oct 2009 14:45:51): i wonder if i ll be able this afternoon
Developer (11 Oct 2009 14:46:12): so i can do it tomorrow and send you the list
Developer (11 Oct 2009 14:46:25): You can go ahead wit the refreh of today without my users
TR (11 Oct 2009 14:48:29): Ok, I don't need the list, just to know once you have created them.
Developer (11 Oct 2009 14:50:18): ok

Developer (20 Oct 2009 16:57:53): hi TR
TR (20 Oct 2009 16:57:59): Hi
Developer (20 Oct 2009 16:58:06): Are you still doing the DB refresh on daily basis?
TR (20 Oct 2009 16:58:20): Yes. It's automatic, I don't actually *do* anything.
Developer (20 Oct 2009 16:58:24):
Developer (20 Oct 2009 16:58:27): ok
Developer (20 Oct 2009 16:58:33): then
Developer (20 Oct 2009 16:59:22): i see
Developer (20 Oct 2009 17:01:42): actually i m looking for this user on alpha qa2_PN3D8J20aa
Developer (20 Oct 2009 17:01:52): i can't find it in the db
Developer (20 Oct 2009 17:02:04): and when i m logged in with it, I added it yesterday and now it's gone
Developer (20 Oct 2009 17:03:02): i ll try using other users
TR (20 Oct 2009 17:16:21): You didn't tell me that you had created these users. The database is refreshed every night back to the baseline...as we discussed
TR (20 Oct 2009 17:16:21): If you add data you have to let me know and I will create a new baseline.

TR (21 Oct 2009 16:08:49): These users that you need. Are they in the database now?
Developer (21 Oct 2009 16:09:02): not yet
Developer (21 Oct 2009 16:09:07): but i can ping them to you
Developer (21 Oct 2009 16:09:12): at least the login
TR (21 Oct 2009 16:09:19): You don't need to ping them to me. Just tell me when they're created
Developer (21 Oct 2009 16:09:25): ok
Developer (21 Oct 2009 16:09:52): but got too much to do today probably will have them ready monday morning
TR (21 Oct 2009 16:10:20): Ok, so as per last time....when they are created please let me know.
Developer (21 Oct 2009 16:10:30): ok

Developer (25 Oct 2009 13:18:21): hi TR
TR (25 Oct 2009 13:18:27): Hi
Developer (25 Oct 2009 13:18:31): what's time is the DB refresh taking time ?
TR (25 Oct 2009 13:18:41): 00:00GMT
Developer (25 Oct 2009 13:18:44): ok
Developer (25 Oct 2009 13:18:52): i ll ping you by the end of the day my new users
TR (25 Oct 2009 13:18:57): Ok, you don't need to ping me the users, just create them and tell me when you have done it
Developer (25 Oct 2009 13:19:01): in the mean time
Developer (25 Oct 2009 13:19:25): I'm working on a script to insert our users in the Db before each time
TR (25 Oct 2009 13:19:39): Ok, you don't need to do that, just create them and tell me when you've done it.
Developer (25 Oct 2009 13:19:48): so this will help us lot and you will be free to do your updates as you want and delete our users if you need to
TR (25 Oct 2009 13:20:02): Ok great. But the process is already working, you just have to tell me once you've created them and they will always be there
Developer (25 Oct 2009 15:00:26): hi TR
Developer (25 Oct 2009 15:00:34): what do i have to give you about the created users? only login
TR (25 Oct 2009 15:00:43): nothing, just tell me when you've create them.
Developer (25 Oct 2009 15:00:48): or Zid, Xid...
Developer (25 Oct 2009 15:00:54):
TR (25 Oct 2009 15:00:54): just tell me WHEN they are created...so I can add them to the baseline.
Developer (25 Oct 2009 15:01:04): today
TR (25 Oct 2009 15:01:17): they are there now?
Developer (25 Oct 2009 15:01:21): not yet, but I will create these users
Developer (25 Oct 2009 15:02:07): ppm_alpha_4 ppm_alpha_5
Developer (25 Oct 2009 15:02:21): ppm_alpha_2 ppm_alpha_3 ppm_alpha_4 ppm_alpha_5
Developer (25 Oct 2009 15:02:21): please don't delete them this time

If at first you don't succeed...

...then try again. Then try again more 125 times. Then quit.

PROCEDURE get_id
    ( p_id_out         OUT NUMBER
    , p_name_in        IN VARCHAR2
    , p_create_user_in IN VARCHAR2 )
IS
    v_new_id      NUMBER := 0;
    v_max_tries   PLS_INTEGER := 127;
    v_default_id  NUMBER := 0;
BEGIN
    v_new_id := lookup_id(p_name_in); -- will be 0 if not found

    WHILE v_new_id = 0 AND v_max_tries > 0
    LOOP
        BEGIN
            INSERT INTO entry
            ( entry_id
            , entry_name
            , create_date
            , create_user
            , create_app
            , mod_date
            , mod_user
            , mod_app)
            VALUES
            ( entry_seq.NEXTVAL
            , p_name_in
            , SYSDATE
            , p_create_user_in
            , 'get_id'
            , SYSDATE
            , p_create_user_in
            , 'get_id' )
            RETURNING entry_id INTO v_new_id;

        EXCEPTION
            WHEN OTHERS THEN NULL;
        END;
    
        v_max_tries := v_max_tries - 1;
    END LOOP;

    p_id_out := v_new_id;
END get_id;

Thanks BB for sending this.

The Undocumented "/1000" currency formatting function

Forum question:

Hi,

How can I format currency values to shorthand?

i.e. how can I display 12500 as 12.5, 2700 as 2.7, 700 as 0.7 etc?

I have tried using various masks but can't achieve the results I'm looking for.

That's a tough one. How to make 700 into 0.7? Could there be some Oracle feature to help with this?

Two quick replies later:

Thanks for the replies guys

I wasnt aware of the "/1000" feature, but it has done exactly what I need.

Oracle needs to do more to promote these display format features. What else are they hiding? That's what we want to know.

How to talk your way out of a hole

One last shot from our favourite consultant:

Be careful what you expect from this proof of concept. We can’t prove the performance will match the requirements, and I would argue that performance isn’t a function of this architecture, it’s a function of technology.

The issue is not the architecture, it’s that [this company] doesn’t have the technology. I would even argue that the technology required might not exist yet.

We can say that indicatively if the technology did exist, then this architecture would hit that performance requirement.

Let's hear it for architecture astronauts.

Rollback segments explained

I recently read this in a book about data warehousing:

Source System Rollback Segments

When extracting from a relational source, extracts that take a long time can be problematic. If an extract asks for all records updated in the last 24 hours, the system must locate the appropriate set of records. This means that no user can change the updated_date field while your query is being processed. As transactions flow in during your query, they are queued up in a separate place called a rollback segment, to be applied once your request is finished. If your query takes too long, this queue gets too large and runs out of space. The system then kills your job and processes the transactions that have been queued up. In general, the folks responsible for the transaction system don't like this kind of behavior.

Now to be fair, the book was published in 1998 and is not specifically about Oracle. Does anyone know whether there has ever been a commercial DBMS that worked anything like this, or did they just make it up?

The Consultant on Backups

Our correspondent overheard The Consultant sorting out the backup requirements for the new system:

Consultant: You have a 6 hour window overnight, now as the queues get longer under heavy loading the end of day queue clearing will run into that 6 hours. Your backup window will start to get squeezed, so we need to know the minimum time to back-up this amount of data, including the time to shut down and start up the databases.

Technical guy: Why?

Consultant: Because it has to take place in that 6 hour window.

Technical guy: Why is that?

Consultant: Because then the users will come back on line and want to use the system.

Technical guy: So?

Consultant: They can't use the system if it's down to be backed up.

Technical guy: We'll use an online backup and they can do whatever they like.

Consultant: Well, if I was in auditing I'd fire you right now. You simply have to shut a database down to back it up. It's the only way you can get a consistent backup.

The Consultant

The Consultant has a Ph. D, vast experience of high-performance systems architecture, a black belt in karate and a reputation as a genius. He's been brought in by senior management at vast but necessary expense for a strategic rethink of the way data is shared between systems, while implementing SOA, improving performance and finding the Higgs Boson. Needless to say, he tends towards the view that database development is overrated. He's already sorted out the data warehouse. Overheard by our correspondent:

I’ve finished the design for the data warehouse. Although I say design, it’s pretty simple. That’s why it was so quick. All data warehouses are essentially the same in that they are a dimensional model. That means that you essentially have everything that is a fact, an immutable fact [waves arms expressively], in the fact table. Just the one, big, table. That’s why they’re so attractive as reporting solutions - everything is in the same place so it’s easy to understand and the reporting is easy to automate. So in that fact table you’ve got all trades, the cashflows, positions, accounting information, accounts, exceptions, counterparties. Anything that’s a fact goes in that table [does wide googly eyes expression with dramatic pause]. Then anything derived is called a dimension, like for instance P&L calculations, whether the account is on balance sheet, or off... they go in the dimension table. Basically all we have to do is just pump messages into that fact table from the bus and then recalculate the dimensions in the dimension table periodically, and that’s the technical job. Getting the facts in there and getting the calculations done.

More Fake Performance Tips

We're not sure if this is a joke or just (more likely) the work of an idiot. Here are 15 Tips for better performance and tuning in Oracle SQL and PL/SQL:

1. FTS (Full Table Scans) are always bad and Index usage is always good.
2. Usage of dynamic SQL within the stored procedure code is always bad even for search procedures.
3. Empty Space in an index that gets created due to the DML operations do not get used.
4. Indexes should be rebuilt at regular intervals.
5. Indexes and statistics are the same thing. Also, histograms are needed only on indexed columns.
6. Usage of cursors is always bad so avoid them like the plague.
7. Truncate command cannot be rolled back because it is a non-logged operation.
8. Table variables in SQL Server are always only memory resident.
9. Column order in a covered index does not matter.
10. In the case of SQL Server, one can separate the clustered index from the table.
11. Only committed data gets written to the disk.
12. Logical I/Os (LIO) are not a cause of concern, only Physical IO (PIO) are.
13. Count(1) is better performing than count(*).
14. Issue frequent commits in the application to make the transaction faster and also improve concurrency.
15. Views are evil evil DB Objects that always slow down performance.

We like the fact that 8, 9 and 10 appear to be about SQL Server, despite the heading. Probably Nawal could only think of 12 fake Oracle tips but thought nobody would notice. Can you help him out with some more misleading tips for Oracle? (Or SQL Server. Nobody will notice.) For example,

16. The buffer cache hit ratio is a reliable indicator of system performance.
17. Bitmap indexes are perfect for columns with a small number of distinct values, like 'Y' and 'N'.
18. The Inuit have fifty words for snow.

Spotted by Michel Cadot on Oracle-L.

Update: Yong Huang pointed out that the source of the article appears to be a list of common myths posted on a SQL Server blog, which makes it slightly less funny than it first seemed. Oh well.

TGI g_friday

Found in a package body:

g_friday CONSTANT VARCHAR2(6) := 'Friday';

...then a couple of hundred lines later:

if to_char(business_date,'fmDay') = g_friday then
    ...end-of-week processing...
else
    ...regular processing...
end if;

Now that's flexible. If end-of-week processing is ever moved to the weekend, all you have to do is set g_friday := 'Saturday'.