Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Why do I have multiple copies of identical sql reported by v$sql?

Why do I have multiple copies of identical sql reported by v$sql?

From: Tom Pall <tom_at_cdproc.com>
Date: Thu, 19 Oct 2000 15:07:29 -0500
Message-Id: <10654.119810@fatcity.com> 





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


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Dwindows-1252" =


http-equiv=3DContent-Type>

<META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>This is a single instance database, 8.0.5.0, =


parallelism=20


degree 4 (used only for recovery, all objects are degree =
1).<BR><BR>There are 6=20


identical cursors, each with different execution counts reported by=20
v$sql.<BR><BR><BR>sql&gt; select sql_text text, =
decode(command_type,2,'INSERT'),=20


executions<BR>from v$sql<BR>where command_type =3D 2<BR>order by =
executions=20

desc;<BR><BR><BR><BR>TEXT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n=
bsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=


;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
DECODE=20


EXECUTIONS<BR>-----------------------------------------------------------=
---------------------=20




------ ----------<BR>INSERT INTO VAL_ERR (=20
IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp; VALUES&nbsp;=20
INSERT&nbsp;&nbsp;&nbsp;&nbsp; 543572<BR>( :b1,:b2,SYSDATE,:b3,:b4&nbsp; =



)<BR><BR>INSERT INTO VAL_ERR ( =


IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID=20

)&nbsp; VALUES&nbsp; INSERT&nbsp;&nbsp;&nbsp;&nbsp; 403299<BR>(=20


:b1,:b2,SYSDATE,:b3,:b4&nbsp; )<BR><BR>INSERT INTO VAL_ERR (=20

IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp; VALUES&nbsp;=20
INSERT&nbsp;&nbsp;&nbsp;&nbsp; 287133<BR>( :b1,:b2,SYSDATE,:b3,:b4&nbsp; =



)<BR><BR>INSERT INTO VAL_ERR ( =


IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID=20

)&nbsp; VALUES&nbsp; INSERT&nbsp;&nbsp;&nbsp;&nbsp; 272127<BR>(=20


:b1,:b2,SYSDATE,:b3,:b4&nbsp; )<BR><BR>INSERT INTO VAL_ERR (=20

IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp; VALUES&nbsp;=20
INSERT&nbsp;&nbsp;&nbsp;&nbsp; 257863<BR>( :b1,:b2,SYSDATE,:b3,:b4&nbsp; =



)<BR><BR>INSERT INTO VAL_ERR ( =


IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID=20

)&nbsp; VALUES&nbsp; INSERT&nbsp;&nbsp;&nbsp;&nbsp; 132471<BR>(=20


:b1,:b2,SYSDATE,:b3,:b4&nbsp; )<BR><BR><BR><BR>I query v$sql using like =

and the=20


text, just to verify.&nbsp; I get 7 rows, one is the new query against=20
v$sql:<BR><BR>sql&gt; select sql_text from v$sql where&nbsp; sql_text =
like=20


'%INSERT INTO VAL_ERR ( =



IP_ADDRE<BR>SS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID=20
)&nbsp; VALUES ( :b1,:b2,SYSDATE,:b3,:b4&nbsp;=20

)%';<BR><BR><BR>SQL_TEXT<BR>---------------------------------------------=
-----------------------------------<BR>INSERT=20


INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp;=20
VALUES<BR>( :b1,:b2,SYSDATE,:b3,:b4&nbsp; )<BR><BR>INSERT INTO VAL_ERR ( =



IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp; VALUES<BR>(=20


:b1,:b2,SYSDATE,:b3,:b4&nbsp; )<BR><BR>INSERT INTO VAL_ERR (=20

IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp; VALUES<BR>(=20


:b1,:b2,SYSDATE,:b3,:b4&nbsp; )<BR><BR>INSERT INTO VAL_ERR (=20

IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp; VALUES<BR>(=20


:b1,:b2,SYSDATE,:b3,:b4&nbsp; )<BR><BR>INSERT INTO VAL_ERR (=20

IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp; VALUES<BR>(=20


:b1,:b2,SYSDATE,:b3,:b4&nbsp; )<BR><BR>INSERT INTO VAL_ERR (=20

IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp; VALUES<BR>(=20


:b1,:b2,SYSDATE,:b3,:b4&nbsp; )<BR><BR>select sql_text from v$sql =

where&nbsp;=20


sql_text like '%INSERT INTO VAL_ERR (=20
IP_ADDRE<BR>SS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp; VALUES (=20


:b1,:b2,SYSDATE,:b3,:b4&nbsp; )%'<BR><BR>7 rows selected<BR><BR><BR>To =

verify, I=20


select distinct using like and the text.&nbsp; There are 3 rows:&nbsp; =
the=20


original text, the first select and<BR>the select distinct =
text.<BR><BR>sql&gt;=20


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



IP_ADDRE<BR>SS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp; VALUES (=20


:b1,:b2,SYSDATE,:b3,:b4&nbsp;=20


)%';<BR><BR>SQL_TEXT<BR>-------------------------------------------------=
-------------------------------<BR>INSERT=20


INTO VAL_ERR ( IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp;=20
VALUES<BR>( :b1,:b2,SYSDATE,:b3,:b4&nbsp; )<BR><BR>select =
distinct(sql_text)=20


from v$sql where&nbsp; sql_text like '%INSERT INTO VAL_ERR<BR>(=20
IP_ADDRESS,SITE_CODE,ATTEMPT_DT,PAGE,SESSION_ID )&nbsp; VALUES (=20


:b1,:b2,SYSDATE,:b<BR>3,:b4&nbsp; )%'<BR><BR>select sql_text from v$sql=20

where&nbsp; sql_text like '%INSERT INTO VAL_ERR (=20
Received on Thu Oct 19 2000 - 15:07:29 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US