Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: why is a simple query parsed always?
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;
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;
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