Re: SQL High version count because of too many varchar2 columns
Date: Sat, 15 Sep 2012 21:34:16 -0700 (PDT)
Message-ID: <1347770056.53402.YahooMailClassic_at_web184803.mail.gq1.yahoo.com>
You said you had ORA-1483, and Note 759642.1 is one that mentions both this error and event 10503, and one of the workarounds is to set session_cached_cursors to 0. Although I don't have a strong good reason to recommend it, generally, quite a few shared cursor related bugs either can be worked around by disabling session cursor cache, or the bug reports mention they tried it without success. And the parameter can be set with alter session. So it's worth trying without any risk. It's just a way to eliminate one factor in the complex (easily buggy) cursor sharing mechanism. But if this really works, it's better to work with Oracle to have a real fix instead of leaving session_cached_cursors at 0, to (as you said) create unnecessary mutex contention. The "random string" thing I said is not my idea. Sorry for not being clear. I mean, instead of a thousand child cursors for SQL "select name from emp where id=:1", create 10 SQLs like this: select /*a*/ name from emp where id=:1;
select /*b*/ name from emp where id=:1;
...
select /*j*/ name from emp where id=:1;
Hopefully, each cursor has about 100 children. But it may need a test to see which of the two cases uses less overall CPU. (I think one of your team members did this test a few years ago?)
Yong Huang
- On Sat, 9/15/12, Eagle Fan <eagle.f_at_gmail.com> wrote:
From: Eagle Fan <eagle.f_at_gmail.com>
Subject: Re: SQL High version count because of too many varchar2 columns
To: "Yong Huang" <yong321_at_yahoo.com>
Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org>
Date: Saturday, September 15, 2012, 10:25 PM
Hi Yong:
Thanks for checking this.
Session_cached_cursor is set as 50. Why do you think setting it as 0 help? Flushing the cursor out of shared pool? My concern is it may cause more library cache mutex contention. And flushed out child cursor is still on the chain.
What's the meaning of random strings? I don't understand it. Can you explain more?
Thanks.
On Saturday, September 15, 2012, Yong Huang wrote:
> We have tried 10503 event in QA environment, but it got ORA-01483 errors.
> And I searched for this event, seems it's buggy and doesn't work as
> expected in all versions.
It's interesting that this note
CDC or Streams Apply Process Returns Error ORA-1483 "Invalid length for DATE
or NUMBER bind" [ID 759642.1]
suggests setting event 10503 as the solution for ORA-1483.
Indeed event 10503 sounds good but doesn't work well. You can only set it
with an instance bounce after you set it in pfile or spfile. You can check
it's set with "oradebug eventdump" or check PGA variable kxsusrgl (oradebug
dumpvar pga kxsusrgl). Bug 10274265 says "we set the user defined graduated
length (kxsusrgl) when we initialize the UGA, ie. the variable is not set if
we set event 10503 in the session". Other than UGA/PGA mixup, that statement
is confusing in that the variable *is* set when you set event 10503.
By the way, what is your session_cached_cursors? Could setting it to 0 help?
If everything fails, I think it's better to have the developers artificially
introduce random strings (maybe just a few) to counteract the "benefit" of
bind variables.
Yong Huang
-- Eagle Fan (www.dbafan.com) -- http://www.freelists.org/webpage/oracle-lReceived on Sat Sep 15 2012 - 23:34:16 CDT