develooper Front page | perl.dbi.users | Postings from June 2003

Re: DBI::Oracle fails make test (specifically t/meta)

Thread Previous | Thread Next
From:
Andy Hassall
Date:
June 16, 2003 06:53
Subject:
Re: DBI::Oracle fails make test (specifically t/meta)
Message ID:
004701c3340e$c65d0fc0$0e64a8c0@excession
Tim Bunce wrote:
> On Sun, Jun 15, 2003 at 02:01:09PM +0100, Andy Hassall wrote:
>> krob427@swbell.net wrote:
>>
>>> however make test gives me the following:
>>> $ make test
>>>
>>> and it never comes back.
>>
>> Maybe it's worth adding the alter session to t/meta.t, since unless
>> you're the DBA you're not going to be able to drop the stats or
>> alter the default optimiser mode? (Although maybe it would then
>> break when 10.0 comes out... so it should probably be version
>> dependent, i.e. only do it on 9.2?)
>>
>> --- t/meta.t~   2003-03-25 13:57:57.000000000 +0000
>> +++ t/meta.t    2003-06-15 13:55:55.000000000 +0100
>>
>> +$dbh->do('alter session set optimizer_mode = RULE');
>> +
>
> Please let me know if that helps!

Well - it certainly helps for the configuration of a 9.2 database having
statistics on the SYS schema - with that line in, the test runs in a
reasonable time, without it, it doesn't seem to finish (I gave up waiting
after 15 minutes). Top queries were against all_objects and user_tables.

Might be a different issue to the OP's issue though! ;-) But has the same
symptoms, anyway.

On further reflection, maybe data dictionary queries in DBD::Oracle itself
need hinting with /*+ RULE*/ rather than just switching the optimiser mode
in t/meta? Since otherwise those schema info calls will take forever when
used outside the test.

Also thinking ahead to 10i; maybe set optimizer_mode = RULE would fail with
an error there, whereas invalid hints are silently ignored.

e.g. the patch at the end of this message, which appeared to work just as
well as the alter session idea to reduce the time for t/meta.t back to
normal.

Any opinions?

thanks,

--- Oracle.pm~  2003-05-14 20:36:49.000000000 +0100
+++ Oracle.pm   2003-06-16 14:45:55.000000000 +0100
@@ -388,7 +388,8 @@
 SELECT *
   FROM
 (
-  SELECT NULL         TABLE_CAT
+  SELECT /*+ RULE*/
+       NULL         TABLE_CAT
      , t.OWNER      TABLE_SCHEM
      , t.TABLE_NAME TABLE_NAME
      , decode(t.OWNER
@@ -441,7 +442,8 @@
 SELECT *
   FROM
 (
-  SELECT NULL              TABLE_CAT
+  SELECT /*+ RULE*/
+         NULL              TABLE_CAT
        , c.OWNER           TABLE_SCHEM
        , c.TABLE_NAME      TABLE_NAME
        , c.COLUMN_NAME     COLUMN_NAME
@@ -473,7 +475,8 @@
 SELECT *
   FROM
 (
-  SELECT to_char( NULL )    UK_TABLE_CAT
+  SELECT /*+ RULE*/
+         to_char( NULL )    UK_TABLE_CAT
        , uk.OWNER           UK_TABLE_SCHEM
        , uk.TABLE_NAME      UK_TABLE_NAME
        , uc.COLUMN_NAME     UK_COLUMN_NAME
@@ -528,7 +531,8 @@
 SELECT *
   FROM
 (
-  SELECT to_char( NULL )     TABLE_CAT
+  SELECT /*+ RULE*/
+         to_char( NULL )     TABLE_CAT
        , tc.OWNER            TABLE_SCHEM
        , tc.TABLE_NAME       TABLE_NAME
        , tc.COLUMN_NAME      COLUMN_NAME

-- 
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)


Thread Previous | Thread Next


nntp.perl.org: Perl Programming lists via nntp and http.
Comments to Ask Bjørn Hansen at ask@perl.org | Group listing | About