Understanding Oracle DB User Calls

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.

The Oracle Database instance statistic user calls is frequently tossed around in casual DBA conversations. But it's like one of those big vocabulary words, corpuscle that's in sophisticated books that we hated to read as kids. And as a kid we were too afraid to ask and too lazy to look up the definition. But once we got a little older we figured out that most people are just as stupid and as lazy as we were. Oracle user calls is like one of those words, except that as DBAs it's very important to understand.

The challenge for DBAs is user calls shows up many reports, is frequently used in conversations, and is used during performance and predictive analysis. So unless you really, really know what the words mean you're back in school feeling kind of strange. And as you progress in your Oracle work you'll discover that user calls is frequently a good general metric for database activity as well as a predictor for CPU and IO activity.

But because I'm not always lazy though sometimes stupid, I actually looked up the word in the dictionary, which for us is Oracle documentation. And as you might have expected what I found is pretty much worthless (seriously, don't bother to look it up).

So I figured it was time to go beyond looking up the definition and do some actual research.

To start, consider our first clue, the word user. This is referring to an Oracle user process, which is more commonly called a client process. Client processes are like SQL*Plus or for that matter any process that gets Oracle related work performed by communicating and working in collaboration with an Oracle server process. So this posting is not about server processes or background processes, but only client, that is, user processes.

The second word is obviously call which is also very telling. When the user process makes a request to its server process for some work to be performed in the database, it is literally making a call. But since this call is originating from a user process it's a user call. (Don't mean to insult anyone here.)

So a user call is referring to a user process making a call to a server process to have some work done in the database on its behalf. It gets a little more interesting when we dig into the types of calls a user process makes and even more interesting when we start counting the calls.

While user processes can issue a connect to the database call, the calls that we as performance analysts care about are the classic parse, execute, and fetch calls. These three calls are what forces the server and background processes to perform a potentially tremendous amount of work while consuming operating system resources.

Let's first focus on the parse call. Whenever a SQL statement is run (a word I use to casually describe what happens when I press RETURN in SQL*Plus) it must first be parsed. I'm not going to get into the parsing details, but to say that regardless of the resulting hard, soft, or softer parse, a single call to the server process is performed. Therefore, a parse call results in only one user call.

How do I know this? I created two Oracle SQL*Plus sessions. In window #1 I connect as an application user and retrieve my session identifier (sid) by running something like select sid from v$mystat. Let's say the result is 12. I also know that the user call statistic number is 7 because from a DBA account I ran, select statistic# from v$statname where name='user calls'. From the second window I connect as a DBA and run select value from v$sesstat where sid=12 and statistic#=7. In my test case the application user had so far made 60 user calls. Now as the application user in window #1, I ran the never-before-run SQL statement select count(*) from customers where status='abaci' and then as the DBA account I re-ran select value from v$sesstat where sid=12 and statistic#=7 and observed the returned value was 63, which means 3 user calls had been performed. I then re-ran the application query and then re-ran the v$sesstat query, and once again the user process SQL*Plus made 3 users calls. Just to be sure, I did this a number of times and observed the exact same result.

This does NOT prove a select statement always makes 3 user calls. Because a select statement requires a parse, execution, and fetch call, this proves parsing (hard, soft, or softer), requires a single call.

To understand the number of calls related to executing and fetching, in the table below are my test results. This is important: The Rows column below is about the number of rows returned, not the number of rows processed.

If you take a few minutes to study the table you'll notice a few things:

1. There is always a single parse and single execute call. Even a commit and rollback call require a parse call and an execute call.

2. A select requires a parse and an execute call, but can make multiple fetch calls. The key to understanding this is realizing the number of fetches is from the user process perspective and not the server process perspective. While a server process may make many operating system requests for blocks outside of the Oracle buffer cache or crunch through millions of rows and buffers in the buffer cache, if the user process only requires two fetches to retrieve the results, then the number of user calls will be two!

As a side note, this is why when the SQL*Plus array size is increased from the default of 50 to the max of 250 (set arraysize 250) you will observe both the number of user calls and the number of SQL*Net roundtrips (statistic #557) proportionally decrease. The drop in user calls is because a single fetch call can now handle many more rows at once. The corresponding drop in SQL*Net activity is because each fetch call invokes a single SQL*Net round trip (send and receive).

However, if you look at the numbers closely, you'll notice the number of SQL*Net round trips is one less then you would expect. This is because SQL*Plus bundles the parse and execute calls into a single SQL*Net action. If you write a bogus SQL statement you'll notice that the parse and execute occur (2 calls) but there is no fetch... and there is only a single SQL*Net roundtrip, which means the parse and execute calls were bundled into a single SQL*Net roundtrip.

3. An update, delete, commit, and rollback statement will only make two user calls because the user process will never ever need to fetch rows from the server process. As with a select statement, the server process may be very busy, but from a user process perspective, when an update/delete/commit/rollback statement is run there is no reason to fetch any rows and therefore the number of fetches is zero!

I think the key to understanding a user call is to remember we're referring to an Oracle client/user process making a request to its server process. Period.

So now when you're standing around talking DBA-smack and someone throws out the word user call, you can look them straight in the eye and not be thinking, "Should I ask what a user call is?"

Oh... a corpuscle is a minute particle of matter.

Thanks for reading!

Craig.


P.S. If you want me to respond to a comment or have a question, please feel free to email me directly at craig@orapub.com. I use a challenge-response spam blocker, so you'll need to open the challenge email and click on the link or I will not receive your email.