RE: transaction speed and size with Spatial index
- From: <JayMiller@xxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 20 Jan 2006 12:10:10 -0500
Perhaps you could send the update to a queue instead of issuing it
directly to the database? If all updates go to the queue and are read
from it then you have forced serialization.
Jay Miller
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
Sent: Thursday, January 19, 2006 4:17 PM
To: ORACLE-L
Subject: Re: transaction speed and size with Spatial index
> Yes, in talking to the web logic developers that is pretty much how
the
> app works. That is why I stated you see blocking and waiting thru the
> views. The upate "lags" so the connection pool throws another
session
> out there to update the record. After a while there are 100 - 200
> sessions and you know what happens.
>
Well then you have following options:
1) if your business logic (not application logic) definitely dictates
serialization on this row, then why spawn several sessions for this
operation at all? It doesn't matter how many sessions the connection
pool
spawns, there's one single row you're contending on, it won't ever be
updated in parallel. Some kind of single incoming data feed would make
more
sense and would perform better e.g. no waiting for this row lock is
needed.
anyway if you can't change the application logic, then try to minimize
the
time row is locked, e.g. shorten your transaction - for that you need to
use
sql_trace/10046 trace as I already suggested and run it through tkprof
to
see where most of the transaction time is spent. You'll probably see
that
most of transaction time is spent waiting on the TX enqueue for this
row,
but pick the next biggest time consumer for tuning, the TX wait in this
context is a symptom of bad application locking strategy (as long as
there
are no additional TX waits on other rows, in that case we'd have more
complicated situation)
2) if application logic can be changed (can change application logic
without
affecting business logic), then remove the single point of
serialization,
use the inserting mechanism or hash the updates to several different
rows
for example and query the latest location using the maximum value of
update
timestamp.
Tanel.
--
//www.freelists.org/webpage/oracle-l
-----------------------------------------
This message is confidential and sent by TD Waterhouse solely for
use by the intended recipient. If you are not the intended
recipient, you are hereby notified that any use, distribution or
copying of this communication is strictly prohibited. This should
not be deemed as an offer or solicitation, to buy or sell any
product. Any 3rd party information contained herein was prepared by
sources deemed reliable, but is not guaranteed. TD Waterhouse does
not accept electronic instructions that would require an original
signature. Information received by or sent from TD Waterhouse is
stored, subject to review, and may be produced to regulatory
authorities or others with a legal right to such.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
