Kevin Kempf's Blog

June 16, 2009

Locked Statistics

Filed under: Oracle — Tags: , , , — kkempf @ 8:12 am

I run the concurrent request gather schema statistics weekly with default settings, against the schema ALL. The past two weeks (since shortly after the 11g RDBMS upgrade) the job failed; I called the first one a fluke but by the second failure I needed to investigate. When I got in on Monday morning, the job was still running with large “concurrent program” spikes and the process was tied up on dictionary lock waits. Did a little digging on Metalink, and found Note 732082.1 which basically says, for whatever reason, you may need to unlock schema stats before your run the gather job. In the end, I just scheduled a pl/sql procedure to run before the stats job. I suppose I could have made it a request group for running statistics gathering; that may have been the “best” answer, but I didn’t want to pollute canned functionality in the apps. I also have to admit I don’t know if it will ever surface again, or why it happened this time. The truth is I just don’t want to have to think about it. 10-20 lines of code later and I’m covered

procedure unlock_schema_stats
is
cursor c1 is
select
distinct(owner) ownername
from
dba_tab_statistics
where
stattype_locked is not null
and
owner not in (‘SYSTEM’,’APPLSYS’,’SYS’)
;
begin
for c1_rec in c1 loop
dbms_stats.unlock_schema_stats(c1_rec.ownername);
end loop
;
end unlock_schema_stats
;

Advertisements

Blog at WordPress.com.