Re: Oracle strace output - detail
- From: Phil Jones <phil@xxxxxxxxxx>
- To: "Dave.Herring@xxxxxxxxxx" <Dave.Herring@xxxxxxxxxx>
- Date: Thu, 12 Jan 2012 22:12:45 +0000
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@xxxxxxxxxx> 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@xxxxxxxxxx
> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Steve Gardiner
> Sent: Thursday, January 12, 2012 8:40 AM
> To: oracle-l@xxxxxxxxxxxxx
> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
