July 11, 2007

Oracle metric – User Calls, Recursive Calls, Execute Count

According to Oracle doc

  • User calls: Number of user calls such as login, parse, fetch, or execute. This metic probabaly incorporates all the activities we are interested from a Java developer’s perspective. It includes Select/update/delete/insert
  • recursive calls: Number of recursive calls generated at both the user and system level. An example of call at user level: a SQL call a store procedure which contains a chunk of SQLs. System recursive call are additional calls for housekeeping such as space management, etc.
  • Execute count: total number of calls (user and recursive) that execute SQL statements. Does that includes “select” statement? I assume not.

Test

To varify my undstanding of these concepts I wrote a program doing DML against a test database. Result is here

Observation

based on Java 3 tier app using JDBC :

  • Execute Count = No. of select/insert/delete/update. For easier understanding. do not think it includes recursive calls as doc indicated.
  • Recursive Calls = No. of update/insert/delete + Ceiling(Hard parse)
  • User Commit = min(No. of update/insert/delete)
  • User Calls = No. of select/insert/delete/update + N * Hard Parse (where N =2 when select and N =1 otherwise.)
With function,
  • All SQLs in a function will includes in EC, UC.
  • A function call will have one RC , one EC and UC

Explanation

  • A sample took from live server : User Calls = 509; Execute Count = 174, Recursive Call = 57 User Commit = 5
  • The app does not use function, so Execute Count is the number of DMLs send from hibernate to database.
  • User Calls is high, it is from hard parse. So the app should use bind variable.(might not true?)
  • Recursive Calls are from hard parse and update/delete/insert. Since user commit is low, so most of RC are hard parse.


Another sample: User Calls = 3872; Exeucte : 1237; Recursive: 28
Recusive is low, so function call and hard parse is low. Why User Calls is so higher than Execute Count??

Looking at v$sql, I find out hundred of SQL like

delete from user_preferences WHERE 
forumid=550 and userid=30387 and name='mailPriority' and value='0'

apparantly cached hard parse cause User Calls high. Cached hard parse wont incure many recursive calls since they are cached. however, one cached hard parse will always generate one user calls.

Action: Ask our developer to use bind variable for this SQL
Expectation: User calls will dropped to EC value.


- No comments Not publicly viewable


Add a comment

You are not allowed to comment on this entry as it has restricted commenting permissions.

Search this blog

Search Warwick Blogs

 

Tags

Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
Not signed in
Sign in

Powered by BlogBuilder
© MMXXI