Hello,
There are a few other threads on this forum about issues with WIAdminServer taking a very long time to boot; the most pertinent one I can find is here:
We are experiencing a very similar problem but can’t fix it via a “Scan / Repair / Compact” because of other bugs / issues related to that process which we’re trying to get resolved.
As the above article points out, the WIAdminServer process blocks on the call to LoadGenPars() call. What’s happening is that the WIAdminServer is generating the following SQL:
select distinct M_GENPAR_N_ID, M_GENPAR_N_USERID, M_GENPAR_N_APPLID,
M_GENPAR_N_TYPE, M_GENPAR_N_VALUE, M_GENPAR_C_LABEL
from OBJ_M_GENPAR,OBJ_M_ACTOR
where M_GENPAR_N_USERID = M_ACTOR_N_ID or M_GENPAR_N_USERID = 0
This SQL is causes the Oracle optimiser to do a full nested table scan on both tables. On our system, with around 10K actors and 150K generic parameters (GENPARs), the query takes over an hour and is growing exponentially.
Obviously WIAdminServer should be “fixed” to correct this problem, but in absence of a fix we could use now, we found that updating the table statistics actually fixes the problem as well. Once there are stats in the tables, oracle decides to do a single scan of each table and then a match sort, which is much much faster (less than 1 second).
However this begs the question: what will the impact be of updating stats on these tables? Will anything else be adversely affected? I know that it’s generally good advice to keep table stats up to date, but as we’ve never done them on the Security Domain tables, we’re worried that something might stop working or start running very inefficiently if we do this.
Does anyone else out there regularly update table statistics on their security domain? If so, are there any known problems or side effects to doing so?
Thanks and best regards,
hansend (BOB member since 2002-12-20)
