compile_schema is slow while fast object checkpoint
ladosMay 6 2013 — edited May 6 2013Hi,
The DBMS_UTILITY.compile_schema(U1,false) normally runs 10 seconds on our "U1" schema (11.2.0.3, oracle linux, 64 bit). But when truncate partition, parallel query runs on an other schema ("U2"), the compilation of "U1" takes more than 1 hour.
top wait events: enq: RO - fast object reuse, enq: KO - fast object checkpoint.
longest sqls of compile: truncate table UTL_RECOMP_ERRORS, truncate table UTL_RECOMP_COMPILED
The compiled shema (U1) is other than U2, and no dependency between U1 and U2.
Both U1 and U2 session wait for CKPT.
If I select all invalid objects of U1, I can recompile them as fast as usual (10 secs) in a loop.
No result found on google for "truncate table UTL_RECOMP_ERRORS".
I know that "fast object checkpoint" can be needed as first part of truncate partition, parallel query. But why an independent schema has to wait for it? Is it a normal behaviour, or can be avoided via configuration?
Thx: lados.