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