Message-Id: <10654.119810@fatcity.com> From: "Tom Pall" Date: Thu, 19 Oct 2000 15:07:29 -0500 Subject: Why do I have multiple copies of identical sql reported by v$sql? This is a multi-part message in MIME format. ------=_NextPart_000_01C7_01C039DE.4CA6D9E0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable This is a single instance database, 8.0.5.0, parallelism degree 4 (used = only for recovery, all objects are degree 1). There are 6 identical cursors, each with different execution counts = reported by v$sql. sql> select sql_text text, decode(command_type,2,'INSERT'), executions from v$sql where command_type =3D 2 order by executions desc; TEXT = DECODE EXECUTIONS -------------------------------------------------------------------------= ------- ------ ---------- INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES INSERT 543572 ( :b1,:b2,SYSDATE,:b3,:b4 ) INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES INSERT 403299 ( :b1,:b2,SYSDATE,:b3,:b4 ) INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES INSERT 287133 ( :b1,:b2,SYSDATE,:b3,:b4 ) INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES INSERT 272127 ( :b1,:b2,SYSDATE,:b3,:b4 ) INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES INSERT 257863 ( :b1,:b2,SYSDATE,:b3,:b4 ) INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES INSERT 132471 ( :b1,:b2,SYSDATE,:b3,:b4 ) I query v$sql using like and the text, just to verify. I get 7 rows, = one is the new query against v$sql: sql> select sql_text from v$sql where sql_text like '%INSERT INTO = VAL_ERR ( IP_ADDRE SS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) VALUES ( = :b1,:b2,SYSDATE,:b3,:b4 )%'; SQL_TEXT -------------------------------------------------------------------------= ------- INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES ( :b1,:b2,SYSDATE,:b3,:b4 ) INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES ( :b1,:b2,SYSDATE,:b3,:b4 ) INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES ( :b1,:b2,SYSDATE,:b3,:b4 ) INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES ( :b1,:b2,SYSDATE,:b3,:b4 ) INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES ( :b1,:b2,SYSDATE,:b3,:b4 ) INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES ( :b1,:b2,SYSDATE,:b3,:b4 ) select sql_text from v$sql where sql_text like '%INSERT INTO VAL_ERR ( = IP_ADDRE SS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) VALUES ( = :b1,:b2,SYSDATE,:b3,:b4 )%' 7 rows selected To verify, I select distinct using like and the text. There are 3 rows: = the original text, the first select and the select distinct text. sql> select sql_text from v$sql where sql_text like '%INSERT INTO = VAL_ERR ( IP_ADDRE SS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) VALUES ( = :b1,:b2,SYSDATE,:b3,:b4 )%'; SQL_TEXT -------------------------------------------------------------------------= ------- INSERT INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) = VALUES ( :b1,:b2,SYSDATE,:b3,:b4 ) select distinct(sql_text) from v$sql where sql_text like '%INSERT INTO = VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) VALUES ( = :b1,:b2,SYSDATE,:b 3,:b4 )%' select sql_text from v$sql where sql_text like '%INSERT INTO VAL_ERR ( = IP_ADDRE SS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) VALUES ( = :b1,:b2,SYSDATE,:b3,:b4 )%' 3 rows selected ------=_NextPart_000_01C7_01C039DE.4CA6D9E0 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
This is a single instance database, 8.0.5.0, = parallelism=20 degree 4 (used only for recovery, all objects are degree = 1).

There are 6=20 identical cursors, each with different execution counts reported by=20 v$sql.


sql> select sql_text text, = decode(command_type,2,'INSERT'),=20 executions
from v$sql
where command_type =3D 2
order by = executions=20 desc;



TEXT        =             &= nbsp;           &n= bsp;           &nb= sp;           &nbs= p;            = ;       =20 DECODE=20 EXECUTIONS
-----------------------------------------------------------= ---------------------=20 ------ ----------
INSERT INTO VAL_ERR (=20 IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )  VALUES =20 INSERT     543572
( :b1,:b2,SYSDATE,:b3,:b4  = )

INSERT INTO VAL_ERR ( = IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID=20 )  VALUES  INSERT     403299
(=20 :b1,:b2,SYSDATE,:b3,:b4  )

INSERT INTO VAL_ERR (=20 IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )  VALUES =20 INSERT     287133
( :b1,:b2,SYSDATE,:b3,:b4  = )

INSERT INTO VAL_ERR ( = IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID=20 )  VALUES  INSERT     272127
(=20 :b1,:b2,SYSDATE,:b3,:b4  )

INSERT INTO VAL_ERR (=20 IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )  VALUES =20 INSERT     257863
( :b1,:b2,SYSDATE,:b3,:b4  = )

INSERT INTO VAL_ERR ( = IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID=20 )  VALUES  INSERT     132471
(=20 :b1,:b2,SYSDATE,:b3,:b4  )



I query v$sql using like = and the=20 text, just to verify.  I get 7 rows, one is the new query against=20 v$sql:

sql> select sql_text from v$sql where  sql_text = like=20 '%INSERT INTO VAL_ERR ( = IP_ADDRE
SS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID=20 )  VALUES ( :b1,:b2,SYSDATE,:b3,:b4 =20 )%';


SQL_TEXT
---------------------------------------------= -----------------------------------
INSERT=20 INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) =20 VALUES
( :b1,:b2,SYSDATE,:b3,:b4  )

INSERT INTO VAL_ERR ( = IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )  VALUES
(=20 :b1,:b2,SYSDATE,:b3,:b4  )

INSERT INTO VAL_ERR (=20 IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )  VALUES
(=20 :b1,:b2,SYSDATE,:b3,:b4  )

INSERT INTO VAL_ERR (=20 IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )  VALUES
(=20 :b1,:b2,SYSDATE,:b3,:b4  )

INSERT INTO VAL_ERR (=20 IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )  VALUES
(=20 :b1,:b2,SYSDATE,:b3,:b4  )

INSERT INTO VAL_ERR (=20 IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )  VALUES
(=20 :b1,:b2,SYSDATE,:b3,:b4  )

select sql_text from v$sql = where =20 sql_text like '%INSERT INTO VAL_ERR (=20 IP_ADDRE
SS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )  VALUES (=20 :b1,:b2,SYSDATE,:b3,:b4  )%'

7 rows selected


To = verify, I=20 select distinct using like and the text.  There are 3 rows:  = the=20 original text, the first select and
the select distinct = text.

sql>=20 select sql_text from v$sql where  sql_text like '%INSERT INTO = VAL_ERR (=20 IP_ADDRE
SS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )  VALUES (=20 :b1,:b2,SYSDATE,:b3,:b4 =20 )%';

SQL_TEXT
-------------------------------------------------= -------------------------------
INSERT=20 INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID ) =20 VALUES
( :b1,:b2,SYSDATE,:b3,:b4  )

select = distinct(sql_text)=20 from v$sql where  sql_text like '%INSERT INTO VAL_ERR
(=20 IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )  VALUES (=20 :b1,:b2,SYSDATE,:b
3,:b4  )%'

select sql_text from v$sql=20 where  sql_text like '%INSERT INTO VAL_ERR (=20