Interval data type not displayed in SQLDeveloper

October 24, 2006

If you select an interval data type column from a table or simply do math on two timestamp columns, the data is not displayed in SQLDeveloper. I guess this is a similar issue as the dbms_session.set_nls I blogged about.
There’s a related thread at OTN SQL Developer forum as well.
Of course, the workaround is to_char. Reiterate my comment. I don’t dispute that to_char should be used in production code when needed, but that doesn’t give SQLDeveloper an excuse for not doing the right thing in the first place. It may be just an additional query of v$nls_parameters.

Advertisements

Black-box testing of Cognos using HtmlUnit

October 24, 2006

Building a report in Cognos is one thing, testing of both correctness and performance is another.
It is possible to use HtmlUnit to do black-box testing. The process is pretty easy as HtmlUnit is just another HttpClient, like a browser. The annoying thing is JavaScript. Cognos uses JavaScript extensively and HtmlUnit’s support of JavaScript is spotty. I actually disabled JavaScript altogether and build my own custom Java code to emulate JavaScript. The key is to set values for all those elements in the formWarpRequest form correctly. Coupled with Congos tracing and DB tracing(Oracle for me), it’s possible to get a complete picture of a report run.
There are tons of similar packages available like HttpUnit or WebTest. Actually, WebTest looks very flexible and possibly no coding at all.

SQL Developer customization

October 16, 2006

One of the most common complaints about SQLDeveloper is that, unlike SQLPlus, which lets us run a glogin.sql and login.sql script to customize, SQLDeveloper doesn’t provide such a mechanism.
To customize, we could use database logon trigger as the following, be sure to grant select any dictionary privilege to the owner of the procedure db_trigger_proc.

create or replace procedure db_trigger_proc
is
  p_row v$session%rowtype;
begin
  select * into p_row
  from v$session
  where sid=(select sid from v$mystat where rownum=1);

  case
  when p_row.module = 'SQL Developer' then
  execute immediate 'alter session set nls_date_format='
  ||chr(39)||'YYYY-MM-DD HH24:MI:SS'||chr(39);
  -- add customization for other programs if needed.
  else null;
  end case;

  exception when NO_DATA_FOUND then null;
end;

create or replace trigger db_trigger
after logon on database
begin
db_trigger_proc;
end;

It’s SQLDeveloper

October 13, 2006

If I run the following in SQL Developer:

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
select * from v$nls_parameters where parameter='NLS_DATE_FORMAT';
select sysdate from dual;
begin
dbms_session.set_nls('nls_date_format','''DD-MON-YY''');
end;
/
select * from v$nls_parameters where parameter='NLS_DATE_FORMAT';
select sysdate from dual;

Here’s the output from SQL Developer:
alter session set succeeded.

PARAMETER                                                        VALUE                                                           
—————————————————————- —————————————————————-
NLS_DATE_FORMAT                                                  YYYY-MM-DD HH24:MI:SS                                           

1 rows selected

SYSDATE                  
————————-
2006-10-13 12:38:06      

1 rows selected

anonymous block completed

PARAMETER                                                        VALUE                                                           
—————————————————————- —————————————————————-
NLS_DATE_FORMAT                                                  DD-MON-YY                                                        <– This proves it’s not the database.

1 rows selected

SYSDATE                  
————————-
2006-10-13 12:38:06      

1 rows selected

So maybe SQLDeveloper is caching NLS parameters?

dbms_session.set_nls works for SQLPlus, not SQL Developer?

October 12, 2006

This is in SQLPlus,

SQL> alter session set nls_date_format=’YYYY-MM-DD HH24:MI:SS’;

Session altered.

SQL> select sysdate from dual;

SYSDATE
——————-
2006-10-12 12:31:20

SQL> begin
  2   dbms_session.set_nls(‘nls_date_format’,”’DD-MON-YY”’);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sysdate from dual;

SYSDATE
———
12-OCT-06

dbms_session.set_nls works perfectly, however, in SQLDeveloper, when I run the same thing:
alter session set succeeded.

SYSDATE                  
————————-
2006-10-12 12:30:42      

1 rows selected

anonymous block completed

SYSDATE                  
————————-
2006-10-12 12:30:42       <– Notice how the format is not changed.

1 rows selected

to_date and to_timestamp

October 5, 2006

I noticed the 9.2.0.7 and 10.2.0.1 CBO evaluates to_date and to_timestamp differently.
Here’s a test:

drop table t;
create table t (c timestamp(9));
— insert a whole year worth of timestamps, 5 minutes apart. Gather stats
begin
for i in 0..364 loop
for j in 0..12*24-1 loop
insert into t values (to_date( ‘2003-01-01 00:00:00′,’YYYY-MM-DD HH24:MI:SS’ ) + i + j/24/12);
end loop;
end loop;
dbms_stats.gather_table_stats(user, ‘T’,cascade=>true);
end;
/

