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


Hello Everyone:

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
query at SQL*Plus I get...

'OKAY'
---------------------------------
Okay

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
running perl "v5.8.4 built for PA-RISC2.0-LP64", with DBI v1.46 and
DBD::Oracle v1.16.

Here is my code. Help!
#
------------------------------------------------------------------------
-
# DB Extent growth check
#
------------------------------------------------------------------------
-
$SQLstmt =
qq{ select 'Okay' from dual
where not exists
( select 'x' from sys.dba_segments t1, extents_history t2
where (t1.owner = t2.owner) and
(t1.segment_name = t2.segment_name) and
(t1.segment_type = 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 = t2.owner) and
(t1.segment_name = t2.segment_name) and
(t1.segment_type = 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 = $DBh->prepare($SQLstmt,{ora_check_sql => 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";
#

Thanks,
Mike
_____

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

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


This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
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.

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 4 of 4 | next ›
Discussion Overview
groupdbi-users @
categoriesperl
postedNov 9, '05 at 9:21p
activeNov 10, '05 at 3:37p
posts4
users3
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase