OTM 5.5: VPD Query Performance - Improvements

[ianlo]

Hi everyone,

We were experiencing terrible performance of our queries under VPDs. The queries were taking approximately 7 mins on average just to complete when run under a domain.

However when run under DBA.ADMIN domain, the same query took < 7 seconds to complete.

Unfortunately, there was no way to run Saved Queries under another domain w/o changing domain grants to allow the Saved Queries to run under DBA.ADMIN (which is a NO NO!)

After examining the queries and tuning them, we managed to improve the performance from 7 mins to 7 seconds even with the VPD profile applied!

Here are the high level approaches and guidelines we used (hopefully it also applies to your queries but we can't guarantee anything)

1) Explain plan of the query when run under VPD profile shows that everytime rows are returned, they are joined with the VPD tables. We should therefore try to ensure that returned rows under each sub-query are as small as possible.

2) Use indexes to try to influence the optimiser to result in index unique scans

3) Hash Joins are extremely expensive on large data sets, hence try to avoid them if possible.

I have attached the query profiles for the query that saw dramatic improvements.

Query wo Optimisation.pdf
- shows the original explain plan without the optimisation

Query w Optimisation.pdf
- shows the new explain plan with optimisation

Note that the number of steps have been reduced from 69 to 67. Important finding is that even though the no. of steps reduced is only 2, this reduction is at the top level loops (refer to previous un-optimsed plan) hence, the performance improves dramatically as it has iterations to execute!

Hope this helps!

Ian

[chrisplough]

Re: OTM 5.5: VPD Query Performance - Improvements

Just a quick (and somewhat off-topic) note. Ian just wrapped up a LONG weekend involving a production v5.0 to v5.5 migration. This was one of the issues we encountered that make the weekend a little ... sleepless, shall we say.

I appreciate that he'd take the time to post this -- especially when I know he really needs to catch some Zzzz's.

Thanks,
Chris

[[email protected]]

Re: OTM 5.5: VPD Query Performance - Improvements

Hi Ian,

Good to know about the production migration from 5.0 to 5.5. I would interested in knowing if you have performed any volume testing on migrated 5.5 (probably Test or Dev environment) for performing bulkplan in comparison to the performance you had in 5.0 environment. Can you compare directly the performance we had on 5.0 to 5.5 after migration.

Also I would like to know what was the weblogic (I hope your app is weblogic and not wepshere or OAS) memory configured in weblogic.conf and for tomcat in tomcat.conf in 5.0 and now in 5.5 setup after migration.

In our case a bulk plan of 25 Orders is taking around 5-6 min on an average, while 77 bulkplans of 25 orders each took around 4 hours to plan. We have the weblogic and tomcat memory configured to 1536 and 1500 respectively. We have App having 4 CPUs around 8G RAM and DB 2 CPUs and 8G RAM. Do you know any planning parameter or properties which can eat up the planning performance after migration which can be turned off if its related to new features 5.5? I understand there are many things needs to be considered on the configuration on OTM and setup when we decided on performance.

Appreciate your information.

Thanks and Regards,
Suresh

[chrisplough]

Re: OTM 5.5: VPD Query Performance - Improvements

Suresh,

In addition to Ian's experience (I look forward to his reply) - I can also recommend increasing your Java heap for WebLogic and Tomcat to 2000MB, if your hardware supports. Also, we've found some performance increases by moving to the latest CU/RU available -- OTM v5.5 CU03 RU02
.

Thanks,
Chris

[[email protected]]

Re: OTM 5.5: VPD Query Performance - Improvements

Hi Chris,

Thanks very much.. well on Monday we are planning to verify the performance after increasing the heap memory to 2000mb.. however I still have some doubt wrt to the planning parameters and properties which has got introduced in 5.5 which is eating the performance and thus causing planning engine to take time to perform the bulk planning.
Please do let me know if you have found any such parameters which can be disabled if not being used.. from your experience so far in 5.5.03 version.

Thanks and Regards,
Suresh

[chrisplough]

Re: OTM 5.5: VPD Query Performance - Improvements

Suresh,

I agree with you - that the functional configuration (planning parameters, properties, etc) of OTM can have a considerable affect on performance -- often more than the hardware and JVM tuning. Unfortunately, though, this is beyond my expertise and a senior functional expert would be able to provide you with a better answer than I can.

I recommend stating a new thread, specifically based on optimizing the planning parameters and properties in v5.5 to improve Bulk Planning and other optimization performance. This will give your issue more visibility and allow the functional experts on the board to help you.

Thanks,
Chris

[[email protected]]

Re: OTM 5.5: VPD Query Performance - Improvements

Dear Chris,

Thanks very much for the information. I have started a new thread for this issue in the same sections.. lets see if anybody have any clue...

Thanks and Regards,
Suresh

[[email protected]]

Re: OTM 5.5: VPD Query Performance - Improvements

Dear Chris,

Well for your information in continuation to increase on JVM Memory to 2000m planned, then we again performed the test,, but unfortunately the results were still the same.. i.e. 500 orders planned in 59 Minutes approximately..
So we need to dig into the OTM for more efficient planning. Anyhow as I said I have opened a new thread for this issue.. so lets hope if get a clue..

