I'm expereinceing a weird problem.
I have a tabl whihcis basically a collection of
counters (NAME VARCHAR2, COUNTER NUMBER). For various reasons that are
unimportant in this context the tables work by atempting an insert on (NAME, 1)
and if that fails they do an update COUNTER = COUNTER +!; This has been
running fine for 3 years. Suddenly in the past 3 weeks I have been seeing
performance problems and the sudden appearance of some (apprently) constraint
checking recursive sql whihc is in direct proportionto the inserts. This
recursive sql was never in the sqlarea previously. The lines look
like:
select executions, sqltext from
v$sqlarea;
....
166092573 INSERT INTO HITCOUNTER (
ALIAS,USEHITCOUNTER,HITS ) VALUES ( :b1,1,0
)165799528 select c.name, u.name from con$ c, cdef$ cd, user$ u
where c.con# = cd.con# and cd.enabled = :1 and c.owner# =
u.user#....
Further if I do:
desc v$session;select sql_text from v$sqlarea
sq, v$session sewhere prev_sql_addr = 'AA46049C' and prev_hash_value =
'1318728909'and sql_address = sq.address and sql_hash_value =
sq.hash_value;
(that sql_address and hsah_value are for the insert
statement above) I frequently (though not exclusively the recursive
sql)
I am always returned either no rows or that
recursive sql statement (and these are not the most executed statements in the
db either (they are 10th and 11th respectively, and any session is equally
likely to run any of the the top 20 executed queries, in any pairings), so I
would expect to see others if this was just a coincidence.
I've searched Metalink and Google for this sql with
basically no luck, and I have an open Tar which is getting no response.
has anyone seen anything similair? Any clues, thoughts, etc?
--George Schlossnagle1024D/1100A5A0
1370 F70A 9365 96C9 2F5E 56C2 B2B9 262F 1100
A5A0
Received on Mon Jul 23 2001 - 10:58:41 CDT
