Google Reader

I’m experimenting with Google Reader as a replacement for BlogLines.

So far it doesn’t look too promising. I’ve had a bit of trouble with the subscription management. It occasionally fails, then works fine?? Also, the interface is a bit…. crappy. In this case the label “beta” is definitely appropriate. Some bits of the interface look badly formatted. I thought this was a firefox issue, but it’s the same on IE also.

What I do like it the idea of it presenting a reading list, rather than a list of blogs, some with new posts. In this respect it’s a little more like a news feed, rather than a list of blogs.

I’ll give it a go for a couple of days then make my decision. Google usually hit the mark so I can only assume this is the start of something better.

Cheers

Tim…

The WRAP Utility and the DBMS_DDL Package…

I’ve just been looking at the changes to the DBMS_DDL package to allow dynamic wrapping of PL/SQL source. To quote a commonly used phrase on varnish and woodstain adverts, “It does exactly what it says on the tin!”, but I’m not sure if I’ll ever make use of this feature myself…

Cheers

Tim…

Question on enhanced commit processing in Oracle 10g Release 2…

I’ve been looking at the enhanced commit processing in Oracle 10g Release 2:

Commit Enhancements in Oracle 10g Database Release 2

In writing this article I’ve noticed a discrepancy in the default values for the COMMIT WRITE command and the COMMIT_WRITE parameter.

The COMMIT documentation says:

“If you specify neither WAIT nor NOWAIT, then WAIT is the default. If you specify neither IMMEDIATE nor BATCH, then IMMEDIATE is the default.”

In contrast the COMMIT_WRITE documentation says:

“If only IMMEDIATE or BATCH is specified, but not WAIT or NOWAIT, then WAIT mode is assumed.
If only WAIT or NOWAIT is specified, but not IMMEDIATE or BATCH, then BATCH mode is assumed.”

Is this difference true, or a documentation error? I guess I’ll have to raise a TAR about this, but if anyone knows the answer already I’d be grateful if you could pass it on.

Cheers

Tim…

Update:

The COMMIT_WRITE documentation is incorrect and Oracle support are raising a bug (4668213) against it. When neither BATCH or IMMEDIATE are specified the default action is IMMEDIATE, the same as the COMMIT WRITE command. A metalink note (336219.1) is due to be released soon with the correct information.

No shortcuts for SQL tuning…

From time to time I get questions like,

“How can I make this SQL statement run faster?”

When I see these questions I’m filled with dread. I don’t want to say nothing, but at the same time there is often little I can do to help because SQL tuning requires some knowledge of what you’re trying to achieve. So this post will serve as my generic answer.

Identify poorly performing SQL statements using Statspack, AWR or ADDM.

Once you’ve identified a problem SQL statement, trace it using SQL Trace to get hold of the execution plan and wait states, which should give you some idea of why it is performing so badly. Use this information to help you decide if you should do one or more of the following:

  • Rewrite the SQL statement.
  • Add suitable indexes to the underlying tables.
  • Force appropriate index usage with optimizer hints. Remember, forcing full scans by avoiding indexes may be appropriate in some situations (suggested by Jeff).
  • Check the statistics used by the optimizer are up to date. Gathering histograms may help. (suggested by Don and Nuno)

During the rewrite process Explain Plan and DBMS_XPLAN will provide you with the adjusted execution plan, without having to rerun the statement.

If you’re using Oracle 10g you might want to consider using the SQL Tuning Advisor or the SQL Access Advisor. I’ve seen limited success with these, but you may find them more useful.

At some point you may realize that no amount of tweaking is going to solve your performance problems and a more radical approach is necessary. In these cases it may be necessary to use materialized views to “pre-fetch” the data for your problem query, or just redesign that section of your application to make it more efficient.

If there are any shortcuts in this process please pass them on, because I’ve not found them yet 🙂

Cheers

Tim…

PS. I’ve made a couple of modifications based on comments 🙂

Assorted rants…

Just a quick point about a post on Pete-s blog, which in turn references a comment on Tom Kyte’s blog. Pete is totally on the money! Here are a few thoughts related to this:

  • You can throw as much hardware as you want at a bad application and it will still be bad. If you design and code applications properly you can run them on less/cheaper hardware and everyone is happy!
  • Vendor neutral = lowest common denominator = crap!
  • Take a good technology and use it badly and you can easily convince yourself and everyone else that the technology is bad.
  • Sometimes, things that sound good in theory are terrible in practice. Did I hear someone mention J2EE? 😉

