transaction speed and size with Spatial index
- From: "Joe Smith" <joe_dba@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 19 Jan 2006 13:54:34 -0600
List,
I need to make a transaction faster or determine that it cannot be made
faster. Resulting in smaller transaction size and/or rewrite the sql
statement. The situation is I have a db server running Solaris 8 with
Oracle 9.2.0.5.0 EE and a web server running Weblogic. The web server uses
connection pooling.
When a specific transaction starts, the process ( user or connection ) will
insert one record into a catalog table. This table includes an Oracle Text
field and a Spatial field. At the same time it is updating 10 to 15 other
tables in the db. Mostly under the weblogic user account.
What happens is the catalog table record is being constantly updated with
latitude and longitude values ( i.e. the Spatial index ). As the update is
occurring to the catalog table, the connection pool starts creating 100 to
200 users and trying to update the same record in the catalog table. This
is the way the app works because the app is constantly trying to track the
position. This record in the catalog table tracks lat/long position. So,
you see what happens. You can use dba_waiters and dba_blockers view created
with catblock.sql and see massive blocking and waiting. If you leave the
system alone, i.e. don?t try to update the record, the system and updates on
the record will catch up.
So, how do I tune the transaction?
I have already read Kyte?s new book and the chapter on transactions. The
only wait event you see in the trace file is enqueue on the catalog table.
Which relates to ?TX? lock on the record.
How do I determine the time a transaction takes? From the beginning to
commit or rollback. And also the size of the transaction as a side bar. In
v$transaction there is a start time col, but I don?t see an end time col.
I am also looking at oracle?s note on TX lock tuning, i.e. primary key
problems, ITL slots , there is no bitmap index, etc?
Has anybody seen this problem before? Has anybody tried to track a position
in a "master table". And is it related to the time it takes to update the
Spatial index?
Thanks.
_________________________________________________________________
Don?t just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
