Yes, it is done at the parse call. In some cases such as witn DBMS_SQL the calls are explicit .
One writes code to explicitly open the cursor, parse the cursor, execute the cursor, fetches rows from the cursor, and close the cursor. The fetch rows call is often placed in a loop.
When writing most PL/SQL procedures, one writes code which explicitly opens the cursor, fetches rows from the cursor, and closes the cursor. But the open call is really a combination of
open and parse. I'm not sure where the execute happens. I'm confused by the execute then fetch vs. execute_and_fetch dbms_sql calls. In the first case the execute is not placed in the loop, in the latter it is.
When writing a ref cursor when "opens" then prints which also result in the other cursor calls being issued.
If one is using a ref cursor. The parse happens at
OPEN :<ref_cur_variable> FOR <query>;
If the above statement is successful, you can revoke permissions from the user and the "print" statement will be successful. However the next time the "open" is issued in will fail the semantics check of a soft parse. This shows the parse happens at the "open" and not the print. Again I wss using "open" in this sense.
Ian MacGregor
Stanford linear Accelerator Center
ian_at_SLAC.Stanford.edu
Original Message-----
Sent: Friday, July 26, 2002 1:49 PM
To: Multiple recipients of list ORACLE-L
Hmm, parsing is not done at the open (oopen call). It is done in the
oparse/osql call. But they are deffered until the execute. So it looks like
it is done at the execute. In OCI8 it is a different story all together.
Anjo.
- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Thursday, July 25, 2002 6:59 PM
> 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
> ian_at_SLAC.Stanford.edu
>
>
>
>
>
>
>
>
> -----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
> > 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 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: 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: Jared Still
> INET: jkstill_at_cybcon.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).
> --
> 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: Anjo Kolk
INET: anjo_at_oraperf.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).
--
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).
Received on Fri Jul 26 2002 - 19:48:33 CDT
