!! Please do not post Off Topic to this List !!
As promised, they just keep coming. Our system was churning out tons of
archive logs (at least for us). It was generating about 40-50M an hour at a
fairly constant clip throughout the day. We are nowhere near a 24 hour shop
so this wasn't user load. It also wasn't scheduled batch jobs (and anyway,
those should have shown spikes in log generation). My guess was some wierd
chattiness of the application.
I took 9 different archive logs ( 3 groups of 3 from different light times.
1,3,4am from 2 days) and loaded them into logminer. I was hoping the same
object(s) were being hit over and over again and I could find that via
logminer. I also checked to make sure this happened over all loaded
logfiles. No such luck, BUT there were only ~2300 DML operations and more
than 100,000 commits (about 10/sec). The session_info field in
v$logmnr_contents pointed to 3 PeopleSoft processes: PSBRKDSP, PSSUBDSP,
PSPUBDSP (I'm glad peoplesoft adds this to their session info). Going to
v$sesstat I found that the bulk of 'redo entries', 'redo size', and 'user
commits' came from 3 SIDs (The size of user commits was consistent with the
10commits/sec rate, and v$session matched these sids with the same 3
peoplesoft processes).
Time for a SQL trace. There were basically 2 pieces of SQL being run over
and over (slightly different for each process). A count of 'log file sync'
waits in the trace file again matched the commit rate. The SQL for one of
the processes was:
SELECT TO_CHAR(LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"') FROM
PSAPMSGSUBCSYNC WHERE CHNLNAME=:1
SELECT CHNLLOCK FROM PSAPMSGSUBCLOCK WHERE CHNLNAME=:1 FOR UPDATE OF
CHNLLOCK
Where the bind variables include
ACTUAL_TIME
APE_INDUSTRY
BANK
BUDGET_POSITION
COMMIT_CNTRL_BUDGET_UPDATE
COMPANY_PROPERTY
COMPETENCY
...
Surprisingly Customer Connection had something on this. It is the default
behavior of the Publish/Subscribe feature when configuring the application
server. The suggestion is to "turn off the Broker servers if your not using
them. You can reduce the time interval for which the brokers check in for
work... this will reduce the amount of logging but will not eliminate it.
To change the Scan interval edit your Domain configuration file, through
psadmin, and change the following variable in the dispatcher sections
[PSBRKDSP_dflt (publication broker dispatcher), PSSUBDSP_dflt (subscription
contract dispatcher) and PSPUBDSP_dflt (publication contract dispatcher)]
Scan Interval=10 (currently set to 10 seconds... this is the interval at
which the dispatchers check into the database looking for work) "
Hope this helps. I'm sure they'll be more installments coming.
Henry
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Henry Poras
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
