Skip Menu |

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 89491
Status: resolved
Priority: 0/
Queue: DBD-Oracle
People
Owner: Nobody in particular
Requestors: sgoeldner [...] cpan.org
Cc:
AdminCc:
Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 1.67_00

History Show all quoted text
  Mon Oct 14 10:20:19 2013 sgoeldner [...] cpan.org - Ticket created
Subject: Slow: RULE hint in data dictionary queries
The RULE hint in data dictionary queries causes very bad performance on a large 11g dictionary. I was about preparing a patch to drop these hints, but found this patch from Andy Hassall: <http://www.mail-archive.com/dbi-dev@perl.org/msg03012.html> What's your opinion?
  Wed Oct 16 05:58:44 2013 bohica [...] ntlworld.com - Correspondence added
On Mon Oct 14 10:20:19 2013, SGOELDNER wrote: Show quoted text
> The RULE hint in data dictionary queries causes very bad > performance on a large 11g dictionary. > I was about preparing a patch to drop these hints, but > found this patch from Andy Hassall: > > <http://www.mail-archive.com/dbi-dev@perl.org/msg03012.html> > > What's your opinion?
I wasn't even using DBD::Oracle back in 2004. Andy's suggestion sounds good to me although I'm a bit strapped for time to try and apply it 9 years later, right now. Martin -- Martin J. Evans Wetherby, UK
  Wed Oct 16 05:58:44 2013 The RT System itself - Status changed from 'new' to 'open'
  Fri Oct 18 13:50:16 2013 tadamo [...] gmail.com - Correspondence added
From: tadamo [...] gmail.com
This issue has recently become a problem for me as well...just posting to show more interest in the patch. Unfortunately, I had to modify our install of DBD::Oracle b/c I couldn't determine a better way to remove the /*+ RULE*/ hint. So, I'd be happy even if there was a way to simply override the default. We're running Oracle 11g. Thanks, - Tom
  Tue Oct 29 10:01:42 2013 sgoeldner [...] cpan.org - Correspondence added
Andy's post contains a defunct link to google groups. It is entirely possible that he referred to this thread: http://www.nntp.perl.org/group/perl.dbi.users/2003/06/msg19009.html All in all, it seems more likely to have statistics as prerequisite for CBO in newer versions of Oracle. Hence the RULE hint patch and later the revised patch with ora_server_version. IMHO, the different hinting depends directly on the availability of statistics and only indirectly on the server version. But isn't this exactly what the /*+ CHOOSE */ hint does? Steffen
  Tue Oct 29 10:10:54 2013 bohica [...] ntlworld.com - Correspondence added
On Tue Oct 29 10:01:42 2013, SGOELDNER wrote: Show quoted text
> Andy's post contains a defunct link to google groups. > It is entirely possible that he referred to this > thread: > > http://www.nntp.perl.org/group/perl.dbi.users/2003/06/msg19009.html > > All in all, it seems more likely to have statistics > as prerequisite for CBO in newer versions of Oracle. > Hence the RULE hint patch and later the revised patch > with ora_server_version. > > IMHO, the different hinting depends directly on the > availability of statistics and only indirectly on the > server version. > But isn't this exactly what the /*+ CHOOSE */ hint does? > > Steffen
Sorry Steffen, you've lost me now. Are casting doubt on all or any of the patches now? I've got some time this week, so if a patch just needs applying and testing I can do it. Martin -- Martin J. Evans Wetherby, UK
  Tue Oct 29 10:58:12 2013 sgoeldner [...] cpan.org - Correspondence added
I opted for the /*+ CHOOSE */ hint - seems the most sensible approach to me. Please note, I made the diff against my Oracle.166_01, which contains statistics_info from RT #88135. If you dont't include statistics_info, simply drop the last hunk from the patch.
Subject: Oracle.pm.diff
--- Oracle.166_01 Mon Aug 26 10:57:59 2013 +++ Oracle.pm Tue Oct 29 15:31:45 2013 @@ -470,7 +470,7 @@ SELECT * FROM ( - SELECT /*+ RULE*/ + SELECT /*+ CHOOSE */ NULL TABLE_CAT , t.OWNER TABLE_SCHEM , t.TABLE_NAME TABLE_NAME @@ -524,7 +524,7 @@ SELECT * FROM ( - SELECT /*+ RULE*/ + SELECT /*+ CHOOSE */ NULL TABLE_CAT , c.OWNER TABLE_SCHEM , c.TABLE_NAME TABLE_NAME @@ -557,7 +557,7 @@ SELECT * FROM ( - SELECT /*+ RULE*/ + SELECT /*+ CHOOSE */ to_char( NULL ) UK_TABLE_CAT , uk.OWNER UK_TABLE_SCHEM , uk.TABLE_NAME UK_TABLE_NAME @@ -626,7 +626,7 @@ SELECT * FROM ( - SELECT /*+ RULE*/ + SELECT /*+ CHOOSE */ to_char( NULL ) TABLE_CAT , tc.OWNER TABLE_SCHEM , tc.TABLE_NAME TABLE_NAME @@ -794,7 +794,8 @@ SELECT * FROM ( - SELECT NULL TABLE_CAT + SELECT /*+ CHOOSE */ + NULL TABLE_CAT , t.OWNER TABLE_SCHEM , t.TABLE_NAME TABLE_NAME , to_number( NULL ) NON_UNIQUE
  Tue Oct 29 12:40:02 2013 bohica [...] ntlworld.com - Correspondence added
On Tue Oct 29 10:58:12 2013, SGOELDNER wrote: Show quoted text
> I opted for the /*+ CHOOSE */ hint - seems the most > sensible approach to me. > Please note, I made the diff against my Oracle.166_01, > which contains statistics_info from RT #88135. > If you dont't include statistics_info, simply drop > the last hunk from the patch.
Thanks Steffen. I applied it and tested it on my clone and sent a pull request to Yanick. If you use git, you always do the same in the future. The repo is at https://github.com/pythian/DBD-Oracle. Martin -- Martin J. Evans Wetherby, UK
  Tue Dec 17 09:25:33 2013 bohica [...] ntlworld.com - Status changed from 'open' to 'patched'
  Thu Feb 20 06:23:20 2014 bohica [...] ntlworld.com - Status changed from 'patched' to 'resolved'
  Thu Feb 20 06:23:22 2014 bohica [...] ntlworld.com - Fixed in 1.67_00 added