That recursive stmt hasn't to be involved with the
stmt beneath. However, are you using private synonyms
or do you have a lot of tables and objects ?. Why
don't you look at gets and misses in the v$rowcache ?.
Are you suffering of row cache lock wait ?. Are you on
8.1.X ?, do you know the thing about setting
_sqlexec_progression_cost=0 ?. If you don't, read the
note 68955.1.
Regards.
- George Schlossnagle <george_at_omniti.com> wrote:
> 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 se
> where 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 Schlossnagle
> 1024D/1100A5A0 1370 F70A 9365 96C9 2F5E 56C2 B2B9
> 262F 1100 A5A0
>
Eng. Christian Trassens
Senior DBA
Systems Engineer
ctrassens_at_yahoo.com
ctrassens_at_hotmail.com
Phone : 541149816062
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christian Trassens
INET: ctrassens_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Jul 24 2001 - 04:11:00 CDT
