RE: ITL waits in the dictionary

This can be an actual problem for some products that feature multi-threaded updates to various “pillars” of a suite and which drop and create indexes as part of patches.  

IF you have that actual problem and you can’t transplant the entire works to a database built with some derivative of large.bsq that is authorized by Oracle, you can work around it without any rule breaking, but it is a bit tedious and does require temporary unavailability of some official indexes.  

1. Update initrans
2. Identify all the indexes or the selection of indexes that seem to get definitions updated in parallel
   1. Generate scripts to rebuild them as a new name with one extra column tacked on the end
   2. And scripts to generate them with the same name and no change
3. Add a few dummy indexes on dummy tables checking that your storage has consumed the freelist and moved into allocating new extents and blocks. These can be many column empty tables, you’re consuming dictionary space, no need to consume user tablespace space. Some not-used user defined for the purpose is ideal. You are filling up this dictionary block permanently.
4. Run generate scripts from 2a. Then drop the indexes you will rebuild with the scripts from 2 b. Add more dummies as per 3.
5. Whew. Very tedious, so don’t do this unless you repetitively have a situation of significance.
6. Now run the scripts from 2b. You can drop the indexes built via 2a, since they ARE in “high ITL” blocks. Don’t drop the dummies that are in “low ITL” blocks.

That’s about it.  

Probably you don’t need to do this, and it might even be less work to rebuild in a new database constructed on top of an approved version of large.bsq. But, if you need to do it and you need to do it in place, this should *not* violate supportability.  

so sorry I couldn’t write this shorter.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark J. Bobak Sent: Wednesday, November 20, 2019 6:57 PM To: oracle_at_dunbar-it.co.uk
Cc: Mladen Gogala; ORACLE-L
Subject: Re: ITL waits in the dictionary  

Agree with Norm. This seems like CTD to me.  

These statistics are for the lifetime of the instance. How long has the instance been up? Is 1,049 more than a blip on the radar? My guess is no....  

-Mark
 

On Wed, Nov 20, 2019, 15:55 Norman Dunbar <oracle_at_dunbar-it.co.uk> wrote:

Hi Mladen,

If you increase initrans it will help with any new blocks but not with the old. However, what actual performance issue are you seeing/experiencing? Try running an AWR report and see what actual effect those 1049 waits have had on your system.

If it's small, does it need worrying about, if it's large, log an SR and see what Oracle says. I'd avoid mucking about in the dictionary except under orders!

Cheers,
Norm.
--

Sent from my Android device with K-9 Mail. Please excuse my brevity.

--

http://www.freelists.org/webpage/oracle-l Received on Fri Nov 22 2019 - 16:21:02 CET