Re: Oracle strace output - detail

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-l