Oracle OCI & Net8

[glaeve]

Excuse for my English (I'm French ...)

I would like to send request to database an get the time used by network transmission.

How could I do ?

Thanks in advandce
Glaeve

 

[stevecal]

If you're cunning, you can use DBMS_UTILITY.GET_TIME to do this. What you'll need to do is have a package server side which will call 2 get_times on either side of a small piece of functionality and then determine the elapsed time between them. On the client side you'll need 2 get_times on either side of your call to the package function. The network trasmission time will be the elapsed client side time - the elapsed server side time.

 

[sem]

In all cases DBMS_UTILITY.GET_TIME can not take into account transmission time, for it is executed on server side.

 

[stevecal]

Sem, I accept that GET_TIME is executed server side. And what we are trying to establish here is 'transmission time'

So what is needed is a mechanism to elaborate accurately elapsed time on the client side. Probably if you want this to be wholly accurate you will have to wrap the elapsed time mechanism into a C or a Perl program.

 

[sem]

I supposed that the question was about the 'network transmission', so just informed glaeve, that the time calculated was the time of his program execution, not "the time used by network transmission". If glaeve needs to calculate execution time, he may also use "set timing on" command in sql*plus, which does the same and also prints the results.

 

[stevecal]

This won't fit the bill as SET TIMING ON times elapsed time for an executed SQL statement, and you can't measure what is happening server side. I think what might do it for Glaeve is use of TIMING e.g.

SQL> TIMING START
SQL> exec sp.timer_pkg;
SQL> TIMING STOP
Elapsed 00:00:00:10

Then he has to subtract the server side elapsed time in sp.timer_pkg from the the client side elapsed time....

What do you think?

 

[sem]

I think that for a single database call there is no difference between using TIMING or SET TIMING ON.
I also think that according to the thread's title, our posts are quite unuseful for glaeve, for he probably needs to measure the speed of data transmission via SQL*Net.
Glaeve, could you please explain the problem? Maybe you have some delays with MTS and large amounts of data transmited?

 

[glaeve]

In fact, my goal is to create a benchmark. I can't use ODBC (this is a conbstraint) so i'll use Net8 and OCI.

For this bench,I need to separate the time used by network transmission and time used by server execution.

Glaeve

 

[sem]

In this case you may follow stevecal's second advise, namely use get_time on the server side to mesure execution time and mesure full time elapsed for client call.