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

I don't know whether it's relevant or not to your specific case, but SQL statements are always re-parsed for the security/user bits and pieces. The fact that my library cache has an execution plan for 'select * from emp' doesn't mean to say that *you* have the rights to select from EMP. Oracle still has to check that, every time someone issues the command.

Dual is a bit of an odd one to test these things out on in any case. Try it with a different (real) table whilst you're in testing mode.

Regards
HJR Received on Tue May 11 2004 - 16:41:22 CDT