Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Re: Re: Re: performance problem with oralce dbd

by tilly (Archbishop)
on Apr 16, 2004 at 05:48 UTC ( [id://345649]=note: print w/replies, xml ) Need Help??


in reply to Re: Re: performance problem with oralce dbd
in thread performance problem with oracle dbd

Oracle in particular does not do well with hard parses.

Convert your query to use placeholders (ie pass in ? for each parameter, and then in the execute pass in the data) and performance should improve greatly. More importantly if load matters, hard parses use a type of internal lock known as a latch, and latch contention will cause Oracle to scale very poorly.

Of course prepare_cached with placeholders will perform even better, but just switching to placeholders will make Oracle perform much better.

To understand Oracle's performance characteristics, I can highly recommend virtually anything by Tom Kyte. He has strong opinions on how to use Oracle (some of which I disagree with) but does a very good job of explaining how things work and giving you the tools to explore further.

UPDATE: As noticed by jplindstrom and etcshadow, I misread the code and the hard parses issue that I talked about is indeed a red herring. However if you are benchmarking, be aware that if the real code does not use placeholders, then hard parses will be a problem for the database, and you really want to avoid them.

  • Comment on Re: Re: Re: performance problem with oralce dbd

Replies are listed 'Best First'.
Re: Re: Re: Re: performance problem with oralce dbd
by etcshadow (Priest) on Apr 16, 2004 at 14:17 UTC
    What you're saying is accurate but not applicable to the case in point. For starters, the poster's code is only one query repeated exactly, thus multiple parses would be soft-parses, not hard-parses. Second, the poster does not even repeatedly parse the statement... there's just one parse and many executes.
    ------------ :Wq Not an editor command: Wq
Re: Re: Re: Re: performance problem with oralce dbd
by jplindstrom (Monsignor) on Apr 16, 2004 at 09:50 UTC
    Isn't the example one prepare and many executes? Does Oracle perform multiple hard parses anyway?

    /J

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://345649]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others examining the Monastery: (3)
As of 2024-04-18 04:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found