Database Tuning Question

Hi,

Per our performance improvement intiative, from the database side, DBA's have suggested to

1. Migrate tablespaces to locally managed.
2. Set cursor_sharing=similar
3. Re-org extents.

Just wanted to check with experts, if the following has any chances of creating problems on application level?

Also, if somebody has done this in the past - Can you share specifics with me?

Appreciate the help, in advance

Regards
Yogi

All,

So, I log a ticket with peoplesoft asking for recommendations for these changes and someone replies to me in 10 mins. Not allowed - not recommended - enter SAR.

So, I refer this guy to Solution ID: 720879 and tell him well someone in your team knows about this so how about you forward this call to him.

Still awaiting response.

Not sure, I dont think it is too much to expect acknowledgement for application compatibility wrt database changes OR maybe it is and men are from venus

Regards
Yogi

Yogi,

hilarious response from Boracle.

Anyway -
1. Migrate tablespaces to locally managed.
Response: YES - absolutely - we do it.
2. Set cursor_sharing=similar
Response: go here to read up on this parameter. We use default of exact. When we migrate to Oracle 10 we'll probably try SIMILAR then.
3. Re-org extents.
Response: Migrating to LOCAL EXTENT MANAGEMENT (#1) will deal with this.

Good Luck,

Larry,

Thanks for the response. A quick one, before I let you go

Per you expertise and best guess, do you think that cursor_sharing similar would bring some performance gain? This change is primarily being done to speed some Cognos queries that run across our database. I am trying to get a feel if the JDE queries would benefit from it.

Just looking for an educated hunch and your answer is as good as the best expert I can consult.

Other items we are planning to execute on:

1. Buffer cache increase.
2. Redo logs increase.
3. Rollback segement addition.
4. Rebuild indexes.

Will really appreciate, if you can suggest some other possible items we can look at.

Ofcourse, I would be posting my testing results here.

Thank you in advance
Regards
Yogi

Yogi,

from what I've read you should stay away from the SIMILIAR setting at your release level (bugs). Try it at Oracle 10 - not 8.1.6.

One thing you didn't mention is how often you analyze the database. This may be one of the bigger performance gains you may realize IF you don't currently analyze on a regular basis.

Larry,

Thanks for your response.

The objects are analyzed daily at 20%. Per our DBA expert, this is excessive and should be limited to stale objects only.

Will look forward to your inputs.

I am also, creating a document which I intend to post here for further references. Would definitely mention you as a "reference"

Regards
Yogi

Yogi,

please don't reference me as an "expert", I'm not. I just know a few things.

(I would agree that daily analysis is excessive)

Re: Database Tuning Question - Oracle Partitioning

We (at least our DBA-team) has done about everything they could think of in order to get a reasonable response out of our JDE system. So far they didn't succeed... I feel we have a platinum-tuned database, running an awful application (try putting 20 man-years of mods in without any tuning consideration).

Some of the Oracle DB mods we've made over the past that helped us:
- Upgrade to Oracle 9.2.0.7
- Putting redo log files on local disks (rest is on NAS)
- Regular cleanup and Reorg of job-master (highly fragmented)
- Standard optimizations (separating indexes and data)
- Etc. etc. etc.

I've asked the DBA-team to give me a top-10 of optimizations over the past year that have proven beneficial for us. Hope to provide you with them later today.

We now have a project to implement Oracle partitioning. Preliminary tests in DEV-environments look VERY promising. E.g. partition on status in order table (999 vs. rest), partition on company in f0911 etc. We don't know yet what it will do in PRD but the signs are looking good. NB: Oracle will charge you for using partitioning (!)

Cheers,
Lambert

Larry,

Thanks for the input regarding the analyze jobs. And, I was planning to refer to you.

I just think that if you know some things, and I dont know anything - about an area - for me, that makes you an expert.

But, please treat this as light hearted humor. I have been following your posts for years now, and I know that you have good knowledge around multiple areas.

Anyways, my DBA has decided the cursor_sharing=similar change, so I have to do some mind boggling testing, to ensure compatibility.

Will post my results.

Do let me know if you think of something else.

Thanks
Yogi

> I have to do some mind boggling testing

You certainly should. Two possibilities spring to mind:
- In 8.1.7, going too far with optimizations may result in frequesnt ORA-00600, killing your sessions in the middle of doing things. Data integrity issues is the main effect of this error. And frequesnt you do not want it to be at all.
- changing this parameter may result in significant (and seemingly random) changes in the performance of different SQL statements, because it may affect some execution plans. You can expect most to improve, which is why you want to implement it, but if a single important to your business statement starts taking much longer to execute, it can make your users unhappy with the overall results.

Hence, I'd also recommend doing long benchmarking before and after, to be able to see and measure the overall net effect.

Re: Database Tuning Question - Oracle Partitioning

Lambert,

Sounds like you have a very large dbms. Would you mind sharing your DBMS size?

Looking forward to your team's "top-10" list.

Cheers,

Alex,

Apologize for the late reading of this post. Have noted your comments and I am definitely going to refer to them during our DBA meeting next week.

Unfortunately, I had to communicate to the team that the Oracle support team literally "shut" doors on us, hence I am going to take expert advice from reputed forum members.

I have entered some pretty nasty but truthful remarks in the survey form, with respect to the support policy with a request to speak to a manager too.

Lets see.

Thanks for the tip.

Regards
Yogi