Archive for August, 2006

ORA-32321

August 29, 2006

Going through the site and feed stats, I found the best way to attract traffic is to talk about an error message.
So here’s the shameless discussion of ORA-32321.
According to OraDoc,

ORA-32321 REFRESH FAST of “string“.”string” unsupported after detail table TRUNCATE
Cause: A detail table has been truncated and no materialized view supports fast refersh after a detail table has been truncated.
Action: Use REFRESH COMPLETE. Note: you can determine why your materialized view does not support fast refresh after TRUNCATE using the DBMS_MVIEW.EXPLAIN_MVIEW() API.

What this really means is when you truncate a detail table on which a fast refresh MV bases, you need to do a complete refresh before any DML to the detail table and resuming usual fast refresh mode for the MV.
begin
execute immediate ‘truncate table detail_tab’;
— fast_MV is a fast refreshable MV built upon detail_tab
dbms_mview.refresh(‘fast_MV’, ‘c‘);
— do some DML to detail_tab, this is where ORA-32321 gets thrown if a complete refresh is not performed on fast_MV as the previous step.
insert/update/delete/merge…
— resume the usual fast refresh
dbms_mview.refresh(‘fast_MV’, ‘f’);
end;
/

It’s time to upgrade to 10g

August 23, 2006

Oracle  9.2.0.8 was out for a couple of platforms and it is also a terminal release.
So it’s probably time to upgrade to 10g. Before you know it, 11g will probably be announced at OpenWorld in October.

SQL Developer Exchange

August 22, 2006

According to Sue Harper’s blog, SQL Developer Exchange is launched.
You can get SQL Developer Reports, Snippets, Tips & Tricks.
More importantly, you can vote for/against Feature Requests.
Can we not show so many digits after the decimal point in score?

Oracle 10g: Instant Client

August 21, 2006

For a very very long time, Oracle has been claiming that you need full installation of Oracle client to take advantage of OCI driver for JDBC, Pro*C, etc. That’s no longer true as the availablity of InstantClient.
I have done some testing on a windows 2003 box, 2.6 GHz single CPU, 2G Memory and 9.2.0.7 database with latest CPU patch. Yes, InstantClient works with older version of database too.
The test is batch insert into a table with 600,000 rows and TCP connection. Yeah, I did use bind variables.
Using 9i OCI driver under %ORACLE_HOME%\bin\ocijdbc9.dll, it takes 42 seconds with OCI driver and amazingly 24 seconds with thin driver. That validates the convention wisdom that thin driver is better than OCI driver.
However, if I used ocijdbc10.dll from instant client, it takes 6 seconds with OCI driver or thin driver.
That’s an amazing improvement. I have done similar test on a much slower Solaris box and the result is even more astonishing, from 214 seconds 9i OCI to 24 seconds instant client OCI.
And it’s very easy to switch, just include the instantclient path in LD_LIBRARY_PATH on *nix platforms and PATH in windows.
According to the InstantClient FAQ:
How does Instant Client interact with an ORACLE_HOME?

As long as the library loading path has the directory containing
Instant Client files (e.g. the instantclient directory) ahead of
library directory in ORACLE_HOME, the application will operate in the
Instant Client mode, and the libraries in the ORACLE_HOME will not be
used.

Of course, YMMV on the improvement. That’s why you want to test it and it’s great that the switch to and from InstantClient is easy. I have yet to determine where the improvement comes from, is it from variable binding?

Oracle 10g: EZConnect

August 18, 2006

It was a pain using SQLPlus to connect to remote database, especially a lot of them, as you need to setup tnsnames.ora etc.
In 10g, EZConnect is much EZier, as the name indicates.
All you need to do is add/modify this to your sqlnet.ora
NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)
And now you can use the following syntax to connect
sqlplus user/password@//host:[port]/service name

Parallel DDL

August 16, 2006

If an object, table or index, is created with PARALLEL, then Oracle will invoke parallel DDL, which is essentially parallel DML into sys objects, like obj$. It sounds to me that if you are creating an empty schema(no data at all), it’s better to just shutdown the parallel DDL altogether. The benefit of parallelism is very tiny when you have an empty schema(create index on a table of millions of rows is a whole different story) that it may not offset the overhead at all. Of course, when you are creating an empty schema, you probably will not worry about performance whatsoever.
To disable parallel DDL;
alter session disable parallel ddl;

Interesting Oracle techniques and investigations

August 15, 2006

Alberto Dell’Era has some very interesting Oracle techniques and investigations at:
http://www.adellera.it/

Particularly, his idea of using partition table exchange to replace all rows of a table may be the answer to race condition in MV complete refresh. Essentially, you build another table on the side, commit, and then partition exchange. This is probably the best of two MV refresh approaches as explained by Alberto

<quote>
The exchange table will make the new rows appear instantaneously (same effect of the commit in a conventional delete+insert+commit) and, most importantly, readers that are currently reading when the exchange is issued will keep reading the old version of the rows until the last fetch of the current statement (ie, the “multiversion read consistency” will be preserved).
</quote>

Until Oracle implements this type of MV refresh, I have to do it manually. No wonder, partition table exchange is all the rage and buzz in DW now.

Cardinality feedback tuning

August 4, 2006

Wolfgang Breitling has proposed cardinality feedback tuning method and it works very well for Oracle.
I have to agree with him that it would be nice if Oracle can automatically apply this method. Whenever the optimizer sees an intermediate cardinality to be drastically different than the query plan as defined below, it should retry with a different plan that produces such a cardinality or close:
1. 0 to non-0, or vice versa.
2. 1 to 1+, or vice versa.
3. n to 100n, (or a threshold that can be set by user), or vice versa.

I don’t see it in 11g though, but that will be the ultimate feature and perhaps the end of Oracle performance tuning consultancy.

MView refresh tracking

August 4, 2006

I would love a catalog view to track the refresh time, refresh mode and refresh method of mviews, even if v$ ones are good so that I can tell the history and decide refresh on DEMAND vs COMMIT, or FAST vs COMPLETE.
ALL_MVIEW_REFRESH_TIMES is not good enough, only LAST_REFRESH timestamp, what’s that going to tell me?
Maybe it’s already there, just I didn’t find it.

A gripe about SQL

August 1, 2006

As a SQL programmer, sometimes I feel invincible that there’s nothing I can’t do with Oracle.
But then comes the pivoting query, SQL sucks. It’s not just Oracle, but SQL language altogether.
Enough workaround, solutions have been suggested, that I don’t want to repeat here. Just search “Pivot” at AskTom. Mind you, each of the solutions has major drawbacks or “Gotchas” that you want to think through before deploying such a solution on a production system. One limitation is you have to know how many columns you will have and another is dynamically generated SQL could exceed limit.
That’s why there are so many Excel users out there swear that Excel is the best thing since sliced bread, oh, except for the 256 column limit.
The BI tools are not farring much better. Cognos sucks at a “wide” report, having too many columns, will take much longer to run especially when you throw in more measures. Interestingly, a “long” report would do ok. So if you have more columns than rows in a Cognos Pivot table, you better swap the columns and rows for performance sake.
I don’t know, maybe pivoting is inherently a hard problem?