Re: Does oracle db serialize concurrent read-only SELECT ?

Jonathan,

I am using my ultra-60 machine again, and I had 3000 rows. The 1000 rows was when I tested it on a borrowed e-250.

I'm not sure if I understand the arraysize and what I can do in the client to change this.
In C++, I do execute, and fetch in a loop. In Java, I execute, get the ResultSet, and do next on the ResultSet. I'm fetching it row by row.

I'm not sure if I understand how oracle handles the request. I see processes ora_sxxx_<DBNAME> spawned when I started the database. The number of this processes seems to be controlled by the mts_servers parameter. There is also one dispatcher process. As I understand from oracle doc, query comes from client is forwarded by the multi-threaded dispatcher to the ora_sxxx_<DBNAME> processes, which I assume are single threaded.
However per connection that I created from the client also causes a process oracle<DBNAME> to be spawned.
I thought this is true only if I chose dedicated server option, but I see this even if I set the listener.ora file connect_data server option to shared.

So which process is actually returning data to the client, and how many processes does the data go through ?

Jonathan Lewis wrote:

> The 1,504 waits to and from clients make it
> look as if you have an arraysize of 1 in your
> client and have about 1,500 rows in the query -
> I thought you had said that you had changed
> this to 1,000.
>
> Most of the time waited by this session the 3.93
> seconds waiting for each row to be acknowledged by
> the client and the next to be requested by the client.
> The problem isn't at the database end of the system,
> it seems to be occurring at the client end of the system,
> or perhaps in the network layer between the server
> and the client.
>
> There is some room for error here, as Oracle records
> time only the 1/100 of a second, and you only have
> 1,500 waits, which allows plenty of room for error in
> the significance of the 3.93 total seconds recorded.
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
> Jung Tjong wrote in message <3ADDEBA3.81C413A8_at_cisco.com>...
> >Jonathan,
> >
> >The only events I saw for the sessions are SQL*Net message to and from
> >client
> >and no file related events.
> >Here is for one of the 5 sessions I created:
> >
> > SID EVENT
> >---------- ------------------------------------------------------
> >TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT MAX_WAIT
> >----------- -------------- ----------- ------------ ----------
> > 12 SQL*Net message to client
> > 1504 0 0 0 0
> >
> > 12 SQL*Net message from client
> > 1503 0 393 .261477046 60
> >
> >The numbers that seem large is the total_waits for all events and
> >time_waited
> >for the message from client event.
> >For the 5 sessions, total_waits is 1504 for the "to client"
> >and 1503 for the "from client".
> >The time_waited for the "from client" ranges from 3xx to 5xx.
> >I don't know the significance of these numbers. Do they look normal ?
> >
  Received on Wed Apr 18 2001 - 15:58:07 CDT