Thanks and Regards,
Suresh

[ianlo]

Re: OTM 5.5: VPD Query Performance - Improvements

Hi Ian,

Good to know about the production migration from 5.0 to 5.5. I would interested in knowing if you have performed any volume testing on migrated 5.5 (probably Test or Dev environment) for performing bulkplan in comparison to the performance you had in 5.0 environment. Can you compare directly the performance we had on 5.0 to 5.5 after migration.

Also I would like to know what was the weblogic (I hope your app is weblogic and not wepshere or OAS) memory configured in weblogic.conf and for tomcat in tomcat.conf in 5.0 and now in 5.5 setup after migration.

In our case a bulk plan of 25 Orders is taking around 5-6 min on an average, while 77 bulkplans of 25 orders each took around 4 hours to plan. We have the weblogic and tomcat memory configured to 1536 and 1500 respectively. We have App having 4 CPUs around 8G RAM and DB 2 CPUs and 8G RAM. Do you know any planning parameter or properties which can eat up the planning performance after migration which can be turned off if its related to new features 5.5? I understand there are many things needs to be considered on the configuration on OTM and setup when we decided on performance.

Appreciate your information.

Thanks and Regards,
Suresh

Hi Suresh,

Sorry for this late reply. But yes we did extensive performance and load testing prior to migration to 5.5. We did a direct comparision because we were able to re-create the database and reload the orders again each time. This provided us with a reference environment to ensure that we had no performance issues.

However, during go-live, even after *extensive* performance testing we still hit performance issues. Interestingly the issue was not on the app or web. Rather it was the Oracle DB.

We realised that any DB migration especially one with a large change such as 9i to 10g, you cannot guarantee the same performance.

Honestly, I don't know whats going on inside the Oracle DB but it did not take us long to realise that after the production migration, the bottleneck was the DB as we had performance monitoring on both the app, web and DB. The Oracle was maxing out all our CPU (we have 4 CPU, IBM pSeries 1.6 GHZ Power5, 8 GB RAM)

We ended up having to spend about 1 week re-tuning the Oracle DB parameters as well as our queries.

The important thing is that you should be runninig the gather_table_stats.sql scripts provieded by Oracle (in the glog/oracle/script8 directory) at least once weekly. With the updated stats over a few weeks, performance improved alot.

I know you suspect that it is the app server but I think the app server being *static* code, the only variant here will be the DB. Hence, my bet is on the DB.

What are your thoughts? Have you had success yet?

Thanks and Best Regards,
Ian

[[email protected]]

Re: OTM 5.5: VPD Query Performance - Improvements

Hi Ian,

Thanks very much for replying to this thread even after long delay. You are 100% correct from the experience you said. Yes our issue too was the database server.
When we had the migrated the TEST environment, we used to do the Volume testing of planning around 2000 orders and 500 orders by pumping the data through JMeter and using another automation tool to select those orders and perform bulk plan. Well we had a so-called benchmark from 5.0 version which was taken around 5-6 months back. Where things were getting planned in 2 hours for 2000 orders whereas in 5.5; around 4 hours to plan these same orders. This caused a moment of panic and started struggling around the servers. Well this hurdle we could solve when we go a dramatic increase in performance of planning by enabling Hyperthreading of CPUs (Not Multhreading in Oracle). After this change, we could plan the same 2000orders in 2 hours 15 minutes. This was very good performance we could analyze on. Based on this performance, we were encouraged to migrate the production that weekend, (the date we originally planned to migrate production).
Well then after the migration of production the same weekend, we had a terrible performance issues with saved queries rendered from OR finder pages..after much monitoring and analysis, we could find some parameters in Oracle which needed a tweak which to some extent resolved the issues of performance we faced. Well the major observation we could find was all the queries were doing a full table scan and not using the index thus causing a heavy load on the CPU and resource. We modified some of the critical saved queries with an index hint, while also changed the database optimizer to back to 9.2.0 which helped us to regain back the performance. Thus I can say now the Oracle Database is still using the 9.2.0 optimizer on 10G.
Anyhow the client is now comfortable to an extent with the environment, however there is a big room of improvement and optimization which we are still undergoing that activity...
I appreciate very much your help by providing the information and experience you had from which we could co-relate and believe to full extent that I was on the right track.

Thanks and Regards,
Suresh

[ianlo]

Re: OTM 5.5: VPD Query Performance - Improvements

Dear Suresh,

You are most welcome! Glad that it work well for you! I am also glad that we could co-relate the issues. I did found performance improvement with Oracle 10g optimisation after several weeks of running the gather_table_stats.sql. The built in ADDM process also did suggest further increase of the Oracle SGA as well as number of open cursors.

I got my DBA to run the ADDM report on a weekly basis to help me identify any other DB parameters that would require tweaking.

On a side note, you might want to check out Metalink Note: 467668.1 This details how to run the gather_table_stats.sql script to ensure stable performance.

Thanks and Best Regards,
Ian