hello,
its not an issue rather doubt which is hunting me from last 2-3 days.
My 10gR2 Oracle database running on
Widows server 2008 R2 Enterprise
Processor ----> intel Xeon cpu E5530@2.4 GHz (4 processor)
RAM------>8GB
its a dedicated server.
Actually i was going through the AWR report whn one of the client came to me with slow database performance during some time interval.
when i had a look at the Top 5 Timed Events in Report it showed me the
Sql*net message from client 834.35s
i told them to run their query so that i can have trace of that.
then time event showed me something nearer to above and our database was taking only 15 sec to carry out the task.
i told them that
"you application/client is taking 834 sec to tell our database to do something.But once the database got your application's request to carry out something, the database is taking only 15 sec to provide you with the result"
so they asked me to justify that the problem is with application side/machine side.
After digging in a lot on the internet ground, chatting with my colleges on cell phone and running through many threads specially
Asktom.oracle.com, i got really confused.
They came up with varied opinions which kept me thinking again & again over the same thing.
In one of his(Thomas kyte) thread the discussion was as follows.
The output of tkprof when the developers run the same program on their PC shows a big difference in
SQL*Net message from client.
The SQL*Net message from client from my tkprof is almost 4-5 times theirs.
Could you please advise what might be the cause of that?
Thanks
Followup August 25, 2009 - 9am Central time zone:
that could mean.....
your machine is slow (their desktop cpus blow your server cpus away) as much as anything.
since their code is not instrumented, try SQL*Net trace (with TIMESTAMPS) can be helpful to check out the time in the network between the client and server. See Note 16658.1 - see if you experience radically different response times on the various machines.
4 stars August 25, 2009 - 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: Jaklin Ekdawi
My Database server is T5120 with 4 core 1.2 GHz Ultra SPARC T2 processor (from O/S it is seen as 32
CPU * 1.2 GHz).
Their PC has 2 CPU * 4 Ghz.
Based on the above, How come their PC is more powerful than my server?
The ASH Report for the execution period of the program shows ¿CPU + Wait for CPU¿ is the top event,
however, tkprof shows ¿SQL*Net from client¿ is the top event waited on.
Top User Events
Event Event Class % Activity Avg Active Sessions
CPU + Wait for CPU CPU 81.66 0.14
log file sync Commit 5.92 0.01
db file scattered read User I/O 2.37 0.00
Can you please explain why there is a difference between tkprof and ASH report?
Thank You
Followup August 25, 2009 - 8pm Central time zone:
I only care about a single cpu, your client program is not using more than one.
I often downloaded stuff from my big bad sparc to my desktop because my desktop was many times faster than the sparc.
look further down in the ash report - sqlnet message from client is an idle wait, we tend to ignore it as it is the time we spent WAITING FOR THE CLIENT.
Their pc looks a lot faster than your server since you are only using a single cpu - your client is not multi-process. It only uses a single cpu.
4 stars August 25, 2009 - 2pm Central time zone
Bookmark | Bottom | Top
Reviewer: Jaklin Ekdawi
Sorry, I forgot to say that during the execution time the cpu on the server was 98-99% idle based
on (using top and sar).
Thanks
Followup August 25, 2009 - 8pm Central time zone:
I agree, because you could not use 31 out of 32 cpus, I would expect it to be almost idle.
97% idle would mean a single cpu was 100% used. So, it sounds like you were using almost 100% of that single cpu you had access to.
and it isn't as fast as their pc.
4 stars August 25, 2009 - 10pm Central time zone
Bookmark | Bottom | Top
Reviewer: Jaklin Ekdawi
The tkprof from the developer's PC is below
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 122 0.39 0.68 0 403 0 0
Execute 122 0.00 0.00 0 0 0 0
Fetch 396 0.09 0.11 0 25354 0 274
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 640 0.48 0.80 0 25757 0 274
Misses in library cache during parse: 122
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 763 0.00 0.00
SQL*Net message from client 763 14.71 312.91
SQL*Net more data to client 122 0.00 0.00
The tkprof for the running the same process on Solaris DB Server
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 124 0.06 0.03 0 0 0 0
Execute 124 0.04 0.01 0 0 0 0
Fetch 400 0.07 0.10 0 802 0 276
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 648 0.17 0.15 0 802 0 276
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 774 0.00 0.00
SQL*Net message from client 774 43.65 964.23
- On Solaris DB server, the CPU is 0.17, however, "SQL*Net message from client" is 964.23.
- On the developer¿s PC, the CPU is 0.48, however, "SQL*Net message from client" is 312.91.
Based on that, how I can approve it is CPU issue?
Followup August 26, 2009 - 7pm Central time zone:
how about this.
have the developers write something that mimics what they do without the database in it. Same sorts of loops and processing they do in the code in the client.
run it on their machine
run it on yours
You'll find your big bad server has cpu's that are extremely slow compared to the desktop machine.
Hence - the time spent waiting for sqlnet message from client is - time spent in YOUR client on YOUR server processing the data returned from the database.
saying "the cpu 0.17 on one and 0.48 on the other" is meaningless, they are completely different cpus with completely different performance characteristics.
And that you have 32 of them - no use to you, you use one..
It looks like your server is about 2-3 times slower then their test machine.
5 stars SQL*Net Message from client may not be an issue from the app May 28, 2010 - 8am Central time zone
Bookmark | Bottom | Top
Reviewer: Thierry B from Paris, France
Hi,
I just read your post as I experienced the exact same problem. I found something that fixed this
problem for me, my sqlnet.ora contained that line after an oracle upgrade:
TRACE_LEVEL_CLIENT=16
I don't know how it came there. But it was the cause of my problem (too long idle time). I simply
removed it and no more wait events.
I hope it can be of any help for anyone.
Followup May 28, 2010 - 8am Central time zone:
that is a client side issue though, isn't it. It is an application side issue, not a database side issue.
5 stars April 15, 2011 - 10am Central time zone
Bookmark | Bottom | Top
Reviewer: A reader
in this thread Tom insisted that the client has access to only 1 CPU on the server even though the questioner told him that his machine is having around 32 CPU'S.
so this thread was the source of my confusion.
i kept asking myself "if the client application has access to one cpu out of 32 cpu's on the server on which the database server is running,then What is the use of multiprocessor? my oracle/client application has access to only one cpu on the server(like Tom said), what other 31 cpu's on the server machine are upto?if one cpu is getting exhausted by the client Application request, then what the other processor are doing ? are they not meant for load balancing?"
when i had a chat on cell phone with my colleagues regarding the oracle access to the cpu's on the server, then they told me that if there are more number of cpu's then the the response time would be fast cause workload is divided among the multiple processor to carry out the task in parallel.
Again i got confused with the deferring opinion..
so just want to have clarification on
* how many processes do i have access to on server machine where my oracle is installed?*
how many processes do the client applicaiton S/W/client machine have access to out of 32 processors?
is workload devided among the cpus on the server ,in case of huge request from the client application?
thanks & i hope my doubt would be cleared