RE: Oracle strace output - detail
Date: Thu, 12 Jan 2012 23:11:22 +0000
Message-ID: <BD475CE0B3EE894DA0CAB36CE2F7DEB44550FEFA_at_LITIGMBCRP02.Corp.Acxiom.net>
Thanks for the additions, Phil. Do you have rules to follow for making REGEXP_* matches optimal? That kind of information is what I was looking for. I don't claim detailed knowledge of regular expression workings, so perhaps you could explain why:
regexp_like(index_name,'^.*(COL)+.*$')
... is 7 to 8 times faster than:
regexp_like(index_name,'.*COL.*')
Is it because without a beginning-of-line anchor the parser doesn't know where to start searching for the pattern, so possibly it acts "greedy" whereas by default I believe REGEXP_* functions in Oracle are "lazy"?
I always like to know the "why" not only because I'm curious but I can then apply it to different situations and also explain it to others instead of just saying, "do it this way because it's faster".
Thx.
DAVID HERRING
DBA
Acxiom Corporation
EML dave.herring_at_acxiom.com TEL 630.944.4762 MBL 630.430.5988
1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.
-----Original Message-----
From: Phil Jones [mailto:phil_at_phillip.im]
Sent: Thursday, January 12, 2012 4:13 PM
To: Herring Dave - dherri
Cc: steve.gardiner_at_hotsos.com; oracle-l_at_freelists.org
Subject: Re: Oracle strace output - detail
Why do you say it shouldn't make a difference?
It makes a huge difference CPU-wise.
Remember you're dealing with a general regexp parsing library, and not an optimised Oracle kernel standard sql LIKE % query. regexp_like will never beat a LIKE % query.
Forget looking at a sqlplus strace - the SQL is executed on the server, not the client. Check the CPU % of the 2 queries in the DB. That'll prove my point.
Cheers,
Phil
On 12 Jan 2012, at 21:35, Herring Dave - dherri <Dave.Herring_at_acxiom.com> wrote:
> Steve,
>
> I can't help with strace, but can give a suggestion with "regexp_like". Try adding in the beginning-of-line and end-of-line anchors:
>
> regexp_like(index_name,'^.*(COL)+.*$')
>
> That *shouldn't* make a difference, but it does, at least under 10.2.0.2. I had great intentions 3 yrs ago to performance test various options with REGEXP functions in Oracle but unfortunately didn't find the time to make it happen. If you figure out why the line anchors make it so much faster than please share, as I've love to know.
>
> DAVID HERRING
> DBA
> Acxiom Corporation
> EML dave.herring_at_acxiom.com
> TEL 630.944.4762
> MBL 630.430.5988
> 1501 Opus Pl, Downers Grove, IL 60515, USA
> WWW.ACXIOM.COM
>
> The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Steve Gardiner
> Sent: Thursday, January 12, 2012 8:40 AM
> To: oracle-l_at_freelists.org
> Subject: Oracle strace output - detail
>
> Oracle-l readers,
>
>
> Does anyone have an idea how to decode this strace entry? Or where to
> look for more info?
>
> 0.000222 read(11,
> "\1w\0\0\6\0\0\0\0\0\20\27\0\0\0\307+\337\201\340\37\314\300\177p\334f\1
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jan 12 2012 - 17:11:22 CST