It was a pleasure to give you some hints for tuning. So, it is never "too late" to learn about tuning. For this you need quite a lot of background information about CBO and database internals. There are many excellent sources of knowledge in blogs either from my colleagues from OakTable or Oracle ACEs and many others.

Just joking! Can be any value until you really determine the right one what is not so easy.

The parameter optimizer_index_caching is considered by CBO for IN list and NESTED LOOP operations and should be set. The optimizer_index_cost_adj could be very dangerous unless you really know what you are doing, especially when system statistics is used. It was introduced in 8i, but in later releases one should use system statistics to tell CBO what is the timing difference between single block and multiple block I/O. Jonathan Lewis has written about this on his site.

What I really hate is that someone says: set this parameter to a certain value without considering what kind of system is that and what is even more important: without considering what are the consequences of such setting. It is like taking a wrong medicine.

My personal opinion regarding optimizer_index_cost_adj is that one should leave it at 100 (default) and if you really understand the mechanism behind then you may experiment with it. Personally I would use it only at statement level by using OPT_PARAM hint if this would be really necessary. This way you don't make a system wide change.

How can we decide which plan is better without executing the sqls in production ?

I have no doubt about the plan without function. But, if you want to be sure, test just by running the SQL also in production before you make definite change. Is there a big difference among the test database and the production? What about indexes, if you have added some?

There should be no hints in the SQL, remove the gather_plan_statistics hint.

Created a few indexes and function based index on the SUBSTR Function... and forced few indexe scans but it also switches to INDEX range scan from INDEX UNIQUE SCAN(PK_PR_CALENDAR) which is happenig now...but i couldnt a force a index scan on the PR_RECORD_DTL table which has around 80K records...but the A-time is only.28 so i just left it....

Ok, drop everything (don't forget to document what you have dropped to be able to re-create it if required) what was added in order to have the same environment as in production, and then run test again and post the result and we can check what we can do.

Right now I don't have time to study your last response but will come back later.

The query manager to run atleast 3-5 times faster..We will be moving to production by tomorrow after the approvals...will let you know by tomorrow..Did added some Function based indexes the plan looked much better but i couldnt force aa Index on the a the table(80,000 rows) under the view...I am not asking anything here but just to inform you....

Regarding adding indexes - be aware that each index introduces additional load due to its maintenance and slows down the insertion of new records and also maintenance but could be very beneficial in speeding up queries. So you have to decide....Tuning is almost always a trade-off process.

The OakTable book will be published in January 2010 - see the below link to Apress.

According to the estimated time to complete the plan without indexes is better and also has lower cost. But this can sometimes be quite a bad measure so I would suggest you to run both with gathering statistics and then you will know which is really better.

By the way, a full table scan is not always evil, sometimes it is the cheapest and the most optimal access path.