Re: Simple Performance Problem

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 - 11:12:59 CST