RE: LOB Operation and SQL*Net Message From Client and cursor #0

Correct, the guy using opnet is saying that turn from the app server layer to the db and back is all on the DB, next to nothing on the network itself, and not much with regards to client think time on the client. And of course tracing is saying mostly sql*net message from client, the time to stuff the data in the tcp buffer, go down the wire, client think time, and come back up. And it's not my place to say his tool is wrong and 10046 is right ;-) Maybe they both are right and it's in a virtualization layer that is hit after the NIC. It's something we are considering, though I don't have the skills or knowledge to dig into that, someone else will have to go there.

>
> Can you use a bigger packet?
> Can you try placing your client process directly on the dbserver?

Can't get the client code there, but taking the SQL statement in question, the one driving all the elapsed time, I run SQL*Plus on the server, both through SQL*Net and bypassing SQL*Net, array size 100 like they do, and "set autotrace trace stat" to discard the rows and not eat time rendering them on the screen, and the processing time drops to 1/10 what it was within the application, with the actual DB time about the same. Unaccounted for time was also similar, though percentage wise constituted a much larger slice of total time.

When repeating with SQL*Plus on a desktop, the total timings came more in line with what we see with the application, though the unaccounted for time went up compared to SQL*Plus on the server. The fetch calls match up with the array size, it's just those OPI level trips where we add a lot more trips into the mix, and don't show up on the cursor's fetch count.

>
> One last thing: Usually tracing on is a minor side effect, but if there is a throughput challenge on
> your trace/output directory and your only wait is a long stream of message waits, that could add up to
> significance. So carefully compare the times traced and untraced to rule this out.

Your comments did make me think about going back and looking at unaccounted for time (something, maybe Tanel posting, about those OPI calls work not being counted anywhere). And timing is the same without tracing.

Larry G. Elkins
elkinsl_at_verizon.net
Cell: 214.695.8605

--
http://www.freelists.org/webpage/oracle-l