Re: PeopleSoft on Informix??

--0__=vZ0UwUZfo9fZhXWEZY9GVHwHzj49ezKacG0JXTcY2nKNtyAr76KWauJw Content-type: text/plain; charset=us-ascii There is hope. I spend a lot of time tuning Informix for PeopleSoft - it is possible! I recommend you speak to your sales rep about getting one of our Field Performance Engineers out there. You might also want to speak to Informix to see if they will send out one of their PS gurus. There are also some really good free lance consultants out there. My starting point in the luxury of a lab is to turn the SQL timing trace trace on, sorting the hogs to the top of the list and running these hogs on their own with explain on. Often you can speed things up considerably by adding an index or updating stats. Sometimes you have to "seed" work tables (that are cleared out at the end of a batch job) with some realistic data, update stats and then delete the seed data. I always set OPTCOMPIND to 0 - personal preference! Get someone knowledgeable to look at your OPOFC and FET_BUF_SIZE env vars, Kernel AIO, processor affinity, residency, etc. I stress knowledgeable - there's a minefield of patches and version compatibility issues. If you have WAN problems, there have been some major i-net traffic advances recently. Also, our 3 tier is coming out soon. See paragraph 1! If you have long batch runs, look into the possibility of splitting up the work and running parallel. You may need to create separate work tables and run the jobs under different user ids to prevent deadlocks, etc. This is non-trivial. You may also need to fragment tables. I never detach indexes for fragmented tables - personal preference. See paragraph1! As a last resort you can change the SQL. Flatten out correlated subqueries by using joins, temp tables, concatenation, etc. Unfortunately you the end up with support and upgrade issues. Then there are always the basics: Disk I/O stats to see if one dev is taking all the strain, no FG writes, etc. Good luck, Greg (Embedded image moved jdiaz @ accustaff.com to file: 06/11/97 08:27 AM PIC18672.PCX) To: informix-list @ rmy.emory.edu cc: (bcc: Greg Salmon) Subject: PeopleSoft on Informix?? Our company is running PeopleSoft v5.10 - currently in the process of moving over to 6. We are running this on Informix 7.12 on Digital Unix. Now, the former DBA who was in charge of this had tons of problems tuning the PeopleSoft databases. Are things really this bad? He had told me that Informix just couldn't handle the massive number of tables, views, and indexes that peoplesoft used and was nearly impossible to tune well. I was even told not to update statistics on any of the tables because this actually broke the informix optimizer and it would choose the most inappropriate path. I'm not new to Informix, and I've done pretty well on the other databases around here, but he's got me scared to touch the peoplesoft stuff. I'm inclined not to believe some of these things, but then again, I'm not going to go playing around with the parameters until I know for sure. Has anyone else had this experience? Is it really true? If not, are there any tricks I should know to get peoplesoft running well? Thanks for your help, Jeff Diaz, DBA jdiaz@accustaff.com --0__=vZ0UwUZfo9fZhXWEZY9GVHwHzj49ezKacG0JXTcY2nKNtyAr76KWauJw Content-type: application/octet-stream; name="PIC18672.PCX" Content-transfer-encoding: base64 CgUBCAAAAABoACwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAABaQABAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAD1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPH E8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sTzRPHE8MTwhP1E9sT zRPHE8MTwhPwEwzIBgzYE8wTxhPDE8IT7hPOBtcTzBPGE8MTE+wTwgbCBwbCEgbCEgbCEsUG1hPL E8YTwxMT6hMMwgYHwgLCAwISwgfEEsMCwwbVE8sTxRPDExPpE8MGAwcCBwMCwhLDB8ISwgISwgLD BtUTyhPFE8MTE+gTwgIHA8ICEw4DDgLDE8USwwLCEMIG1BPKE8UTwxMT5xMCAwcDAg4TDgITwgIS D8ISD8ISBRICEcICwwbUE8oTxRPCExPmEwYCBwMCDgIOwgLDExITEhPCEg8GxgLDBtMMDAfJE8QT whMT5hMGwwITBgMCDhLFEw8SE8ISBgIDwhIDEsMGB9MDxwwHxRPDExPlEwYHAhESAg8CwhMPwhMP xBMPxRIQwgIDAgMCBtMDxwPEDAfDE8IT4RMHwwzCBgLCEhMCDxLIE8MSD8MSwwIQAwIDBgfSDMkD wgPCDAfCExPbEwfGDMIDDAIHERITEhMSwxMPwxMPwxPDEgIDAgMCwwMCBgzREwfHDMYDDMITE9YT B8UMyAMGB8ICBhLDAsYTEhMSExIPwhIHAgcCAwUQAgYRBgfSE8UTB8QMwgMMwhMT0hMHxAzLA8IM BsISDxESExITAw4DxBMSExITwxICBwPCAsMDDMIGB9ITyRMHwwzCExPPEwfDDMkDxQwHwhMGBxIT AhECEwMOAg7DExITDxMPwxIDAgMCBwMCDAYRBgfSE8kTwhPCDMITE8wTB8MMxwPEDMIHxxMGxBLD Ag4DDgIGwg/IEgIDwgIDAgwCEMIGB9ITyRMHDAcMwhMTyhMHwgzGA8MMwgfMEwYHwhLCEAIOAg4C DhDDAhIPxhIFAgXDAgUCEQYH0hPHEwfCDAcPDMITE8gTB8IMxQPDDAfQEwbDEhDEAhAOEA4QwgLG EgcSBhIGBcMCBcIGB9ATB8UMEwfCDA8HDwwHwhMTxhMHwgzEA8MMB9MTBgfCEhADEMICDhAOEMIC EQIDxxIGBwbCAgUCEQYHyxMHxAwHwhMHEwzCEwcPBw8MB8MTE8UTBwzEA8IMB9YTBsQSEAMCA8UC EQIDAgPDEgcSBgfCBgUQAhDCBgfGEwfEDAfGE8INEwzCEw8HwgwHwxPCE8QTBwzDA8IMB9gTBgfE EhACEMYCEQIDAsQSBhLDBsICEALCBgfCEwfDDAfKEwfCDRMHwhPCDAfEE8ITE8MTBwzCA8IMB9oT DBIHwxLDDBEDxQIDAgPDEgYSBgfCBgIQAhAGDAfCEwzDE8MHyRMHwhPCBxMHxRPDExPDEwzCAwwH 3RMGxxICEQPDAgMCA8MSBhIGBwYMBhACEAIGDMMTDBPCB8YTwwfHEwfGE8MTwhPDEwwDDAfeEwYH xxICEQPDAgMCwhIGEgYHBgwGEAIQAsIGB8MTDMYTwwfKEwzGE8MTwhPDE8IMB98TDBLCB8USAgMR xAISB8ISBgcGDAYQBhAGEAYMB8MMB8kTwwfHEwzGE8MTwhPDEwwPwgzfEwYSB8ISB8ISAhECAwID EgcSBwYHBgwGEAYQxgzDD8IHxRPDB8kTBwzGE8MTwhPDEwzDD8QM3BPCBhIGwxIGAhECAwIHBgcG yAzJDxMHzRMHwwwHxxPDE8ITwxMHDMYPxwwH1BMGEgYSBhLLDM4PwwwTDMcTwgfEDAfJE8QTwhMT xBMHwgzLD9sM0w/GDAfDEwzDEwfEDAfLE8YTwxMTxhMHxAztD8gMBgfIE8QMB84TxxPDE8ITyhMH xwzbD8sMEAUMBcIMwgYH1RPKE8UTwxMT0RMH2wwGEAYQBhACBQwFDAUMBgwHBgfWE8sTxRPDExPu EwYMBhAGEAIGDAYMwwYH1xPLE8YTwxMT8BPKBgfYE8wTxhPDExP1E9sTzRPHE8MTwhP1E9sTzRPH E8MTwhMMAAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA/wAAAP8A//8AAAD/ /wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA/wAAAP8A//8A AAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA/wAAAP8A //8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA/wAA AP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCkgICA /wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vwoKCk gICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw//vw oKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzApsrw //vwoKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDAwNzA psrw//vwoKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICAwMDA wNzApsrw//vwoKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACAAICA wMDAwNzApsrw//vwoKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACAgACA AICAwMDAwNzApsrw//vwoKCkgICA/wAAAP8A//8AAAD//wD/AP//////AAAAgAAAAIAAgIAAAACA gACA//vwoKCkgICA/wAAAP8A//8AAAD//wD/AP////// --0__=vZ0UwUZfo9fZhXWEZY9GVHwHzj49ezKacG0JXTcY2nKNtyAr76KWauJw--