Hi all…
This is my first post after learning about this excellent resource! Hopefully this wil help all of us to obtain some closure on the myraid of issues with these products. I am an experienced Business Objects administrator (versions 3 and 4)and I’m sure I can be of help to many of you.
One thing I have not mastered is the Cost Estimate feature, however. I have always had problems with this, and they continue. The following is what I know: 1) You must enable the feature in the tool AND the database (in our case, Oracle) 2) You must manually set up the plan analyzer table (using the provided script) and analyze this table here’s where my problems start – it seems that the with this feature activated, the query exclusively locks the plan analyzer table. When this happens for the first user query, no one else can access the table, resulting in all my users hanging! Can someone provide me w/further explanation here…I know a decent amount of Oracle, but i am not a DBA, and no one has been able to adequately explain this to me. Some folks say that this is a bug with the tool – that no commits are being done when analyzing the query (??) Business Objects insists this is an Oracle issue. Has anyone else experienced hangs with this feature??? Even more bizarre is that this happened last week (my first week on the job), and we have the feature turned off!!! I had users calling saying that their queries were hanging. i called the DBA, who did in fact see the familiar “DELETE PLAN TABLE” activity under the user ID’s. Can anyone shed some light on any of this for me? I would really appreciate any help here. I know what is happening, but not why or how to alleviate… Thanks in advance,
Neal Messier
Fidelity Investments
To turn off the locking on the plan table do the following:
In the Ociv7.sbo file, under either the [Defaults] section or the appropriate database version section ([ORACLE 7.3]), add the line: TxnIsolation=1
Regards,
Luis Gonzalez
From: Neal Messier [SMTP:neal.messier@FMR.COM]
You must manually set up the plan analyzer table (using the provided script) and analyze this table
here’s where my problems start – it seems that the with this feature activated, the query exclusively locks the plan analyzer table. When this happens for the first user query, no one else can access the table, resulting in all my users hanging!
Can someone provide me w/further explanation here…I know a decent amount of Oracle, but i am not a DBA, and no one has been able to adequately explain this to me. Some folks say that this is a bug with the tool – that no commits are being
done when analyzing the query (??) Business Objects insists this is an Oracle issue.
This is slightly off topic, but I tried to “turn off locking” in an MS SQL 6.5 database using BusObj 4.0.5.5 in the .SBO files as suggested below and had ZERO success. After weeks and weeks of pursuing it with BusObj Tech Support they agreed it was a bug and said it would need to be addressed in a future release. I’ve not readdressed the issue under 4.1.X (we went to using views with nolock) so I don’t know if it ever was resolved. I also don’t know if the feature might work in databases other than MS SQL 6.5.
I do remember problems with this parameter with MS SQL Server, but I cannot find any information in my notes right now, and I don’t believe there are any problems with it currently. If and when I find more information, I will post it, but it may be that any problems were fixed in 4.0.5.6a or 4.1.
But as far as using the parameter against Oracle, I posted the solution that I know worked with one customer, so I’m hoping it will work for Neal as well. I hope he lets us know of his success or failure with this solution.
Regards,
Luis Gonzalez
From: David Berg [SMTP:dbberg@DUKE-ENERGY.COM]
This is slightly off topic, but I tried to “turn off locking” in an MS SQL 6.5 database using BusObj 4.0.5.5 in the .SBO files as suggested below and had ZERO success. After weeks and weeks of pursuing it with BusObj Tech Support they agreed it was a bug and said it would need to be addressed in a future release. I’ve not readdressed the issue under 4.1.X (we went to using views with nolock) so I don’t know if it ever was resolved. I also don’t know if the feature might work in databases other than MS SQL 6.5.
To turn off the locking on the plan table do the following:
In the Ociv7.sbo file, under either the [Defaults] section or the appropriate database version section ([ORACLE 7.3]), add the line: TxnIsolation=1
Regards,
Luis Gonzalez
Thanks Luis, I can try that, but I have a couple of questions: 1) Does this mean I’ll have to do this on every client??? (have a bunch of stand-alone setups here right now) 2) Why is this locking even taking place when I have the cost estimate feature turned OFF in the tool? #2 is the real issue here. Last week we had an incident where this locking began, seemingly out of the blue. The feature was turned off, and I was informed that it has been off for quite awhile (months). So to make a long story short, this issue caused major problems in getting out critical reports on time, and now people are looking for answers! I do not know what to tell them…
Any further help would again be appreciated. Thanks,
Neal Messier
Fidelity Investments
Yes, you have to do this for every PC. 2. Check the parameters of each connection, including the connections to the repository: ensure that none have the ‘Cost estimate’ on. Also, what else could have happened “out of the blue” that could have caused the behavior? When did you set up your Oracle database to work with Cost Estimates?
If you do a SQL*Net trace or a Tracker SQLBO log, can you tell if it’s actually BusinessObjects that’s sending out the DELETE PLAN_TABLE statements?
Regards,
Luis Gonzalez
From: Neal Messier [SMTP:neal.messier@FMR.COM] Sent: Monday, October 05, 1998 11:58 AM
Thanks Luis, I can try that, but I have a couple of questions: 1) Does this mean I’ll have to do this on every client??? (have a bunch of stand-alone setups here right now)
2) Why is this locking even taking place when I have the cost estimate feature turned OFF in the tool? #2 is the real issue here. Last week we had an incident where this locking began, seemingly out of the blue. The feature was turned off, and I was informed that it has been off for quite awhile (months). So to make a long story short, this issue caused major problems in getting out critical reports on time, and now people are looking for answers!
Yes, you have to do this for every PC. 2. Check the parameters of each connection, including the connections to the repository: ensure that none have the ‘Cost estimate’ on. Also, what else could have happened “out of the blue” that could have caused the behavior? When did you set up your Oracle database to work with Cost Estimates?
If you do a SQL*Net trace or a Tracker SQLBO log, can you tell if it’s actually BusinessObjects that’s sending out the DELETE PLAN_TABLE statements?
Luis,
Thanks again for your help. In reply to your questions: 1)Setting this parameter for every PC is not a viable option. We have over 100 users in different locations. (Would I have to do this for every PC in a Master/Shared environment as well?) But back to the main issue here, I should not have to do this at all as we have the option turned off! 2) I double checked the parameters of each connection, including the connection to the repository, and the Cost Estimate feature is not checked anywhere. I do not know when the Oracle database was set up to work w/Cost Estimates, just that it is. Other than this, I don’t know all that much about what happens on the database side, other than the fact that it uses the plan table.
From experience, I am pretty sure that it is indeed Business Objects that is sending out the the DELETE PLAN_TABLE statements…shouldn’t you be able to verify this for me? I can certainly run a trace/tracker, but I am not yet set up with a proper test environment to do that. Keep in mind that the Cost Estimate option is not on, and we do not want to turn it on.
My goal here is to try and explain why these DELETE PLAN TABLE statements were issued when the feature is not on. (which, for some reason, took exclusive locks on the PLAN_TABLE, which in turn caused all my users queries to hang during critical month-end report processing). Again, this really did seem to come “out of the blue”, as it is the first time this has happened here since the feature was turned off last year. And just like it appeared, it disappeared! (once the DBA killed the DELETE jobs). It is not happening now, but i need to ensure it does not in the future. A secondary goal is to receive some type of an answer from BO as to why this feature takes exclusive locks on the plan table (i.e. if we wanted to use this feature, how could we overcome this? (other than modifying the setting on each PC) Is it a bug? Is it being addressed?)
Has anyone else had any success (or lack of) using the Cost Estimate feature??? I would love to hear both sides. No offense to you Luis, or Business Objects, but I have been ‘round and round’ with you guys over this issue for quite awhile, and have yet to receive any satisfactory answers…