Re: Can Bind variables be an issue with respect to network transfer?

Hi Lothar,

4700 bind variables look really pretty high. What is your default_sdu_size? Have you tried to increase it and compare timing? https://docs.oracle.com/database/121/NETAG/performance.htm#NETAG1476

On Wed, Oct 14, 2020 at 12:04 PM Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

> Hi,
>
> I investigate a software that gets single rows in a loop.
> In this case it is 2400 iterations each retrieving one or no row. Access
> is quick, via primary key index but the execution time is suspiciously
> high.
> My investigation shows in the runtime stats that the statement is
> executed on the DB in a fraction of the time.
> The total run time for the statement is 8 minutes, the total database
> execution time for 2400 executions is about 10 seconds.
> Did iterative stack on the oracle process and it show that the time was
> spend on network transfer.
> But the question still remains why network transfer is done in the
> execution phase of a statement.
> I guess the reason could be bind variables since there are over 4700
> bind variables and the binding is done in the execution phase.
> I wonder if that idea is reasonable. Even if there are many bind
> variables I wonder if it can make up to that amount of time.
> The bind variables are all rather short.
> ...
> That seems to be sending a message. What kind of Message could that be?
> An acknowledge?
>
> Regards
>
> Lothar
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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