Re: Simple Performance Problem

Also note that most unix systems have a limit of 5K that can be send in a pipe at the time. So the TDU can't be bigger than that (actually it is the default).

Exceptions are HP (8K) and NT (later versions go all the way up to 32K).

Anjo.

Jonathan Lewis wrote:

> There is still an 'SQL*Net' connection between
> the front-end and the back-end, it's just that
> the lower layers of the communication stack uses
> shared memory or pipes or whatever the local
> inter-process communication is instead of a
> 'wet-string' network layer such as tcp/ip
>
> If the COBOL is doing one row at a time, I don't
> think this will help though. But in principal you
> could define a BEQ or IPC entry in the server's
> tnsnames.ora (and a matching adjustment in the
> listener.ora) to increase the SDU. In the bit
> description = (
> )
> include the line
> (SDU = 4096)
>
> The absolute maximum depends on the
> mechanism and the O/S, but for some platforms
> BEQ can go up to just short of 32K, from the
> default of (I think) 2K.
>
> NB - I think IPC can be fixed in 7.3 and 8.0,
> but you may find that BEQ can only be rigged
> in 8.0 and above
>
> BTW - if the COBOL program is fetching a
> row and processing it, then you will probably
> see in v$session_wait/event that Oracle is
> spending its time in 'wait for client message'.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Buck Turgidson wrote in message <816nag$a2t$1_at_bgtnsc02.worldnet.att.net>...
> >The COBOL is executed on the same server as the database. Would tuning
> >SQL*Net still be a possibility? My understanding was that it was for
> >network communications. Heck, I could be wrong, though. I think it is as
> >you describe in paragraph (1) - it is retrieving a row, and doing some
> >processing, calculations, etc.
> >
> >
> >Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message
> >news:943118072.29945.0.nnrp-14.9e984b29_at_news.demon.co.uk...
> >>
> >> If you are confident that Oracle is doing its bit
> >> effectively (i.e. identifying the 100,000 rows) and
> >> the problem is getting them to the COBOL program
> >> there are two possibilities:
> >>
> >> 1) Is the COBOL program doing single row fetches.
> >> If so, there is little you can do beyond re-writing it
> >> to use array-fetches.
> >>
> >> 2) If the COBOL is running array fetches, can you
> >> widen the pipeline between the front-end and the
> >> backend, i.e. increase the SDU (session data unit)
> >> of the sql*net connection. There are details of how to
> >> do this in the SQL*Net manuals from around 7.3.3
> >> onwards. You may have to construct an IPC or BEQ
> >> entry for the tnsnames.ora to get the best throughput.
> >>
> >> One item to check first, though, is the v$session_event
> >> (and v$session_wait) for the session running the COBOL
> >> to find out where most of the time is being lost.
> >>
> >>
> >> --
> >>
> >> Jonathan Lewis
> >> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> >>
> >> Buck Turgidson wrote in message
> ><8163t0$4a6$1_at_bgtnsc02.worldnet.att.net>...
> >> >I have been analyzing some performance issues with PeopleSoft payroll.
> >All
> >> >SQL statements are pretty well optimized, but one query, which retrieves
> >> all
> >> >YTD taxes for every employee in the paygroup, comprises 40% of the
> >> run-time.
> >> >The query is executed only once, and returns at least 100K rows,
> >> potentially
> >> >twice that. It begings to return rows quickly, it just has a lot of
> rows
> >> to
> >> >return.
> >> >
> >> >Short of a Catepillar bulldozer, is there a way to speed up the physical
> >> >movement of this data from the datafiles into memory, so it can be
> >> processed
> >> >by PSFT COBOL? Such things as caching the table, parallel query? We
> use
> >a
> >> >pretty hefty RS/6000 AIX with multiple CPU's.
> >> >
> >> >I'd love to hear some "Stupid DBA Tricks"
> >> >
> >> >
> >>
> >>
> >
> >
Received on Thu Nov 25 1999 - 03:20:38 CST