— Now use to_date
explain plan for
select * from t where c between to_date( ‘2003-02-14 00:00:00′,’yyyy-mm-dd hh24:mi:ss’ )
and to_date( ‘2003-02-18 00:00:00′,’yyyy-mm-dd hh24:mi:ss’ );
And here’s the plan:
 
————————————————————————-
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
————————————————————————-
|   0 | SELECT STATEMENT     |             |  1154 | 12694 |    75  (59)|
|*  1 |  TABLE ACCESS FULL   | T           |  1154 | 12694 |    75  (59)|
————————————————————————-
 
Predicate Information (identified by operation id):
—————————————————
 
   1 – filter(“T”.”C”<=TIMESTAMP’2003-02-18 00:00:00′ AND
              “T”.”C”>=TIMESTAMP’2003-02-14 00:00:00′ )

So far so good. CBO converts to_date to timestamp constant and gets the correct cardinality. 4 days * 24 * 12 = 1152. 1154 is very close.

— now to_timestamp
explain plan for
select * from t where c between to_timestamp(‘2003-02-14 00:00:00.000′,’yyyy-mm-dd hh24:mi:ss.ff3’ )
and to_timestamp(‘2003-02-18 00:00:00.000′,’yyyy-mm-dd hh24:mi:ss.ff3’ );

This is the plan
 
————————————————————————-
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
————————————————————————-
|   0 | SELECT STATEMENT     |             |   263 |  2893 |   179  (83)|
|*  1 |  FILTER              |             |       |       |            |
|*  2 |   TABLE ACCESS FULL  | T           |   263 |  2893 |   179  (83)|
————————————————————————-
 
Predicate Information (identified by operation id):
—————————————————
 
   1 – filter(TO_TIMESTAMP(‘2003-02-14
              00:00:00.000′,:B1)<=TO_TIMESTAMP(‘2003-02-18 00:00:00.000’,:B2))
   2 – filter(“T”.”C”>=TO_TIMESTAMP(‘2003-02-14 00:00:00.000’,:B1) AND
              “T”.”C”<=TO_TIMESTAMP(‘2003-02-18 00:00:00.000’,:B2))

Notice how Oracle converts the format string to bind variables. That’s when the 0.25% rule kicks in and to_timestamp behaves almost like any PL/SQL function. 365*24*12*.05*.05=262.8. That’s where the cardinality 263 comes from.

This could result in disaster when the num_rows for T is small and T is joined to other tables. If CBO evaluates the cardinality down to 1…

GTT(Global Temporary Table) and Hint

September 27, 2006

I saw a peculiar usage of GTT recently.
I guess it’s a fairly common that you would create GTT, insert some data and join to another table(most likely a regular one, but could be another GTT). By the way, this is very SQLServer-ish thinking and we should avoid it as much as possible in Oracle. For the case I see, it’s justified to do it this way(I don’t want to get into too much details about the justification, but let’s just say it’s due to the lack of partitioning option). However, the case would insert data into the GTT and use dbms_stats to analyze table. That’s the interesting part. I guess the developer knows that Oracle CBO needs accuate stats but did not realize that the GTT has only one copy of the stats and the same copy will be used by every session. It would be ok if we are guaranteed that the GTT can only be accessed by one session. If two sessions run the same routine with vast different number of rows inserted, they could step on each other.
The solution is to use hint, something to be avoided generally, but justified with GTT. In the case of dynamic SQL and the number rows inserted into GTT is known as it should be, we can use CARDINALITY hint. Notice CARDINALITY hint is undocumented, but blessed by Tom Kyte as a good hint. Otherwise, we can use dynamic_sampling hint and test to make sure we set the correct level.

Use firefox for Report Studio

September 26, 2006

If you are a firefox and Report Studio user, you probably know by now that Report Studio does not support firefox.
But you probably also know by now that the IE tab plug-in does wonders…
BTW, Query Studio drag-and-drop also works with IE tab.

Cognos, a perfect example of a database with the ultimate extensibility

September 22, 2006

Sorry, this is not meant to be compliment.
Let’s start off with Tom Kyte’s post on the DailyWTF.
Unfortunately for the database of ultimate extensibility, usability is nil and performance is down the drain.
Now you may be complaining about Cognos performance, right? Just look at the XML stored as blobs/clobs in content store.
No wonder…

SQL Server pivot query

September 18, 2006

I blogged about Pivot query, sometimes referred to as a cross-tab query, a while ago.
Mike has mentioned pivot syntax in SQL Server here. I checked it out.
I wouldn’t describe it as cool or glamorous, but it is a neat feature that I hope Oracle will adopt. I know, not easy, for some pride issues.