Re: Optimizing Programs for ORACLE Apps (RULE Based)

Hi Juan,

          The parameter values i specified in the brackets are ideal parameters for Oracle HRMS 11o systems.

Like high value of db_file_multiblock_read_count will prevent plan to user full table scan in RBO systems where it uses index like crazy.

i guess high value of optimizer_max_permutations (80000) also has to do something with RBO.

Though the system is in RBO the statistics are gathered periodically ,though there is no use of this but it's being done as a practice i guess.

Usually in RBO systems what happens suddenly as the data grows systems tends to slow down , mark has pointed out such cases.

As the TOP sql's are part of the common codes which runs fine in 11i env for other clients i was thinking in line with you (in you prev mail) to test the interface by setting parameter for that session. There were couple of undocumented parameter are set as per Oracle Support Document. Is it allowed to change the same through execute immediate , i was getting some error as it was not recognizing the '_', might be my syntax was wrong.

I can see i have to do a lot of trail and error case with the parameter still i found the cause. Though i got the long running sql's but cant comment on pl/sql bottlenecks as i haven't explore that.

At least i don't want to tune the common code. :-)

Thanks
Tanmoy

On 7/11/05, Juan Carlos Reyes Pacheco <juancarlosreyesp_at_gmail.com> wrote:
> 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
>

-- 
Regards,
Tanmoy 

-- "Time is the coin of life. Only you can determine how it will be spent."
--
http://www.freelists.org/webpage/oracle-l