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@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
> 
--
http://www.freelists.org/webpage/oracle-l


Other related posts: