Tuning stored procedure

I am running a stored procedure, which is mainly querry
intensive. I spent quite some times to tune the querries
individually and after all of them were satisfactory in terms of
performance I ran the procedure in our test environment
and it ran well. But in the production environment,
which is exactly same as test in terms of configuration( more powerful machine with more SGA) procedure is taking hell lot of time.

The thing I noticed that when I ran it in production it is creating 2 sessions whereas it is not the case in test.

Can anybody suggest me what are the areas I should be looking at?

I am using oracle 8.1.5.0.0 on AIX 4.2 with EMC storage.

Thanks in advance

could you paste your store proceedure here along with your query.probably we could help

I would suggest that you use the explain plan and tune the sql statements. Then make sure that if you are opening cursors, there are enough open cursor parameter setted up. Make sure the the table that you are using are all analyzed, such that they could be well used by the cost based optimizer. Also make sure that the indices are also analyzed. If your procedure does any updates try to use indices in the conditional clause. In the sql query and else where try to make use of the bind variables, This would save the library cache misses.

If you have any more problems after using the above stated features, pl. let us know.

Sam

I must admit to being curious as to how the procedure is creating 2 sessions, and how you determined this.

However, what I suspect is that your instances are not as similar as you think. There are *tons* of variables that can affect statement plan generation. You must verify that *all* of them are the same or equivalent (based on machine capabilities but using the same formula to get final value) on the two machines. These include:

Init.ora params:

Independant:
· OPTIMIZER_FEATURES_ENABLE
· OPTIMIZER_INDEX_CACHING
· OPTIMIZER_INDEX_COST_ADJ
· OPTIMIZER_MAX_PERMUTATIONS
· OPTIMIZER_MODE
· OPTIMIZER_PERCENT_PARALLEL
· OPTIMIZER_SEARCH_LIMIT
· HASH_JOIN_ENABLED
· ALWAYS_ANTI_JOIN
· PARTITION_VIEW_ENABLED

Hardware-dependant:
· HASH_AREA_SIZE
· HASH_MULTIBLOCK_IO_COUNT
· SORT_AREA_SIZE
· SORT_WRITE_BUFFER_SIZE
· DB_FILE_MULTIBLOCK_READ_COUNT
· BITMAP_MERGE_AREA_SIZE


Statistics.
- Often, the development environ is smaller and has less data. If you are going to *tune* SQL in development, then it *must* have the statistics copied over from production. Look into DBMS_STATS package. Even better, of course, is to have the same data!
- Same indexes. This means same definition, same size, same stats, same depth.
- Same histograms

There are probably more, but this is what I have handy.

*All* of these variables affect the optimizer and contribute to what plan the optimizer will choose. If *any* of these are different, then your plans could be different in production vs development.

Hope this helps,

- Chris

I agree 100% with Chris, could probably add some more init parameters which can affect performance significally.

What immediatelly comes to my mind is parallel_execution related parameters. The reason for this is the fact that aghosha mentions "...noticed that when I ran it in production it is creating 2 sessions ...). A stored procedure surely can't initiate 2 simultaneous sessions, that's for sure. I think what aghosha actually noticed were two *processes*, not two *sessions". It might be that on production system optimizer has choosen to process some queries in parallel, which might not be the best decision under certain circumstances.

Thanks everybody. I will follow your suggestions and let you know the result.

-- ABOUT 2 SESSIONS
I created a new user who has execute permission on the procedure. During the course of the execution I ran the following querry

SELECT osuser,username,status,terminal
from v$session;

I found 2 osuser of my name and one of them is active
till the procedure finished and other one was getting active status periodically.
I even noticed 2 sessions from SQL NAVIGATOR.

Thanks

Thanks again to everybody. The problem is solved.

I checked all the initialization parameter and did not find any difference.
Fo a small table which has 200 rows oracle optimizer was picking parallel
execution plan. The table definition had DEGREE 2.

But when I ran the querry individually execution plan was perfect.

As soon as I changed the degrre to 1 the execution time came down from 4hrs to
2min.

I appreciate all of your help.