Over the years I’ve found these to be true time and time again. Remember, it’s just my opinion 🙂

On a different note, my rating super-fan is back again. The DBMS_EPG article once again went to 5 stars for 1 vote, now it’s 3.5 stars for 4 votes. Who is this mystery super-fan?

I recently found out that a couple of friends/acquaintances now read my blog. The majority of my readership for both the blog and the website are based in Asia and the USA. Having some people so close to home seems kinda weird 🙂

Cheers

Tim…

DBMS_EPG – The Embedded PL/SQL Gateway in Oracle 10g Database R2

I’ve been playing around with the DBMS_EPG package in Oracle 10g Database Release 2. This allows you to configure Database Access Descriptors (DADs) to run mod_plsql applications directly from the database without using an application server or the HTTP server from the companion CD. Instead it uses the XML DB HTTP server. I’ve written an article on it on my website:

DBMS_EPG – The Embedded PL/SQL Gateway in Oracle 10g Database R2

It definitely works, but I’ve tried it on both RHEL4 and Windows and it feels a little dodgy. I think I’ve already found one bug, mentioned in the article, but it might just be me doing something wrong. I’m not sure if it’s ready for a production application, but it’s certainly a nice idea.

Cheers

Tim…

Pipelined table functions and cursor_sharing don’t mix…

So you want to use pipelined table functions and cursor_sharing in 10.1.0. Check this out…

First create this test package:

CREATE OR REPLACE PACKAGE ptf_test AS
TYPE t_ptf_row IS RECORD (
dummy dual.dummy%TYPE
);

TYPE t_ptf_tab IS TABLE OF t_ptf_row;

FUNCTION get_data_1
RETURN t_ptf_tab PIPELINED;

FUNCTION get_data_2 (p_in IN VARCHAR2)
RETURN t_ptf_tab PIPELINED;

END;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY ptf_test AS
FUNCTION get_data_1
RETURN t_ptf_tab PIPELINED
AS
l_row t_ptf_row;
BEGIN
SELECT dummy
INTO l_row
FROM dual;

PIPE ROW(l_row);
RETURN;
END;

FUNCTION get_data_2 (p_in IN VARCHAR2)
RETURN t_ptf_tab PIPELINED
AS
l_row t_ptf_row;
BEGIN
SELECT dummy
INTO l_row
FROM dual;

PIPE ROW(l_row);
RETURN;
END;
END;
/
SHOW ERRORS

Then turn on cursor_sharing and query the pipelined table functions:

SQL> ALTER SESSION SET CURSOR_SHARING=SIMILAR;

Session altered.

SQL> SELECT *
2  FROM TABLE(ptf_test.get_data_1);

D
-
X

1 row selected.

SQL> SELECT *
2  FROM TABLE(ptf_test.get_data_2('x'));
FROM TABLE(ptf_test.get_data_2('x'))
*
ERROR at line 2:
ORA-22905: cannot access rows from a non-nested table item

SQL>

Now turn it off and try again:

SQL> ALTER SESSION SET CURSOR_SHARING=EXACT;

Session altered.

sys@db10g> SELECT *
2  FROM TABLE(ptf_test.get_data_1);

D
-
X

1 row selected.

SQL> SELECT *
2  FROM TABLE(ptf_test.get_data_2('x'));

D
-
X

1 row selected.

SQL>

So you can’t use cursor_sharing and pipelined table functions with parameters together. It took me a while to figure out the problem, but when I did it wasn’t long before support came back with an unfixed bug relating to it (Bug 4178323).

The solution? Don’t use cursor_sharing 🙂

Cheers

Tim…

Note. Script changed to use ALTER SESSION to prevent anyone leaving their test instance in an dodgy state. Thanks for the heads-up Gary 😉

The first rating mystery…

There seems to be a strange pattern to the article ratings on my website. When I put a new article live it nearly always gets a 5 star rating by the first couple of reviewers, then over time drops to somewhere around the 3 star zone. I wonder if I have a couple of fans who give me 5 stars no matter what, then the realists kick in and it gets pulled down to a more realistic rating. 🙂

Cheers

Tim…