Query on Number of Parses and Executions(for Selects from Views)

  • From: <k.sriramkumar@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 6 Apr 2004 11:53:13 +0530

Hi Guru's
    I have a query like this
  select <column_name list>,rowid
  from <view>
  where col1=:b1 and col2 =:b2 and col3='N'
  order by col5
  1. This SQL is executed from PL/SQL and the query static in nature  
  2. Oracle version is
  3. This query would be executed say 2000 times for various values of col1,col2
  4. I traced the session and  tkprof output shows that number of parses and 
executes are 2079. 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     2079    216.92     233.07          0          0          0           0
Execute   2079      0.33       0.43          0          0          0           0
Fetch     5297      1.54       2.71        233      11929          0        3218
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     9455    218.79     236.21        233      11929          0        3218
  I expected that this statement to be parsed once and executed 2079 times. why 
is it getting parsed 2079 times?.
Misses in library cache during parse: 1
which means that the statement was not found in the library cache only once...
My initial guess was that the optimizer was rewriting the query but checked 
query_rewrite_enabled=false...The optimizer mode is RULE...would not be able to 
change the optimizer_mode for now
Would be great if you could throw some light on this please.
Best Regards

Sriram Kumar 


This message contains privileged and confidential information and is intended 
only for the individual named.If you are not the intended recipient you should 
not disseminate,distribute,store,print, copy or deliver this message.Please 
notify the sender immediately by e-mail if you have received this e-mail by 
mistake and delete this e-mail from your system.E-mail transmission cannot be 
guaranteed to be secure or error-free as information could be 
intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain 
viruses.The sender therefore does not accept liability for any errors or 
omissions in the contents of this message which arise as a result of e-mail 
transmission. If verification is required please request a hard-copy version.

Please see the official ORACLE-L FAQ: http://www.orafaq.com
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Other related posts: