| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Cursor Sharing| Soft Parsing
The way to avoid parse calls is to write your application so that it
doesn't make parse calls. (I hate the term "soft parse"--it's not a
statistic, it's a derivation: it's a 'parse call' that doesn't execute a
'hard parse.')
Examples:
0. For a traditional client server architecture, in which a user connects, does work, and then goes away, here is the wretched-awful way to do it.
/* BAD: requires many parse calls per connect */ loop
cursor = parse(sql_text); execute(cursor); loop fetch(cursor); /* if oct=='select' */ end; close(cursor);
This method produces one parse call per execute. Doesn't scale. Library
cache latch contention, shared pool latch contention, too much CPU
consumption, and all sorts of performance side-effects as you try to
increase a system's number of users. (By the way, "oct" stands for
"Oracle command type," just like in Oracle trace files.)
/* BETTER: requires only one parse call per connect */ cursor = parse(sql_text_with_bind_variables); loop
execute(cursor, bind_val1, bind_val2, ...); loop fetch(cursor); /* if oct=='select' */ end;
This method produces one parse call per login. Scales okay if you only have a few hundred users. But doesn't scale to thousands of users. It eventually produces the same problems as example 0, just after accommodating more users.
2. For many applications, even one parse call per user "connect" is too many. Hence one important motivation for 3-tier architectures. In a 3-tier configuration, you can start application services that make persistent connections to the database, parse each SQL statement once in January when you start your system, and never require another parse of the same SQL statements until your next shutdown/startup.
For example, a service to provide payroll functions to your users might connect to the db immediately upon instance startup at 12:01am January 1. Immediately upon service instantiation, each service would parse each of the payroll application's SQL statements that it will need (ever!), and leave the cursors open, ready for use. When an application browser makes a service request of the persistent service, the apps server tier simply reuses the appropriate cursors that were opened on January 1. The middle tier doesn't close the cursors until you shut down the application for maintenance (e.g., nightly on WinNT, annually on Linux :)). The persistent service is called "persistent" because it stays connected to the db for extended durations and never closes its cursors.
This method scales beautifully to tens of thousands of users and beyond, because the number of parse calls is proportional only to the number of SQL statements in the application, which is a constant.
For more information, hit www.hotsos.com/catalog, and download Jeff Holt's and my "Scaling Applications to Massive User Counts."
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago - Miracle Database Forum, Sep 20-22 Middlefart Denmark - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
-----Original Message-----
Ian A.
Sent: Thursday, July 25, 2002 4:58 PM
To: Multiple recipients of list ORACLE-L
I checked the Tom Kyte site. A soft parse comprises two operations.
One is a simple syntax check;
e.g. "select from dual;" would fail this soft parse as it is missing a
column list or a literal.
The other portion of a soft parse what he calls a semantics check is
checking to see if the tables and columns exist, that the person has
the proper permissions, that there are no ambiguities.
select deptno from emp, dept
where emp.deptno = dept.deptno
/
would fail this type of parse. My Kyte's definition of a soft parse
jibes nicely with the one I used earlier. I didn't include the
syntactical error portion as the statements in question are all valid
SQL. However it is just as important. Semantic and syntactical checks
are done; i.e.., a soft
parse is done before the cache is checked.
Quoting from the article
"The next step in the parse operation is to see if the statement we are
currently
parsing has already in fact been processed by some other session. If it
has ?
we may be in luck here, we can skip the next two steps in the process,
that of
optimization and row source generation. If we can skip these next two
steps in
the process, we have done what is known as a Soft Parse.
While writing this it has suddenly dawned on me what Suhen was talking about when said cursor_sharing = 'FORCE' avoids a hard parse at the cost of a soft.
If this is set
select * from emp where ename = 'KING';
will be soft parsed.
It will be changed to
select * from emp where ename = :bind_variable;
This statement will undergo soft parsing again.
If the statement can be found in cache; then no hard parsing is needed. The generation of the second SQL statement replacing the literal with a bind variables increases the likelihood of not having to hard parse.
1 select a.name, b.value
2 from v$sysstat a, v$sesstat b
3 where a.statistic# = b.statistic#
4 and a.statistic# = 191
5* and b.sid = 8
SQL> /
NAME
VALUE
1* select ename from scott.emp where empno = :v_empno SQL> / ENAME
NAME
VALUE
now if I revoke the permissions on the table.
SQL> /
select ename from scott.emp where empno = :v_empno
*
ORA-01031: insufficient privileges ------------------------------------------------------------------------ -------------------
The article posted by Tom Kyte, does not state that session_cached_cursors avoids soft parses. It says they make finding the cursor less expensive. Particularly the expense of latching the shared pool and the library cache.
He runs a query 1000 times. Once without it being cached and again with it being cached and finds
NAME RUN1 RUN2DIFF
---------------------------------------- ---------- ---------- ---------- LATCH.shared pool 2142 1097 -1045 LATCH.library cache 17361 2388-14973
The lesser latch count is for the query using session_cached cursors. Session_Cached_Cursors do save on resources and are important to scalability. But I have yet to see something which proves they stop soft parsing.
I saw Steve' Orr's contribution
"An entry is created
for the session's cursor cache and future cursor CLOSEs are ignored.
Once in
the session cursor cache the SQL statement does not need to be reparsed.
This gives a significant performance boost!
Giving credit where due: The above was "inspired" from pages 277-280 in
"Scaling Oracle8i" by James Morle."
I have posted material which refutes the above.
Again how does one avoid the soft parsing?
-----Original Message-----
Sent: Wednesday, July 24, 2002 11:43 PM
To: Multiple recipients of list ORACLE-L
Ian,
When coding you should parse once and execute the query many times rather than
loop
parse
bind
execute
close
end;
It can be seen that a parse operation is done on each iteration through
the
loop. You may have avoided hard parsing but the program is still soft
parsing. It has to check the shared pool for the query executed each
time.
When coding u should rather
parse
loop
bind
execute
end;
close;
So you would be parsing once and executing the query several times.
Therefore reduction on latch contention which makes your application
more
scalable and hence better performance.
Check out
http://asktom.oracle.com/pls/ask/f?p=4950:8:1092060::NO::F4950_P8_DISPLA
YID,F4950_P8_CRITERIA:2588723819082,%7Bsoft%7D%20and%20%7Bparsing%7D
Also see Bjorn's paper on bind variables
Cheers
Suhen
> Please define soft parsing. Oracle needs to check that the user
> submitting a SQL statement has permissions to run it. It has to do
this
> every time a statement is run, bind variables or not. I thought the
> processing of the statement to check permissions to be soft parsing.
But,
> perhaps I'm misinformed. > > When "cursor-sharing" converts a statement to use bind variables itwould
> the number of statements present in the pool. > > Ian MacGregor > Stanford Linear Accelerator Center > ian_at_SLAC.Stanford.edu > > -----Original Message----- > Sent: Wednesday, July 24, 2002 9:23 PM > To: Multiple recipients of list ORACLE-L > > > Mike, Kirti, > > Try page 441 > > CURSOR_SHARING=FORCE does improve badly written applications that uselots
> > CURSOR_SHARING=FORCE reduces the hard parsing. > > What CURSOR_SHARING=FORCE does is rewrites all queries to use bind > variables before parsing. > > eg. select ename from emp where empno = 10; > rewritten as > select ename from emp where empno =:SYS_B_0 > or in 8.1.6 , 8.1.7 > select name from emp where empno =:"SYS_B_0" > > So it substitutes the literal with bind variables but incurs the costof
> soft parsing the statement. > Soft Parsing too frequently limits the scalability of applications and > sacrifices optimal performance which could have been achieved in thefirst
> place if written using bind variables. > > Parse once and execute as many times as we like. > > Also check out Bjorn's paper on bind variables and cursor sharing at > http://technet.oracle.com/deploy/performance/pdf/cursor.pdf > > So CURSOR sharing is not the "silver bullet" as one may expect. > > Regards > Suhen > > On Thu, 25 Jul 2002 10:23, you wrote: > > Mike, > > What is the version of the database? Some versions of 8.1.7 had afew
> > page #s). > > > > - Kirti > > > > -----Original Message----- > > Sent: Wednesday, July 24, 2002 6:08 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Has anyone set Cursor Sharing to Force ? > > I have a new system that we have to support > > and there is alot literals filling up the > > pool. I have never changed this parameter > > from the default as many seemed to think the > > jury was still out on it. However, due to > > my situation, I figured I would try it out. > > If anyone has any experience with this one > > I would be curious to know what happened. > > > > Mike
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Suhen Pather INET: Suhen.Pather_at_strandbags.com.au Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: ian_at_SLAC.Stanford.EDU Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cary Millsap INET: cary.millsap_at_hotsos.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Jul 25 2002 - 23:43:23 CDT
 |
 |