Wonderful. Thanks.
Tim.
On Mon, Jun 16, 2003 at 02:54:15PM +0100, Andy Hassall wrote:
> Tim Bunce wrote:
> > On Sun, Jun 15, 2003 at 02:01:09PM +0100, Andy Hassall wrote:
> >> [EMAIL PROTECTED] 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 ([EMAIL PROTECTED]) icq(5747695) (http://www.andyh.co.uk)
> Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
>
