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



Subject: Slow: RULE hint in data dictionary queries
Download (untitled) / with headers
text/plain 273b
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?
Download (untitled) / with headers
text/plain 541b
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
From: tadamo [...] gmail.com
Download (untitled) / with headers
text/plain 347b
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
Download (untitled) / with headers
text/plain 555b
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
Download (untitled) / with headers
text/plain 852b
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
Download (untitled) / with headers
text/plain 260b
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
Download Oracle.pm.diff
text/x-diff 1.1k
--- 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
Download (untitled) / with headers
text/plain 557b
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


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.