version_count
Chuck
Hash: SHA1
Can someone please give me an explanation, or point me to a URL which
has one, of what v$sqlarea.version_count is all about? I understand it
has to do with the # of child cursors for a given SQL in the library
cache, but why would you need more than one cursor? Is that cursor not
shareable? What would make a cursor not shareable? Does it have to do
with different execution plans for the same SQL but with different bind
variables? Or is it simply the # of sessions running the same SQL at he
same time? TIA
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with PCLinuxOS - http://enigmail.mozdev.org
iEYEARECAAYFAkf2WZAACgkQzIf+rZpn0oTspACdFxt0gFkvXgtJ+kMh3oANr0s7
nwkAoJ8SS36nI0QHWoprM7ZMlSG3dhk+
=GE+T
-----END PGP SIGNATURE-----
joel garry
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Can someone please give me an explanation, or point me to a URL which
> has one, of what v$sqlarea.version_count is all about? I understand it
> has to do with the # of child cursors for a given SQL in the library
> cache, but why would you need more than one cursor? Is that cursor not
> shareable? What would make a cursor not shareable? Does it have to do
> with different execution plans for the same SQL but with different bind
> variables? Or is it simply the # of sessions running the same SQL at he
> same time? TIA
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.5 (GNU/Linux)
>
> iEYEARECAAYFAkf2WZAACgkQzIf+rZpn0oTspACdFxt0gFkvXgtJ+kMh3oANr0s7
> nwkAoJ8SS36nI0QHWoprM7ZMlSG3dhk+
> =GE+T
> -----END PGP SIGNATURE-----
From the performance manual: "A SQL statement can map to multiple
cursors, because the objects referred to in the cursor can differ from
user to user."
There's lots of explanations on the web and in books. Here's a good
start (found by searching for v$sqlarea.version_count on
asktom.oraclecom) http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9497064796920
http://www.freelists.org/archives/oracle-l/03-2004/msg03180.html
Dan's primer is pretty good summary of 11: http://www.psoug.org/reference/cursor_sharing.html
A view exists to tell you why:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2124.htm
jg
--
@home.com is bogus.
That darn internet. http://www.ic3.gov/media/2008/080403.htm
Chuck
Hash: SHA1
joel garry wrote:
| On Apr 4, 9:38 am, Chuck <skilover_nos...@bluebottle.com> wrote:
| Can someone please give me an explanation, or point me to a URL which
| has one, of what v$sqlarea.version_count is all about? I understand it
| has to do with the # of child cursors for a given SQL in the library
| cache, but why would you need more than one cursor? Is that cursor not
| shareable? What would make a cursor not shareable? Does it have to do
| with different execution plans for the same SQL but with different bind
| variables? Or is it simply the # of sessions running the same SQL at he
| same time? TIA
| From the performance manual: "A SQL statement can map to multiple
| cursors, because the objects referred to in the cursor can differ from
| user to user."
That makes sense. Different users with objects of the same name would
require different cursors for the same SQL. That's not my case though.
It's a peoplesoft database with a single application schema and everyone
uses the same objects.
| There's lots of explanations on the web and in books. Here's a good
| start (found by searching for v$sqlarea.version_count on
| asktom.oraclecom)
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9497064796920
| http://www.freelists.org/archives/oracle-l/03-2004/msg03180.html
| Dan's primer is pretty good summary of 11:
http://www.psoug.org/reference/cursor_sharing.html
| A view exists to tell you why:
|
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2124.htm
I'll check those links too. Thanks.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with PCLinuxOS - http://enigmail.mozdev.org
iEYEARECAAYFAkf2egwACgkQzIf+rZpn0oSfGgCfYChqyWCwT9g4/IzVCAZtoSKy
3roAoJOEc9hx7AGKj3+fBsOBiaoovUOi
=bCj0
-----END PGP SIGNATURE-----
Chuck
Hash: SHA1
joel garry wrote:
| A view exists to tell you why:
|
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2124.htm
Is there a better explanation of what each column means? For example I
see a lot with BIND_MISMATCH = 'Y'. The doc simply says "The bind
metadata does not match the existing child cursor". What is the bind
metadata? Is it trying to say that maybe different data types were used
for the bind variables by different executions of the SQL?
I also see a lot where all columns = 'N' which still leaves me wondering
why the cursor wasn't shareable.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with PCLinuxOS - http://enigmail.mozdev.org
iEYEARECAAYFAkf2ffYACgkQzIf+rZpn0oTUMACaAgB0AU0KLrIJRDK8GlRvQgZu
A28AnjfZL7iQnmK0fn5HdbLBUL4REMZW
=X9ir
-----END PGP SIGNATURE-----
joel garry
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> joel garry wrote:
>
> | A view exists to tell you why:
>
> Is there a better explanation of what each column means? For example I
> see a lot with BIND_MISMATCH = 'Y'. The doc simply says "The bind
> metadata does not match the existing child cursor". What is the bind
> metadata? Is it trying to say that maybe different data types were used
> for the bind variables by different executions of the SQL?
>
> I also see a lot where all columns = 'N' which still leaves me wondering
> why the cursor wasn't shareable.
>
Do you have underscore parameters set for peoplesoft? Maybe someone
with peoplesoft experience could shed more light.
Search metalink for bind_mismatch, there are known issues.
jg
--
@home.com is bogus.
"I always operated under the presumption that my words could end up on
the front page of the paper, but it's another thing entirely when it
actually happens..." http://www.geek.com/interview-why-nick-white-left-microsoft/
Dion Cho
varchar data type.
Visit here.
http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/
Check following test case:
-- Create sample table
DROP TABLE t_bind_mismatch;
CREATE TABLE t_bind_mismatch(
name1 VARCHAR2(100),
name2 VARCHAR2(100),
name3 VARCHAR2(100)
);
-- Flush Shared Pool
ALTER SYSTEM FLUSH shared_pool;
-- Execute Sample Statements
-- Sample 1
VAR name1 VARCHAR2(10);
VAR name2 VARCHAR2(10);
VAR name3 VARCHAR2(10);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';
INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);
-- Sample 2
VAR name1 VARCHAR2(50);
VAR name2 VARCHAR2(10);
VAR name3 VARCHAR2(10);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';
INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);
-- Sample 3
VAR name1 VARCHAR2(50);
VAR name2 VARCHAR2(50);
VAR name3 VARCHAR2(10);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';
INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);
-- Sample 4
VAR name1 VARCHAR2(50);
VAR name2 VARCHAR2(50);
VAR name3 VARCHAR2(50);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';
INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);
-- Sample 5
VAR name1 VARCHAR2(200);
VAR name2 VARCHAR2(50);
VAR name3 VARCHAR2(50);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';
INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);
-- Sample 6
VAR name1 VARCHAR2(200);
VAR name2 VARCHAR2(200);
VAR name3 VARCHAR2(50);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';
INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);
-- Sample 7
VAR name1 VARCHAR2(200);
VAR name2 VARCHAR2(200);
VAR name3 VARCHAR2(200);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';
INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);
-- Sample 8
VAR name1 VARCHAR2(2500);
VAR name2 VARCHAR2(200);
VAR name3 VARCHAR2(200);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';
INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);
-- Sample 9
VAR name1 VARCHAR2(2500);
VAR name2 VARCHAR2(2500);
VAR name3 VARCHAR2(200);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';
INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);
-- Sample 10
VAR name1 VARCHAR2(2500);
VAR name2 VARCHAR2(2500);
VAR name3 VARCHAR2(2500);
EXEC :name1 := 'NAME';
EXEC :name2 := 'NAME';
EXEC :name3 := 'NAME';
INSERT INTO t_bind_mismatch VALUES(:name1, :name2, :name3);
-- Check V$SQLAREA and version count
SELECT version_count, executions, sql_text
FROM v$sqlarea
WHERE sql_text like 'INSERT INTO t_bind_mismatch VALUES%';
On 4월5일, 오전4시13분, Chuck <skilover_nos...@bluebottle.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> joel garry wrote:
>
> | A view exists to tell you why:
> |http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynvie...
>
> Is there a better explanation of what each column means? For example I
> see a lot with BIND_MISMATCH = 'Y'. The doc simply says "The bind
> metadata does not match the existing child cursor". What is the bind
> metadata? Is it trying to say that maybe different data types were used
> for the bind variables by different executions of the SQL?
>
> I also see a lot where all columns = 'N' which still leaves me wondering
> why the cursor wasn't shareable.
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.5 (GNU/Linux)
> Comment: Using GnuPG with PCLinuxOS -http://enigmail.mozdev.org