My comments are below ... ----------------- Ron Reidy Lead DBA Array BioPharma, Inc.
-----Original Message----- From: Peter J. Holzer [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 20, 2004 9:10 AM To: [EMAIL PROTECTED] Subject: Re: Slow connection to Oracle 9i [Rearranged quotes for better readability] On 2004-10-19 08:23:53 -0600, Reidy, Ron wrote: > BAXTER, LINCOLN A [mailto:[EMAIL PROTECTED]: >> Ok, then if you use Apache and mod_perl this should be easy... just open >> connection if you don't have it (or you get and error on it), and keep it >> around. >> >> Call commit (even if you have done only selects), at the end of each "event" >> instead of close. > This is an **extremely** bad idea. A commit() should be issues only > when necessary - the cost in the database of a commit is large and > doing so in this random fashion is an invitation to other performance > problems. >I don't know what lincoln means with "event", but if it is a single http >request, then that is not a "random fashion". Maybe I misstated or was misunderstood. To clarify: Issuing commits in the manner described is (IMHO) random fashion. Commit/rollback is for transactional use; a select statement by itself is not a transaction. If it were, then when one uses SQL*Plus or iSQL*Plus, you would need to issue commits all the time. As pointed out in the link I supplied to Steve Adam's website, random commits are a bad idea. Someone else on this list (I forget who) correctly stated this may(?) only apply to high insert situations. However, there are a lot of things that go on under the hood when a commit is issued. In the case of the OP and the traffic he describes, the policy of issuing commits at the end of every script where not needed, could lead to other performance issues. I invite all to go to Steve Adam's website and read up on this. There are others in the Oracle community (Jonathon Lewis, Connor McDonald to name a couple) who also rigorously test Oracle "features". If someone else has the empirical evidence that commit is cheaper than rollback and the impacts to the system are minimal, I invite you to post your test results and your data. In this way, others here can benefit from this knowledge by testing the theory and validating the results. >An http request is often naturally a transaction - the user has clicked >on a button or link and expects that to either work or not work, but not >sort-of-work. >Also, in many environments (and I believe, mod_perl is one of them) you >have absolutely no guarantee that two consecutive requests from the same >"session" will get the same database connection at the server - if you >want any changes to be visible from one request to the next you have to >commit at the end of the request. hp -- _ | Peter J. Holzer | Shooting the users in the foot is bad. |_|_) | Sysadmin WSR / LUGA | Giving them a gun isn't. | | | [EMAIL PROTECTED] | -- Gordon Schumacher, __/ | http://www.hjp.at/ | mozilla bug #84128 This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system.
