RE: SQL Query Question

[Reidy, Ron]

Run a 10046 trace to see where you are waiting.
-----Original Message-----
From: Vergara, Michael (TEM) [mailto:[EMAIL PROTECTED] 
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.