RE: SQL Query Question
- From: Ron.Reidy@xxxxxxxxxxxxxxxxxx (Ron Reidy)
- Date: Thu, 10 Nov 2005 08:37:05 -0700
Run a 10046 trace to see where you are waiting.
-----Original Message-----
From: Vergara, Michael (TEM) [mailto:mvergara@xxxxxxxxxxx]
Sent: Wednesday, November 09, 2005 2:21 PM
To: dbi-users@xxxxxxxx
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.
- Prev by Date: Re: Compiling dbd-oracle on hpux64
- Next by Date: Wish List
- Previous by thread: RE: SQL Query Question
- Next by thread: fetch the column/rows and fields into hash from dbi
- Index(es):
Relevant Pages
|
