Friday, May 7, 2010

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, deletecommit, 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.

18 comments:

  1. Hi Craig
    You are so right. Sometimes it just helps to know what you're talking about. "User Calls", among som many other things, sounds so obvious and self explanatory. But if you ask Oracle DBAs, most of them will probably stumble in their answer.

    Great post! Hope to see you in San Fransisco. You still owe me that coffee :-)

    Regards Lasse Jenssen, Norway

    ReplyDelete
  2. Now if you like to play some friendly prank phone calls on your friends then you might be interested in finding or knowing how to make prank 800 voices. Maybe you would like to stop someone from calling you and don't know how. Here's how.

    ReplyDelete
  3. Android develops and progresses a little bit more every year, but the apps whether free or paid, remain of the same importance as ever before. In this roundup, let's check out what are the best Android apps, available out there for the students. Check This Out

    ReplyDelete
  4. I like this site very much, Its a rattling nice place to read and get info . Mega888 apk download

    ReplyDelete
  5. Technology is vital to the existence and competitive edge of any company. I believe that's the great understatement of the year. Look at what technology allows us to do as compared to 30 years ago; now think back about five years, we've come a long way. best app to spy the camera

    ReplyDelete
  6. Thanks much for another information. It isn?t easy that sort expertise reading through homework, i am keeping an eye out for. 토토사이트

    ReplyDelete
  7. 918Kiss APK Download Now. 918Kiss Android APK and 918KISS iOS mobile App available. Official 918Kiss Download Page in Malaysia. Best Casino App…

    Best Regards:
    918kissvault.com

    ReplyDelete
  8. Ratings & reviews of best SEO companies & agencies in Malaysia. 10seos brings the ranking of top SEO companies, SEO firms, & SEO agencies in Malaysia.

    Best Regards:
    myseodr.com

    ReplyDelete
  9. Currently, there are 2 major types of security camera systems for homes and businesses, and many customers have a hard time choosing between traditional CCTV cameras and newer IP based cameras. Hybrid analog and IP systems provide the ability for both types of cameras, allowing for easy future expansion. These hybrid DVR/NVR systems will provide security camera recording from both styles of cameras seamlessly while allowing users to view their cameras remotely over the internet as well. best hidden spy cameras wireless

    ReplyDelete
  10. Hey there, I think your site might be having browser compatibility issues. When I look at your blog site in Opera, it looks fine but when opening in Internet Explorer, it has some overlapping. I just wanted to give you a quick heads up! Other then that, fantastic blog! bet188

    ReplyDelete
  11. What i don’t realize is in reality how you are no longer actually a lot more neatly-preferred than you may be right now. You are so intelligent. You understand thus considerably with regards to this subject, produced me in my view believe it from so many various angles. Its like women and men aren’t fascinated except it’s one thing to accomplish with Girl gaga! Your individual stuffs great. Always maintain it up! buy steroids

    ReplyDelete
  12. judi bolaJudi Sesudah Terpercaya – Metode Menemukan Yang Layak Dengan Kebutuhan Anda Bila Anda penggemar bermain slot kasino online tetapi pada saat yang ...

    ReplyDelete
  13. daftar slot online . Photo: hncarmer via Instagram. Fourth, this is where Tucson began. Jesuit missionary Father Kino established a "visit" here that, decades ...

    ReplyDelete
  14. best online casino malaysia The playing area of the virtual club features a variety of Online Casino Malaysia slot machines. Games like dice, slots and roulette.

    ReplyDelete
  15. Thanks for sharing very informative article.
    Regards: "Pinoy Tv"

    ReplyDelete
  16. To many people, the term mobile marketing is often thought of as simply some form of advertisement sent to a mobile phone from a company or advertising service.  At one point, this definition might have been correct, but mobile marketing has moved far beyond the simple act of delivering an advertisement to a mobile phone.  In fact, the term mobile marketing can now be considered something of an umbrella term to cover all the aspects of delivering or receiving information with their target audience through a mobile device. best mobile app developer ins ingapore

    ReplyDelete
  17. I regularly visiting the listed content, which is very interesting going to the website everyday. Searching for new, interesting info here. Thanks for sharing the content. Regards - satta guessing

    ReplyDelete
  18. Delving deeper into the realm of Official kk8 reveals a treasure trove of table games, where strategy meets excitement. From the strategic calculations of blackjack to the thrill of predicting the roulette wheel's outcome,

    ReplyDelete