- This topic has 11 replies, 3 voices, and was last updated 17 years, 11 months ago by
.
-
Posts
-
I tried the Hibernate reverse engineering wizard last weekend. Tuesday I started receiving performance complaints about a major app I maintain.
After quite a bit of headscratching it turns out that Oracle switched from the rule-based optimizer to the cost-based optimizer because statistics had been computed for the tables I reversed. Since I do a lot af joining with tables in an enterprise app in another schema, none of which are analyzed, my execution plans went off the deep end. Order was restored by dumping the statistics. My sysadmin is going to be livid…
I believe the doumentation should make it clearer that reversing is not necessarily nonintrusive.Frans,
Reverse engineering was certainly designed to be non-intrusive; this is the first time we’ve ever heard about this sort of issue. Are you sure the statistics were not turned on by something / someone else?
Nevertheless, I’ll get the concerned developer to look into this immediately.
We apologize for any inconvenience caused, thank you for your report.The possibility that someone else turned on the statistics is quite remote. My sysadmins certainly had no reason to, and I know enough about the damage you can do to do this knowingly.
The timeline is this:
January 14th, around 10:30 A.M. I was fooling around with the Hibernate reversing option.
January 15th I was not in the office. That evening I started work on updating a Unix cron that runs some maintenance SQL-scripts on my app. I noticed that the job from the preceding night had failed. A query that always runs in less than a minute had been running for 10 hours before failing with a rollback segment error.
January 16th I checked the logs early in the morning and saw that the same SQL was misbehaving again. I cancelled it. I also started receiving complaints from users that the interactive part of the app was very sluggish. I filed an incident report with my ICT-department, in which I asked if someone had accidentally turned on the statistics (!)
I will spare you the further details, but on January 21st I had determined that Oracle was running the cost-based optimizer when it was supposed to be running the RBO. I had earlier glanced at the ALL_TABLES view to check if there were any statistics and had concluded there were not. This time I paged through the whole view and when the tables in my schema came into view I saw – statistics! The LAST_ANALYZED column was set to January 14th, around 10:28/10:29. Realizing that I was probably the moron who had turned on the statistics, I checked the generated Java and XML files. They were from about 10:31/10:32. Most damning was the fact that not all the tables in the schema were analyzed, which fits perfectly with the fact that I had not selected all the tables for reversing.
Your apology is accepted, but I am most to blame. Out of principle, I should not have been doing this with a production database. Live and learn.Frans,
Thanks for the detailed follow up. Could you tell us exactly what Oracle version and Oracle driver version you are using?
The Hibernate developer informed me that for simple RE, turning on statistics is rather pointless – the matter is still being investigated.
Just a sanity check, does the user account you use in our DB Browser (this is the same account we would use to RE) have the necessary privileges to make these changes?Oracle 9i 9.2.0.6 with the latest Oracle thin ojdbc14.zip from Oracle (older versions gave me the infamous NLS logon exception scattered throughout this forum).
Your Hibernate developer is quite right, and this was the reason I did not believe there were any risks.
The user account has the necessary privileges.
I was thinking that I have recently updated CaseStudio to its final version preceding its morph into TOAD data modeller. I have extensive experience with CaseStudio, and it never used to do an analyze (again, what would the point be). I will try to rule out the new version of CaseStudio this evening. If this doesn’t give a result I will scour my machine for other things I might possibly have done that day.
If all fails I will take a shot at reproducing the behaviour.Frans,
MyEclipse hibernate engine (and db browser) don’t touch your statistics in some way.It is simple select command and they can’t change statistics.
You can call analyze or dbms_stats from MyEclipse’s sql editor, but you can be sure : MyEclipse will not do it for you.
If you want user RBO optimizer you can set OPTIMIZER_MODE to RULE in your init.ora file (work on database level) or in start your session (on session level) and you can’t care about database statistics for your RBO optimized queries.FYI : rule based optimizer doesn’t exists in oracle 10
Regards,
I was able to reproduce the problem. It is indeed MyEclipse that is turning on the statistics. Before clicking Finish in the wizard LAST_ANALYZED in ALL_TABLES was empty for all objects in my schema. After clicking finish and waiting for the operation to complete there are statistics for two tables AUTHORISATIE and GEBRUIKERS. This is perfectly understandable. I had selected AUTHORISATIE for reversing, and that table references GEBRUIKERS. The analysis is recursive. If you are not doing it then the driver is suspect.
Frans,
As Peco said above, we checked and we don’t have any code in the RE process which would turn on the statistics.
We appreciate you working with us on this – when you do find out what caused this or if you find a reproducible test case, do let us know.I saw your latest post after I posted mine, we’re looking at your findings internally.
Q: you mentioned ojdbc14.zip – I assume that has 2 separate driver JARs in it? Are you using ojdbc14.jar or ojdbc_g.jar? The latter is a debug driver and not recommended.I downloaded the jar from http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_10201.html. It says it’s a jar, but when the download dialog opens it is suddenly a ZIP. I downloaded the ojdbc14.jar (or ZIP) sized 1,540,457 bytes, not the ojdbc_g.jar. AFAIK, a jar is basically a ZIP following certain conventions and containing Java classes. I am just coming back to Java after a ten year absence so I am a bit shaky on this. The zip I got does not contain jar files, but, at the top level an oracle folder and a folder named META-INF.
If you like, I have a dump of v$sqlarea, containing all the SQL issued today against a quiet test database. It is chock full of analyze table …. estimate statistics commands issued at just the time I was doing my reproduction.Frans,
You are correct, Reverse Engineering generate statictics for some tables.
However, MyEclipse code doesn’t generate statistics.Oracle JDBC driver have bug (it is oracle bug 4999817 but it isn’t fixed in latest jdbc driver) and call ‘analyze table’ internal.
The problem is that Oracle considered that JDBC specification request estimate statistics with latest parameter in getIndexInfo and JDBC driver call ‘analyze table’ when client (our RE engineering or what ever) call getIndexInfo. However, Oracle’s jdbc drivers (mostly latest versions) call this command always.MyEclipse uses index information for Reverse Engineering, but we call simple JDBC commands and doesn’t generate statistics.You will got statistics on table with every client which use Oracle’s jdbc driver and search index information.I can make little demonstration application which call index info from jdbc driver and you will see that it call ‘analyze table’.
Oracle’s JDBC driver have a lot bugs and we have made a lot workarounds until now.I suppose that we will made workaround for this as well.
You are correct for jar as well – zip and jar packing is very similar (you can unpack jar with unzip).ojdbc14.jar is correct file and it is enough for mostly usage.
You can use ojdbc14_g.jar for debugging, but it is slower and if you use i18n you will need orai18n.jar as well.Thank your for your feedback and sorry for the inconvenience caused
Regards,
