Re: Simple Performance Problem

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 Sat Nov 20 1999 - 13:21:49 CST