RE: SQL Query Question

------_=_NextPart_001_01C5E60C.9AB27F74
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Run a 10046 trace to see where you are waiting.
-----Original Message-----
From: Vergara, Michael (TEM) [mailto:mvergara@guidant.com]=20
Sent: Wednesday, November 09, 2005 2:21 PM
To: dbi-users@perl.org
Subject: SQL Query Question


Hello Everyone:
=20
Thanks to all who have responded to my question about logging. That got
me to the problematic SQL. This SQL,
on just one instance, hangs and does not complete. I have let it run
for hours to no avail. When I run this=20
query at SQL*Plus I get...

'OKAY'
---------------------------------
Okay
=20
1 row selected.
Elapsed: 00:00:00.84

This query runs on all of the other instances without an issue. This is
a 9.2.0.4 database, running on HP-UX. I am=20
running perl "v5.8.4 built for PA-RISC2.0-LP64", with DBI v1.46 and
DBD::Oracle v1.16.
=20
Here is my code. Help!
#
------------------------------------------------------------ ------------
-
# DB Extent growth check
#
------------------------------------------------------------ ------------
-
$SQLstmt =3D
qq{ select 'Okay' from dual
where not exists
( select 'x' from sys.dba_segments t1, extents_history t2
where (t1.owner =3D t2.owner) and
(t1.segment_name =3D t2.segment_name) and
(t1.segment_type =3D t2.segment_type) and
(t1.segment_type not like '%TEMP%') and
(t1.segment_type not like '%ROLLBACK%') and
(t1.segment_type not like '%UNDO%') and
((t1.extents - t2.extents) > :Threshold) )
union all
select 'Extent Growth Above Threshold (${DbExtGrowthThreshold})'
from dual
where exists
( select 'x' from sys.dba_segments t1, extents_history t2
where (t1.owner =3D t2.owner) and
(t1.segment_name =3D t2.segment_name) and
(t1.segment_type =3D t2.segment_type) and
(t1.segment_type not like '%TEMP%') and
(t1.segment_type not like '%ROLLBACK%') and
(t1.segment_type not like '%UNDO%') and
((t1.extents - t2.extents) > :Threshold) ) };
$SQLh =3D $DBh->prepare($SQLstmt,{ora_check_sql =3D> 0 });
$SQLh->bind_param( ":Threshold", $DbExtGrowthThreshold );
$SQLh->execute;
$SQLh->bind_col( 1, \$DbSegments );
$SQLh->fetch;
$SQLh->finish;
print STDOUT "DB Extent Growth....: $DbSegments \n";
print LOGF "DB Extent Growth....: $DbSegments \n";
#
=20
Thanks,
Mike
_____ =20

Michael P. Vergara Be good and you will be lonesome=09
Oracle Database Administrator Mark Twain=09
(951) 914-2000 (Voice) =09
(951) 914-2990 (FAX) =09
www.guidant.com =09

Any views expressed herein are not necessarily those of Guidant
Corporation. =09
=20

This electronic message transmission is a PRIVATE communication which =
contains
information which may be confidential or privileged. The information is =
intended=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
or use of the contents of this information is prohibited. Please notify =
the
sender of the delivery error by replying to this message, or notify us =
by
telephone (877-633-2436, ext. 0), and then delete it from your system.


------_=_NextPart_001_01C5E60C.9AB27F74--