Re: Q:Sqlnet .. multiple queries in a single packet ?

In article <hblG5.44132$XV.2323858_at_nntp3.onemain.com>,   "Barbara Kennedy" <barbken_at_teleport.com> wrote:
>
> "EnderW" <ender29_at_my-deja.com> wrote in message
> news:8sb391$i7e$1_at_nnrp1.deja.com...
> > In article <8s9k45$gcl$1_at_nnrp1.deja.com>,
> > sybrandb_at_my-deja.com wrote:
> > > In article <8s8csu$ltt$1_at_nnrp1.deja.com>,
> > > EnderW <ender29_at_my-deja.com> wrote:
> > > > Hi,
> > > > My basic question is if there is a way to tell SQLNet to
 bundle
> > > > queries and send them together and get the results. What
 happens is
> > > > that SQLNet sends each packet "each sql" seperately. Is there a
 way
 to
> > > > tell SQLNet to send multiple sql statements in one batch ?
 Thanks
> > > >
> > > > --
> > > > Ender Wiggin
> > > >
> > > > Sent via Deja.com http://www.deja.com/
> > > > Before you buy.
> > > >
> > > There is not.
> > > It has never been there, and it will never be there, as PL/SQL has
 been
> > > devised for that purpose. Also, if you want to do that outside
 PL/SQL,
> > > IMO, there isn't any advantage to that. Most sql statements will
> > > comprise more than one TCP/IP packet, so they are going to be
> > > fragmented anyway.
> > >
> > > Regards,
> > >
> > > --
> > > Sybrand Bakker, Oracle DBA
> >
> > Well I had an unfortunate case of an application sending small
 packets
> > of sql queries "select x.name from table t where t.name = XXX" and
 this
> > packet is small than the max MTU packet size. There are hardly any
> > packets large enough to be 1K even. So I was thinking of trying to
> > merge these small packets into one unit. Thanks for the answer....
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> I think there is a misunderstanding. Sybrand is correct in that
 multiple
> queries will be returned in seperate packets. However, from reading
 your
> response I think there may be some misunderstanding in what seperate
 queries
> are. The example you give above is 1 query. It may return 0 to N
 rows. You
> can retrieve more than 1 row (assuming the result set has more than 1
 row)
> at a time in a packet. I don't know how you are trying ot communicate
 with
> the database, but in OCI and some ODBC drivers you can specify how
 many rows
> to retrieve at a time. (In JDBC also, and I thinkthe Oracle driver
 defaults
> to 10 which is reasonable) In OCI this is called an array fetch and
 it can
> help a lot. In addition in your listener.ora and tnsnames.ora files
 you can
> set the TDU and SDU to increase the size of the packet (provided your
> network allows that else it defaults). I believe these are
 documented in
> the documentation under SQLNet. I know the array fetch can have a
 positive
> effect on performance, (especially if the row is small), but i have
 not
> mucked around with the SDU and TDU settings.
> Jim
>
>

Yep,

   The application issues several queries (same queries but with different values) all of which are selects but those are done to check whether there is such a value in the database. If there is any value returned, it is a single row ( a single value) which can fit in a single packet. If I can find a way to packet at least the queries issued in a single packet, I could at least cut down some response time. However this would require changing the way application works I guess which is not in my power. Thanks

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Oct 15 2000 - 14:58:19 CDT