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

  • From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 6 Apr 2004 01:53:43 -0500

Sriram,

How many parse *calls* get made is determined entirely by the
application. The application is simply telling the database to parse the
same statement 2,079 times. The Oracle kernel is smart enough to know
that only the first requires a "hard parse," but once the app tells the
db to execute a parse call, the db counts it as a parse call. ...And it
costs a lot of end-user response time to process all these parse calls:
lots of CPU and lots of network latency that shows up as time spent in
"SQL*Net message from client" timed events.

To fix the problem, pull the parse call outside of whatever loop it's in
within the application source code. If it's PL/SQL, then use the
DBMS_SQL package to extract the parse call from the loop so that you
have this:

  /* good */
  dbms_system.parse(...)
  loop
    dbms_sql.execute_and_fetch(...)
  end

...And NOT this:

  /* bad */
  loop
    execute immediate ...
  end


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 4/6 Seattle, 5/7 Dallas, 5/18 New Jersey
- SQL Optimization 101: 4/19 Denver, 5/3 Boston, 5/24 San Diego
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
k.sriramkumar@xxxxxxxxxxxxxxxxxx
Sent: Tuesday, April 06, 2004 1:23 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Query on Number of Parses and Executions(for Selects from
Views)

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 9.2.0.4
  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?.
 
Also,
 
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 

 


DISCLAIMER:
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: