Re: concerning soft parses
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 3 Mar 2004 10:34:49 -0000
Mark,
Didn't we hammer out a list, probably not complete,
of the possible different costs. I seem to recall you,
Tanel, and I throwing odds and ends into the pot until
we got:
Hard parse:
includes cache loading, optimisation
Cursor authentication
search and find existing cursor, check and create privilege map
Session caching call
search and find existing cursor, privilege map already exists
Cached (held) cursor call
No search needed, go to held address, but still check for run-time pool
dbms_shared_pool.keep (plus cache/hold)
No search needed, go to held address, check existence of run-time pool
cursor_space_for_time (plus cache/hold)
No search needed, go to held address, go direct to run-time pool
(fill and "lock" memory, so other sessions crash if insufficient shared
pool)
I have sample code that uses a very simple SQL statement
that varies in cost (on 100 repetitions) from 2,800 latch gets
down to 4 latch gets - effectively 28 latch gets per shared
call, down to free of charge after the first few executions.
BUT the possible feature usage depends hugely on the coding
strategy used for the application (and the F.O.C case is
probably totally unachievable in the real world)
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
March 2004 Hotsos Symposium - The Burden of Proof
Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial
April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar
----- Original Message -----
From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, March 03, 2004 7:56 AM
Subject: RE: concerning soft parses
It's essentially a soft parse which is a session cursor cache hit.
Since the session has the cursor cached, there is less work involved,
and most importantly, less library cache latch activity. Less latch
activity means decreased serialization, means increased scalability.
-Mark
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
