"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
Received on Sun Oct 15 2000 - 11:47:46 CDT