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.



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  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.
> Acxiom Corporation
> EML   dave.herring@xxxxxxxxxx
> TEL    630.944.4762
> MBL   630.430.5988 
> 1501 Opus Pl, Downers Grove, IL 60515, USA
> 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

Other related posts: