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

"EnderW" <ender29_at_my-deja.com> wrote in message news:8sd28q$uce$1_at_nnrp1.deja.com...
> 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

>
>

If the application uses bind variables (e.g. select a from table_x where id=:bind_variable) then just changing the bind variable, rebinding, and executing will cut down on the query time - Oracle won't have to reparse etc. If you can't do that and are on 8.1 try share_cursor=force in the init.ora. (Check the Oracle documentation I may be remembering the parameter name a little wrong and I don't have the docs at home.) That will change the sql to use bind variables on the server side. (you should use them in the application, but you change what you can change) I think we would be interested to know if that helps speed things up. Jim Received on Sun Oct 15 2000 - 18:42:30 CDT