Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: why is a simple query parsed always?

Re: why is a simple query parsed always?

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Tue, 11 May 2004 17:42:01 +0200
Message-ID: <c7qs89$j57$1@news.BelWue.DE>


utkanbir wrote:
> Hi ,
>
> I am monitoring the sql trace files (event 10046) , what i realized is
> it seems my sql statements are always parsed . At last i tried to run
> select * from dual in same session several times , and each time when
> i run the query it is parsed again. Why dont i reuse queries?
>
> This is the trace file output after rendering it by using tkprof:
>
> SELECT *
> FROM
> DUAL
>
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> Parse 4 0.00 0.00 0 0 0
> 0
> Execute 4 0.00 0.00 0 0 0
> 0
> Fetch 4 0.00 0.00 0 12 0
> 4
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 12 0.00 0.00 0 12 0
> 4
>
> Misses in library cache during parse: 1
> Optimizer goal: CHOOSE
> Parsing user id: 46
>
>
> Kind Regards,
> hope

What tool did you use? If sql*plus and you entered

sql> select * from dual;

then you shouldn't wonder. The only good news is that after the first hard parse (Misses in library cache during parse: 1) you only did softparse.

If you want to avoid parses altogether ( a very commendable aproach), you'll have to make sure that you're reusing your cursors. e.g, by using dbms_sql or such:

( the following is a slight variation of a demo that can be found in

   Tom Kyte, Expert One-on-One, Chapter 16)

scott_at_DEMO10G>edit
Wrote file afiedt.buf

   1 create or replace procedure demo (p_cnt in int) is    2 l_cursor int;

   3    l_col       varchar2(1);
   4    i           int;

   5 l_status int;
   6 begin
   7 l_cursor := dbms_sql.open_cursor;    8 dbms_sql.parse(l_cursor, 'select * from dual', dbms_sql.native);    9 dbms_sql.define_column (l_cursor, 1, l_col, 1);   10 for i in 1..p_cnt loop
  11      l_status := dbms_sql.execute  ( l_cursor);
  12      while (dbms_sql.fetch_rows (l_cursor) > 0 )
  13      loop
  14        dbms_sql.column_value(l_cursor, 1, l_col);
  15        dbms_output.put_line (l_col);
  16      end loop;
  17     end loop;

  18* end;
scott_at_DEMO10G>/

Procedure created.

scott_at_DEMO10G>alter session set events '10046 trace name context forever, level 4';

Session altered.

scott_at_DEMO10G>exec demo(5);
X
X
X
X
X

And the tkprof report shows:

select *
from
  dual

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.01          0          0          0           0
Execute      5      0.00       0.00          0          0          0           0
Fetch       10      0.00       0.00          0         15          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.01       0.01          0         15          0           5

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 20 (recursive depth: 1)

Rows Row Source Operation

-------  ---------------------------------------------------
       5  TABLE ACCESS FULL OBJ#(222) (cr=15 pr=0 pw=0 time=516 us)



And we're down to 1 parse, just as we wanted. However, this is for every call of the procedure. If you wrap that procedure in a package and open the cursor only once at package instantiation (or first call of the procedure), you're down to one parse per session, the ultimate goal.

And I guess to close the cursor when it's no longer needed wouldn't be too much to ask.

HTH Holger Received on Tue May 11 2004 - 10:42:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US