Possibly burying myself deeper: Parsing is done at the open call. If a cursor needs
to be
re-opened, Oracle will check for permissions whether cursors are cached or not.
Some experiments. First just using bind variables in the statement.
SQL> alter session set session_cached_cursors = 10;
Session altered.
SQL> VARIABLE V_EMPNO NUMBER
BEGIN
:V_EMPNO := 7934;
END;
/
SQL> select ename from scott.emp where empno = :v_empno;
ENAME
----------
MILL
As this is the first statement. I would expect hard and soft parsing to be taking
place.
SQL> BEGIN
2 :V_EMPNO := 7782;
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> select ename from scott.emp where empno = :v_empno;
ENAME
----------
CLARK
What type of parsing is done here. The statement is in the buffer pool
-------------------------------------------------------------------------------------------------
If "scott" revokes privileges
and the above statement is rerun
SQL> /
select ename from scott.emp where empno = :v_empno
*
ERROR at line 1:
ORA-01031: insufficient privileges
----------------------------------------------------------------------------------------------
Scott restores privileges ...
SQL> variable my_select refcursor;
SQL> BEGIN
2 OPEN :my_select FOR SELECT ename from s
3 END;
4 /
PL/SQL procedure successfully completed.
SQL> print my_select
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILL
CLARK
14 rows selected.
------------------------------------------------------------------------------------------
Print closes the cursor.
If "scott" revokes permisssions at this point.
SQL> BEGIN
2 OPEN :my_select FOR SELECT ename from scott.emp;
3 END;
4 /
OPEN :my_select FOR SELECT ename from scott.emp;
*
ERROR at line 2:
ORA-06550: line 2, column 45:
PLS-00904: insufficient privilege to access object SCOTT.EMP
ORA-06550: line 2, column 21:
PL/SQL: SQL Statement ignored
-----------------------------------------------------------------------------------------------
However if scott restores permissions
SQL> BEGIN
2 OPEN :my_select FOR SELECT ename from scott.emp;
3 END;
4 /
PL/SQL procedure successfully completed.
and now revokes them here.
The print statement will still work
SQL> print my_sele
ENAME
----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILL
CLARK
14 rows selected.
------------------------------------------------------------------------------------------------
So how does one keep such cursors open. Given a cursor such as
BEGIN
OPEN :my_select FOR SELECT ename from scott.emp
where empno = :v_empno;
END;
How does one display the information, change the value of :v_empno, and display the
infromation again without re-opening the cursor.
In the distant past when I was writing a lot of Pro*C I'd get the occaisional fetch
out of sequence error when I would change the value of a bind variable and try to
fetch without first opening the cursor. Doesn't one have to re-opne to rebind.
N.B. mail sent in haste -- late for an appointment.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-----Original Message-----
Sent: Thursday, July 25, 2002 12:38 AM
To: Multiple recipients of list ORACLE-L
On Wednesday 24 July 2002 22:08, MacGregor, Ian A. wrote:
> 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.
No, code that uses bind variables need only parse SQL statements
once if session_cached_cursors is set. Further executions of the same
SQL don't require a hard or soft parse.
Jared
> When "cursor-sharing" converts a statement to use bind variables it would
> save on hard parsing, if a match were found the pool; also, it could lessen
> the number of statements present in the pool.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> [EMAIL PROTECTED]
>
> -----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 use lots
> of literals.
> However coding should be done using bind variables in almost all occasions.
>
> 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 cost of
> soft parsing the statement.
> Soft Parsing too frequently limits the scalability of applications and
> sacrifices optimal performance which could have been achieved in the first
> 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 a few
> > bugs when this parameter was set to FORCE. I suggest searching Metalink.
> > But it does work as advertised in later releases. I would also recommend
> > reviewing Tom Kytes' book to read about his views in using this parameter
> > at the instance level (my boss is reading my copy, so I can't give you
> > 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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Jared Still
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
