Re: Optimizing Programs for ORACLE Apps (RULE Based)

Hi Tannoy,

we had to set this parameter for a problem we had in small tables, OPTIMIZER_INDEX_COST_ADJ = 10
OPTIMIZER_INDEX_CACHING = 90
But based on a previous thread on this forum, I would suggest you set them if you want. but keep in mind maybe they could have not effect on your situation. Maybe another value could be helpful. This is not a MUST for everyone.

The optimizer_max_permutations = 2000 (now 80000), will take more time to parse, but you can get a better execution play, the idea of setting to 80000 is to test, to see if it choose a better execution plan, but to leave set in 80000 permanently is something you have to evaluate.

> db_file_multiblock_read_count = 8 ( now 64 ), it dependes of your operating system.
For example windows blocksize is 128, 8k database block =>16. There is sa trick on statspack ot gather the real size of your system block.

The important about cbo is to recalculate statitsics, and I'm afraid about any secondary effect of doing it, so I suggest you strongly to do in test database

My suggestion about gathering statistics is, you have two kind of gathering The normal compute statistics (use a more specific command to gather only in what you need to gathe statistis) EXEC DBMS_UTILITY.ANALYZE_SCHEMA('ADQ','COMPUTE'); And the histograms.
EXEC DBMS_STATS.GATHER_SCHEMA_STATS( OWNNAME=>'ADQ', ESTIMATE_PERCENT=>100,METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'); In my system, in my situatoin gathering histograms works perfectly, it had increased the performance in 100%, but because the first execution plan could be the wrong, you can slowdown your system instead.

My suggestion is try gathering statistics normally, and test; then gather histograms too and test again and see if this helps to you.

Some other parameter could be
TIMED_STATISTICS = TRUE
And
CURSOR_SHARING = EXACT
SESSION_CACHED_CURSORS = 1000

--
http://www.freelists.org/webpage/oracle-l