I'm going to ask the obvious question: Have statistics been generated for the schemas in question?
It's assumed that you're now using CBO, and if stats have not been generated, then CBO is making assumptions about the nature of your data. Jared On Monday 14 July 2003 07:54, Barbara Baker wrote: > Oracle 8.1.7.4 > OpenVMS 7.2-1 > DIGITAL TCP/IP Services for OpenVMS Alpha Ver > V5.0A-ECO 3 > > > After an upgrade from Oracle 7.3.4 to 8.1.7.4 on > OpenVMS, some (but not all) of our batch jobs are > suffering severe performance degradation. One of our > critical jobs went from 3 hours to 9 hours elapsed > time. > > The reason is obvious. The solution is not. One of > our jobs increased from 45 minutes to 1 hr 30 min. > The direct i/o for this job increased from 480 to > 1,046,938. (Identical everything. Only difference > 7.3.4 versus 8.1.7.4) This direct i/o number is from > the parent process the process that is communicating > with the detached process actually running the oracle > code via a mailbox (using the bequeath adapter). The > jobs causing trouble are batch jobs running on the > server, and are using bequeath. > > Oracle has been pretty much useless. They recommended > increasing sysgen pql parameters, which did nothing. > > I've been able to achieve the greatest performance > gain for this job by increasing BIOLM (buffered i/o > limit) for bequeath listener (using parameters on > run/detached in startup_bequeath.com), tnslsnr > (increasing lsnrctl.com and tnslsnr.com), and > increasing biolm for Oracle acct and user acct running > the job. > > Anyone out there > - had similar experiences? > - know what in blazes Oracle did to 2-task to shoot up > direct i/o? > - know how to influence the 2-task communication to > use increased buffered i/o and decrease direct i/o? > > Any ideas greatly appreciated. > > Thanks! > Barb > > > > > __________________________________ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
