utlrp.sql – How to decrease resource consumption with utlprp.sql

Just being back from Oracle Open World 2017 I’m working on the open topics, the homework, the follow-up activities. And I took several notes during OOW when customers asked me about things I’ve seen before. One topic I wanted to write about a long time ago already: utlrp.sql – How to decrease resource consumption with utlprp.sql. Where it started Actually it’s all Uwe’s fault (Uwe Kirchhoff is one of my best and oldest friends at Oracle and one of the best ACS guys I can think of). Uwe told me this story about a customer case he had years ago….

Will gathering fixed object stats reduce recompilation time post upgrade?

Interesting question, isn’t it? Will the time to recompile invalid objects post upgrade decreased once fixed object stats have been gathered? First of all fixed object stats on X$-tables won’t be gathered by default [This will change with Oracle 12c where it is part of the Auto Stats Gathering job]. X$ structures are undocumented. V$ views are build on top of them and should only be used even though it might be sometimes useful to access X$ tables such as X$BH (buffer headers – contains information describing the current contents of a piece of the buffer cache) sometimes directly. Anyway,…